18. März 2010

Keine "Out-Of-Space"-Fehler mehr: Resumable Statements!

English title: No more out-of-space errors any more: Resumable Statements!

Es kommt ja recht häufig vor, dass man mit einer SQL- oder PL/SQL-Operation (CREATE TABLE, INSERT) gegen eine Speicherplatzgrenze stößt.
The situation that a SQL or PL/SQL operation encounters a resource limit, is quite frequently.
SQL> create table mysales as select * from sh.sales;
create table mysales as select * from sh.sales
*
FEHLER in Zeile 1:
ORA-01536: Speicherplatz-Zuteilung für Tablespace 'USERS' überschritten
Lästig ist es, wenn es eine langlaufende Operation war und das Problem erst kurz vor Schluß auftritt. Nicht nur, dass die ganze Arbeit bis zum Auftreten des Fehlers umsonst war, auch das anschließende Rollback der Transaktion frißt ganz ordentlich Ressourcen weg. Und wenn die Ressourcen dann da sind, muss man wieder von vorne anfangen. Es wäre doch toll, wenn die Datenbank einfach eine Mitteilung raussenden, dann warten würde, bis der DBA die nötigen Privilegien oder Ressourcen eingeräumt hat und dann einfach weitermachte. Und genau das geht schon seit Oracle9i: Mit den Resumable Statements.
This is annoying, particularly if long-running operation encounter this error. Not only that the work which was done so far, will be lost, there will be also the rollback activity which again consumes resources. And then, after the DBA has granted the missing resources, the whole process has to run again. It would be much nicer if the database just sent a message that there is a resource limit, wait and finally - when the resource has been granted - proceeds in executing the SQL command. And this is possible since Oracle9i: With Resumable Statements.
Damit ein Datenbanknutzer Resumable Statements nutzen kann, benötigt er vom DBA zunächst die Rolle RESUMABLE. Anschließend wird das Feature wie folgt aktiviert.
A database user needs the RESUMABLE role in order to use the feature. Then Resumable Statements are being activated in a database session as follows.
SQL> alter session enable resumable;

SQL> alter session set resumable_timeout = 30;
Der Timeout ist quasi die Zeit, die dem DBA zur Verfügung steht, um die nötigen Ressourcen für den Datenbankuser freizugeben. Die Angabe ist in Sekunden - das Beispiel mit 30 Sekunden ist mit Sicherheit ein wenig knapp gewählt - denn in dieser Zeit wird ein DBA das wohl kaum schaffen. In der Praxis sind hier sicherlich höhere Werte (Bspw. "3600" für eine Stunde) angebracht. Zum Ausprobieren wollen wir aber mal bei 30 Sekunden bleiben.
Timeout denotes the time the database will wait after the resource limit error has been occurred. If the DBA does not grant the missing resources with (here) 30 seconds, the statement will fail with an error message. 30 seconds are nice for testing - in practice you might use higher values ("3600" for one hour) - it takes some time to notify the DBA, then they need to investigate what's happening, decide and execute the missing grant. To play with the feature you might keep with 30 seconds.
Setzen wir nun das obige Statement nochmals ab. Nun kommt die Fehlermeldung nicht mehr sofort, sondern eben erst nach 30 Sekunden und sie sieht auch etwas anders aus ...
So we issue the above command again - now the error message does not appear immediately but after 30 seconds - and it's a different error message now ...then
SQL> create table mysales as select * from sh.sales;
create table mysales as select * from sh.sales
*
FEHLER in Zeile 1:
ORA-30032: Timeout bei unterbrochener (wiederaufnehmbarer) Anweisung
ORA-01536: Speicherplatz-Zuteilung für Tablespace 'USERS' überschritten
Doch wie soll der DBA nun von dem aufgetretenen Problem etwas mitbekommen ...? Zunächst wäre das alert.log-File zu nennen. In dem Moment, in dem das Statement in den Suspend-Modus fällt, wird ein Eintrag gemacht.
So ... how is the DBA being notified about suspended SQL statements? First there is the alert.log file. Suspended statements are being logged.
:
Tue Mar 16 13:51:30 2010
statement in resumable session 'User RESTEST(97), Session 50, Instance 1' was suspended due to
    ORA-01536: Speicherplatz-Zuteilung für Tablespace 'USERS' überschritten
