目录
获取慢SQL方式
--获取目前执行时间较长sql:
select datediff(ss, last_recv_time, sysdate) ss ,
dbms_lob.substr(sf_get_session_sql(sess_id)), --抓取sql的全部内容
curr_sch,
sess_id,
substr(clnt_ip, 8, instr(clnt_ip,':',8)-8)
from v$sessions
where state='ACTIVE'
order by 1 desc;
--查询最近10000条SQL历史记录,并按照耗时倒序排列。
select * from v$sql_history order by time_used desc;
--查询,显示系统自启动以来执行时间最长的300条SQL语句。
SELECT * FROM V$SYSTEM_LONG_EXEC_SQLS ORDER BY EXEC_TIME DESC;
--历史sql EXEC_TIME 执行时间(MS) MAX_MEM_USED 内存使用峰值(K)。
select SQL_TXT,EXEC_TIME,MAX_MEM_USED/1024 from V$SQL_STAT_HISTORY order by 3 desc;
--目前sql。
select SQL_TXT,EXEC_TIME,MAX_MEM_USED/1024 from V$SQL_STAT order by 3 desc;
--统计历史慢SQL执行情况:
select sql_text,
COUNT(0) 执行次数,
max(EXEC_TIME) 最大执行时间,
TRUNC(avg(exec_time),2) 平均执行时间
from v$long_exec_sqls a
group by sql_text
order by COUNT(0) desc,
max(EXEC_TIME);
慢SQL处理方法
1 慢sql应急处理方法
--清理执行计划:
SP_CLEAR_PLAN_CACHE(plan_id);
plan_id:指定计划 ID,其值可以从动态视图 V$CACHEPLN 中的 CACHE_ITEM 列获
得。
select CACHE_ITEM, from SYS."V$CACHEPLN" where sqlstr like '%LEFT JOIN tss.QN_TELEPHONE_RECORD_TASK%';
--查杀执行时间超过 300s:
select datediff(ss, last_recv_time, sysdate) ss ,
dbms_lob.substr(sf_get_session_sql(sess_id)), --抓取sql的全部内容
'sp_close_session('||sess_id||');'
from v$sessions
where state='ACTIVE' and datediff(ss, last_recv_time, sysdate) >100;
2 慢sql日常处理方法
1)更新统计信息
分析表数据量与统计信息下的数据量差值是否较大,较大时需要重新收集统计信息:
select count(*) from SCHEMA.TABLENAME;--查询表的数据量
--统计信息查询
dbms_stats.table_stats_show('SCHEMA','TABLENAME');--查询表的数据量
dbms_stats.index_stats_show('SCHEMA','INDEXNAME');--查询索引的数据量
dbms_stats.column_stats_show('SCHEMA','TABLENAME','COLUMNNAME');--查询字段数据量
--更新表的统计信息
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLENAME', NULL, 100, TRUE, 'FOR ALL COLUMNS SIZE AUTO');
--更新索引的统计信息
DBMS_STATS.GATHER_INDEX_STATS('SCHEMA',REC.INDEX_NAME,NULL,100);
--服务器使用脚本更新统计信息
/opt/dmdbms/jdk/bin/java -jar ./statcmd-1.0.4.jar 16 SCHEMA TABLENAME 0 SYSDBA/'密码'@IP:端口
注:更新统计信息尽量在中午或晚上执行,避免跟业务操作时间重合,影响业务使用。
2)添加索引
当查询sql比较慢,执行计划中显示走的全表扫描,可以针对条件字段添加索引或者组合索引。
--普通索引
CREATE INDEX "IDXNAME" ON "'SCHEMA'"."TABLENAME"("'COLUMNNAME'" ASC) ONLINE;
--组合索引
CREATE INDEX "IDXNAME" ON "'SCHEMA'"."TABLENAME"("'COLUMNNAME1'" ASC,"'COLUMNNAME2'" ASC) ONLINE;
3)添加hint
通过执行计划,发现部分表有索引但走的全表扫描,或者条件中有or之类的判断时,可以通过添加hint方式,改变sql的执行计划,优化执行效率。
常用的hint:
-
/*+INDEX(TABLENAME, IDX_NAME)*/--强制使用索引;
-
/*+ENABLE_HASH_JOIN(0)*/--禁用hash join关联;
-
/*+OPTIMIZER_OR_NBEXP(2)*/--条件中含有or时可以使用;
-
--/*+ENABLE_RQ_TO_NONREF_SPL(3)*/--执行计划中有SPL时使用。
4)绑定hint
实际生产中,部分慢SQL执行频率较高、业务短期内没有升级需求或者其他特殊情况,无法及时处理,可以通过模糊绑定hint方式,使sql强制走hint的执行计划。
--模糊绑定 部份SQL:
SF_INJECT_HINT('SQL_TEXT ','HINT_TEXT', 'add_optimize_or01', '', TRUE, NULL);
-
SQL_TEXT为需要添加hint的sql文本,必须跟查出来的慢SQL结构保持一致,且尽量保证不与其他sql相同。
-
HINT_TEXT是具体绑定的hint内容,如ENABLE_HASH_JOIN(0)。
-
add_optimize_or01是绑定的名称,类似主键ID。
--查询所有绑定的sql:
select * from SYSINJECTHINT;