首页 / 数据库 / MySQL / 一次访问Oracle数据字典的优化

推荐阅读：DBA任务---确保统计信息准确性http://www.linuxidc.com/Linux/2011-09/42536.htm今晚上有位哥们QQ问我有没有什么SQL脚本用来收集统计信息的 几乎未加思考我就把上面的脚本原封不动的贴个了那位哥们后来那位哥们改写了SQL，说下面的SQL要跑200多秒，7千多W的逻辑读

1. SELECT  OWNER,  
2.            SEGMENT_NAME,  
3.            CASE  
4.              WHEN SIZE_GB < 0.5 THEN  
5.               30  
6.              WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN  
7.               20  
8.              WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN  
9.               10  
10.              WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN  
11.               5  
12.              WHEN SIZE_GB >= 10 THEN  
13.               1  
14.            END AS PERCENT,  
15.            2 AS DEGREE  
16.       FROM （SELECT OWNER,  
17.                    SEGMENT_NAME,  
18.                    SUM（BYTES / 1024 / 1024 / 1024） SIZE_GB  
19.               FROM DBA_SEGMENTS A  
20.              WHERE OWNER IN （"DATASYNC_PRC","OSS03","BAS01","DATASYNC_1","DATASYNC_2","OSS_CMS"）  
21.                AND SEGMENT_NAME IN   
22.                    （SELECT  DISTINCT TABLE_NAME  
23.                       FROM DBA_TAB_STATISTICS B  
24.                      WHERE （LAST_ANALYZED IS NULL OR STALE_STATS = "YES"）  
25.                        AND OWNER IN（"DATASYNC_PRC","OSS03","BAS01","DATASYNC_1","DATASYNC_2","OSS_CMS"））  
26.              GROUP BY OWNER, SEGMENT_NAME）;          

该SQL语句执行计划如下：

