一条SQL语句的优化学习

１．问题描述：

ECSS中有一条BI ETL的SQL语句(如下)，当S_ETL_I_IMG_26表的数据量达到15W, S_ETL_R_IMG_26表有150W后，这条SQL语句将会执行10多个小时．

DELETE　 FROM S_ETL_R_IMG_26

WHERE EXISTS

( SELECT 'X'

FROM S_ETL_I_IMG_26

WHERE S_ETL_R_IMG_26.ROW_ID = S_ETL_I_IMG_26.ROW_ID

)

２．问题分析与处理：

经过DBA优化后，这条SQL语句在数据量达到15W以后，执行所花费的时间是在一分钟以下．

以下是DBA的详细分析和优化过程．我们大家可以好好的学习一下．

==2009-6-23 DBA更新

今天上午观察，该sql已经使用上昨天导入的outline，效率很快。该问题解决了。

==2009-6-22 DBA更新

经过2009-6-19 21：00 对S_ETL_R_IMG_26表 作exp/imp，重整以后，S_ETL_R_IMG_26目前这个表大小才56M了，缩小为原来的1/10，数据空洞已经消除了。

但是今天生产库上的该sql的执行计划还是没有变，执行效率也没有提高。

进一步分析，把生产库上的S_ETL_I_IMG_26/S_ETL_R_IMG_26两个表的数据导入开发库，在开发库的执行计划是（如下），使用hash join ，效率很快，大概4分钟就完成delete 50w的记录。

开发库执行计划：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1335637332

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost

--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | 475K| 15M| | 475

| 1 | DELETE | S_ETL_R_IMG_26 | | | |

|* 2 | HASH JOIN RIGHT SEMI| | 475K| 15M| 10M| 475

| 3 | INDEX FULL SCAN | S_ETL_I_IMG_26_M2 | 475K| 5576K| |

| 4 | TABLE ACCESS FULL | S_ETL_R_IMG_26 | 1596K| 33M| | 160

--------------------------------------------------------------------------------

生产库执行计划：

SQL> select * from table(dbms_xplan.display_cursor('bs5h9z7kp1qa2', 0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID bs5h9z7kp1qa2, child number 0

-------------------------------------

DELETE FROM S_ETL_R_IMG_26 WHERE EXISTS ( SELECT 'X' FROM

S_ETL_I_IMG_26 WHERE S_ETL_R_IMG_26.ROW_ID = S_ETL_I_IMG_26.ROW_ID )

Plan hash value: 2166185037

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)

--------------------------------------------------------------------------------

| 0 | DELETE STATEMENT | | | | 111 (100)

| 1 | DELETE | S_ETL_R_IMG_26 | | |

| 2 | NESTED LOOPS SEMI | | 475K| 15M| 111 (0)

| 3 | INDEX FULL SCAN | S_ETL_R_IMG_26_M3 | 1596K| 33M| 109 (0)

|* 4 | INDEX FAST FULL SCAN| S_ETL_I_IMG_26_M2 | 141K| 1662K| 0 (0)

--------------------------------------------------------------------------------

于是进一步研究，为何该sql在开发/生产库上的执行计划不一样，发现是生产的参数不同引起。OPTIMIZER_INDEX_COST_ADJ这个参数在生产上为1，开发库为100，意思是在生产库上告诉优化器，使用index的代价为1，而在开发库上告诉优化器，使用index的代价为100，所以优化器在生产库上偏重走index，导致通过index full scan做nested loop来完成，由于S_ETL_R_IMG_26在生产库上有150万行记录，nestloop需要做150万次以上查询，故执行效率很低。

生产ecss

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj integer 1

开发ecssint

SQL> show parameter OPTIMIZER_INDEX_COST_ADJ

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_index_cost_adj integer 100

===2009-6-19 DBA更新

S_ETL_R_IMG_26这个表应该有很多空间浪费， 因为S_ETL_R_IMG_26 637M （150万条记录），S_ETL_I_IMG_26 仅9M（26万条记录），而两个表结构是一致的，这样估算，S_ETL_R_IMG_26这个表实际最多60M空间就可以了，浪费90％的空间，也有很多数据空洞。

最好作一次exp、imp，这样可以重建index也可以消除数据空洞。

SQL> select bytes/1024/1024 from dba_segments where segment_name='S_ETL_R_IMG_26';

BYTES/1024/1024

---------------

637

SQL> select bytes/1024/1024 from dba_segments where segment_name='S_ETL_I_IMG_26';

BYTES/1024/1024

---------------

9

SQL> select count(*) from siebel.S_ETL_R_IMG_26;

COUNT(*)

----------

1584586

SQL> select count(*) from siebel.S_ETL_I_IMG_26;

COUNT(*)

----------

266396

SQL>

SQL> desc siebel.S_ETL_I_IMG_26

Name Type Nullable Default Comments

---------------- ----------------- -------- ------- --------

ROW_ID VARCHAR2(15 CHAR)

LAST_UPD DATE sysdate

MODIFICATION_NUM NUMBER(10)

OPERATION VARCHAR2(1 CHAR)

SQL> desc siebel.S_ETL_R_IMG_26

Name Type Nullable Default Comments

---------------- ----------------- -------- ------- --------

ROW_ID VARCHAR2(15 CHAR)

LAST_UPD DATE sysdate

MODIFICATION_NUM NUMBER(10)

SQL>

对于这个参数OPTIMIZER_INDEX_COST_ADJ在google查了一个．

OPTIMIZER_INDEX_COST_ADJ

这个初始化参数代表一个百分比,取值范围在1到10000之间.该参数表示索引扫描和全表扫描成本的比较。缺省值100表示索引扫描成本等价转换与全表扫描成本。

这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右。

对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，

通常我们需要反复调整取得一个合理值。更为具体的可以根据统计信息,db file scattered reads/db file sequential reads来计算.

这个参数当时是Oracle 的优化工程师过来调整为1的．调整1表示使用索引的Cost是全表扫描的Cost 的1%才使用索引．

在生产环境上调整这个参数得再认真观察和评审．

这条SQL语句的优化已经不是我们增加索引所能解决的了，跟数据库的参数有非常大的关系．