Oracle Session Statistics

on

Session Statistics



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))
    ================================================================================= 

  • Full Table Scans


  • Here are some scripts related to Full Table Scans .

    System Statistics (Table)

    SYSTEM STATISTICS (TABLE) NOTES:
  • Statistic Name - Name of the statistic
  • Bytes - Size
  • This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.
  • Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
  • Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
  • Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
  • Table scans rows gotten reflect the cumulative number of rows read for full table scans.
  • Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
  • Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
  • Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
  • Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.

    select 	NAME,
	VALUE
from 	v$sysstat
where	NAME like '%table'

    Process Table Scans

    PROCESS TABLE SCAN NOTES:
  • User Process - Name of user process
  • Long Scans - Full scan of a table that has > 5 database blocks.
  • Short Scans - Full scan of a table that has <= 5 database blocks.
  • Row Retrieved - Cumulative number of rows read for full table scans.

    select 	ss.username||'('||se.sid||') ' "User Process",
	sum(decode(name,'table scans (short tables)',value)) "Short Scans",
	sum(decode(name,'table scans (long tables)', value)) "Long Scans",
	sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"
from 	v$session ss,
	v$sesstat se,
	v$statname sn
where 	se.statistic# = sn.statistic#
and 	(name  like '%table scans (short tables)%'
or 	 name  like '%table scans (long tables)%'
or 	 name  like '%table scan rows gotten%')
and 	se.sid = ss.sid
and 	ss.username is not null
group 	by ss.username||'('||se.sid||') '

    Process Table Scans (Avg)

    PROCESS TABLE SCAN (AVERAGE) NOTES:
  • User Process - Name of user process
  • Short Scans - Number of short scans (<= 5 blocks)
  • Long Scans - Number of long scans (> 5 blocks)
  • Rows Retrieved - Number of rows retrieved
  • Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)

    select 	ss.username||'('||se.sid||') ' "User Process",
	sum(decode(name,'table scans (short tables)',value)) "Short Scans",
	sum(decode(name,'table scans (long tables)', value)) "Long Scans",
	sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
from 	v$session ss,
	v$sesstat se,
	v$statname sn
where  	se.statistic# = sn.statistic#
and    	(name  like '%table scans (short tables)%'
or 	 name  like '%table scans (long tables)%'
or 	 name  like '%table scan rows gotten%')
and 	se.sid = ss.sid
and 	ss.username is not null
group	by ss.username||'('||se.sid||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 desc

  • =======================================================================

  • Shared Pool Information


  • Here are some scripts related to Shared Pool Information .

    Quick Check

    SHARED POOL QUICK CHECK NOTES:

    select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES  Logic
