ORACLE: Update einer Spalte nicht möglich

Hallo zusammen,

ich muß in meiner ORACLE8i-DB den Wert einer Spalte aktualisieren. Nach dem Update-Befehl (SQlPlus) ist der Rechner lahmgelegt, habe dann nach über einer Stunde abgebrochen. Die Tabelle hat ca. 16000 Zeilen und 40 Spalten. Kann ich in ORACLE irgendwelche Parameter ändern, damit das Update funktioniert/schneller wird?

Vielen Dank für Eure Hilfe

Marion

Hallo Marion,

ein paar mehr Infos wären gut. Betriebssystem? 8i heißt: 8.1.7.4 ? Wenn schon (noch) 8i, dann ist die letzte Version empfehlenswert, auch wenn Dein Problem nichts damit zu tun haben dürfte.
Wie lautet der Update-Befehl?
16000 Zeilen updaten sollte kein Problem sein, außer auf einem „Taschenrechner“. Hast Du genug Plattenplatz, TEMP-Tablespace und Rollbacksegmente, und Hauptspeicher? (Schwer zu sagen, was genug ist…). Ist die Tabelle analysiert? Gibt es nutzbare Indizes? Hast Du Dir mal den Ausführungsplan angesehen?
Genug Fragen für den Anfang?

Gruß, muzel

ich muß in meiner ORACLE8i-DB den Wert einer Spalte
aktualisieren. Nach dem Update-Befehl (SQlPlus) ist der
Rechner lahmgelegt, habe dann nach über einer Stunde
abgebrochen. Die Tabelle hat ca. 16000 Zeilen und 40 Spalten.
Kann ich in ORACLE irgendwelche Parameter ändern, damit das
Update funktioniert/schneller wird?

Vielen Dank für Eure Hilfe

Marion

Hallo mutzel,

in der Tat viele Fragen.
Also meine DB ist eine Kopie der ProdDB, ORACLE 8.1.7.3 (wird demnächst auf 9 upgedatet) auf meinem „Taschenrechner“ WinXPProf, 1,3 GH, 512MB Arbeitsspeicher, freier Speicherplatz 6 GB. Ist der das Problem?

Die Abfrage lautet: "UPDATE LEISTUNGEN SET FESTPREISABRECHNUNG=0 WHERE ROWNUM

Hallo Marion,

Dein System ist sicher ausreichend bestückt, wäre noch zu klären, ob Oracle genug davon abbekommt.
Das update ist trivial und sollte in nullkommanix erledigt sein. Was passiert eigentlich genau? Du logst Dich ein, schickst das Update los, und nichts passiert mehr?

Die Tabelle habe ich analysiert,

Gut. Entschuldige, wenn ich nochmal zurückfrage, Du weißt, wie…? Analyze Table usw…?

es werden keine Redo-Logs erzeugt

Wenn das wirklich wahr sein sollte, kann kein Update funktionieren.

nutzbare Indices sind nicht angelegt (nicht index-organisiert)

Hat nichts miteinander zu tun, ist aber auch egal :wink:.

…Jetzt werde ich 'mal versuchen, einen Ausführungsplan zu finden.

Erscheint mir fast zuviel Aufwand bei DEM simplen Update.
Hmm. Hast Du den Enterprise Manager installiert? Da könnte man mal genauer analysieren, wo die DB krank ist, ob vielleicht gerade ein Tablespace überzulaufen droht, ob genug TEMP-Tablespace vorhanden ist, genug Hauptspreicher reserviert ist, überhaupt alle Initialisierungsparameter.
Auf anderem Wege ist das etwas mühsam.
Hast Du die DB schonmal neu gestartet?
Was hast Du vor dem Update gemacht? Schon 99 andere Updates/Inserts/Deletes, vielleicht ohne commit oder rollback :wink:?
Falls keine Geheimnisse drinstehen, kannst Du ja mal die init.ora hier reinstellen oder mir per mail schicken. Normalerweise heißt die aktuell verwendete init.ora „init“(Datenbankname)".ora" und befindet sich im Verzeichnis database.
Soviel erstmal, Ferndiagnose ist eben etwas schwierig.

Gruß, muzel (ohne t)

