============================================
Display time waited for each wait class.
============================================
SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited
FROM v$event_name a
JOIN v$system_event b ON a.name = b.event
GROUP BY a.wait_class;
===============================================
Display session wait information by wait class.
===============================================
SELECT *
FROM v$session_wait_class
WHERE sid = &enter_sid;
===============================================
Statistics (delete lock unlock)
===============================================
BEGIN
DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE');
DBMS_STATS.unlock_table_stats('MY_SCHEMA','LOAD_TABLE');
END;
=============================================
Retrieve SAMPLE Date (%)
============================================
SELECT e.empno, e.ename, d.dname
FROM emp SAMPLE (10) e
JOIN dept d ON e.deptno = d.deptno;
================================================
past 30 minutes waits
================================================
select
ash.event,
sum(ash.wait_time +ash.time_waited) ttl_wait_time
from v$active_session_history ash
where ash.sample_time between sysdate - 30/1440 and sysdate
group by ash.event
order by 2
/
=====================================================================
What user is waiting the most(last_hour) ?
=====================================================================
select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3
========================================================================
What SQL is currently using the most resources? (lasthour)
=================================================================
select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4
/
================================================================
What object is currently causing the highest resource waits? (lasthour)
=================================================================
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4
/
------------------------------------------ Current User Activity ----------------------------------------
substr(s.username,1,18) username,
substr(s.program,1,15) program,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
from
v$session s,
v$process p,
v$transaction t,
v$rollstat r,
v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by 1
;
---------------------------------------------- O/S Cpu Usage ------------------------------------------------
select
to_char(begintime,'DD-MON-YY HH24:MI:SS') begintime,
to_char(endtime,'DD-MON-YY HH24:MI:SS') endtime,
inst,
snapid,
round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) utpct,
round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) ntpct,
round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) stpct,
round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100) iowtpct,
(100-
(
round((utdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((ntdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((stdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)+
round((iowtdiff/(utdiff+itdiff+stdiff+iowtdiff+ntdiff))*100)
)) itpct
from
(
select begintime,endtime,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
ut-(nvl(lag(ut) over (partition by inst order by inst,snapid),0)) utdiff,
bt-(nvl(lag(bt) over (partition by inst order by inst,snapid),0)) btdiff,
it-(nvl(lag(it) over (partition by inst order by inst,snapid),0)) itdiff,
st-(nvl(lag(st) over (partition by inst order by inst,snapid),0)) stdiff,
iowt-(nvl(lag(iowt) over (partition by inst order by inst,snapid),0)) iowtdiff,
nt-(nvl(lag(nt) over (partition by inst order by inst,snapid),0)) ntdiff,
vin-(nvl(lag(vin) over (partition by inst order by inst,snapid),0)) vindiff,
vout-(nvl(lag(vout) over (partition by inst order by inst,snapid),0)) voutdiff
from
(
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,oss.snap_id SnapId,oss.instance_number Inst,
sum(decode(oss.stat_name,'USER_TIME',value,0)) ut,
sum(decode(oss.stat_name,'BUSY_TIME',value,0)) bt,
sum(decode(oss.stat_name,'IDLE_TIME',value,0)) it,
sum(decode(oss.stat_name,'SYS_TIME',value,0)) st,
sum(decode(oss.stat_name,'IOWAIT_TIME',value,0)) iowt,
sum(decode(oss.stat_name,'NICE_TIME',value,0)) nt,
sum(decode(oss.stat_name,'VM_IN_BYTES',value,0)) vin,
sum(decode(oss.stat_name,'VM_OUT_BYTES',value,0)) vout
from dba_hist_osstat oss,dba_hist_snapshot sn
where oss.dbid=&dbid
and oss.dbid = sn.dbid
and oss.instance_number = sn.instance_number
and oss.snap_id = sn.snap_id
and oss.snap_id between &bsnap - 1 and &esnap
and oss.stat_name in (
'USER_TIME',
'BUSY_TIME',
'IDLE_TIME',
'SYS_TIME',
'IOWAIT_TIME',
'NICE_TIME',
'VM_IN_BYTES',
'VM_OUT_BYTES'
)
group by sn.begin_interval_time,sn.end_interval_time,oss.snap_id,oss.instance_number
order by oss.instance_number,oss.snap_id
)
)
where snapid between &bsnap and &esnap
order by inst,snapid;
----------------------------------- Cpu’s/Sockets/Cores/Load Average -----------------------------------
select * from (
select stat_name,value
from dba_hist_osstat
where dbid = &dbid
and instance_number = 1
and snap_id = &snapid
)
pivot (sum(value) for stat_name in ('NUM_CPUS','NUM_CPU_SOCKETS','NUM_CPU_CORES','PHYSICAL_MEMORY_BYTES','LOAD'));
---------------------------------- Database version and platform ------------------------------------
select distinct version,platform_name from dba_hist_database_instance
--------------------------------------- Physical and Logical I/O -----------------------------------------
,(extract(Minute from endtime-begintime)*60+extract(Second from endtime-begintime)) secs,
snapid,inst,
prd-nvl(lag(prd) over (partition by inst order by inst,snapid),0) prddiff,
pwrt-nvl(lag(pwrt) over (partition by inst order by inst,snapid),0) pwrtdiff,
iordreq-nvl(lag(iordreq) over (partition by inst order by inst,snapid),0) iorddiff,
iowrtreq-nvl(lag(iowrtreq) over (partition by inst order by inst,snapid),0) iowrtdiff,
prmbr-nvl(lag(prmbr) over (partition by inst order by inst,snapid),0) prmbrdiff,
cgets-nvl(lag(cgets) over (partition by inst order by inst,snapid),0) cgetsdiff,
dbgets-nvl(lag(dbgets) over (partition by inst order by inst,snapid),0) dbgetsdiff
from
(
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,ss.snap_id SnapId,ss.instance_number Inst,
sum(decode(ss.stat_name,'physical read total bytes',value,0)) prd,
sum(decode(ss.stat_name,'physical write total bytes',value,0)) pwrt,
sum(decode(ss.stat_name,'physical read total IO requests',value,0)) iordreq,
sum(decode(ss.stat_name,'physical write total IO requests',value,0)) iowrtreq,
sum(decode(ss.stat_name,'physical read total multi block requests',value,0)) prmbr,
sum(decode(ss.stat_name,'consistent gets',value,0)) cgets,
sum(decode(ss.stat_name,'db block gets',value,0)) dbgets
from dba_hist_sysstat ss,dba_hist_snapshot sn
where ss.dbid=&dbid
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.snap_id = sn.snap_id
and ss.snap_id between &bsnap-1 and &esnap
and ss.stat_name in (
'physical read total bytes',
'physical write total bytes',
'physical read total IO requests',
'physical write total IO requests',
'physical read total multi block requests',
'consistent gets',
'db block gets'
)
group by sn.begin_interval_time,sn.end_interval_time,ss.snap_id,ss.instance_number
order by ss.instance_number,ss.snap_id
)
where snapid between &bsnap and &esnap
order by 4,5;
-------------------------- Top 10 sql statements By Elapsed Time -------------------------------
select * from (
select ss.snap_id snapid,ss.instance_number inst,ss.sql_id sqlid
,round(sum(ss.elapsed_time_delta)) elapsed
,nvl(round(sum(ss.executions_delta)),1) execs
,round(sum(ss.buffer_gets_delta)) gets
,round(sum(ss.rows_processed_delta)) rowsp
,round(sum(ss.disk_reads_delta)) reads
,dense_rank() over(partition by snap_id,instance_number order by sum(ss.elapsed_time_delta) desc) sql_rank
from
dba_hist_sqlstat ss
where
ss.dbid = &dbid and
ss.snap_id between &bsnap and &esnap
group by ss.snap_id,ss.instance_number,ss.sql_id
)
where sql_rank < 11 and snapid between &bsnap and &esnap;
----------------------------------- Top 5 Foreground Waits --------------------------------
with se as (
select sn.begin_interval_time begintime,
sn.end_interval_time EndTime,se.snap_id SnapId,se.instance_number Inst,
se.event_name stat,se.time_waited_micro_fg value,
nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
order by se.instance_number,se.snap_id,se.event_name),0) prevval,
se.time_waited_micro_fg-
nvl(lag(se.time_waited_micro_fg) over(partition by se.instance_number,se.event_name
order by se.instance_number,se.snap_id,se.event_name),0) valuediff
from dba_hist_system_event se,dba_hist_snapshot sn
where se.dbid=&dbid
and se.dbid = sn.dbid
and se.instance_number = sn.instance_number
and se.snap_id = sn.snap_id
and se.snap_id between &bsnap-1 and &esnap
and se.wait_class != 'Idle'
order by se.snap_id,se.instance_number,se.event_name
) ,
sdbcpu as (
select sn.begin_interval_time begintime,sn.end_interval_time EndTime,
stm.snap_id snapid,stm.instance_number inst,stm.stat_name stat
,stm.value value
,nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) prevval
,stm.value-
nvl(lag(stm.value) over(partition by stm.instance_number order by stm.instance_number,stm.snap_id),0) valuediff
from dba_hist_sys_time_model stm,dba_hist_snapshot sn
where
stm.stat_name = ('DB CPU')
and stm.dbid = &dbid
and stm.snap_id between &bsnap-1 and &esnap
and stm.dbid = sn.dbid
and stm.instance_number = sn.instance_number
and stm.snap_id = sn.snap_id
order by stm.snap_id,stm.instance_number
) ,
sunion as (
select begintime,endtime,snapid,inst,stat,valuediff from se
union all
select begintime,endtime,snapid,inst,stat,valuediff from sdbcpu
order by 3,4
),
spct as (
select begintime,endtime,snapid,inst,stat,valuediff,
round(ratio_to_report(valuediff) over (partition by snapid,inst),4) as pct
from sunion
order by 3,4 asc,7 desc
)
select * from (
select to_char(begintime,'DD-MON-RR HH24:MI:SS') begintime
,to_char(endtime,'DD-MON-RR HH24:MI:SS') endtime,snapid,inst,stat,valuediff,round(pct*100,2) pct,
row_number() over (partition by snapid,inst order by snapid,inst asc,pct desc) as rnum
from spct
)
where rnum < 6 and snapid between &bsnap and &esnap;
No comments:
Post a Comment