一次访问Oracle数据字典的优化

一次访问Oracle数据字典的优化

推荐阅读：DBA任务---确保统计信息准确性

今晚上有位哥们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   (8)| 00:00:04 |  
6. |*116 |         FILTER                           |                     |       |       |            |          |  
7. | 117 |          NESTED LOOPS OUTER              |                     |     1 |   147 |  1142   (8)| 00:00:04 |  
8. |*118 |           HASH JOIN                      |                     |    11 |  1419 |  1120   (8)| 00:00:04 |  
9. |*119 |            HASH JOIN                     |                     |     1 |    70 |   142   (5)| 00:00:01 |  
10. | 120 |             NESTED LOOPS                 |                     |     1 |    58 |    19   (0)| 00:00:01 |  
11. | 121 |              INLIST ITERATOR             |                     |       |       |            |          |  
12. | 122 |               TABLE ACCESS BY INDEX ROWID| USER$               |     6 |   102 |     7   (0)| 00:00:01 |  
13. |*123 |                INDEX UNIQUE SCAN         | I_USER1             |     6 |       |     2   (0)| 00:00:01 |  
14. |*124 |              INDEX RANGE SCAN            | I_OBJ2              |     1 |    41 |     2   (0)| 00:00:01 |  
15. | 125 |             VIEW                         | TABCOMPARTV$        | 19453 |   227K|   122   (5)| 00:00:01 |  
16. | 126 |              TABLE ACCESS FULL           | TABCOMPART$         | 19453 |   227K|   122   (5)| 00:00:01 |  
17. | 127 |            VIEW                          | TABSUBPARTV$        |   269K|    15M|   969   (8)| 00:00:04 |  
18. |*128 |             TABLE ACCESS FULL            | TABSUBPART$         |   269K|  8681K|   969   (8)| 00:00:04 |  
19. | 129 |           TABLE ACCESS BY INDEX ROWID    | MON_MODS_ALL$       |     1 |    18 |     2   (0)| 00:00:01 |  
20. |*130 |            INDEX UNIQUE SCAN             | I_MON_MODS_ALL  

 

 

1. |*131 |         INDEX RANGE SCAN                 | I_OBJ1              |     1 |     6 |     2   (0)| 00:00:01 |  
2. |*132 |        INDEX UNIQUE SCAN                 | I_TAB_STATS  

 

 

1. | 133 |       TABLE ACCESS CLUSTER               | TAB$                |   160 |   960 |     2   (0)| 00:00:01 |  
2. |*134 |        INDEX UNIQUE SCAN                 | I_OBJ#              |     1 |       |     1   (0)| 00:00:01 |  
3. |*135 |      FILTER                              |                     |       |       |            |          |  
4. |*136 |       FILTER                             |                     |       |       |            |          |  
5. | 137 |        NESTED LOOPS OUTER                |                     |     1 |    51 |     3  (34)| 00:00:01 |  
6. | 138 |         NESTED LOOPS OUTER               |                     |     1 |    36 |     2  (50)| 00:00:01 |  
7. |*139 |          FIXED TABLE FULL                | X$KQFTA             |     1 |    21 |     1 (100)| 00:00:01 |  
8. |*140 |          TABLE ACCESS BY INDEX ROWID     | FIXED_OBJ$          |     1 |    15 |     1   (0)| 00:00:01 |  
9. |*141 |           INDEX UNIQUE SCAN              | I_FIXED_OBJ  

| 133 | TABLE ACCESS CLUSTER | TAB$ | 160 | 960 | 2 (0)| 00:00:01 | |*134 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 | |*135 | FILTER | | | | | | |*136 | FILTER | | | | | | | 137 | NESTED LOOPS OUTER | | 1 | 51 | 3 (34)| 00:00:01 | | 138 | NESTED LOOPS OUTER | | 1 | 36 | 2 (50)| 00:00:01 | |*139 | FIXED TABLE FULL | X$KQFTA | 1 | 21 | 1 (100)| 00:00:01 | |*140 | TABLE ACCESS BY INDEX ROWID | FIXED_OBJ$ | 1 | 15 | 1 (0)| 00:00:01 | |*141 | INDEX UNIQUE SCAN | I_FIXED_OBJ

view plaincopy to clipboardprint?

1. | 142 |         TABLE ACCESS BY INDEX ROWID      | TAB_STATS$          |     1 |    15 |     1   (0)| 00:00:01 |  
2. |*143 |          INDEX UNIQUE SCAN               | I_TAB_STATS  

 

 

