Performance-Problem bei Oracle 8.1

[Metaner]

Hallo zusammen,

ich benötige unbedingt den Rat eines Ora-Experten. Ich habe ein recht mächtiges SQL Statement, das über 4 Tabellen abgesetzt wird. Das gesamte Statemant möchte ich jetzt hier nicht posten :-)). Nur kurz angerissen ...

SELECT COUNT(Attribub) FROM Table WHERE ... UNION (SELECT .... WHERE ... IN (SELECT ...) AND ... IN (SELECT ...))

Die Tabellen haben jeweils 550.000, 5.600.000, 350.000 und 600.000 Datensätze. Jetzt zu dem Problem.

Ich habe das SQL Statemant direkt per SQL Plus auf auf die Datenbank abgesetzt. Anschließend habe ich unter "Sitzungen" (im DBA-Studio eingelogt) die Abarbeitung des SQL Befehls verfolgt. Dabei konnte ich beobachten, das Oracle zunächst das SELECT COUNT(Attribt) ... (bis zum Union) ausgeführt hat ... und anschließend, das was nach Union kommt.

1. SQL Statement fragt eine Tabelle mit 550.000 Zeilen ab mit einer Verknüpfung (in WHERE Bedingung) zur Tabelle mit 5.600.000 Zeilen. Unter "Sitzungen" konnte ich jetzt die Verarbeitung verfolgen. Das Statement lieferte nach 4 Minuten das Ergebnis.

2. SQL Statement fragt wieder die Tabelle mit 550.000 Zeilen ab ... nur hier werden alle Tabellen in die WHERE Bedinung einbezogen. Jetzt mußte ich folgendes beobachten:

Es wird mir von Oracle angezeigt, dass etwa 37000 Blöcke verarbeitet werden. Diese werden auch recht schnell hochgezählt. Nach 8 Minuten sind etwa 95% abgearbeitet. Doch dann stockt die Verarbeitung. Es werden nur noch paar Blöcke pro Sekunde verarbeitet ... manchmal nur 1 Block in paar Sekunden !!!! Die Verarbeitung läuft zwar ... jedoch sehr sehr langsam. Angezeigt wird eine Restverarbeitungszeit von 20 Sekunden ... aber tatsächlich braucht es dann 40-50 Minuten bis das Ergebnis zurückgeliefert wird!!!!!! Dies habe ich nun dreimal durchgeführt. Jedesmal hatte ich beim zweiten SQL bei etwas 95% diesen Performance-Einbruch. Vor dem dritten Versuch habe ich auch mal den DB-Server und meinen Client neu gestartet!

Ich bin leider kein Oracle Experte, so dass ich nicht wirklich mit den boardeigenen Analyse-Werkzeugen umgehen kann ... hoffe daher, hier Hilfe zu finden.

Achja ... Indexe sind natürlich gesetzt!!!! :-)) Auf dem Datenbankserver läuft die Version 8.1i; auf dem Client 8.1.7 (unter Windows 2000 SP2).

Hat jemand eine Idee oder einen Tipp was ich tun kann? Für jeden Hinweis wäre ich sehr dankbar.

Gruss Jan

Übrigens: Auf SQL-Server 7.0 und 2000 (mit einen 10x kleineren Datenbestand ... eine größere hatte ich leider nicht) werden BEIDE Sqls in 10sec abgearbeitet.

[IJK]

mmmh

eigentlich bin ich kein Oracle-Experte, komme vom DB2

liest sich aber so, als solltest du mal einen Reorg auf die DBs veranlassen. Was du beschreibst könnte der Effekt sein, dass die im handelsüblichen Index liegenden Daten schnell verarbeitet werden, das es aber "Seiten" außerhalb gibt - und da kommen die "Schnarch"Zeiten her.

wenn also der DBA einen neuen Reorg fährt sollte das Statement schneller gehen können

WICHTIG: Das ist nur eine graue Theorie ohne Oracle-Wissen.

Trotzdem eine Alternative (das Statement liest sich komisch) - frag mal deinen DBA nach seinen "Verbesserungsvorschlägen"

