OracleDB‎ > ‎Tuning‎ > ‎


Get optimal redolog (redo log) file size after the database has been running for a while.


An easier way to get recommendations for the redo log file size is to view the Redo Log Groups page of the OEM Database Control.

Increase processes (restart required)

SQL> alter system set processes=250 scope=spfile;

Identify queries taking up the most cpu resources

SELECT sql_text, executions,  
ROUND(elapsed_time/1000000, 2) elapsed_seconds,
ROUND(cpu_time/1000000, 2) cpu_secs from
(select * from v$sql order by elapsed_time desc)
WHERE rownum <6;

To determine if they need to increase share_pool_size:

There are several ratios that you can check after a representative sample time that may indicate that you need to enlarge the shared pool, which is set by the shared_pool_size parameter in your init.ora file and defaults to 3500000 (3.5 Meg). One indicator is the Library Cache Get Hit Ratio, which shows how many cursors are being shared (SQL statements (gets) which were already found and parsed (gethits) in the shared pool, with no parsing or re-parsing needed), and is determined by:

select gethits,gets,gethitratio from v$librarycache where namespace = 'SQL AREA';

If the gethitratio is less than 90%, you should consider increasing the shared pool size. Another indicator is the reloads per pin ratio, which shows how many parsed statements (pins) have been aged out (reloaded) of the shared pool for lack of space (ideally 0), and is determined by:

select reloads,pins,reloads/pins from v$librarycache where namespace = 'SQL AREA';

If the reloads/pins ratio is more than 1%, you should consider increasing the shared pool size. A third indicator, which is not as important as the first two, is the dictionary object getmisses per get ratio, which shows how many cached dictionary object definitions in the dictionary cache are encountering too many misses (aged out?), and is determined by:

select sum(getmisses),sum(gets),sum(getmisses)/sum(gets) from v$rowcache;

If the getmisses/gets ratio is more than 15%, you should consider increasing the shared pool size.

to increase shared pool size

alter system set shared_pool_size=180m;

if sga_max is not enough you have to increase it

SQL> show sga

Total System Global Area 289406976 bytes
Fixed Size 788808 bytes
Variable Size 99612344 bytes
Database Buffers 188743680 bytes
Redo Buffers 262144 bytes
SQL> ALTER SYSTEM SET sga_max_size=250m SCOPE=spfile;

System altered.

SQL> startup force

Look at your dictionary cache

SQL> SELECT (sum(gets - getmisses - fixed)) / SUM(gets)
 2 "data dictionary hit ratio" from v$rowcache;
data dictionary hit ratio

Shoot for .95 to .99 eventhough oracle says .85 is adequet enough

To increase the library cache ration, you simply increase the shared pool size for the instance.

To lookup sql query a system process is running. First lookup the pid with ps or in top then run the following script in sqlplus

Select a.sid,a.serial#,,c.sql_text,a.module,
to_number(b.spid) PROCESSID,
substr(a.username, 1, 12) username
From v$session a,
v$process b,v$sql c
Where a.paddr = b.addr
and a.sql_address = c.address
and b.spid = '&spid'