Oracle performans increase queries ...
SQL
1:
Eger “BUFFER HIT RATIO” degeri 70’den büyük ise “init.ora”
dosyasından
“db_block_buffers”
parametresi artırılması gerekmektedir.
SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads",
d.VALUE "phy_writes",
ROUND (100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE +
b.VALUE) ) "BUFFER HIT RATIO"
FROM v$sysstat a, v$sysstat
b, v$sysstat c, v$sysstat d
WHERE a.statistic# = 37
AND b.statistic# = 38
AND c.statistic# = 39
AND d.statistic# = 40;
logical_reads phys_reads
phy_writes BUFFER HIT RATIO
------------- ----------
---------- ----------------
1764891 6.0798E+10 1017191
-3444775
SQL
2:
Eger “DATA DICT CACHE HIT RATIO” degeri 90’dan büyük ise “init.ora”
dosyasından
“shared_pool_size” parametresi artırılması gerekmektedir.
SELECT SUM (gets) "Data Dict. Gets",
SUM (getmisses) "Data Dict. cache misses",
TRUNC ((1 - (SUM (getmisses) / SUM (gets))) * 100
) "DATA DICT CACHE HIT RATIO"
FROM v$rowcache;
Data Dict.
Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
---------------
----------------------- -------------------------
10627015
1101048 89
SQL
3:
Eger “LIBRARY CACHE MISS RATIO” degeri %1’dan büyük ise “init.ora”
dosyasından
“shared_pool_size” parametresi artırılması gerekmektedir.
SELECT SUM (pins) "executions",
SUM (reloads) "Cache misses while executing",
(((SUM (reloads) / SUM (pins)))) "LIBRARY
CACHE MISS RATIO"
FROM v$librarycache;
SQL
4:
“HIT
RATIO” ve “PIN HIT RATIO” 70 degerinden büyük olmalıdır.
SELECT namespace, TRUNC (gethitratio * 100) "Hit ratio",
TRUNC (pinhitratio * 100) "pin hit ratio", reloads "reloads"
FROM v$librarycache;
NAMESPACE Hit ratio pin hit
ratio reloads
--------------- ----------
------------- ----------
SQL AREA 21 97 16917
TABLE/PROCEDURE 90 94 28734
BODY 95 99 478
TRIGGER 98 97 497
INDEX 41 77 245
CLUSTER 99 99 47
OBJECT 100 100 0
PIPE 98 99 0
JAVA SOURCE 100 100 0
JAVA RESOURCE 100 100 0
JAVA DATA 66 99 0
SQL
5:
“REDO
LOG” için gerekli olan bosluk
degeri
SELECT SUBSTR (NAME, 1, 30), VALUE
FROM v$sysstat
WHERE
NAME = 'redo log space requests';
SUBSTR(NAME,1,30) VALUE
------------------------------
----------
redo log space requests 377
SQL
6:
Bos bellek degeri
SELECT
NAME, BYTES
FROM v$sgastat
WHERE
NAME = 'free memory';
NAME BYTES
--------------------------
----------
free memory 44821748
free memory 3120304
free memory 390144
SQL
7:
VT
açıldıgından
ve kullanıcı baglandıgından beri çalıstırılan SQL degerleri toplamı
SELECT SUM (executions) "Total SQL run since startup",
SUM (users_executing) "SQL executing now"
FROM v$sqlarea;
Tot SQL run since startup SQL
executing now
-------------------------
-----------------
1404816 27
SQL
8:
Kilit
ve kilitlenme durumlarının listesi
SELECT SUBSTR (username, 1, 12) "User", SUBSTR (lock_type, 1, 18)
"Lock
Type",
SUBSTR (mode_held, 1, 18) "Mode
Held"
FROM SYS.dba_lock a, v$session b
WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread')
AND a.session_id
= b.SID;
User Lock Type Mode Held
------------
------------------ ------------------
XR Null
Control File Row-S (SS)
RS Row-S (SS)
MERNISDW PS Share
REPOWNER PL/SQL User Lock
Exclusive
REPOWNER PL/SQL User Lock
Share
MERNISDW PS Share
Temp Segment Row-X (SX)
MERNISDW PS Share
MERNISDW PS Share
REPOWNER PL/SQL User Lock
Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW TO Row-X (SX)
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW PS Share
MERNISDW TO Row-X (SX)
MERNISDW Transaction
Exclusive
SQL
9:
Eger “miss_ratio” veya “immediate_miss_ratio”
1 den büyük ise kilitlenme durumu
vardır
bu durumda “init.ora” dosyasından “LOG_SMALL_ENTRY_MAX_SIZE”
parametresi
azaltılması gerekmektedir.
SELECT SUBSTR (l.NAME, 1, 30) NAME,
(misses / (gets + .001)) * 100 "miss_ratio",
(immediate_misses / (immediate_gets + .001)
)
* 100 "immediate_miss_ratio"
FROM v$latch l, v$latchname
LN
WHERE l.latch# = LN.latch#
AND ( (misses / (gets + .001)) * 100 > .2
OR (immediate_misses / (immediate_gets + .001)) * 100 > .2
)
ORDER BY
l.NAME;
NAME miss_ratio
immediate_miss_ratio
------------------------------
---------- --------------------
active service list
,.4494E+39 0
client/application info
,.5134E+40 0
dummy allocation 4.,420E+38 0
FOB s.o list latch ,.2623E+40
0
KTF sga latch ,.2959E+40
,.0075E+42
library cache ,.0527E+40
2.,747E+39
loader state object freelist
,.6459E+40 0
Memory Management Latch
,.2028E+40 0
messages ,.3129E+40 0
parameter table allocation
man ,.4131E+40 0
process queue reference ,.0064E+42
8.,529E+39
query server freelists
2.,277E+39 0
resmgr group change latch
2.,430E+39 0
resmgr:free threads list
5.,868E+39 0
session state list latch
6.,364E+38 0
slave class create 1.1,61E+40
0
SQL memory manager latch
,.5102E+40 0
user lock ,.7538E+40 0
SQL
10:
Alınan
sonuçtaki herhangi bir deger
1 den büyük ise veri için daha fazla “Rollback
Segment”
ihtiyaç duyulur.
SELECT
CLASS, COUNT
FROM v$waitstat
WHERE
CLASS IN
('free list',
'system undo
header',
'system undo
block',
'undo header',
'undo block'
)
GROUP BY
CLASS, COUNT;
CLASS COUNT
------------------ ----------
undo header 27
system undo block 0
system undo header 0
free list 0
undo block 12
SQL
11:
Alınan
sonuçlardan herhangi biri 0.01 den büyük ise “Roolback Segment” artırmak
gerekir.
SELECT
NAME, waits, gets, waits / gets "Ratio"
FROM v$rollstat a, v$rollname
b
WHERE a.usn = b.usn;
NAME WAITS GETS Ratio
-------------------------
---------- ---------- ----------
SYSTEM 0 1847 0
_SYSSMU1$ 1 51812 ,.0000E+43
_SYSSMU2$ 0 67028 0
_SYSSMU3$ 0 50381 0
_SYSSMU4$ 2 57912 ,.0000E+44
_SYSSMU5$ 2 41491 ,.0000E+44
_SYSSMU6$ 5 64980 ,.0001E+44
_SYSSMU7$ 2 56792 ,.0000E+43
_SYSSMU8$ 3 53986 ,.0001E+44
_SYSSMU9$ 2 60986 ,.0000E+44
_SYSSMU10$ 1 57212 ,.0000E+44
SQL
12:
Toplam
bekleme zamanlarını görmek
SELECT SUBSTR (event, 1, 30) event, total_waits, total_timeouts,
average_wait
FROM v$session_event
WHERE average_wait > 0;
EVENT
TOTAL_WAITS TOTAL_TIMEOUTS AVERAGE_WAIT
--------------------------
----------- -------------- ------------
log file
sync 2 0 ,04
db file
sequential read 56 0 ,44
db file
scattered read 1 0 1,53
SQL*Net
more data to client 26 0 ,01
SQL*Net
message from client 150 0 91,55
SQL*Net
break/reset to client 4 0 ,21
cursor:
pin S wait on X 31 30 1,47
PX Deq:
Msg Fragment 1 0 ,12
PX Deq:
Execution Msg 8082 8080 199,91
events in
waitclass Other 3 2 ,02
events in
waitclass Other 1 0 ,12
events in
waitclass Other 2 0 ,03
events in
waitclass Other 2 0 ,03
SQL*Net
message from client 6 0 7,93
cursor:
pin S wait on X 31 30 1,46
PX Deq:
Msg Fragment 1 0 ,08
PX Deq:
Execution Msg 8082 8080 199,91
db file
sequential read 1 0 ,11
cursor:
pin S wait on X 32 30 1,42
PX Deq:
Execution Msg 8086 8083 199,88
SQL*Net
message from client 168 0 13898,4
SQL
13:
Kuyruklar
için ortalama bekleme, sıfırın yakınında olmalıdır.
SELECT paddr, TYPE "Queue type", queued "# queued", WAIT, totalq,
DECODE (totalq, 0, 0, WAIT / totalq) "AVG WAIT"
FROM v$queue;
PADD Queue type # queued WAIT
TOTALQ AVG WAIT
---- ---------- ----------
---------- ---------- ----------
00 COMMON 0 0 0 0
2124 DISPATCHER 0 0 0 0
Yorumlar
Yorum Gönder