Hi!

Und Du bist sicher, daß beispielsweise die Datensätze nicht gelockt sind?

Mittels zweiter SQL-Session bzw. einfacher über ein Tool (z.B. Toad) kannst Du ja nachschaun, wie weit das update überhaupt kommt

Eine äußerste um-7-Ecken-Lösung wäre eine kleine PL/SQL-Prozedur in einem kleinen SQL-Script, daß mittels Cursor immer nur einen Datensatz nach dem anderen updatet - dazwischen läßt Du Dir diverseste Log-Meldungen ausgeben (Ich weiß, dieser Ansatz ist zwar äußerst unprofessionell, hat mir aber bereits ein paar mal wegen fehlender Berechtigungen und fehlender Tools geholfen)

Grüße,
Tomh

Moien,

wenn das update über eine Stunde läuft kannst du davon asugehen, daß es GAR nicht läuft, d. h. wie schon vorher erwähnt irgendwo gelockt wird! so lang dauert es net mal auf nem ausgebautem Taschenrechner…

Starte das update nochmals und sehe dir dann über den OEM an ob bzw. wo eine Session gelockt ist! Dort kannst du auch sehen von welchem Rechner die Sperre kommt, sodaß du entscheiden kannst ob du die Session killen willst odeer net…

Gruß

Bernd

Hallo muzel!

Ich stimme mit BerndW und tomh darin überein, dass das Problem in bestehenden Locks besteht, DEN update sollte jedes System, auf dem sich Oracle überhaupt erst installieren lässt in Nullkommanix schaffen.

Die Tabelle habe ich analysiert,

Gut. Entschuldige, wenn ich nochmal zurückfrage, Du weißt,
wie…? Analyze Table usw…?

DBMS_STATS ist hier übrigens unbedingt vorzuziehen, allerdings weiss ich nicht auswendig, ob das unter 8i schon verfügbar war…
Ganz sicher jedenfalls sieht es nicht danach aus, als könnte der update von Marion irgendetwas anderes als einen FTS machen, insofern sind die Statistiken hier ohnehin egal.

es werden keine Redo-Logs erzeugt

Wenn das wirklich wahr sein sollte, kann kein Update
funktionieren.

Ääähm, das ist schlicht und ergreifend falsch! Ich denke du verwechselst hier Redo (vulgo Logging) mit Undo (vulgo Rollback). Ich habe hier viiieeeele Tabellen, die keinen Redo generieren, die sich trotzdem bestens updaten lassen, und das sogar noch schneller als mit Redo (eh logisch…).

…Jetzt werde ich 'mal versuchen, einen Ausführungsplan zu finden.

Acuh ohne Tools kannst du (Marion) dir den übrigens mittels EXPLAIN PLAN FOR erstellen lassen. Er wird dann in der Tabelle plan_table abgelegt (evtl. muss dein DBA zuvor noch $ORACLE_HOME/rdbms/admin/utlxplan.sql laufen lassen).

Hmm. Hast Du den Enterprise Manager installiert? Da könnte man
mal genauer analysieren, wo die DB krank ist

Das geht auch ohne den EM (ich mag die Klickibunti - Tools alle nicht, aber das ist ohnehin eine andere Diskussion).

ob vielleicht
gerade ein Tablespace überzulaufen droht,

Welcher Tablespace sollte hier überlaufen? Marion setzt einen numerischen Wert auf einen anderen numerischen Wert, d.h. die betreffende Row braucht danach genausoviel Platz wie zuvor. Einzige Möglichkeit: Rollback/Undo. Aber auch das sollte keine Stunden in Anspruch nehmen.

ob genug
TEMP-Tablespace vorhanden ist

Den braucht der Update nicht (er sortiert nicht, macht keine Joins, kurz: nix, wofür er TEMP-Space verwenden müsste).

genug Hauptspreicher reserviert
ist,

Auch das wird kaum einen Einfluss haben.

überhaupt alle Initialisierungsparameter.

Bist du dir sicher? Das sind (unter 9i) ca. 250 (dokumentierte) Stück, wovon schätzungsweise 200 keinen Einfluss auf das genannte Statement haben dürften (zumindest nicht, ohne eine eingehende Analyse des Sessionstatus beim Absetzen des Updates), z.B. alle Java-Parameter.

