Currently connected sessions waits, longops, sql, etc...
Oracle Wait info
select/*+ rule*/ distinct--because of multiple records in v$sql may exist
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session_wait.EVENT
, v$latchname.NAME as LATCHNAME
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1,
gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#,
gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, gv$sql.ADDRESS as SQL_ADDRESS
, gv$sql.HASH_VALUE as HASH_VALUE
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, sql1.ADDRESS as PREV_SQL_ADDRESS
, sql1.HASH_VALUE as PREV_HASH_VALUE
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';'
as KILL_SESSION
, gv$instance.HOST_NAME
, 'kill '||gv$process.SPID as KILL_SPID
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$lock lockwait
, gv$lock lockhold
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%'
or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR
and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded
and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1)
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, machine asc;
------------
Prompt Active session waits ratio
select WAIT_EVENT
,lpad(TO_CHAR(PCTTOT,'990D99'),6)||'% waits with average duration ='
||TO_CHAR(AVERAGE_WAIT_MS,'9990D99')||'ms' as EVENT_VALUES
from (
select --RANK() OVER (order by sum(time_waited) desc) as RANK,
event as WAIT_EVENT--, sum(time_waited) time_waited_ms
,round(RATIO_TO_REPORT(sum(time_waited)) OVER ()*100,2) AS PCTTOT
,round(avg(average_wait)*10,2) as AVERAGE_WAIT_MS
from
(select se.SID, se.INST_ID, se.EVENT, se.TIME_WAITED,
se.AVERAGE_WAIT from gv$session_event se
where se.WAIT_CLASS not in ('Idle')
union
select ss.SID, ss.INST_ID, sn.NAME as EVENT
, ss.VALUE as TIME_WAITED
, 0 as AVERAGE_WAIT from gv$sesstat ss, v$statname sn
where ss."STATISTIC#" = sn."STATISTIC#"
and sn.NAME in ('CPU used when call started'))
where (sid, inst_id) in
(select sid, inst_id from gv$session
where gv$session.SERVICE_NAME not in ('SYS$BACKGROUND'))
group by event
order by PCTTOT desc) we;
Sunday, 16 October 2011
Oracle Active session on single Instance and RAC
Currently connected sessions waits, longops, sql, etc...
active session sessions
Instance
select/*+ RULE*/ distinct--because of multiple records in v$sql for PREV_SQL
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
, gv$session.SERVICE_NAME as service
, gv$session.INST_ID
, gv$session.SID
, gv$session.SERIAL#
, decode(gv$session_wait.state, 'WAITING', gv$session_wait.EVENT,
'On CPU / runqueue') as EVENT
, v$latchname.NAME as LATCHNAME
, gv$latchholder.sid
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE'
, DBMS_ROWID.ROWID_CREATE( 1, gv$session.ROW_WAIT_OBJ#
, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#
, gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P1RAW /* X$BH.HLADDR for LATCH: CACHE BUFFERS CHAINS */
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, ses_optimizer_env38.VALUE as ses_optimizer_mode
, ses_optimizer_env48.VALUE as ses_cursor_sharing
, gv$sql.SQL_ID as SQL_ID
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, gv$sql.SQL_PROFILE
, gv$session.SQL_CHILD_NUMBER SQL_CHILD_NUMBER
, sql1.SQL_ID as PREV_SQL_ID
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
, gv$session.PREV_CHILD_NUMBER
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
, 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#
||''';' as KILL_SESSION
--, gv$session.PROCESS
, gv$instance.HOST_NAME
, 'kill -9 '||gv$process.SPID as KILL_SPID
-- from 10g and above - SQL Trace info
, gv$session.SQL_TRACE, gv$session.SQL_TRACE_WAITS, gv$session.SQL_TRACE_BINDS
, 'begin sys.dbms_support.start_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||', waits=>TRUE, binds=>TRUE );end;'
, 'begin sys.dbms_support.stop_trace_in_session('||gv$session.SID||','||
gv$session.SERIAL#||' );end;'
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$ses_optimizer_env ses_optimizer_env38
, gv$ses_optimizer_env ses_optimizer_env48
, gv$lock lockwait
, gv$lock lockhold
, gv$latchholder
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.SQL_CHILD_NUMBER = gv$sql.CHILD_NUMBER(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.PREV_CHILD_NUMBER = sql1.CHILD_NUMBER(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and gv$session.SERVICE_NAME not in ('SYS$BACKGROUND')
and gv$session.PROGRAM not like '%QMNC%' --Queue Monitor Coordinator excluded
and gv$session.PROGRAM not like '%q00%' --Queue monitor processes excluded
--and v$session.PROGRAM not like '%J00%' --DBMS_JOB processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session_wait.p1raw = gv$latchholder.laddr(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and (nvl(gv$session_longops.TIME_REMAINING, 1) > 0
or
nvl(gv$session_longops.START_TIME, sysdate) = (select max(START_TIME) from gv$session_longops gsl where gv$session.SID = gsl.sid and gv$session.INST_ID = gsl.INST_ID and gv$session.SERIAL# = gsl.SERIAL# and gv$session.sql_address = gsl.SQL_ADDRESS and gv$session.sql_hash_value = gsl.SQL_HASH_VALUE))
and gv$session.sid = ses_optimizer_env38.sid(+)
and gv$session.INST_ID = ses_optimizer_env38.INST_ID(+)
and nvl(ses_optimizer_env38.id,38) = 38 --optimizer_mode
and gv$session.sid = ses_optimizer_env48.sid(+)
and gv$session.INST_ID = ses_optimizer_env48.INST_ID(+)
and nvl(ses_optimizer_env48.id, 48) = 48--cursor_sharing
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
order by tx_stime, status, LOGON_TIME, username, gv$session.sid;
=================
SET LINES 300 pagesize 300
col kill for a15
col UNAM2 format a20 word heading "Oracle User/OS User"
col PROG format a15 word heading "Program|Terminal"
col SID format a10 word heading "SID/|Serial#"
col STAT format A7 Heading "Status"
col LTIM format a20 word heading "Logon Time/|Last Activy time"
col TIMI format a20 word heading "Start time/|Last Update time|remaining->elapsed|[%done end time]"
col STMT format a64 heading "Statement" WORD
col OPNA format a10 WORD PRINT HEADING "Long Op"
col WORK format a12 WORD PRINT HEADING "Target|So far|Total Work"
col MESS format a50 WORD PRINT HEADING "Long op message" wrap
--NEWLINE
SELECT
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill
,ses.sql_id
,lo.opname opna
,NVL(ses.username,lo.username)||' / '||ses.osuser||DECODE(ses.audsid,userenv('SESSIONID'),'**','') unam2
,NVL(ses.status,'unknown') stat
-- ,TO_CHAR(ses.logon_time,'DD-MON-YYYY:HH24:MI:SS')||' '
-- ||to_char(sysdate - (ses.last_call_et)/86400,'DD-MON-YYYY:hh24:mi:ss') ltim
,NVL(LPAD(TO_CHAR(target),11,'_'),'___________')||','
||LPAD(TO_CHAR(sofar),11,'_')||','
||LPAD(TO_CHAR(totalwork),11,'_')||' ('||units||') ['
||DECODE(0,totalwork,'n/a',CEIL(100*sofar/(totalwork)))||'%]'
work
,start_time||' '
||last_update_time
||' '||TO_CHAR(time_remaining)||''
||'>;'||elapsed_seconds||' ['||
DECODE(0,time_remaining+elapsed_seconds,'n/a',CEIL(100*elapsed_seconds/(time_remaining+elapsed_seconds)))||'% eta '
||TO_CHAR(sysdate+(time_remaining/(60*60*24)),'HH24:MIpm')||']' timi
,DECODE(ses.program,'','',ses.program||' '||ses.terminal) prog
-- ||'('||RTRIM(ses.machine)||')'
-- ,lo.sid||'/'||lo.serial#||' ['||ses.audsid||']' sid
,message mess
,REPLACE(s.sql_text,CHR(10),' ') stmt
--,context
FROM gv$sql s ,gv$session_longops lo ,gv$session ses
WHERE lo.sql_address=s.address
and lo.inst_id=s.inst_id
AND lo.sql_hash_value=s.hash_value
AND lo.time_remaining!=0
AND lo.sid=ses.sid(+)
AND lo.serial#=ses.serial#(+)
/
set linesize 200 pages 200
col sid format 99999
col name format a36
col p1 format 999999999 Head 'P1'
col program format a25
col p2 format 999999999 Head 'P2'
col p3 format 999999999 Head 'P3'
col pgm format a15 head 'What'
col state format a15
col wt format 9999999 head 'Wait|Time'
col WaitEvent format a38 head 'Wait Event'
col lc format 99999999999.99 head 'last call'
col kill for a16
select
--A.sid,
''''||b.sid ||','|| b.serial#||',@'||b.inst_id ||'''' kill,
decode(A.event,'null event','CPU Exec',A.event) WaitEvent,A.p1,A.p2,A.p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm,b.sql_id,PREV_SQL_ID
from gv$session_wait A,gv$session B
where 1=1
and (A.event like 'gc%'
or A.event like 'GC%'
or A.event like 'ge%')
and A.event not like '%remote message'
and A.event not like '%sleep'
and A.sid=B.sid
and B.status='ACTIVE'
and a.inst_id=b.inst_id
order by 1
/
define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col dbt format 999999 head 'DB Time'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head '% of DB Time'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,.001*((sum(s2.time_waited))) time_waited,
(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/count(s3.sample_id)),2) as wait_pct
from xtimes s1,
v$active_session_history s2,
v$active_session_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/
undefine interval_mins
Measuring RAC Waits from DBA_HIST_ACTIVE_SESS_HISTORY
define interval_mins=30
set lines 200
col samplestart format a30 head 'Begin Time'
col sampleend format a30 head 'End Time'
col aas format 999.99 head 'AAS'
col aas_event format 999.99 head 'AAS per |Event'
col event format a30
col time_waited format 999999 head 'Time (ms)'
col wait_pct format 999.99 head 'Wait %'
COLUMN bt NEW_VALUE _bt NOPRINT
COLUMN et NEW_VALUE _et NOPRINT
select min(cast(sample_time as date)) bt,
sysdate et
from v$active_session_history;
with xtimes (xdate) as
(select to_date('&_bt') xdate
from dual
union all
select xdate+(&&interval_mins/1440)
from xtimes
where xdate+(&&interval_mins/1440) < sysdate)
select to_char(s1.xdate,'DD-MON-RR HH24:MI:SS') samplestart,
to_char(s1.xdate+(&&interval_mins/1440),'DD-MON-RR HH24:MI:SS') sampleend,
s2.event,
.001*((sum(s2.time_waited))) time_waited,
10*(count(s2.sample_id)/(60*&&interval_mins)) aas_event,
10*(count(s3.sample_id)) dbt,
round(100*(sum(s2.time_waited)/1000000/(10*(count(s3.sample_id)))),2) as wait_pct
from xtimes s1,
dba_hist_active_sess_history s2,
dba_hist_active_sess_history s3
where s2.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s3.sample_time between s1.xdate and s1.xdate+(&&interval_mins/1440)
and s2.sample_id=s3.sample_id
and (s2.event like 'gc%'
or s2.event like 'GC%'
or s2.event like 'ge%')
and s2.event not like '%remote message'
and s2.event not like '%sleep'
group by s1.xdate,s2.event
order by s1.xdate
/
Oracle Trace File Info .....
Oracle Trace File path ....
Trace file info ..select value || '/' ||
(select lower(instance_name) from v$instance) ||
'_ora_' ||
(
select spid
from v$process
where addr = (
select paddr
from v$session
where sid = (
select sid
from v$mystat
where rownum = 1
)
)
) ||
'.trc'
from
v$parameter
where
name = 'user_dump_dest'
;
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_17513.trc
1 row selected.
SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
SELECT a.trace_path || ' > ' || b.trace_file "Trace File Path"
FROM
( SELECT value trace_path FROM v$parameter WHERE name='user_dump_dest' ) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE FROM v$process,
(select lower(instance_name) instance from v$instance) c
WHERE addr = ( SELECT paddr FROM v$session WHERE (audsid, sid) = ( SELECT sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID') FROM dual))) b
/
==============================================================
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
Oracle 11gr2 +
to check trace file name ...
SET ECHO OFF FEEDBACK 6 HEADING ON LINESIZE 200 PAGESIZE 0 TERMOUT ON TIMING OFF TRIMOUT ON TRIMSPOOL ON VERIFY OFF
SELECT a.trace_path || ' > ' || b.trace_file "Trace File Path",
'! cat '||a.trace_path || '/' || b.trace_file "Trace File Path"
FROM
( SELECT VALUE trace_path FROM v$diag_info WHERE name='Diag Trace' ) a
, ( SELECT c.instance || '_ora_' || spid ||'.trc' TRACE_FILE FROM v$process,
(select instance_name instance from v$instance) c
WHERE addr = ( SELECT paddr FROM v$session WHERE (audsid, sid) = ( SELECT sys_context('USERENV', 'SESSIONID') , sys_context('USERENV', 'SID') FROM dual))) b
/
set linesize 200 pagesize 200
col name for a20
col value for a70
select * from v$diag_info;
Oracle SGA Info
set pagesize 999 lines 70 verify off heading off feedback off termout off
col start_up format a45 justify right
col sp_size format 999,999,999 justify right
col x_sp_used format 999,999,999 justify right
col sp_used_shr format 999,999,999 justify right
col sp_used_per format 999,999,999 justify right
col sp_used_run format 999,999,999 justify right
col sp_avail format 999,999,999 justify right
col sp_sz_pins format 999,999,999 justify right
col sp_no_pins format 999,999 justify right
col sp_no_obj format 999,999 justify right
col sp_no_stmts format 999,999 justify right
col sp_sz_kept_chks format 999,999,999 justify right
col sp_no_kept_chks format 999,999 justify right
col 1time_sum_pct format 999 justify right
col 1time_ttl_pct format 999 justify right
col ltime_ttl format 999,999,999 justify right
col 1time_sum format 999,999,999,999 justify right
col tot_lc format 999,999,999,999 justify right
col sp_free format 999,999,999,999 justify right
col val1 new_val x_sgasize noprint
col val2 new_val x_sp_size noprint
col val3 new_val x_lp_size noprint
col val4 new_val x_jp_size noprint
col val5 new_val x_bc_size noprint
col val6 new_val x_other_size noprint
col val7 new_val x_str_size noprint
col val8 new_val x_KGH noprint
select val1, val2, val3, val4, val5, val6, val7, val8
from
(select sum(bytes) val1 from v$sgastat) s1,
(select nvl(sum(bytes),0) val2 from v$sgastat where pool='shared pool') s2,
(select nvl(sum(bytes),0) val3 from v$sgastat where pool='large pool') s3,
(select nvl(sum(bytes),0) val4 from v$sgastat where pool='java pool') s4,
(select nvl(sum(bytes),0) val5 from v$sgastat where name='buffer_cache') s5,
(select nvl(sum(bytes),0) val6 from v$sgastat where name in ('log_buffer','fixed_sga')) s6,
(select nvl(sum(bytes),0) val7 from v$sgastat where pool='streams pool') s7,
(select nvl(sum(bytes),0) val8 from v$sgastat where pool='shared pool' and name='KGH: NO ACCESS') s8;
col val1 new_val x_sp_used noprint
col val2 new_val x_sp_used_shr noprint
col val3 new_val x_sp_used_per noprint
col val4 new_val x_sp_used_run noprint
col val5 new_val x_sp_no_stmts noprint
col val6 new_val x_sp_vers noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val1,sum(sharable_mem) val2, sum(runtime_mem) val4, sum(persistent_mem) val3,count(*) val5, max(version_count) val6 from v$sqlarea;
col val1 new_val x_1time_sum noprint
col val2 new_val x_1time_ttl noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val1, count(*) val2 from v$sqlarea
where executions=1;
col val1 new_val x_ra noprint
select round(nvl((used_space+free_space),0),2) val1 from v$shared_pool_reserved;
col val2 new_val x_sp_no_obj noprint
select count(*) val2 from v$db_object_cache;
col val2 new_val x_sp_no_kept_chks noprint
col val3 new_val x_sp_sz_kept_chks noprint
select decode(count(*),'',0,count(*)) val2, decode(sum(sharable_mem),'',0,sum(sharable_mem)) val3
from v$db_object_cache where kept='YES';
col val2 new_val x_sp_free_chks noprint
select sum(bytes) val2 from v$sgastat where name='free memory' and pool='shared pool';
col val2 new_val x_sp_no_pins noprint
select count(*) val2 from v$session a, v$sqltext b where a.sql_address||a.sql_hash_value = b.address||b.hash_value;
col val2 new_val x_sp_sz_pins noprint
select sum(sharable_mem+persistent_mem+runtime_mem) val2 from v$session a,v$sqltext b,v$sqlarea c
where a.sql_address||a.sql_hash_value = b.address||b.hash_value
and b.address||b.hash_value = c.address||c.hash_value;
col val3 new_val x_tot_lc noprint
select nvl(sum(lc_inuse_memory_size)+sum(lc_freeable_memory_size),0) val3 from v$library_cache_memory;
col val2 new_val x_sp_avail noprint
select &x_sp_size-(&x_tot_lc*1024*1024)-&x_sp_used val2 from dual;
col val2 new_val x_sp_other noprint
select &x_sp_size-(&x_tot_lc*1024*1024) val2 from dual;
col val1 new_val x_trend_4031 noprint
col val2 new_val x_trend_size noprint
col val3 new_val x_trend_rS noprint
col val4 new_val x_trend_rs_size noprint
select request_misses val1,decode(request_misses,0,0,last_Miss_Size) val2,request_failures val3,decode(request_failures,0,0,last_failure_size) val4 from v$shared_pool_reserved;
set termout on heading off
ttitle -
center 'SGA/Shared Pool Breakdown' skip 2
spool sga.out
select ' *** If database started recently, this data is not as useful ***',' ','Database Started: '||to_char(startup_time, 'Mon/dd/yyyy hh24:mi:ss') start_up,'Instance Name/No: '||instance_name||'-'||instance_number,' ','Breakdown of SGA '||round((&x_sgasize/1024/1024),2)||'M ',
' Shared Pool Size : '||round((&x_sp_size/1024/1024),2)||'M ('||round((&x_sp_size/&x_sgasize)*100,0)||'%) Reserved ' ||round((&x_ra/1024/1024),2)||'M ('||round((&x_ra/&x_sp_size)*100,0)||'%)' sp_size,
' Large Pool : '||round((&x_lp_size/1024/1024),2)||'M ('||round((&x_lp_size/&x_sgasize)*100,0)||'%)',
' Java Pool : '||round((&x_jp_size/1024/1024),2)||'M ('||round((&x_jp_size/&x_sgasize)*100,0)||'%)',
' Buffer Cache : '||round((&x_bc_size/1024/1024),2)||'M ('||round((&x_bc_size/&x_sgasize)*100,0)||'%)',
' Streams Pool : '||round((&x_str_size/1024/1024),2)||'M ('||round((&x_str_size/&x_sgasize)*100,0)||'%)',
' Other Areas in SGA : '||round((&x_other_size/1024/1024),2)||'M ('||round((&x_other_size/&x_sgasize)*100,0)||'%)',' ',' *** High level breakdown of memory ***',' ',
' sharable : '||round((&x_sp_used_shr/1024/1024),2)||'M' sp_used_shr,
' persistent : '||round((&x_sp_used_per/1024/1024),2)||'M' sp_used_per,
' runtime : '||round((&x_sp_used_run/1024/1024),2)||'M' sp_used_run,
' ','SQL Memory Usage (total) : '||round((&x_sp_used/1024/1024),2)||'M ('||round((&x_sp_used/&x_sp_size)*100,0)||'%)',' ',' *** No guidelines on SQL in Library Cache, but if ***',
' *** pinning a lot of code--may need larger Shared Pool ***',' ',
'# of SQL statements : '||&x_sp_no_stmts sp_no_stmts,'# of pinned SQL statements : '||&x_sp_no_pins sp_no_pins,'# of programmatic constructs : '||&x_sp_no_obj sp_no_obj,'# of pinned programmatic construct : '
||&x_sp_no_kept_chks sp_no_kept_chks,' ','Efficiency Analysis: ',' *** High versions (100s) could be bug ***',' ',' Max Child Cursors Found : '||&x_sp_vers,' Programmatic construct memory size (Kept) : '
||round((&x_sp_sz_kept_chks/1024/1024),2)||'M' sp_sz_kept_chks,' Pinned SQL statements memory size (active sessions) : '||round((&x_sp_sz_pins/1024/1024),2)||'M' sp_sz_pins,
' ',' *** LC at 50% or 60% of Shared Pool not uncommon ***',' ',' Estimated Total Library Cache Memory Usage : '||&x_tot_lc||'M ('||100*(round(((&x_tot_lc) / (&x_sp_size/1024/1024)),2))||'%)' perc_lc,
' Other Shared Pool Memory : '||round((&x_sp_other/1024/1024),2)||'M',' Shared Pool Free Memory Chunks : '||round(((&x_sp_free_chks) /1024/1024),2)||'M ('||100*(round((&x_sp_free_chks / &x_sp_size),2))||'%)' perc_free,
' ',' ****Ideal percentages for 1 time executions is 20% or lower**** ',
' ',' # of objects executed only 1 time : '||&x_1time_ttl||' ('||100*round(((&x_1time_ttl / &x_sp_no_stmts)),2)||'%)',' Memory for 1 time executions: : '||round((&x_1time_sum/1024/1024),2)||'M ('||
100*round(((&x_1time_sum / &x_sp_used)),2)||'%)',' ',' ***If these chunks are growing, SGA_TARGET may be too low***',' ',' Current KGH: NO ACCESS Allocations: '||round((&x_KGH/1024/1024),2)||'M ('||100*round((&x_KGH/&x_sp_size),2)||'%)',
' ',' ***0 misses is ideal, but if growing value points to memory issues***',
' ',' # Of Misses for memory : '|| &x_trend_rs,' Size of last miss : '|| &x_trend_rs_size,' # Of Misses for Reserved Area : '|| &x_trend_4031,' Size of last miss Reserved Area : '|| &x_trend_size
from v$instance;
spool off
ttitle off
set heading feedback on
clear col
SGA/Shared Pool Breakdown
*** If database started recently, this data is not as useful ***
Database Started: Jun/21/2016 13:49:53
Instance Name/No: arac1-1
Breakdown of SGA 16384.12M
Shared Pool Size : 2048M (12%) Reserved 202.73M (10%)
Large Pool : 288M (2%) Java Pool : 128M (1%)
Buffer Cache : 13376.24M (82%) Streams Pool : 0M (0%)
Other Areas in SGA : 32M (0%)
*** High level breakdown of memory *** sharable : 307.46M
persistent : 270.54M runtime : 255.69M
SQL Memory Usage (total) : 833.69M (41%)
*** No guidelines on SQL in Library Cache, but if ***
*** pinning a lot of code--may need larger Shared Pool ***
# of SQL statements : 3418 # of pinned SQL statements : 4
# of programmatic constructs : 24033
# of pinned programmatic construct : 4433 Efficiency Analysis:
*** High versions (100s) could be bug ***
Max Child Cursors Found : 18
Programmatic construct memory size (Kept) : 17.78M
Pinned SQL statements memory size (active sessions) : .45M
*** LC at 50% or 60% of Shared Pool not uncommon ***
Estimated Total Library Cache Memory Usage : 336M (16%)
Other Shared Pool Memory : 1712M
Shared Pool Free Memory Chunks : 440.09M (21%)
****Ideal percentages for 1 time executions is 20% or lower****
# of objects executed only 1 time : 2206 (65%)
Memory for 1 time executions: : 495.83M (59%)
***If these chunks are growing, SGA_TARGET may be too low***
Current KGH: NO ACCESS Allocations: 0M (0%)
***0 misses is ideal, but if growing value points to memory issues***
# Of Misses for memory : 0 Size of last miss : 0
# Of Misses for Reserved Area : 0
Size of last miss Reserved Area : 0
DATABASE GROWTH / Disk Used by Object Type
DATABASE GROWTH / Disk Used by Object Type
define schema_name='SYS'
define days_back=10
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = '&schema_name'
and space_used_delta > 0;
Space used (M) Total Schema Size (M) Percent of Total Disk Usage
-------------- --------------------- -----------------------------------------
19626.4335 1564335.63 1.25%
DATABASE GROWTH delta
define schema_name='SYS'
define days_back=10
ttitle "Total Disk Used by Object Type"
select c.segment_type, sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes)/1024/1024 "Total Space (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and space_used_delta > 0
and c.owner = '&schema_name'
group by rollup(segment_type);
SEGMENT_TYPE Space used (M) Total Space (M) Percent of Total Disk Usage
------------------ -------------- --------------- -----------------------------------------
INDEX 421.957651 357853.375 .12%
INDEX PARTITION 1297.58915 33745 3.85%
TABLE 119.543896 434165.25 .03%
TABLE PARTITION 17787.3428 738572 2.41%
19626.4335 1564335.63 1.25%
SQL>
Object Growth
set linesize 300 pagesize 300
col OWNER for a20
col OBJECT_NAME for a35
with
awr_seg_stat as (
select
ts#,obj#,dataobj#
,snap_id
,space_used_total
,space_used_delta
,space_allocated_total
,space_allocated_delta
,physical_writes_delta
,physical_write_requests_delta
from dba_hist_seg_stat s
--where obj# in (select object_id from dba_objects where owner='')
)
,top_segs_by_growth as (
select *
from (
select
ts#,obj#
,sum(space_allocated_delta) delta
,dense_rank()over(order by sum(space_allocated_delta) desc) N
from awr_seg_stat
group by ts#,obj#
)
where N<=10 -- topN
)
select
s.n
,o.owner,o.object_type,o.object_name
,round(s.delta/1024/1024) "Delta(MB)"
from top_segs_by_growth s ,dba_objects o
where s.obj# = o.object_id
order by 1
/
N OWNER OBJECT_TYPE OBJECT_NAME Delta(MB)
---------- -------------------- ----------------------- ----------------------------------- ----------
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
6 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
7 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
7 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 37
8 SYS TABLE PARTITION WRH$_LATCH_CHILDREN 36
9 SYS TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY 31
10 SYS TABLE PARTITION WRH$_ACTIVE_SESSION_HISTORY 30
SQL>
Subscribe to:
Posts (Atom)
