jump to content

Here are some scripts related to Session Statistics .

Session I/O By User

SESSION I/O BY USER NOTES:

  • Username - Name of the Oracle process user
  • OS User - Name of the operating system user
  • PID - Process ID of the session
  • SID - Session ID of the session
  • Serial# - Serial# of the session
  • Physical Reads - Physical reads for the session
  • Block Gets - Block gets for the session
  • Consistent Gets - Consistent gets for the session
  • Block Changes - Block changes for the session
  • Consistent Changes - Consistent changes for the session

    select	nvl(ses.USERNAME,'ORACLE PROC') username,
    	OSUSER os_user,
    	PROCESS pid,
    	ses.SID sid,
    	SERIAL#,
    	PHYSICAL_READS,
    	BLOCK_GETS,
    	CONSISTENT_GETS,
    	BLOCK_CHANGES,
    	CONSISTENT_CHANGES
    from	v$session ses, 
    	v$sess_io sio
    where 	ses.SID = sio.SID
    order 	by PHYSICAL_READS, ses.USERNAME
    
    

    CPU Usage By Session

    CPU USAGE BY SESSION NOTES:

  • Username - Name of the user
  • SID - Session id
  • CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)

    select 	nvl(ss.USERNAME,'ORACLE PROC') username,
    	se.SID,
    	VALUE cpu_usage
    from 	v$session ss, 
    	v$sesstat se, 
    	v$statname sn
    where  	se.STATISTIC# = sn.STATISTIC#
    and  	NAME like '%CPU used by this session%'
    and  	se.SID = ss.SID
    order  	by VALUE desc
    
    

    Resource Usage By User

    RESOURCE USAGE BY USER NOTES:

  • SID - Session ID
  • Username - Name of the user
  • Statistic - Name of the statistic
  • Value - Current value

    select 	ses.SID,
    	nvl(ses.USERNAME,'ORACLE PROC') username,
    	sn.NAME statistic,
    	sest.VALUE
    from 	v$session ses, 
    	v$statname sn, 
    	v$sesstat sest
    where 	ses.SID = sest.SID
    and 	sn.STATISTIC# = sest.STATISTIC#
    and 	sest.VALUE is not null
    and 	sest.VALUE != 0            
    order 	by ses.USERNAME, ses.SID, sn.NAME
    
    

    Session Stats By Session

    SESSION STAT NOTES:

  • Username - Name of the user
  • SID - Session ID
  • Statistic - Name of the statistic
  • Usage - Usage according to Oracle

    select  nvl(ss.USERNAME,'ORACLE PROC') username,
    	se.SID,
    	sn.NAME stastic,
    	VALUE usage
    from 	v$session ss, 
    	v$sesstat se, 
    	v$statname sn
    where  	se.STATISTIC# = sn.STATISTIC#
    and  	se.SID = ss.SID
    and	se.VALUE > 0
    order  	by sn.NAME, se.SID, se.VALUE desc
    
    

    Cursor Usage By Session

    CURSOR USAGE BY SESSION NOTES:

  • Username - Name of the user
  • Recursive Calls - Total number of recursive calls
  • Opened Cursors - Total number of opened cursors
  • Current Cursors - Number of cursor currently in use

    select 	user_process username,
    	"Recursive Calls",
    	"Opened Cursors",
    	"Current Cursors"
    from  (
    	select 	nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process, 
    			sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
    			sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
    			sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
    	from 	v$session ss, 
    		v$sesstat se, 
    		v$statname sn
    	where 	se.STATISTIC# = sn.STATISTIC#
    	and 	(NAME  like '%opened cursors current%'
    	or 	 NAME  like '%recursive calls%'
    	or 	 NAME  like '%opened cursors cumulative%')
    	and 	se.SID = ss.SID
    	and 	ss.USERNAME is not null
    	group 	by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
    )
    orasnap_user_cursors
    order 	by USER_PROCESS,"Recursive Calls"
    
    

    User Hit Ratios

    USER HIT RATIO NOTES:

  • Username - Name of the user
  • Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
  • DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
  • Physical Reads - The cumulative number of blocks read from disk.

  • Logical reads are the sum of consistent gets and db block gets.
  • The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
  • Hit ratio should be > 90%

    select	USERNAME,
    	CONSISTENT_GETS,
            BLOCK_GETS,
            PHYSICAL_READS,
            ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) Ratio
    from 	v$session, v$sess_io
    where 	v$session.SID = v$sess_io.SID
    and 	(CONSISTENT_GETS+BLOCK_GETS) > 0
    and 	USERNAME is not null
    order	by ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))