Temporary Tablespace Space Monitoring

Here are some quick SQL's to monitor the usage of TEMP Tablespace:

Space Usage of TEMP Tablespace:

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

Temp Space Usage by Session:

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

 
Temp Space Usage by Statement:
 

SELECT S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

Waits Related to Initialization Parameters

Wait Problem: Potential Fix
Free buffer: Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code
Buffer busy:  Segment Header — Add freelists or freelist groups or use ASSM
Buffer busy:  Data Block — Separate hot data; use reverse key indexes; small block sizes
Buffer busy:  Data Block — Increase initrans and/or maxtrans
Buffer busy:  Undo Header —Use automatic undo management
Buffer busy:  Undo Block — Commit more; use automatic undo management
Latch free:  Investigate the detail (listing in next table of this chapter for fixes)
Log buffer space: Increase the log buffer; use faster disks for the redo logs
Scattered read: Indicates many full table scans — tune the code; cache small tables
Sequential read: Indicates many index reads — tune the code (especially joins)
Write complete waits: Adds database writers; checkpoint more often; buffer cache too small

Latch Problem: Potential Fix

Library cache:  Use bind variables; adjust the shared_pool_size
Shared pool:  Use bind variables; adjust the shared_pool_size
Row cache objects: Increase the shared pool. This is not a common problem.
Cache buffers chain:  If you get this latch wait, it means you need to reduce logical I/O rates by tuning and minimizing the I/O requirements of the SQL involved. High I/O rates could be a sign of a hot block (meaning
a block highly accessed). Cache buffer lru chain latch contention can be resolved by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache. You should adjust DB_BLOCK_BUFFERS, and possible DB_BLOCK_SIZE. Multiple buffer pools can help
reduce contention on this latch. You can create additional cache buffer lru chain latches by adjusting the configuration parameter DB_BLOCK_LRU_LATCHES. You may be able to reduce the load on the cache buffer chain latches by increasing the configuration parameter. _DB_BLOCK_HASH_BUCKETS may need to be increased or set to a prime number (in pre-9i versions).

Library Cache SQL

The following query uses the V$LIBRARYCACHE view to examine the reload ratio in the library cache:

select Sum(Pins) "Hits",

Sum(Reloads) "Misses",

((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"

from V$LibraryCache;

Data Dictionary Cache Hit Ratio SQL

SQL:

select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"

from V$RowCache

where Gets + GetMisses <> 0;

Session or System Modifiable Parameter SQL

There are two key fields in the V$PARAMETER view:
■ ISSES_MODIFIABLE Indicates if a user with the ALTER SESSION privilege can modify
this initialization parameter for their session.

■ ISSYS_MODIFIABLE Indicates if someone with ALTER SYSTEM privilege can modify
this particular parameter.


This query displays the initialization parameters that can be modified with an ALTER SYSTEM or ALTER SESSION command (partial result displayed):

select name, value, isdefault, isses_modifiable, issys_modifiable

from v$parameter

where issys_modifiable <> 'FALSE'

or isses_modifiable <> 'FALSE'

order by name;

Data Cache Hit Ratio SQL

Query to view the data cache hit ratio:

column phys format 999,999,999 heading 'Physical Reads'

column gets format 999,999,999 heading ' DB Block Gets'

column con_gets format 999,999,999 heading 'Consistent Gets'

column hitratio format 999.99 heading ' Hit Ratio '

select sum(decode(name,'physical reads',value,0)) phys,

sum(decode(name,'db block gets',value,0)) gets,

sum(decode(name,'consistent gets', value,0)) con_gets,

(1 - (sum(decode(name,'physical reads',value,0)) /

(sum(decode(name,'db block gets',value,0)) +

sum(decode(name,'consistent gets',value,0))))) * 100 hitratio

from v$sysstat;

Cardinality in Oracle Table Joins

Cardinatly: This is the number of rows that Oracle expects to process or expects to be the number rows that will be returned after joining the tables from the join condition.

More to follow...............

Check RMAN Backup Status

SQL to report on all backups i.e., Full, Incremental and Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SQL to report on all Full and Incremental backup but not Archive log backups:

col STATUS format a9

col hrs format 999.99

select SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,

elapsed_seconds/3600 hrs

from V$RMAN_BACKUP_JOB_DETAILS

where input_type='DB INCR'

order by session_key;

To check progress of RMAN Backup:

select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,

sysdate + TIME_REMAINING/3600/24 end_at

from v$session_longops

where totalwork > sofar

AND opname NOT LIKE '%aggregate%'

AND opname like 'RMAN%';