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

最新推荐文章于 2024-08-27 09:41:07 发布

iteye_20478

最新推荐文章于 2024-08-27 09:41:07 发布

阅读量61

点赞数

文章标签：数据库网络

前面写过一篇帖子：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，请自己改写

iteye_20478

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一次访问ORACLE数据字典的优化

前面写过一篇帖子：DBA任务---确保统计信息准确性http://blog.csdn.net/robinson1988/article/details/6321537今晚上有位哥们QQ问我有没有什么SQL脚本用来收集统计信息的几乎未加思考我就把上面的脚本原封不动的贴个了那位哥们后来那位哥们改写了SQL，说下面的SQL要跑200多秒，7千多W的逻辑读SELECT OWNER, ...
复制链接

扫一扫