SQL

Lösung: Kann ein Select eine Tabelle sperren?

Ich danke Andrew Sayer für die richtige Lösung. Ein select statement ohne eine for update klausel kann natärlich kein Transaction lock setzen. Das Transaction lock muss daher von einem anderen Statement der selben Session stammen.

Solange kein commit stattgefunden hat, beibt der Lock ja aufrecht. Wir müssten daher mittels active session history in der Zeit zurückgehen, um fest zu stellen, woher das Transaction lock stammt.

Grundsätzlich ist es eine gute Idee, das lang laufende Select statement zu beschleunigen. Dadurch kann erreicht werden, dass das transcation lock eine kürzere Zeitspanne gehalten wird, da der commit Zietpunkt schneller erreicht werden kann.

 

Kann ein Select eine Tabelle sperren?

Vor kurzem untersuchte ich ein “enq: TM contention” wait event. Ich fand über eine Abfrage auf die active session history (ASH) die blocking session heraus.

Als ich die blocking session über eine ASH query untersuchte, war zur selben Zeit wie der TM lock in der blockierten session auftrat,  gerade ein lang laufendes Select aktiv.

Das ist doch merkwürdig, nicht wahr? Kann eine Select einen Lock erzeugen? Wo ist hier der Denkfehler? Und würde es helfen, das Select Statement zu beschleunigen?

Wer sich nicht sicher ist, kann sich bei Arup Nanda über den TM Lock informieren.

enq: TX row lock contention and enq:TM contention

Lösung: Index Rebuild: Magic or Voodoo?

Meine Leser haben das Problem natürlich richtig erkannt. Ich danke Jure Bratina, Andrew Sayer und Martin Berger für Ihre Beiträge.
Im folgenden sehen Sie jetzt den ganzen Testcase in kommentieer Form. Das meiste ist, denke ich, selbsterklärend.
Am Anfang wird das Szenario aufgebaut. Das Schema ist, wie gesagt, das SH Beispielschema.

alter table sales add  (sparse varchar2(300)); 
update sales set sparse = rpad('sometext',300, '*');
commit;
create index sparse_idx on sales (sparse);
select blocks from user_segments where segment_name ='SPARSE_IDX';

Lassen Sie uns jetzt die Grösse des Indexsegements prüfen:

select blocks from user_segments where segment_name ='SPARSE_IDX';

   BLOCKS
---------
    44032

select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';

LEAF_BLOCKS
-----------
      41766

Jetzt kommt ein Update, den ich in ähnlicher Form auch in der Original Datenbank gefunden habe:

update sales set sparse=NULL;

918843 Zeilen aktualisiert.

exec dbms_stats.gather_table_stats(user,'SALES');

Und wie wirkt sich das auf die Statistiken und die Segemente aus?

select leaf_blocks from user_indexes where index_name ='SPARSE_IDX';

LEAF_BLOCKS
-----------
          0
select blocks from user_segments where segment_name ='SPARSE_IDX';

    BLOCKS
----------
     44032

Da der Optimizer lediglich die Index Statistiken prüft, nicht aber die Segementstatistiken, hält der Optimizer den Index für winzig klein.
Das also alle nicht null Werte der Spalte sparse im Index sein müssen, ist es aus Sicht des Optimizers am besten, den angeblich kleinen Index zu scanen.
Das Indexsegment hat aber immer noch die volle Grösse. Erst ein Index rebuld schafft hier Abhilfe.

Index Rebuild: Magic or Voodoo?

Den heutigen Blog schreibe ich nicht gerne. Aber ich fühle mich der Wahrheit verpflichtet und das Thema ist zu interessant um es zu verschweigen.

Als in den Freelists gefragt wurde, ob der Index rebuild auch manchmal nützlich sein kann, habe ich das verneint, unter Hinweis auf entsprechende Einträge bei Mr. Index, Richard Foote.

Ich wusste schon, dass ich mich auf dünnen Eis bewege, aber ich  konnte der Chance oberschlau zu sein und einer gängigen Meinung zu widersprechen, einfach nicht widerstehen. Natürlich trat das unvermeidliche ein und Jonathan Lewis korrigierte mich mit den Hinweis, dass ein Index rebuild in manchgen Grenzfällen eben doch nützlich sein kann. Wie hatte ich bloss glauben können, unbemerkt von Jonathan’s Radar durch zu schlüpfen.

Kurz darauf, als wolle das Schicksal mich auch noch mahnen, fiehl mir bei einem Kunden eine Abfrage auf, die offensichtlich ineffizient war. Ich habe die Abfrage auf das SH  schema umgeschrieben und einen kleinen Testcase erzeugt. Wie ich diesen Testcase gemacht habe, werden Sie in der Lösung sehen. Genau das sollen Sie ja erraten.

select time_id from sales where sparse is not null;

