1.查询AWR报告:
select s.snap_id,
s.dbid,
s.instance_number,
to_char(s.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') cdate
from dba_hist_snapshot s
order by snap_id desc;
select dbms_workload_repository.awr_report_html(1594936874,
1,
153940,
153941)
from dual;
select dbms_workload_repository.ash_report_html(1594936874,
1,
to_date('2015-07-17 10:00:00',
'yyyy-mm-dd hh24:mi:ss'),
to_date('2015-07-17 10:05:00',
'yyyy-mm-dd hh24:mi:ss'))
from dual;
2.查询ASH报告:
select dbms_workload_repository.ash_report_html(l_dbid => 2288065565,
l_inst_num => 1,
l_btime => to_date('2015-12-17 12:00:00',
'yyyy - mm - dd hh24 :mi :ss '),
l_etime => to_date('2015-12-17 12:40:00',
'yyyy-mm-dd hh24:mi:ss'))
from dual;
select output
from table(dbms_workload_repository.ash_report_text((select dbid
from v$database),
1,
TO_DATE('20130712203500',
'YYYYMMDDHH24MISS'),
TO_DATE('20130712204600',
'YYYYMMDDHH24MISS')));
3.sql_info
select sql_id,
first_load_time,
round(cpu_time / executions / 1000, 2) cpu_time_us,
round(elapsed_time / executions / 1000, 2) elapsed_time_us,
round(buffer_gets / executions / 1000, 2) buffer_gets,
executions,
parsing_user_id
from v$sql
where sql_id = '&1';
4.查看具体SQL的IO
select to_char(sample_time, 'yyyy/mm/dd hh24') sample_time,
sql_id,
round(sum(h.delta_read_io_bytes / 1024 / 1024 / 1024), 2) read_size_g
from v$active_session_history h
where h.sample_time >= sysdate - 4 / 24
group by to_char(sample_time, 'yyyy/mm/dd hh24'), sql_id
having round(sum(h.delta_read_io_bytes / 1024 / 1024 / 1024), 2) >= 50
order by 1 asc, 3 desc;
5.查看总的IO
select e.snap_id,
to_char(e.startup_time, 'yyyy-mm-dd hh24:mi:ss') instance_startup_time,
to_char(e.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') snapshot_end_time,
b.value - a.value IO_requests,
round((d.value - c.value)/1024/1024/1024) IO_GB
from dba_hist_sysstat a,
dba_hist_sysstat b,
dba_hist_sysstat c,
dba_hist_sysstat d,
dba_hist_snapshot e
where a.stat_name = 'physical read total IO requests'
and b.stat_name = 'physical read total IO requests'
and c.stat_name = 'physical read total bytes'
and d.stat_name = 'physical read total bytes'
and a.snap_id = e.snap_id - 1
and b.snap_id = e.snap_id
and c.snap_id = e.snap_id - 1
and d.snap_id = e.snap_id
and e.end_interval_time BETWEEN
TO_DATE('20160511 10:20:00', 'yyyymmdd hh24:mi:ss') AND
TO_DATE('20160511 21:14:00', 'yyyymmdd hh24:mi:ss')
order by e.begin_interval_time
6.查看SQL当前指标
select a.HASH_VALUE,
a.sql_text,
a.PLAN_HASH_VALUE,
round(a.BUFFER_GETS / a.EXECUTIONS) bg_per_exec,
round(a.CPU_TIME / 1000 / a.EXECUTIONS) cpu_per_exec,
round(a.ELAPSED_TIME / 1000 / a.EXECUTIONS) time_per_exec,
round(a.ROWS_PROCESSED / a.EXECUTIONS) rows_per_exec,
a.EXECUTIONS,
a.BUFFER_GETS,
a.DISK_READS,
a.CPU_TIME,
a.ELAPSED_TIME,
a.ROWS_PROCESSED
from v$sql a
where hash_value = '3332437006'
and cpu_time <> 0
and buffer_gets <> 0
and rows_processed <> 0
order by sql_text;
7.查询sql历史执行信息(次数,时间,HASH_VALUES变化等)
select sn.snap_id,
s.instance_number,
to_char(sn.end_interval_time, 'YYYYMMDD HH24:MI:SS'),
s.plan_hash_value,
s.executions_delta,
round(s.elapsed_time_delta / s.executions_delta)/1000 elapsed_time_ms ,
round(s.BUFFER_GETS_delta / s.executions_delta),
round(s.CPU_TIME_delta / s.executions_delta)/1000 CPU_TIME_ms
from dba_hist_snapshot sn, sys.WRH$_SQLSTAT s
where s.snap_id = sn.snap_id
and s.sql_id = '5t21ty9qhhpw2'
and s.instance_number = sn.instance_number
and s.executions_delta > 0
order by sn.end_interval_time desc;
8.查询temp空间历史消耗情况
select *
from (select t.sample_time,
s.PARSING_SCHEMA_NAME,
t.sql_id,
t.sql_child_number as sql_child,
round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
round(t.temp_space_allocated /
(select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))
from dba_temp_files d),
2) * 100 || ' %' as temp_pct,
t.program,
t.module,
s.SQL_TEXT
from v$active_session_history t, v$sql s
where t.sample_time > to_date('&begin_time', 'yyyy-mm-dd hh24:mi:ss')
and t.sample_time < to_date('&end_time', 'yyyy-mm-dd hh24:mi:ss')
and t.temp_space_allocated is not null
and t.sql_id = s.SQL_ID
order by t.temp_space_allocated desc)
where rownum < 50
order by temp_used desc;
9.表空间使用情况
select b.tablespace_name,
round(b.TOTAL_GB) || 'G' as TOTAL_GB,
round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2) || '%' FREE_PCT,
(100 -
round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))) * 100 / b.TOTAL_GB, 2)) || '%' USED_PCT,
round(nvl(a.FREE_GB,0),2) || 'G' as FILE_FREE_GB,
round((b.TOTAL_GB - (B.DISK_GB - nvl(a.FREE_GB,0))),2) || 'GB' TOTAL_FREE_GB
from (select TABLESPACE_NAME, sum(bytes) / 1024 / 1024 / 1024 FREE_GB
from dba_FREE_space
group by tablespace_name) a,
(select tablespace_name,
SUM(BYTES) / 1024 / 1024 /1024 DISK_GB,
sum(decode(maxbytes, 0, bytes, maxbytes)) / 1024 / 1024 / 1024 as TOTAL_GB
FROM DBA_DATA_FILES
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name
order by 1;
10.session UNDO占用情况
select se.SID,
se.SERIAL#,
se.STATUS,
round(tr.USED_UBLK *
(select value from v$parameter where NAME = 'db_block_size') / 1024 / 1024 / 1024,
4) || ' G' as undo_size
from v$session se, v$transaction tr
where se.TADDR = tr.ADDR(+)
ORDER BY 4 desc;
11.SPID to sql
select se.username,
se.sid,
se.serial#,
se.osuser,
se.machine,
se.program,
se.logon_time,
sa.sql_text
from v$session se, v$sqlarea sa, v$process pr
where se.SQL_ADDRESS = sa.ADDRESS
and se.SQL_HASH_VALUE = sa.HASH_VALUE
and se.PADDR=pr.ADDR
and pr.spid = '&SPID';
11.HOLDER会话查询
select count(0), sql_id, BLOCKING_SESSION
from v$session ee
where BLOCKING_SESSION in
(select BLOCKING_SESSION
from (select cc.SID,
cc.SERIAL#,
cc.BLOCKING_SESSION,
cc.EVENT,
cc.SQL_ID,
cc.USERNAME
from v$session cc
where cc.BLOCKING_SESSION is not null
and EVENT = '&1'))
group by sql_id, BLOCKING_SESSION
order by count(0) desc;
select sql_id,BLOCKING_SESSION,sid from v$session where sid in (
select BLOCKING_SESSION from
(select BLOCKING_SESSION,rownum num from
(select count(0), sql_id, BLOCKING_SESSION
from v$session ee
where BLOCKING_SESSION in
(select BLOCKING_SESSION
from (select cc.SID,
cc.SERIAL#,
cc.BLOCKING_SESSION,
cc.EVENT,
cc.SQL_ID,
cc.USERNAME
from v$session cc
where cc.BLOCKING_SESSION is not null
and EVENT = '&1'))
group by sql_id, BLOCKING_SESSION
order by count(0) desc)) test
where num <=1);
12.隐参查询
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ') ;
13.lgwr写的平均响应时间和等待
with t as (
select s.snap_id, s.instance_number, s.end_interval_time ,
total_waits - lag(total_waits, 1) over (partition by s.instance_number order by s.snap_id) waits,
(time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number order by s.snap_id))/1000 twt
from dba_hist_system_event ev, dba_hist_snapshot s
where ev.instance_number = s.instance_numbr and ev.snap_id = s.snap_id
and event_name = 'log file parallel write'
and s.end_interval_time BETWEEN TO_DATE('20170401 10:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20170614 11:00:00', 'yyyymmdd hh24:mi:ss') )e
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt_ms
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;
14. 单块读的平均响应时间
with t as (
select s.snap_id, s.instance_number, s.end_interval_time ,
total_waits - lag(total_waits, 1) over (partition by s.instance_number order by s.snap_id) waits,
(time_waited_micro - lag(time_waited_micro, 1) over (partition by s.instance_number order by s.snap_id))/1000 twt
from dba_hist_system_event ev, dba_hist_snapshot s
where ev.instance_number = s.instance_number and ev.snap_id = s.snap_id
and event_name = 'db file sequential read'
and s.end_interval_time BETWEEN TO_DATE('20170401 10:00:00', 'yyyymmdd hh24:mi:ss') AND TO_DATE('20171014 11:00:00', 'yyyymmdd hh24:mi:ss') )
select to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number, sum(waits), sum(twt), round(sum(twt)/sum(waits), 2) wt_ms
from t
where (to_char(end_interval_time, 'hh24') between 9 and 12 or to_char(end_interval_time, 'hh24') between 14 and 17)
group by to_char(end_interval_time,'YYYYMMDD HH24:MI'), instance_number
order by 1, instance_number;
15.等待事件监控
SELECT /*+ordered*/
SW.SEQ#,
S.SQL_HASH_VALUE,
S.SQL_ID,
SW.SID || ',' || S.SERIAL# SIDS,
S.USERNAME,
S.OSUSER,
SW.EVENT,
D.EVENT_COUNT,
SW.P1,
SW.P2,
SW.P3,
SW.WAIT_TIME "WAIT",
SW.STATE,
SW.SECONDS_IN_WAIT SEC,
S.STATUS,
S.LAST_CALL_ET, TO_CHAR(S.LOGON_TIME, 'dd/hh24:mi:ss') LOGON_TIME,
ST.SQL_TEXT SQL_TEXT
FROM (SELECT EVENT, COUNT(1) EVENT_COUNT
FROM V$SESSION_WAIT
WHERE EVENT NOT LIKE '%SQL*Net%'
AND EVENT NOT LIKE 'PX Deq%'
AND EVENT NOT LIKE 'rdbms ipc message'
AND EVENT NOT LIKE 'queue messages'
AND EVENT NOT LIKE 'class slave wait'
AND EVENT NOT LIKE
'Streams AQ: waiting for messages in the queue'
GROUP BY EVENT
HAVING((EVENT = 'latch free' AND COUNT(1) > 10) OR (EVENT = 'enqueue' AND COUNT(1) > 5) OR (EVENT = 'db file scattered read' AND COUNT(1) > 10) OR (EVENT = 'db file sequential read' AND COUNT(1) > 50) OR (EVENT = 'buffer busy waits' AND COUNT(1) > 20) OR (EVENT NOT IN ('db file sequential read', 'buffer busy waits', 'db file scattered read', 'latch free', 'enqueue') AND COUNT(1) > 10))) D,
V$SESSION_WAIT SW,
V$SESSION S,
V$SQL ST
WHERE D.EVENT = SW.EVENT
AND SW.SID = S.SID
AND S.SQL_ADDRESS = ST.ADDRESS(+)
AND S.SQL_HASH_VALUE = ST.HASH_VALUE(+)
AND S.USERNAME NOT IN ('SYS', 'SOLIX')
AND ST.CHILD_NUMBER(+) = 0
AND SW.WAIT_CLASS <> 'Idle'
ORDER BY SW.EVENT, S.USERNAME
16.清理sharepool某条sql占用
select sql_id,
sql_text,
sql_fulltext,
executions,
round(sharable_mem / 1024 / 1024, 2) sharmemo_used_mb,
last_active_time,
parsing_schema_name,
s.service,
'exec dbms_shared_pool.purge(''' || address || ',' ||
hash_value || ''',''c'');' remove_sql
from v$sql s
where s.sharable_mem > 1 * 1024 * 1024
order by s.sharable_mem desc;
17、会话打开游标数
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current' order by 1 desc;
18.session redo
select sysdate,
se.username,
se.sid,
se.serial#,
se.status,
se.machine,
se.osuser,
round(st.value / 1024 / 1024) redosize,
sa.sql_text
from v$session se, v$sesstat st, v$sqlarea sa
where se.sid = st.sid
and st.STATISTIC# =
(select STATISTIC# from v$statname where NAME = 'redo size')
--and se.username is not null
and st.value > 10 * 1024 * 1024
and se.SQL_ADDRESS = sa.ADDRESS
and se.SQL_HASH_VALUE = sa.HASH_VALUE
order by redosize;