AI 助理
备案控制台
开发者社区 数据库 文章 正文

浅谈Oracle执行计划

2017-11-22 701
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

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

一: 获取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. 

  
  
  2. Table created.  
    3. 

  
  
  3.  
    4. 

  
  
  4. SQL> select count(*) from t;  
    5. 

  
  
  5.  
    6. 

  
  
  6.   COUNT(*)  
    7. 

  
  
  7. ----------  
    8. 

  
  
  8.      77262  
    9. 

  
  
  9.  
    10. 

  
  
  10. SQL> update t set id=99 where rownum=1;  
    11. 

  
  
  11. 1 row updated.  
    12. 

  
  
  12.  
    13. 

  
  
  13. SQL> commit;  
    14. 

  
  
  14. Commit complete.  
    15. 

  
  
  15.  
    16. 

  
  
  16. SQL> select id,count(*) from t group by id;  
    17. 

  
  
  17.  
    18. 

  
  
  18.         ID   COUNT(*)  
    19. 

  
  
  19. ---------- ----------  
    20. 

  
  
  20.          1      77261  
    21. 

  
  
  21.         99          1  
    22. 

  
  
  22.  
    23. 

  
  
  23. SQL> create index i_t_id on t(id);  
    24. 

  
  
  24. Index created. 
    25.

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. 

  
  
  2. SQL> select * from t where id=99;  
    3. 

  
  
  3.  
    4. 

  
  
  4. Execution Plan  
    5. 

  
  
  5. ----------------------------------------------------------  
    6. 

  
  
  6. Plan hash value: 4153437776  
    7. 

  
  
  7. --------------------------------------------------------------------------------------  
    8. 

  
  
  8.  
    9. 

  
  
  9. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time|  
    10. 

  
  
  10.  
    11. 

  
  
  11. --------------------------------------------------------------------------------------  
    12. 

  
  
  12.  
    13. 

  
  
  13. |   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |  
    14. 

  
  
  14.  
    15. 

  
  
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    79 |     2   (0)| 00:00:01 |  
    16. 

  
  
  16.  
    17. 

  
  
  17. |*  2 |   INDEX RANGE SCAN          | I_T_ID |     1 |       |     1   (0)| 00:00:01 |  
    18. 

  
  
  18.  
    19. 

  
  
  19. --------------------------------------------------------------------------------------  
    20. 

  
  
  20. Predicate Information (identified by operation id):  
    21. 

  
  
  21. ---------------------------------------------------  
    22. 

  
  
  22.    2 - access("ID"=99)  
    23. 

  
  
  23. Note  
    24. 

  
  
  24. -----  
    25. 

  
  
  25.    - dynamic sampling used for this statement (level=2)  
    26. 

  
  
  26.  
    27. 

  
  
  27. Statistics  
    28. 

  
  
  28. ----------------------------------------------------------  
    29. 

  
  
  29.          10  recursive calls  
    30. 

  
  
  30.           0  db block gets  
    31. 

  
  
  31.          67  consistent gets  
    32. 

  
  
  32.           1  physical reads  
    33. 

  
  
  33.           0  redo size  
    34. 

  
  
  34.         491  bytes sent via SQL*Net to client  
    35. 

  
  
  35.         420  bytes received via SQL*Net from client  
    36. 

  
  
  36.           2  SQL*Net roundtrips to/from client  
    37. 

  
  
  37.           0  sorts (memory)  
    38. 

  
  
  38.           0  sorts (disk)  
    39. 

  
  
  39.           1  rows processed 
    40.

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


 
 
    

  
  
  1. SQL> exec dbms_stats.gather_table_stats('HR','T',CASCADE=>TRUE);  
    2. 

  
  
  2. PL/SQL procedure successfully completed.  
    3. 

  
  
  3.  
    4. 

  
  
  4. SQL> select * from t where id=99;  
    5. 

  
  
  5.  
    6. 

  
  
  6. Execution Plan  
    7. 

  
  
  7. ----------------------------------------------------------  
    8. 

  
  
  8. Plan hash value: 4153437776  
    9. 

  
  
  9. --------------------------------------------------------------------------------------  
    10. 

  
  
  10.  
    11. 

  
  
  11. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time|  
    12. 

  
  
  12.  
    13. 

  
  
  13. --------------------------------------------------------------------------------------  
    14. 

  
  
  14.  
    15. 

  
  
  15. |   0 | SELECT STATEMENT            |        |    14 |   378 |     2   (0)| 00:00:01 |  
    16. 

  
  
  16.  
    17. 

  
  
  17. |   1 |  TABLE ACCESS BY INDEX ROWID| T      |    14 |   378 |     2   (0)| 00:00:01 |  
    18. 

  
  
  18.  
    19. 

  
  
  19. |*  2 |   INDEX RANGE SCAN          | I_T_ID |    14 |       |     1   (0)| 00:00:01 |  
    20. 

  
  
  20.  
    21. 

  
  
  21. --------------------------------------------------------------------------------------  
    22. 

  
  
  22. Predicate Information (identified by operation id):  
    23. 

  
  
  23. ---------------------------------------------------  
    24. 

  
  
  24.    2 - access("ID"=99)  
    25. 

  
  
  25.  
    26. 

  
  
  26. Statistics  
    27. 

  
  
  27. ----------------------------------------------------------  
    28. 

  
  
  28.           0  recursive calls  
    29. 

  
  
  29.           0  db block gets  
    30. 

  
  
  30.           3  consistent gets  
    31. 

  
  
  31.           0  physical reads  
    32. 

  
  
  32.           0  redo size  
    33. 

  
  
  33.         491  bytes sent via SQL*Net to client  
    34. 

  
  
  34.         420  bytes received via SQL*Net from client  
    35. 

  
  
  35.           2  SQL*Net roundtrips to/from client  
    36. 

  
  
  36.           0  sorts (memory)  
    37. 

  
  
  37.           0  sorts (disk)  
    38. 

  
  
  38.           1  rows processed 
    39.

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


 
 
    

  
  
  1. SQL> set autot off  
    2. 

  
  
  2. SQL> update t set id=99 where id=1;  
    3. 

  
  
  3. 77261 rows updated.  
    4. 

  
  
  4.  
    5. 

  
  
  5. SQL> update t set id=1 where rownum=1;  
    6. 

  
  
  6. 1 row updated.  
    7. 

  
  
  7.  
    8. 

  
  
  8. SQL> commit;  
    9. 

  
  
  9. Commit complete.  
    10. 

  
  
  10.  
    11. 

  
  
  11. SQL> select id,count(*) from t group by id;  
    12. 

  
  
  12.  
    13. 

  
  
  13.         ID   COUNT(*)  
    14. 

  
  
  14. ---------- ----------  
    15. 

  
  
  15.          1          1  
    16. 

  
  
  16.         99      77261  
    17. 

  
  
  17.  
    18. 

  
  
  18. SQL> set autot traceonly  
    19. 

  
  
  19. SQL> select * from t where id=99;  
    20. 

  
  
  20. 77261 rows selected.  
    21. 

  
  
  21.  
    22. 

  
  
  22. Execution Plan  
    23. 

  
  
  23. ----------------------------------------------------------  
    24. 

  
  
  24. Plan hash value: 4153437776  
    25. 

  
  
  25. --------------------------------------------------------------------------------------  
    26. 

  
  
  26.  
    27. 

  
  
  27. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time|  
    28. 

  
  
  28.  
    29. 

  
  
  29. --------------------------------------------------------------------------------------  
    30. 

  
  
  30.  
    31. 

  
  
  31. |   0 | SELECT STATEMENT            |        |    14 |   378 |     2   (0)| 00:00:01 |  
    32. 

  
  
  32.  
    33. 

  
  
  33. |   1 |  TABLE ACCESS BY INDEX ROWID| T      |    14 |   378 |     2   (0)| 00:00:01 |  
    34. 

  
  
  34.  
    35. 

  
  
  35. |*  2 |   INDEX RANGE SCAN          | I_T_ID |    14 |       |     1   (0)| 00:00:01 |  
    36. 

  
  
  36.  
    37. 

  
  
  37. --------------------------------------------------------------------------------------  
    38. 

  
  
  38. Predicate Information (identified by operation id):  
    39. 

  
  
  39. ---------------------------------------------------  
    40. 

  
  
  40.    2 - access("ID"=99)  
    41. 

  
  
  41.  
    42. 

  
  
  42. Statistics  
    43. 

  
  
  43. ----------------------------------------------------------  
    44. 

  
  
  44.           0  recursive calls  
    45. 

  
  
  45.           0  db block gets  
    46. 

  
  
  46.       11017  consistent gets  
    47. 

  
  
  47.           0  physical reads  
    48. 

  
  
  48.       13840  redo size  
    49. 

  
  
  49.     3170997  bytes sent via SQL*Net to client  
    50. 

  
  
  50.       57070  bytes received via SQL*Net from client  
    51. 

  
  
  51.        5152  SQL*Net roundtrips to/from client  
    52. 

  
  
  52.           0  sorts (memory)  
    53. 

  
  
  53.           0  sorts (disk)  
    54. 

  
  
  54.       77261  rows processed  
    55. 

  
  
  55.  
    56. 

  
  
  56.  
    57. 

  
  
  57. SQL> select /*+ full(t) */ *  from t where id=99;  
    58. 

  
  
  58. 77261 rows selected.  
    59. 

  
  
  59.  
    60. 

  
  
  60. Execution Plan  
    61. 

  
  
  61. ----------------------------------------------------------  
    62. 

  
  
  62. Plan hash value: 1601196873  
    63. 

  
  
  63. --------------------------------------------------------------------------  
    64. 

  
  
  64. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    65. 

  
  
  65. --------------------------------------------------------------------------  
    66. 

  
  
  66. |   0 | SELECT STATEMENT  |      |    14 |   378 |   101   (1)| 00:00:02 |  
    67. 

  
  
  67. |*  1 |  TABLE ACCESS FULL| T    |    14 |   378 |   101   (1)| 00:00:02 |  
    68. 

  
  
  68. --------------------------------------------------------------------------  
    69. 

  
  
  69. Predicate Information (identified by operation id):  
    70. 

  
  
  70. ---------------------------------------------------  
    71. 

  
  
  71.    1 - filter("ID"=99)  
    72. 

  
  
  72.  
    73. 

  
  
  73. Statistics  
    74. 

  
  
  74. ----------------------------------------------------------  
    75. 

  
  
  75.           1  recursive calls  
    76. 

  
  
  76.           0  db block gets  
    77. 

  
  
  77.        5477  consistent gets  
    78. 

  
  
  78.           0  physical reads  
    79. 

  
  
  79.           0  redo size  
    80. 

  
  
  80.     2357600  bytes sent via SQL*Net to client  
    81. 

  
  
  81.       57070  bytes received via SQL*Net from client  
    82. 

  
  
  82.        5152  SQL*Net roundtrips to/from client  
    83. 

  
  
  83.           0  sorts (memory)  
    84. 

  
  
  84.           0  sorts (disk)  
    85. 

  
  
  85.       77261  rows processed  
    86. 

  
  
  86.  
    87. 

  
  
  87. SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T',CASCADE=>TRUE);  
    88. 

  
  
  88. PL/SQL procedure successfully completed.  
    89. 

  
  
  89.  
    90. 

  
  
  90. SQL> select * from t where id=99;  
    91. 

  
  
  91. 77261 rows selected.  
    92. 

  
  
  92.  
    93. 

  
  
  93. Execution Plan  
    94. 

  
  
  94. ----------------------------------------------------------  
    95. 

  
  
  95. Plan hash value: 1601196873  
    96. 

  
  
  96. --------------------------------------------------------------------------  
    97. 

  
  
  97. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    98. 

  
  
  98. --------------------------------------------------------------------------  
    99. 

  
  
  99. |   0 | SELECT STATEMENT  |      | 77241 |  2036K|   101   (1)| 00:00:02 |  
    100. 

  
  
  100. |*  1 |  TABLE ACCESS FULL| T    | 77241 |  2036K|   101   (1)| 00:00:02 |  
    101. 

  
  
  101. --------------------------------------------------------------------------  
    102. 

  
  
  102. Predicate Information (identified by operation id):  
    103. 

  
  
  103. ---------------------------------------------------  
    104. 

  
  
  104.    1 - filter("ID"=99)  
    105. 

  
  
  105.  
    106. 

  
  
  106. Statistics  
    107. 

  
  
  107. ----------------------------------------------------------  
    108. 

  
  
  108.           0  recursive calls  
    109. 

  
  
  109.           0  db block gets  
    110. 

  
  
  110.       10845  consistent gets  
    111. 

  
  
  111.           0  physical reads  
    112. 

  
  
  112.           0  redo size  
    113. 

  
  
  113.     3170997  bytes sent via SQL*Net to client  
    114. 

  
  
  114.       57070  bytes received via SQL*Net from client  
    115. 

  
  
  115.        5152  SQL*Net roundtrips to/from client  
    116. 

  
  
  116.           0  sorts (memory)  
    117. 

  
  
  117.           0  sorts (disk)  
    118. 

  
  
  118.       77261  rows processed 
    119.

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


 
 
    

  
  
  1. SQL> explain plan for select * from t where id=1;  
    2. 

  
  
  2. Explained.  
    3. 

  
  
  3.  
    4. 

  
  
  4. SQL> select * from table(dbms_xplan.display);  
    5. 

  
  
  5.  
    6. 

  
  
  6. PLAN_TABLE_OUTPUT  
    7. 

  
  
  7. --------------------------------------------------------------------------------  
    8. 

  
  
  8. Plan hash value: 4153437776  
    9. 

  
  
  9. --------------------------------------------------------------------------------------  
    10. 

  
  
  10. | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time |  
    11. 

  
  
  11. --------------------------------------------------------------------------------------  
    12. 

  
  
  12.  
    13. 

  
  
  13. |   0 | SELECT STATEMENT            |        |    14 |   378 |     2   (0)| 00:00:01 |  
    14. 

  
  
  14.  
    15. 

  
  
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| T      |    14 |   378 |     2   (0)| 00:00:01 |  
    16. 

  
  
  16.  
    17. 

  
  
  17. |*  2 |   INDEX RANGE SCAN          | I_T_ID |    14 |       |     1   (0)| 00:00:01 |  
    18. 

  
  
  18.  
    19. 

  
  
  19. --------------------------------------------------------------------------------------  
    20. 

  
  
  20. Predicate Information (identified by operation id):  
    21. 

  
  
  21. ---------------------------------------------------  
    22. 

  
  
  22.    2 - access("ID"=1)  
    23. 

  
  
  23.  
    24. 

  
  
  24. 14 rows selected. 
    25.

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


 
 
    

  
  
  1. SQL> create table t1 as select  1 id,object_name from dba_objects;  
    2. 

  
  
  2. Table created.  
    3. 

  
  
  3.  
    4. 

  
  
  4. SQL> update t1 set id=99 where rownum=1;  
    5. 

  
  
  5. 1 row updated.  
    6. 

  
  
  6.  
    7. 

  
  
  7. SQL> commit;  
    8. 

  
  
  8. Commit complete.  
    9. 

  
  
  9.  
    10. 

  
  
  10. SQL> create index i_t1_id on t1(id);  
    11. 

  
  
  11. Index created.  
    12. 

  
  
  12.  
    13. 

  
  
  13. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist  
    14. 

  
  
  14. ograms where table_name='T1';  
    15. 

  
  
  15. no rows selected  
    16. 

  
  
  16.  
    17. 

  
  
  17. SQL> exec dbms_stats.gather_table_stats('HEROSTART_CN','T1',CASCADE=>TRUE);  
    18. 

  
  
  18. PL/SQL procedure successfully completed.  
    19. 

  
  
  19.  
    20. 

  
  
  20. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist  
    21. 

  
  
  21. ograms where table_name='T1';  
    22. 

  
  
  22.  
    23. 

  
  
  23. TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE  
    24. 

  
  
  24. -------------------- -------------------- --------------- --------------  
    25. 

  
  
  25. T1                   ID                                 0              1  
    26. 

  
  
  26. T1                   OBJECT_NAME                        0     2.4504E+35  
    27. 

  
  
  27. T1                   ID                                 1             99  
    28. 

  
  
  28. T1                   OBJECT_NAME                        1     6.2963E+35  
    29. 

  
  
  29.  
    30. 

  
  
  30. SQL> select count(*),id from t1 group by id;  
    31. 

  
  
  31.  
    32. 

  
  
  32.   COUNT(*)         ID  
    33. 

  
  
  33. ---------- ----------  
    34. 

  
  
  34.      77267          1  
    35. 

  
  
  35.          1         99  
    36. 

  
  
  36.  
    37. 

  
  
  37. SQL> set autot traceonly  
    38. 

  
  
  38. SQL> select * from t1 where id=99;  
    39. 

  
  
  39.  
    40. 

  
  
  40. Execution Plan  
    41. 

  
  
  41. ----------------------------------------------------------  
    42. 

  
  
  42. Plan hash value: 1111474805  
    43. 

  
  
  43.  
    44. 

  
  
  44. ---------------------------------------------------------------------------------------  
    45. 

  
  
  45.  
    46. 

  
  
  46. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
    47. 

  
  
  47.  
    48. 

  
  
  48. ---------------------------------------------------------------------------------------  
    49. 

  
  
  49.  
    50. 

  
  
  50. |   0 | SELECT STATEMENT            |         |    14 |   378 |     2   (0)| 00:00:01 |  
    51. 

  
  
  51.  
    52. 

  
  
  52. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    14 |   378 |     2   (0)| 00:00:01 |  
    53. 

  
  
  53.  
    54. 

  
  
  54. |*  2 |   INDEX RANGE SCAN          | I_T1_ID |    14 |       |     1   (0)| 00:00:01 |  
    55. 

  
  
  55.  
    56. 

  
  
  56. ---------------------------------------------------------------------------------------  
    57. 

  
  
  57. Predicate Information (identified by operation id):  
    58. 

  
  
  58. ---------------------------------------------------  
    59. 

  
  
  59.    2 - access("ID"=99)  
    60. 

  
  
  60.  
    61. 

  
  
  61. Statistics  
    62. 

  
  
  62. ----------------------------------------------------------  
    63. 

  
  
  63.           0  recursive calls  
    64. 

  
  
  64.           0  db block gets  
    65. 

  
  
  65.         351  consistent gets  
    66. 

  
  
  66.           0  physical reads  
    67. 

  
  
  67.           0  redo size  
    68. 

  
  
  68.         487  bytes sent via SQL*Net to client  
    69. 

  
  
  69.         420  bytes received via SQL*Net from client  
    70. 

  
  
  70.           2  SQL*Net roundtrips to/from client  
    71. 

  
  
  71.           0  sorts (memory)  
    72. 

  
  
  72.           0  sorts (disk)  
    73. 

  
  
  73.           1  rows processed  
    74. 

  
  
  74.  
    75. 

  
  
  75.  
    76. 

  
  
  76. SQL> select * from t1 where id=1;  
    77. 

  
  
  77. 77267 rows selected.  
    78. 

  
  
  78.  
    79. 

  
  
  79. Execution Plan  
    80. 

  
  
  80. ---------------------------------------------------------  
    81. 

  
  
  81. Plan hash value: 3617692013  
    82. 

  
  
  82. --------------------------------------------------------------------------  
    83. 

  
  
  83. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    84. 

  
  
  84. --------------------------------------------------------------------------  
    85. 

  
  
  85. |   0 | SELECT STATEMENT  |      | 77254 |  2036K|   101   (1)| 00:00:02 |  
    86. 

  
  
  86. |*  1 |  TABLE ACCESS FULL| T1   | 77254 |  2036K|   101   (1)| 00:00:02 |  
    87. 

  
  
  87. --------------------------------------------------------------------------  
    88. 

  
  
  88. Predicate Information (identified by operation id):  
    89. 

  
  
  89. ---------------------------------------------------  
    90. 

  
  
  90.    1 - filter("ID"=1)  
    91. 

  
  
  91.  
    92. 

  
  
  92. Statistics  
    93. 

  
  
  93. ----------------------------------------------------------  
    94. 

  
  
  94.           8  recursive calls  
    95. 

  
  
  95.           0  db block gets  
    96. 

  
  
  96.        5489  consistent gets  
    97. 

  
  
  97.           0  physical reads  
    98. 

  
  
  98.           0  redo size  
    99. 

  
  
  99.     2357825  bytes sent via SQL*Net to client  
    100. 

  
  
  100.       57081  bytes received via SQL*Net from client  
    101. 

  
  
  101.        5153  SQL*Net roundtrips to/from client  
    102. 

  
  
  102.           3  sorts (memory)  
    103. 

  
  
  103.           0  sorts (disk)  
    104. 

  
  
  104.       77267  rows processed  
    105. 

  
  
  105.  
    106. 

  
  
  106. SQL> set autot off  
    107. 

  
  
  107. SQL> exec dbms_stats.delete_column_stats('HR','T1','ID');  
    108. 

  
  
  108.  
    109. 

  
  
  109. PL/SQL procedure successfully completed.  
    110. 

  
  
  110.  
    111. 

  
  
  111. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist  
    112. 

  
  
  112. ograms where table_name='T1';  
    113. 

  
  
  113.  
    114. 

  
  
  114. TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE  
    115. 

  
  
  115. -------------------- -------------------- --------------- --------------  
    116. 

  
  
  116. T1                   OBJECT_NAME                        0     2.4504E+35  
    117. 

  
  
  117. T1                   OBJECT_NAME                        1     6.2963E+35  
    118. 

  
  
  118.  
    119. 

  
  
  119. SQL> set autot traceonly  
    120. 

  
  
  120. SQL> select * from t1 where id=99;  
    121. 

  
  
  121.  
    122. 

  
  
  122. Execution Plan  
    123. 

  
  
  123. ----------------------------------------------------------  
    124. 

  
  
  124. Plan hash value: 1111474805  
    125. 

  
  
  125. ---------------------------------------------------------------------------------------  
    126. 

  
  
  126.  
    127. 

  
  
  127. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
    128. 

  
  
  128.  
    129. 

  
  
  129. ---------------------------------------------------------------------------------------  
    130. 

  
  
  130.  
    131. 

  
  
  131. |   0 | SELECT STATEMENT            |         |   773 | 20871 |    77   (0)| 00:00:01 |  
    132. 

  
  
  132.  
    133. 

  
  
  133. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   773 | 20871 |    77   (0)| 00:00:01 |  
    134. 

  
  
  134.  
    135. 

  
  
  135. |*  2 |   INDEX RANGE SCAN          | I_T1_ID |   309 |       |    75   (0)| 00:00:01 |  
    136. 

  
  
  136.  
    137. 

  
  
  137. ---------------------------------------------------------------------------------------  
    138. 

  
  
  138. Predicate Information (identified by operation id):  
    139. 

  
  
  139. ---------------------------------------------------  
    140. 

  
  
  140.    2 - access("ID"=99)  
    141. 

  
  
  141.  
    142. 

  
  
  142. Statistics  
    143. 

  
  
  143. ----------------------------------------------------------  
    144. 

  
  
  144.           0  recursive calls  
    145. 

  
  
  145.           0  db block gets  
    146. 

  
  
  146.         351  consistent gets  
    147. 

  
  
  147.           0  physical reads  
    148. 

  
  
  148.           0  redo size  
    149. 

  
  
  149.         487  bytes sent via SQL*Net to client  
    150. 

  
  
  150.         420  bytes received via SQL*Net from client  
    151. 

  
  
  151.           2  SQL*Net roundtrips to/from client  
    152. 

  
  
  152.           0  sorts (memory)  
    153. 

  
  
  153.           0  sorts (disk)  
    154. 

  
  
  154.           1  rows processed  
    155. 

  
  
  155.  
    156. 

  
  
  156. SQL> select * from t1 where id=1;  
    157. 

  
  
  157. 77267 rows selected.  
    158. 

  
  
  158.  
    159. 

  
  
  159.  
    160. 

  
  
  160. Execution Plan  
    161. 

  
  
  161. ----------------------------------------------------------  
    162. 

  
  
  162. Plan hash value: 1111474805  
    163. 

  
  
  163. ---------------------------------------------------------------------------------------  
    164. 

  
  
  164.  
    165. 

  
  
  165. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
    166. 

  
  
  166.  
    167. 

  
  
  167. ---------------------------------------------------------------------------------------  
    168. 

  
  
  168.  
    169. 

  
  
  169. |   0 | SELECT STATEMENT            |         |   773 | 20871 |    77   (0)| 00:00:01 |  
    170. 

  
  
  170.  
    171. 

  
  
  171. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   773 | 20871 |    77   (0)| 00:00:01 |  
    172. 

  
  
  172.  
    173. 

  
  
  173. |*  2 |   INDEX RANGE SCAN          | I_T1_ID |   309 |       |    75   (0)| 00:00:01 |  
    174. 

  
  
  174.  
    175. 

  
  
  175. ---------------------------------------------------------------------------------------  
    176. 

  
  
  176. Predicate Information (identified by operation id):  
    177. 

  
  
  177. ---------------------------------------------------  
    178. 

  
  
  178.    2 - access("ID"=1)  
    179. 

  
  
  179.  
    180. 

  
  
  180. Statistics  
    181. 

  
  
  181. ----------------------------------------------------------  
    182. 

  
  
  182.           1  recursive calls  
    183. 

  
  
  183.           0  db block gets  
    184. 

  
  
  184.       10781  consistent gets  
    185. 

  
  
  185.           0  physical reads  
    186. 

  
  
  186.           0  redo size  
    187. 

  
  
  187.     3171208  bytes sent via SQL*Net to client  
    188. 

  
  
  188.       57081  bytes received via SQL*Net from client  
    189. 

  
  
  189.        5153  SQL*Net roundtrips to/from client  
    190. 

  
  
  190.           0  sorts (memory)  
    191. 

  
  
  191.           0  sorts (disk)  
    192. 

  
  
  192.       77267  rows processed  
    193. 

  
  
  193.  
    194. 

  
  
  194. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);  
    195. 

  
  
  195. PL/SQL procedure successfully completed.  
    196. 

  
  
  196.  
    197. 

  
  
  197. SQL> set autot off  
    198. 

  
  
  198. SQL> select table_name,column_name,endpoint_number,endpoint_value from user_hist  
    199. 

  
  
  199. ograms where table_name='T1';  
    200. 

  
  
  200.  
    201. 

  
  
  201. TABLE_NAME           COLUMN_NAME          ENDPOINT_NUMBER ENDPOINT_VALUE  
    202. 

  
  
  202. -------------------- -------------------- --------------- --------------  
    203. 

  
  
  203. T1                   ID                              5527             99  
    204. 

  
  
  204. T1                   ID                              5526              1  
    205. 

  
  
  205. T1                   OBJECT_NAME                        0     2.4504E+35  
    206. 

  
  
  206. T1                   OBJECT_NAME                        1     6.2963E+35 
    207.

