一问题描述

有次生产环境cpu使用率增高，ADDM报告提示某条sql比较耗费cpu：

提示：

在分析期间, 此 SQL 语句至少利用了 6 个不同的执行计划

#查看该sql都有哪些执行计划

SELECT *

FROM table(DBMS_XPLAN.DISPLAY_AWR('sqlid值'));

我手动执行这个sql需要5秒。但是我用sql monitor查看发现它执行得超慢，5个多小时了，还没执行完：

发现99%慢在一个表的全表扫描上，但是这个条件字段上是有索引的，它没走索引，走的全表扫描：

我手动执行这个sql（5秒），看它的执行计划这个表是走了索引的。

说明数据库有时选择了错误的执行计划。

二解决办法

2.1 收集下这个表的统计信息

BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname     => 'schema名称',     -- 表所属的用户（如 HR）tabname     => '表名',      -- 表名（如 EMPLOYEES）estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 自动估算采样比例method_opt  => 'FOR ALL COLUMNS SIZE AUTO',      -- 自动选择直方图列cascade     => TRUE,             -- 同时收集索引的统计信息（默认为 FALSE）degree      => 8,no_invalidate => FALSE           -- 立即使依赖的游标失效（可选）);END;/

#查看统计信息

select table_name,num_rows,blocks,last_analyzed from dba_tables where table_name in ('表名') and owner='schema名称';

2.2 固化执行计划

如果收集完表的统计信息，sql还是慢，则固化下执行计划

2.2.1 上传文件coe_xfr_sql_profile.sql

点击这里下载该文件，然后用oracle用户将其上传至/home/oracle目录下

2.2.2 执行coe_xfr_sql_profile.sql，并手动执行其生成的固化sql

# su - oracle

$ sqlplus / as sysdba @/home/oracle/coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 7arsymf6aatr3 >>>入参1：此处输入需要固化的SQL_ID

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2931187647 .036 >>> 一般选择AVG_ET_SECS最小版本,并记住PLAN_HASH_VALUE值：2931187647
3394618928 42.585
894327090 164.624

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 2931187647 >>>入参2：此处输入上面AVG_ET_SECS最小耗费资源的PLAN_HASH_VALUE 值

Values passed:
~~~~~~~~~~~~~
SQL_ID : "7arsymf6aatr3"
PLAN_HASH_VALUE: "2931187647"

Execute coe_xfr_sql_profile_7arsymf6aatr3_2931187647.sql >>>>此处为oracle自动生成的SQL固化脚本
on TARGET system in order to create a custom SQL Profile
with plan 2931187647 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.
SQL> @coe_xfr_sql_profile_7arsymf6aatr3_2931187647 >>>>手动执行上面生成的sql脚本。
... ...
PL/SQL procedure successfully completed.

... ...
COE_XFR_SQL_PROFILE_7arsymf6aatr3_2931187647 completed >>>>固化完成。

select * from dba_sql_profiles;