Oracle DBA Forum

Oracle DBA Forum (https://odba.ru/index.php)
-   temp (https://odba.ru/forumdisplay.php?f=6)
-   -   Oracle Performance Tuning (https://odba.ru/showthread.php?t=273)

Marley 10.03.2010 10:53

Oracle Performance Tuning
 
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 12.03.2010 09:19

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;


https://img.oracledba.net/etc/tuningbeb.jpg


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

-- 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');


https://img.oracledba.net/etc/tuningbuff.jpg

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

SELECT event "Event", total_waits "Waits", time_waited "Total"
FROM v$system_event
ORDER BY event;

Marley 12.03.2010 09:24

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 12.03.2010 09:54

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 12.03.2010 10:17

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);



Текущее время: 22:32. Часовой пояс GMT +3.

Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd. Перевод: zCarot