Oracle Performance Tuning

Name: DB Block Efficiency

select round((1-(pr.value/(bg.value+cg.value)))*100,2)

from v$sysstat pr, v$sysstat bg, v$sysstat cg

where pr.name = 'physical reads'

and bg.name = 'db block gets'

and cg.name = 'consistent gets'

The init.ora parameter: DB_BLOCK_BUFFERS controls

the amount of memory allocated for the data cache.

When an application requests data, Oracle first

attempts to find it in the data cache.

The more often Oracle finds requested data in memory

a physical IO is avoided, and thus overall performance

is better. Under normal circumstances this ratio

should be greater than or equal to 95%. Initially

set the DB_BLOCK_BUFFERS size to be 20 - 50% the

size of the SGA.

Name: Dictionary Cache Efficiency

select round(sum(gets)/(sum(gets)+sum(getmisses)) * 100,2)

from v$rowcache;

The init.ora parameter: SHARED_POOL_SIZE controls the amount of

memory allocated for the shared buffer pool.

The shared buffer pool contains SQL and PL/SQL statements

(library cache), the data dictionary cache,

and information on data base sessions.

This percentage will never equal 100 because the cache

must perform an initial load when Oracle first starts up.

The percentage, therefore, should continually get closer

to 100 as the system stays "up."

Ideally, the entire data dictionary would be cached in memory.

Initially set the SHARED_POOL_SIZE to be 50-100% the size of

the init.ora parameter: DB_BLOCK_BUFFERS - then fine tune the

parameter.

Name: Disk Reads Max SQL

select sql_text

from v$sqlarea, v$session

where address = sql_address

and username is not null

and disk_reads/executions =

(select max(disk_reads/executions)

from v$sqlarea, v$session

where address = sql_address

and username is not null)

This query returns the first 1000 bytes of the SQL statement

having the highest number of disk reads per execution.

This query is designed to help determine the user generated

SQL causing a large number of disk reads per statement execution.

Name: Disk Reads Max User

select username

from v$sqlarea, v$session

where address = sql_address

and username is not null

and disk_reads/executions =

(select max(disk_reads/executions)

from v$sqlarea, v$session

where address = sql_address

and username is not null)

This query returns the username associated with the SQL

statement having the highest number of disk reads per execution.

This query is designed to help determine the user causing

a large number of disk reads per statement execution.