Autor: lotharflatz

Lösung: Unpivot mit Cross Apply

Die cross apply erzeugt so etwas wie ein cartesisches Produkt. Zudem wird die Regel aufgehoben, dass alle Tabellen in der FROM Klausel gleichzeit sind.

Es wird eine Reihenfolge definert und es ist eine Referenz innerhaln der FROM Klausel möglich.

Das Statement unten kombiniert jeden Datensatz der Basistabelle emp mit allen Sätzen der brechneten Tabelle CrossApplied. Das Verblüffende ist, CrossApplied sich auch auf Werte in emp beziehen kann.

Der execution Plan dazu ist unten aufgeführt. Man sieht die Verwandtschaft von CROSS APPLY zur ebenfalls neuen Klause LATERAL.

Tatsächlich war diese Variante im einem Test mit 14 Millionen Sätzen etwas schneller als die Lösung mit UNPIVOT Klausel.

SELECT  empno,
        CrossApplied.Col_name, CrossApplied.Col_value
  FROM emp t
 CROSS APPLY (Select 'Mgr' col_name, t.Mgr col_value from dual
              UNION ALL
              Select 'job', t.job  from dual
              UNION ALL
              Select 'sal', t.sal  from dual) CrossApplied
;


 --------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |       |       |    87 (100)|          |
|   1 |  NESTED LOOPS      |                 |    42 |  1386 |    87   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP             |    14 |   210 |     3   (0)| 00:00:01 |
|   3 |   VIEW             | VW_LAT_C2E3294A |     3 |    54 |     6   (0)| 00:00:01 |
|   4 |    UNION-ALL       |                 |       |       |            |          |
|   5 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Advertisements

Unpivot mit CROSS_APPLY

Manchmal wird in einer Verarbeitungslogik ein unpivot benötigt. Beispielsweise wenn eine generische Schnittstelle befüllt werden soll. Üblicherweise enthält eine solche Schnittstellentabelle Schlüsselfelder, einen Spaltennamen und einen Spaltenwert.

Ich bin kein Freund solcher Schnittstellentabellen, aber man hat nicht immer die Wahl. 😉 Bei der Entwicklung einer zeitkritischen Verarbeitung war das Standard Unpivot mir zu langsam und ich suchte Alternativen.

Dabei stiess ich auf die relative neue cross_apply klausel und fand es noch interessant sie näher kennen zu lernen. Das unpivot Beispiel eignet sich dafür noch recht gut.

Diese Aufgabe will ich ihnen daher nicht vorenthalten.

Also: Wenn das meine Unpivot query ist, wie kann man mit cross_apply zum selben Resultat kommen?


SELECT  empno, column_name, value
FROM emp t
UNPIVOT ( VALUE FOR COLUMN_NAME IN ( Mgr, deptno, sal))
/

Auswerten von historischen SQL Monitor Berichten

Ich bin schon bis tief in die Nacht am Bildschirm gesessen und habe zu gesehen wie ein Batch Job ablief. Dabei habe ich in regelmässigen Abstand SQL Monitor Berichte weg gespeichert, die wir später kategorisiert und bearbeitet haben.
Seit Oracle 12 gibt es auch den historischen SQL Monitor.
Ich habe ein kleines Skript geschrieben, mit dem man einen Batchlauf im nachhinein auswerten kann.
Es ist in meinem Augen das nützlichste Script, dass ich je geschrieben habe.
Es hat keine Parameter, ich editiere meine Skripts lieber.
Das Skript ist natürlich noch verbesserungsfähig.
Vielleicht möchte der eine oder andere eine bessere Variante veröffentlichen?

Und, nebenbei bemerkt, ist es für Sql*Plus geschrieben…


