影响SQL执行效率的因素主要有以下几点。1)统计信息，具体如下旧统计信息优化器参数配置错误数据库迁移前后优化器变化频繁数据量变化2)SQL语句编写问题3)游标共享问题，具体如下：变量窥探使用常量4)资源争用问题，具体表现为：硬件资源不足，lock或latch争用等。以上原因最终会导致宿主机CPU占用率升高，宿主机I/O异常繁忙，语句执行时间异常增加，数据库整体性能下降，应用超时等。01定位问题SQLSQL优化的第一步是定位问题SQL。Oracle会在内存中记录每条SQL语句执行所消耗的资源，然后有一个特殊的进程（MMON）会周期性地将这些统计指标保存为AWR快照。笔者认为AWR快照收集是Oracle相对于其他数据库的优势之一。很棒的工具。两次快照统计值的差异可以反映快照期间的资源消耗情况。Oracle可以通过指定两个快照来生成性能报告。执行Oracle内置的awrrpt脚本，根据提示一步步生成AWR报告。AWR报告对SQL有各种排名，比如SQL耗时、CPU消耗、I/O消耗、逻辑读消耗、物理读消耗、执行次数、解析次数、共享内存使用大小、子游标使用、Cluster等待要执行的排名。除了通过AWR发现异常SQL，我们还可以通过以下方式定位异常SQL。宿主机资源异常，找出top进程（topprocess），查看执行的SQL语句，查询命令如下：SQL>selectp.inst_id,p.spid,a.sid,a.serial#,a.sql_id,a.event,a.status,a.program,a.machinefromgv$sessiona,gv$processpwherea.inst_id=p.inst_idanda.paddr=p.addrandp.spid=15883；服务超时反馈，查看相应应用模块正在执行的SQL语句。数据库的整体性能下降了。检查异常等待事件。查询语句如下：SQL>selecta.inst_id,a.process,a.sid,a.serial#,a.sql_id,a.event,a.status,a.program,a.machinefromgv$sessionawhereevent='dbfilescatteredread';02SQLHealthCheck接下来介绍SQLTuningHealthCheck(SQLHC)脚本的基础知识以及如何使用它来收集有关性能不佳的SQL的关键信息。SQLHC可以帮助我们专注于特定的SQL并检查基于成本的优化器统计信息、对象元数据、配置参数和其他可能影响性能的因素。与SQLT（SQLTXPLAIN）相比，SQLHC不需要提前在数据库中配置脚本，只需要使用执行语句的SQL_ID来生成报表即可。SQLHC适用于Oracle10g及以上，也支持RAC。对于简单的SQL问题，我们可以通过执行计划判断其优化方向；对于复杂SQL问题的诊断，我们需要更多的信息，比如数据库版本信息、参数设置、表/索引/字段统计、统计信息变化、当前和历史执行计划、sql路径/sqlprofile/sqlplanbaseline等好消息是，无需运行多个脚本来收集数据，单个SQLHC就可以收集所有数据并以HTML等易于阅读的格式显示。SQLHC官方下载地址为：SQLTuningHealth-CheckScript(SQLHC)（文档编号：1366133.1）。先从MOS下载脚本代码上传到服务器，然后找到需要评估的SQL_ID，可以来自AWR、ASH报告或V$SQL视图。接下来，我们将重点介绍如何使用SQLHC。SQLHC主要包括以下三个文件。sqldx.sql：只收集信息，不做任何修改，比较常用。sqlhc.sql：收集SQL相关的数据字典信息。sqlhcxec.sql：执行SQL，然后收集信息，会产生变更操作。要执行上面的脚本，我们需要有DBA或者访问数据字典查看的权限。操作也非常简单。只需要将sqlhc.sql上传到数据库服务器指定的目录下执行即可。具体方法如下：shell>unzipsqlhc.zipArchive:sqlhc.zipcreating:sqlhc/inflating:sqlhc/sqlhc.sqlinflating:sqlhc/sqldx。sqlinflating:sqlhc/sqlhcxec.sqlshell>cdsqlhcshell>ls-tlrtotal2-rw-r--r--1mfggprddba48747Nov112013sqldx.sql-rw-r--r--1mfggprddba288298Apr162014sqlhc.sql-rw-r--rec--1mfggprddba48747Nov112013sqldx.sql-rw-r--r--1mfggprddba288298Apr162014sqlhc.sql-rw-r--rec--1mfggprddba2mfgg2sqlprshelld6A2mfgg2sqlprshelld6A2执行时/assysdbaSQL>@sqlhc.sqlTd18wwg2f3txc0，需要输入以下两个参数。OraclePack许可证（调整、诊断或无）[T|D|N]（必需）。T：调音，比较常用。D：诊断。纽：没有。SQL_ID：需要诊断的SQL语句。下面以SQLd18wwg2f3txc0为例。执行后会自动打包生成一个压缩文件sqlhc_20200303_1555_d18wwg2f3txc0.zip。生成的内容包括health_check、diagnostics、executionplan、sql_detail、10053trace、sqldx、SQLmonitor（可选），如图1所示。图1SQLHC.SQL执行后生成的文件图1中的部分内容解释如下。health_check：用于诊断，以及统计信息异常、索引异常、特殊参数设置等诊断：内容包括SQL文本、执行计划绑定、当前和历史SQL信息、表/列/索引/列直方图详情、系统参数、建表语句、SQL相关等待事件等。执行计划：SQL文本、当前和历史执行计划。sqldx：SQL绑定、直方图、每个对象的数据字典信息等。10053Trace：SQL是如何执行的。03SQLPROFILESQLprofile是查询中辅助信息的集合，包括查询中引用的所有表和列。SQL配置文件存储在数据字典中，优化器在优化过程中使用这些信息来确定最优计划。1.使用coe_xfr_sql_profile.sql1)运行分析脚本，命令如下：shell>sqlplus"/assysdba"SQL>@coe_xfr_sql_profile.sqlcdwjdd67x27mh2)输入想要的执行计划哈希值，命令如下：SQL>SQL_ID(required)PLAN_HASH_VALUEAVG_ET_SECS------------------------2979024279.0116478551115.164从上面的输出结果来看，2979024279对应的执行计划响应时间比较快。Parameter2:PLAN_HASH_VALUE(required)Entervaluefor2:2979024279Valuespassed:~~~~~~~~~~~~~SQL_ID:"cdwjdd67x27mh"PLAN_HASH_VALUE:"2979024279"Executecoe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql3)输出结果如下：SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql4)检查profile健康）状况。通过查询dba_sql_profiles视图查看具体固化情况，命令如下：SQL>selectname,SQL_TEXT,statusfromdba_sql_profiles;5)删除配置文件。在dba_sql_profiles中输入对应的profile名称进行删除，命令如下：SQL>execdbms_sqltune.drop_sql_profile('coe_f4sgavkagjb1q_2593387201');2.使用SQL调优建议工具1)运行SQL调优建议工具(SQLTuningAdvisor)。带上问题SQL_ID，命令如下：SQL>vartuning_taskvarchar2(100);SQL>DECLARE2l_sql_idv$session.prev_sql_id%TYPE;3l_tuning_taskVARCHAR2(30);4BEGIN5l_sql_id:='gg859wbj3hkfq';6l_tuning_task:=dbms_sqltune.create_tuning_id=task)7:tuning_task:=l_tuning_task;8dbms_sqltune.execute_tuning_task(l_tuning_task);9dbms_output.put_line(l_tuning_task);10END;11/PL/SQLproceduresuccessfullycompleted.SQL>SQL>printtuning_task;TUNING_TASK------------TASK_8233查看建议内容，查询语句如下：SQL>SELECTdbms_sqltune.report_tuning_task('TASK_8233')FROMdual;2)接受SQL配置文件。根据SQL调优建议工具提供的建议，接受SQLprofile。命令如下：SQL>execdbms_sqltune.accept_sql_profile(task_name=>'TASK_8193',replace=>TRUE,force_match=>true);SQLprofile有两个建议，所以有些情况下只需要接受SQLprofile，有些情况下需要同时采用创建索引和接受SQLprofile这两个建议。因为创建索引后，数据库可能需要借助SQLprofile来选择新的索引。3)查看SQL配置文件。数据字典视图DBA_SQL_PROFILES可以用来显示数据库中存储的SQL配置文件，命令如下：SQL>SELECTNAME,SQL_TEXT,CATEGORY,STATUSFROMDBA_SQL_PROFILES;4）删除SQL配置文件，命令如下：SQL>BEGINDBMS_SQLTUNE.DROP_SQL_PROFILE(name=>'sql_profile');END;/04SQL计划管理Oracle11gR1引入了SQL计划管理（SPM，SQLPlanManagement）。SPM是一种预防机制，使优化器能够自动管理执行计划，从而确保数据库使用已知或通过验证的最优计划。当系统启用自动SQLPlanBaseline（SQL计划基线）捕获时，CBO会记录会话中执行的任何SQL，并将SQL相关信息存储为SQL计划基线。第一次执行的语句会被认为是最优执行计划，因为没有基础数据。在第二次执行时，CBO将与存储在SQL计划基线中的计划进行比较。如果新执行计划的性能得到提升，SPM会将新执行计划标记为该语句的最优执行计划。默认情况下，CBO将使用SQL计划基线中的最优执行计划。对于异常SQL的自动捕获，需要将参数optimizer_capture_sql_plan_baselines的值设置为true，默认为false，命令如下：SQL>altersystemsetoptimizer_capture_sql_plan_baselines=truescope=spfile;接下来，我们将演示如何将SQL执行计划手动加载到SQL计划基线优化案例中。1）执行SQL语句，命令如下：SQL>setautotraceonSQL>setline150pagesize0SQL>select*fromscott.deptwheredeptno=10;10YDFDNEWYORKExecutionPlan-----------------------------------------------------------Planhashvalue:2852011669------------------------------------------------------------------------------|Id|操作|名称|行|字节|成本（%CPU）|时间|------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|19|1(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|DEPT|1|19|1(0)|00:00:01||*2|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:00:01|找到对应的SQL_ID和PLANHASHVALUE，命令如下：SQL>selectsql_id,plan_hash_valuefromv$sqlwheresql_textlike'%scott.deptwheredeptno=10%';SQL_IDPLAN_HASH_VALUE----------------------------fxchug4tpuqcj28520116692)加载上面SQL语句到SQLplanbaseline，命令如下：SQL>varnnumberSQL>begin2:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'fxchug4tpuqcj',plan_hash_value=>2852011669,fixed=>'NO',enabled=>'YES');3end;4/PL/SQLproceduresuccessfullycompleted.3）查看DBA_SQL_PLAN_BASELINES视图确认情况，命令如下：SQL>selectSQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,SQL_TEXTfromdba_SQL_PLAN_BASELINESwhereACCEPTED='YES'orderbyLAST_MODIFIED;SQL_HANDLEPLAN_NAMEENABLEDACCEPTED----------------------------------------------------------------------SQL_59f9d6822a74ea01SQL_PLAN_5myfqh8p79uh10348d329YESYES