from 	v$shared_pool_reserved
where 	REQUEST_FAILURES > 0
and 	0 != (
	select 	to_number(VALUE) 
        from 	v$parameter 
        where 	NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
       'Request Failures = '||REQUEST_FAILURES Logic
from 	v$shared_pool_reserved
where 	REQUEST_FAILURES < 5
and 	0 != ( 
	select 	to_number(VALUE) 
	from 	v$parameter 
	where 	NAME = 'shared_pool_reserved_size')

    Memory Usage

    SHARED POOL MEMORY USAGE NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Sharable Memory - Amount of sharable memory in the shared pool consumed by the object

    select 	OWNER,
	NAME||' - '||TYPE object,
	SHARABLE_MEM
from 	v$db_object_cache
where 	SHARABLE_MEM > 10000 
and	type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order 	by SHARABLE_MEM desc

    Loads

    LOADS INTO SHARED POOL NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.

    select 	OWNER,
	NAME||' - '||TYPE object,
	LOADS
from 	v$db_object_cache
where 	LOADS > 3 
and 	type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order 	by LOADS desc

    Executions

    SHARED POOL EXECUTION NOTES:
  • Owner - Owner of the object
  • Object - Name/namespace of the object
  • Executions - Total number of times this object has been executed

    select 	OWNER,
	NAME||' - '||TYPE object,
	EXECUTIONS
from 	v$db_object_cache
where 	EXECUTIONS > 100 
and 	type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order 	by EXECUTIONS desc

    Details

    SHARED POOL DETAIL NOTES:
  • Owner - Owner of the object
  • Name - Name of the object
  • DB Link - Database link name, if any
  • Namespace - Namespace of the object
  • Type - Type of the object
  • Sharable Memory - Amount of sharable memory in the shared pool consumed by the object
  • Loads - Number of times the object has been loaded. This count also increases when an object has been invalidated.
  • Executions - Total number of times this object has been executed
  • Locks - Number of users currently locking this object
  • Pins - Number of users currently pinning this object

    select	OWNER,
	NAME,
	DB_LINK,
	NAMESPACE,
	TYPE,
        SHARABLE_MEM,
        LOADS,
        EXECUTIONS,
        LOCKS,
        PINS
from 	v$db_object_cache
order 	by OWNER, NAME

    Library Cache Statistics

    SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:
  • Namespace - Library cache namespace (SQL AREA, TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, PIPE)
  • Gets - Number of times the system requests handles to library objects belonging to this namespace
  • GetHits - Number of times the handles are already allocated in the cache. If the handle is not already allocated, it is a miss. The handle is then allocated and inserted into the cache.
  • GetHit Ratio - Number of GETHITS divided by GETS. Values close to 1 indicate that most of the handles the system has tried to get are cached.
  • Pins - Number of times the system issues pin requests for objects in the cache in order to access them.
  • PinHits - Number of times that objects the system is pinning and accessing are already allocated and initialized in the cache. Otherwise, it is a miss, and the system has to allocate it in the cache and initialize it with data queried from the database or generate the data.
  • PinHit Ratio - Number of PINHITS divided by number of PINS. Values close to 1 indicate that most of the objects the system has tried to pin and access have been cached.
  • Reloads - Number of times that library objects have to be reinitialized and reloaded with data because they have been aged out or invalidated.
  • Invalidations - Number of times that non-persistent library objects (like shared SQL areas) have been invalidated.
  • GetHit Ratio and PinHit Ratio should be > 70

    select 	NAMESPACE,
	GETS,
	GETHITS,
	round(GETHITRATIO*100,2) gethit_ratio,
	PINS,
	PINHITS,
	round(PINHITRATIO*100,2) pinhit_ratio,
	RELOADS,
	INVALIDATIONS
from 	v$librarycache

    Reserve Pool Settings

    SHARED POOL RESERVED SIZE NOTES:
  • Parameter - Name of the parameter
  • Value - Current value for the parameter
  • shared_pool_reserved_size - Controls the amount of SHARED_POOL_SIZE reserved for large allocations. The fixed view V$SHARED_POOL_RESERVED helps you tune these parameters. Begin this tuning only after performing all other shared pool tuning on the system.
  • shared_pool_reserved_min_alloc - Controls allocation for the reserved memory. To create a reserved list, SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC. Only allocations larger than SHARED_POOL_RESERVED_POOL_MIN_ALLOC can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool's free lists. The default value of SHARED_POOL_RESERVED_MIN_ALLOC should be adequate for most systems.

    select 	NAME,
	VALUE
from 	v$parameter
where 	NAME like '%reser%'

    Pinned Objects

    PINNED OBJECT NOTES:
  • Object Name - Name of the object
  • Object Type - Type of the object (INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK)
  • Kept Status - YES or NO, depending on whether this object has been "kept" (permanently pinned in memory) with the PL/SQL procedure DBMS_SHARED_POOL.KEEP

    select 	NAME,
	TYPE,
	KEPT
from 	v$db_object_cache
where 	KEPT = 'YES'

  • ======================================================================

  • Cursor/SQL Processing


  • Here are some scripts related to Cursor/SQL Processing .

    Disk Intensive SQL

    SQL WITH MOST DISK READ NOTES:
  • Username - Name of the user
  • Disk Reads - Total number of disk reads for this statement
  • Executions - Total number of times this statement has been executed
  • Reads/Execs - Number of reads per execution
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select 	a.USERNAME,
	DISK_READS,
	EXECUTIONS,
	round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",
	SQL_TEXT
from 	dba_users a, v$session, v$sqlarea
where 	PARSING_USER_ID=USER_ID 
and 	ADDRESS=SQL_ADDRESS(+) 
and 	DISK_READS > 10000
order  	by DISK_READS desc, EXECUTIONS desc

    Buffer Intensive SQL

    SQL WITH MOST BUFFER SCAN NOTES:
  • Username - Name of the user
  • Buffer Gets - Total number of buffer gets for this statement
  • Executions - Total number of times this statment has been executed
  • Gets/Execs - Number of buffer gets per execution
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select	EXECUTIONS,
	BUFFER_GETS,
	round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS) / 400,2) "Gets/Execs",
	SQL_TEXT
