Camaret

ein wenig Abwechslung

cost

Damit ich's hier nachlesen kann (und nicht bei Jonathan Lewis nachschlagen muss):

Kosten sind für den CBO definiert als:

Cost = (anzahl_einzelblockzugriffe * sreadtime (= single block read time)
+ anzahl_multiblockzugriffe * mreadtime (= multi block read time)
+ CPUCycles / cpuspeed)
/ sreadtime

Was bedeutet: (Laufzeit Einzelblockzugriffe + Laufzeit Multiblockzugriffe + CPU Time)/Einzelblockzugriff
Also: Laufzeit in Einheiten von single block read time

Dabei erfolgt keine Berücksichtigung von direct reads (für merge join, hash join, sorts).

connect by level

noch ein interessanter Hinweis von Tanel Poder: die Zeilengenerierung über connect by kostet für große Satzmengen sehr viel Arbeitsspeicher (unter anderem, weil die Ressourcenlimits - PGA_AGGREGATE_TARGET - für diesen Fall nicht greifen). Alternativ kann man aber connect by + cartesian join verwenden.

Performance Tools Reference (Metalink)

Tanel Poder hat vor kurzem auf das Metalink-Dokument "438452.1 - Performance Tools Quick Reference Guide" verwiesen, das eine ganze Reihe interessanter Hilfsmittel anspricht. Ich kannte kaum die Hälfte davon (dem Namen nach ...)

Hübsch ist z.B. SQLTXPLAIN, das einen HTML-Report für ein gegebenes Statement liefert.

Update eines Joins

Mal wieder ein Syntaxbeispiel, diesmal zum Thema "Update eines Joins". Interessant ist diese Variante z.B. dann, wenn ein MERGE nicht in Frage kommt, weil eine Spalte, über die gejoint wird, geändert werden soll (was beim MERGE nicht möglich ist):

SQL> merge into emp e
2  using (select * from dept) d
3  on (e.deptno = d.deptno)
4  when matched then update set e.deptno = d.deptno;
on (e.deptno = d.deptno)
*
FEHLER in Zeile 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "E"."DEPTNO"

SQL> r
1  update
2  (select e.deptno emp_no, d.deptno dept_no
3     from emp e, dept d
4    where e.deptno = d.deptno)
5* set emp_no = dept_no

14 Zeilen wurden aktualisiert.

Wobei das Beispiel in diesem Fall zugegebenermaßen nicht allzu sinnvoll ist ...

Korrelationseffekte

Die Erfahrung lehrt, dass die konsequente Verwendung von Aliasen bei der Durchführung eines Joins eine gute Idee ist. Dass man dem cbo damit die Arbeit erleichtert, ist dabei ein netter Nebeneffekt, aber das Hauptproblem mit fehlenden Aliasen sind die unübersichtlichen Korrelationseffekte, die sich aus ihnen ergeben:

1. Anlage einer Hilfsview, die für jede empno aus emp die Summe aus sal und komm liefert (das ist inhaltlich natürlich nicht besonders aufregend – und keine View wert)

create or replace view sal_complete
as
select empno
     , ename
     , sal + nvl(comm, 0) sal
  from emp;

2. dazu formulieren wir eine Query, die uns die Angabe aus sal_complete über eine skalare Subquery liefert

select empno
     , ename
     , sal
     , comm
     , (select sal
          from sal_complete s
         where s.empno = t.empno) sal_comm
  from emp t;

EMPNO ENAME             SAL       COMM   SAL_COMM
---------- ---------- ---------- ---------- ----------
7369 SMITH             800                   800
7499 ALLEN            1600        300       1900 --> ok
7521 WARD             1250        500       1750 --> ok
7566 JONES            2975                  2975
7654 MARTIN           1250       1400       2650 --> ok
7698 BLAKE            2850                  2850
7782 CLARK            2450                  2450
7788 SCOTT            3000                  3000
7839 KING             5000                  5000
7844 TURNER           1500          0       1500 --> ok
7876 ADAMS            1100                  1100
7900 JAMES             950                   950
7902 FORD             3000                  3000
7934 MILLER           1300                  1300

14 Zeilen ausgewählt.
-- funktioniert also tadellos

3. anschließend fällt jemandem auf, dass es doch viel hübscher wäre, wenn die Spalte der View einen aussagekräftigeren Namen als "sal" bekäme.

create or replace view sal_complete
as
select empno
     , ename
     , sal + nvl(comm, 0) sal_comm
  from emp;

4. unsere Query läuft noch immer, liefert aber leider nicht mehr das gewünschte Ergebnis

select empno
     , ename
     , sal
     , comm
     , (select sal
          from sal_complete s
         where s.empno = t.empno) sal_comm
  from emp t;

EMPNO ENAME             SAL       COMM   SAL_COMM
---------- ---------- ---------- ---------- ----------
7369 SMITH             800                   800
7499 ALLEN            1600        300       1600 --> ???
7521 WARD             1250        500       1250 --> ???
7566 JONES            2975                  2975
7654 MARTIN           1250       1400       1250 --> ???
7698 BLAKE            2850                  2850
7782 CLARK            2450                  2450
7788 SCOTT            3000                  3000
7839 KING             5000                  5000
7844 TURNER           1500          0       1500 --> ???
7876 ADAMS            1100                  1100
7900 JAMES             950                   950
7902 FORD             3000                  3000
7934 MILLER           1300                  1300

14 Zeilen ausgewählt.

5. Nach der Umbenennung von "sal" in "sal_comm" betrifft die Korrelation nur noch die Bedingung der skalaren Subquery während der Ergebniswert aus der äußeren Query stammt – denn nur dort gibt es eine Spalte "sal". Mit einem passenden Alias bekommt man stattdessen eine plausible Fehlermeldung:

select empno
     , ename
     , sal
     , comm
     , (select s.sal
          from sal_complete s
         where s.empno = t.empno) sal_comm
  from emp t
(select s.sal
*
FEHLER in Zeile 2:
ORA-00904: "S"."SAL": invalid identifier

Nachtrag 21.02.2016: Fast acht Jahre später hat mir Martin Berger jetzt die eigentliche Erklärung für das Verhalten geliefert: Oracle wandelt das IN in ein EXISTS um, bei dem die beiden fraglichen Spalten miteinander verglichen werden - wie ein CBO Trace (Event 10053) zeigt.

Parameter-Views

Tanel Poder hat schon vor einiger Zeit in seinem Blog über die Tücken der diversen Parameter-Views des data dictionary geschrieben. Hier noch mal die Definitionen der fraglichen Views:

• V$PARAMETER - die Parameter auf Session-Ebene
• V$PARAMETER2 - ebenfalls die Parameter auf Session-Ebene; Listen-Einträge werden auf mehrere Sätze verteilt
• V$SYSTEM_PARAMETER - die Parameter auf System-Ebene
• V$SYSTEM_PARAMETER2 - ebenfalls die Parameter auf System-Ebene; Listen-Einträge werden auf mehrere Sätze verteilt

Zusätzlich gibt's dann noch V$SPPARAMETER (die Inhalte des spfiles), V$OBSOLETE_PARAMETER (nun ja: obsolete Parameter) und V$PARAMETER_VALID_VALUES ("displays a list of valid values for list parameters")

Details zu dbms_xplan

Im Blog der cbo-Entwickler gibt’s ein paar interessante Informationen zu den Optionen, die dbms_xplan bietet. Besonders die Formatierungsoptionen (die in RDBMS\ADMIN\dbmsxpln.sql erscheinen) sehen interessant aus.