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.

logon trigger

keine neue Erkenntnis, sondern ein Syntaxbeispiel:

create or replace trigger scott_logon_trigger
after logon on database
begin
if user = 'SCOTT' then
execute immediate 'alter session set WORKAREA_SIZE_POLICY=Manual';
execute immediate 'alter session set sort_area_size = 100000000';
execute immediate 'alter session set hash_area_size = 100000000';
end if;
end;
/

In 10g erfolgt das PGA-Management per default automatisch. Eine Umstellung der WORKAREA_SIZE_POLICY und explizite Setzung der %_area_size-Parameter kann für kostspielige Operationen interessant sein, da die automatische Zuweisung des PGA-Memories bestimmte (prozentual bestimmte) Obergrenzen nicht überschreitet.

Ein logon trigger ist vor allem dann interessant, wenn ein Parameter für Sessions einer Applikation gesetzt werden soll, die diese Setzung selbst nicht vornehmen können.

Outer Join Syntax

Die Leute, die den Oracle Optimizer entwickeln, haben unter http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html die Unterschiede zwischen der ANSI-Syntax für Outer Join Operationen und Oracles (+)-Operator erläutert. Ausgesprochen erhellend.

sys_connect_by_path

da ich die Syntax dieser hübschen Möglichkeit, die übergeordneten Elemente in einer Hierarchie auf einfache Weise darzustellen, regelmäßig vergesse, hier ein Beispiel für die Verwendung der sys_connect_by_path-Funktion:

SQL> r
1  select empno,
2         ename,
3         job,
4         sys_connect_by_path(job, '/') job_hierarchy
5    from emp
6  connect by prior empno = mgr
7   start with mgr is null
8*

EMPNO ENAME      JOB       JOB_HIERARCHY
---------- ---------- --------- ---------------------------------
7839 KING       PRESIDENT /PRESIDENT
7566 JONES      MANAGER   /PRESIDENT/MANAGER
7788 SCOTT      ANALYST   /PRESIDENT/MANAGER/ANALYST
7876 ADAMS      CLERK     /PRESIDENT/MANAGER/ANALYST/CLERK
7902 FORD       ANALYST   /PRESIDENT/MANAGER/ANALYST
7369 SMITH      CLERK     /PRESIDENT/MANAGER/ANALYST/CLERK
7698 BLAKE      MANAGER   /PRESIDENT/MANAGER
7499 ALLEN      SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7521 WARD       SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7654 MARTIN     SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7844 TURNER     SALESMAN  /PRESIDENT/MANAGER/SALESMAN
7900 JAMES      CLERK     /PRESIDENT/MANAGER/CLERK
7782 CLARK      MANAGER   /PRESIDENT/MANAGER
7934 MILLER     CLERK     /PRESIDENT/MANAGER/CLERK

14 Zeilen ausgewählt.

Funktioniert seit 9i.

Redo-Generierung für Indizes

Eigentlich hatte ich angenommen, die Regeln der nologging-clause einigermaßen genau zu kennen. Das war aber wohl ein Irrtum. Zu meiner Überraschung ist es nämlich offenbar nicht möglich, die redo-Generierung für Indizes bei direct-path-Inserts zu vermeiden. Hier noch mal die Details zum Mitschreiben:

• im NOARCHIVELOG-Modus erzeugt ein INSERT /*+ append */ nur minimale redo Informationen
• allerdings werden redo-Informationen für zugehörige Indizes erzeugt
• dies geschieht auch dann, wenn der Index in den nologging Modus gesetzt wurde
• nologging spielt für Indizes nur bei rebuild-Operationen eine Rolle

Damit diese Aussagen nicht reine Behauptungen bleiben, hier die zugehörigen Tests:

create table test as select * from dba_objects where 1 = 0;

Tabelle wurde erstellt.

insert into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 5982892  redo size

-- nach rollback:
insert /*+ append */ into test select * from dba_objects;

54496 Zeilen wurden erstellt.

--> 2328  redo size

--> dabei spielt das logging-Attribut der Tabelle im vorliegenden NOARCHIVELOG-Modus keine Rolle

-- nach rollback:
create index test_idx1 on test(object_name);

Index wurde erstellt.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

alter index test_idx1 nologging;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 3799568  redo size

--> der nologging-Modus des Index spielt im Fall des INSERTs keine Rolle
--> Änderungen am Index werden immer geloggt
--> für größere Änderungsoperationen ist demnach ein kompletter Neuaufbau des Index vorzuziehen

-- nach rollback:
alter index test_idx1 unusable;

Index wurde geändert.

insert /*+ append */ into test select * from dba_objects;

54497 Zeilen wurden erstellt.

--> 1888  redo size

alter index test_idx1 rebuild;

Index wurde geändert.

--> 60180 redo size

--> minimale redo Generierung für das rebuild eines Index im nologging-Modus

Advanced Query Rewrite

während alle ernsthaften Blogger sich mit Features aus Version 11 beschäftigen, entdecke ich immer noch hübsche Dinge in 10g. So z.B. das Package DBMS_ADVANCED_REWRITE, das es ermöglicht, eine abgesetzte Query intern durch ein anderes Statement zu ersetzen. Einerseits kann man damit natürlich ungeheuren Blödsinn anstellen, andererseits eröffnet dieses Package ziemlich umfangreiche Möglichkeiten für die Definition einer intelligenten Rewrite-Logik im DataWarehouse-Bereich. Hier ein ganz simples Syntax-Beispiel:

-- als sys:
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO kettle;

Benutzerzugriff (Grant) wurde erteilt.

-- als kettle:
SQL> create table test_rewrite(a varchar2(64));

Tabelle wurde erstellt.

SQL> insert into test_rewrite(a)
2 values ('aus der Tabelle test_rewrite');

1 Zeile wurde erstellt.

SQL> commit;

SQL> begin
2    SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence
3        (name             => 'test_rewrite',
4         source_stmt      => 'SELECT * FROM test_rewrite',
5         destination_stmt => 'SELECT ''aus dual'' from dual',
6         validate         => FALSE);
7    end;
8    /

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SELECT * FROM test_rewrite;

A
------------------------------------
aus der Tabelle test_rewrite

-- das System muss erst davon überzeugt werden, dass unser Rewrite vertrauenswürdig ist
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

Session wurde geändert.

SQL> SELECT * FROM test_rewrite;

A
--------
aus dual

SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence 
2         (name => 'test_rewrite');

PL/SQL-Prozedur erfolgreich abgeschlossen.