Der Exection Plan mit Runtime Statitiken sieht aus wie folgt:


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |      1 |        |      0 |00:00:00.04 |   41770 |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES      |      1 |      1 |      0 |00:00:00.04 |   41770 |
|*  2 |   INDEX FULL SCAN                          | SPARSE_IDX |      1 |      1 |      0 |00:00:00.04 |   41770 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SPARSE" IS NOT NULL)

Aus welchem Grund wählt der Optimizer einen Full Index Scan? Eine kurze Überprüfung zeigt, dass Full Table scan sehr viel effizienter ist.Zudem ist die Schärtzung (E-Rows) auch noch richtig. Die Statitiken sind also aktuell.

Was ist hier geschehen? Hinweis: Ich habe nach dem DML auf der Tabelle gesucht und ich fand ein update Statement.

Eine unerwartete Bedingung in der where Klausel

Es macht mich stolz zu erfahren, dass Carlos Sierra meinen Blog verfolgt. Carlos ist mir aus meiner Zeit bei Oracle schon lange bekannt, auch wenn wir uns erst kürzlich das erste Mal getroffen haben. Ich schätze Carlos als einen Mann der Tat. Wenn er einen Missstand sieht, beklagt er sich nicht, sondern tut etwas dagegen.
Mit meinem nächsten Beispiel will ich zeigen, dass er auch ein scharfsinniger Analytiker ist.
Vor Kurzem sah ich in einem Plan eine Bedingung, die so nicht im Sql statement stand. Ich wollte wissen, wie die Bedingung in den Plan gekommen war.
Ich gebe dazu ein einfaches Beispiel: Gegeben sei eine Tabelle x die wie folgt aussieht


SQL> desc x
Name Null? Typ
----------------------------------------- -------- -----------------
Y NUMBER
Z NOT NULL NUMBER

Ich lasse die folgende Abfrage laufen:


select count(*) from x where z=1;

Der Exection Plan sieht aus wie folgt:


Plan hash value: 989401810

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   420 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| X    |  1144K|    14M|   420   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL("Z",1)=1)

Wieso weisst der Plan diese merkwürdige Bedingung auf? Die Antwort finded Ihr in Carlos Sierra’s Blog. Noch ein Hinweis: Es hat etwas mit Default Werten zu tun.

 

Lösung: Die Collection in der Collection

Die entscheidenden Hinweise zur Lösung der Aufgabe habe ich bei Adrian Billington gefunden. Es ist schon ein älterer Eintrag, aber immer noch gültig: introduction to bulk pl/sql enhancements in 9i.
Der Code unten sollte mit den Anmerkungen selbst erklärend sein.
Um zu zeigen, wie die Zugriffe auf die Daten funktionieren, habe ich noch eine Ausgabe über DBMS_output eingefügt.

— Zuerst muss man den Typ für die nested collection erzeugen

CREATE OR REPLACE type emp_t
AS
  object
  (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2) 
  )
/
create or replace TYPE tbl_emp  AS TABLE OF emp_t
/

 

DECLARE
  CURSOR c1
  IS
    SELECT deptno,
      dname,
      CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
                     ) AS tbl_emp  
           ) as emps -- ich brauche einen alias um die eingebette Collection referenzieren zu können
  FROM dept d;

  TYPE tbl_dept IS TABLE OF c1%ROWTYPE;
  depts tbl_dept ;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO depts;
   CLOSE c1;
   FOR i IN 1..depts.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(depts(i).deptno);
      FOR j IN  1.. depts(i).emps.COUNT
      LOOP
        DBMS_OUTPUT.PUT_LINE('***'||depts(i).emps(j).ename);
      END LOOP;
   END LOOP;
END;
/

 

Die Collection in der Collection

wir mussten einen sehr häufig genutzte PL/SQL Function umschreiben. Der Code litt stark unter dem Context Switch. Er wurde millionenfach aufgerufen. Wir setzen uns das Ziel, mit einem einzigen Bulk Collect alle Daten aus der Datenbank aus zu lesen. Ich gebe hier ein codeskellet, dass ich auf das scott/tiger schema angepasst habe.

DECLARE
   CURSOR c1
   IS
   SELECT deptno, dname
     FROM dept;
   CURSOR c2 (p_deptno NUMBER)
       IS
       SELECT empno,ename,sal, comm
         FROM emp
        WHERE deptno= p_deptno;
BEGIN
   FOR c1rec IN C1
   LOOP
      FOR c2rec IN c2(c1rec.deptno)
      LOOP
         NULL;
      END LOOP;
   END LOOP;
END;
/

In der Realität steht natürlich komplexe Logic statt null in der Scheife. Ich arbeitete gemeinsam mit einem Entwickler des Softwareherstellers an dem Problem. Wir hatten nicht genug Zeit um den Code zu verstehen. Wie beschlossen, den Code nur ganz formal um zu wandeln und die Schleifen zu belassen. Wir wollten nul also alle Daten in einem einzigen Schritt in ein geschachteltes Array lesen.
Also, alle Departments enthalten alle Employees. Wie in unserem Beispiel war auch in der Realität die Resultatsmenge pro Abfrage so klein, dass Alles locker in ein Array passte.

