Memory Tuning

Database buffer catchive tune

Sql>select (1-(p.value/(d.value+c.value))) * 100 from v$ sysstat p,v$ sysstat d, v$sysstat c

where

p.name = ‘physical reads’ and

d.name = ‘db block gets’ and

c.name = ‘consistent gets’;

output : it should be above 95 %. Below means problem

or go to check the heading of awr report check the instance ebisency % target 100%

check Buffer hit % = above 95% , below means problem

sql>alter system set db_cache_advice = on;

sql>select * from v$db_cache_advice; (size , physical factor , estimated physical factor, estimated physical reads )

Without increasing default size

Configure the multiple keep components

If full table scan means configure to be re cycle

Sql>alter table emp storage (buffer_pool keep);

Sql>select table_name, buffer_pool from dba_tables;

Check performance

Sql>select name, (1-(physical_reads / (db_block_gets + consistent_gets))) * 100

From v$buffer_pool_statistics;

Output: check for above 95 %(keep , default, recycle)

Sql>select * from v$db_cache_advice

Where name = ‘keep’;

Redolog Buffer tuning;

Sql>select (1-(r.value/e.value)) * 100 from v$sysstat r, v$sysstat e where

r.name = ‘redo buffer allocation retries and

e.name = ‘redo entries’;

Output : It should be above 99%

AWR report  heading Instance Efficiency:

Redo nowait;

Output : It should be above 99%

Instance Activity string

Totalsize/redo buffer allocate retn’s’

Total size/redo entries;

Shared Pool Tuning

Sql>select namespace,gethitratio * 100,pinhidratio * 100 from

V$librarycache;

Output : It should be above 95%

or

Sql>select (sum(reloads)/sum(pins) * 100 from v$ librarycache

Output : It should be less than 1%

Or

Sql>select (1-(sum(getmisses) / sum(gets))) * 100 from v$ rowcache;

Or

Goto AWR report check heading instance efficiency hits % 100 and check library hit % is above 95 %

Sql>select * from v$shared_pool_advice (size factor ,  estimated library cache memory objects)

ASMM

Sga_max_size = <>

Sga_target = <>

Sga_target > 0 means enable the ASMM

Sql>select * from v$sga_dynamic_components;

Sql>select * from v$sga_target_advice; (size, factor ,estimated physical reads)

Automatic PGA memory management

Pga_aggregate_target = <>

Workarea_size_policy=auto/manual

Sql>select * from v$pgastat where name like ‘ %cache%’;

Output : should be 100 %

Sql>select * from v$pga_target_advice; (size,factor,%,overallocation)                                                                                                                                                                            

or

Goto AWR report  check heading PGA activity stage and check catch hit % in should be 100%

Check point tuning:

Fast_start_mttr_target=0/1 to 3600 seconds (oracle recommendation 180 seconds)

>0 means enable automatic check point

Find Check point complete /incomplete

Sql> select * from v$ sysstat where name like ‘%checkpoint%’;

Output : background check point started (one value will be displayed)

background check point completed(one value will be displayed)

difference must be maximum 1

or

goto awr report check the heading of instance activity stats and check

background checkpoint started (one value will be displayed)

background checkpoint completed(one value will be displayed)

difference must be maximum 1

sql>select total_waits frm v$ system_event

 where event in (‘logfile switch(checkpoint incomplete)’, ‘ logfile switch (archiving needed)’);

Output : must be in zero , > 0 means problem so

Sql> alter system set log_archive_max_processes = 5;

Or

Increase the redolog groups

Or

Increase the redolog filesize

Disk IO tuning

Sql>select d.name,f.phyrds,f.phyrds/f.readtim AVG_READ_TIME,f.phywrts,f.phywrts/f.writetim AVG_WRITE_TIME
from v$filestat f , v$datafile d
Where f.file#= d.file# and f.readtim>0 and f.writetim>0 and f.phywrts/f.writetim>=20 and f.phyrds/f.readtim>=5 ;

Output : DF average physical reddtime < 5 milliseconds & writetime <20 milliseconds

If the above value is false means there is problem , so df moved to another mount point using rename concept

For ex.,

Sql>alter tablespace data offline;

Sql>!cp /opt/oracle/data1.dbf                   /opt/oracle/data01.dbf

Sql>alter tablespace data rename datafile’ /opt/oracle/data1.dbf’     to      ‘/opt/oracle/data01.dbf’;

Sql>alter tablespace data online;

Library Cache

Sql>select * from v$locked_object; (SIDUSN (1=lockers, 0=waiters), objectid , session id , username)

Kill session

Sql>select sid,serial# from v$session where sid=9;

o/p: 9,1234

sql>alter system kill session  ‘9,1234’;

or

sql>rollback;

or

sql>commit

sql>select * from dba_waiters

sql>select * from dba_blockers