1. SQL> select * from table（dbms_xplan.display）;  
2.   
3. PLAN_TABLE_OUTPUT  
4. ---------------------------------------------------------------------------------------------------------------------------  
5. --------------------  
6. Plan hash value: 2028155339  
7.   
8. ----------------------------------------------------------------------------------------------------------------  
9. | Id  | Operation                                | Name                | Rows  | Bytes | Cost （%CPU）| Time     |  
10. ----------------------------------------------------------------------------------------------------------------  
11. |   0 | SELECT STATEMENT                         |                     | 22412 |  3852K|   113K  （2）| 00:05:55 |  
12. |   1 |  HASH GROUP BY                           |                     | 22412 |  3852K|   113K  （2）| 00:05:55 |  
13. |*  2 |   FILTER                                 |                     |       |       |            |          |  
14. |   3 |    VIEW                                  | SYS_DBA_SEGS        |  2837 |   487K|   110K  （2）| 00:05:46 |  
15. |   4 |     UNION-ALL                            |                     |       |       |            |          |  
16. |   5 |      NESTED LOOPS                        |                     |  1840 |   296K| 93690   （2）| 00:04:53 |  
17. |*  6 |       HASH JOIN                          |                     |  1779 |   272K| 93690   （2）| 00:04:53 |  
18. |   7 |        TABLE ACCESS FULL                 | TS$                 |   172 |  1548 |    54   （2）| 00:00:01 |  
19. |   8 |        NESTED LOOPS                      |                     |  1779 |   257K| 93635   （2）| 00:04:53 |  
20. |*  9 |         HASH JOIN                        |                     |  6571 |   757K| 80450   （3）| 00:04:12 |  
21. |* 10 |          FILTER                          |                     |       |       |            |          |  
22. |* 11 |           HASH JOIN RIGHT OUTER          |                     |  7221 |   423K| 10278   （6）| 00:00:33 |  
23. |  12 |            TABLE ACCESS FULL             | USER$               |  3200 | 54400 |    27   （0）| 00:00:01 |  
24. |  13 |            TABLE ACCESS FULL             | OBJ$                |  3854K|   158M| 10133   （5）| 00:00:32 |  
25. |  14 |          VIEW                            | SYS_OBJECTS         |  3507K|   194M| 70065   （2）| 00:03:40 |  
26. |  15 |           UNION-ALL                      |                     |       |       |            |          |  
27. |* 16 |            TABLE ACCESS FULL             | TAB$                |   210K|  5548K| 15995   （2）| 00:00:51 |  
28. |  17 |            TABLE ACCESS FULL             | TABPART$            |   148K|  2895K|   727   （3）| 00:00:03 |  
29. |  18 |            TABLE ACCESS FULL             | CLU$                |    10 |   150 | 14128   （2）| 00:00:45 |  
30. |* 19 |            TABLE ACCESS FULL             | IND$                |   750K|    16M| 16045   （2）| 00:00:51 |  
31. |  20 |            TABLE ACCESS FULL             | INDPART$            |   620K|    11M|  2424   （4）| 00:00:08 |  
32. |* 21 |            TABLE ACCESS FULL             | LOB$                |  2273 | 50006 | 15929   （2）| 00:00:50 |  
33. |  22 |            TABLE ACCESS FULL             | TABSUBPART$         |   269K|  5261K|   932   （4）| 00:00:03 |  
34. |  23 |            TABLE ACCESS FULL             | INDSUBPART$         |  1503K|    28M|  3868   （5）| 00:00:13 |  
35. |  24 |            TABLE ACCESS FULL             | LOBFRAG$            |  2977 | 65494 |    17   （0）| 00:00:01 |  
36. |* 25 |         TABLE ACCESS CLUSTER             | SEG$                |     1 |    30 |     2   （0）| 00:00:01 |  
37. |* 26 |          INDEX UNIQUE SCAN               | I_FILE#_BLOCK#      |     1 |       |     1   （0）| 00:00:01 |  
38. |* 27 |       INDEX UNIQUE SCAN                  | I_FILE2             |     1 |     8 |     0   （0）| 00:00:01 |  
39. |  28 |      NESTED LOOPS                        |                     |     1 |   109 |  2274   （1）| 00:00:08 |  
40. |  29 |       NESTED LOOPS                       |                     |     1 |   101 |  2274   （1）| 00:00:08 |  
41. |* 30 |        FILTER                            |                     |       |       |            |          |  
42. |* 31 |         HASH JOIN OUTER                  |                     |     1 |    92 |  2273   （1）| 00:00:08 |  
43. |  32 |          NESTED LOOPS                    |                     |   568 | 42600 |  2245   （1）| 00:00:08 |  
44. |* 33 |           TABLE ACCESS FULL              | UNDO$               |  1116 | 45756 |     5   （0）| 00:00:01 |  
45. |* 34 |           TABLE ACCESS CLUSTER           | SEG$                |     1 |    34 |     2   （0）| 00:00:01 |  
46. |* 35 |            INDEX UNIQUE SCAN             | I_FILE#_BLOCK#      |     1 |       |     1   （0）| 00:00:01 |  
47. |  36 |          TABLE ACCESS FULL               | USER$               |  3200 | 54400 |    27   （0）| 00:00:01 |  
48. |  37 |        TABLE ACCESS CLUSTER              | TS$                 |     1 |     9 |     1   （0）| 00:00:01 |  
49. |* 38 |         INDEX UNIQUE SCAN                | I_TS#               |     1 |       |     0   （0）| 00:00:01 |  
50. |* 39 |       INDEX UNIQUE SCAN                  | I_FILE2             |     1 |     8 |     0   （0）| 00:00:01 |  
51. |* 40 |      HASH JOIN                           |                     |   996 | 77688 | 14672   （1）| 00:00:46 |  
52. |  41 |       TABLE ACCESS FULL                  | TS$                 |   172 |  1548 |    54   （2）| 00:00:01 |  
53. |* 42 |       FILTER                             |                     |       |       |            |          |  
54. |* 43 |        HASH JOIN RIGHT OUTER             |                     |   996 | 68724 | 14618   （1）| 00:00:46 |  
55. |  44 |         TABLE ACCESS FULL                | USER$               |  3200 | 54400 |    27   （0）| 00:00:01 |  
56. |  45 |         NESTED LOOPS                     |                     |   531K|    26M| 14574   （1）| 00:00:46 |  
57. |  46 |          TABLE ACCESS FULL               | FILE$               |   872 | 10464 |     3   （0）| 00:00:01 |  
58. |* 47 |          TABLE ACCESS CLUSTER            | SEG$                |   610 | 24400 |    23   （0）| 00:00:01 |  
59. |* 48 |           INDEX RANGE SCAN               | I_FILE#_BLOCK#      |     1 |       |     2   （0）| 00:00:01 |  
60. |  49 |    VIEW                                  | DBA_TAB_STATISTICS  |    42 |  1932 |  2828   （6）| 00:00:09 |  
61. |  50 |     UNION-ALL                            |                     |       |       |            |          |  
62. |* 51 |      FILTER                              |                     |       |       |            |          |  
63. |  52 |       NESTED LOOPS OUTER                 |                     |     1 |   115 |    23   （0）| 00:00:01 |  
64. |  53 |        NESTED LOOPS                      |                     |     1 |    97 |    21   （0）| 00:00:01 |  
65. |  54 |         NESTED LOOPS OUTER               |                     |     1 |    74 |    19   （0）| 00:00:01 |  
66. |  55 |          NESTED LOOPS                    |                     |     1 |    67 |    19   （0）| 00:00:01 |  
67. |  56 |           INLIST ITERATOR                |                     |       |       |            |          |  
68. |  57 |            TABLE ACCESS BY INDEX ROWID   | USER$               |     6 |   102 |     7   （0）| 00:00:01 |  
69. |* 58 |             INDEX UNIQUE SCAN            | I_USER1             |     6 |       |     2   （0）| 00:00:01 |  
70. |* 59 |           INDEX RANGE SCAN               | I_OBJ2              |     1 |    50 |     2   （0）| 00:00:01 |  
71. |* 60 |          INDEX UNIQUE SCAN               | I_TAB_STATS  
72. |* 61 |         TABLE ACCESS CLUSTER             | TAB$                |     1 |    23 |     2   （0）| 00:00:01 |  
73. |* 62 |          INDEX UNIQUE SCAN               | I_OBJ#              |     1 |       |     1   （0）| 00:00:01 |  
74. |  63 |        TABLE ACCESS BY INDEX ROWID       | MON_MODS_ALL$       |     1 |    18 |     2   （0）| 00:00:01 |  
75. |* 64 |         INDEX UNIQUE SCAN                | I_MON_MODS_ALL  