LiGrü

Michael (IBM-geprägt)

[Metaner]

Danke für Deine Antwort.

Sicherlich ist das SQL Statement auch "schöner" zu schreiben. Ich muss nur leider ein OOSQL absetzen, das in unserer Entwicklungsumgebung von unserem Framework in ein SQL umgesetzt wird. Dieses Framework unterstützt zwar Oracle, hat aber Probleme mit JOINS.

Mein SQL Statement macht aber nix anderes bei der Abfrage SELECT ... WHERE (attribut1) IN (SELECT attribut1 FROM Table2 WHERE attribut=attribut) AND attribut2 IN (SELECT attribut2 FROM Table3 WHERE ...) als bei der Anwendung von JOINS!!!

Auf die Idee mit der Reorganisation bin ich gestern auch schon gekommen. Hatte ich nur vergessen zu schreiben. In Oracle gibt es eine Funktion "Statistiken aktualisieren" o.ä. ... was m. E. einer Reorganisation gleichkommt. Diese habe ich auf die verwendeten Tabellen angewendet. Anschließend hatte ich aber keinen Performancegewinn feststellen können.

Was für eine Möglichkeit habe ich, dieses SQL zu tracen/loggen damit ich feststellen kann, wo der Flaschenhals sitzt? Wie muss ich das einstellen?

Gruss Jan

[Olli_Master]

>> In Oracle gibt es eine Funktion "Statistiken aktualisieren" o.ä. ... was m. E. einer Reorganisation gleichkommt.

Das ist nicht richtig. Bei einer Reorganisation werden i.A. die Daten der Tabellen sozusagen "defragmentiert" - bei einem ANALYZE werden lediglich Statistiken für die optimierung von SQL-Statements erstellt. Ein Analyze verschiebt keine Daten innerhalb der Datendateien.

3 Vorschläge

a) Ich würde zunächst Versuchen herauszubekommen, was genau während des "Performance-Einbruchs" passiert bzw. warum er aufritt. Folgende Parameter der Datenbank müsste man auf geeignete Art und Weise ermitteln:

- Disk-Activity (Logical und Physical)

- Wartezyklen / Sperren

- Trefferrate für Puffer-Cache

- ...

Bestimmen kann man das per Oracle Enterprise Manager (Diagnostic Pack, Performance Analyzer) - oder per SQL-Skript (das ist aber sehr umständlich, da sich die Daten ändern und man ggf. den Verlauf benötigt...)

Eventuell sind eine oder mehrere Tabellen "fragmentiert". Dafür gibts ein Oracle-Werkzeug (TableSpace-Map) - aber glaube nur im Diagnostic-Pack für den Enterprise Manager (extra lizenzpflichtig!!!). Nach einer Defragmentierung mit dem Tool müssen aber alle Indices neu aufgebaut werden...

Wenn du das Tool nicht haben solltest und wenn du willst kann ich aber mal ein SQL-Skript für die Bestimmung der Fragmente pro Tabelle bzw. Index heraussuchen.

c) Welche Version hat die Datenbank (8.1.?.?.?) und welche Version hat der Client (8.1.7.?.?) - Die Client-Version sollte immer kleiner order gleich der Server-Version sein - NIEMALS größer. Das kannst du aber zur Not austesten, wenn du das SQL-Statement vom SQL-Plus des DB-Servers ausführst.

Ich hoffe da ist was hilfreiches dabei...

[Metaner]

Hi Olli,

danke für Deine Unterstützung.

1. Frage

Ich habe hier den Enterprice Manager installiert. Aber leider ist im Menü "Werkzeuge" kein Eintrag zu Tuning / Tablespace Map / SQL Analyzer etc. zu finden.

Habe mal die Installation erneut angeworfen und die benutzerdefinierte Option ausgewählt. Dort kann ich auch nichts zu o.g. Themen finden. Wie kommt das? Ist das eine extra Software???

2. Frage

Wie kann ich einen Tablespace reorganisieren?

Gruss Jan

[Jaraz]

Hi,

