站长工具在线免费观看百度推广下载安装
一 问题描述
有次生产环境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;
*/
2.3 查看下当前会话
看是否还有相关慢sql。收集该表的统计信息和固化执行计划不会影响已经在执行的sql。
这些查询sql如果执行了好几个小时的话，问下业务能否kill。运行那么久没有结果感觉查询下去也没啥意义了，但是还是要谨慎kill，需要问下业务能否kill。