from 	v$sqlarea
where  	BUFFER_GETS / decode(EXECUTIONS,0,1, EXECUTIONS) / 400 > 10 
order  	by EXECUTIONS desc

    Buffer SQL w/ Most Loads

    SQL WITH MOST LOAD NOTES:
  • Loads - Number of times the cursor has been loaded after the body of the cursor has been aged out of the cache while the text of the SQL statement remained in it, or after the cursor is invalidated
  • First Load Time - Time at which the cursor was first loaded into the SGA
  • Sorts - Number of sorts performed by the SQL statement
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select 	LOADS,
	FIRST_LOAD_TIME,
	SORTS,
	SQL_TEXT
from 	v$sqlarea
where  	LOADS > 50 
order  	by EXECUTIONS desc

    Open Cursors By User

    OPEN CURSORS BY USER NOTES:
  • Username - Name of user
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select	nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,
	SQL_TEXT
from 	v$open_cursor oc, 
	v$session s
where 	s.SADDR = oc.SADDR
order 	by 1

    Running Cursors By User

    RUNNING CURSORS BY USER NOTES:
  • Username - Name of user
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select	nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,
	SQL_TEXT
from 	v$open_cursor oc, v$session s
where 	s.SQL_ADDRESS = oc.ADDRESS
and 	s.SQL_HASH_VALUE = oc.HASH_VALUE
order 	by 1

    LR Open Cursors

    OPEN CURSORS WITH LOW HIT RATIO NOTES:
  • Username - Name of user
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select	nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' username,
	SQL_TEXT
