1.为什么要使用sql优化器
对于oralce数据库我们一般的定位思路压测过程中拉一份AWR报告，查看报告中资源消耗或者执行时间比较靠前的sql，然后分析他的执行计划
但是对于一般的测试人员很难准确的给出sql存在的问题以及调优思路，最重要的给不出sql优化以后，执行效率对比结果。针对此问题，oracle
这套sql优化器作用就显得尤为明显了。
2.如何使用sql优化器
执行方式：
1、创建.sql脚本，将如下代码放入脚本中；
2、修改sql_id及task_name保存；
3、上传至数据库服务器；
4、进入数据库后进入DBA模式，使用@/path/*.sql命令执行脚本。
tips:脚本中的sql_为需要分析的sqlId，可通过AWR等途径获取，task_name有唯一键约束，不能重复。
?
优化器脚本：
variable task_li_test varchar2(4000);
exec :task_li_test :=DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id=>'8xjxd3t3xs2bg',task_name=>'task_04');//sqlId为需要分析的sql
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(TASK_NAME=>'task_04');
select a.owner,b.task_id,b.task_name,a.created from dba_advisor_tasks a,dba_advisor_log b where a.task_id=b.task_id and a.task_name='task_04';?//task_name有唯一键约束，不能重复,可从_01开始
set long 99999
select dbms_sqltune.report_tuning_task('task_04') from dual;
?
3.sql优化器执行以后，会给出分析sql的优化建议，例如：
优化建议：
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 98.18%) //此sql优化后预计优化效率提升
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_02')
--------------------------------------------------------------------------------
or creating the recommended index. If you choose to create the //优化建议，针对此sql，优化器给出的调优方案，此方案可直接反馈给项目组
recommended index, consider dropping the index "RPT"."JSCP_PCC_BMZ_QSH"
because it is a prefix of the recommended index.
create index RPT.IDX$$_00980001 on RPT.HZB_JSCPART1_PROCCALICUGP_BMZ("QSH",
"BRANCH_COMPANY_CODE","FLAG","PRODUCT_GROUP_NAME","PRODUCT_FLAG","KJ_FLAG")
;
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_02')
--------------------------------------------------------------------------------
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
?
