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.

V$SES_OPTIMIZER_ENV

In 10g kann man mit Hilfe der View V$SES_OPTIMIZER_ENV die für die einzelnen Sessions gesetzten Parameter ermitteln, die den cbo betreffen:

-- für eine Session wurden die PGA-Settings verändert
SQL> r
1  select *
2    from V$SES_OPTIMIZER_ENV
3*  where name = 'sort_area_size'

SID         ID NAME                           ISD VALUE
----------  -------------------------------   --- ---------
 85         16 sort_area_size                 YES 65536
 86         16 sort_area_size                 YES 65536
 91         16 sort_area_size                 YES 65536
 95         16 sort_area_size                 NO  200000000
101         16 sort_area_size                 YES 65536
107         16 sort_area_size                 YES 65536
122         16 sort_area_size                 YES 65536
125         16 sort_area_size                 YES 65536
127         16 sort_area_size                 YES 65536

SQL> r
1  select *
2    from V$SES_OPTIMIZER_ENV
3*  where sid = 95

SID         ID NAME                          ISD VALUE
---------- ------------------------------    --- ----------
95          2 parallel_execution_enabled     NO  false
95          9 optimizer_features_enable      YES 10.2.0.3
95         11 cpu_count                      YES 4
95         12 active_instance_count          YES 1
95         13 parallel_threads_per_cpu       YES 2
95         14 hash_area_size                 NO  200000000
95         15 bitmap_merge_area_size         YES 1048576
95         16 sort_area_size                 NO  200000000
95         17 sort_area_retained_size        YES 0
95         24 pga_aggregate_target           YES 786432 KB
95         35 parallel_query_mode            YES enabled
95         36 parallel_dml_mode              YES disabled
95         37 parallel_ddl_mode              YES enabled
95         38 optimizer_mode                 YES all_rows
95         48 cursor_sharing                 YES exact
95         50 star_transformation_enabled    YES false
95         66 optimizer_index_cost_adj       YES 100
95         67 optimizer_index_caching        YES 0
95         70 query_rewrite_enabled          YES true
95         71 query_rewrite_integrity        YES enforced
95        101 workarea_size_policy           NO  manual
95        102 _smm_auto_cost_enabled         NO  false
95        105 optimizer_dynamic_sampling     YES 2
95        112 statistics_level               YES typical
95        114 skip_unusable_indexes          YES true
95        165 optimizer_secure_view_merging  YES true

26 Zeilen ausgewählt.

Histogrammerstellung in 10g

Wie man in Richard Footes Blog nachlesen kann, hat sich in 10g das default-Verhalten für dbms_stats geändert: statt der bisherigen method_opt 'FOR ALL COLUMNS SIZE 1' wird nun die Variante 'FOR ALL COLUMNS SIZE AUTO' verwendet; Oracle entscheidet in Abhängigkeit von Datenverteilung und "workload" (werden die fraglichen Spalten in SQL-Statements referenziert?), ob eine Anlage erforderlich ist. Bei Richard Foote finden sich noch diverse Erläuterungen dazu, wann dieses Verhalten problematisch sein kann, aber für mich war zunächst einmal wichtig zu wissen, dass Histogramme vorliegen können, ohne dass sie explizit angefordert wurden.