from	v$open_cursor oc0, v$session se0
where 	se0.SADDR = oc0.SADDR   
and	se0.USERNAME != 'SYS'
and    	60 < (
		select 	"Hit Ratio" 
		from 	(
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads", 
	(
	(sum(decode(NAME, 'consistent gets',value, 0)) +
	 sum(decode(NAME, 'db block gets',value, 0)) -
	 sum(decode(NAME, 'physical reads',value, 0)))
	/
	(sum(decode(NAME, 'consistent gets',value, 0)) +
	 sum(decode(NAME, 'db block gets',value, 0))) * 100)
	 "Hit Ratio" 
from 	v$sesstat ss, v$statname sn, v$session se
where 	ss.SID = se.SID
and	sn.STATISTIC# = ss.STATISTIC#
and 	VALUE != 0
and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 
group 	by se.USERNAME, se.SID
) XX
		where 	nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' = "User Session")
order 	by nvl(se0.USERNAME,'ORACLE'), se0.SID

    LR Running Cursors

    RUNNING CURSORS WITH LOW HIT RATIO NOTES:
  • Username - Name of user
  • SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

    select	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||'),
	SQL_TEXT
from 	v$open_cursor oc0, v$session se0
where  	se0.SQL_ADDRESS = oc0.ADDRESS 
and 	se0.SQL_HASH_VALUE = oc0.HASH_VALUE 
and	se0.username != 'SYS'
and    	60 > (
	select 	"Hit Ratio" 
	from (
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads", 
		(
		(sum(decode(NAME, 'consistent gets',value, 0)) +
		 sum(decode(NAME, 'db block gets',value, 0)) -
		 sum(decode(NAME, 'physical reads',value, 0)))
		/
		(sum(decode(NAME, 'consistent gets',value, 0)) +
		 sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio" 
from 	v$sesstat ss, v$statname sn, v$session se
where 	ss.SID = se.SID
and	sn.STATISTIC# = ss.STATISTIC#
and 	VALUE != 0
and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 
group 	by se.USERNAME, se.SID
)
        where 	nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')' = "User Session")
order 	by nvl(se0.username,'ORACLE'), se0.sid

    LR Objects Access

    OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:
  • Username - Name of the user
  • Object Owner - Owner of the object
  • Object - Name of the object

    select	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' username,
	OWNER,
	OBJECT
from 	v$access ac, v$session se0
where 	ac.SID    = se0.SID
and  	ac.TYPE   = 'TABLE'
and 	60 < (
	select 	"Hit Ratio" 
	from
(
select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",
	sum(decode(NAME, 'consistent gets',value, 0))  "Consistent Gets",
	sum(decode(NAME, 'db block gets',value, 0))  "DB Block Gets",
	sum(decode(NAME, 'physical reads',value, 0))  "Physical Reads", 
	(
		(sum(decode(NAME, 'consistent gets',value, 0)) +
		 sum(decode(NAME, 'db block gets',value, 0)) -
		 sum(decode(NAME, 'physical reads',value, 0)))
		/
		(sum(decode(NAME, 'consistent gets',value, 0)) +
		 sum(decode(NAME, 'db block gets',value, 0))) * 100) "Hit Ratio" 
from 	v$sesstat ss, 
	v$statname sn, 
	v$session se
where 	ss.SID = se.SID
and	sn.STATISTIC# = ss.STATISTIC#
and 	VALUE != 0
and 	sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 
group 	by se.USERNAME, se.SID
)
	where 	nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' = "User Session")
order 	by USERNAME,se0.SID,OWNER

  • =======================================================================

  • Tables/Indexes


  • Here are some scripts related to Tables/Indexes .

    Tabs w/ Questionable Inds

    TABLES WITH QUESTIONABLE INDEX(ES) NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table
  • Column - Name of the column in question
  • The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).
  • The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.

    select 	TABLE_OWNER,
	TABLE_NAME,
	COLUMN_NAME
from  	dba_ind_columns 
where  	COLUMN_POSITION=1
and  	TABLE_OWNER not in ('SYS','SYSTEM')
group  	by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having  count(*) > 1

    Tabs With More Than 5 Inds

    TABLES WITH MORE THAN 5 INDEXES NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table
  • Index Count - Number of indexes

    select 	OWNER,
	TABLE_NAME,
	COUNT(*) index_count
from  	dba_indexes 
where  	OWNER not in ('SYS','SYSTEM')
group  	by OWNER, TABLE_NAME 
having  COUNT(*) > 5 
order 	by COUNT(*) desc, OWNER, TABLE_NAME

    Tables With No Indexes

    TABLES WITHOUT INDEXES NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table

    select 	OWNER,
	TABLE_NAME
from 
(
select 	OWNER, 
	TABLE_NAME 
from 	dba_tables
minus
select 	TABLE_OWNER, 
	TABLE_NAME 
from 	dba_indexes
)
orasnap_noindex
where	OWNER not in ('SYS','SYSTEM')
order 	by OWNER,TABLE_NAME

    Tables With No PK

    NO PRIMARY KEY NOTES:
  • Table Owner - Owner of the table
  • Table Name - Name of the table

    select  OWNER,
	TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and 	OWNER not in ('SYS','SYSTEM')
order	by OWNER, TABLE_NAME

    Disabled Constraints

    DISABLED CONSTRAINT NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table
  • Constraint Name - Name of the constraint
  • Constraint Type - Type of constraint
  • Status - Current status of the constraint

    select  OWNER,
        TABLE_NAME,
        CONSTRAINT_NAME,
        decode(CONSTRAINT_TYPE, 'C','Check',
                                'P','Primary Key',
                                'U','Unique',
                                'R','Foreign Key',
                                'V','With Check Option') type,
        STATUS 
from 	dba_constraints
where 	STATUS = 'DISABLED'
order 	by OWNER, TABLE_NAME, CONSTRAINT_NAME

    FK Constraints

    FOREIGN KEY CONSTRAINTS NOTES:
  • Table Owner - Owner of the table
  • Table Name - Name of the table
  • Constraint Name - Name of the constraint
  • Column Name - Name of the column
  • Referenced Table - Name of the referenced table
  • Reference Column - Name of the referenced column
  • Position - Position of the column

    select 	c.OWNER,
	c.TABLE_NAME,
	c.CONSTRAINT_NAME,
	cc.COLUMN_NAME,
	r.TABLE_NAME,
	rc.COLUMN_NAME,
	cc.POSITION
from 	dba_constraints c, 
	dba_constraints r, 
	dba_cons_columns cc, 
	dba_cons_columns rc
where 	c.CONSTRAINT_TYPE = 'R'
and 	c.OWNER not in ('SYS','SYSTEM')
and 	c.R_OWNER = r.OWNER
and 	c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and 	c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and 	c.OWNER = cc.OWNER
and 	r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and 	r.OWNER = rc.OWNER
and 	cc.POSITION = rc.POSITION
order 	by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION

    FK Index Problems

    FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:
  • Owner - Owner of the table
  • Constraint Name - Name of the constraint
  • Column Name - Name of the column
  • Position - Position of the index
  • Problem - Nature of the problem
  • It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

    select 	acc.OWNER,
	acc.CONSTRAINT_NAME,
	acc.COLUMN_NAME,
	acc.POSITION,
	'No Index' Problem
from   	dba_cons_columns acc, 
	dba_constraints ac
where  	ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and   	ac.CONSTRAINT_TYPE = 'R'
and     acc.OWNER not in ('SYS','SYSTEM')
and     not exists (
        select  'TRUE' 
        from    dba_ind_columns b
        where   b.TABLE_OWNER = acc.OWNER
        and     b.TABLE_NAME = acc.TABLE_NAME
        and     b.COLUMN_NAME = acc.COLUMN_NAME
        and     b.COLUMN_POSITION = acc.POSITION)
order   by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

    Inconsistent Column Names

    INCONSISTENT COLUMN DATATYPE NOTES:
  • Owner - Owner of the table
  • Column - Name of the column
  • Table Name - Name of the table
  • Datatype - Datatype of the column

    select 	OWNER,
	COLUMN_NAME,
	TABLE_NAME,
	decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from 	dba_tab_columns 
where  	(COLUMN_NAME, OWNER) in
		(select	COLUMN_NAME, 
			OWNER
	 	 from 	dba_tab_columns
	 	 group	by COLUMN_NAME, OWNER
	  	 having	min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
		 	max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and 	OWNER not in ('SYS', 'SYSTEM')
order	by COLUMN_NAME,DATA_TYPE

    Object Extent Warning

    TABLES THAT CANNOT EXTEND NOTES:
  • Owner - Owner of the object
  • Object Name - Name of the object
  • Object Type - Type of object
  • Tablespace - Name of the tablespace
  • Next Extent - Size of next extent (bytes)

    select 	OWNER,
	SEGMENT_NAME,
	SEGMENT_TYPE,
	TABLESPACE_NAME,
	NEXT_EXTENT
from (
	select 	seg.OWNER, 
		seg.SEGMENT_NAME,
			seg.SEGMENT_TYPE, 
		seg.TABLESPACE_NAME,
			t.NEXT_EXTENT
	from 	dba_segments seg,
			dba_tables t
	where 	(seg.SEGMENT_TYPE = 'TABLE'
	and  	 seg.SEGMENT_NAME = t.TABLE_NAME
	and  	 seg.owner = t.OWNER
	and    NOT EXISTS (
			select 	TABLESPACE_NAME
				from 	dba_free_space free
				where 	free.TABLESPACE_NAME = t.TABLESPACE_NAME
				and 	BYTES >= t.NEXT_EXTENT))
	union
	select 	seg.OWNER, 
		seg.SEGMENT_NAME,
			seg.SEGMENT_TYPE, 
		seg.TABLESPACE_NAME,
			c.NEXT_EXTENT
	from 	dba_segments seg,
			dba_clusters c 
	where  	(seg.SEGMENT_TYPE = 'CLUSTER'
	and    	 seg.SEGMENT_NAME = c.CLUSTER_NAME
	and    	 seg.OWNER = c.OWNER
	and    	NOT EXISTS (
			select 	TABLESPACE_NAME
			from 	dba_free_space free
			where 	free.TABLESPACE_NAME = c.TABLESPACE_NAME
			and 	BYTES >= c.NEXT_EXTENT))
	union
	select 	seg.OWNER, 
		seg.SEGMENT_NAME,
			seg.SEGMENT_TYPE, 
		seg.TABLESPACE_NAME,
			i.NEXT_EXTENT
	from 	dba_segments seg,
			dba_indexes  i
	where  	(seg.SEGMENT_TYPE = 'INDEX'
	and    	 seg.SEGMENT_NAME = i.INDEX_NAME
	and    	 seg.OWNER        = i.OWNER
	and    	 NOT EXISTS (
			select 	TABLESPACE_NAME
					from 	dba_free_space free
					where 	free.TABLESPACE_NAME = i.TABLESPACE_NAME
			and 	BYTES >= i.NEXT_EXTENT))
	union
	select 	seg.OWNER, 
		seg.SEGMENT_NAME,
			seg.SEGMENT_TYPE, 
		seg.TABLESPACE_NAME,
			r.NEXT_EXTENT
	from 	dba_segments seg,
			dba_rollback_segs r
	where  	(seg.SEGMENT_TYPE = 'ROLLBACK'
	and    	 seg.SEGMENT_NAME = r.SEGMENT_NAME
	and    	 seg.OWNER        = r.OWNER
	and    	 NOT EXISTS (
			select	TABLESPACE_NAME
					from 	dba_free_space free
					where 	free.TABLESPACE_NAME = r.TABLESPACE_NAME
                and 	BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order 	by OWNER,SEGMENT_NAME

    Segment Fragmentation

    OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:
  • Owner - Owner of the object
  • Tablespace Name - Name of the tablespace
  • Segment Name - Name of the segment
  • Segment Type - Type of segment
  • Size - Size of the object (bytes)
  • Extents - Current number of extents
  • Max Extents - Maximum extents for the segment
  • Percentage - Percentage of extents in use
  • As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.
  • To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7
  • Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017
  • Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.

    select 	OWNER,
	TABLESPACE_NAME,
	SEGMENT_NAME,
	SEGMENT_TYPE,
	BYTES,
	EXTENTS,
	MAX_EXTENTS,
	(EXTENTS/MAX_EXTENTS)*100 percentage
from 	dba_segments
where 	SEGMENT_TYPE in ('TABLE','INDEX')
and 	EXTENTS > MAX_EXTENTS/2
order 	by (EXTENTS/MAX_EXTENTS) desc

    Extents reaching maximum

    TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :
  • Owner - Owner of the segment
  • Segment Name - Name of the segment

    select owner "Owner",
       segment_name "Segment Name",
       segment_type "Type",
       tablespace_name "Tablespace",
       extents "Ext",
       max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3) 
and owner not in ('SYS','SYSTEM')
order by owner, segment_name

    Analyzed Tables

    ANALYZED TABLE NOTES:
  • Owner - Owner of the table
  • Analyzed - Number of analyzed tables
  • Not Analyzed - Number of tables that have not be analyzed
  • Total - Total number of tables owned by user
  • The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.
  • A COMPUTE will cause a table-level lock to be placed on the table during the operation.

    select	OWNER,
	sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
	sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
	count(TABLE_NAME) total
from 	dba_tables
where 	OWNER not in ('SYS', 'SYSTEM')
group 	by OWNER

    Recently Analyzed Tables

    LAST ANALYZED TABLE NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table
  • Last Analyzed - Last analyzed date/time

    select 	OWNER,
	TABLE_NAME,
	to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from 	dba_tab_columns
where 	OWNER not in ('SYS','SYSTEM')
and 	LAST_ANALYZED is not null
and	COLUMN_ID=1
and 	(SYSDATE-LAST_ANALYZED) < 30
order	by (SYSDATE-LAST_ANALYZED)

    Cached Tables

    CACHED TABLE NOTES:
  • Owner - Owner of the table
  • Table Name - Name of the table
  • Cache - Cached?

  • Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads.

    select 	OWNER,
	TABLE_NAME,
	CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME
    ========================================================================================

  • Note==> taking reference from http://vsbabu.org/oracle/ and 
  • http://www.oracle-books.com/orasnap/example/index.htm

    • Comments

    Post a Comment

    Oracle DBA Information