DBA Scripts - Very Useful - 12. Miscellaneous Reports

Miscellaneous Reports

Here are some scripts related to Miscellaneous Reports .

Write Request Queue

AVERAGE WRITE REQUEST QUEUE LENGTH NOTES:

  Summed Dirty Queue Length - Value for summed dirty queue length (total)

  Write Requests - Value for write requests (total)

  Write Request Queue Length - Average request queue length

select       sum(decode(NAME,'summed dirty queue length',VALUE,0)) summed_dirty_queue_length,

               sum(decode(NAME,'write requests',VALUE,0)) write_requests,

               sum(decode(NAME,'summed dirty queue length',VALUE,0)) / 

               sum(decode(NAME,'write requests',VALUE))  write_request_queue_length

from        v$sysstat

where       NAME in ('summed dirty queue length','write requests')

and          VALUE > 0

Lazy DBWR Indicators

LAZY DBWR INDICATOR NOTES:

  Dirty Buffers Inspected - Value for dirty buffers inspected (total)

  Dirty Buffers Inspected - Value for free buffers inspected (total)

select  sum(decode (NAME, 'dirty buffers inspected', VALUE)) dirty_buffers_inspected,

               sum(decode (NAME, 'free buffer inspected', VALUE)) free_buffers_inspected

from        

Lazy DBWR Indicators

where       NAME in ( 'dirty buffers inspected','free buffer inspected')

and          VALUE > 0

DBA Scripts - Very Useful - 8, 9. Full Table Scans, Data Dictionary Info

8. 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

9. Data Dictionary Info

Here are some scripts related to Data Dictionary Info .

Dictionary Cache

DATA DICTIONARY CACHE NOTES:

  Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.

  Gets - Total number of requests for information on the data object.

  Getmisses - Number of data requests resulting in cache misses.

  % Cache Misses - Miss Ratio

  Count - Total number of entries in the cache

  Usage - Number of cache entries that contain valid data.

  This report shows the DC cache statistics (a part of the shared pool).

  Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.)

  Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer.

  If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file)

select       PARAMETER,

               GETS,

               GETMISSES,

               round(GETMISSES/GETS,2)*100 "% Cache Misses",

               COUNT,

               USAGE

from        v$rowcache

where       GETS > 0

order        by (GETMISSES/GETS)*100 desc

Latch Gets/Misses

LATCH GET AND MISS NOTES:

  Latch Name - Name of the latch

  Gets - Number of times obtained wait

  Misses - Number of time obtained with wait but failed first try

  Gets / Misses % - Ratio of misses to gets

  Immediate Gets - Number of times obtained with no wait

  Immediate Misses - Number of times failed to get with no wait

select       NAME,

               GETS,

               MISSES,

               round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",

               IMMEDIATE_GETS,

               IMMEDIATE_MISSES

from        v$latch

where       GETS != 0

or            IMMEDIATE_MISSES > 0

order        by ((GETS-MISSES) / GETS) desc

DBA Scripts - Very Useful - 7. Rollback Segments

Here are some scripts related to Rollback Segments .

Segments

ROLLBACK INFORMATION NOTES:

  Segment Name - Name of the rollback segment.

  Owner - Owner of the rollback segment.

  Tablespace - Name of the tablespace containing the rollback segment.

  Segment ID - ID number of the rollback segment.

  File ID - ID number of the block containing the segment header.

  Block ID - Starting block number of the extent.

  Initial Extent - Initial extent size in bytes.

  Next Extent - Secondary extent size in bytes.

  Min Extents - Minimum number of extents.

  Max Extents - Maximum number of extents.

  PCT Increase - Percent increase for extent size.

  Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.

  Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .

select       SEGMENT_NAME,

               OWNER,

               TABLESPACE_NAME,

               SEGMENT_ID,

               FILE_ID,

               BLOCK_ID,

               INITIAL_EXTENT,

               NEXT_EXTENT,

               MIN_EXTENTS,

               MAX_EXTENTS,

               PCT_INCREASE,

               STATUS,

               INSTANCE_NUM

from        dba_rollback_segs

order        by SEGMENT_NAME

Transactions

ROLLBACK STATISTIC (TRANSACTION TABLES) NOTES:

  Statistic Name - Name of the statistic

  Value - Current value

  The name of the consistent changes statistic is misleading. It does not indicate the number of updates (or changes), but rather, the number of times a consistent get had to retrieve and "old" version of a block because of updates that occurred after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data blocks consistent reads - undo records applied; the new statistic gives the actual number of data records applied.

  The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism, which uses the SCN (System Change Number) to make sure the data being read has not changed sine the query was started.

  The data blocks consistent reads - undo records applied statistic reflects the number of updates (or changes) applied.

select NAME,

       VALUE

from   v$sysstat

where  name in (

       'consistent gets',

       'consistent changes',

       'transaction tables consistent reads - undo records applied',

       'transaction tables consistent read rollbacks',

       'data blocks consistent reads - undo records applied',

       'no work - consistent read gets',

       'cleanouts only - consistent read gets',

       'rollbacks only - consistent read gets',

       'cleanouts and rollbacks - consistent read gets')

