Oracle 10g für Admission Control und Analysefunktionen einrichten

[mailya]

Hallo liebes Tutorial-Foren-Team!

Wusste ja gar nicht, dass es hier auch eine Ecke für Datenbank-Geschädigte gibt ;-)
Erst einmal ein Lob! Die durchstöberten Threads wirken sehr nett und hilfsbereit - zudem aber auch sehr kompetent!

Doch nun zu meinem Problem bzw. wohl eher Anliegen.
Im Rahmen meiner Studienarbeit die ich gerade schreibe (Analyse von Kapazitäten bei Oracle) muss ich einen Oracle 10g-Server aufsetzen. Mit Hilfe des Universal Installers ist mir dies auch nicht sonderlich schwer gefallen.
Leider tu ich mir bei der optimalen Konfiguration ein wenig schwer.

Das was der Server leisten soll, sind vergleichbare Ergebnisse zu liefern. Ich generiere 3 verschiedene Benutzertypen. Diese Benutzertypen sind durch verschiedene immer gleich bleibende Transaktionen/Queries gekennzeichnet. Also wird der Ressourcenverbrauch (IO/CPU), bei einem identischen Benutzer immer der gleiche sein.
Um jedoch diesen Ressourcenverbrauch zu messen, habe ich versucht in einer "Endlosschleife" immer die gleichen Anfragen an den Datenbankserver mittels PL/SQL Prozeduren mit eingebetteten Anfragen zu ermitteln.
Leider sind die Ergebnisse je öfter die Schleife durchläuft immer langsamer und somit nicht aussagekräftig.

Ich vermute das liegt an den Redo-Log-Buffern, die pro Transaktion immer größer werden? (Bitte nicht auslachen, wenn ich hier komplett "sinnlose" Annahmen treffe. Bin leider kein Oracle bzw. allgemein DB-Experte!)

Habe versucht nach jeder EINZELNEN Messung die Datenbank herunterzufahren und anschließend wieder zu starten. Jetzt waren die Ergebnisse in etwa identisch. Doch es kann nicht sein, dass es hierbei keinen einfacheren Weg gibt?

Wie würdet ihr hierbei vorgehen?

Ziel des ganzen soll eine Tabelle sein, in der für jeden der 3 Benutzer Werte für den CPU und IO Verbrauch hinterlegt werden. Wenn der Benutzer nun in Zukunft mit der Datenbank verbindet, wird überprüft, wieviel Ressourcen mittels bisherigen Erfahrungen (==Datenbankspalte des Benutzers) verbraucht wurde und die Anfrage wird mit einem Algorithmus ausgewertet. Dieser Algorithmus wird zugeliefert und fungiert für mich als Black-Box.

Ich habe mir gedacht, das ganze per Trigger oder alternativ per VPD zu lösen... Hier wird eine PL/SQL-Prozedur aufgerufen, die die Werte aus der ertsllten Ressourcen-Tabelle ermittelt sowie die aktuelle Systemauslastung aus den v_$-Tabellen von Oracle. Dies alles wird dann an den Algorithmus übergeben (der ist übrigens in Java geschrieben; würde dann über meine PL/SQL-Prozedur "aufgerufen" werden!) und bekommt sozusagen eine 1 für Annahme und eine 0 für Ablehnung der Anfrage! Habe mir hierbei gedacht, das ganze per RMI zu lösen. Oder per Beans. Was performanter und Ressourcenschonender ist. Bei Beans hätte ich jedenfalls noch keine Idee wie ich vorgehen müsste.
Oder gibt es da bessere Möglichkeiten?

Vielen Dank schon einmal im Voraus für eure Hilfe! Werde jetzt erstmal Windows 2003 Server und anschließend mal wieder Oracle 10g R2 installieren

Gruß,

mailya

Achso, hier noch die Konfiguration des "armen,leistungsschwachen" Servers:
Die Konfiguration des "Servers" ist wie folgt (leider steht mir kein schnellerer Rechner zur Verfügung!)
FSC Celsius 400
P4 1700 MHz
1GB RAM
40 GB HDD
OS: Win 2003 Server Enterprise
DB: Oracle 10g R2

 

[Exceptionfault]

