home

pga sorts resource manager locks    

 

     
physical disk usage buffer cache hit ratio per session buffer cache hit ratio

 

Tablespace info

NoLOGGING can be set at the table, index or tablespace level

Data dictionary objects, rollback segments, tables,indexes, and very large objects should have separate tablespaces

If a temporary tablespace is not specified, the system tablespace is used

The system tablespace should only contain objects owned by sys

 

 

check Points

Instance recovery is faster with frequent checkpointing

Frequent checkpointing causes more writes by DBWn processes to the datafiles

if DBWn has not finished checkpointing a file the LGWR has to wait if it needs the file again

When a log group is filled a checkpoint occurs

DBWn writes all or part of the dirty blocks to the datafiles

CKPT updates the data file headers and the control file

ARCn archives the filled online redo log files

Checkpoint not complete; unable to allocate file means checkpoints are ocurring to frequently

 

 

Instance Recovery

Changing the number of redo log groups and members will affect performance

Changing the number of redo log groups and memebers will not affect the time required to instance recovery

Changing the size of the redo log members will affect instance recovery time

 

Redo logs

Place each member of a group on a seperate device

Must have at least 2 groups

Only need 1 log, but use more than one for safety

DBWn process should write to a different disk than the ARCn process reads from

 

 

parameters

sort_area_size

sort_area_retained_size

sort_multiblock_read_count

sort_area_size* 2 * degree of parallelis - The amount of memory required for a parallel query that will perform a sort

 

 

Tuning Views

v$latch - the number of unsuccesful immediate request for a latch - immediate misses

v$lock - display the ID of the session locking an object and the type of lock being held

v$sesstat - all the calculated session statistics

v$session

v$statname

v$event_name

v$session_wait

v$session_event

v$rollstat - sum of waits to the sum of gets - contention for the redo log buffer

v$sort_segment - total_extents for the temporary tablespace

 

 

Oracle Shared Server

Incurs some CPU overhead

supports Oracle Net functionality

increases the number of concurrent users

is advantageous for database-intensive work

 

 

 

Row Migration
Run ANALYZE TABLE .. list compute statistics , export the table data and import the data back into the table

 

Dispatchers 
select decode(totalq. 0, 'No Requests'
, wait/totalq||'hundredths of seconds')"Average Wait Time Per Requests"
from v$queue q
where q.type!='DISPATCHES



select decode(sum(totalq),0, 'No Responses'
, SUM(wait)/SUM(totalq)) "Average wait time"
from v$queue q, v$dispatcher d
where q.type = 'DISPATCHER'
and q.paddr = d.paddr;

 

Physical Disk Usage
SELECT f.phyrds “Physical Reads”, f.phywrts “Physical Writes”, d.name “File Name”
FROM v$datafile d, v$filestat f where d.file#=f.file#
ORDER BY d.name;

 

buffer cache hit ratio
SELECT 1 – ( physical.value / (blockgets.value+consistent.value)) “Buffer Cache Hit Ratio”
FROM v$sysstat physical, v$sysstat blockgets, v$sysstat consistent WHERE physical.name = ’physical reads’
AND blockgets.name = ’db block gets’
AND consistent.name = ’consistent gets’;

 

per session buffer cache hit ratio 
SELECT ses.username, ses.osuser
, 1 – (io.physical_reads / io.block_gets + io.consistent_gets))) “Hit Ratio”
FROM v$sess_io io, v$session ses
WHERE io.sid = ses.sid
AND (io.block_gets + io.consistent_gets) ! = 0
AND ses.username IS NOT NULL;