几个有用的oracledba_hist_*查询语句耗CPU最多的10条语句select*from(selects.SQL_ID,sum(s.CPU_TIME_DELTA),sum(s.DISK_READS_DELTA),count(*)fromDBA_HIST_SQLSTATsgroup...
几个有用的oracle dba_hist_*查询语句
耗CPU最多的10条语句
select * from
(select
s.SQL_ID,
sum(s.CPU_TIME_DELTA),
sum(s.DISK_READS_DELTA),
count(*)
from DBA_HIST_SQLSTAT s
group by s.SQL_ID
order by sum(s.CPU_TIME_DELTA) desc
) www.2cto.com
where rownum <11
/
最近7天，指定时间段（8:00-16:00）最消耗CPU的10条语句
select * from
(select
s.SQL_ID,
sum(s.CPU_TIME_DELTA),
sum(s.DISK_READS_DELTA),
count(*)
from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p
where 1=1
and s.SNAP_ID = p.SNAP_ID
and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 8 and 16
and p.END_INTERVAL_TIME between SYSDATE-7 and SYSDATE
group by s.SQL_ID
order by sum(s.CPU_TIME_DELTA) desc
) www.2cto.com
where rownum <11
/
可以进一步关联DBA_HIST_SQLTEXT视图得到详细的SQL语句
select * from
(select
s.SQL_ID, s.SQL_TEXT
sum(s.CPU_TIME_DELTA),
sum(s.DISK_READS_DELTA),
count(*)
from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p, DBA_HIST_SQLTEXT t
where 1=1
and s.SNAP_ID = p.SNAP_ID
and s.SQL_ID = t.SQL_ID
and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 8 and 16
and t.COMMAND_TYPE != 47 –- Exclude PL/SQL blocks from output
and p.END_INTERVAL_TIME between SYSDATE-7 and SYSDATE
group by s.SQL_ID
order by sum(s.CPU_TIME_DELTA) desc
)
where rownum <11
/ www.2cto.com
分析指定SQL语句各版本执行计划的资源消耗情况
select st.SQL_ID,
st.PLAN_HASH_VALUE,
sum(st.EXECUTIONS_DELTA) EXECUTIONS,
sum(st.ROWS_PROCESSED_DELTA) CROWS,
trunc(sum(st.CPU_TIME_DELTA)/1000000/60) CPU_MINS,
trunc(sum(st.ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT st
where st.SQL_ID in (‘&1′)
group by st.SQL_ID , st.PLAN_HASH_VALUE
order by st.SQL_ID, CPU_MINS;
分析对比SQL语句在不同执行计划中的执行情况
SELECT st2.SQL_ID,
st2.PLAN_HASH_VALUE,
st_long.PLAN_HASH_VALUE l_PLAN_HASH_VALUE,
st2.CPU_MINS,
st_long.CPU_MINS l_CPU_MINS,
st2.ELA_MINS,
st_long.ELA_MINS l_ELA_MINS,
st2.EXECUTIONS, www.2cto.com
st_long.EXECUTIONS l_EXECUTIONS,
st2.CROWS,
st_long.CROWS l_CROWS,
st2.CPU_MINS_PER_ROW,
st_long.CPU_MINS_PER_ROW l_CPU_MINS_PER_ROW
FROM
(SELECT st.SQL_ID,
st.PLAN_HASH_VALUE,
SUM(st.EXECUTIONS_DELTA) EXECUTIONS,
SUM(st.ROWS_PROCESSED_DELTA) CROWS,
TRUNC(SUM(st.CPU_TIME_DELTA) /1000000/60) CPU_MINS , www.2cto.com
DECODE( SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW ,
TRUNC(SUM(st.ELAPSED_TIME_DELTA) /1000000/60) ELA_MINS
FROM DBA_HIST_SQLSTAT st
WHERE 1 =1
AND ( st.CPU_TIME_DELTA !=0
OR st.ROWS_PROCESSED_DELTA !=0)
GROUP BY st.SQL_ID,
st.PLAN_HASH_VALUE
) st2, www.2cto.com
(SELECT st.SQL_ID,
st.PLAN_HASH_VALUE,
SUM(st.EXECUTIONS_DELTA) EXECUTIONS,
SUM(st.ROWS_PROCESSED_DELTA) CROWS,
TRUNC(SUM(st.CPU_TIME_DELTA) /1000000/60) CPU_MINS ,
DECODE( SUM(st.ROWS_PROCESSED_DELTA), 0 , 0 , (SUM
(st.CPU_TIME_DELTA)/1000000/60)/SUM(st.ROWS_PROCESSED_DELTA) ) CPU_MINS_PER_ROW ,
TRUNC(SUM(st.ELAPSED_TIME_DELTA) /1000000/60) ELA_MINS www.2cto.com
FROM DBA_HIST_SQLSTAT st
WHERE 1 =1
AND ( st.CPU_TIME_DELTA !=0
OR st.ROWS_PROCESSED_DELTA !=0)
HAVING TRUNC(SUM(st.CPU_TIME_DELTA)/1000000/60) > 10
GROUP BY st.SQL_ID,
st.PLAN_HASH_VALUE
) st_long
WHERE 1 =1
AND st2.SQL_ID = st_long.SQL_ID
AND st_long.CPU_MINS_PER_ROW/DECODE(st2.CPU_MINS_PER_ROW,0,1,st2.CPU_MINS_PER_ROW) > 2 www.2cto.com
ORDER BY l_CPU_MINS DESC,
st2.SQL_ID,
st_long.CPU_MINS DESC,
st2.PLAN_HASH_VALUE;
![如何从python读取sql[mysql基础教程]](https://img1.php1.cn/3cd4a/24de0/3b4/77028e3ebbb95f60.jpeg)
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有