v$top — Oracle Top-Sessions als Echtzeit View

Warum sollen die wichtigsten UNIX Tools nur im ASCII Format auf der Shell verfügbar sein?

top als Oracle View

Please find the English translation of this article below!

Update: Die Programmbeispiele sind nun so formatiert, dass sie beim Kopieren nicht verändert werden. Danke für den Hinweis.
Update2: Durch ein Editorproblem verschwand aus dem Script runtop.sh unbemerktein Leerzeichen. Sollte nun funktionieren.

top-oracle-processes

Und wenn das top erst da ist, warum nicht gleich das SQL Tuning vereinfachen?

Identifizieren von Sessions und SQL mit hohem CPU Anteil

Und so funktioniert’s:

(1) Erstellen Sie ein Script runtop.sh, das den top Output bereinigt und in einem für Oracle lesbaren Format unter /tmp ablegt



#!/bin/sh

# runtop.sh : generate oracle readable top output

#

# written by oraservices.de; info@oraservices.de

# download at http://www.orasupport.de

# license : GPL

#

while true

do

top -b -n2 -d0.01 | awk '/^top/{i++}i==2' | \

egrep -v "^top|^Tasks|^Cpu|^Mem|^Swap|PID" | \

sed "s/^ *//;s/  */|/g" > /tmp/v_top.tmp

mv /tmp/v_top.tmp /tmp/v_top.dat

sleep 1

done

Start des Scripts mit

nohup ./runtop.sh &

Der Output sieht so aus

$head -7 /tmp/v_top.dat

4096|oracle|15|0|2176|1040|792|R|48|0.1|0:00.02|top|

1|root|15|0|2040|632|548|S|0|0.0|0:00.79|init|

2|root|RT|0|0|0|0|S|0|0.0|0:00.04|migration/0|

3|root|34|19|0|0|0|S|0|0.0|0:00.00|ksoftirqd/0|

4|root|RT|0|0|0|0|S|0|0.0|0:00.00|watchdog/0|

5|root|RT|0|0|0|0|S|0|0.0|0:00.04|migration/1|

6|root|34|19|0|0|0|S|0|0.0|0:00.00|ksoftirqd/1|

(2) Erstellen Sie eine Externe Tabelle, mit der dieser Output in Oracle sichbar wird

create or replace directory data_dir as '/tmp';

CREATE TABLE v_top

(PID varchar2(10),

OSUSER varchar2(10),

PR varchar2(5),

NI varchar2(10),

VIRT varchar2(10),

RES varchar2(10),

SHR varchar2(10),

S varchar2(10),

CPU number(5,2),

MEM varchar2(10),

TIME varchar2(10),

COMMAND varchar2(50))

ORGANIZATION EXTERNAL

(

TYPE oracle_loader

DEFAULT DIRECTORY data_dir

ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|")

LOCATION ('v_top.dat')

)

REJECT LIMIT UNLIMITED NOPARALLEL;

select * from v_top;

Bemerkungen

while true	Ja, eine Endlosschleife. Abbrechen mit kill oder weiter im Hintergrund laufen lassen.
`top -b -n2 -d0.01 \|awk '/^top/{i++}i==2'`	Erst die zweite Ausgabe des top ist brauchbar. Das Delay zwischen den Ausgaben ist minimal.
mv /tmp/v_top.tmp /tmp/v_top.dat	Durch das mv entsteht nur eine minimale Zeitlücke, in der der Inhalt von v_top.dat ungültig ist.
sleep 1	Aktualisierung jede Sekunde. Können Sie natürlich auch seltener machen.
PID varchar2(10)	Obwohl das top für PID und %MEM Number Werte liefert, macht es das Einlesen in eine Externe Tabelle deutlich einfacher, wenn Korrekturen vermieden werden. Varchar2 ist unkompliziert.
select count (*) from person p1, person p2	Ein fehlendes Join Kriterium ist eine einfache Art, ein lang laufendes und lastintensives SQL Statement für Testzwecke zu erstellen.
v_top	Zur besseren Unterscheidbarkeit von den „echten“ V$ views des Oracle Data Dictionary habe ich mich für v_ entschieden. Zur weiteren Unterscheidbarkeit erstellen Sie v_top ggf. in einem eigenen Schema.
nohup ./runtop.sh &	Mit nohup können Sie die UNIX Shell, von der Sie das runtop.sh gestartet haben auch wieder verlassen; ansonsten erscheint die Meldung „there are stopped jobs“. Abbrechen des Scripts mit $ ps -ef \| grep runtop oracle 11044 1 0 10:28 ? 00:00:00 /bin/sh ./runtop.sh oracle 13256 11851 0 10:37 pts/0 00:00:00 grep runtop $ kill 11044 $