Auf anderem Wege ist das etwas mühsam.

Der Meinung bin ich nicht, aber wie schon gesagt, ich schreibe mir eben lieber meine eigenen Scripts…

Hast Du die DB schonmal neu gestartet?
Was hast Du vor dem Update gemacht? Schon 99 andere
Updates/Inserts/Deletes, vielleicht ohne commit oder rollback
:wink:?

Es dürfte eher die Frage sein, was eine andere Session gemacht hat oder dabei ist zu tun. Die eigene Session sollte eher egal sein (wenn man mal von Rollback-Usage absieht).

Gruß auch,
Martin

Hallo,

also ich vermute mal, wir schießen sowieso mit Kanonen auf Spatzen, wahrscheinlich ist sie ganz allein auf ihrem PC mit ihrer Datenbank, also auch selbst schuld, wenn etwas klemmt, und da hilft mal durchstarten am ehesten.
(Kann mich irren).

DBMS_STATS ist hier übrigens unbedingt vorzuziehen, allerdings
weiss ich nicht auswendig, ob das unter 8i schon verfügbar
war…

Ich auch nicht, schon ne Weile her.

verwechselst hier Redo (vulgo Logging) mit Undo (vulgo
Rollback).

Ich dachte, ich hätte mal gelernt, daß alle Schreiboperationen über die Redologs gehen…?

Ich gebe ja zu, vieles war geraten, aber wenn die Datenbank steht, dann steht sie, und das kann viele Gründe haben, die nicht mit dem aktuellen Statement zu tun haben.

Gruß, muzel

Hallo muzel,

verwechselst hier Redo (vulgo Logging) mit Undo (vulgo
Rollback).

Ich dachte, ich hätte mal gelernt, daß alle Schreiboperationen
über die Redologs gehen…?

Nein, mach mal ALTER TABLE my_table NOLOGGING und sieh dir dann an, wieviel Redo da bei direct path writes (z.B. INSERT /*+ append*/) generiert wird. Allerdings hatte ich da etwas übers Ziel hinausgeschossen, ECHTE Update (also UPDATE my_tab SET col=:x WHERE …) Statements generieren immer Redo - zumindest wüsste ich nicht, wie man das ausschalten könnte.

Ich gebe ja zu, vieles war geraten, aber wenn die Datenbank
steht, dann steht sie, und das kann viele Gründe haben, die
nicht mit dem aktuellen Statement zu tun haben.

Ich suche nur immer gern die Ursache, bevor ich einen etwaigen Fehler per AEG-Verfahren verstecke (und nie wieder finde). Ein echtes Problem habe ich nämlich dann, wenn der Fehler auf der Produktivdatenbank auftritt und ich DANN anfangen muss zu suchen, weil meine einzige Lösungsvariante (ausschalten-einschalten-geht) nicht durchführbar ist.

Gruß
Martin

Hallo zusammen,

richtig, ich bin der einzige user auf meiner DB.

DBMS_STATS gibt’s schon für ORACLE8i.

Die DB habe ich jedesmal nach dem Absturz neu gestartet. Gibt aber bestimmt eine elegantere Möglichkeit, wieder mit der DB arbeiten zu können. Bleiben beim (gewaltsamen) Abbruch vielleicht irgendwelche DS-Sperren bestehen?

Die init.ora sieht so aus (vorsichtshalber mit allen Kommentaren, bevor ich was falsches lösche):

#######################################################################

db_name = „NS1T“

instance_name = NS1T

service_names = NS1T

db_files = 1024 # INITIAL

db_files = 80 # SMALL

db_files = 400 # MEDIUM

db_files = 1500 # LARGE

control_files = („F:\oracle\oradata\NS1T\control01.ctl“, „F:\oracle\oradata\NS1T\control02.ctl“, „F:\oracle\oradata\NS1T\control03.ctl“)

open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8 # INITIAL

db_file_multiblock_read_count = 8 # SMALL

db_file_multiblock_read_count = 16 # MEDIUM

db_file_multiblock_read_count = 32 # LARGE