1. ----------------------------------------------------------------------------------------------------------------  
2.   
3. Predicate Information (identified by operation id):  
4. ---------------------------------------------------  
5.   
6.    2 - filter( EXISTS (SELECT 0 FROM  ( (SELECT "U"."NAME" "OWNER","O"."NAME" "TABLE_NAME",NULL  
7.               "PARTITION_NAME",NULL "PARTITION_POSITION",NULL "SUBPARTITION_NAME",NULL  
8.               "SUBPARTITION_POSITION",'TABLE' "OBJECT_TYPE","T"."ROWCNT"  
9.               "NUM_ROWS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."BLKCNT",TO_NUMBER(NULL))  
10.               "BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."EMPCNT",TO_NUMBER(NULL))  
11.               "EMPTY_BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."AVGSPC",TO_NUMBER(NULL))  
12.               "AVG_SPACE","T"."CHNCNT" "CHAIN_CNT","T"."AVGRLN" "AVG_ROW_LEN","T"."AVGSPC_FLB"  
13.               "AVG_SPACE_FREELIST_BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."FLBCNT",TO_NUMBER(NULL))  
14.               "NUM_FREELIST_BLOCKS","TS"."CACHEDBLK" "AVG_CACHED_BLOCKS","TS"."CACHEHIT"  
15.               "AVG_CACHE_HIT_RATIO","T"."SAMPLESIZE" "SAMPLE_SIZE","T"."ANALYZETIME"  
16.               "LAST_ANALYZED",DECODE(BITAND("T"."FLAGS",512),0,'NO','YES')  
17.               "GLOBAL_STATS",DECODE(BITAND("T"."FLAGS",256),0,'NO','YES')  
18.               "USER_STATS",DECODE(BITAND("T"."TRIGFLAG",67108864)+BITAND("T"."TRIGFLAG",134217728),0,NULL,67108864,'DA  
19.               TA',134217728,'CACHE','ALL') "STATTYPE_LOCKED",CASE  WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN  
20.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN  
21.               T',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END  "STALE_STATS" FROM  
22.               "SYS"."MON_MODS_ALL{1}quot; "M","SYS"."TAB_STATS{1}quot; "TS","SYS"."TAB{1}quot; "T","SYS"."OBJ{1}quot; "O","SYS"."USER  

 

 

1.               WHERE ("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
2.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') AND "O"."NAMESPACE"=1 AND  
3.               "O"."NAME"=:B1 AND "O"."OWNER#"="U"."USER#" AND "O"."SUBNAME" IS NULL AND "O"."REMOTEOWNER" IS NULL AND  
4.               "O"."LINKNAME" IS NULL AND "O"."OBJ#"="T"."OBJ#" AND BITAND("T"."PROPERTY",1)=0 AND  
5.               "O"."OBJ#"="TS"."OBJ#"(+) AND "T"."OBJ#"="M"."OBJ#"(+) AND ("T"."ANALYZETIME" IS NULL OR CASE  WHEN  
6.               "T"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMB  
7.               ER("DBMS_STATS"."GET_PREFS"('STALE_PERCENT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1)  
8.               THEN 'YES' ELSE 'NO' END ='YES')) UNION ALL  (SELECT "U"."NAME" "OWNER","O"."NAME"  
9.               "TABLE_NAME","O"."SUBNAME" "PARTITION_NAME","TP"."PART#" "PARTITION_POSITION",NULL  
10.               "SUBPARTITION_NAME",NULL "SUBPARTITION_POSITION",'PARTITION' "OBJECT_TYPE","TP"."ROWCNT"  
11.               "NUM_ROWS","TP"."BLKCNT" "BLOCKS","TP"."EMPCNT" "EMPTY_BLOCKS","TP"."AVGSPC" "AVG_SPACE","TP"."CHNCNT"  
12.               "CHAIN_CNT","TP"."AVGRLN" "AVG_ROW_LEN",TO_NUMBER(NULL) "AVG_SPACE_FREELIST_BLOCKS",TO_NUMBER(NULL)  
13.               "NUM_FREELIST_BLOCKS","TS"."CACHEDBLK" "AVG_CACHED_BLOCKS","TS"."CACHEHIT"  
14.               "AVG_CACHE_HIT_RATIO","TP"."SAMPLESIZE" "SAMPLE_SIZE","TP"."ANALYZETIME"  
15.               "LAST_ANALYZED",DECODE(BITAND("TP"."FLAGS",16),0,'NO','YES')  
16.               "GLOBAL_STATS",DECODE(BITAND("TP"."FLAGS",8),0,'NO','YES')  
17.               "USER_STATS",DECODE(DECODE(BITAND("TAB"."TRIGFLAG",67108864)+BITAND("TP"."FLAGS",32),0,0,1)+DECODE(BITAN  
18.               D("TAB"."TRIGFLAG",134217728)+BITAND("TP"."FLAGS",64),0,0,2),0,NULL,1,'DATA',2,'CACHE','ALL')  
19.               "STATTYPE_LOCKED",CASE  WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN  
20.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE  
21.               NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END  "STALE_STATS"  
22.               FROM "SYS"."MON_MODS_ALL{1}quot; "M","SYS"."TAB{1}quot; "TAB","SYS"."TAB_STATS{1}quot; "TS", (SELECT "OBJ#"  
23.               "OBJ#","DATAOBJ#" "DATAOBJ#","BO#" "BO#",ROW_NUMBER() OVER ( PARTITION BY "BO#" ORDER BY "PART#")  
24.               "PART#","HIBOUNDLEN" "HIBOUNDLEN","HIBOUNDVAL" "HIBOUNDVAL","TS#" "TS#","FILE#" "FILE#","BLOCK#"  
25.               "BLOCK#","PCTFREE{1}quot; "PCTFREE{1}quot;,"PCTUSED{1}quot; "PCTUSED{1}quot;,"INITRANS" "INITRANS","MAXTRANS"  
26.               "MAXTRANS","FLAGS" "FLAGS","ANALYZETIME" "ANALYZETIME","SAMPLESIZE" "SAMPLESIZE","ROWCNT"  
27.               "ROWCNT","BLKCNT" "BLKCNT","EMPCNT" "EMPCNT","AVGSPC" "AVGSPC","CHNCNT" "CHNCNT","AVGRLN"  
28.               "AVGRLN","PART#" "PHYPART#" FROM SYS."TABPART{1}quot; "TABPART{1}quot; WHERE "FILE#">0 AND "BLOCK#">0)  
29.               "TP","SYS"."OBJ{1}quot; "O","SYS"."USER{1}quot; "U" WHERE ("U"."NAME"='BAS01' OR "U"."NAME"='DATAS)  
30.    6 - access("S"."TS#"="TS"."TS#")  
31.    9 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID")  
32.   10 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR  
33.               NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR  
34.               NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS')  
35.   11 - access("O"."OWNER#"="U"."USER#"(+))  
36.   16 - filter(BITAND("T"."PROPERTY",1024)=0)  
37.   19 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR  
38.               "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9)  
39.   21 - filter(BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128)  
40.   25 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID")  
41.   26 - access("S"."TS#"="SO"."TS_NUMBER" AND "S"."FILE#"="SO"."HEADER_FILE" AND  
42.               "S"."BLOCK#"="SO"."HEADER_BLOCK")  
43.   27 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")  
44.   30 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR  
45.               NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR  
46.               NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS')  
47.   31 - access("S"."USER#"="U"."USER#"(+))  
48.   33 - filter("UN"."STATUS{1}quot;<>1)  
49.   34 - filter("S"."TYPE#"=1 OR "S"."TYPE#"=10)  
50.   35 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#")  
51.   38 - access("S"."TS#"="TS"."TS#")  
52.   39 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#")  
53.   40 - access("S"."TS#"="TS"."TS#")  
54.   42 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR  
55.               NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR  
56.               NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS')  
57.   43 - access("S"."USER#"="U"."USER#"(+))  
58.   47 - filter("S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>10 AND  
59.               "S"."TYPE#"<>1)  
60.   48 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")  
61.   51 - filter("T"."ANALYZETIME" IS NULL OR CASE  WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN  
62.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN  
63.               T',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')  
64.   58 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
65.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS')  
66.   59 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER"  
67.               IS NULL AND "O"."LINKNAME" IS NULL AND "O"."SUBNAME" IS NULL)  
68.        filter("O"."SUBNAME" IS NULL AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)  
69.   60 - access("O"."OBJ#"="TS"."OBJ#"(+))  
70.   61 - filter(BITAND("T"."PROPERTY",1)=0)  
71.   62 - access("O"."OBJ#"="T"."OBJ#")  
72.   64 - access("T"."OBJ#"="M"."OBJ#"(+))  
73.   66 - filter("TP"."ANALYZETIME" IS NULL OR CASE  WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN  
74.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE  
75.               NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')  
76.   68 - access("O"."OBJ#"="TP"."OBJ#")  
77.   73 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
78.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS')  
79.   74 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER"  
80.               IS NULL AND "O"."LINKNAME" IS NULL)  
81.        filter("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)  
82.   75 - access("O"."OBJ#"="TS"."OBJ#"(+))  
83.   77 - filter("FILE#">0 AND "BLOCK#">0)  
84.   79 - access("TP"."OBJ#"="M"."OBJ#"(+))  
85.   81 - access("TP"."BO#"="TAB"."OBJ#")  
86.   87 - filter("BLOCK#"=0 AND "FILE#"=0)  
87.   89 - access("TP"."OBJ#"="M"."OBJ#"(+))  
88.   90 - filter("O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS  
89.               NULL)  
90.   91 - access("O"."OBJ#"="TP"."OBJ#")  
91.   92 - filter(("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
92.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') AND ("TP"."ANALYZETIME" IS  
93.               NULL OR CASE  WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN  
94.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE  
95.               NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES'))  
96.   93 - access("O"."OWNER#"="U"."USER#")  
97.   95 - access("TP"."BO#"="TAB"."OBJ#")  
98.   97 - filter("TCP"."ANALYZETIME" IS NULL OR CASE  WHEN "TCP"."ANALYZETIME" IS NULL THEN NULL WHEN  
99.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TCP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERC  
100.               ENT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')  
101.   99 - access("O"."OBJ#"="TCP"."OBJ#")  
102.  104 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
103.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS')  
104.  105 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER"  
105.               IS NULL AND "O"."LINKNAME" IS NULL)  
106.        filter("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL)  
107.  106 - access("O"."OBJ#"="TS"."OBJ#"(+))  
108.  110 - access("TCP"."OBJ#"="M"."OBJ#"(+))  
109.  112 - access("TCP"."BO#"="TAB"."OBJ#")  
110.  116 - filter("TSP"."ANALYZETIME" IS NULL OR CASE  WHEN "TSP"."ANALYZETIME" IS NULL THEN NULL WHEN  
111.               ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TSP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERC  
112.               ENT',"U"."NAME","PO"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')  
113.  118 - access("TCP"."OBJ#"="TSP"."POBJ#")  
114.  119 - access("PO"."OBJ#"="TCP"."OBJ#")  
115.  123 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR  
116.               "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS')  
117.  124 - access("U"."USER#"="PO"."OWNER#" AND "PO"."NAME"=:B1 AND "PO"."NAMESPACE"=1 AND  
118.               "PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL)  
119.        filter("PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL)  
120.  128 - filter("FILE#">0 AND "BLOCK#">0)  
121.  130 - access("TSP"."OBJ#"="M"."OBJ#"(+))  
122.  131 - access("SO"."OBJ#"="TSP"."OBJ#")  
123.  132 - access("SO"."OBJ#"="TS"."OBJ#"(+))  
124.  134 - access("TCP"."BO#"="TAB"."OBJ#")  
125.  135 - filter(NULL IS NOT NULL)  
126.  136 - filter(DECODE(NVL("FOBJ"."OBJ#",0),0,TO_DATE(NULL),INTERNAL_FUNCTION("ST"."ANALYZETIME")) IS  
127.               NULL)  
128.  139 - filter("T"."KQFTANAM"=:B1)  
129.  140 - filter("T"."KQFTAVER"="FOBJ"."TIMESTAMP"(+)-TO_DATE(' 1991-01-01 00:00:00', 'syyyy-mm-dd  
130.               hh24:mi:ss'))  
131.  141 - access("T"."KQFTAOBJ"="FOBJ"."OBJ#"(+))  
132.  143 - access("T"."KQFTAOBJ"="ST"."OBJ#"(+))  
133.   
134. 已选择303行。             

当时是下午，脑的晕晕的 没有怎么关注这个事情，晚上10点过睡觉起来，那位哥们又QQ找我了 呵呵，由于睡了一觉，加上自己一看到大SQL就像打了鸡血一样，所以再次请求那位哥们把SQL发给我，经过半分钟的分析，加了个HINT

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  /*+ UNNEST */ 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秒钟左右出结果，逻辑读由7千多W降低到8W多，我为什么加这个HINT就不说了，经常看我博客的人肯定懂的

有时候单独去访问数据字典很快，但是如果关联了太多数据字典性能就急剧下降，这个时候你不要怕，把它当成我们普通人写的SQL那样对待

很多人说遇到数据字典 加个 /*+ rule */ ，对于这个我是非常不赞同的

另外就是关于这个脚本 DBA任务---确保统计信息准确性

会收集所有分区的统计信息，而不是只收集某个分区(如果表的数据只有一个分区发生了变化那么重复收集分区统计信息就做无用功了)

所以如果哥们要借鉴我的这个SQL，请自己改写