Oracle查询表空间使⽤率很慢
Oracle查询表空间使⽤率很慢
问题描述
执⾏查询表空间的语句，需要接近2min的时间才能执⾏完成。
以前也在其他客户的⽣产库遇到过⼀样的情况，当时是由于回收站的内容过多引起的。
不过这次的情况却不是这样，因为回收站的内容并不多。
调试分析
⽼⽅法，设置statistics_level=all获取详细的执⾏情况，如下：
20:32:05 SYS@anonymous>SELECT a.tablespace_name,
20:32:052ROUND (a.bytes_alloc /1024/1024, 2) megs_alloc,
20:32:053ROUND (NVL (b.bytes_free, 0) /1024/1024, 2) megs_free,
20:32:054ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) /1024/1024, 2 ) megs_used,
20:32:055ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_free,
20:32:056100-ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_used,
access是什么意思啊了20:32:057ROUND (maxbytes /1048576, 2) MAX
20:32:058FROM
20:32:059 (SELECT f.tablespace_name,
20:32:0510SUM (f.BYTES) bytes_alloc,
20:32:0511SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes
20:32:0512FROM dba_data_files f
20:32:0513GROUP BY tablespace_name
20:32:0514 ) a,
20:32:0515 (SELECT f.tablespace_name,
20:32:0516SUM (f.BYTES) bytes_free
20:32:0517FROM dba_free_space f
20:32:0518GROUP BY tablespace_name
20:32:0519 ) b
20:32:0520WHERE a.tablespace_name = b.tablespace_name(+)
20:32:0521UNION ALL
20:32:0522SELECT h.tablespace_name,
20:32:0523ROUND (SUM (h.bytes_free + h.bytes_used) /1048576, 2) megs_alloc,
20:32:0524ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0)) /1048576, 2 ) megs_free,
20:32:0525ROUND (SUM (NVL (p.bytes_used, 0)) /1048576, 2) megs_used,
20:32:0526ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) /SUM (h.bytes_used + h.bytes_free) ) *100, 2 ) pct_free,
20:32:0527100-ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) /SUM (h.bytes_used + h.bytes_free) ) *100, 2 ) pct_used,
20:32:0528ROUND (SUM (f.maxbytes) /1048576, 2) MAX
20:32:0529FROM SYS.v_$temp_space_header h,
20:32:0530 SYS.v_$temp_extent_pool p,
20:32:0531 dba_temp_files f
20:32:0532WHERE p.file_id(+) = h.file_id
20:32:0633AND p.tablespace_name(+) = h.tablespace_name
20:32:0634AND f.file_id= h.file_id
20:32:0635AND f.tablespace_name = h.tablespace_name
20:32:0636GROUP BY h.tablespace_name
20:32:0637ORDER BY1 ;
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
xxxxxxxxxxxxxxxx 409640197798.12 1.884096
xxxxxxxxxxxxxxxx 81922283590927.8772.138192
xxxxxxxxxxxxxxxx 2048111393554.3545.652048
xxxxxxxxxxxxxxxx 2457616092848465.4834.5224576
xxxxxxxxxxxxxxxx 40964095199.98 .024096
xxxxxxxxxxxxxxxx 81928187599.94 .068192
xxxxxxxxxxxxxxxx 4000337362784.3315.674000
xxxxxxxxxxxxxxxx 54630434656119974363.4436.56546304
xxxxxxxxxxxxxxxx 40962969112772.4927.514096
xxxxxxxxxxxxxxxx 409640861099.76 .244096
xxxxxxxxxxxxxxxx 81923047514537.1962.818192
xxxxxxxxxxxxxxxx 2766087707819953027.8772.13276608
xxxxxxxxxxxxxxxx 163848605777952.5247.4816384
xxxxxxxxxxxxxxxx 40962944115271.8828.124096
xxxxxxxxxxxxxxxx 1638435751280921.8278.1816384
xxxxxxxxxxxxxxxx 40961074302226.2273.784096
xxxxxxxxxxxxxxxx 409640712599.39 .614096
xxxxxxxxxxxxxxxx 4096391518195.58 4.424096
xxxxxxxxxxxxxxxx 163841598440097.56 2.4416384
xxxxxxxxxxxxxxxx 327608390.8824369.1325.6174.3932767.98
xxxxxxxxxxxxxxxx 536034.135325.88 .6499.3632767.98
xxxxxxxxxxxxxxxx 40962946115071.9228.084096
xxxxxxxxxxxxxxxx 409640524498.93 1.074096
xxxxxxxxxxxxxxxx 2009613860623668.9731.0320096
xxxxxxxxxxxxxxxx 1638413068331679.7620.2416384
xxxxxxxxxxxxxxxx 2081924093316725919.6680.34208192
xxxxxxxxxxxxxxxx 2009616143395380.3319.6720096
xxxxxxxxxxxxxxxx 2048189815092.687.322048
xxxxxxxxxxxxxxxx 2009616138395880.319.720096
xxxxxxxxxxxxxxxx 68096133005479619.5380.4768096
xxxxxxxxxxxxxxxx 819281791399.84 .168192
xxxxxxxxxxxxxxxx 327673197179697.57 2.4332767.98
xxxxxxxxxxxxxxxx 102402371.757868.2523.1676.8410240
xxxxxxxxxxxxxxxx 103909292.251097.7589.4310.5732767.98
xxxxxxxxxxxxxxxx 4356.25209.384146.88 4.8195.1932767.98
35 rows selected.
Elapsed: 00:01:54.62
20:34:00 SYS@anonymous>select*from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8ks58zbpgra00, child number1
-------------------------------------
SELECT a.tablespace_name, ROUND (a.bytes_alloc /1024/
1024, 2) megs_alloc, ROUND (NVL (b.bytes_free, 0) /1024/1024, 2)
megs_free, ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) /
1024/1024, 2 ) megs_used, ROUND ((NVL (b.bytes_free, 0) /
a.bytes_alloc) *100, 2) pct_free, 100-
ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_used,
ROUND (maxbytes /1048576, 2) MAX FROM (SELECT
f.tablespace_name, SUM (f.BYTES) bytes_alloc, SUM (DECODE
(f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes FROM
dba_data_files f GROUP BY tablespace_name ) a, (SELECT
f.tablespace_name, SUM (f.BYTES) bytes_free FROM dba_free_space f
GROUP BY tablespace_name ) b WHERE a.tablespace_name =
b.tablespace_name(+) UNION ALL SELECT h.tablespace_name, ROUND (SUM
(h.bytes_free + h.bytes_used) /1048576, 2) megs_alloc,
ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.b
Plan hash value: 2506036241
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------
|0|SELECT STATEMENT ||1||35|00:01:54.53|53354|4865||||
|1| SORT ORDER BY||1|3|35|00:01:54.53|53354|4865|4096|4096|4096 (0)|
|2|UNION-ALL||1||35|00:01:54.53|53354|4865||||
|*3| HASH JOIN OUTER||1|2|34|00:01:54.18|53310|4865| 1229K| 1229K| 1243K (0)|
|4|VIEW||1|2|34|00:00:00.66|444|0||||
|5| HASH GROUP BY||1|2|34|00:00:00.66|444|0| 941K| 941K| 1349K (0)|
|6|VIEW| DBA_DATA_FILES |1|2|108|00:00:00.65|444|0||||
|7|UNION-ALL||1||108|00:00:00.65|444|0||||
|8| NESTED LOOPS ||1|1|0|00:00:00.57|112|0||||
|9| NESTED LOOPS ||1|1|0|00:00:00.57|112|0||||
|10| NESTED LOOPS ||1|1|0|00:00:00.57|112|0||||
|*11| FIXED TABLE FULL| X$KCCFN |1|1|108|00:00:00.57|0|0||||
|*12|TABLE ACCESS BY INDEX ROWID|FILE$ |108|1|0|00:00:00.01|112|0||||
|*13|INDEX UNIQUE SCAN | I_FILE1 |108|1|108|00:00:00.01|4|0||||
|*14| FIXED TABLE FIXED INDEX| X$KCCFE (ind:1) |0|1|0|00:00:00.01|0|0||||
|15|TABLE ACCESS CLUSTER | TS$ |0|1|0|00:00:00.01|0|0||||
|*16|INDEX UNIQUE SCAN | I_TS# |0|1|0|00:00:00.01|0|0||||
|17| NESTED LOOPS ||1|1|108|00:00:00.08|332|0||||
|18| NESTED LOOPS ||1|1|108|00:00:00.08|220|0||||
|19| NESTED LOOPS ||1|1|108|00:00:00.01|220|0||||
|20| NESTED LOOPS ||1|1|108|00:00:00.01|108|0||||
|*21| FIXED TABLE FULL| X$KCCFN |1|1|108|00:00:00.01|0|0||||
|*22| FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1) |108|1|108|00:00:00.01|108|0||||
|*23|TABLE ACCESS BY INDEX ROWID|FILE$ |108|1|108|00:00:00.01|112|0||||
|*24|INDEX UNIQUE SCAN | I_FILE1 |108|1|108|00:00:00.01|4|0||||
|*25| FIXED TABLE FIXED INDEX| X$KCCFE (ind:1) |108|1|108|00:00:00.08|0|0||||
|26|TABLE ACCESS CLUSTER | TS$ |108|1|108|00:00:00.01|112|0||||
|*27|INDEX UNIQUE SCAN | I_TS# |108|1|108|00:00:00.01|4|0||||
|28|VIEW||1|31|34|00:01:53.52|52866|4865||||
|29| HASH GROUP BY||1|31|34|00:01:53.52|52866|4865| 9291K| 2834K| 1346K (0)|
|30|VIEW| DBA_FREE_SPACE |1|140| 100K|00:01:53.50|52866|4865||||
|31|UNION-ALL||1|| 100K|00:01:53.48|52866|4865||||
|32| NESTED LOOPS ||1|1|0|00:00:00.01|38|0||||
|33| NESTED LOOPS ||1|1|0|00:00:00.01|38|0||||
|34|TABLE ACCESS FULL| FET$ |1|1|0|00:00:00.01|38|0||||
|*35|TABLE ACCESS CLUSTER | TS$ |0|1|0|00:00:00.01|0|0||||
|*36|INDEX UNIQUE SCAN | I_TS# |0|1|0|00:00:00.01|0|0||||
|*37|INDEX UNIQUE SCAN | I_FILE2 |0|1|0|00:00:00.01|0|0||||
|38| NESTED LOOPS ||1|88|84440|00:00:00.15|394|0||||
|39| NESTED LOOPS ||1|88|84440|00:00:00.06|390|0||||
|*40|TABLE ACCESS FULL| TS$ |1|31|34|00:00:00.01|38|0||||
|*41| FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |34|3|84440|00:00:00.05|352|0||||
|*42|INDEX UNIQUE SCAN | I_FILE2 |84440|1|84440|00:00:00.06|4|0||||
|*43| HASH JOIN||1|50|16133|00:01:53.30|52396|4865| 2297K| 2297K| 2426K (0)|
|44| NESTED LOOPS ||1|50|16133|00:01:53.29|52358|4865||||
|*45| HASH JOIN||1|808|16133|00:01:53.26|52354|4865| 1753K| 1753K| 1511K (0)|
|*46|TABLE ACCESS FULL| RECYCLEBIN$ |1|152|191|00:00:00.01|4|0||||
|47| FIXED TABLE FULL| X$KTFBUE |1| 100K| 723K|00:01:52.95|52350|4865||||
|*48|INDEX UNIQUE SCAN | I_FILE2 |16133|1|16133|00:00:00.01|4|0||||
|*49|TABLE ACCESS FULL| TS$ |1|31|34|00:00:00.01|38|0||||
|50| NESTED LOOPS ||1|1|0|00:00:00.01|38|0||||
|51| NESTED LOOPS ||1|1|0|00:00:00.01|38|0||||
|52| MERGE JOIN CARTESIAN ||1|425|0|00:00:00.01|38|0||||
|*53|TABLE ACCESS FULL| TS$ |1|3|0|00:00:00.01|38|0||||
|54| BUFFER SORT ||0|152|0|00:00:00.01|0|0|73728|73728||
|*55|TABLE ACCESS FULL| RECYCLEBIN$ |0|152|0|00:00:00.01|0|0||||
|56|TABLE ACCESS CLUSTER | UET$ |0|1|0|00:00:00.01|0|0||||
|*57|INDEX UNIQUE SCAN | I_FILE#_BLOCK# |0|1|0|00:00:00.01|0|0||||
|*58|INDEX UNIQUE SCAN | I_FILE2 |0|1|0|00:00:00.01|0|0||||
|59| HASH GROUP BY||1|1|1|00:00:00.35|44|0| 856K| 856K| 463K (0)|
|60| NESTED LOOPS OUTER||1|1|1|00:00:00.35|44|0||||
|*61| HASH JOIN||1|1|1|00:00:00.35|42|0| 1281K| 1281K| 402K (0)|
|62| NESTED LOOPS ||1|1|1|00:00:00.35|3|0||||
|63| NESTED LOOPS ||1|1|1|00:00:00.35|1|0||||
|64| NESTED LOOPS ||1|1|1|00:00:00.35|0|0||||
|*65| FIXED TABLE FULL| X$KCCFN |1|1|1|00:00:00.34|0|0||||
|*66| FIXED TABLE FIXED INDEX| X$KCCTF (ind:1) |1|1|1|00:00:00.01|0|0||||
|*67| FIXED TABLE FIXED INDEX| X$KTFTHC (ind:1) |1|1|1|00:00:00.01|1|0||||
|68|TABLE ACCESS CLUSTER | TS$ |1|1|1|00:00:00.01|2|0||||
|*69|INDEX UNIQUE SCAN | I_TS# |1|1|1|00:00:00.01|1|0||||
|70|VIEW| V_$TEMP_SPACE_HEADER |1|1|1|00:00:00.01|39|0||||
|71| NESTED LOOPS ||1|1|1|00:00:00.01|39|0||||
|*72|TABLE ACCESS FULL| TS$ |1|1|1|00:00:00.01|38|0||||
|*73| FIXED TABLE FIXED INDEX| X$KTFTHC (ind:2) |1|1|1|00:00:00.01|1|0||||
|*74|VIEW PUSHED PREDICATE | V_$TEMP_EXTENT_POOL |1|1|1|00:00:00.01|2|0|||| |75| NESTED LOOPS ||1|1|1|00:00:00.01|2|0||||
|*76|TABLE ACCESS BY INDEX ROWID | TS$ |1|1|1|00:00:00.01|2|0||||
|*77|INDEX UNIQUE SCAN | I_TS1 |1|1|1|00:00:00.01|1|0||||
|*78| FIXED TABLE FIXED INDEX| X$KTSTFC (ind:1) |1|1|1|00:00:00.01|0|0||||
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3- access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
11- filter(("FNNAM" IS NOT NULL AND "FNTYP"=4AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
12- filter("F"."SPARE1" IS NULL)
13- access("FNFNO"="F"."FILE#")
14- filter("FE"."FENUM"="F"."FILE#")
16- access("F"."TS#"="TS"."TS#")
21- filter(("FNNAM" IS NOT NULL AND "FNTYP"=4AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
22- filter("FNFNO"="HC"."KTFBHCAFNO")
23- filter("F"."SPARE1" IS NOT NULL)
24- access("FNFNO"="F"."FILE#")
25- filter("FE"."FENUM"="F"."FILE#")
27- access("HC"."KTFBHCTSN"="TS"."TS#")
35- filter("TS"."BITMAPPED"=0)
36- access("TS"."TS#"="F"."TS#")
37- access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40- filter(("TS"."BITMAPPED"<>0AND "TS"."CONTENTS$"=0AND INTERNAL_FUNCTION("TS"."ONLINE$")))
41- filter("TS"."TS#"="F"."KTFBFETSN")
42- access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
43- access("TS"."TS#"="RB"."TS#")
45- access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#") 46- filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
48- access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
49- filter(("TS"."BITMAPPED"<>0AND "TS"."CONTENTS$"=0AND INTERNAL_FUNCTION("TS"."ONLINE$")))
53- filter("TS"."BITMAPPED"=0)
55- filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
57- access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
filter("TS"."TS#"="U"."TS#")
58- access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
61- access("HC"."KTFTHCTFNO"="H"."FILE_ID" AND "TS"."NAME"="H"."TABLESPACE_NAME")
65- filter(("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7))
66- filter(("TF"."TFDUP"<>0AND BITAND("TF"."TFSTA",32)<>32AND "V"."FNFNO"="TF"."TFNUM" AND "TF"."TFFNH"="V"."FNNUM"))
67- filter("V"."FNFNO"="HC"."KTFTHCTFNO")
69- access("HC"."KTFTHCTSN"="TS"."TS#")
72- filter(("TS"."CONTENTS$"=1AND "TS"."BITMAPPED"<>0AND "TS"."ONLINE$"=1))
73- filter(("HC"."KTFTHCCVAL"=0AND "HC"."INST_ID"=USERENV('INSTANCE') AND "TS"."TS#"="H
C"."KTFTHCTSN"))
74- filter("P"."FILE_ID"="H"."FILE_ID")
76- filter(("TS"."CONTENTS$"=1AND "TS"."BITMAPPED"<>0AND "TS"."ONLINE$"=1))
77- access("TS"."NAME"="H"."TABLESPACE_NAME")
78- filter(("FC"."INST_ID"=USERENV('INSTANCE') AND "TS"."TS#"="FC"."KTSTFCTSN"))
147 rows selected.
Elapsed: 00:00:00.08
可以发现，慢的步骤在于对表X$KTFBUE的全表扫描，这个问题也引起了另外⼀个问题，可以看我另外⼀篇博⽂->。
从执⾏计划很容易得出由于该表缺乏统计信息或统计信息不准确导致的。
原本尝试想要⽤hint强制使⽤索引的，但是不⾏，⽽且这固定表的索引本⾝就很奇葩。⽤/*+cardinality(dba_free_space.x$ktfbue 999) */ /*+cardinality(u.x$ktfbue 999) */之类的hint都不⾏。
mos的⼀篇关于该问题的⽂章：Query Against Dictionary Views Extracting Tablespace Information is Slow or Appears to Hang (Doc ID 1292253.1)
直接收集统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS',DEGREE =>40,TABNAME =>'X$KTFBUE',ESTIMATE_PERCENT=>100,NO_INVALIDATE => FALSE);
Elapsed: 00:10:24.03
20:24:43 sys@anonymous(eportdb01)>select LAST_ANALYZED,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_ROW_LEN from dba_tab_statistics where owner='SYS'and table_name='X$KTFBUE';
LAST_ANALYZED NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
------------------- ---------- ---------- ------------ -----------
2020-05-1520:24:4373040769
再次执⾏发现对固定表X$KTFBUE已经可以⽤上索引了，查询表空间使⽤率的sql⼤概3s所有可以执⾏完。
22:54:11 sys@anonymous(eportdb01)>SELECT a.tablespace_name,
22:54:362ROUND (a.bytes_alloc /1024/1024, 2) megs_alloc,
22:54:363ROUND (NVL (b.bytes_free, 0) /1024/1024, 2) megs_free,
22:54:364ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) /1024/1024, 2 ) megs_used,
22:54:365ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_free,
22:54:366100-ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_used,
22:54:367ROUND (maxbytes /1048576, 2) MAX
22:54:368FROM
22:54:369 (SELECT f.tablespace_name,
22:54:3610SUM (f.BYTES) bytes_alloc,
22:54:3611SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes
22:54:3612FROM dba_data_files f
22:54:3613GROUP BY tablespace_name
22:54:3714 ) a,
22:54:3715 (SELECT f.tablespace_name,
22:54:3716SUM (f.BYTES) bytes_free
22:54:3717FROM dba_free_space f
22:54:3718GROUP BY tablespace_name
22:54:3719 ) b
22:54:3720WHERE a.tablespace_name = b.tablespace_name(+)
22:54:3721UNION ALL
22:54:3722SELECT h.tablespace_name,
22:54:3723ROUND (SUM (h.bytes_free + h.bytes_used) /1048576, 2) megs_alloc,
22:54:3724ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0)) /1048576, 2 ) megs_free,
22:54:3725ROUND (SUM (NVL (p.bytes_used, 0)) /1048576, 2) megs_used,
22:54:3726ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) /SUM (h.bytes_used + h.bytes_free) ) *100, 2 ) pct_free,
22:54:3727100-ROUND ( ( SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0) ) /SUM (h.bytes_used + h.bytes_free) ) *100, 2 ) pct_used,
22:54:3728ROUND (SUM (f.maxbytes) /1048576, 2) MAX
22:54:3729FROM SYS.v_$temp_space_header h,
22:54:3730 SYS.v_$temp_extent_pool p,
22:54:3731 dba_temp_files f
22:54:3732WHERE p.file_id(+) = h.file_id
22:54:3733AND p.tablespace_name(+) = h.tablespace_name
22:54:3734AND f.file_id= h.file_id
22:54:3735AND f.tablespace_name = h.tablespace_name
22:54:3736GROUP BY h.tablespace_name
22:54:3737ORDER BY1 ;
TABLESPACE_NAME MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED MAX
------------------ ---------- ---------- ---------- ---------- ---------- ----------
XXXXXXXXXXXXXXXX 61441621452326.3873.626144
XXXXXXXXXXXXXXXX 409640197798.12 1.884096
XXXXXXXXXXXXXXXX 81922283590927.8772.138192
XXXXXXXXXXXXXXXX 2048111393554.3545.652048
XXXXXXXXXXXXXXXX 2457616075850165.4134.5924576
XXXXXXXXXXXXXXXX 40964095199.98 .024096
XXXXXXXXXXXXXXXX 81928187599.94 .068192
XXXXXXXXXXXXXXXX 4000337362784.3315.674000
XXXXXXXXXXXXXXXX 54630434570920059563.2836.72546304
XXXXXXXXXXXXXXXX 40962969112772.4927.514096
XXXXXXXXXXXXXXXX 409640861099.76 .244096
XXXXXXXXXXXXXXXX 81923047514537.1962.818192
XXXXXXXXXXXXXXXX 2766087690519970327.872.2276608
XXXXXXXXXXXXXXXX 163848599778552.4847.5216384
XXXXXXXXXXXXXXXX 40962944115271.8828.124096
XXXXXXXXXXXXXXXX 1638435521283221.6878.3216384
XXXXXXXXXXXXXXXX 40961074302226.2273.784096
XXXXXXXXXXXXXXXX 409640712599.39 .614096
XXXXXXXXXXXXXXXX 4096391518195.58 4.424096
XXXXXXXXXXXXXXXX 163841597940597.53 2.4716384
XXXXXXXXXXXXXXXX 327608045.3824714.6324.5675.4432767.98
XXXXXXXXXXXXXXXX 53604442.44917.5682.8817.1232767.98
XXXXXXXXXXXXXXXX 40962946115071.9228.084096
XXXXXXXXXXXXXXXX 409640524498.93 1.074096
XXXXXXXXXXXXXXXX 2009613843625368.8831.1220096
XXXXXXXXXXXXXXXX 1638413068331679.7620.2416384
XXXXXXXXXXXXXXXX 2081924028816790419.3580.65208192
XXXXXXXXXXXXXXXX 2009616087400980.0519.9520096
XXXXXXXXXXXXXXXX 2048187417491.58.52048
XXXXXXXXXXXXXXXX 2009616111398580.1719.8320096
XXXXXXXXXXXXXXXX 68096126265547018.5481.4668096
XXXXXXXXXXXXXXXX 819281672599.69 .318192
XXXXXXXXXXXXXXXX 327673195281597.51 2.4932767.98
XXXXXXXXXXXXXXXX 102402491.57748.524.3375.6710240
XXXXXXXXXXXXXXXX 1039010144.31245.6997.64 2.3632767.98
XXXXXXXXXXXXXXXX 4356.25209.384146.88 4.8195.1932767.98
36 rows selected.
Elapsed: 00:00:03.13
22:54:40 sys@anonymous(eportdb01)>select*from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID 8ks58zbpgra00, child number2
-------------------------------------
SELECT a.tablespace_name, ROUND (a.bytes_alloc /1024/
1024, 2) megs_alloc, ROUND (NVL (b.bytes_free, 0) /1024/1024, 2)
megs_free, ROUND ((a.bytes_alloc - NVL (b.bytes_free, 0)) /
1024/1024, 2 ) megs_used, ROUND ((NVL (b.bytes_free, 0) /
a.bytes_alloc) *100, 2) pct_free, 100-
ROUND ((NVL (b.bytes_free, 0) / a.bytes_alloc) *100, 2) pct_used,
ROUND (maxbytes /1048576, 2) MAX FROM (SELECT
f.tablespace_name, SUM (f.BYTES) bytes_alloc, SUM (DECODE
(f.autoextensible, 'YES', f.maxbytes, 'NO', f.BYTES ) ) maxbytes FROM
dba_data_files f GROUP BY tablespace_name ) a, (SELECT
f.tablespace_name, SUM (f.BYTES) bytes_free FROM dba_free_space f
GROUP BY tablespace_name ) b WHERE a.tablespace_name =
b.tablespace_name(+) UNION ALL SELECT h.tablespace_name, ROUND (SUM
(h.bytes_free + h.bytes_used) /1048576, 2) megs_alloc,
ROUND ( SUM ((h.bytes_free + h.bytes_used) - NVL (p.b
Plan hash value: 3723612910
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|0|SELECT STATEMENT ||1||36|00:00:03.12|17525|96||||
|1| SORT ORDER BY||1|38|36|00:00:03.12|17525|96|4096|4096|4096 (0)|
|2|UNION-ALL||1||36|00:00:03.12|17525|96||||
|*3| HASH JOIN OUTER||1|37|35|00:00:03.11|17478|96| 1229K| 1229K| 1596K (0)|
|4|VIEW||1|37|35|00:00:00.25|154|0||||
|5| HASH GROUP BY||1|37|35|00:00:00.25|154|0| 941K| 941K| 1372K (0)|
|6|VIEW| DBA_DATA_FILES |1|109|109|00:00:00.25|154|0||||
|7|UNION-ALL||1||109|00:00:00.25|154|0||||
|8| NESTED LOOPS ||1|1|0|00:00:00.01|2|0||||
|9| NESTED LOOPS ||1|1|0|00:00:00.01|2|0||||
|*10| HASH JOIN||1|1|0|00:00:00.01|2|0| 1079K| 1079K| 192K (0)|
|*11|TABLE ACCESS FULL|FILE$ |1|1|0|00:00:00.01|2|0||||
|*12| FIXED TABLE FULL| X$KCCFN |0|108|0|00:00:00.01|0|0||||
|13|TABLE ACCESS CLUSTER | TS$ |0|1|0|00:00:00.01|0|0||||
|*14|INDEX UNIQUE SCAN | I_TS# |0|1|0|00:00:00.01|0|0||||
|*15| FIXED TABLE FIXED INDEX| X$KCCFE (ind:1) |0|1|0|00:00:00.01|0|0||||
|16| NESTED LOOPS ||1|108|109|00:00:00.25|152|0||||
|*17| HASH JOIN||1|108|109|00:00:00.16|152|0| 1393K| 1393K| 1573K (0)|
|18|TABLE ACCESS FULL| TS$ |1|37|37|00:00:00.01|41|0||||
|19| NESTED LOOPS ||1|108|109|00:00:00.16|111|0||||
|*20| HASH JOIN||1|108|109|00:00:00.16|2|0| 2440K| 2440K| 1647K (0)|
|*21| FIXED TABLE FULL| X$KCCFN |1|108|109|00:00:00.15|0|0||||
|*22|TABLE ACCESS FULL|FILE$ |1|109|109|00:00:00.01|2|0||||
|*23| FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1) |109|1|109|00:00:00.01|109|0||||
|*24| FIXED TABLE FIXED INDEX| X$KCCFE (ind:1) |109|1|109|00:00:00.09|0|0||||
|25|VIEW||1|33|35|00:00:02.85|17324|96||||
|26| HASH GROUP BY||1|33|35|00:00:02.85|17324|96| 9291K| 2834K| 2511K (0)|
|27|VIEW| DBA_FREE_SPACE |1|78687| 100K|00:00:02.82|17324|96||||
|28|UNION-ALL||1|| 100K|00:00:02.81|17324|96||||
|29| NESTED LOOPS ||1|1|0|00:00:00.01|41|0||||
|30| NESTED LOOPS ||1|1|0|00:00:00.01|41|0||||
|31|TABLE ACCESS FULL| FET$ |1|1|0|00:00:00.01|41|0||||
|*32|TABLE ACCESS CLUSTER | TS$ |0|1|0|00:00:00.01|0|0||||
|*33|INDEX UNIQUE SCAN | I_TS# |0|1|0|00:00:00.01|0|0||||
|*34|INDEX UNIQUE SCAN | I_FILE2 |0|1|0|00:00:00.01|0|0||||
|*35| HASH JOIN||1|78675|83871|00:00:00.11|397|0| 1969K| 1969K| 1573K (0)|
|36|INDEX FULL SCAN | I_FILE2 |1|109|109|00:00:00.01|1|0||||
|37| NESTED LOOPS ||1|78675|83871|00:00:00.06|396|0||||
|*38|TABLE ACCESS FULL| TS$ |1|33|35|00:00:00.01|41|0||||
|*39| FIXED TABLE FIXED INDEX| X$KTFBFE (ind:1) |35|2397|83871|00:00:00.05|355|0||||
|40| NESTED LOOPS ||1|10|16133|00:00:02.65|16845|96||||
|41| NESTED LOOPS ||1|10|16133|00:00:02.60|708|96||||
|42| NESTED LOOPS ||1|157|16133|00:00:02.57|704|96||||
|*43|TABLE ACCESS FULL| RECYCLEBIN$ |1|157|191|00:00:00.01|4|0||||
|*44| FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |191|1|16133|00:00:02.57|700|96||||
|*45|INDEX UNIQUE SCAN | I_FILE2 |16133|1|16133|00:00:00.02|4|0||||
|*46|TABLE ACCESS CLUSTER | TS$ |16133|1|16133|00:00:00.05|16137|0||||
|*47|INDEX UNIQUE SCAN | I_TS# |16133|1|16133|00:00:00.01|4|0||||
|48| NESTED LOOPS ||1|1|0|00:00:00.01|41|0||||
|49| NESTED LOOPS ||1|1|0|00:00:00.01|41|0||||
|50| MERGE JOIN CARTESIAN ||1|438|0|00:00:00.01|41|0||||
|*51|TABLE ACCESS FULL| TS$ |1|3|0|00:00:00.01|41|0||||
|52| BUFFER SORT ||0|157|0|00:00:00.01|0|0|73728|73728||
|*53|TABLE ACCESS FULL| RECYCLEBIN$ |0|157|0|00:00:00.01|0|0||||
|54|TABLE ACCESS CLUSTER | UET$ |0|1|0|00:00:00.01|0|0||||
|*55|INDEX UNIQUE SCAN | I_FILE#_BLOCK# |0|1|0|00:00:00.01|0|0||||
|*56|INDEX UNIQUE SCAN | I_FILE2 |0|1|0|00:00:00.01|0|0||||
|57| HASH GROUP BY||1|1|1|00:00:00.02|47|0| 856K| 856K| 491K (0)|
|58| NESTED LOOPS ||1|1|1|00:00:00.02|47|0||||
|59| NESTED LOOPS ||1|1|1|00:00:00.02|46|0||||
|60| NESTED LOOPS OUTER||1|1|1|00:00:00.02|45|0||||
|*61| HASH JOIN||1|1|1|00:00:00.02|43|0| 1451K| 1451K| 716K (0)|
|62| NESTED LOOPS ||1|1|1|00:00:00.02|1|0||||
|63| NESTED LOOPS ||1|1|1|00:00:00.02|0|0||||
|*64| FIXED TABLE FULL| X$KCCTF |1|1|1|00:00:00.02|0|0||||
|*65| FIXED TABLE FIXED INDEX| X$KCCFN (ind:1) |1|1|1|00:00:00.01|0|0||||
|*66| FIXED TABLE FIXED INDEX| X$KTFTHC (ind:1) |1|1|1|00:00:00.01|1|0||||
|67|VIEW| V_$TEMP_SPACE_HEADER |1|1|1|00:00:00.01|42|0||||
|68| NESTED LOOPS ||1|1|1|00:00:00.01|42|0||||
|*69|TABLE ACCESS FULL| TS$ |1|1|1|00:00:00.01|41|0||||
|*70| FIXED TABLE FIXED INDEX| X$KTFTHC (ind:2) |1|1|1|00:00:00.01|1|0||||
|*71|VIEW PUSHED PREDICATE | V_$TEMP_EXTENT_POOL |1|1|1|00:00:00.01|2|0||||
|72| NESTED LOOPS ||1|1|1|00:00:00.01|2|0||||
|*73|TABLE ACCESS BY INDEX ROWID| TS$ |1|1|1|00:00:00.01|2|0||||
|*74|INDEX UNIQUE SCAN | I_TS1 |1|1|1|00:00:00.01|1|0||||
|*75| FIXED TABLE FIXED INDEX| X$KTSTFC (ind:1) |1|1|1|00:00:00.01|0|0||||
|*76|INDEX UNIQUE SCAN | I_TS1 |1|1|1|00:00:00.01|1|0||||
|*77|TABLE ACCESS BY INDEX ROWID | TS$ |1|1|1|00:00:00.01|1|0||||
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3- access("A"."TABLESPACE_NAME"="B"."TABLESPACE_NAME")
10- access("FNFNO"="F"."FILE#")
11- filter("F"."SPARE1" IS NULL)
12- filter(("FNTYP"=4AND "FNNAM" IS NOT NULL AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
14- access("F"."TS#"="TS"."TS#")
15- filter("FE"."FENUM"="F"."FILE#")
17- access("HC"."KTFBHCTSN"="TS"."TS#")
20- access("FNFNO"="F"."FILE#")
21- filter(("FNTYP"=4AND "FNNAM" IS NOT NULL AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4))
22- filter("F"."SPARE1" IS NOT NULL)
23- filter("FNFNO"="HC"."KTFBHCAFNO")
24- filter("FE"."FENUM"="F"."FILE#")
32- filter("TS"."BITMAPPED"=0)
33- access("TS"."TS#"="F"."TS#")
34- access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
35- access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
38- filter(("TS"."BITMAPPED"<>0AND "TS"."CONTENTS$"=0AND INTERNAL_FUNCTION("TS"."ONLINE$")))
39- filter("TS"."TS#"="F"."KTFBFETSN")
43- filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
44- filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")) 45- access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
46- filter(("TS"."BITMAPPED"<>0AND "TS"."CONTENTS$"=0AND INTERNAL_FUNCTION("TS"."ONLINE$")))
47- access("TS"."TS#"="RB"."TS#")
51- filter("TS"."BITMAPPED"=0)
53- filter(("RB"."TS#" IS NOT NULL AND "RB"."FILE#" IS NOT NULL AND "RB"."BLOCK#" IS NOT NULL))
55- access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
filter("TS"."TS#"="U"."TS#")
56- access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
61- access("HC"."KTFTHCTFNO"="H"."FILE_ID")
64- filter((BITAND("TF"."TFSTA",32)<>32AND "TF"."TFDUP"<>0))
65- filter(("V"."FNTYP"=7AND "V"."FNNAM" IS NOT NULL AND "V"."FNFNO"="TF"."TFNUM" AND "TF"."TFFNH"="V"."FNNUM"))
66- filter("V"."FNFNO"="HC"."KTFTHCTFNO")
69- filter(("TS"."CONTENTS$"=1AND "TS"."BITMAPPED"<>0AND "TS"."ONLINE$"=1))
70- filter(("HC"."INST_ID"=USERENV('INSTANCE') AND "HC"."KTFTHCCVAL"=0AND "TS"."TS#"="HC"."KTFTHCTSN"))
71- filter("P"."FILE_ID"="H"."FILE_ID")
73- filter(("TS"."CONTENTS$"=1AND "TS"."BITMAPPED"<>0AND "TS"."ONLINE$"=1))
74- access("TS"."NAME"="H"."TABLESPACE_NAME")
75- filter(("FC"."INST_ID"=USERENV('INSTANCE') AND "TS"."TS#"="FC"."KTSTFCTSN"))
76- access("TS"."NAME"="H"."TABLESPACE_NAME")
77- filter("HC"."KTFTHCTSN"="TS"."TS#")
147 rows selected.
Elapsed: 00:00:00.87
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论