浅谈Oracle执行计划

  • 宋祖强

    2016-06-28 22:11:05
  • Oracle
  • 转载

   http://ylw6006.blog.51cto.com/470441/892178/ 

 要对sql语句进行分析，首先就是要去看sql语句的执行计划是否存在问题，oracle在10g之后，默认采用CBO基于代价的优化器，sql语句的执行计划根据统计信息分析来决定，如果统计信息未收集，则采取动态采样的方式来决定最优的执行计划！

一: 获取sql语句的执行计划，在使用执行计划前，先以sys用户运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql，该脚本创建了plustrace角色，并给该角色查询v$sessstat,v$statname,v$mystat三个动态性能视图的权限；最后将plustrace角色授予普通用户。

点击(此处)折叠或打开

  1. sys@TESTDB12>@$ORACLE_HOME/sqlplus/admin/plustrce.sql
  2. sys@TESTDB12>drop role plustrace;
  3. drop role plustrace
  4.           *
  5. ERROR at line 1:
  6. ORA-01919: role 'PLUSTRACE' does not exist
  7. sys@TESTDB12>create role plustrace;
  8. Role created.
  9. sys@TESTDB12>
  10. sys@TESTDB12>grant select on v_$sesstat to plustrace;
  11. Grant succeeded.
  12. sys@TESTDB12>grant select on v_$statname to plustrace;
  13. Grant succeeded.
  14. sys@TESTDB12>grant select on v_$mystat to plustrace;
  15. Grant succeeded.
  16. sys@TESTDB12>grant plustrace to dba with admin option;
  17. Grant succeeded.
  18. sys@TESTDB12>
  19. sys@TESTDB12>set echo off
  20. sys@TESTDB12>grant plustrace to scott;
    Grant succeeded.


一: 获取sql语句的执行计划，在使用执行计划前，先以sys用户运行脚本$ORACLE_HOME/sqlplus/admin/plustrce.sql，该脚本创建了plustrace角色，并给该角色查询v$sessstat,v$statname,v$mystat三个动态性能视图的权限；最后将plustrace角色授予普通用户

1：创建测试表，填充数据，创建索引

点击(此处)折叠或打开

  1. SQL> create table t as select 1 id,object_name from dba_objects;
  2. Table created.
  3. SQL> select count(*) from t;
  4.   COUNT(*)
  5. ----------
  6.      77262
  7. SQL> update t set id=99 where rownum=1;
  8. 1 row updated.
  9. SQL> commit;
  10. Commit complete.
  11. SQL> select id,count(*) from t group by id;
  12.         ID COUNT(*)
  13. ---------- ----------
  14.          1 77261
  15.         99 1
  16. SQL> create index i_t_id on t(id);
  17. Index created.


2：获取sql语句的执行计划，‘dynamic sampling used for this statement (level=2)’表示采取级别2的动态采样；执行计划的步骤为靠右靠上先执行，而不是第一列的id顺序，在本例中先执行缩进最靠右的I_T_ID索引范围扫描，然后根据索引扫描出来的结果定位到T表相应行的rowid，谓词中的"2 - access("ID"=99)"表示where后条件id=99会对id为2的INDEX RANGE SCAN造成决定行的影响，这个也很好理解，在本例中如果where语句后面为id=1，则必然选择全表扫描才是最优的执行计划；rows则会返回的结果集行数，统计信息中对应select语句主要看物理读和一致性读的个数.

点击(此处)折叠或打开

  1. SQL> set autot traceonly
  2. SQL> select * from t where id=99;
  3.   
  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 4153437776
  7. -----------------------------------------------------------------------------------
  8.   
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
  10.   
  11. ------------------------------------------------------------------------------------
  12.   
  13. | 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:0
  14.   
  15. | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01
  16.   
  17. |* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01
  18.   
  19. ------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  22.    2 - access("ID"=99)
  23. Note
  24. ----- - dynamic sampling used for this statement (level=2)
  25.   
  26. Statistics
  27. ----------------------------------------------------------
  28.          10 recursive calls
  29.           0 db block gets
  30.          67 consistent gets
  31.           1 physical reads
  32.           0 redo size
  33.         491 bytes sent via SQL*Net to client
  34.         420 bytes received via SQL*Net from client
  35.           2 SQL*Net roundtrips to/from client
  36.           0 sorts (memory)
  37.           0 sorts (disk)
  38.           1 rows processed