set newpage 0
set space 0
SET LONG 10000000 LONGCHUNKSIZE 10000000 pages 0 lines 500
set echo off
set feedback off
set verify off
set heading off
set trimspool on
set sqlp ''
spool temp.sql
WITH reps as (
	SELECT REPORT_ID,
	  KEY1 SQL_ID,
	  KEY2 SQL_EXEC_ID,
	  KEY3 SQL_EXEC_START,
	  T.PERIOD_START_TIME,
	  T.PERIOD_END_TIME,
	  TRUNC(86400*(T.PERIOD_END_TIME-T.PERIOD_START_TIME)) DURATION,
	  DECODE(TO_NUMBER(EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/plsql_entry_object_id')), 
				NULL, EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module'),
			   (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID = TO_NUMBER(EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/plsql_entry_object_id')))) CALLED_FROM ,
	  EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module') module
	FROM DBA_HIST_REPORTS t,
	  dba_hist_sqltext st
	WHERE T.PERIOD_START_TIME BETWEEN TO_DATE('01.07.2017 01:00:00','DD.MM.YYYY HH24:MI:SS') AND TO_DATE('03.07.2017 23:59:00','DD.MM.YYYY HH24:MI:SS')
	  AND 86400*(T.PERIOD_END_TIME-T.PERIOD_START_TIME) >= 1000 -- abitrary treshold, longrunning statement
	  AND ST.SQL_ID                                      = T.KEY1
	  AND COMMAND_TYPE                                  <> 47 -- no PL/SQL 
	  and EXTRACTVALUE(XMLTYPE(REPORT_SUMMARY), '/report_repository_summary/sql/module')!='DBMS_SCHEDULER'), -- No gather stats
commands as (
	SELECT REPORT_ID, 1 line, 'spool sqlmon_'||SQL_ID||'_'||CALLED_FROM||'_'||REPORT_ID||'.html'  command FROM REPS
	UNION ALL
	SELECT REPORT_ID,2, 'SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => '|| REPORT_ID||', TYPE => ''EM'')       FROM dual;' FROM REPS
	UNION ALL
	SELECT REPORT_ID,3, 'SPOOL OFF' FROM REPS
)
SELECT COMMAND
  from commands
order by REPORT_ID,  line;
spool off
@@temp
set echo on
set feedback on
set verify on

 

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.

Lösung: Eine unerwartete Bedingung in der Where Clausel

Die merkwürdige Bedingung wird von der Datenbank automatisch generiert.
Die Ursache ist die DDL Optimization, die es in der Form seit Version 11G gibt.
Wenn man bei einer Tabelle eine zusätzliche Spalte einfügt, muss diese Spalte nicht zwingend physisch erzeugt werden.
Es kann auch eine “DDL optimized” Spalte erzeugt werden und wenn man einen Default angibt, kann diese Spalte auch not null sein.
Damit erspart die Datenbank sich den Aufwand, jeden Datensatz um eine Spalte zu erweitern.
Satt dessen wird nur ein Eintrag ins Dictionary gemacht, was natürlich viel schneller geht.
Jede Datenzeile kann einen Wert für die “DDL optimized” Spalte enthalten, wenn der Wert über einen insert eingefügt wurde.
Wenn kein Wert eingefügt wird, wird der Default Wert verwendet.
Da es möglich ist, dass kein erfasster Wert existiert, muss die Datenbank den spaltennamen durch die Formel ersetzen.
Hier eine einfaches Beispiel :

create table x (y number);
insert into x select rownum from dual connect by rownum < 1000000;
commit;
alter table x add ( z number default 1 not null);
select 1 from x where z=1;

Wenn wir uns den Execution Plan der Abfrage ansehen, bemerken wir, dass der Spaltenname Z durch die Formel


(NVL("Z",1)=1)

ersetzt wurde.

Hier noch der Link zu Carlos Blog: Interesting case where a full table scan is chosen instead of an index

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 parallele Aktivität bricht ein

Wie Sie sich erinnern werden, hatten wir einen Hash Outer Join mit dominanten Nullwerten. In Folge bekam der parallelen Prozess, der die Nullwerten behandelte, bei weitem die meiste Arbeit zu tun.
Wie sollen wir dies lösen? lassen Sie uns einige grundsätzliche Überlegungen anstellen.
Ein Null Wert ist ein Sonderfall . Wir vergleichen einen Null Wert im Foreign Key mit einem Primärschlüssel , der not null definiert ist. Wir können sicherlich keinen Join Treffer für die Null Werte zu finden. Daher kann jeder der parallelen Sklaven den Vergleich durchführen, solange gewährleistet ist, dass dadurch nie ein Treffer erzeugt wird.
Es lohnt sich also die Null Werte in beliebige andere Werte um zu wandeln, solange nur zwei Voraussetzungen erfüllt sind:

  1. wir müssen die Nullwerte in einer solchen Art und Weise umwandeln, dass die gleichmäßig über alle parallel Slaves verteilt werden
  2. wir müssen sicherstellen, dass im Vergleich zu vorher kein zusätzlicher Satz ins Resultat kommt

Die Lösung zu finden hat mich viel Zeit gekostet. Zuerst habe ich versucht mit negativen Werten zum Ziel zu kommen. Aber auch sie wurden auch an nur einen Parallel Prozess gesandt, genau wie die Null Werte.
Das Problem ist, dass unsere neu generierten Schlüssel etwa im gleichen Bereich wie der Primary Key der Build-Tabelle sein müssen, um über alle Slaves verteilt zu werden.
Ich hätte versuchen können über eine with Klausel den Bereich heraus zu finden, aber das hielt ich für uncool und konnte mich nicht dazu überwinden.
Stattdessen habe ich etwas anderes versucht, was ehrlich gesagt ein bisschen wie ein Hack wirkt, aber viel besser aussieht. Ich benutzte den Primary Key der äußeren Tabelle einen nicht null wertigen breit streuenden Schlüssel zu generieren.
Ich habe auch die Tatsache zunutze, dass die IDs integer waren und veränderte die Join-Bedingung zu:

FROM T_TAB1 T1
LEFT OUTER JOIN T_TAB2 T2
ON ( NVL(T2.X_ID, T2_id +0.5)    = T1.X_ID)

Das ist sicherlich ein bischen getrickst. Das einzig Positive daran ist, dass es funktioniert.
Die Aktivity Tab im Sql Monitor sah nachher so aus:

Vergleichen Sie dies mit dem vorherigen Bild. Es ist ziemlich beeindruckend.
Nun, in der Version 12 die Datenbank sollte in der Lage sein, mit schräg verteilten join Schlüsseln um zu gehen.
Aber es funktioniert nicht in jedem Fall, wie Randolf Geist feststellte.
Wenn jemand meine Lösung nicht gut findet, sollte er vielleicht er einen Blick auf_fix_control 6808773 werfen, aber ich gebe für keinen Fall Garantien ab. 😉