SQL运行慢问题分析

查询活动会话中执行时间大于1s的SQL

select * from (
select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* 
from v$sessions s  where state='ACTIVE')
where t > 1

检查当前会话

--查询当前所有会话数
select count(*) from v$sessions;
--查看当前数据库中活动会话
select count(*) from v$sessions where state='ACTIVE';
--查看当前空闲会话
select count(*) from v$sessions where state='IDLE';
--结束会话
sp_close_session(sess_id);

看看有没有阻塞

SELECT
        DS.SESS_ID "被阻塞的会话ID",
        DS.SQL_TEXT "被阻塞的SQL",
        DS.TRX_ID "被阻塞的事务ID",
        (CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
        DS.CREATE_TIME "开始阻塞时间",
        SS.SESS_ID "占用锁的会话ID",
        SS.SQL_TEXT "占用锁的SQL",
        SS.CLNT_IP "占用锁的IP",
        L.TID "占用锁的事务ID"
FROM
        V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
        DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
        SS.TRX_ID = L.TID
WHERE
        L.BLOCKED = 1

-- 杀掉占用锁的会话，释放锁。
SP_CLOSE_SESSION ( 占用锁的会话 ID );
-- 杀掉被阻塞的会话。
SP_CLOSE_SESSION ( 被阻塞的会话 ID );

创建索引并收集统计信息

--创建索引
CREATE INDEX idx_name ON table_name (column1, column2);
--收集所有统计信息
STAT 100 ON "idx_name";
--收集10000行统计信息
stat(b_sub,10000);

收集其他统计信息

--收集指定用户下所有表所有列的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('username',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集指定用户下所有索引的统计信息：
DBMS_STATS.GATHER_SCHEMA_STATS('usename',1.0,TRUE,'FOR ALL INDEXED SIZE AUTO');
--或 收集单个索引统计信息：
DBMS_STATS.GATHER_INDEX_STATS('username','IDX_T2_X');

--收集指定用户下某表统计信息：
DBMS_STATS.GATHER_TABLE_STATS('username','table_name',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

--收集某表某列的统计信息：
STAT 100 ON table_name(column_name);

查看执行计划，看看是哪个步骤较慢，试试改变策略

一些可能会用到的优化参数

-- 单个/总共的分组、嵌套连接、集合运算、分析函数运算、统计信息、DISTINCT、临时表、TOP、ESEND操作符的数据总缓存大小
HAGR_BUF_SIZE(1024)/GLOBAL_SIZE(20480) 
-- 单个/总共的哈希连接操作符数据总缓存
HJ_BUF_SIZE(1024)/GLOBAL_SIZE(20480) 
-- 禁用哈希连接
ENABLE_HASH_JOIN(0)
-- 否允许 IN LIST 表达式优化
ENABLE_IN_VALUE_LIST_OPT
-- 对单表条件是否下放的不同处理方式
FILTER_PUSH_DOWN(1)
-- BDTA 缓存的记录数
BDTA_SIZE() 
-- 是否使用代价计算来决定使用的通讯操作符，0：否；1：是。
NEW_MOTION(0)

如果是带参数的函数，试试直接把参数写入函数，看看是不是还是很慢
清除内存中的缓存计划

sp_clear_plan_cache();
sp_clear_plan_cache(pln号);

参考文档：http://eco.dameng.com/document/dm/zh-cn/ops/instance-monitor

作者