exp 超慢的解決

匯出資料,慢的不行了.一個表,10條記錄,花了2分鐘.系統cpu也N高.

對匯出進行進行sql trace

exec dbms_system.set_sql_trace_in_session(9,12,true);

出現在N多的nest loop.而且,執行效率好慢.

[@more@]

SELECT COLNAME, COLNO, PROPERTY
FROM
SYS.EXU9CCLU WHERE CNO = :1 ORDER BY COLNO

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 24 0 0
Execute 1 0.00 0.15 0 3 0 0
Fetch 2 0.00 5.87 367 453652 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 6.07 367 453679 0 1

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 135

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 VIEW
1 SORT UNIQUE
1 UNION-ALL
1 NESTED LOOPS ANTI
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
93701 NESTED LOOPS OUTER
93701 TABLE ACCESS FULL COL$
1219 TABLE ACCESS CLUSTER ATTRCOL$
1 TABLE ACCESS CLUSTER CCOL$
61122 INDEX UNIQUE SCAN I_COBJ# (object id 30)
1 TABLE ACCESS BY INDEX ROWID OBJ$
1 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
1 TABLE ACCESS CLUSTER USER$
1 INDEX UNIQUE SCAN I_USER# (object id 11)
0 TABLE ACCESS FULL NOEXP$
0 NESTED LOOPS ANTI
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS OUTER
0 NESTED LOOPS
0 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID CCOL$
1 INDEX RANGE SCAN I_CCOL1 (object id 54)
0 TABLE ACCESS BY INDEX ROWID COL$
1 INDEX UNIQUE SCAN I_COL3 (object id 47)
0 TABLE ACCESS BY INDEX ROWID COL$
0 INDEX UNIQUE SCAN I_COL3 (object id 47)
0 TABLE ACCESS CLUSTER ATTRCOL$
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN I_USER# (object id 11)
0 TABLE ACCESS FULL NOEXP$
0 NESTED LOOPS ANTI
0 NESTED LOOPS
0 NESTED LOOPS OUTER
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID CCOL$
1 INDEX RANGE SCAN I_CCOL1 (object id 54)
0 TABLE ACCESS BY INDEX ROWID COL$
1 INDEX UNIQUE SCAN I_COL3 (object id 47)
0 TABLE ACCESS BY INDEX ROWID OBJ$
0 INDEX UNIQUE SCAN I_OBJ1 (object id 36)
0 TABLE ACCESS CLUSTER USER$
0 INDEX UNIQUE SCAN I_USER# (object id 11)
0 TABLE ACCESS CLUSTER COL$
0 INDEX UNIQUE SCAN I_OBJ# (object id 3)
0 TABLE ACCESS CLUSTER ATTRCOL$
0 TABLE ACCESS BY INDEX ROWID COLTYPE$
0 INDEX UNIQUE SCAN I_COLTYPE2 (object id 285)
0 TABLE ACCESS FULL NOEXP$

********************************************************************************

SELECT IOBJID, IDOBJID, INAME, IOWNER, IOWNERID, ISPACE, ITSNO, IFILENO,
IBLOCKNO, BTNAME, BTOWNER, PROPERTY, CLUSTER$, PCTFREE$,
INITRANS, MAXTRANS, BLEVEL, TYPE, DEFLOG, TSDEFLOG, DEGREE,
INSTANCES, ROWCNT, LEAFCNT, DISTKEY, LBLKKEY, DBLKKEY, CLUFAC,
PRECCNT, IFLAGS
FROM
SYS.EXU9IND WHERE IOBJID= :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 6 0 0
Execute 1 0.00 0.03 0 0 0 0
Fetch 2 0.00 0.00 3 34 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.12 3 40 0 1

這肯定是由於修改了某些資料庫引數,導致系統執行sql,出現了錯誤的執行計劃.

仔細回想了下,最近為了測試hash_join的效能,把.

has_join_enable 設定成false,是導致這些問題的主要原因.