数据库慢sql查询
--查询sql耗时时间，从大到小排序：
select timestampdiff(ss,LAST_RECV_TIME,sysdate),sf_get_session_sql(sess_id),* from SYS."V$SESSIONS" where state='ACTIVE' and sess_id<>sessid order by 1 desc;
--查询当前执行时间大于1s的session
select * from (select timestampdiff(second,s.last_recv_time,sysdate) t ,s.* from v$sessions s where state='ACTIVE') where t > 1 order by T desc;
数据库阻塞查询
with
locks as
(
select
o.name ,
l.* ,
s.sess_id ,
s.sql_text,
s.clnt_ip ,
s.last_send_time
from
v$lock l ,
sysobjects o,
v$sessions s
where
l.table_id=o.id
and l.trx_id =s.trx_id
)
,
lock_tr as
(
select trx_id wt_trxid, row_idx blk_trxid from locks where blocked=1
)
,
res as
(
select
sysdate stattime ,
t1.name ,
s.wt_trxid ,
t2.sess_id blk_sessid ,
s.blk_trxid ,
t2.clnt_ip ,
SF_GET_SESSION_SQL(t1.sess_id) fulsql ,
datediff(ss, t1.last_send_time, sysdate) ss,
t1.sql_text wt_sql
from
lock_tr s,
locks t1 ,
locks t2
where
t1.ltype ='OBJECT'
and t1.table_id<>0
and t2.ltype ='OBJECT'
and t2.table_id<>0
and s.wt_trxid =t1.trx_id
and s.blk_trxid =t2.trx_id
)
select distinct wt_sql, clnt_ip, ss, wt_trxid, blk_trxid from res;
定位锁等待问题
1.查看被挂起的事务（TRX_ID）
SELECT
TX_W.ID AS TRX_ID,
SESS.SESS_ID ,
SQL_TEXT ,
APPNAME ,
CLNT_IP
FROM
V$TRXWAIT TX_W
LEFT JOIN V$TRX TX
ON
TX_W.ID=TX.ID
LEFT JOIN V$SESSIONS SESS
ON
TX.SESS_ID=SESS.SESS_ID;
2.通过挂起事务ID（TRX_ID）找到它等待的事务（WAIT_FOR_ID）
SELECT WAIT_FOR_ID,WAIT_TIME FROM V$TRXWAIT WHERE ID=上一个sql查询出的TRX_ID;
3.通过等待事务ID（WAIT_FOR_ID）定位到连接以及执行的语句
SELECT
TX.ID AS TRX_ID,
SESS.SESS_ID ,
SQL_TEXT ,
APPNAME ,
CLNT_IP
FROM
V$TRX TX
LEFT JOIN V$SESSIONS SESS
ON
(
TX.SESS_ID=SESS.SESS_ID
)
WHERE
TX.ID = 上一个sql查询出的WAIT_FOR_ID;
4.关闭造成锁等待的SQL
SP_CLOSE_SESSION(SESS_ID);
统计信息相关
1、查询表的统计信息
select * from SYSSTATS where id in
(
select id from V$DICT_CACHE_ITEM where name='TEST'
);
2、查看统计信息
DBMS_STATS.COLUMN_STATS_SHOW('用户名','表名','列名');--根据模式名，表名和列名获得列的统计信息
DBMS_STATS.TABLE_STATS_SHOW('用户名','表名');--根据模式名，表名获得表的统计信息
DBMS_STATS.INDEX_STATS_SHOW('用户名','索引名');--根据模式名，索引名获得索引的统计信息
3、收集统计信息
DBMS_STATS.GATHER_TABLE_STATS('用户名','表名','',100,TRUE,'FOR ALL COLUMNS SIZE AUTO');--根据设定的参数，收集表、表中的列和表上的索引的统计信息

DBMS_STATS.GATHER_INDEX_STATS('用户名','索引名','',100);--根据设定的参数，收集索引的统计信息

DBMS_STATS.GATHER_SCHEMA_STATS('用户名',100,TRUE,' FOR ALL COLUMNS SIZE AUTO');--收集模式下对象的统计信息

DBMS_STATS.UPDATE_ALL_STATS;--更新已有的统计信息。
缓存计划问题
1.查询USE_PLN_POOL参数判断数据库是否开启了计划缓存：
select PARA_NAME,PARA_VALUE from v$dm_ini where para_name like 'USE_PLN_POOL';
该参数设置为非0，即开启了计划缓存。
2.通过如下语句可以查询计划缓存的情况:
select cache_item,sqlstr from v$cachepln where sqlstr like '%SQL语句%';
3.查询到对应的语句的计划缓存信息后，通过如下命令生成语句计划到本地文件：
alter session set events 'immediate trace name plndump level 查出的cache_item,dump_file ''/dbdata/plan0415.log''';
dump_file可以指定位置也可以不指定，默认在数据目录trace下面。
alter session set events ’immediate trace name plndump level 查出的cache_item’;
根据获取到的计划，我们可以确认计划是否准确，如果计划缓存中的计划存在问题，那后续的SQL在缓存中找到对应的计划后依然使用执行效率不高的计划。如果相关计划一直没有被淘汰，就有可能出现程序执行耗时很长的问题。
当遇到该种问题时，我们可以尝试清理计划缓存来丢弃执行效率不高的计划，然后让优化器根据最新的统计信息生成新的执行计划，清理的方法如下：
--清理计划缓存
call SP_CLEAR_PLAN_CACHE(cache_item);
归档日志挖掘
1.检查参数值是否为1或2
select para_value from v$dm_ini where para_name='RLOG_APPEND_LOGIC';
2.创建系统包
SP_CREATE_SYSTEM_PACKAGES(1,'DBMS_LOGMNR');
3.找出需要分析的归档文件（选归档文件暴增期间的）
SELECT CREATE_TIME,PATH FROM V$ARCH_FILE ORDER BY CREATE_TIME;
4.添加待分析的归档文件，可添加多个 （根据3步骤查找出的PATH）
DBMS_LOGMNR.ADD_LOGFILE('/mppdata/dmdata/SWX_TEST/arch/ARCHIVE_LOCAL1_20210629033108453_0.log');
DBMS_LOGMNR.ADD_LOGFILE('/mppdata/dmdata/SWX_TEST/arch/ARCHIVE_LOCAL1_20210629033250283_0.log');
如果归档日志太多，可以使用以下语句查询出来批量添加，时间由自己指定。
select
'DBMS_LOGMNR.ADD_LOGFILE('''
||PATH
||''');'
from
V$ARCH_FILE
where
create_time >= '2023-08-30 10:00:00'
and create_time <= '2023-08-30 11:00:00';
5.查看已经添加的logfile
SELECT * FROM V$LOGMNR_LOGS;
6.开始分析
DBMS_LOGMNR.START_LOGMNR(OPTIONS=>2130);
7.查看分析结果（分别把两个查询结果保存出来）
SELECT * FROM (
SELECT operation,username,substr(sql_redo,1,50) s, count(*) cnt
FROM V$LOGMNR_CONTENTS
group by operation,username,substr(sql_redo,1,50) )
WHERE CNT > 100
ORDER BY cnt DESC ;

SELECT * FROM (
SELECT operation,substr(sql_redo,1,50) s, count(*) cnt
FROM V$LOGMNR_CONTENTS
WHERE username='用户名'
group by operation,substr(sql_redo,1,50))
WHERE CNT > 100
ORDER BY cnt DESC ;
8.结束分析
DBMS_LOGMNR.END_LOGMNR();