3：使用dbms_stats包对表进行收集统计信息，在本例中可以看出进行分析过的表，将不会继续使用动态采样的方式收集信息


点击(此处)折叠或打开


4：对表分析后的表进行update操作，在未重新进行分析前，将产生错误的执行计划，重新分析后正常;因而在生产环境中，经常有DML操作的表应当根据实际情况进行分析，否则将产生类似的问题.

点击(此处)折叠或打开

  1. SQL> set autot off
  2. SQL> update t set id=99 where id=1;
  3. 77261 rows updated.
  4.   
  5. SQL> update t set id=1 where rownum=1;
  6. 1 row updated.
  7.   
  8. SQL> commit;
  9. Commit complete.
  10.   
  11. SQL> select id,count(*) from t group by id;
  12.   
  13.         ID COUNT(*)
  14. ---------- ----------
  15.          1 1
  16.         99 77261
  17.   
  18. SQL> set autot traceonly
  19. SQL> select * from t where id=99;
  20. 77261 rows selected.
  21.   
  22. Execution Plan
  23. ----------------------------------------------------------
  24. Plan hash value: 4153437776
  25. --------------------------------------------------------------------------------------
  26.   
  27. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
  28.   
  29. ------------------------------------------------------------------------------------
  30.   
  31. | 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01
  32. | 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01
  33.   
  34. |* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01
  35.   
  36. -----------------------------------------------------------------------------------
  37. Predicate Information (identified by operation id):
  38. ---------------------------------------------------
  39.    2 - access("ID"=99)
  40.   
  41. Statistics
  42. ----------------------------------------------------------
  43.           0 recursive calls
  44.           0 db block gets
  45.       11017 consistent gets
  46.           0 physical reads
  47.       13840 redo size
  48.     3170997 bytes sent via SQL*Net to client
  49.       57070 bytes received via SQL*Net from client
  50.        5152 SQL*Net roundtrips to/from client
  51.           0 sorts (memory)
  52.           0 sorts (disk)
  53.       77261 rows processed
  54.   
  55.   
  56. SQL> select /*+ full(t) */ * from t where id=99;
  57. 77261 rows selected.
  58.   
  59. Execution Plan
  60. ----------------------------------------------------------
  61. Plan hash value: 1601196873
  62. --------------------------------------------------------------------------
  63. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  64. --------------------------------------------------------------------------
  65. | 0 | SELECT STATEMENT | | 14 | 378 | 101 (1)| 00:00:02 |
  66. |* 1 | TABLE ACCESS FULL| T | 14 | 378 | 101 (1)| 00:00:02 |
  67. --------------------------------------------------------------------------
  68. Predicate Information (identified by operation id):
  69. ---------------------------------------------------
  70.    1 - filter("ID"=99)
  71.   
  72. Statistics
  73. ----------------------------------------------------------
  74.           1 recursive calls
  75.           0 db block gets
  76.        5477 consistent gets
  77.           0 physical reads
  78.           0 redo size
  79.     2357600 bytes sent via SQL*Net to client
  80.       57070 bytes received via SQL*Net from client
  81.        5152 SQL*Net roundtrips to/from client
  82.           0 sorts (memory)
  83.           0 sorts (disk)
  84.       77261 rows processed
  85.   
  86. SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T',CASCADE=>TRUE);
  87. PL/SQL procedure successfully completed.
  88.   
  89. SQL> select * from t where id=99;
  90. 77261 rows selected.
  91.   
  92. Execution Plan
  93. ----------------------------------------------------------
  94. Plan hash value: 1601196873
  95. --------------------------------------------------------------------------
  96. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  97. --------------------------------------------------------------------------
  98. | 0 | SELECT STATEMENT | | 77241 | 2036K| 101 (1)| 00:00:02 |
  99. |* 1 | TABLE ACCESS FULL| T | 77241 | 2036K| 101 (1)| 00:00:02 |
  100. --------------------------------------------------------------------------
  101. Predicate Information (identified by operation id):
  102. ---------------------------------------------------
  103.    1 - filter("ID"=99)
  104.   
  105. Statistics
  106. ----------------------------------------------------------
  107.           0 recursive calls
  108.           0 db block gets
  109.       10845 consistent gets
  110.           0 physical reads
  111.           0 redo size
  112.     3170997 bytes sent via SQL*Net to client
  113.       57070 bytes received via SQL*Net from client
  114.        5152 SQL*Net roundtrips to/from client
  115.           0 sorts (memory)
  116.           0 sorts (disk)
  117.       77261 rows processed