Ich vermute das liegt an den Redo-Log-Buffern, die pro Transaktion immer größer werden?

So ähnlich Der Redo Log Buffer ist i.d.R. recht klein (ca. 1MB). Hier werden die Änderungen
gepuffert bevor sie in die Redo Logs auf Platte geschrieben werden. Meistens ist das schreiben in
die RedoLogs der Engpass und bremmst die Transaktionen aus. Die Größe der Transaktion ist eigentlich
weniger relevant, da Oracle optimistisches COMMIT macht, d.h. auch wenn die Transaktion eigentlich
noch nicht commited ist, werden die Aktionen in den RedoLogs eingetragen um später ein schnelles
Commit zu machen. Dies hat zur Folge dass ein Rollback etwas länger dauert.

Wenn du deine Schleife in PL/SQL machst, könnte die Zeitverzögerung auch von den Context - Switches
zwischen dem SQL Layer und dem PL/SQL Layer kommen. Die schlagen bei Schleifen stärker ins Gewicht
weshalb man eher BULK Operationen macht die nur einen ContextSwitch erfordern.

Habe versucht nach jeder EINZELNEN Messung die Datenbank herunterzufahren und anschließend
wieder zu starten. Jetzt waren die Ergebnisse in etwa identisch. Doch es kann nicht sein,
dass es hierbei keinen einfacheren Weg gibt?

Du kannst einen Neustart quasi simulieren, indem du den Shared Pool und den Buffer Cache leerst:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

Werte für IO und CPU bekommst du aus der View v$sesstat, verknüpft mit der v$statname. Diese kannst
du z.B. mit einem LogOff Trigger bei der Abmeldung in deine Tabelle übernehmen und so pro Session
die Werte protokollieren.

Wenn ich dein Problem richtig verstanden habe willst du Aktionen je nach Last der Datenbank und
bisherigen Aktionen des Users verbieten oder erlauben ?
In dem Fall würde ich mir den RessourceManager etwas genauer anschauen.

 

[mailya]

Hi!
Danke dir für deine schnelle, hilfreiche Antwort!!

Wenn du deine Schleife in PL/SQL machst, könnte die Zeitverzögerung auch von den Context - Switches
zwischen dem SQL Layer und dem PL/SQL Layer kommen. Die schlagen bei Schleifen stärker ins Gewicht
weshalb man eher BULK Operationen macht die nur einen ContextSwitch erfordern.

Könntest du mir hierzu eventuell noch ein paar detailierte Informationen geben? Eventuell eine Referenz zu Whitepapers oder bzgl. Laufzeitverhalten einige Messreihen? Wie sieht der Unterschied von BULK-Operationen genau aus?

Bitte mein Unwissen zu entschuldigen. Hab mir bisher alles hierbei selbst beigebracht und wurde sozusagen ins kalte Wasser geworfen...

Das Simulieren des Neustarts werde ich auf jeden Fall mal testen. Danke für den Tipp.
Die Idee mit dem Logoff-Trigger hört sich auch sehr gut an... Ich wusste schon, warum ich euch frage.. Ihr gebt mir echt total guten Input

Wenn ich dein Problem richtig verstanden habe willst du Aktionen je nach Last der Datenbank und
bisherigen Aktionen des Users verbieten oder erlauben ?
In dem Fall würde ich mir den RessourceManager etwas genauer anschauen.

Also der Resource-Manager ist ja der eigentlich "Casus-Knackus", weswegen das "Projekt" ins Leben gerufen wurde. Er ist scheinbar einfach zu leistungsschwach und zu starr. Es gibt hier einen ALgorithmus (meine Black-Box), die mit Hilfe von wirtschaftlichen Kennzahlen und Vorausschätzungen ob wichtigere Anfragen kommen werden eine aktuelle Anfrage Ablehnen und Annehmen kann. Und dies anhand der aktuellen Auslastung des Servers und der Messreihen der individuellen Benutzer-Verbrauchszahlen.

Die Tabelle mit diesen Verbrauchszahlen könnte man dann ja auch theoretisch dynamisch wachsend machen und so Benutzerverhalten analysiert werden. (Also das wäre jetzt reine Theorie).