三：动态采样，动态采样分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. 

  
  
  2. Table created.  
    3. 

  
  
  3.  
    4. 

  
  
  4. SQL> create index i_t2_id on t2 (object_id);  
    5. 

  
  
  5. Index created.  
    6. 

  
  
  6.  
    7. 

  
  
  7. SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab  
    8. 

  
  
  8. le_name='T2';  
    9. 

  
  
  9.  
    10. 

  
  
  10.   NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED  
    11. 

  
  
  11. ---------- ----------- ---------- ---------------  
    12. 

  
  
  12.  
    13. 

  
  
  13.  
    14. 

  
  
  14. SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in  
    15. 

  
  
  15. dexes where table_name='T2';  
    16. 

  
  
  16.  
    17. 

  
  
  17.     BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED  
    18. 

  
  
  18. ---------- ----------- ------------- ---------- -------------------  
    19. 

  
  
  19.          1         171         77265      77265 2012-06-07:13:55:04 
    20.

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


 
 
    

  
  
  1. SQL> set autot traceonly  
    2. 

  
  
  2. SQL> select * from t2 where object_id > 30;  
    3. 

  
  
  3. 77236 rows selected.  
    4. 

  
  
  4.  
    5. 

  
  
  5. Execution Plan  
    6. 

  
  
  6. ----------------------------------------------------------  
    7. 

  
  
  7. Plan hash value: 1513984157  
    8. 

  
  
  8. --------------------------------------------------------------------------  
    9. 

  
  
  9. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    10. 

  
  
  10. --------------------------------------------------------------------------  
    11. 

  
  
  11. |   0 | SELECT STATEMENT  |      | 58208 |  4490K|   105   (0)| 00:00:02 |  
    12. 

  
  
  12. |*  1 |  TABLE ACCESS FULL| T2   | 58208 |  4490K|   105   (0)| 00:00:02 |  
    13. 

  
  
  13. --------------------------------------------------------------------------  
    14. 

  
  
  14. Predicate Information (identified by operation id):  
    15. 

  
  
  15. ---------------------------------------------------  
    16. 

  
  
  16.    1 - filter("OBJECT_ID">30)  
    17. 

  
  
  17. Note  
    18. 

  
  
  18. -----  
    19. 

  
  
  19.    - dynamic sampling used for this statement (level=2)  
    20. 

  
  
  20.  
    21. 

  
  
  21. Statistics  
    22. 

  
  
  22. ----------------------------------------------------------  
    23. 

  
  
  23.          24  recursive calls  
    24. 

  
  
  24.           0  db block gets  
    25. 

  
  
  25.        5586  consistent gets  
    26. 

  
  
  26.           6  physical reads  
    27. 

  
  
  27.           0  redo size  
    28. 

  
  
  28.     3005346  bytes sent via SQL*Net to client  
    29. 

  
  
  29.       57059  bytes received via SQL*Net from client  
    30. 

  
  
  30.        5151  SQL*Net roundtrips to/from client  
    31. 

  
  
  31.           6  sorts (memory)  
    32. 

  
  
  32.           0  sorts (disk)  
    33. 

  
  
  33.       77236  rows processed 
    34.

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


 
 
    

  
  
  1. SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;  
    2. 

  
  
  2. 77236 rows selected.  
    3. 

  
  
  3.  
    4. 

  
  
  4. Execution Plan  
    5. 

  
  
  5. ----------------------------------------------------------  
    6. 

  
  
  6. Plan hash value: 3661687773  
    7. 

  
  
  7. ---------------------------------------------------------------------------------------  
    8. 

  
  
  8.  
    9. 

  
  
  9. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  
    10. 

  
  
  10.  
    11. 

  
  
  11. ---------------------------------------------------------------------------------------  
    12. 

  
  
  12.  
    13. 

  
  
  13. |   0 | SELECT STATEMENT            |         |  1556 |   120K|     8   (0)| 00:00:01 |  
    14. 

  
  
  14.  
    15. 

  
  
  15. |   1 |  TABLE ACCESS BY INDEX ROWID| T2      |  1556 |   120K|     8   (0)| 00:00:01 |  
    16. 

  
  
  16.  
    17. 

  
  
  17. |*  2 |   INDEX RANGE SCAN          | I_T2_ID |   280 |       |     3   (0)| 00:00:01 |  
    18. 

  
  
  18.  
    19. 

  
  
  19. ---------------------------------------------------------------------------------------  
    20. 

  
  
  20. Predicate Information (identified by operation id):  
    21. 

  
  
  21. ---------------------------------------------------  
    22. 

  
  
  22.    2 - access("OBJECT_ID">30)  
    23. 

  
  
  23.  
    24. 

  
  
  24. Statistics  
    25. 

  
  
  25. ----------------------------------------------------------  
    26. 

  
  
  26.           1  recursive calls  
    27. 

  
  
  27.           0  db block gets  
    28. 

  
  
  28.       10897  consistent gets  
    29. 

  
  
  29.         165  physical reads  
    30. 

  
  
  30.           0  redo size  
    31. 

  
  
  31.     3314234  bytes sent via SQL*Net to client  
    32. 

  
  
  32.       57059  bytes received via SQL*Net from client  
    33. 

  
  
  33.        5151  SQL*Net roundtrips to/from client  
    34. 

  
  
  34.           0  sorts (memory)  
    35. 

  
  
  35.           0  sorts (disk)  
    36. 

  
  
  36.       77236  rows processed 
    37.

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


 
 
    

  
  
  1. SQL> set autot off  
    2. 

  
  
  2. SQL> exec dbms_stats.gather_table_stats('HR','T2',CASCADE=>TRUE);  
    3. 

  
  
  3. PL/SQL procedure successfully completed.  
    4. 

  
  
  4.  
    5. 

  
  
  5. SQL> select num_rows,avg_row_len,blocks,last_analyzed from user_tables where tab  
    6. 

  
  
  6. le_name='T2';  
    7. 

  
  
  7.  
    8. 

  
  
  8.   NUM_ROWS AVG_ROW_LEN     BLOCKS LAST_ANALYZED  
    9. 

  
  
  9. ---------- ----------- ---------- -------------------  
    10. 

  
  
  10.      77266          29        381 2012-06-07:14:05:52  
    11. 

  
  
  11.  
    12. 

  
  
  12. SQL> select blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from user_in  
    13. 

  
  
  13. dexes where table_name='T2';  
    14. 

  
  
  14.  
    15. 

  
  
  15.     BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS LAST_ANALYZED  
    16. 

  
  
  16. ---------- ----------- ------------- ---------- -------------------  
    17. 

  
  
  17.          1         171         77265      77265 2012-06-07:14:05:52  
    18. 

  
  
  18.  
    19. 

  
  
  19. SQL> set autot traceonly  
    20. 

  
  
  20. SQL> select /*+ dynamic_sampling(t2 0) */ * from t2 where object_id > 30;  
    21. 

  
  
  21. 77236 rows selected.  
    22. 

  
  
  22.  
    23. 

  
  
  23. Execution Plan  
    24. 

  
  
  24. ----------------------------------------------------------  
    25. 

  
  
  25. Plan hash value: 1513984157  
    26. 

  
  
  26.  
    27. 

  
  
  27. --------------------------------------------------------------------------  
    28. 

  
  
  28. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
    29. 

  
  
  29. --------------------------------------------------------------------------  
    30. 

  
  
  30. |   0 | SELECT STATEMENT  |      | 77242 |  2187K|   106   (1)| 00:00:02 |  
    31. 

  
  
  31. |*  1 |  TABLE ACCESS FULL| T2   | 77242 |  2187K|   106   (1)| 00:00:02 |  
    32. 

  
  
  32. --------------------------------------------------------------------------  
    33. 

  
  
  33.  
    34. 

  
  
  34. Predicate Information (identified by operation id):  
    35. 

  
  
  35. ---------------------------------------------------  
    36. 

  
  
  36.    1 - filter("OBJECT_ID">30)  
    37. 

  
  
  37.  
    38. 

  
  
  38. Statistics  
    39. 

  
  
  39. ----------------------------------------------------------  
    40. 

  
  
  40.          12  recursive calls  
    41. 

  
  
  41.           0  db block gets  
    42. 

  
  
  42.        5516  consistent gets  
    43. 

  
  
  43.           0  physical reads  
    44. 

  
  
  44.           0  redo size  
    45. 

  
  
  45.     3005346  bytes sent via SQL*Net to client  
    46. 

  
  
  46.       57059  bytes received via SQL*Net from client  
    47. 

  
  
  47.        5151  SQL*Net roundtrips to/from client  
    48. 

  
  
  48.           5  sorts (memory)  
    49. 

  
  
  49.           0  sorts (disk)  
    50. 

  
  
  50.       77236  rows processed 
    51.

