Oracle Performance Tuning

[Marley]

Performance Views

Configuration Advice

• V$DB_CACHE_ADVICE

Statistics

• V$SYSSTAT
• V$WAITSTAT
• V$SYSTEM_EVENT
• DBA_EXTENTS

Buffer Cache and subset pools

• V$BUFFER_POOL
• V$BUFFER_POOL_STATISTICS
• V$SGA
• V$SGASTAT
• V$PGASTAT

[Marley]

Tuning the Database Buffer Cache

Database read buffer cache hit ratio

-- Database read buffer cache hit ratio =

-- 1 -(physical reads / (db block gets + consistent gets))



-- LIO / (PIO + LIO)% hints of block in the buffer



--

-- physical read - block request from disk (I/O)

-- consisten gets - consistent read blick request

-- db block gets - block requests

--





SELECT 'Database Buffer Cache Hit Ratio ' "Ratio", 

ROUND (

		(1 - 

			(

			(SELECT SUM(value) From V$SYSSTAT WHERE name = 'physical reads')

			/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')

			+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets'))

			)

		)* 100

	   )

       

      ||'%' "Percentage" 

      FROM DUAL;

---------------

PGA cache hit ratio

-- PGA cache hit ratio = (bytes processed / (bytes processed + extra bytes read/written))*100



SELECT (

       (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'bytes processed')

       / ((SELECT VALUE FROM V$PGASTAT WHERE NAME = 'bytes processed')+

       (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'extra bytes read/written'))

       )

*100 "PGA Cache Ht Ratio" FROM DUAL;

-----------------------------

select * from v$waitstat;

-----------------

-- buffer busy a free buffer waits

SELECT event "Event", total_waits "Waits", time_waited "Total"

FROM v$system_event WHERE event IN('buffer busy waits', 'free buffer waits');

-----------------

SELECT event "Event", total_waits "Waits", time_waited "Total"

FROM v$system_event

ORDER BY event;

[Marley]

Analyzing Sorting

select 'Sorts in Memory' "Ration",

ROUND(

(SELECT SUM(value) FROM v$SYSSTAT WHERE name = 'sorts (memory)') /

(SELECT SUM(value) FROM V$SYSSTAT

WHERE name IN ('sorts (memory)', 'sorts (disk)'))* 100,2)

|| '%' "Percentage"

FROM DUAL;

-- find SQL statements with high sorting amounts



Select * from (

Select sorts, executions, sql_text 

from v$sqlarea

order by sorts desc)

where rownum < 10 ;

--heaviest impact

select * from (

select sorts, executions, sql_text

from v$sqlarea order by executions*sorts desc)

where rownum <10;

[Marley]

select fs.phyrds "Reads" , fs.phywrts "Writes" , fs.avgiotim "Average I/O Time", df.name "Datafile"

FROM v$datafile df, v$filestat fs 

WHERE df.file# = fs.file#;

select fs.phyrds "Reads" , fs.phywrts "Writes" , fs.avgiotim "Average I/O Time", df.name "Datafile"

FROM v$tempfile df, v$tempstat fs 

WHERE df.file# = fs.file#;

SELECT ts.name AS ts, fs.phyrds "Reads" , fs.phywrts "Writes",

fs.phyblkrd as br, fs.phyblkwrt as bw,

fs.readtim "RTime", fs.writetim "WTime"

FROM v$tablespace ts, v$datafile df, v$filestat fs

WHERE ts.ts# = df.ts# AND df.file# = fs.FILE#

UNION

SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes",

ts.phyblkrd AS br, ts.phyblkwrt AS bw,

ts.readtim "RTime", ts.writetim "WTime"

FROM v$tablespace ts, v$tempfile tf, v$tempstat ts

WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;

[Marley]

Locking

select * from dba_locks d join v$lock v on(v.sid=d.session_id)

join v$session s on(s.sid=v.sid);