Was ich machen muss ist die Umsetzung in Oracle, dass der externe Java-Algorithmus mit Werten gefüttert wird und somit die kommende Anfrage (in diesem Fall: der Login eines Benutzers) zur jeweiligen Zeit X angenommen oder abgelehnt wird. Ablehnen heißt in diesem Fall sozusagen in eine Warteschlange gesetzt zu werden.

Und wie gesagt, der Resource-Manager allein reicht dafür nicht aus. Im nächsten Schritt wird die ankommende Anfrage anstatt abgelehnt in eine niederwertigere Resource Consumer Group verschoben. Das simuliert dann die einreihung in die Warteschlange.

Sind nun alle Klarheiten beseitigt? Ich hoffe natürlich nicht *g*

Grüße und schon mal danke für die erste Antwort,

mailya

 

[Exceptionfault]

Jetzt ist mir auch deine Aufgabe ein bisschen klarer ;-)
Messreihen zu BULK Operationen kann ich dir leider keine geben. Ich bin in der Wirtschaft, da wird
probiert und nicht gemessen Am besten suchst du mal bei google nach "Oracle BULK Perfromace"
oder so.

Im Grunde lässt es sich so erklären. Anstatt einen Cursor aufzumachen und Zeile für Zeile
durchzuschleifen, definiert man eine Tabelle in PL/SQL, lädt das komplette Ergebnis der Abfrage in
die Tabelle und geht darin dann die Zeilen durch. Der Unterschied ist, dass er zwar mehr Memory braucht
um die Daten alle auf einmal zu laden, dafür aber nur einmal zwischen der SQL und der PL/SQL Engine
wechseln muss und nicht für jede Zeile...

Ich habe übrigens mal etwas ähnliches geschrieben um ein paar Infos über die Nutzung von unseren
Applikationen zu erhalten. Die PL/SQL Prozedur sammelt ausgewählte Statistiken zur aktuellen
Session und schreibt diese in eine Statistiktabelle zusammen mit der UserID und der Applikation
(ModuleID). FIRST_VALUE ist der Zeitpunkt der ersten Messung, LAST_VALUE der letzte Wert. Die
Prozedur wure auch über einen LogOff Trigger ausgeführt.

Was wahrscheinlich nicht ganz so leicht wird, ist die Ermittlung der aktuellen Last auf der Datenbank.
Oracle liefert zwar tausende von Werten, diese aber richtig in Abhängigkeit zu interpretieren
ist eine sportliche Aufgabe ;-)

Hier wird übrigens auch BULK BINDING genutzt *g*

PROCEDURE GATHER_SESSION_STATS
IS
    TYPE r_statval IS RECORD( 
        NAME    VARCHAR2(64),
        NVALUE  NUMBER
    );
    TYPE statval_tab IS TABLE OF r_statval;
    t_statval   statval_tab;
BEGIN

    SELECT  n.NAME, v.VALUE
    BULK COLLECT INTO t_statval
    FROM    v$sesstat V INNER JOIN v$statname N 
    ON      V.STATISTIC#  = N.STATISTIC#
    WHERE   V.SID         = TO_NUMBER( SYS_CONTEXT( 'USERENV',  'SID') )
    AND     V.STATISTIC#  IN ( 4, 5, 6, 12, 13, 39, 66 );
    
    FOR i IN t_statval.FIRST..t_statval.LAST LOOP
        COUNT_STATISTIC( t_statval(i).NAME, t_statval(i).NVALUE );
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        PKG_ERRORS.LOG_ERROR ( 0, 'PKG_STATISTICS.GATHER_SESSION_STATS', SQLERRM, SQLCODE );
        RAISE;
END;

       MODULEID       APPUSERID STATISTIC                                VALUE FIRST_VALUE         LAST_VALUE
--------------- --------------- ------------------------------ --------------- ------------------- -------------------
              1           10046 logon                                       72 12.06.2006 15:35:12 27.06.2006 12:45:22
              1           10047 logon                                      874 01.06.2006 18:29:27 27.06.2006 12:47:23
              0           10006 logon                                        2 05.05.2006 13:02:33 05.05.2006 14:12:04
              1           10047 user commits                               135 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 user rollbacks                               0 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 user calls                                 944 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 CPU used by this session                  5327 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 DB time                                  15243 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 physical read total bytes             82452480 27.06.2006 12:52:56 27.06.2006 12:52:56
              1           10047 physical write bytes                         0 27.06.2006 12:52:56 27.06.2006 12:52:56

 

