导出数据,慢的不行了.一个表,10条记录,花了2分钟.系统cpu也N高.

对导出进行进行sql trace

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

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

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,是导致这些问题的主要原因.