Wednesday, July 8, 2009

Performance tuning in oracle 10g database

Performance tuning in oracle 10g database:

Current sessions and their activity in oracle 10g:

The following query statement can be used to disaply all the current sessions in current instance/db:

-- GV$INSTANCE view for RAC database
--- v$instance for single instance database

select sid, serial#, username, status, osuser,process, machine, program,type,module, event,
decode(command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
command||': Other') command
from v$session
where username is not null

How to find Oracle Buffer Cache Hit Ratio:

Here are the few sql statements that can be used whether oracle database is functioning well or not, however buffer cache hit ratio is an indicator and can not be used as a prime factor when tuning database, one has to look more into oracle wait events and V$DB_CACHE_ADVICE view data before tuning buffer chache.
The following statement can be used to find buffer cache hit ratio in oracle 10g.

select
100*(1 - (v3.value / (v1.value + v2.value))) "Buffer Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets from cache' and
v2.name = 'consistent gets from cache' and
v3.name = 'physical reads cache'

There will not be much use of increasing buffer cache in case one doing full table scans or operations that are not using the buffer cache. Running oracle database under automatic memory management mode is best option.

The following sql stements list the sql statements that are waiting for some action to be completed and/or waiting for some resource to be available or currently running in instance. the text that is given below is simple and I feel it can help us to find status of running statements with wait events details

Oracle Library Cache Hit Ratio:

Here is the simple query
to find oracle library cache hit ratio, the more hit ratio you see the more efficiently the library cache is being used and should be more than 96% or even 99%

select sum(pinhits)/sum(pins)
,100* (sum(pinhits)/sum(pins) ) "Library Cache Hit Ratio" from v$librarycache

The best way I feel to decide whether to increase library cache allocated memory or not is based on reloads column of v$librarycache view. the values should be near to 0.

shared_pool_size is what we need to increase in case required to do so for library cache.

HOW TO FIND FREE MEMORY IN SGA:

The amount of free memory can be find using v$sgastat view and here is the simple query to find SGA free memory

select * from v$sgastat where name like '%free memory%'

Dictionary Cache hit ratio:

SELECT sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) "Dictionary Cache hit ratio"
FROM V$ROWCACHE
WHERE gets > 0


we do not have to worry in case it returns 0 records as it shows no load on dictionary cache. we have to find at parameter level in case hit ration is not acceptable and once can find by grouping parameter column of v$rowache to zero on exact problems and able to see in
granularity level.

Redo Log Buffer configuration:

The following statement tell us whether redo log buffer is well adequate or not, however one has to check how check point and archiving taking place in addition to this when one plan to increase redo log buffer size. the parameter used for redo log buffer is LOG_BUFFER



select name, value from v$sysstat where NAME in
('redo buffer allocation retries',
'redo log space wait time')
/
the values should be near to zero and one has to check when peak load on database system and it is static parameter i.e. required instance startup.

redo buffer allocation retries is the number of times user process waited for redo log buffer space.
redo log space wait time is the total time waited by all the process for redo log buffer space.

the value should be near to zero for best performance otherwise one has to check checkpointing and archiving before one can go ahead with increasing log_buffer size.


Find the running queries SQL statements including events