db_block_buffers = 19200 # INITIAL

db_block_buffers = 100 # SMALL

db_block_buffers = 550 # MEDIUM

db_block_buffers = 3200 # LARGE

shared_pool_size = 52428800 # INITIAL

shared_pool_size = 3500000 # SMALL

shared_pool_size = 5000000 # MEDIUM

shared_pool_size = 9000000 # LARGE

large_pool_size = 614400
java_pool_size = 20971520

log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800

processes = 150 # INITIAL

processes = 50 # SMALL

processes = 100 # MEDIUM

processes = 200 # LARGE

parallel_max_servers = 5 # SMALL

parallel_max_servers = 4 x (number of CPUs) # MEDIUM

parallel_max_servers = 4 x (number of CPUs) # LARGE

log_buffer = 32768 # INITIAL

log_buffer = 32768 # SMALL

log_buffer = 32768 # MEDIUM

log_buffer = 163840 # LARGE

#audit_trail = true # if you want auditing
#timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5M each

Uncommenting the line below will cause automatic archiving if archiving has

been enabled using ALTER DATABASE ARCHIVELOG.

log_archive_start = true

log_archive_dest_1 = „location=F:\oracle\oradata\NS1T\archive“

log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

If using private rollback segments, place lines of the following

form in each of your instance-specific init.ora files:

#rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )

If using public rollback segments, define how many

rollback segments each instance will pick up, using the formula

# of rollback segments = transactions / transactions_per_rollback_segment

In this example each instance will grab 40/5 = 8

transactions = 40

transactions_per_rollback_segment = 5

Global Naming – enforce that a dblink has same name as the db it connects to

global_names = true

Edit and uncomment the following line to provide the suffix that will be

appended to the db_name parameter (separated with a dot) and stored as the

global database name when a database is created. If your site uses

Internet Domain names for e-mail, then the part of your e-mail address after

the ‚@‘ is a good candidate for this parameter value.

db_domain = us.acme.com # global database name is db_name.db_domain

Uncomment the following line if you wish to enable the Oracle Trace product

to trace server activity. This enables scheduling of server collections

from the Oracle Enterprise Manager Console.

Also, if the oracle_trace_collection_name parameter is non-null,

every session will write to the named collection, as well as enabling you

to schedule future collections from the console.

oracle_trace_enable = true

oracle_trace_collection_name = „“

define directories to store trace and alert files

background_dump_dest = F:\oracle\admin\NS1T\bdump
#Uncomment this parameter to enable resource management for your database.
#The SYSTEM_PLAN is provided by default with the database.
#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_plan
user_dump_dest = F:\oracle\admin\NS1T\udump

db_block_size = 8192

remote_login_passwordfile = exclusive

os_authent_prefix = „“

The following parameters are needed for the Advanced Replication Option

job_queue_processes = 4
job_queue_interval = 10
open_links = 4

distributed_transactions = 500
mts_dispatchers = „(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)“

Uncomment the following line when your listener is configured for SSL

(listener.ora and sqlnet.ora)

mts_dispatchers = „(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)“

compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536

#######################################################################

Viele Grüße

Marion

Hallo Marion,
auf Anhieb erkenne ich nichts, was wesentlich daneben liegt (gut ich habe z.B. bei log_buffer und sort_area_size mehr als zehnmal soviel, aber, wie gesagt, das Update schafft normalerweise auch ein Taschenrechner).

Ziemlich weit unten stehen ein paar Zeilen
von

job\_queue\_processes = 4

bis

... "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"

Ich vermute mal, die Einträge stammen vom Prod-System (und Du sitzt an einem Testsystem)? Würde ich mal probeweise auskommentieren.
Oder läuft bei Dir wirklich eine Replikation?

Und dann mal mit dem Enterprise-Manager draufschauen:
Nur mal als Beipiel:

  • den Tablespace suchen, in dem die Tabelle liegt (unter „Speicher“), Tablespace map aufrufen, nachschauen, ob der Tablespace voll (und dazu nicht automatisch erweiterbar) ist,

  • überprüfen, ob die Tabelle selbst eine Begrenzung (max extents) erreicht hat (unter „Schema“ bis zur Tabelle durchhangeln und die Speicherparameter ansehen) usw.
    Das kriegt man auch alles mit SELECTs heraus, z.B. die voreingestellten Speicherparameter der Tabelle aus der USER_TABLES,

    select * from user_tables where table_name = …

