Tip #12: SQL Script ur Anzeige der `hit ratio` der laufenden Prozesse

Tip #12: SQL Script ur Anzeige der 'hit ratio' der laufenden Prozesse. (Type: SQL)
Läuft Ihr Server langsam ??? – Wer lässt noch Queries laufen und WARUM behindern sie das
System? Der DBA hat - wie immer - keine Zeit, sich um Ihr Problem sofort zu kümmern, aber er gibt
Ihnen eins von seinen Tools und da können Sie dann nachsehen ;-)….
Dieser Tip enthält ein paar einfache Scripts, die zeigen, welche Oracle Prozesse gerade in einer
Instanz laufen und wie dioe „buffer hit ratio“ für diese Prozesse aussieht (niedrige „hit ratios“ sind ein
Indikator für schlecht getuntes SQL, das die ganze Instanz behindern kann....
Das erste Script zeigt die “active processes” und ihre aktuelle “hit ratio”.
/*************************************************************************/
/* listproc.sql - Lists currently running processes and their hit ratios */
/*
*/
/*
*/
/* You need select access to V$SESSION, V$PROCESS, and V$SESS_IO
*/
/* to run this script.
*/
/*
*/
/* The columns returned by this script are:
*/
/
Oracle ID (schemaname) = The oracle 'schema' or 'user' that is
*/
/*
running the SQL statement.
*/
/*
System ID (username)
= The system id that the process is
*/
/*
running under. Will be the unix userid */
/*
if Oracle running on unix.
*/
/*
Program
= The name of the program that is running the SQL.*/
/*
Physical Reads = The number of physical block reads.
*/
/*
Hit Ratio
= The ratio of buffer to physical block reads.
*/
/*
be an indication of the efficiency of the query*/
/*
running. Anything under 90% is bad. Very low */
/*
hit ratios (< 10-20%) in a process can slow
*/
/*
down the whole system.
*/
/*************************************************************************/
column schemaname format a10 heading 'Oracle ID'
column username format a10 heading 'System ID'
column program format a32 heading 'Program'
column hit_ratio format 9.90 heading 'Hit Ratio'
column physical_reads format 9999999 heading 'Reads'
column sid format 99999
SELECT
s.schemaname
, p.username
, s.program
,io.physical_reads
,(io.block_gets+io.consistent_gets)/
(io.block_gets+io.consistent_gets+io.physical_reads) hit_ratio
FROM V$Session s
,V$Process p
,V$Sess_io io
WHERE s.paddr = p.addr
AND s.sid
= io.sid
-- Only look at active processes
AND s.status = 'ACTIVE'
-- Need this predicate to prevent division by 0
AND (io.block_gets+io.consistent_gets+io.physical_reads) > 0
/
Ein Beispiel:
SQL> @hitratio
Oracle ID
---------SYS
SYS
SYS
BDES490
BDES490
System ID
---------oracle7
oracle7
oracle7
oracle7
oracle7
Program
Reads Hit Ratio
-------------------------------- -------- --------1.00
10894
.83
18
.95
C:\ORAWIN\BIN\PLUS31.EXE
1.00
sqlplus@larabee (TNS interface)
3478
.83
Das nächste Script zeigt alle Processe und ihren Status, SID & Serial#. Die SID und
die Serial# werden im Befehl ALTER SYSTEM KILL SESSION benötigt, das einen
schlechten Oracle Prozess beenden kann.
/*************************************************************************/
/* listproc.sql - Lists currently processes, status, sid & serial#
*/
/*
*/
/*
*/
/* You need select access to V$SESSION, V$PROCESS to run this script
*/
/*
*/
/*************************************************************************/
column schemaname format a10 heading 'Oracle ID'
column username format a10 heading 'System ID'
column program format a30 heading 'Program'
column user_name format a15 heading 'User Name'
column sid format 99999
SELECT s.schemaname
,p.username
,s.program
,s.sid
,s.serial#
,s.status
FROM V$Session s
,V$Process p
where s.paddr = p.addr
/
Ein Beispiel:
SQL> @listproc
Oracle ID
---------KATK490
SYS
SYS
SYS
SYS
SYS
ORAPIPE
BDES490
JOJJ490
BDES490
ARJJ490
BHAR490
BDES490
MAJJ490
BHAR490
ARJJ490
MAJJ490
SHARED
BDES490
System ID Program
SID
SERIAL# STATUS
---------- ------------------------------ ------ --------- -------C:\WINDOWS\SYSTEM32\OLE2.DLL
21
447 KILLED
oracle7
1
1 ACTIVE
oracle7
2
1 ACTIVE
oracle7
3
1 ACTIVE
oracle7
4
1 ACTIVE
oracle7
5
1 ACTIVE
orapipe
? @gamera (TNS interface)
9
8021 INACTIVE
oracle7
C:\ORAWIN\BIN\CKRON10L.DLL
12
105 INACTIVE
oracle7
C:\ORAWIN\BIN\R25DES.EXE
7
32691 INACTIVE
oracle7
C:\ORAWIN\BIN\PLUS31.EXE
16
275 ACTIVE
oracle7
C:\ORAWIN\BIN\CKRON10L.DLL
6
2029 INACTIVE
oracle7
C:\ORAWIN\BIN\PLUS31.EXE
10
2545 INACTIVE
oracle7
sqlplus@larabee (TNS interface)
17
619 ACTIVE
oracle7
C:\ORAWIN\BIN\CKRON10L.DLL
13
35 INACTIVE
oracle7
C:\ORAWIN\BIN\R25DES.EXE
14
39 INACTIVE
oracle7
C:\ORAWIN\BIN\R25DES.EXE
8
9173 INACTIVE
oracle7
C:\ORAWIN\BIN\R25DES.EXE
11
2273 INACTIVE
oracle7
C:\ORAWIN\BIN\PLUS31.EXE
15
67 INACTIVE
oracle7
C:\ORAWIN\BIN\PLUS31.EXE
18
739 INACTIVE