[mailya]

Hallihallo!
Ich nochmal. Habe nun ein paar Tage "Urlaub" bzw. wohl eher Fußballpause gemacht und habe mich nun wieder an mein kleines Projekt gesetzt.

Mittlerweile bin ich dabei, die Logon und Logoff Trigger zu basteln. Aufgrund der Tatsache, dass ich jedoch eher noch Newbie in der Sache bin, wollte ich mir meine Überlegungen hier nochmal bestätigen oder sogar berichtigen lassen. Man lernt ja geren von Profis dazu

Also. Zuerst die mir "einfachere" Abfrage für den Logoff-Trigger. Nochmals zum Zusammenfassen: Ich müchte den CPU sowie IO-Verbrauch für die nun zu beendende Session ermitteln um diese dann in meine ausgelagerte Benutzercharakteristik-Datenbank abzulegen.
Hier nun der Quelltext:

select PHYSICAL_READS 
from v$session ses 
where ses.audsid = USERENV('SESSIONID');

select VALUE cpu_usage 
from v$session ss, v$sesstat se, v$statname sn 
where se.STATISTIC# = ss.STATISTIC# 
and NAME like '%CPU used by this Session%' 
and ss.SID = se.SID 
and ss.audsid = USERENV('SESSIONID');

Gibt es hierbei etwas zu optimieren bzw. ist etwas an meinen Überlegungen total falsch? Mächte wie gesagt die gesamte CPU-Time und IO-Verbrauch aller Abfragen in der Session des Benutzers mit USERENV('SESSIONID') ermittelt haben.


Der Logon-Trigger wiederum soll ja die aktuelle Systemauslastung ermitteln.
Hierzu wäre meine einzige Idee alle derzeit aktiven Sessions mit einem select zu erfassen und cpu sowie IO aufzusummieren. WIe das genau aussehen soll weiß ich zwar noch nicht so genau, aber vielleicht hat ja jemand von euch eine idee? ;-)
Mein Blick wandert rüber zu Exceptionfault...

Freue mich über eure Tipps und Tricks!
Und bitte bedenkt:
select * from wissen where name="mailya" and wissen<"0.0004"

Gruß,
mailya

 

[mailya]

ok, habe gerade die ersten fehler in meinem kleinen quellcodeschnipsel berichtigt.

Nun habe ich über ein Join mit den Tabellen v$sesstat, v$session und v$statname alle notwendigen Informationen zusammen für die jeweilige SESSION des Benutzers mit nur einer Query.

Nur leider bin ich noch immer verzweifelt auf der suche nach der tabelle mit IO und CPU Auslastung im Moment. Das was also der AWR grafisch aufbereitet.

Greetz,

mailya

 

[mailya]

Vielleicht hätte ich auch den berichtigten Code mit angeben sollen..

    SELECT  n.NAME, v.VALUE
    FROM     sys.v_$sesstat v, sys.v_$session ses, sys.v_$statname n
    WHERE  v.STATISTIC#=n.STATISTIC#
    AND       v.SID=ses.SID
    AND       ses.audsid=TO_NUMBER(sys_context('userenv', 'SESSIONID'))
    AND       n.NAME_IN ('CPU used by this...', 'DB time', '.......' );

Habe ein wenig rumgesucht und kam zur derzeitigen Auslastung des Systems (in Bezug zumindest auf CPU) zu folgender Möglichkeit:

    SELECT  VALUE
    FROM     sys.v_$sysmetric
    WHERE  METRIC_ID=2057
    AND       GROUP_ID=3;

Der ermittelte Wert (sogar in %) wird allerdings leider nur durch Oracle alle 15 sekunden neu gemessen.
Gibt es hier vielleicht eine Möglichkeit genauere Werte abzufragen? Und wie sieht es mit IO aus?

Vielen Dank erneut für Eure Hilfe!

Gruß,
mailya