CRM2.0的客户资料查询中，有一个语句在AWR报告的TOP SQL中总是靠前，从个人掌握的调优手段上来看，我先是对小表创建了索引，然后将小表钉到了keep池，如下：
select *
from (select p.*, rownum rownum1
from (select a.CUST_ID as custId,
a.PARTY_ID as ptyId,
a.LATN_ID as latnId,
a.PTY_LATN_ID as ptyLatnId,
a.MKT_CHANNEL_ID as mktChannelId,
a.cust_type as mgmtChannelId,
getOrgNameById(a.REGION_ID) as regionName,
a.CUST_NAME as custName,
a.CUST_NUMBER as custCd,
a.CUST_ADDRESS as addrDesc,
a.STATUS_CD as custState,
(select code_name
from crm551.tb_pty_code
where code = a.STATUS_CD
and code_type = 'CSD') custStateName,
a.CITY_OR_COUNTY_FLAG as cityOrCountyFlag,
(select z.code_name
from crm551.tb_pty_code z
where a.MKT_CHANNEL_ID = z.code
and code_type = 'CHN') as mktChannelName,
(select z.code_name
from crm551.tb_pty_code z
where a.CUST_TYPE = z.code
and code_type = 'CHN') as mgmtChannelName
from crm551.CUST_551 a
WHERE 1=1
and a.cust_number ='100000000128') p
where rownum <=10)
where rownum1 >= 1;
create index crm551.idx_pty_code on crm551.tb_pty_code(code);
alter table crm551.tb_pty_code storage(buffer_pool keep);
alter index crm20_test.idx_pty_code storage(buffer_pool keep);
通过autotrace根据，发现每次执行，需要消耗81个逻辑读：
就目前个人的调优手段上来说，已经江郎才尽，所以想借助于SQL PROFILE来试下，下面是测试过程。
第一步：收集表的统计信息，以有效支撑CBO：
提示：刚因为tb_pty_code只是在模式crm551上创建了同义词，实际上它是crm20_dba模式下的对象，因此在执行dbms_stats.gather_table_stats时，报20000的错误。
第二步：使用SQL Tuning Advisor来尝试这条SQL：
1）创建优化任务
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext :='select *
from (select p.*, rownum rownum1
from (select a.CUST_ID as custId,
a.PARTY_ID as ptyId,
a.LATN_ID as latnId,
a.PTY_LATN_ID as ptyLatnId,
a.MKT_CHANNEL_ID as mktChannelId,
a.cust_type as mgmtChannelId,
getOrgNameById(a.REGION_ID) as regionName,
a.CUST_NAME as custName,
a.CUST_NUMBER as custCd,
a.CUST_ADDRESS as addrDesc,
a.STATUS_CD as custState,
(select code_name
from crm551.tb_pty_code
where code = a.STATUS_CD
and code_type = ''CSD'') custStateName,
a.CITY_OR_COUNTY_FLAG as cityOrCountyFlag,
(select z.code_name
from crm551.tb_pty_code z
where a.MKT_CHANNEL_ID = z.code
and code_type = ''CHN'') as mktChannelName,
(select z.code_name
from crm551.tb_pty_code z
where a.CUST_TYPE = z.code
and code_type = ''CHN'') as mgmtChannelName
from crm551.CUST_551 a
WHERE 1=1
and a.cust_number =''100000000128'') p
where rownum <=10)
where rownum1 >= 1';
my_task_name :=dbms_sqltune.create_tuning_task (sql_text=>my_sqltext,
user_name =>user,
scope =>'COMPREHENSIVE',
time_limit =>60,
task_name =>'sql_tuning_test',
description =>'Tuning Task'
);
END;
/
2）执行优化的任务：
exec dbms_sqltune.execute_tuning_task('sql_tuning_test');
3）检查优化任务的状态
select * from user_advisor_tasks where task_name='sql_tuning_test';
4)得到优化任务执行的结果
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
SQL> SET LONG 999999
SQL> set serveroutput on size 999999
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_test
Tuning Task Owner : CRM551
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 02/22/2011 14:03:04
Completed at : 02/22/2011 14:03:07
Number of SQL Restructure Findings: 2
-------------------------------------------------------------------------------
Schema Name: CRM551
SQL ID : 0zykymvbk78ft
SQL Text : select *
from (select p.*, rownum rownum1
from (select a.CUST_ID as custId,
a.PARTY_ID as ptyId,
a.LATN_ID as latnId,
a.PTY_LATN_ID as ptyLatnId,
a.MKT_CHANNEL_ID as mktChannelId,
a.cust_type as mgmtChannelId,
getOrgNameById(a.REGION_ID) as
regionName,
a.CUST_NAME as custName,
a.CUST_NUMBER as custCd,
a.CUST_ADDRESS as addrDesc,
a.STATUS_CD as custState,
(select code_name
from crm551.tb_pty_code
where code = a.STATUS_CD
and code_type = 'CSD') custStateName,
a.CITY_OR_COUNTY_FLAG as
cityOrCountyFlag,
(select z.code_name
from crm551.tb_pty_code z
where a.MKT_CHANNEL_ID = z.code
and code_type = 'CHN') as
mktChannelName,
(select z.code_name
from crm551.tb_pty_code z
where a.CUST_TYPE = z.code
and code_type = 'CHN') as
mgmtChannelName
from crm551.CUST_551 a
WHERE 1=1
and a.cust_number ='100000000128') p
where rownum <=10)
where rownum1 >= 1
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TO_NUMBER("Z"."CODE")=:B1 used at line ID 3 of the execution
plan contains an implicit data type conversion on indexed column "CODE".
This implicit data type conversion prevents the optimizer from selecting
indices on table "CRM20_DBA"."TB_PTY_CODE".
Recommendation
--------------
- Rewrite the predicate into an equivalent form. to take advantage of
indices.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate TO_NUMBER("Z"."CODE")=:B1 used at line ID 3 of the execution
plan contains an implicit data type conversion on indexed column "CODE".
This implicit data type conversion prevents the optimizer from selecting
indices on table "CRM20_DBA"."TB_PTY_CODE".
Recommendation
--------------
- Rewrite the predicate into an equivalent form. to take advantage of
indices.
Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 6 of the execution plan.
The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3312946380
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 236
|* 1 | TABLE ACCESS BY INDEX ROWID | TB_PTY_CODE | 1 | 1
|* 2 | INDEX RANGE SCAN | IDX_PTY_CODE | 3 |
|* 3 | TABLE ACCESS FULL | TB_PTY_CODE | 1 | 1
|* 4 | TABLE ACCESS BY INDEX ROWID | TB_PTY_CODE | 1 | 1
|* 5 | INDEX RANGE SCAN | IDX_PTY_CODE | 3 |
|* 6 | VIEW | | 1 | 236
|* 7 | COUNT STOPKEY | | |
| 8 | TABLE ACCESS BY INDEX ROWID| CUST_551 | 1 | 10
|* 9 | INDEX RANGE SCAN | UIDX_CUST_551_CUST_NUMBER | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODE_TYPE"='CSD')
2 - access("CODE"=:B1)
3 - filter("CODE_TYPE"='CHN' AND TO_NUMBER("Z"."CODE")=:B1)
4 - filter("CODE_TYPE"='CHN')
5 - access("Z"."CODE"=:B1)
6 - filter("ROWNUM1">=1)
7 - filter(ROWNUM<=10)
9 - access("A"."CUST_NUMBER"='100000000128')
-------------------------------------------------------------------------------
很强大而牛B的结果，居然指出了因为一个字段上的类型不一致导致了索引无法使用，经检查，发现是CUST_551表的MKT_CHANNEL_ID字段为number型，而tb_pty_code为varchar型，两种字符类型不同，导致在tb_pty_code字段上的索引无法使用，将SQL语句调整为：
select *
from (select p.*, rownum rownum1
from (select a.CUST_ID as custId,
a.PARTY_ID as ptyId,
a.LATN_ID as latnId,
a.PTY_LATN_ID as ptyLatnId,
a.MKT_CHANNEL_ID as mktChannelId,
a.cust_type as mgmtChannelId,
getOrgNameById(a.REGION_ID) as regionName,
a.CUST_NAME as custName,
a.CUST_NUMBER as custCd,
a.CUST_ADDRESS as addrDesc,
a.STATUS_CD as custState,
(select code_name
from crm551.tb_pty_code
where code = a.STATUS_CD
and code_type = 'CSD') custStateName,
a.CITY_OR_COUNTY_FLAG as cityOrCountyFlag,
(select z.code_name
from crm551.tb_pty_code z
where to_char(a.MKT_CHANNEL_ID) = z.code
and code_type = 'CHN') as mktChannelName,
(select z.code_name
from crm551.tb_pty_code z
where a.CUST_TYPE = z.code
and code_type = 'CHN') as mgmtChannelName
from crm551.CUST_551 a
WHERE 1=1
and a.cust_number ='100000000128') p
where rownum <=10)
where rownum1 >= 1
再来看：
1）执行计划：
2）统计信息：
看，逻辑读一下子从81降到了66，优化的效果很明显
6：删除优化任务
exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
捕获1.JPG来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12350275/viewspace-687719/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12350275/viewspace-687719/
1124
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
