|
TFTHCTSN"[NUMBER,22],
"HC"."KTFTHCSZ"[NUMBER,22], "HC"."KTFTHCCVAL"[NUMBER,22]
69 - "V"."FNNUM"[NUMBER,22], "V"."FNTYP"[NUMBER,22], "V"."FNFNO"[NUMBER,22],
"V"."FNNAM"[VARCHAR2,513]
70 - "HC"."KTFTHCTFNO"[NUMBER,22], "HC"."KTFTHCTSN"[NUMBER,22], "HC"."KTFTHCSZ"[NUMBER,22],
"HC"."KTFTHCCVAL"[NUMBER,22]
71 - "TS".ROWID[ROWID,10], "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30],
"TS"."BLOCKSIZE"[NUMBER,22]
72 - "TS".ROWID[ROWID,10]
73 - "TF"."TFNUM"[NUMBER,22], "TF"."TFSTA"[NUMBER,22], "TF"."TFFNH"[NUMBER,22],
"TF"."TFDUP"[NUMBER,22]
74 - "USED_SPACE"[NUMBER,22], "FREE_SPACE"[NUMBER,22]
75 - (#keys=1) "TS"."NAME"[VARCHAR2,30], SUM("HC"."KTFTHCFREE"*"TS"."BLOCKSIZE")[22],
SUM(("HC"."KTFTHCSZ"-"HC"."KTFTHCFREE")*"TS"."BLOCKSIZE")[22]
76 - "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22], "HC"."KTFTHCSZ"[NUMBER,22],
"HC"."KTFTHCFREE"[NUMBER,22]
77 - "TS"."TS#"[NUMBER,22], "TS"."NAME"[VARCHAR2,30], "TS"."BLOCKSIZE"[NUMBER,22]
78 - "TS".ROWID[ROWID,10], "TS"."NAME"[VARCHAR2,30]
79 - "HC"."INST_ID"[NUMBER,22], "HC"."KTFTHCTSN"[NUMBER,22], "HC"."KTFTHCSZ"[NUMBER,22],
"HC"."KTFTHCFREE"[NUMBER,22], "HC"."KTFTHCCVAL"[NUMBER,22]
450 rows selected.
针对执行计划比较长的sql语句，第一眼看有没有明显的问题，如TABLE ACCESS FULL、MERGE JOIN CARTESIAN等，这些会给sql带来较差的性能问题。
一眼看去，全是系统基表，不敢乱动。那么在执行计划56行那块，有个全表扫描，最要命的是上面还有一个笛卡尔积。下面来看看recyclebin$表中有多少行记录：
SQL> select count(1) from RECYCLEBIN$;
COUNT(1)
----------
697111 接近70万行记录，难怪1小时也跑不出来。
清除recyclebin：
?
?
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from recyclebin$;
COUNT(1)
----------
3334
再次运行sql：
?
?
SQL> set timing on;
SQL> set linesize 200;
SQL> set pagesize 1000;
SQL> col tablespace_name for a20;
SQL> col Total(M) for 999,999,999;
SQL> col USED(M) for 999,999,999;
SQL> col FREE(M) for 999,999,999;
SQL> SELECT D.TABLESPACE_NAME,
2 SPACE "Total(M)",
3 SPACE - NVL(FREE_SPACE, 0) "USED(M)",
4 ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED(%)",
5 FREE_SPACE "FREE(M)"
6 FROM (SELECT TABLESPACE_NAME,
7 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
8 SUM(BLOCKS) BLOCKS
9 FROM DBA_DATA_FILES
10 GROUP BY TABLESPACE_NAME) D,
11 (SELECT TABLESPACE_NAME,
12 ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
13 FROM DBA_FREE_SPACE
14 GROUP BY TABLESPACE_NAME) F
15 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
16 UNION ALL --if have tempfile
17 SELECT D.TABLESPACE_NAME,
18 SPACE "Total(M)",
19 USED_SPACE "USED(M)",
20 ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED(%)",
21 NVL(FREE_SPACE, 0) "FREE(M)"
22 FROM (SELECT TABLESPACE_NAME,
23 ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
24 SUM(BLOCKS) BLOCKS
25 FROM DBA_TEMP_FILES
26 GROUP BY TABLESPACE_NAME) D,
27 (SELECT TABLESPACE_NAME,
28 ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
29 ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
30 FROM V$TEMP_SPACE_HEADER
31 GROUP BY TABLESPACE_NAME) F
32 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
33 ORDER BY 4;
20 rows selected.
Elapsed: 00:00:04.17
Execution Plan
----------------------------------------------------------
Plan hash value: 3967328987
------------------------------------------------------------------------------------- |