order  by NAME

Contention

ROLLBACK CONTENTION NOTES:

  Segment Name - Name of the rollback segment.

  Seg# - Rollback segment number.

  Gets - Number of header gets.

  Waits - Number of header waits.

  Hit Ratio - Ratio of gets to waits. This should be >= 99%.

  Active Transactions - Number of active transactions.

  Writes - Number of bytes written to rollback segment.

  Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.

  Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention.

  select  b.NAME,

               a.USN seg#,

               GETS,

               WAITS,

               round(((GETS-WAITS)*100)/GETS,2) hit_ratio,

               XACTS active_transactions,

               WRITES

from        v$rollstat a,

               v$rollname b

where       a.USN = b.USN

Growth

ROLLBACK EXTENDING AND SHRINKAGE NOTES:

  Rollback Segment - Name of rollback segment.

  Seg# - Rollback segment number.

  Size - Size in bytes of the rollback segment.

  OptSize - Optimal size of rollback segment.

  HWM - High Water Mark of rollback segment size.

  Extends - Number of times rollback segment was extended to have a new extent.

  Wraps - Number of times rollback segment wraps from one extent to another.

  Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time.

  Average Shrink - Total size of freed extents divided by number of shrinks.

  Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data.

  Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.

select       NAME,

               a.USN,

               RSSIZE,

               OPTSIZE,

               HWMSIZE,

               EXTENDS,

               WRAPS,

               SHRINKS,

               AVESHRINK,

               AVEACTIVE,

               STATUS

from        v$rollstat a , 

               v$rollname b

where       a.USN=b.USN

order        by NAME

DBA Scripts - Very Useful - 5,6. Hit/Miss Ratios, User Information

5. Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

  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 > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select       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",

               round((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,2) "Hit Ratio"

from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

  Gets - Total number of requests for information on the data object.

  Cache Misses - Number of data requests resulting in cache misses

  Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select       sum(GETS),

               sum(GETMISSES),

               round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)

from        v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

  Pins - The number of times a pin was requested for objects of this namespace.

  Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  Hit Ratio should be > 85%

select       sum(PINS) Pins,

               sum(RELOADS) Reloads,

               round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from        v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

  Executions - The number of times a pin was requested for objects of this namespace.

  Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select       sum(PINS) Executions,

               sum(RELOADS) cache_misses,

               sum(RELOADS) / sum(PINS) miss_ratio

from        v$librarycache

6. User Information

Here are some scripts related to User Information .

User Objects

USER OBJECT NOTES:

  Username - Owner of the object(s)

  Tabs - Table(s)

  Inds - Index(es)

  Syns - Synonym(s)

  Views - Views(s)

  Seqs - Sequence(s)

  Procs - Procedure(s)

  Funcs - Function(s)

  Pkgs - Packages(s)

  Trigs - Trigger(s)

  Deps - Dependencies

select       USERNAME,

               count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,

               count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,

               count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,

               count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,

               count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,

               count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,

               count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,

               count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,

               count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,

               count(decode(o.TYPE#,10,o.OBJ#,'')) Deps

from        obj$ o,

               dba_users u

where       u.USER_ID = o.OWNER# (+)

group       by USERNAME

order        by USERNAME

Invalid Objects

INVALID OBJECT NOTES:

  Owner - Owner of the object

  Object Type - Type of object

  Object Name - Name of the object

  Status - Status of the object

select       OWNER,

               OBJECT_TYPE,

               OBJECT_NAME,

               STATUS

from        dba_objects

where       STATUS = 'INVALID'

order        by OWNER, OBJECT_TYPE, OBJECT_NAME

Object Modification

OBJECT MODIFICATION NOTES: (Modified in last 7 days)

  Owner - Owner of the object

  Object Name - Name of the object

  Object Type - Type of the object

  Last Modified - Last modification date/time

  Created - Object creation date/time

  Status - Status of the object

select       OWNER,

               OBJECT_NAME,

               OBJECT_TYPE,

               to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,

               to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,

               STATUS

from        dba_objects

where       (SYSDATE - LAST_DDL_TIME) < 7

order        by LAST_DDL_TIME DESC

User Privileges

USER PRIVILEGES NOTES:

  Grantee - Grantee name, user or role receiving the grant

  Granted Role - Granted role name

  Admin - Grant was with the ADMIN option

  Default - Role is designated as a DEFAULT ROLE for the user

  Privilege - System privilege

select       rp.GRANTEE,

               GRANTED_ROLE,

               rp.ADMIN_OPTION,

               DEFAULT_ROLE,

               PRIVILEGE

from        dba_role_privs rp, dba_sys_privs sp

where       rp.GRANTEE = sp.GRANTEE

and          rp.GRANTEE not in ('SYS','SYSTEM','DBA')

order        by  rp.GRANTEE, GRANTED_ROLE, PRIVILEGE