manuelle Einstellung der sort_area_size

bei Jonathan Lewis gibt's mal wieder einen wichtigen Hinweis: in 10.2.0.4 und diversen 11er Versionen gibt es einen Bug, der dafür sorgt, dass ein ALTER SESSION zur Abschaltung der automatischen PGA-Verteilung nicht unmittelbar wirksam wird: die Details dazu gibt es hier. In einem Kommentar zum Eintrag wird darauf hingewiesen, dass dieses Verhalten schon in 10.2.0.3 vorliegen soll. Das sollte ich mir bei Gelegenheit mit einem 10032er Trace anschauen.

INSERT ALL

kein ganz neues Feature aber sehr praktisch ist das INSERT ALL-Statement, mit dem man z.B. den Inhalt einer großen Tabelle auf mehrere kleine Tabellen verteilen kann. Das Vorgehen dabei sieht etwa folgendermaßen aus:

SQL> select * from emp;

EMPNO ENAME      JOB              MGR HIREDATE          SAL 
---------- ---------- --------- ---------- ---------- ---------- 
7369 SMITH      CLERK           7902 17.12.1980        800 
7499 ALLEN      SALESMAN        7698 20.02.1981       1600 
7521 WARD       SALESMAN        7698 22.02.1981       1250 
7566 JONES      MANAGER         7839 02.04.1981       2975 
7654 MARTIN     SALESMAN        7698 28.09.1981       1250 
7698 BLAKE      MANAGER         7839 01.05.1981       2850 
7782 CLARK      MANAGER         7839 09.06.1981       2450 
7788 SCOTT      ANALYST         7566 19.04.1987       3000 
7839 KING       PRESIDENT            17.11.1981       5000 
7844 TURNER     SALESMAN        7698 08.09.1981       1500 
7876 ADAMS      CLERK           7788 23.05.1987       1100 
7900 JAMES      CLERK           7698 03.12.1981        950 
7902 FORD       ANALYST         7566 03.12.1981       3000 
7934 MILLER     CLERK           7782 23.01.1982       1300 

14 Zeilen ausgewõhlt.

SQL> create table emp_old
2  as
3  select * from emp where 1 = 0;

Tabelle wurde erstellt.

SQL> create table emp_new
2  as
3  select * from emp where 1 = 0

Tabelle wurde erstellt.

SQL> insert all
2  when HIREDATE <= to_date('31.12.1981')
3  then into emp_old 
values(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL)
4  when HIREDATE > to_date('31.12.1981')
5  then into emp_new 
values(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL)
6  select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
7    from emp;

14 Zeilen wurden erstellt.

SQL> select * from emp_old;

EMPNO ENAME      JOB              MGR HIREDATE          SAL 
---------- ---------- --------- ---------- ---------- ---------- 
7369 SMITH      CLERK           7902 17.12.1980        800 
7499 ALLEN      SALESMAN        7698 20.02.1981       1600 
7521 WARD       SALESMAN        7698 22.02.1981       1250 
7566 JONES      MANAGER         7839 02.04.1981       2975 
7654 MARTIN     SALESMAN        7698 28.09.1981       1250 
7698 BLAKE      MANAGER         7839 01.05.1981       2850 
7782 CLARK      MANAGER         7839 09.06.1981       2450 
7839 KING       PRESIDENT            17.11.1981       5000 
7844 TURNER     SALESMAN        7698 08.09.1981       1500 
7900 JAMES      CLERK           7698 03.12.1981        950 
7902 FORD       ANALYST         7566 03.12.1981       3000 

11 Zeilen ausgewõhlt.

SQL> select * from emp_new;

EMPNO ENAME      JOB              MGR HIREDATE          SAL 
---------- ---------- --------- ---------- ---------- ---------- 
7788 SCOTT      ANALYST         7566 19.04.1987       3000 
7876 ADAMS      CLERK           7788 23.05.1987       1100 
7934 MILLER     CLERK           7782 23.01.1982       1300 

Nützlich an diesem Feature ist vor allem, dass hier nur ein FTS erforderlich ist, während das Füllen der Zieltabellen über mehrere Statements natürlich mehrere Zugriffe erfordern würde.

Bitmap Indizes

dunkel erinnere ich mich daran, dass es - seltene - Fälle gibt, in denen ein Bitmap-Index über mehrere Spalten sinnvoll sein kann, obwohl die Grundidee dieses Indextyps ja eher in der Kombination von Einzelindizes liegt. Was aber bei der Kombination mehrerer Spalten im Bitmap-Index berücksichtigt werden muss, ist, dass zwei zentrale Vorteile der Bitmap-Indizes bei Verwendung mehrerer Spalten verloren gehen: der Index ist dann nicht mehr klein und der Aufbau wird deutlich verlängert:

• Fakten-Tabelle mit einer Größe von 18 GB.
• der Aufbau von Bitmap-Indizes auf Einzelspalten benötigt jeweils ca. 25 min und die Größe der Indizes liegt bei < 2 GB.
• der Aufbau eines kombinierten Index über 4 Spalten dauerte fast 5 h und die Größe des Index erreichte fast 60 GB (er ist also wahrscheinlich sogar größer als ein entsprechender B*Tree-Index).

In diesem Fall zumindest ist ein Bitmap-Index über die Spaltenkombination offenbar komplett nutzlos ...

Nachtrag 23.04.2011: der hier beschriebene Effekt ergibt sich aus der Clusterung der Daten in der Tabelle, wie ich später herausgefunden habe: http://martinpreiss.blogspot.com/search/label/Bitmap%20Index

BI Methodology

Marco Russo und Alberto Ferrari haben eine Serie zum Thema BI Methodology aus Sicht von Microsoft gestartet, die recht vielversprechend begonnen hat. Die Artikel finden sich hier.

Rollup, Cube, Grouping Sets

Mark Rittman erklärt in seinem Blog, wie man die genannten Oracle-SQL Group-By-Extensionen sinnvoll verwenden kann. Wie man's von Herrn Rittman kennt, ist das wieder mal ein ausgesprochen erhellender Beitrag. Vielleicht verstehe ich die Klauseln aber auch deshalb inzwischen besser, weil ich ziemlich regelmäßig Situationen antreffe, wo man sie einsetzen könnte.

SQL access advisor in 11g

Mark Rittmans Blog gehört zu den besten mir bekannten Ressourcen in Sachen BI. Eine hübsche Einführung in die Verwendung des SQL access advisor in 11g findet man hier.

DB_FILE_MULTIBLOCK_READ_COUNT

Im Structured Data Blog von Greg Rahn findet sich eine hübsche Zusammenfassung zu den Eigenschaften des Parameters DB_FILE_MULTIBLOCK_READ_COUNT in 10.2. Da das Thema regelmäßig Unklarheiten hervorruft (jedenfalls bei mir), ist dieser Link mal wieder vor allem als Erinnerung gedacht.

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.

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.