Wie sieht der zugehörige Select aus?

 

Ist die Migrationsdatenbank langsamer?

Für eine Mirgation wurde eine Datenbank in einer virtuellen Umgebung bereitgestellt.  Erste Tests zeigen, dass die Migrationsdatenbank ein Vielfaches langsamer ist als die Produktionsdatenbank. Die Tests konzentrieren sich auf eine bestimmte Abfrage.

Hier ein Ausschnitt aus einem AWR der Migrationsdatenbank. Zu sehen sind die relevanten Daten des Sql Befehls, den man untersuchen soll als Ausschnitt aus der Liste „SQL ordered by elapsed time“.

 

apsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

199.24

1

199.24

98.16

3.93

96.72

Zum Vergleich die selben Daten aus der produktiven Datenbank:

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

11.02

1

11.02

65.95

99.98

0.0

Was fällt Ihnen auf? Mit welcher Arbeitshypothese würden Sie die Untersuchung beginnen und was würden Sie prüfen? Hinweis: Der Befehl ist ein count welcher nur eine Tabelle liest. Der Execution plan ist in beiden Fällen identisch, es ist jeweils ein Full Table Scan.

Warum ist die neue Hardware langsamer?

Man kauft neue Hardware um schneller zu werden. Das ist eine ganz normale Erwartung. Was aber wenn die neue Hardware langsamer ist als die alte? Die Spekulationen über die Ursache gingen wild hin und her. Da ich auf diesen Einsatz urlaubsbedingt lange warten musste, war die Spannung gross als die Untersuchung endlich beginnen konnte.

Eine schneller Überprüfung zeigte, dass die neue Hardware nicht langsamer war als die alte. Den entscheidenden Hinweis lieferte ein raw trace. Ich zeige hier nur ein entscheidenden Ausschnitt.

Auf der alten Hardware sah der trace so aus:


FETCH #25:c=1154407,e=1152124,p=0,cr=102603,cu=0,mis=0,r=101,dep=0,og=1,tim=650755949521

Cursor #25 ist ein grosses Select, das langsam läuft. Auf der neuen Hardware hingegen sah der trace wie folgt aus:


FETCH #601010888:c=31200,e=22483,p=0,cr=3706,cu=50,mis=0,r=1,dep=0,og=1,plh=3621104505,tim=39783214696
WAIT #601010888: nam='SQL*Net message from client' ela= 171 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217398
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=39783217416
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217423
WAIT #0: nam='SQL*Net message from client' ela= 117 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217546
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=39783217560
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=39783217567

Cursor #601010888: entspricht Cursor #25 auf der alten Hardware. Die Datenbank auf der neuen Hardware ist Version 11, die Datenbank auf der alten Hardware ist Version 10.
Offensichtlich besteht ausser bei der Version mindestens ein weiterer Unterschied zwischen beiden Datenbanken. Was ist es? Wie wirkt sich dieser Unterschied aus?
Beide Datenbanken werden über das exakt selbe Programm angesprochen, welche mit MS Visual Studio realisiert ist.

 

Lösung: Warum ist kein Alias nötig?

Nun, beantworten wir zunächst die Zusatzfrage. Es scheint, dass in sql die Typen nicht direkt genutzt werden können.
Wie Martin Berx im Kommentar zum Englischen Blog richtig bemerkt, muss man zuerst auf den Typ casten.
In diesem Fall:

TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'

Dies it ein nützlicher kleiner Trick, den man immer wieder einmal brauchen kann.
Jetzt kann man das Statement ausführen. Mein Vermutung war, dass der natural Join dafür sorgt, dass man keinen Alias genötigt.
In der Tat resultiert

SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders o USING (customer_id)
JOIN order_items oi ON (oi.order_id=o.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY order_id
)
GROUP BY order_id

in:

FEHLER in Zeile 10:
ORA-00918: Spalte nicht eindeutig definiert

Erst wenn man einen Prefix für Order id im group by definiert geht es:


SELECT order_id,
SUM(quantity*unit_price)
FROM order_items
HAVING SUM(quantity *unit_price) > ALL
(SELECT SUM(quantity*unit_price)
FROM customers
JOIN orders USING (customer_id)
JOIN order_items ON (order_items.order_id=orders.order_id )
WHERE TREAT(CUST_ADDRESS AS CUST_ADDRESS_TYP).STATE_PROVINCE = 'FL'
GROUP BY o.order_id
)
GROUP BY order_id

Meiner Meinung nach sollte es eigentlich immer so funktionieren wie im Fall des natural join. Da hat man schon definiert, dass die Felder gleich sind, soll sich der Optimizer doch eines aussuchen.