SQL> select * from table（dbms_xplan.display）;PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2028155339----------------------------------------------------------------------------------------------------------------| Id| Operation| Name| Rows| Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 22412 |3852K| 113K（2）| 00:05:55 || 1 |HASH GROUP BY | | 22412 |3852K| 113K（2）| 00:05:55 ||*2 | FILTER | | | |||| 3 |VIEW| SYS_DBA_SEGS|2837 | 487K| 110K（2）| 00:05:46 || 4 | UNION-ALL| | | |||| 5 |NESTED LOOPS| |1840 | 296K| 93690 （2）| 00:04:53 ||*6 | HASH JOIN| |1779 | 272K| 93690 （2）| 00:04:53 || 7 |TABLE ACCESS FULL | TS$ | 172 |1548 |54 （2）| 00:00:01 || 8 |NESTED LOOPS| |1779 | 257K| 93635 （2）| 00:04:53 ||*9 | HASH JOIN| |6571 | 757K| 80450 （3）| 00:04:12 ||* 10 |FILTER| | | ||||* 11 | HASH JOIN RIGHT OUTER| |7221 | 423K| 10278 （6）| 00:00:33 ||12 |TABLE ACCESS FULL | USER$ |3200 | 54400 |27 （0）| 00:00:01 ||13 |TABLE ACCESS FULL | OBJ$|3854K| 158M| 10133 （5）| 00:00:32 ||14 |VIEW| SYS_OBJECTS |3507K| 194M| 70065 （2）| 00:03:40 ||15 | UNION-ALL| | | ||||* 16 |TABLE ACCESS FULL | TAB$| 210K|5548K| 15995 （2）| 00:00:51 ||17 |TABLE ACCESS FULL | TABPART$| 148K|2895K| 727 （3）| 00:00:03 ||18 |TABLE ACCESS FULL | CLU$|10 | 150 | 14128 （2）| 00:00:45 ||* 19 |TABLE ACCESS FULL | IND$| 750K|16M| 16045 （2）| 00:00:51 ||20 |TABLE ACCESS FULL | INDPART$| 620K|11M|2424 （4）| 00:00:08 ||* 21 |TABLE ACCESS FULL | LOB$|2273 | 50006 | 15929 （2）| 00:00:50 ||22 |TABLE ACCESS FULL | TABSUBPART$ | 269K|5261K| 932 （4）| 00:00:03 ||23 |TABLE ACCESS FULL | INDSUBPART$ |1503K|28M|3868 （5）| 00:00:13 ||24 |TABLE ACCESS FULL | LOBFRAG$|2977 | 65494 |17 （0）| 00:00:01 ||* 25 | TABLE ACCESS CLUSTER | SEG$| 1 |30 | 2 （0）| 00:00:01 ||* 26 |INDEX UNIQUE SCAN | I_FILE#_BLOCK#| 1 | | 1 （0）| 00:00:01 ||* 27 | INDEX UNIQUE SCAN| I_FILE2 | 1 | 8 | 0 （0）| 00:00:01 ||28 |NESTED LOOPS| | 1 | 109 |2274 （1）| 00:00:08 ||29 | NESTED LOOPS | | 1 | 101 |2274 （1）| 00:00:08 ||* 30 |FILTER| | | ||||* 31 | HASH JOIN OUTER| | 1 |92 |2273 （1）| 00:00:08 ||32 |NESTED LOOPS| | 568 | 42600 |2245 （1）| 00:00:08 ||* 33 | TABLE ACCESS FULL| UNDO$ |1116 | 45756 | 5 （0）| 00:00:01 ||* 34 | TABLE ACCESS CLUSTER | SEG$| 1 |34 | 2 （0）| 00:00:01 ||* 35 |INDEX UNIQUE SCAN | I_FILE#_BLOCK#| 1 | | 1 （0）| 00:00:01 ||36 |TABLE ACCESS FULL | USER$ |3200 | 54400 |27 （0）| 00:00:01 ||37 |TABLE ACCESS CLUSTER| TS$ | 1 | 9 | 1 （0）| 00:00:01 ||* 38 | INDEX UNIQUE SCAN| I_TS# | 1 | | 0 （0）| 00:00:01 ||* 39 | INDEX UNIQUE SCAN| I_FILE2 | 1 | 8 | 0 （0）| 00:00:01 ||* 40 |HASH JOIN | | 996 | 77688 | 14672 （1）| 00:00:46 ||41 | TABLE ACCESS FULL| TS$ | 172 |1548 |54 （2）| 00:00:01 ||* 42 | FILTER | | | ||||* 43 |HASH JOIN RIGHT OUTER | | 996 | 68724 | 14618 （1）| 00:00:46 ||44 | TABLE ACCESS FULL| USER$ |3200 | 54400 |27 （0）| 00:00:01 ||45 | NESTED LOOPS | | 531K|26M| 14574 （1）| 00:00:46 ||46 |TABLE ACCESS FULL | FILE$ | 872 | 10464 | 3 （0）| 00:00:01 ||* 47 |TABLE ACCESS CLUSTER| SEG$| 610 | 24400 |23 （0）| 00:00:01 ||* 48 | INDEX RANGE SCAN | I_FILE#_BLOCK#| 1 | | 2 （0）| 00:00:01 ||49 |VIEW| DBA_TAB_STATISTICS|42 |1932 |2828 （6）| 00:00:09 ||50 | UNION-ALL| | | ||||* 51 |FILTER| | | ||||52 | NESTED LOOPS OUTER | | 1 | 115 |23 （0）| 00:00:01 ||53 |NESTED LOOPS| | 1 |97 |21 （0）| 00:00:01 ||54 | NESTED LOOPS OUTER | | 1 |74 |19 （0）| 00:00:01 ||55 |NESTED LOOPS| | 1 |67 |19 （0）| 00:00:01 ||56 | INLIST ITERATOR| | | ||||57 |TABLE ACCESS BY INDEX ROWID | USER$ | 6 | 102 | 7 （0）| 00:00:01 ||* 58 | INDEX UNIQUE SCAN| I_USER1 | 6 | | 2 （0）| 00:00:01 ||* 59 | INDEX RANGE SCAN | I_OBJ2| 1 |50 | 2 （0）| 00:00:01 ||* 60 |INDEX UNIQUE SCAN | I_TAB_STATS|* 61 | TABLE ACCESS CLUSTER | TAB$| 1 |23 | 2 （0）| 00:00:01 ||* 62 |INDEX UNIQUE SCAN | I_OBJ#| 1 | | 1 （0）| 00:00:01 ||63 |TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 |18 | 2 （0）| 00:00:01 ||* 64 | INDEX UNIQUE SCAN| I_MON_MODS_ALL view plaincopy to clipboardprint？

