JAVA面试题分享三百八十七：慢SQL处理方案

目录

获取慢SQL方式

慢SQL处理方法

1 慢sql应急处理方法

2 慢sql日常处理方法

---

获取慢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;