昨天开发咨询我一个问题,希望我对下面的语句进行调优。
语句类似下面的形式
SELECT subscriber_no
FROM SUBSCRIBER S
WHERE SUBSCRIBER_TYPE = 'RM'
and CONV_RUN_NO in (20, 21, 22, 23)
and not EXISTS (SELECT s.AGREEMENT_NO
FROM SERVICE_AGREEMENT S, CSM_OFFER C
WHERE s.subscriber_no = S.AGREEMENT_NO
and SOC = C.SOC_CD
and SOC_TYPE ='P'
)
这个语句的执行计划如下:
Plan hash value: 1111602366
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 22 | 56648 (1)| 00:11:20 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | SUBSCRIBER | 2 | 22 | 56646 (1)| 00:11:20 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 2 | 44 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SERVICE_AGREEMENT_PK | 8 | 104 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CSM_OFFER_1IX | 1 | | 1 (0)| 00:00:01 |
|* 7 | MAT_VIEW ACCESS BY INDEX ROWID| CSM_OFFER | 1 | 9 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "PRDREFSSCA"."CSM_OFFER"
"C","PRDAPPO"."SERVICE_AGREEMENT" "S" WHERE "S"."AGREEMENT_NO"=:B1 AND "SOC"="C"."SOC_CD" AND
"SOC_TYPE"='P'))
2 - filter(("CONV_RUN_NO"=20 OR "CONV_RUN_NO"=21 OR "CONV_RUN_NO"=22 OR "CONV_RUN_NO"=23) AND
"SUBSCRIBER_TYPE"='RM')
5 - access("S"."AGREEMENT_NO"=:B1)
6 - access("SOC"="C"."SOC_CD")
7 - filter("SOC_TYPE"='P')
从语句来看是真没有多少什么调优空间了。为了想尽快得到结果,我建议加了一个hint /*+parallel(4)*/
我一再叮嘱他,这中语句最好在备份库中执行,因为备份中的数据是相对比较新的,他也同意。备份库当时负载很低,执行大概用了6分钟的样子。输出结果大约有70多万。
我以为这件事就这么过去了,结果今天早上他找到我说,希望把这70多万条记录存到一个一个临时用户下,需要关联查找更多的细节。
这种问题让人有些纠结。不过为了问题的解决,也不能完全怪他。
尝试一:
首先的一个思想就是想吐省事,在中午的时候尝试生成这些记录。
create table issue_sub_list nologging as
SELECT /*+parallel(4)*/ subscriber_no
FROM SUBSCRIBER S
WHERE SUBSCRIBER_TYPE = 'RM'
and CONV_RUN_NO in (20, 21, 22, 23)
and not EXISTS (SELECT s.AGREEMENT_NO
FROM SERVICE_AGREEMENT S, CSM_OFFER C
WHERE s.subscriber_no = S.AGREEMENT_NO
and SOC = C.SOC_CD
and SOC_TYPE ='P'
)
这条语句结果在生产上执行了将近10分钟还没有完成的意思,我查看执行计划的输出列情况,才全表扫描了30%,所以果断终止了这个操作。
这条路不建议使用,我看还有什么别的路可走。
尝试二:
一来就是对得到的数据进一步转换为insert语句。尝试在windows的ultraEdit下面编辑,因为这些id有差不多11M左右,使用文本编辑器没过一会,就提示缓存溢出,卡在那了。
尝试三:
看来文本编辑器还是很有使用限制的,linux环境下的命令方式要相对好很多。百兆上G的日志都没有问题。
我写了如下的脚本。
while read line
do
sqlplus -s n1/n1@xxx < insert into issue_sublist values( $line);
EOF
done < sub_list.log
执行的速度来看,单条语句是没有问题的,但是这条语句执行了10多分钟,还没有完,进步还是让人不够满意。
尝试四:
大家使用外部表来查看数据库的日志信息,这是一种方式,我们也可以应用到这个场景中。来看看外部表的情况。
创建了directory
create directory EXT_DATAPUMP_DIR as '/oravl01/oracle/ext_datapump/DUMP';
grant read,write on directory EXT_DATAPUMP_DIR to n1;
然后使用语句创建了外部表。
create table issue_sublist_ext(text varchar2(1000)) organization external
(type oracle_loader
default directory EXT_DATAPUMP_DIR
location('sub_list.log')
);
Table created.
Elapsed: 00:00:00.01
SQL> select count(*)from issue_sublist_ext;
COUNT(*)
----------
769685
Elapsed: 00:00:00.25
SQL> drop table issue_sublist;
Table dropped.
Elapsed: 00:00:00.04
我们来创建一个表存储这些数据。
SQL> create table issue_sublist as select *from issue_sublist_ext;
Table created.
Elapsed: 00:00:00.64
SQL> !exp n1/n1@xxx file=issue_sublist.dmp tables=issue_sublist buffer=910200
About to export specified tables via Conventional Path ...
. . exporting table ISSUE_SUBLIST 769951 rows exported
Export terminated successfully without warnings.
来看看dump的情况,生成的dump文件和文本的大小差不多大小。
TRUABP4 /oravl01/oracle/ext_datapump/DUMP > ll
total 24880
-rw-r--r-- 1 oracle dba 11545275 Mar 11 12:04 sub_list.log
-rw-r--r-- 1 oracle dba 357 Mar 11 12:05 ISSUE_SUBLIST_EXT_8860.log
-rw-r--r-- 1 oracle dba 13869056 Mar 11 12:07 issue_sublist.dmp
这个dump拷贝到生产中,直接imp速度是相当快的,一秒内搞定。
> time imp n1/n1 file=issue_sublist.dmp tables=issue_sublist buffer=910200 fromuser=n1 touser=cust_prsnl
. importing N1's objects into CUST_PRSNL
. . importing table "ISSUE_SUBLIST" 769951 rows imported
Import terminated successfully without warnings.
real 0m1.277s
user 0m0.133s
sys 0m0.060s
通过这个例子我们可以看到,我们尝试尽可能减少生产的负载,使用外部表间接转换为dump,会减少大量的insert语句执行,而且效率也高。可以作为一种参考。
语句类似下面的形式
SELECT subscriber_no
FROM SUBSCRIBER S
WHERE SUBSCRIBER_TYPE = 'RM'
and CONV_RUN_NO in (20, 21, 22, 23)
and not EXISTS (SELECT s.AGREEMENT_NO
FROM SERVICE_AGREEMENT S, CSM_OFFER C
WHERE s.subscriber_no = S.AGREEMENT_NO
and SOC = C.SOC_CD
and SOC_TYPE ='P'
)
这个语句的执行计划如下:
Plan hash value: 1111602366
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 22 | 56648 (1)| 00:11:20 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL | SUBSCRIBER | 2 | 22 | 56646 (1)| 00:11:20 |
| 3 | NESTED LOOPS | | | | | |
| 4 | NESTED LOOPS | | 2 | 44 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SERVICE_AGREEMENT_PK | 8 | 104 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CSM_OFFER_1IX | 1 | | 1 (0)| 00:00:01 |
|* 7 | MAT_VIEW ACCESS BY INDEX ROWID| CSM_OFFER | 1 | 9 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "PRDREFSSCA"."CSM_OFFER"
"C","PRDAPPO"."SERVICE_AGREEMENT" "S" WHERE "S"."AGREEMENT_NO"=:B1 AND "SOC"="C"."SOC_CD" AND
"SOC_TYPE"='P'))
2 - filter(("CONV_RUN_NO"=20 OR "CONV_RUN_NO"=21 OR "CONV_RUN_NO"=22 OR "CONV_RUN_NO"=23) AND
"SUBSCRIBER_TYPE"='RM')
5 - access("S"."AGREEMENT_NO"=:B1)
6 - access("SOC"="C"."SOC_CD")
7 - filter("SOC_TYPE"='P')
从语句来看是真没有多少什么调优空间了。为了想尽快得到结果,我建议加了一个hint /*+parallel(4)*/
我一再叮嘱他,这中语句最好在备份库中执行,因为备份中的数据是相对比较新的,他也同意。备份库当时负载很低,执行大概用了6分钟的样子。输出结果大约有70多万。
我以为这件事就这么过去了,结果今天早上他找到我说,希望把这70多万条记录存到一个一个临时用户下,需要关联查找更多的细节。
这种问题让人有些纠结。不过为了问题的解决,也不能完全怪他。
尝试一:
首先的一个思想就是想吐省事,在中午的时候尝试生成这些记录。
create table issue_sub_list nologging as
SELECT /*+parallel(4)*/ subscriber_no
FROM SUBSCRIBER S
WHERE SUBSCRIBER_TYPE = 'RM'
and CONV_RUN_NO in (20, 21, 22, 23)
and not EXISTS (SELECT s.AGREEMENT_NO
FROM SERVICE_AGREEMENT S, CSM_OFFER C
WHERE s.subscriber_no = S.AGREEMENT_NO
and SOC = C.SOC_CD
and SOC_TYPE ='P'
)
这条语句结果在生产上执行了将近10分钟还没有完成的意思,我查看执行计划的输出列情况,才全表扫描了30%,所以果断终止了这个操作。
这条路不建议使用,我看还有什么别的路可走。
尝试二:
一来就是对得到的数据进一步转换为insert语句。尝试在windows的ultraEdit下面编辑,因为这些id有差不多11M左右,使用文本编辑器没过一会,就提示缓存溢出,卡在那了。
尝试三:
看来文本编辑器还是很有使用限制的,linux环境下的命令方式要相对好很多。百兆上G的日志都没有问题。
我写了如下的脚本。
while read line
do
sqlplus -s n1/n1@xxx < insert into issue_sublist values( $line);
EOF
done < sub_list.log
执行的速度来看,单条语句是没有问题的,但是这条语句执行了10多分钟,还没有完,进步还是让人不够满意。
尝试四:
大家使用外部表来查看数据库的日志信息,这是一种方式,我们也可以应用到这个场景中。来看看外部表的情况。
创建了directory
create directory EXT_DATAPUMP_DIR as '/oravl01/oracle/ext_datapump/DUMP';
grant read,write on directory EXT_DATAPUMP_DIR to n1;
然后使用语句创建了外部表。
create table issue_sublist_ext(text varchar2(1000)) organization external
(type oracle_loader
default directory EXT_DATAPUMP_DIR
location('sub_list.log')
);
Table created.
Elapsed: 00:00:00.01
SQL> select count(*)from issue_sublist_ext;
COUNT(*)
----------
769685
Elapsed: 00:00:00.25
SQL> drop table issue_sublist;
Table dropped.
Elapsed: 00:00:00.04
我们来创建一个表存储这些数据。
SQL> create table issue_sublist as select *from issue_sublist_ext;
Table created.
Elapsed: 00:00:00.64
SQL> !exp n1/n1@xxx file=issue_sublist.dmp tables=issue_sublist buffer=910200
About to export specified tables via Conventional Path ...
. . exporting table ISSUE_SUBLIST 769951 rows exported
Export terminated successfully without warnings.
来看看dump的情况,生成的dump文件和文本的大小差不多大小。
TRUABP4 /oravl01/oracle/ext_datapump/DUMP > ll
total 24880
-rw-r--r-- 1 oracle dba 11545275 Mar 11 12:04 sub_list.log
-rw-r--r-- 1 oracle dba 357 Mar 11 12:05 ISSUE_SUBLIST_EXT_8860.log
-rw-r--r-- 1 oracle dba 13869056 Mar 11 12:07 issue_sublist.dmp
这个dump拷贝到生产中,直接imp速度是相当快的,一秒内搞定。
> time imp n1/n1 file=issue_sublist.dmp tables=issue_sublist buffer=910200 fromuser=n1 touser=cust_prsnl
. importing N1's objects into CUST_PRSNL
. . importing table "ISSUE_SUBLIST" 769951 rows imported
Import terminated successfully without warnings.
real 0m1.277s
user 0m0.133s
sys 0m0.060s
通过这个例子我们可以看到,我们尝试尽可能减少生产的负载,使用外部表间接转换为dump,会减少大量的insert语句执行,而且效率也高。可以作为一种参考。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1456071/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1456071/