本文以《让oracle跑的更快》为指导，如有雷同，不胜荣幸！

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/892178如需转载请自行联系原作者


ylw6006

文章标签：
SQL
索引
关系型数据库
Oracle
Perl
数据库
关键词：
Oracle数据库一键上云执行计划
科技探索者
目录
相关文章
何雨晨
|
1月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
45 1
1941623231718325
|
6月前
|
监控 Oracle 关系型数据库
Oracle 12c的Adaptive执行计划：数据的“聪明导航员”
【4月更文挑战第19天】Oracle 12c的Adaptive执行计划是数据库查询的智能优化工具，能根据实际运行情况动态调整执行策略。它像一个聪明的导航系统，不仅生成初始执行计划，还能实时监控并适应统计信息和资源变化，例如自动切换索引或调整并行度。此外，它支持自适应连接和统计信息收集，提升处理复杂查询和变化数据环境的能力。数据管理员应充分利用这一特性来优化查询性能和用户体验。
1941623231718325
75 1
姚远OracleACE
|
SQL 缓存 监控
Oracle中如何生成有用的SQL 执行计划（译）
漫画戴夫·艾伦（Dave Allen）曾经讲过一个古老的笑话，一个旅行者问路人去某个城镇的路，路人只是说：“如果我是你，我就不会从这里开始。”
姚远OracleACE
162 0
姚远OracleACE
|
SQL 存储 缓存
获取和解读Oracle中SQL的执行计划（译文）
生成和显示 SQL 语句的执行计划是大多数 DBA、SQL 开发人员和性能专家的常见任务，因为它提供了 SQL 语句执行性能相关的信息。执行计划显示执行 SQL 语句的详细步骤，这些步骤表示为一组使用和生成行的数据库运算符。运算符的顺序和实现由查询优化器根据查询转换和物理优化技术来决定。
姚远OracleACE
322 0
小小工匠
|
SQL Oracle 关系型数据库
Oracle优化05-执行计划
Oracle优化05-执行计划
小小工匠
459 0
程序员石磊
|
SQL Oracle 关系型数据库
Oracle 性能优化技巧-获取真实执行计划
Oracle 性能优化技巧-获取真实执行计划
程序员石磊
276 0
Oracle 性能优化技巧-获取真实执行计划
TiAmoZhang
|
SQL 存储 Oracle
Oracle数据库 | SQL语句执行计划、语句跟踪与优化实例
Oracle数据库 | SQL语句执行计划、语句跟踪与优化实例
TiAmoZhang
336 0
lemotree
|
SQL Oracle 关系型数据库
Oracle - SPM固定执行计划
lemotree
314 0
lemotree
|
SQL
oracle-如何手动刷新执行计划plan
lemotree
739 0
lemotree
|
SQL
oracle-sqlplus-查看sql执行计划
lemotree
336 0