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