kann es nicht sein, das einfach dein physikalischer RAM nicht ausreicht um das Statement zu verarbeiten?

Bei 95% fängt Oracle dann an zu swappen.

Bist du sicher das auf jeder Spalte der where Bedingungen ein Index liegt?

Gruß Jaraz

[MoBaB]

ähnliches problem hatten wir.

bei uns waren es aber die schreibzugriffe und die damit zusammenhängende belastung der pagefile.

sitedem lagern wir die pagefile immer auf eine andere physikalische platte aus.

weiß aber net ob dir das was hilft!

[Olli_Master]

Es ist wichtig, dass du zunächst bestimmst, was genau das Problem ist. Eine Reorganisation von Objekten ist nämlich nicht ganz so einfach.

Nun zu deinen Fragen:

> 1. Frage: Ist das eine extra Software???

Ja - wie gesagt, es handelt sich um Zusatzpacks für den Enterprise-Managers (OEM), welche extra lizensiert und installiert werden müssen.Informationen über den OEM - Bis zur 2.2-Version des OEM müssen die Packs außerdem auf dem Administrationsrechner installiert sein.

Folgende Packs wären für dich wichtig:

- Diagnostic Pack: Analyse der Datenbank-Problemen, Bestimmung des Fehlers

- Tuning Pack: Reorganisation von Tabellen und Indices

> 2. Frage: Wie kann ich einen Tablespace reorganisieren?

a) Mit installierten Tuning-Pack via OEM unter

"Werkzeuge" --> "Tuning Pack" --> "Reorganisations-Assistent" (Datensicherung!!!)

Ohne Tuning Pack: Indem man die Speicherparameter für die defragmentierten Tabellen/Indices korrekt vergibt (--> DBA) und dann das entsprechende Datenbankschema exportiert und neu importiert. (-->DBA ... Datensicherung, usw.)

------------

Bevor du mit Sachen wie einer Reorganisation anfängst, prüfe erst mal, ob du wirklich Tabellen bzw. Indices mit vielen Fragmenten (>=10) hast (Die Spool-Datei musst du ggf. noch anpassen, für die Indices musst du 'TABLE' in 'INDEX' ändern...). Alles ab 10 Segmenten kann die Performance beeinflußen.

spool c:\temp\DBTuning_FragNeed.lst

set line 200

set echo     off

set feedback off

column "Next [M]" format  9G990D00

set heading on

set termout on

select substr (de.owner, 1, 20)           "Owner"

     , substr (de.segment_name, 1, 30)    "Table Name (Segment)"

     , substr (de.tablespace_name, 1, 12) "Tablespace Name"

     , count (*)                          "Frag"

     , dt.next_extent / 1024 / 1024       "Next [M]"

     , substr (df.name, 1, 35)            "DataFile Name"

  from sys.dba_extents de

     , v$datafile      df

     , sys.dba_tables  dt

  where de.file_id      = df.file#

    and de.owner        = dt.owner

    and de.segment_name = dt.table_name

    and de.segment_type = 'TABLE'

  group by de.owner, de.segment_name, de.tablespace_name, dt.next_extent, df.name

  having count(*) > 9

  order by count(*) desc;

spool off

Das mit dem zu geringem physikalischen Speicher ist übrigens eine gute Idee, dass müsste man mit den Systemmonitor-Statistiken für Windows 2000 erkennen können.

Noch Fragen?

[Enno]

andersrum angefangen:

wie hoch ist denn deine Speicherauslastung während der Abarbeitung?

wenn noch nicht voll oder so ca. 75% ausgelastet, versuche mal folgendes:

im %ORAHOME%/admin/%Instanzname%/pfiles

oder in %ORAHOME%/database

gibt es eine datei %instanzname%.ora

darin gibts nen parameter BLOCKBUFFERS

versuch mal diesen weiter nach oben zu drehen.

danach die instanz neu starten.

und wieder testen.

du kannst auch mal die speicherparameter etwas hoch drehen.

aber immer aufpassen, viele parameter sind pro angemeldetem user. können also im extremfall ziemlich hochgehen.

MfG

Enno