针对执行计划比较长的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 -------------------------------------------------------------------------------------