Oracle

Oracle

Posts 1-7 of 7
  • André Brosda
    André Brosda    Premium Member
    The company name is only visible to registered members.
    Locks per Row auslesen ORA 11
    Wir haben einen Kunden mit einer Oracle 11 64Bit Datenbank (die Möglicherweise auch noch uaf einem Cluster läuft....

    Unsere Anwendung hat ein paar hochfrequentierte Tabellen für Interne Nr. Kreise etc (das lässt sich nicht über Sequences abbilden)... Kann man abfragen (als nicht sysdba) welche Sperren auf der Tabelle bzw auf den Rows sind? Die Tabelle hat nur ein paar 100 Datensätze wird aber natürlich ständig benutzt.
    • 27 Jul 2011, 12:09 pm
  • Gerrit Haase
    Gerrit Haase    Premium Member
    The company name is only visible to registered members.
    Re: Locks per Row auslesen ORA 11
    Leserecht auf V$-Tabellen reicht, ist in der DBA-Rolle mit drin (z.B. User: SYSTEM):

    List locks:
    ========
    column lock_type format a12
    column mode_held format a10
    column mode_requested format a10
    column blocking_others format a20
    column username format a10
    SELECT session_id
    , lock_type
    , mode_held
    , mode_requested
    , blocking_others
    , lock_id1
    FROM dba_lock l
    WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread')
    /


    Blockierende Sessions:
    ===================
    select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
    from v$lock l1, v$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
    and l1.id2=l2.id2
    /

    Welche Objekte sind Locked:
    =======================
    set lines 100 pages 999
    col username format a20
    col sess_id format a10
    col object format a25
    col mode_held format a10
    select oracle_username || ' (' || s.osuser || ')' username
    , s.sid || ',' || s.serial# sess_id
    , owner || '.' || object_name object
    , object_type
    , decode( l.block
    , 0, 'Not Blocking'
    , 1, 'Blocking'
    , 2, 'Global') status
    , decode(v.locked_mode
    , 0, 'None'
    , 1, 'Null'
    , 2, 'Row-S (SS)'
    , 3, 'Row-X (SX)'
    , 4, 'Share'
    , 5, 'S/Row-X (SSX)'
    , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
    from v$locked_object v
    , dba_objects d
    , v$lock l
    , v$session s
    where v.object_id = d.object_id
    and v.object_id = l.id1
    and v.session_id = s.sid
    order by oracle_username
    , session_id
    /


    Welche Row is Locked:
    ===================
    select do.object_name
    , row_wait_obj#
    , row_wait_file#
    , row_wait_block#
    , row_wait_row#
    , dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
    ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
    from v$session s
    , dba_objects do
    where sid=&sid
    and s.ROW_WAIT_OBJ# = do.OBJECT_ID
    /


    Then select the row with that rowid...
    ============================
    select * from <table> where rowid=<rowid>;
    • 27 Jul 2011, 12:34 pm
  • Jörg Sobottka
    Jörg Sobottka
    The company name is only visible to registered members.
    Re: Locks per Row auslesen ORA 11
    Hallo Gerrit,

    eine gute Zusammenstellung. Ich würd nur gern mehr über die Hintergründe erfahren.

    André Brosda schrieb:
    Wir haben einen Kunden mit einer Oracle 11 64Bit Datenbank (die Möglicherweise auch noch uaf einem Cluster läuft.... Was heisst möglicherweise auf einem Cluster? Windows Failover Cluster, RAC?

    Unsere Anwendung hat ein paar hochfrequentierte Tabellen für Interne Nr. Kreise etc (das lässt sich nicht über Sequences abbilden)... Kann hochfrequentiert mit Lesen und Schreiben? Nur Lesend? Nur schreibend?
    man abfragen (als nicht sysdba) welche Sperren auf der Tabelle bzw auf den Rows sind? Die Tabelle hat nur ein paar 100 Datensätze wird aber natürlich ständig benutzt.
    Was ist denn das Problem? Eine hohe CPU-Last? Viele Buffer Gets? Viele Disk Reads? Lange Ausführungszeit?

    Wurden die sessions schon einmal getraced?

    bzgl. den ganzen V$ Views:
    Wenn der User temporär SELECT_CATALOG_ROLE gegranted bekommen würde, hätte er ausreichend Rechte um alle Untersuchungen durchzuführen. Das ist im Übrigen auch die Rolle, die man im Grid Control benötigt, um einen Read Only User anzulegen. Viele Kunden scheuen sich, eine DBA User rauszugeben (zu recht).

    Viele Grüsse
    Jörg Sobottka
    Installation, Migration, Performance Tuning, High Availability, Remote Monitoring
    • 27 Jul 2011, 1:19 pm
  • André Brosda
    André Brosda    Premium Member
    The company name is only visible to registered members.
    Re^2: Locks per Row auslesen ORA 11
    Möglicherweise habe ich geschrieben weil bei dem Kunden nicht ganz klar ist, auf was für Maschinen die DB läuft.Sie läuft nicht unter unserer Kontrolle/Verwaltung.

    Das Problem das wir haben ist das Zugriffe auf unsere zentrale NummernKreisTabelle hin und wieder Ärger macht, weil sie gesperrt ist und dann wohl ein LockTimeOut auftritt. Ich hab bis jetzt aber nicht mal die Fehlermeldung gesehen, an mich wurde nur der Wunsch mit den Locks herangetragen.
    • 27 Jul 2011, 2:50 pm
  • Jörg Sobottka
    Jörg Sobottka
    The company name is only visible to registered members.
    Re^3: Locks per Row auslesen ORA 11
    Hallo Herr Brosda,


    André Brosda schrieb:
    Das Problem das wir haben ist das Zugriffe auf unsere zentrale NummernKreisTabelle hin und wieder Ärger macht, weil sie gesperrt ist und dann wohl ein LockTimeOut auftritt. Ich hab bis jetzt aber nicht mal die Fehlermeldung gesehen, an mich wurde nur der Wunsch mit den Locks herangetragen.
    aber Sie kennen ja die Applikation. Wird von dieser Tabelle gelesen und geschrieben? Wenn ja, haben Sie evtl. ein Hot-Spot Problem, d.h. das Schreiben und das Lesen als Full Table Scan blockieren sich gegenseitig. Abhilfe kann da ein Index bringen, der die Selects abfackelt, so dass die Updates in der Tabelle geschrieben werden können. Wichtig ist, dass der Optimizer dann aber auch dazu gezwungen wird, den Select zu nehmen (ein seltener Fall von Index-Hint oder Statistiken "manipulieren").

    Viele Grüsse
    Jörg Sobottka
    Installation, Migration, Performance Tuning, High Availability, Remote Monitoring
    • 27 Jul 2011, 2:58 pm
  • Randolf Geist
    Randolf Geist
    The company name is only visible to registered members.
    Re^3: Locks per Row auslesen ORA 11
    André Brosda schrieb:
    Das Problem das wir haben ist das Zugriffe auf unsere zentrale NummernKreisTabelle hin und wieder Ärger macht, weil sie gesperrt ist und dann wohl ein LockTimeOut auftritt. Ich hab bis jetzt aber nicht mal die Fehlermeldung gesehen, an mich wurde nur der Wunsch mit den Locks herangetragen.
    Ich würde davon ausgehen, dass es sich um eine Tabelle handelt, auf die serialisiert per Sperrung zugegriffen wird, um monoton aufsteigende Nummernkreise zu erzeugen, die keine Lücken beinhalten - eine Anforderung, die mit Oracle Sequences nicht abgebildet werden kann.

    In diesem Zusammenhang tritt dann wahrscheinlich eher ein "ORA-00060 Deadlock" auf, wenn die Applikationslogik ein "Überkreuz" solcher Sperrungen verursachen kann.

    Eine andere Variante wäre ein Timeout auf Applikationsebene, da Oracle bei "normalen" DML-Locks (also keine Deadlocks) keinen Timeout anbietet, ausser bei SELECT ... FOR UPDATE.

    Bezüglich Ihrer eigentlichen Frage: Man kann in Oracle aufgrund der Implementierung der Row-Level Locks nicht mittels einer einfachen Abfrage feststellen, welche Rows einer Tabelle gesperrt sind - die obige Abfrage zeigt nur Rows an, die aufgrund einer Blockierung eines Prozesses relevant sind, und die Angaben sind je nach Oracle-Version nicht immer 100% zuverlässig. Sie zeigt nicht an, welche Rows im allgemeinen darüber hinaus gesperrt sind.

    Falls ein Deadlock auftritt, schreibt Oracle automatisch einen entsprechendes Trace-File, das hilft, dem Problem auf Applikationsebene nachzugehen.

    Möglicherweise, je nachdem, wie Ihre Applikationslogik aussieht, könnte auch eine Änderung im Lock-Verhalten in Oracle 11g zu Ihren Problemen beitragen - falls Sie also das Problem unter 10g so nicht haben, aber unter 11g reproduzierbar ist, wäre das auch zu überprüfen, z.B.:

    http://hoopercharles.wordpress.com/2010/01/07/deadlock-on-or...

    Allerdings betrifft diese Änderung eher PK/FK-Beziehungen.

    Mit freundlichen Grüßen,
    Randolf Geist

    Oracle ACE Director / OakTable member
    http://oracle-randolf.blogspot.com/
    • 27 Jul 2011, 5:06 pm
  • André Brosda
    André Brosda    Premium Member
    The company name is only visible to registered members.
    Re^4: Locks per Row auslesen ORA 11
    Hmmm, ich werde mir mal die Indizes ansehen... Ist zwar "eigentlich" nicht meine Baustelle, aber der Tipp ist gut :-)

    Die Applikation ist Powerbuilder, puh da muesste ich jetzt grübeln, wie da die Logik ist.... Ich vermute ein

    Begin Transaction;
    Select xyz into
    Update
    commit;

    Aber ich sehe mir das morgen noch mal an und poste auch eine Lösung wenn es die denn gibt :-)
    • 27 Jul 2011, 6:42 pm