Man kann also hier schon den Usernamen, die Instanz (wichtig im Cluster-Umfeld) und die Session ablesen. Mit dieser Info kann der DBA nun in der Dictionary-View DBA_RESUMABLE das eigentliche Problem nachsehen.
The alert.log entry already shows the instance (Cluster installations), the username, the session id and the error message indicating the occurred problem. The DBA_RESUMABLE view contains more information.
SQL> select START_TIME, name, ERROR_NUMBER, ERROR_PARAMETER1, ERROR_MSG from dba_resumable;

START_TIME           NAME                           ERROR_NUMBER ERROR_PARA
-------------------- ------------------------------ ------------ ----------
03/16/10 13:53:23    User RESTEST(97), Session 50,          1536 USERS
                     Instance 1

ERROR_MSG
--------------------------------------------------------------------------------
ORA-01536: Speicherplatz-Zuteilung für Tablespace 'USERS' überschritten
Mit dieser Information wird recht schnell deutlich, was zu tun ist ... sobald der DBA die Quota auf dem Tablespace erweitert ..
The DBA now does know what to do - as soon as the tablespace quota is being granted ...
SQL> alter user restest quota unlimited on users;
... arbeitet das SQL-Kommando wieder weiter, was man anhand des alert.log auch sofort erkennen kann.
... the database resumed statement execution - this will be indicated in the alert.log file.
:
Tue Mar 16 13:51:30 2010
statement in resumable session 'User RESTEST(97), Session 50, Instance 1' was suspended due to
    ORA-01536: Speicherplatz-Zuteilung für Tablespace 'USERS' überschritten
Tue Mar 16 14:05:21 2010
statement in resumable session 'User RESTEST(97), Session 50, Instance 1' was resumed
Aus Sicht des Datenbankusers (oder der Applikation) ist dann überhaupt kein Fehler aufgetreten - das Statement ist einfach nur sehr lange gelaufen.
From the database users' view the statement just took very long - there was no error message at all.
SQL> create table mysales as select * from sh.sales;

Tabelle wurde erstellt.

Abgelaufen: 00:12:10.35
Mit der Session-ID (hier: 50) kann der DBA das Statement aber auch abbrechen lassen - das geht dann so:
The DBA can also decide to abort the suspended statement - the session id (here: 50) is needed for this:
begin
  dbms_resumable.abort(50);
end;
In dem Fall sieht die Datenbanksitzung etwas anderes ...
Then the database user gets an error message ...
SQL> create table mysales as select * from sh.sales;
create table mysales as select * from sh.sales
                                         *
FEHLER in Zeile 1:
ORA-01013: Benutzer hat Abbruch des aktuellen Vorgangs angefordert
Nun möchte der DBA nicht unbedingt ständig das alert.log File überwachen; schöner wäre ja eine Benachrichtigung per Email oder Pager oder anderen Diensten. Und auch das ist problemlos machbar - hierfür stellt die Datenbank einen eigenen Trigger bereit: AFTER SUSPEND ON DATABASE. Das folgende Beispiel sendet eine Email-Nachricht mit UTL_MAIL.
Monitoring the alert.log file is not the best approach to get notified about suspended statements. The database offers a special trigger which fires each time a statement is being suspended (AFTER SUSPEND ON DATABASE). The trigger code can then execute any kind of notification - the following example just sends an Email message via UTL_MAIL.
CREATE OR REPLACE TRIGGER notify_dba
AFTER SUSPEND ON DATABASE
BEGIN
  utl_mail.send(
    sender => 'dba-mail-account@mycompany.de',
    recipients => 'karl-dba@mycompany.de',
    subject => 'SQL-Kommando ist im "Suspended State" - bitte nachsehen'
  );
END;
/
Weitere Informationen zum Thema Resumable Statements findet Ihr (wie immer) in den Handbüchern und zwar im Administrators' Guide. Die Doku enthält auch eine Liste der Statements, die Resumable sein können. Alles in allem finde ich das ein sehr nützliches Feature.
The Oracle documemtation ("Administrators' Guide") contains more information about resumable statements (and also a list of statements which are resumable. This is IMHO a very useful feature

Keine Kommentare:

Beliebte Postings