写了一个存储过程,操作的数据量大概在100万左右,主要做的事情是将A表中的字段merge到B表中去,开始的时候刚建用户,表,用sqlldr导入数据到A表,没有做表分析。发现这个存储过程速度还可以,400多秒完成。在truncate B表后,做了表分析后,发现这个存储过程变得巨慢,跟踪了下session,两个tkprof输出最大的不同见下: 分析前快的(所有记录操作完成):
********************************************************************************SELECT ID
FROM
TEMP_CUST WHERE IDTYPE = :B3 AND SEC_CERT_NO = :B2 AND ORGID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1027433 15.55 17.75 0 0 0 0
Fetch 1027433 13.76 13.98 18 3278873 0 265694
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2054867 29.31 31.74 18 3278873 0 265694Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 108 (recursive depth: 1)
******************************************************************************** 分析后慢的(只做了28969笔,就强行手工停了):
********************************************************************************SELECT ID
FROM
TEMP_CUST WHERE IDTYPE = :B3 AND SEC_CERT_NO = :B2 AND ORGID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 28969 0.78 0.68 0 0 0 0
Fetch 28969 428.80 422.12 0 6157031 0 541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 57939 429.58 422.80 0 6157031 0 541Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 108 (recursive depth: 1)
********************************************************************************
可见两次query的次数相差很多,第一次全部执行完成才做了300多万,第二次执行了2万多次查询就已经query了600多万了,不知道为什么,求达人指教!附:我分析表的命令 exec dbms_stats.gather_schema_stats('USER_NAME'); 后来我强行加索引提示这个问题解决,但是还是不明白其中的道理,求指教
********************************************************************************SELECT ID
FROM
TEMP_CUST WHERE IDTYPE = :B3 AND SEC_CERT_NO = :B2 AND ORGID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1027433 15.55 17.75 0 0 0 0
Fetch 1027433 13.76 13.98 18 3278873 0 265694
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2054867 29.31 31.74 18 3278873 0 265694Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 108 (recursive depth: 1)
******************************************************************************** 分析后慢的(只做了28969笔,就强行手工停了):
********************************************************************************SELECT ID
FROM
TEMP_CUST WHERE IDTYPE = :B3 AND SEC_CERT_NO = :B2 AND ORGID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 28969 0.78 0.68 0 0 0 0
Fetch 28969 428.80 422.12 0 6157031 0 541
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 57939 429.58 422.80 0 6157031 0 541Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 108 (recursive depth: 1)
********************************************************************************
可见两次query的次数相差很多,第一次全部执行完成才做了300多万,第二次执行了2万多次查询就已经query了600多万了,不知道为什么,求达人指教!附:我分析表的命令 exec dbms_stats.gather_schema_stats('USER_NAME'); 后来我强行加索引提示这个问题解决,但是还是不明白其中的道理,求指教
另外最好可以贴出2种情况下的执行计划。
走索引了,我看了 Index range啊
可否提供执行计划?我很好奇。
10gR2 ,不应该是Optimizer mode: CHOOSE
from plan_table
where statement_id = 'TEST';
******************************************************************************
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID COST
SELECT STATEMENT 0
TABLE ACCESS BY INDEX ROWID TEMP_CUST 1 0
INDEX RANGE SCAN IDX_CUST_SEC_ID 2 1
******************************************************************************OPTIMIZER MODE = CHOOSE是因为手工设置的,10R2的默认值是ALL_ROWS.
alter session set events '10046 trace name context forever, level 12';
......
alter session set sql_trace=false;
alter session set events '10046 trace name context off';试着跟踪下的,生成的跟踪文件信息很全,一般都能定位出问题的。