English Version of this Article: v$top — Oracle Session Top as RealTime View

Why should the most important UNIX Tools only be available in ASCII Format and only in the shell?

top as Oracle View

top-oracle-processes

And now that top is in the database, why not simplify SQL tuning?

Identify Sessions with high CPU Activity

And this is how it works:

(1) Create a Script called runtop.sh, that cleans out the top output and writes it in a format readable by Oracle; the output file is written in the /tmp directory and is refreshed every second.

#!/bin/sh

# runtop.sh : generate oracle readable top output

#

# written by oraservices.de; info@oraservices.de

# download at http://www.orasupport.de

# license : GPL

#

while true

do

top -b -n2 -d0.01 | awk '/^top/{i++}i==2' | \

egrep -v "^top|^Tasks|^Cpu|^Mem|^Swap|PID" | \

sed "s/^ *//;s/  */|/g" > /tmp/v_top.tmp

mv /tmp/v_top.tmp /tmp/v_top.dat

sleep 1

done

Start the script by issuing

nohup ./runtop.sh &

The output looks like this

$head -7 /tmp/v_top.dat

4096|oracle|15|0|2176|1040|792|R|48|0.1|0:00.02|top|

1|root|15|0|2040|632|548|S|0|0.0|0:00.79|init|

2|root|RT|0|0|0|0|S|0|0.0|0:00.04|migration/0|

3|root|34|19|0|0|0|S|0|0.0|0:00.00|ksoftirqd/0|

4|root|RT|0|0|0|0|S|0|0.0|0:00.00|watchdog/0|

5|root|RT|0|0|0|0|S|0|0.0|0:00.04|migration/1|

6|root|34|19|0|0|0|S|0|0.0|0:00.00|ksoftirqd/1|

(2) Create an Oracle External Table based on this data

create or replace directory data_dir as '/tmp';

CREATE TABLE v_top

(PID varchar2(10),

OSUSER varchar2(10),

PR varchar2(5),

NI varchar2(10),

VIRT varchar2(10),

RES varchar2(10),

SHR varchar2(10),

S varchar2(10),

CPU number(5,2),

MEM varchar2(10),

TIME varchar2(10),

COMMAND varchar2(50))

ORGANIZATION EXTERNAL

(

TYPE oracle_loader

DEFAULT DIRECTORY data_dir

ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|")

LOCATION ('v_top.dat')

)

REJECT LIMIT UNLIMITED NOPARALLEL;

select * from v_top;

Remarks

while true	Yes, this is an infinite loop. Interrupt the script with kill or leave it running in the background.
`top -b -n2 -d0.01 \|awk '/^top/{i++}i==2'`	Ignore the first top output. The second output is usable. The delay between first and second output is minimal.
mv /tmp/v_top.tmp /tmp/v_top.dat	Using mv there is only a short time interval in which the contents of v_top.dat is invalid.
sleep 1	Refresh every second. Of course you can change the refresh interval for less current data.
PID varchar2(10)	Although top yields number values for PID and %MEM data processing in External Tables is much less error prone if you read the data as varchar2.
select count (*) from person p1, person p2	A missing join predicate is an easy way to create a load intense and long running SQL statement for tests.
v_top	For better distinction between the „real“ V$ views in the Oracle Data Dictionary I decided to use the prefix v_. For even better distinction you may create v_top in a separate schema.
nohup ./runtop.sh &	Using nohup you can exit the UNIX shell in which you started the runtop.sh script; otherwise you will receive the message „there are stopped jobs“ when you try to exit the shell. Stop the script using kill $ ps -ef \| grep runtop oracle 11044 1 0 10:28 ? 00:00:00 /bin/sh ./runtop.sh oracle 13256 11851 0 10:37 pts/0 00:00:00 grep runtop $ kill 11044 $