ähnlich für die Tablespaces, aber ich nehme eben doch lieber den OEM.

Gruß, muzel

Vermutlich geht deiner DB die physikalische Speicher aus. Versuche mal shared_pool_size = 1000000 zu setzen. Ich habe die Erfahrung gemacht,
dass ein zu großer Wert das OS zum swappen bringt und damit den ganzen Rechner extrem ausbremst. Für deinen Insert sollte dieser Parameter ohnehin belanglos sein.

Gruß Markus

Hi,
voreingestellt sind 10% vom Hauptspeicher - warum sollte die Kiste swappen?
Wieviel SGA haben wir eigentlich?
java_pool+shared pool+large pool + …? dürfte bei 512 MB Hauptspeicher immer noch kein Problem sein.

Gruß,
muzel

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Hi!

Oder läuft bei Dir wirklich eine Replikation?

Und das wäre nach dem Locking ein zweiter Klassiker - wobei ich mich frage, wie diese auf eine Stand-Alone-DB kommt … Full-Export?

Grüße,
Tomh

Hallo Marion!

Nachdem ich immer noch das Gefühl habe, dass dein Problem nicht mit den Initialisierungsparametern zu tun hat, mach mal dieses hier:

  1. Starte deinen Update
  2. Öffne ein 2. SQL*Plus und logge dich auf die DB ein
  3. „SELECT sid, username FROM v$session WHERE username IS NOT NULL;“
  4. „SELECT * FROM v$session_wait WHERE sid=[ergebnis_von_oben];“
  5. Den zweiten SELECT immer wieder mal wiederholen und nachsehen, worauf deine Session so wartet.
  6. Das Ergebnis (vor allem die Spalten event, p1text, seconds_in_wait, state sind interessant) mal hier reinstellen.
  7. Hoffen, dass einer von uns was damit anfangen kann :wink:

Gruß
Martin

Hallo,

also ich habe das Problem auf rabiate Weise gelöst, DB gelöscht und neu erstellt. War ein bischen Arbeit, aber und nun läuft das update in ein paar Sekunden. Woran’s lag, werde ich jetzt leider nicht mehr erfahren.

Vielen, vielen Dank für Eure Antworten!

Viele Grüße

Marion

Und was machst Du beim gleichen Problem auf der Produktions-DB :wink: ?

ich jetzt leider nicht mehr erfahren.

…und wir auch nicht, wirklich schade.

Gruß muzel

Hi,
voreingestellt sind 10% vom Hauptspeicher - warum sollte die
Kiste swappen?

Weil Oracle nicht nur eine SGA hat, sondern auch noch PGA verwaltet, und die können den Speicher auch vollmachen…

Wieviel SGA haben wir eigentlich?

Also unter 9i ist die SGA:
db_[nK_]cache_size (~db_block_buffers unter 8i) +
java_pool_size +
large_pool_size +
shared_pool_size +
log_buffer

Dazu kommt noch die PGA, die (wenn man keine Shared Server verwendet), durch
pga_aggregate_target
gesteuert werden kann. Das geht aber unter 8i nicht, da wird die Grösse der PGA durch
sort_area_size
hash_area_size
bitmap_merge_area_size
create_bitmap_area_size
und vermutlich noch einige andere Parameter gesteuert, die mir entweder unbekannt sind oder mir gerade nicht einfallen :wink:
Zu beachten sind vor allem die Parameter sort_area_size und hash_area_size, weil die pro Session bzw. sogar mehrmals pro Statement vergeben werden (d.h. ein einzelnes Statement könnte auch 10 x die hash_area_size verwenden).
Und DAMIT wird’s dann schon lustig mit dem Speicherbedarf. Hat aber natürlich mit der ursprünglichen Frage absolut nix mehr zu tun und steht nur hier, weil ich gestern wieder mal eine neue Lieferung Senf bekommen habe :wink:

Gruß
Martin