1. |  65 |      NESTED LOOPS                        |                     |     1 |   147 |   775   （6）| 00:00:03 |  
2. |* 66 |       FILTER                             |                     |       |       |            |          |  
3. |  67 |        NESTED LOOPS OUTER                |                     |     1 |   141 |   773   （6）| 00:00:03 |  
4. |* 68 |         HASH JOIN                        |                     |     1 |   123 |   771   （6）| 00:00:03 |  
5. |  69 |          NESTED LOOPS OUTER              |                     |     1 |    65 |    19   （0）| 00:00:01 |  
6. |  70 |           NESTED LOOPS                   |                     |     1 |    58 |    19   （0）| 00:00:01 |  
7. |  71 |            INLIST ITERATOR               |                     |       |       |            |          |  
8. |  72 |             TABLE ACCESS BY INDEX ROWID  | USER$               |     6 |   102 |     7   （0）| 00:00:01 |  
9. |* 73 |              INDEX UNIQUE SCAN           | I_USER1             |     6 |       |     2   （0）| 00:00:01 |  
10. |* 74 |            INDEX RANGE SCAN              | I_OBJ2              |     1 |    41 |     2   （0）| 00:00:01 |  
11. |* 75 |           INDEX UNIQUE SCAN              | I_TAB_STATS  

  

