一次访问ORACLE数据字典的优化
来源：互联网 发布：拉萨联通网络包年 编辑：程序博客网 时间：2024/05/17 08:18
前面写过一篇帖子：DBA任务---确保统计信息准确性http://blog.csdn.net/robinson1988/article/details/6321537
今晚上有位哥们QQ问我有没有什么SQL脚本用来收集统计信息的 几乎未加思考我就把上面的脚本原封不动的贴个了那位哥们
后来那位哥们改写了SQL，说下面的SQL要跑200多秒，7千多W的逻辑读
SELECT OWNER, SEGMENT_NAME, CASE WHEN SIZE_GB < 0.5 THEN 30 WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN 20 WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN 10 WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN 5 WHEN SIZE_GB >= 10 THEN 1 END AS PERCENT, 2 AS DEGREE FROM (SELECT OWNER, SEGMENT_NAME, SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB FROM DBA_SEGMENTS A WHERE OWNER IN ('DATASYNC_PRC','OSS03','BAS01','DATASYNC_1','DATASYNC_2','OSS_CMS') AND SEGMENT_NAME IN (SELECT DISTINCT TABLE_NAME FROM DBA_TAB_STATISTICS B WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES') AND OWNER IN('DATASYNC_PRC','OSS03','BAS01','DATASYNC_1','DATASYNC_2','OSS_CMS')) GROUP BY OWNER, SEGMENT_NAME); 该SQL语句执行计划如下：
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
| 65 | NESTED LOOPS | | 1 | 147 | 775 (6)| 00:00:03 ||* 66 | FILTER | | | | | || 67 | NESTED LOOPS OUTER | | 1 | 141 | 773 (6)| 00:00:03 ||* 68 | HASH JOIN | | 1 | 123 | 771 (6)| 00:00:03 || 69 | NESTED LOOPS OUTER | | 1 | 65 | 19 (0)| 00:00:01 || 70 | NESTED LOOPS | | 1 | 58 | 19 (0)| 00:00:01 || 71 | INLIST ITERATOR | | | | | || 72 | TABLE ACCESS BY INDEX ROWID | USER$ | 6 | 102 | 7 (0)| 00:00:01 ||* 73 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||* 74 | INDEX RANGE SCAN | I_OBJ2 | 1 | 41 | 2 (0)| 00:00:01 ||* 75 | INDEX UNIQUE SCAN | I_TAB_STATS
| 76 | VIEW | TABPARTV$ | 148K| 8397K| 747 (6)| 00:00:03 ||* 77 | TABLE ACCESS FULL | TABPART$ | 148K| 4632K| 747 (6)| 00:00:03 || 78 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||* 79 | INDEX UNIQUE SCAN | I_MON_MODS_ALL
| 80 | TABLE ACCESS CLUSTER | TAB$ | 21 | 126 | 2 (0)| 00:00:01 ||* 81 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 82 | NESTED LOOPS | | 1 | 140 | 737 (4)| 00:00:03 || 83 | NESTED LOOPS | | 1 | 134 | 735 (4)| 00:00:03 || 84 | NESTED LOOPS | | 1 | 117 | 734 (4)| 00:00:03 || 85 | NESTED LOOPS OUTER | | 1 | 76 | 731 (4)| 00:00:03 || 86 | VIEW | TABPARTV$ | 1 | 58 | 729 (4)| 00:00:03 ||* 87 | TABLE ACCESS FULL | TABPART$ | 1 | 32 | 729 (4)| 00:00:03 || 88 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||* 89 | INDEX UNIQUE SCAN | I_MON_MODS_ALL
|* 90 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 41 | 3 (0)| 00:00:01 ||* 91 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 ||* 92 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 ||* 93 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 || 94 | TABLE ACCESS CLUSTER | TAB$ | 21 | 126 | 2 (0)| 00:00:01 ||* 95 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 96 | NESTED LOOPS | | 3 | 360 | 146 (5)| 00:00:01 ||* 97 | FILTER | | | | | || 98 | NESTED LOOPS OUTER | | 1 | 114 | 144 (5)| 00:00:01 ||* 99 | HASH JOIN | | 1 | 96 | 142 (5)| 00:00:01 || 100 | NESTED LOOPS OUTER | | 1 | 65 | 19 (0)| 00:00:01 || 101 | NESTED LOOPS | | 1 | 58 | 19 (0)| 00:00:01 || 102 | INLIST ITERATOR | | | | | || 103 | TABLE ACCESS BY INDEX ROWID | USER$ | 6 | 102 | 7 (0)| 00:00:01 ||*104 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||*105 | INDEX RANGE SCAN | I_OBJ2 | 1 | 41 | 2 (0)| 00:00:01 ||*106 | INDEX UNIQUE SCAN | I_TAB_STATS
| 107 | VIEW | TABCOMPARTV$ | 19453 | 588K| 122 (5)| 00:00:01 || 108 | TABLE ACCESS FULL | TABCOMPART$ | 19453 | 398K| 122 (5)| 00:00:01 || 109 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||*110 | INDEX UNIQUE SCAN | I_MON_MODS_ALL
| 111 | TABLE ACCESS CLUSTER | TAB$ | 160 | 960 | 2 (0)| 00:00:01 ||*112 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 113 | NESTED LOOPS | | 35 | 5810 | 1146 (8)| 00:00:04 || 114 | NESTED LOOPS OUTER | | 1 | 160 | 1144 (8)| 00:00:04 || 115 | NESTED LOOPS | | 1 | 153 | 1144 (8)| 00:00:04 ||*116 | FILTER | | | | | || 117 | NESTED LOOPS OUTER | | 1 | 147 | 1142 (8)| 00:00:04 ||*118 | HASH JOIN | | 11 | 1419 | 1120 (8)| 00:00:04 ||*119 | HASH JOIN | | 1 | 70 | 142 (5)| 00:00:01 || 120 | NESTED LOOPS | | 1 | 58 | 19 (0)| 00:00:01 || 121 | INLIST ITERATOR | | | | | || 122 | TABLE ACCESS BY INDEX ROWID| USER$ | 6 | 102 | 7 (0)| 00:00:01 ||*123 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||*124 | INDEX RANGE SCAN | I_OBJ2 | 1 | 41 | 2 (0)| 00:00:01 || 125 | VIEW | TABCOMPARTV$ | 19453 | 227K| 122 (5)| 00:00:01 || 126 | TABLE ACCESS FULL | TABCOMPART$ | 19453 | 227K| 122 (5)| 00:00:01 || 127 | VIEW | TABSUBPARTV$ | 269K| 15M| 969 (8)| 00:00:04 ||*128 | TABLE ACCESS FULL | TABSUBPART$ | 269K| 8681K| 969 (8)| 00:00:04 || 129 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||*130 | INDEX UNIQUE SCAN | I_MON_MODS_ALL
|*131 | INDEX RANGE SCAN | I_OBJ1 | 1 | 6 | 2 (0)| 00:00:01 ||*132 | INDEX UNIQUE SCAN | I_TAB_STATS
| 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
| 142 | TABLE ACCESS BY INDEX ROWID | TAB_STATS$ | 1 | 15 | 1 (0)| 00:00:01 ||*143 | INDEX UNIQUE SCAN | I_TAB_STATS
----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter( EXISTS (SELECT 0 FROM ( (SELECT "U"."NAME" "OWNER","O"."NAME" "TABLE_NAME",NULL "PARTITION_NAME",NULL "PARTITION_POSITION",NULL "SUBPARTITION_NAME",NULL "SUBPARTITION_POSITION",'TABLE' "OBJECT_TYPE","T"."ROWCNT" "NUM_ROWS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."BLKCNT",TO_NUMBER(NULL)) "BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."EMPCNT",TO_NUMBER(NULL)) "EMPTY_BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."AVGSPC",TO_NUMBER(NULL)) "AVG_SPACE","T"."CHNCNT" "CHAIN_CNT","T"."AVGRLN" "AVG_ROW_LEN","T"."AVGSPC_FLB" "AVG_SPACE_FREELIST_BLOCKS",DECODE(BITAND("T"."PROPERTY",64),0,"T"."FLBCNT",TO_NUMBER(NULL)) "NUM_FREELIST_BLOCKS","TS"."CACHEDBLK" "AVG_CACHED_BLOCKS","TS"."CACHEHIT" "AVG_CACHE_HIT_RATIO","T"."SAMPLESIZE" "SAMPLE_SIZE","T"."ANALYZETIME" "LAST_ANALYZED",DECODE(BITAND("T"."FLAGS",512),0,'NO','YES') "GLOBAL_STATS",DECODE(BITAND("T"."FLAGS",256),0,'NO','YES') "USER_STATS",DECODE(BITAND("T"."TRIGFLAG",67108864)+BITAND("T"."TRIGFLAG",134217728),0,NULL,67108864,'DA TA',134217728,'CACHE','ALL') "STATTYPE_LOCKED",CASE WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN T',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END "STALE_STATS" FROM "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 WHERE ("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') AND "O"."NAMESPACE"=1 AND "O"."NAME"=:B1 AND "O"."OWNER#"="U"."USER#" AND "O"."SUBNAME" IS NULL AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL AND "O"."OBJ#"="T"."OBJ#" AND BITAND("T"."PROPERTY",1)=0 AND "O"."OBJ#"="TS"."OBJ#"(+) AND "T"."OBJ#"="M"."OBJ#"(+) AND ("T"."ANALYZETIME" IS NULL OR CASE WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMB ER("DBMS_STATS"."GET_PREFS"('STALE_PERCENT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')) UNION ALL (SELECT "U"."NAME" "OWNER","O"."NAME" "TABLE_NAME","O"."SUBNAME" "PARTITION_NAME","TP"."PART#" "PARTITION_POSITION",NULL "SUBPARTITION_NAME",NULL "SUBPARTITION_POSITION",'PARTITION' "OBJECT_TYPE","TP"."ROWCNT" "NUM_ROWS","TP"."BLKCNT" "BLOCKS","TP"."EMPCNT" "EMPTY_BLOCKS","TP"."AVGSPC" "AVG_SPACE","TP"."CHNCNT" "CHAIN_CNT","TP"."AVGRLN" "AVG_ROW_LEN",TO_NUMBER(NULL) "AVG_SPACE_FREELIST_BLOCKS",TO_NUMBER(NULL) "NUM_FREELIST_BLOCKS","TS"."CACHEDBLK" "AVG_CACHED_BLOCKS","TS"."CACHEHIT" "AVG_CACHE_HIT_RATIO","TP"."SAMPLESIZE" "SAMPLE_SIZE","TP"."ANALYZETIME" "LAST_ANALYZED",DECODE(BITAND("TP"."FLAGS",16),0,'NO','YES') "GLOBAL_STATS",DECODE(BITAND("TP"."FLAGS",8),0,'NO','YES') "USER_STATS",DECODE(DECODE(BITAND("TAB"."TRIGFLAG",67108864)+BITAND("TP"."FLAGS",32),0,0,1)+DECODE(BITAN D("TAB"."TRIGFLAG",134217728)+BITAND("TP"."FLAGS",64),0,0,2),0,NULL,1,'DATA',2,'CACHE','ALL') "STATTYPE_LOCKED",CASE WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END "STALE_STATS" FROM "SYS"."MON_MODS_ALL{1}quot; "M","SYS"."TAB{1}quot; "TAB","SYS"."TAB_STATS{1}quot; "TS", (SELECT "OBJ#" "OBJ#","DATAOBJ#" "DATAOBJ#","BO#" "BO#",ROW_NUMBER() OVER ( PARTITION BY "BO#" ORDER BY "PART#") "PART#","HIBOUNDLEN" "HIBOUNDLEN","HIBOUNDVAL" "HIBOUNDVAL","TS#" "TS#","FILE#" "FILE#","BLOCK#" "BLOCK#","PCTFREE{1}quot; "PCTFREE{1}quot;,"PCTUSED{1}quot; "PCTUSED{1}quot;,"INITRANS" "INITRANS","MAXTRANS" "MAXTRANS","FLAGS" "FLAGS","ANALYZETIME" "ANALYZETIME","SAMPLESIZE" "SAMPLESIZE","ROWCNT" "ROWCNT","BLKCNT" "BLKCNT","EMPCNT" "EMPCNT","AVGSPC" "AVGSPC","CHNCNT" "CHNCNT","AVGRLN" "AVGRLN","PART#" "PHYPART#" FROM SYS."TABPART{1}quot; "TABPART{1}quot; WHERE "FILE#">0 AND "BLOCK#">0) "TP","SYS"."OBJ{1}quot; "O","SYS"."USER{1}quot; "U" WHERE ("U"."NAME"='BAS01' OR "U"."NAME"='DATAS) 6 - access("S"."TS#"="TS"."TS#") 9 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID") 10 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 11 - access("O"."OWNER#"="U"."USER#"(+)) 16 - filter(BITAND("T"."PROPERTY",1024)=0) 19 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9) 21 - filter(BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128) 25 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID") 26 - access("S"."TS#"="SO"."TS_NUMBER" AND "S"."FILE#"="SO"."HEADER_FILE" AND "S"."BLOCK#"="SO"."HEADER_BLOCK") 27 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#") 30 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 31 - access("S"."USER#"="U"."USER#"(+)) 33 - filter("UN"."STATUS{1}quot;<>1) 34 - filter("S"."TYPE#"=1 OR "S"."TYPE#"=10) 35 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#") 38 - access("S"."TS#"="TS"."TS#") 39 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#") 40 - access("S"."TS#"="TS"."TS#") 42 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 43 - access("S"."USER#"="U"."USER#"(+)) 47 - filter("S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>10 AND "S"."TYPE#"<>1) 48 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#") 51 - filter("T"."ANALYZETIME" IS NULL OR CASE WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN T',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 58 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 59 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL AND "O"."SUBNAME" IS NULL) filter("O"."SUBNAME" IS NULL AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 60 - access("O"."OBJ#"="TS"."OBJ#"(+)) 61 - filter(BITAND("T"."PROPERTY",1)=0) 62 - access("O"."OBJ#"="T"."OBJ#") 64 - access("T"."OBJ#"="M"."OBJ#"(+)) 66 - filter("TP"."ANALYZETIME" IS NULL OR CASE WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 68 - access("O"."OBJ#"="TP"."OBJ#") 73 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 74 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) filter("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 75 - access("O"."OBJ#"="TS"."OBJ#"(+)) 77 - filter("FILE#">0 AND "BLOCK#">0) 79 - access("TP"."OBJ#"="M"."OBJ#"(+)) 81 - access("TP"."BO#"="TAB"."OBJ#") 87 - filter("BLOCK#"=0 AND "FILE#"=0) 89 - access("TP"."OBJ#"="M"."OBJ#"(+)) 90 - filter("O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 91 - access("O"."OBJ#"="TP"."OBJ#") 92 - filter(("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') AND ("TP"."ANALYZETIME" IS NULL OR CASE WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCE NT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')) 93 - access("O"."OWNER#"="U"."USER#") 95 - access("TP"."BO#"="TAB"."OBJ#") 97 - filter("TCP"."ANALYZETIME" IS NULL OR CASE WHEN "TCP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TCP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERC ENT',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 99 - access("O"."OBJ#"="TCP"."OBJ#") 104 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 105 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"=:B1 AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) filter("O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 106 - access("O"."OBJ#"="TS"."OBJ#"(+)) 110 - access("TCP"."OBJ#"="M"."OBJ#"(+)) 112 - access("TCP"."BO#"="TAB"."OBJ#") 116 - filter("TSP"."ANALYZETIME" IS NULL OR CASE WHEN "TSP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TSP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERC ENT',"U"."NAME","PO"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 118 - access("TCP"."OBJ#"="TSP"."POBJ#") 119 - access("PO"."OBJ#"="TCP"."OBJ#") 123 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 124 - access("U"."USER#"="PO"."OWNER#" AND "PO"."NAME"=:B1 AND "PO"."NAMESPACE"=1 AND "PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL) filter("PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL) 128 - filter("FILE#">0 AND "BLOCK#">0) 130 - access("TSP"."OBJ#"="M"."OBJ#"(+)) 131 - access("SO"."OBJ#"="TSP"."OBJ#") 132 - access("SO"."OBJ#"="TS"."OBJ#"(+)) 134 - access("TCP"."BO#"="TAB"."OBJ#") 135 - filter(NULL IS NOT NULL) 136 - filter(DECODE(NVL("FOBJ"."OBJ#",0),0,TO_DATE(NULL),INTERNAL_FUNCTION("ST"."ANALYZETIME")) IS NULL) 139 - filter("T"."KQFTANAM"=:B1) 140 - filter("T"."KQFTAVER"="FOBJ"."TIMESTAMP"(+)-TO_DATE(' 1991-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 141 - access("T"."KQFTAOBJ"="FOBJ"."OBJ#"(+)) 143 - access("T"."KQFTAOBJ"="ST"."OBJ#"(+))已选择303行。
当时是下午，脑的晕晕的 没有怎么关注这个事情，晚上10点过睡觉起来，那位哥们又QQ找我了 呵呵，由于睡了一觉，加上自己一看到大SQL就像打了鸡血一样，所以再次请求那位哥们把SQL发给我，经过半分钟的分析，加了个HINT
SELECT OWNER, SEGMENT_NAME, CASE WHEN SIZE_GB < 0.5 THEN 30 WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN 20 WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN 10 WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN 5 WHEN SIZE_GB >= 10 THEN 1 END AS PERCENT, 2 AS DEGREE FROM (SELECT OWNER, SEGMENT_NAME, SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB FROM DBA_SEGMENTS A WHERE OWNER IN ('DATASYNC_PRC','OSS03','BAS01','DATASYNC_1','DATASYNC_2','OSS_CMS') AND SEGMENT_NAME IN (SELECT /*+ UNNEST */ DISTINCT TABLE_NAME FROM DBA_TAB_STATISTICS B WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES') AND OWNER IN('DATASYNC_PRC','OSS03','BAS01','DATASYNC_1','DATASYNC_2','OSS_CMS')) GROUP BY OWNER, SEGMENT_NAME); 执行计划如下：
SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 539876731------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 22412 | 4224K| 122K (3)| 00:06:24 || 1 | HASH GROUP BY | | 22412 | 4224K| 122K (3)| 00:06:24 ||* 2 | HASH JOIN | | 22412 | 4224K| 122K (3)| 00:06:24 || 3 | VIEW | VW_NSO_1 | 790 | 13430 | 11899 (8)| 00:00:38 || 4 | HASH UNIQUE | | 790 | 36340 | 11899 (8)| 00:00:38 || 5 | VIEW | DBA_TAB_STATISTICS | 790 | 36340 | 11898 (8)| 00:00:38 || 6 | UNION-ALL | | | | | || 7 | NESTED LOOPS OUTER | | 231 | 26565 | 3598 (7)| 00:00:12 ||* 8 | FILTER | | | | | ||* 9 | HASH JOIN OUTER | | 231 | 24948 | 3598 (7)| 00:00:12 || 10 | NESTED LOOPS | | 1321 | 116K| 1885 (1)| 00:00:06 || 11 | NESTED LOOPS | | 1605 | 105K| 188 (1)| 00:00:01 || 12 | INLIST ITERATOR | | | | | || 13 | TABLE ACCESS BY INDEX ROWID | USER$ | 6 | 102 | 7 (0)| 00:00:01 ||* 14 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||* 15 | INDEX RANGE SCAN | I_OBJ2 | 267 | 13350 | 30 (0)| 00:00:01 ||* 16 | TABLE ACCESS CLUSTER | TAB$ | 1 | 23 | 2 (0)| 00:00:01 ||* 17 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 18 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1926K| 33M| 1653 (10)| 00:00:06 ||* 19 | INDEX UNIQUE SCAN | I_TAB_STATS{1}OBJ# | 1 | 7 | 0 (0)| 00:00:01 || 20 | NESTED LOOPS OUTER | | 265 | 43195 | 3176 (9)| 00:00:10 || 21 | NESTED LOOPS | | 265 | 41340 | 3176 (9)| 00:00:10 ||* 22 | FILTER | | | | | ||* 23 | HASH JOIN OUTER | | 261 | 39150 | 2652 (11)| 00:00:09 ||* 24 | HASH JOIN | | 930 | 119K| 940 (5)| 00:00:03 || 25 | NESTED LOOPS | | 5315 | 301K| 188 (1)| 00:00:01 || 26 | INLIST ITERATOR | | | | | || 27 | TABLE ACCESS BY INDEX ROWID | USER$ | 6 | 102 | 7 (0)| 00:00:01 ||* 28 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||* 29 | INDEX RANGE SCAN | I_OBJ2 | 886 | 36326 | 30 (0)| 00:00:01 || 30 | VIEW | TABPARTV$ | 148K| 10M| 747 (6)| 00:00:03 ||* 31 | TABLE ACCESS FULL | TABPART$ | 148K| 4632K| 747 (6)| 00:00:03 || 32 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1926K| 33M| 1653 (10)| 00:00:06 || 33 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 ||* 34 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 ||* 35 | INDEX UNIQUE SCAN | I_TAB_STATS{1}OBJ# | 1 | 7 | 0 (0)| 00:00:01 || 36 | NESTED LOOPS | | 1 | 156 | 737 (4)| 00:00:03 || 37 | NESTED LOOPS | | 1 | 150 | 735 (4)| 00:00:03 || 38 | NESTED LOOPS | | 1 | 133 | 734 (4)| 00:00:03 || 39 | NESTED LOOPS OUTER | | 1 | 92 | 731 (4)| 00:00:03 || 40 | VIEW | TABPARTV$ | 1 | 74 | 729 (4)| 00:00:03 ||* 41 | TABLE ACCESS FULL | TABPART$ | 1 | 32 | 729 (4)| 00:00:03 || 42 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||* 43 | INDEX UNIQUE SCAN | I_MON_MODS_ALL{1}OBJ | 1 | | 1 (0)| 00:00:01 ||* 44 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 41 | 3 (0)| 00:00:01 ||* 45 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 ||* 46 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 ||* 47 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 || 48 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 ||* 49 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 50 | NESTED LOOPS | | 52 | 7124 | 659 (2)| 00:00:03 || 51 | NESTED LOOPS OUTER | | 51 | 6681 | 556 (2)| 00:00:02 ||* 52 | FILTER | | | | | || 53 | NESTED LOOPS OUTER | | 51 | 6324 | 556 (2)| 00:00:02 ||* 54 | HASH JOIN | | 122 | 12932 | 311 (3)| 00:00:01 || 55 | NESTED LOOPS | | 5315 | 301K| 188 (1)| 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 | 886 | 36326 | 30 (0)| 00:00:01 || 60 | VIEW | TABCOMPARTV$ | 19453 | 911K| 122 (5)| 00:00:01 || 61 | TABLE ACCESS FULL | TABCOMPART$ | 19453 | 398K| 122 (5)| 00:00:01 || 62 | TABLE ACCESS BY INDEX ROWID | MON_MODS_ALL$ | 1 | 18 | 2 (0)| 00:00:01 ||* 63 | INDEX UNIQUE SCAN | I_MON_MODS_ALL{1}OBJ | 1 | | 1 (0)| 00:00:01 ||* 64 | INDEX UNIQUE SCAN | I_TAB_STATS{1}OBJ# | 1 | 7 | 0 (0)| 00:00:01 || 65 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 ||* 66 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 67 | NESTED LOOPS OUTER | | 240 | 46800 | 3728 (9)| 00:00:12 || 68 | NESTED LOOPS | | 240 | 45120 | 3728 (9)| 00:00:12 ||* 69 | FILTER | | | | | ||* 70 | HASH JOIN OUTER | | 240 | 43680 | 3247 (10)| 00:00:11 ||* 71 | HASH JOIN | | 1713 | 274K| 1534 (6)| 00:00:05 || 72 | NESTED LOOPS | | 124 | 11160 | 556 (2)| 00:00:02 ||* 73 | HASH JOIN | | 122 | 10248 | 311 (3)| 00:00:01 || 74 | NESTED LOOPS | | 5315 | 301K| 188 (1)| 00:00:01 || 75 | INLIST ITERATOR | | | | | || 76 | TABLE ACCESS BY INDEX ROWID| USER$ | 6 | 102 | 7 (0)| 00:00:01 ||* 77 | INDEX UNIQUE SCAN | I_USER1 | 6 | | 2 (0)| 00:00:01 ||* 78 | INDEX RANGE SCAN | I_OBJ2 | 886 | 36326 | 30 (0)| 00:00:01 || 79 | VIEW | TABCOMPARTV$ | 19453 | 493K| 122 (5)| 00:00:01 || 80 | TABLE ACCESS FULL | TABCOMPART$ | 19453 | 227K| 122 (5)| 00:00:01 || 81 | TABLE ACCESS CLUSTER | TAB$ | 1 | 6 | 2 (0)| 00:00:01 ||* 82 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 || 83 | VIEW | TABSUBPARTV$ | 269K| 19M| 969 (8)| 00:00:04 ||* 84 | TABLE ACCESS FULL | TABSUBPART$ | 269K| 8681K| 969 (8)| 00:00:04 || 85 | TABLE ACCESS FULL | MON_MODS_ALL$ | 1926K| 33M| 1653 (10)| 00:00:06 ||* 86 | INDEX RANGE SCAN | I_OBJ1 | 1 | 6 | 2 (0)| 00:00:01 ||* 87 | INDEX UNIQUE SCAN | I_TAB_STATS{1}OBJ# | 1 | 7 | 0 (0)| 00:00:01 ||* 88 | FILTER | | | | | ||* 89 | FILTER | | | | | ||* 90 | HASH JOIN RIGHT OUTER | | 783 | 39933 | 7 (29)| 00:00:01 || 91 | TABLE ACCESS FULL | TAB_STATS$ | 770 | 11550 | 3 (0)| 00:00:01 ||* 92 | HASH JOIN OUTER | | 783 | 28188 | 3 (34)| 00:00:01 || 93 | FIXED TABLE FULL | X$KQFTA | 783 | 16443 | 1 (100)| 00:00:01 || 94 | TABLE ACCESS FULL | FIXED_OBJ$ | 784 | 11760 | 2 (0)| 00:00:01 || 95 | VIEW | SYS_DBA_SEGS | 2837 | 487K| 110K (2)| 00:05:46 || 96 | UNION-ALL | | | | | || 97 | NESTED LOOPS | | 1840 | 296K| 93690 (2)| 00:04:53 ||* 98 | HASH JOIN | | 1779 | 272K| 93690 (2)| 00:04:53 || 99 | TABLE ACCESS FULL | TS$ | 172 | 1548 | 54 (2)| 00:00:01 || 100 | NESTED LOOPS | | 1779 | 257K| 93635 (2)| 00:04:53 ||*101 | HASH JOIN | | 6571 | 757K| 80450 (3)| 00:04:12 ||*102 | FILTER | | | | | ||*103 | HASH JOIN RIGHT OUTER | | 7221 | 423K| 10278 (6)| 00:00:33 || 104 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 || 105 | TABLE ACCESS FULL | OBJ$ | 3854K| 158M| 10133 (5)| 00:00:32 || 106 | VIEW | SYS_OBJECTS | 3507K| 194M| 70065 (2)| 00:03:40 || 107 | UNION-ALL | | | | | ||*108 | TABLE ACCESS FULL | TAB$ | 210K| 5548K| 15995 (2)| 00:00:51 || 109 | TABLE ACCESS FULL | TABPART$ | 148K| 2895K| 727 (3)| 00:00:03 || 110 | TABLE ACCESS FULL | CLU$ | 10 | 150 | 14128 (2)| 00:00:45 ||*111 | TABLE ACCESS FULL | IND$ | 750K| 16M| 16045 (2)| 00:00:51 || 112 | TABLE ACCESS FULL | INDPART$ | 620K| 11M| 2424 (4)| 00:00:08 ||*113 | TABLE ACCESS FULL | LOB$ | 2273 | 50006 | 15929 (2)| 00:00:50 || 114 | TABLE ACCESS FULL | TABSUBPART$ | 269K| 5261K| 932 (4)| 00:00:03 || 115 | TABLE ACCESS FULL | INDSUBPART$ | 1503K| 28M| 3868 (5)| 00:00:13 || 116 | TABLE ACCESS FULL | LOBFRAG$ | 2977 | 65494 | 17 (0)| 00:00:01 ||*117 | TABLE ACCESS CLUSTER | SEG$ | 1 | 30 | 2 (0)| 00:00:01 ||*118 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 ||*119 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | 0 (0)| 00:00:01 || 120 | NESTED LOOPS | | 1 | 109 | 2274 (1)| 00:00:08 || 121 | NESTED LOOPS | | 1 | 101 | 2274 (1)| 00:00:08 ||*122 | FILTER | | | | | ||*123 | HASH JOIN OUTER | | 1 | 92 | 2273 (1)| 00:00:08 || 124 | NESTED LOOPS | | 568 | 42600 | 2245 (1)| 00:00:08 ||*125 | TABLE ACCESS FULL | UNDO$ | 1116 | 45756 | 5 (0)| 00:00:01 ||*126 | TABLE ACCESS CLUSTER | SEG$ | 1 | 34 | 2 (0)| 00:00:01 ||*127 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 (0)| 00:00:01 || 128 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 || 129 | TABLE ACCESS CLUSTER | TS$ | 1 | 9 | 1 (0)| 00:00:01 ||*130 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 ||*131 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 8 | 0 (0)| 00:00:01 ||*132 | HASH JOIN | | 996 | 77688 | 14672 (1)| 00:00:46 || 133 | TABLE ACCESS FULL | TS$ | 172 | 1548 | 54 (2)| 00:00:01 ||*134 | FILTER | | | | | ||*135 | HASH JOIN RIGHT OUTER | | 996 | 68724 | 14618 (1)| 00:00:46 || 136 | TABLE ACCESS FULL | USER$ | 3200 | 54400 | 27 (0)| 00:00:01 || 137 | NESTED LOOPS | | 531K| 26M| 14574 (1)| 00:00:46 || 138 | TABLE ACCESS FULL | FILE$ | 872 | 10464 | 3 (0)| 00:00:01 ||*139 | TABLE ACCESS CLUSTER | SEG$ | 610 | 24400 | 23 (0)| 00:00:01 ||*140 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("SEGMENT_NAME"="TABLE_NAME") 8 - filter("T"."ANALYZETIME" IS NULL OR CASE WHEN "T"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"T"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCENT' ,"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 9 - access("T"."OBJ#"="M"."OBJ#"(+)) 14 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 15 - access("O"."OWNER#"="U"."USER#" AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL AND "O"."SUBNAME" IS NULL) filter("O"."SUBNAME" IS NULL AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 16 - filter(BITAND("T"."PROPERTY",1)=0) 17 - access("O"."OBJ#"="T"."OBJ#") 19 - access("O"."OBJ#"="TS"."OBJ#"(+)) 22 - filter("TP"."ANALYZETIME" IS NULL OR CASE WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCENT ',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 23 - access("TP"."OBJ#"="M"."OBJ#"(+)) 24 - access("O"."OBJ#"="TP"."OBJ#") 28 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 29 - access("O"."OWNER#"="U"."USER#" AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) filter("O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 31 - filter("FILE#">0 AND "BLOCK#">0) 34 - access("TP"."BO#"="TAB"."OBJ#") 35 - access("O"."OBJ#"="TS"."OBJ#"(+)) 41 - filter("BLOCK#"=0 AND "FILE#"=0) 43 - access("TP"."OBJ#"="M"."OBJ#"(+)) 44 - filter("O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 45 - access("O"."OBJ#"="TP"."OBJ#") 46 - filter(("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') AND ("TP"."ANALYZETIME" IS NULL OR CASE WHEN "TP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCENT ',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES')) 47 - access("O"."OWNER#"="U"."USER#") 49 - access("TP"."BO#"="TAB"."OBJ#") 52 - filter("TCP"."ANALYZETIME" IS NULL OR CASE WHEN "TCP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TCP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN T',"U"."NAME","O"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 54 - access("O"."OBJ#"="TCP"."OBJ#") 58 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 59 - access("O"."OWNER#"="U"."USER#" AND "O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) filter("O"."NAMESPACE"=1 AND "O"."REMOTEOWNER" IS NULL AND "O"."LINKNAME" IS NULL) 63 - access("TCP"."OBJ#"="M"."OBJ#"(+)) 64 - access("O"."OBJ#"="TS"."OBJ#"(+)) 66 - access("TCP"."BO#"="TAB"."OBJ#") 69 - filter("TSP"."ANALYZETIME" IS NULL OR CASE WHEN "TSP"."ANALYZETIME" IS NULL THEN NULL WHEN ("M"."INSERTS"+"M"."DELETES"+"M"."UPDATES">"TSP"."ROWCNT"*TO_NUMBER("DBMS_STATS"."GET_PREFS"('STALE_PERCEN T',"U"."NAME","PO"."NAME"))/100 OR BITAND("M"."FLAGS",1)=1) THEN 'YES' ELSE 'NO' END ='YES') 70 - access("TSP"."OBJ#"="M"."OBJ#"(+)) 71 - access("TCP"."OBJ#"="TSP"."POBJ#") 73 - access("PO"."OBJ#"="TCP"."OBJ#") 77 - access("U"."NAME"='BAS01' OR "U"."NAME"='DATASYNC_1' OR "U"."NAME"='DATASYNC_2' OR "U"."NAME"='DATASYNC_PRC' OR "U"."NAME"='OSS03' OR "U"."NAME"='OSS_CMS') 78 - access("U"."USER#"="PO"."OWNER#" AND "PO"."NAMESPACE"=1 AND "PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL) filter("PO"."NAMESPACE"=1 AND "PO"."REMOTEOWNER" IS NULL AND "PO"."LINKNAME" IS NULL) 82 - access("TCP"."BO#"="TAB"."OBJ#") 84 - filter("FILE#">0 AND "BLOCK#">0) 86 - access("SO"."OBJ#"="TSP"."OBJ#") 87 - access("SO"."OBJ#"="TS"."OBJ#"(+)) 88 - filter(NULL IS NOT NULL) 89 - filter(DECODE(NVL("FOBJ"."OBJ#",0),0,TO_DATE(NULL),INTERNAL_FUNCTION("ST"."ANALYZETIME")) IS NULL) 90 - access("T"."KQFTAOBJ"="ST"."OBJ#"(+)) 92 - access("T"."KQFTAOBJ"="FOBJ"."OBJ#"(+) AND "T"."KQFTAVER"="FOBJ"."TIMESTAMP"(+)-TO_DATE(' 1991-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 98 - access("S"."TS#"="TS"."TS#") 101 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID") 102 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 103 - access("O"."OWNER#"="U"."USER#"(+)) 108 - filter(BITAND("T"."PROPERTY",1024)=0) 111 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9) 113 - filter(BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128) 117 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID") 118 - access("S"."TS#"="SO"."TS_NUMBER" AND "S"."FILE#"="SO"."HEADER_FILE" AND "S"."BLOCK#"="SO"."HEADER_BLOCK") 119 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#") 122 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 123 - access("S"."USER#"="U"."USER#"(+)) 125 - filter("UN"."STATUS{1}quot;<>1) 126 - filter("S"."TYPE#"=1 OR "S"."TYPE#"=10) 127 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#") 130 - access("S"."TS#"="TS"."TS#") 131 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#") 132 - access("S"."TS#"="TS"."TS#") 134 - filter(NVL("U"."NAME",'SYS')='DATASYNC_PRC' OR NVL("U"."NAME",'SYS')='OSS03' OR NVL("U"."NAME",'SYS')='BAS01' OR NVL("U"."NAME",'SYS')='DATASYNC_1' OR NVL("U"."NAME",'SYS')='DATASYNC_2' OR NVL("U"."NAME",'SYS')='OSS_CMS') 135 - access("S"."USER#"="U"."USER#"(+)) 139 - filter("S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>10 AND "S"."TYPE#"<>1) 140 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")已选择251行。这次SQL能在1秒钟左右出结果，逻辑读由7千多W降低到8W多，我为什么加这个HINT就不说了，经常看我博客的人肯定懂的
有时候单独去访问数据字典很快，但是如果关联了太多数据字典性能就急剧下降，这个时候你不要怕，把它当成我们普通人写的SQL那样对待
很多人说遇到数据字典 加个 /*+ rule */ ，对于这个我是非常不赞同的
另外就是关于这个脚本 DBA任务---确保统计信息准确性 http://blog.csdn.net/robinson1988/article/details/6321537
会收集所有分区的统计信息，而不是只收集某个分区(如果表的数据只有一个分区发生了变化那么重复收集分区统计信息就做无用功了)
所以如果哥们要借鉴我的这个SQL，请自己改写
- 一次访问ORACLE数据字典的优化
- Oracle的数据字典
- ORACLE的数据字典
- oracle的数据字典
- ORACLE数据库SQL优化--->ORACLE访问数据的方法
- Oracle的数据字典视图
- Oracle数据库的数据字典
- 常用的ORACLE数据字典
- Oracle数据字典的导出
- Oracle常用的数据字典
- oracle常用的数据字典!
- Oracle提供的数据字典
- oracle数据字典的介绍
- Oracle常用的数据字典
- Oracle常用的数据字典
- oracle常用的数据字典
- ORACLE数据字典的使用
- 常用的oracle数据字典
- warning: extra tokens at end of #include directive
- poj 1503Integer Inquiry(高精度 水题)
- 51单片机学习（四）用点阵LED显示I LOVE YOU！
- 解读java的包装类
- Pku 2723 Get Luffy Out
- 一次访问ORACLE数据字典的优化
- Boa web服务器在linux2.6.32下CGI测试
- QT中的插件---load plugin
- 基础算法 —— 递归求N阶乘
- 有关getchar()和EOF
- Java中，ResultSet 的用法
- Pku 3207 Ikki's Story IV - Panda's Trick
- Google技术学习
- Spring 管理数据源