5：执行计划的另一种查看方法，使用explain，同set autot on方式比(set autot traceonly只产生执行计划和统计信息，不执行实际的sql语句，因而广泛用于生产环境中)，不产生"Statistics"信息

点击(此处)折叠或打开

  1. SQL> explain plan for select * from t where id=1;
  2. Explained.
  3.   
  4. SQL> select * from table(dbms_xplan.display);
  5.   
  6. PLAN_TABLE_OUTPUT
  7. --------------------------------------------------------------------------------
  8. Plan hash value: 4153437776
  9. --------------------------------------------------------------------------------------
  10. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  11. --------------------------------------------------------------------------------------
  12.   
  13. | 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
  14.   
  15. | 1 | TABLE ACCESS BY INDEX ROWID| T | 14 | 378 | 2 (0)| 00:00:01 |
  16.   
  17. |* 2 | INDEX RANGE SCAN | I_T_ID | 14 | | 1 (0)| 00:00:01 |
  18.   
  19. --------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  22.    2 - access("ID"=1)
  23.   
  24. 14 rows selected.


二：直方图(histogram），指数据在列上的值分布情况；倾斜表场合，应当做对索引列做直方图,否则同样会产生错误的执行计划
DBMS_STATS包对表进行分析主要包含
1:表本身的分析，包括表的行数，数据块数，每一行的长度等信息
2:列的分析，包括列的重复数，列的空值数，列的值分布情况等
3:索引的分析，包括索引的块数，索引的深度(blevel),索引的聚合因子等

点击(此处)折叠或打开

  1. SQL> create table t1 as select 1 id,object_name from dba_objects;
  2. Table created.
  3.   
  4. SQL> update t1 set id=99 where rownum=1;
  5. 1 row updated.
  6.   
  7. SQL> commit;
  8. Commit complete.
  9.   
  10. SQL> create index i_t1_id on t1(id);
  11. Index created.
  12.   
  13. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
  14. ograms where table_name='T1';
  15. no rows selected
  16.   
  17. SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T1',CASCADE=>TRUE);
  18. PL/SQL procedure successfully completed.
  19.   
  20. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
  21. ograms where table_name='T1';
  22.   
  23. TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
  24. -------------------- -------------------- --------------- --------------
  25. T1 ID 0 1
  26. T1 OBJECT_NAME 0 2.4504E+35
  27. T1 ID 1 99
  28. T1 OBJECT_NAME 1 6.2963E+35
  29.   
  30. SQL> select count(*),id from t1 group by id;
  31.   
  32.   COUNT(*) ID
  33. ---------- ----------
  34.      77267 1
  35.          1 99
  36.   
  37. SQL> set autot traceonly
  38. SQL> select * from t1 where id=99;
  39.   
  40. Execution Plan
  41. ----------------------------------------------------------
  42. Plan hash value: 1111474805
  43.   
  44. ---------------------------------------------------------------------------------------
  45.   
  46. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  47.   
  48. ------------------------------------------------------------------------------------
  49.   
  50. | 0 | SELECT STATEMENT | | 14 | 378 | 2 (0)| 00:00:01 |
  51. | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 14 | 378 | 2 (0)| 00:00:01 |
  52. |* 2 | INDEX RANGE SCAN |I_T1_ID | 14 | | 1 (0)| 00:00:01 |
  53.   
  54. ------------------------------------------------------------------------------------
  55. Predicate Information (identified by operation id):
  56. ---------------------------------------------------
  57.    2 - access("ID"=99)
  58.   
  59. Statistics
  60. ----------------------------------------------------------
  61.           0 recursive calls
  62.           0 db block gets
  63.         351 consistent gets
  64.           0 physical reads
  65.           0 redo size
  66.         487 bytes sent via SQL*Net to client
  67.         420 bytes received via SQL*Net from client
  68.           2 SQL*Net roundtrips to/from client
  69.           0 sorts (memory)
  70.           0 sorts (disk)
  71.           1 rows processed
  72.   
  73.   
  74. SQL> select * from t1 where id=1;
  75. 77267 rows selected.
  76.   
  77. Execution Plan
  78. ---------------------------------------------------------
  79. Plan hash value: 3617692013
  80. --------------------------------------------------------------------------
  81. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  82. --------------------------------------------------------------------------
  83. | 0 | SELECT STATEMENT | | 77254 | 2036K| 101 (1)| 00:00:02 |
  84. |* 1 | TABLE ACCESS FULL| T1 | 77254 | 2036K| 101 (1)| 00:00:02 |
  85. --------------------------------------------------------------------------
  86. Predicate Information (identified by operation id):
  87. ---------------------------------------------------
  88.    1 - filter("ID"=1)
  89.   
  90. Statistics
  91. ----------------------------------------------------------
  92.           8 recursive calls
  93.           0 db block gets
  94.        5489 consistent gets
  95.           0 physical reads
  96.           0 redo size
  97.     2357825 bytes sent via SQL*Net to client
  98.       57081 bytes received via SQL*Net from client
  99.        5153 SQL*Net roundtrips to/from client
  100.           3 sorts (memory)
  101.           0 sorts (disk)
  102.       77267 rows processed
  103.   
  104. SQL> set autot off
  105. SQL> exec dbms_stats.delete_column_stats('HR','T1','ID');
  106.   
  107. PL/SQL procedure successfully completed.
  108.   
  109. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
  110. ograms where table_name='T1';
  111.   
  112. TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
  113. -------------------- -------------------- --------------- --------------
  114. T1 OBJECT_NAME 0 2.4504E+35
  115. T1 OBJECT_NAME 1 6.2963E+35
  116.   
  117. SQL> set autot traceonly
  118. SQL> select * from t1 where id=99;
  119.   
  120. Execution Plan
  121. ----------------------------------------------------------
  122. Plan hash value: 1111474805
  123. ------------------------------------------------------------------------------------
  124.   
  125. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  126. ------------------------------------------------------------------------------------
  127.   
  128. | 0 | SELECT STATEMENT | | 773 | 20871 | 77 (0)| 00:00:01 |
  129. | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 773 | 20871 | 77 (0)| 00:00:01 |
  130.   
  131. |* 2 | INDEX RANGE SCAN | I_T1_ID | 309 | | 75 (0)| 00:00:01 |
  132. ------------------------------------------------------------------------------------
  133. Predicate Information (identified by operation id):
  134. ---------------------------------------------------
  135.    2 - access("ID"=99)
  136.   
  137. Statistics
  138. ----------------------------------------------------------
  139.           0 recursive calls
  140.           0 db block gets
  141.         351 consistent gets
  142.           0 physical reads
  143.           0 redo size
  144.         487 bytes sent via SQL*Net to client
  145.         420 bytes received via SQL*Net from client
  146.           2 SQL*Net roundtrips to/from client
  147.           0 sorts (memory)
  148.           0 sorts (disk)
  149.           1 rows processed
  150.   
  151. SQL> select * from t1 where id=1;
  152. 77267 rows selected.
  153.   
  154.   
  155. Execution Plan
  156. ----------------------------------------------------------
  157. Plan hash value: 1111474805
  158. ---------------------------------------------------------------------------------------
  159.   
  160. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  161.   
  162. ---------------------------------------------------------------------------------------
  163.   
  164. | 0 | SELECT STATEMENT | | 773 | 20871 | 77 (0)| 00:00:01 |
  165.   
  166. | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 773 | 20871 | 77 (0)| 00:00:01 |
  167.   
  168. |* 2 | INDEX RANGE SCAN | I_T1_ID | 309 | | 75 (0)| 00:00:01 |
  169.   
  170. ---------------------------------------------------------------------------------------
  171. Predicate Information (identified by operation id):
  172. ---------------------------------------------------
  173.    2 - access("ID"=1)
  174.   
  175. Statistics
  176. ----------------------------------------------------------
  177.           1 recursive calls
  178.           0 db block gets
  179.       10781 consistent gets
  180.           0 physical reads
  181.           0 redo size
  182.     3171208 bytes sent via SQL*Net to client
  183.       57081 bytes received via SQL*Net from client
  184.        5153 SQL*Net roundtrips to/from client
  185.           0 sorts (memory)
  186.           0 sorts (disk)
  187.       77267 rows processed
  188.   
  189. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
  190. PL/SQL procedure successfully completed.
  191.   
  192. SQL> set autot off
  193. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist
  194. ograms where table_name='T1';
  195.   
  196. TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
  197. -------------------- -------------------- --------------- --------------
  198. T1 ID 5527 99
  199. T1 ID 5526 1
  200. T1 OBJECT_NAME 0 2.4504E+35
  201. T1 OBJECT_NAME 1 6.2963E+35


三：动态采样，动态采样分0-10，11个级别，级别越高，采样的结果越精确，需要消耗的数据库成本也越高

level0:不进行动态采样

level1:对没有进行分析的表进行动态采样，要求同时满足下列4个条件；
sql语句中至少有一个未分析的表
未分析的表出现在关联查询或者子查询中
未分析的表没有索引
未分析的表占用的数据块大于动态采样默认的数据块(32个)

level2:对所有未分析的表做分析，动态采样的数据块是默认数据块的2倍

level3:采样的表包含满足level2定义的所有表，同时包括，谓词中包含的潜在的需要动态采样的表

level4:采样的表满足level3定义的所有表，同时还包括一些表，他们包含一个单表的谓词会引用另外的2个列或者更多的列

level5,6,7,8,9:采样的表包含满足level4定义的所有表，同时分别使用默认数据库的2,4,8,32,128倍的数量做动态采样

level10:采样的表满足level9定义的所有表,同时对表的所有数据进行动态采样

1：创建基表，未收集统计信息前，user_tables视图中的相关信息未填充，11g版本中，user_indexes视图中的信息会被填充

点击(此处)折叠或打开

  1. SQL> create table t2 as select object_id,object_name from dba_objects;
  2. Table created.
  3.   
  4. SQL> create index i_t2_id on t2 (object_id);
  5. Index created.
  6.   
  7. SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab
  8. le_name='T2';
  9.   
  10.   NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
  11. ---------- ----------- ---------- ---------------
  12.   
  13.   
  14. SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in
  15. dexes where table_name='T2';
  16.   
  17.     BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
  18. ---------- ----------- ------------- ---------- -------------------
  19.          1 171 77265 77265 2012-06-07:13:55:04

2：执行查询，毫无疑问的采用动态采样，也能获取正确的执行计划，‘1 - filter("OBJECT_ID">30)’表示对结果进行过滤

点击(此处)折叠或打开

  1. SQL> set autot traceonly
  2. SQL> select * from t2 where object_id > 30;
  3. 77236 rows selected.
  4.   
  5. Execution Plan
  6. ----------------------------------------------------------
  7. Plan hash value: 1513984157
  8. --------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  10. --------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 58208 | 4490K| 105 (0)| 00:00:02 |
  12. |* 1 | TABLE ACCESS FULL| T2 | 58208 | 4490K| 105 (0)| 00:00:02 |
  13. --------------------------------------------------------------------------
  14. Predicate Information (identified by operation id):
  15. ---------------------------------------------------
  16.    1 - filter("OBJECT_ID">30)
  17. Note
  18. -----
  19.    - dynamic sampling used for this statement (level=2)
  20.   
  21. Statistics
  22. ----------------------------------------------------------
  23.          24 recursive calls
  24.           0 db block gets
  25.        5586 consistent gets
  26.           6 physical reads
  27.           0 redo size
  28.     3005346 bytes sent via SQL*Net to client
  29.       57059 bytes received via SQL*Net from client
  30.        5151 SQL*Net roundtrips to/from client
  31.           6 sorts (memory)
  32.           0 sorts (disk)
  33.       77236 rows processed


3：使用hint提示符，禁用动态采样，将产生错误的执行计划

点击(此处)折叠或打开

  1. SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;
  2. 77236 rows selected.
  3.   
  4. Execution Plan
  5. ----------------------------------------------------------
  6. Plan hash value: 3661687773
  7. ---------------------------------------------------------------------------------------
  8.   
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  10.   
  11. ---------------------------------------------------------------------------------------
  12.   
  13. | 0 | SELECT STATEMENT | | 1556 | 120K| 8 (0)| 00:00:01 |
  14.   
  15. | 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1556 | 120K| 8 (0)| 00:00:01 |
  16.   
  17. |* 2 | INDEX RANGE SCAN | I_T2_ID | 280 | | 3 (0)| 00:00:01 |
  18.   
  19. ---------------------------------------------------------------------------------------
  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------
  22.    2 - access("OBJECT_ID">30)
  23.   
  24. Statistics
  25. ----------------------------------------------------------
  26.           1 recursive calls
  27.           0 db block gets
  28.       10897 consistent gets
  29.         165 physical reads
  30.           0 redo size
  31.     3314234 bytes sent via SQL*Net to client
  32.       57059 bytes received via SQL*Net from client
  33.        5151 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.       77236 rows processed


3：收集统计信息后，user_tables视图相应的内容会被填充，禁用动态采样也能获取正确的执行计划

点击(此处)折叠或打开

  1. SQL> set autot off
  2. SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);
  3. PL/SQL procedure successfully completed.
  4.   
  5. SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab
  6. le_name='T2';
  7.   
  8.   NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
  9. ---------- ----------- ---------- -------------------
  10.      77266 29 381 2012-06-07:14:05:52
  11.   
  12. SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in
  13. dexes where table_name='T2';
  14.   
  15.     BLEVEL LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS LAST_ANALYZED
  16. ---------- ----------- ------------- ---------- -------------------
  17.          1 171 77265 77265 2012-06-07:14:05:52
  18.   
  19. SQL> set autot traceonly
  20. SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;
  21. 77236 rows selected.
  22.   
  23. Execution Plan
  24. ----------------------------------------------------------
  25. Plan hash value: 1513984157
  26.   
  27. --------------------------------------------------------------------------
  28. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  29. --------------------------------------------------------------------------
  30. | 0 | SELECT STATEMENT | | 77242 | 2187K| 106 (1)| 00:00:02 |
  31. |* 1 | TABLE ACCESS FULL| T2 | 77242 | 2187K| 106 (1)| 00:00:02 |
  32. --------------------------------------------------------------------------
  33.   
  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------
  36.    1 - filter("OBJECT_ID">30)
  37.   
  38. Statistics
  39. ----------------------------------------------------------
  40.          12 recursive calls
  41.           0 db block gets
  42.        5516 consistent gets
  43.           0 physical reads
  44.           0 redo size
  45.     3005346 bytes sent via SQL*Net to client
  46.       57059 bytes received via SQL*Net from client
  47.        5151 SQL*Net roundtrips to/from client
  48.           5 sorts (memory)
  49.           0 sorts (disk)
  50.       77236 rows processed


请使用浏览器的分享功能分享到微信等