1. |  76 |          VIEW                            | TABPARTV$           |   148K|  8397K|   747   （6）| 00:00:03 |  
2. |* 77 |           TABLE ACCESS FULL              | TABPART$            |   148K|  4632K|   747   （6）| 00:00:03 |  
3. |  78 |         TABLE ACCESS BY INDEX ROWID      | MON_MODS_ALL$       |     1 |    18 |     2   （0）| 00:00:01 |  
4. |* 79 |          INDEX UNIQUE SCAN               | I_MON_MODS_ALL  

  

1. |  80 |       TABLE ACCESS CLUSTER               | TAB$                |    21 |   126 |     2   （0）| 00:00:01 |  
2. |* 81 |        INDEX UNIQUE SCAN                 | I_OBJ#              |     1 |       |     1   （0）| 00:00:01 |  
3. |  82 |      NESTED LOOPS                        |                     |     1 |   140 |   737   （4）| 00:00:03 |  
4. |  83 |       NESTED LOOPS                       |                     |     1 |   134 |   735   （4）| 00:00:03 |  
5. |  84 |        NESTED LOOPS                      |                     |     1 |   117 |   734   （4）| 00:00:03 |  
6. |  85 |         NESTED LOOPS OUTER               |                     |     1 |    76 |   731   （4）| 00:00:03 |  
7. |  86 |          VIEW                            | TABPARTV$           |     1 |    58 |   729   （4）| 00:00:03 |  
8. |* 87 |           TABLE ACCESS FULL              | TABPART$            |     1 |    32 |   729   （4）| 00:00:03 |  
9. |  88 |          TABLE ACCESS BY INDEX ROWID     | MON_MODS_ALL$       |     1 |    18 |     2   （0）| 00:00:01 |  
10. |* 89 |           INDEX UNIQUE SCAN              | I_MON_MODS_ALL  

  