select
substr(a.spid,1,12) pid,
substr(b.sid,1,6) sid,
substr(b.serial#,1,6) serial#,
substr(b.machine,1,6) machine,
substr(b.username,1,8) username,
b.STATUS,b.state,b.EVENT,
substr(b.osuser,1,12) os_user,
substr(b.program,1,30) program,
b.sql_id
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and lower(event) not like '%sql*net%'
order by spid

we get all the sql stetements that we are interest of and can be list what exactly being executed in db with following query statement.

select sql_text from v$sql where sql_id='sql_id from above query'

once we get sql statements the are waiting for events to be completed, we can employ explain plan or sqltrace utitilies to tune the query or can go further to find what exactly cuasing wait events to appear in instance/db.

-- more info will come soon--

Performance tuning in oracle 10g database

Performance tuning in oracle 10g database:

Current sessions and their activity in oracle 10g:

The following query statement can be used to disaply all the current sessions in current instance/db:

-- GV$INSTANCE view for RAC database
--- v$instance for single instance database

select sid, serial#, username, status, osuser,process, machine, program,type,module, event,
decode(command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
command||': Other') command
from v$session
where username is not null

How to find Oracle Buffer Cache Hit Ratio:

Here are the few sql statements that can be used whether oracle database is functioning well or not, however buffer cache hit ratio is an indicator and can not be used as a prime factor when tuning database, one has to look more into oracle wait events and V$DB_CACHE_ADVICE view data before tuning buffer chache.
The following statement can be used to find buffer cache hit ratio in oracle 10g.

select
100*(1 - (v3.value / (v1.value + v2.value))) "Buffer Cache Hit Ratio [%]"
from
v$sysstat v1, v$sysstat v2, v$sysstat v3
where
v1.name = 'db block gets from cache' and
v2.name = 'consistent gets from cache' and
v3.name = 'physical reads cache'

There will not be much use of increasing buffer cache in case one doing full table scans or operations that are not using the buffer cache. Running oracle database under automatic memory management mode is best option.

The following sql stements list the sql statements that are waiting for some action to be completed and/or waiting for some resource to be available or currently running in instance. the text that is given below is simple and I feel it can help us to find status of running statements with wait events details

Oracle Library Cache Hit Ratio:

Here is the simple query
to find oracle library cache hit ratio, the more hit ratio you see the more efficiently the library cache is being used and should be more than 96% or even 99%

select sum(pinhits)/sum(pins)
,100* (sum(pinhits)/sum(pins) ) "Library Cache Hit Ratio" from v$librarycache

The best way I feel to decide whether to increase library cache allocated memory or not is based on reloads column of v$librarycache view. the values should be near to 0.

shared_pool_size is what we need to increase in case required to do so for library cache.

HOW TO FIND FREE MEMORY IN SGA:

The amount of free memory can be find using v$sgastat view and here is the simple query to find SGA free memory

select * from v$sgastat where name like '%free memory%'

Dictionary Cache hit ratio:

SELECT sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) "Dictionary Cache hit ratio"
FROM V$ROWCACHE
WHERE gets > 0


we do not have to worry in case it returns 0 records as it shows no load on dictionary cache. we have to find at parameter level in case hit ration is not acceptable and once can find by grouping parameter column of v$rowache to zero on exact problems and able to see in
granularity level.

Redo Log Buffer configuration:

The following statement tell us whether redo log buffer is well adequate or not, however one has to check how check point and archiving taking place in addition to this when one plan to increase redo log buffer size. the parameter used for redo log buffer is LOG_BUFFER



select name, value from v$sysstat where NAME in
('redo buffer allocation retries',
'redo log space wait time')
/
the values should be near to zero and one has to check when peak load on database system and it is static parameter i.e. required instance startup.

redo buffer allocation retries is the number of times user process waited for redo log buffer space.
redo log space wait time is the total time waited by all the process for redo log buffer space.

the value should be near to zero for best performance otherwise one has to check checkpointing and archiving before one can go ahead with increasing log_buffer size.


Find the running queries SQL statements including events

select
substr(a.spid,1,12) pid,
substr(b.sid,1,6) sid,
substr(b.serial#,1,6) serial#,
substr(b.machine,1,6) machine,
substr(b.username,1,8) username,
b.STATUS,b.state,b.EVENT,
substr(b.osuser,1,12) os_user,
substr(b.program,1,30) program,
b.sql_id
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
and lower(event) not like '%sql*net%'
order by spid

we get all the sql stetements that we are interest of and can be list what exactly being executed in db with following query statement.

select sql_text from v$sql where sql_id='sql_id from above query'

once we get sql statements the are waiting for events to be completed, we can employ explain plan or sqltrace utitilies to tune the query or can go further to find what exactly cuasing wait events to appear in instance/db.

-- more info will come soon--