1. |* 90 |         TABLE ACCESS BY INDEX ROWID      | OBJ$                |     1 |    41 |     3   （0）| 00:00:01 |  
2. |* 91 |          INDEX RANGE SCAN                | I_OBJ1              |     1 |       |     2   （0）| 00:00:01 |  
3. |* 92 |        TABLE ACCESS CLUSTER              | USER$               |     1 |    17 |     1   （0）| 00:00:01 |  
4. |* 93 |         INDEX UNIQUE SCAN                | I_USER#             |     1 |       |     0   （0）| 00:00:01 |  
5. |  94 |       TABLE ACCESS CLUSTER               | TAB$                |    21 |   126 |     2   （0）| 00:00:01 |  
6. |* 95 |        INDEX UNIQUE SCAN                 | I_OBJ#              |     1 |       |     1   （0）| 00:00:01 |  
7. |  96 |      NESTED LOOPS                        |                     |     3 |   360 |   146   （5）| 00:00:01 |  
8. |* 97 |       FILTER                             |                     |       |       |            |          |  
9. |  98 |        NESTED LOOPS OUTER                |                     |     1 |   114 |   144   （5）| 00:00:01 |  
10. |* 99 |         HASH JOIN                        |                     |     1 |    96 |   142   （5）| 00:00:01 |  
11. | 100 |          NESTED LOOPS OUTER              |                     |     1 |    65 |    19   （0）| 00:00:01 |  
12. | 101 |           NESTED LOOPS                   |                     |     1 |    58 |    19   （0）| 00:00:01 |  
13. | 102 |            INLIST ITERATOR               |                     |       |       |            |          |  
14. | 103 |             TABLE ACCESS BY INDEX ROWID  | USER$               |     6 |   102 |     7   （0）| 00:00:01 |  
15. |*104 |              INDEX UNIQUE SCAN           | I_USER1             |     6 |       |     2   （0）| 00:00:01 |  
16. |*105 |            INDEX RANGE SCAN              | I_OBJ2              |     1 |    41 |     2   （0）| 00:00:01 |  
17. |*106 |           INDEX UNIQUE SCAN              | I_TAB_STATS  

  

1. | 107 |          VIEW                            | TABCOMPARTV$        | 19453 |   588K|   122   （5）| 00:00:01 |  
2. | 108 |           TABLE ACCESS FULL              | TABCOMPART$         | 19453 |   398K|   122   （5）| 00:00:01 |  
3. | 109 |         TABLE ACCESS BY INDEX ROWID      | MON_MODS_ALL$       |     1 |    18 |     2   （0）| 00:00:01 |  
4. |*110 |          INDEX UNIQUE SCAN               | I_MON_MODS_ALL  

  

1. | 111 |       TABLE ACCESS CLUSTER               | TAB$                |   160 |   960 |     2   （0）| 00:00:01 |  
2. |*112 |        INDEX UNIQUE SCAN                 | I_OBJ#              |     1 |       |     1   （0）| 00:00:01 |  
3. | 113 |      NESTED LOOPS                        |                     |    35 |  5810 |  1146   （8）| 00:00:04 |  
4. | 114 |       NESTED LOOPS OUTER                 |                     |     1 |   160 |  1144   （8）| 00:00:04 |  
5. | 115 |        NESTED LOOPS                      |                     |     1 |   153 |  1144