Freitag, März 30, 2012

dbms_scheduler und die Sommerzeit

Dieser Tage ist mir in einem Kundensystem folgendes Phänomen begegnet: mit Beginn der Sommerzeit verschoben sich einige - aber nicht alle - Scheduler-Jobs um genau eine Stunde nach hinten. Ganz offenbar hatte jemand vergessen, die Uhr umzustellen - aber wer? Und warum? Ein Blick in dba_scheduler_jobs zeigte, dass der Unterschied zwischen den Jobs, die die Sommerzeit korrekt behandelt hatten, und jenen, denen das nicht gelungen war, bei den Zeitangaben klar zu erkennen war:
  • die korrekte Zeit lieferten die Jobs mit Zeitzonenangabe, also z.B.: EUROPE/BERLIN
  • die falsche Zeit lieferten die Jobs mit Offset, also +01:00
Was mir durchaus einleuchtet. Der feste Offset kann natürlich nur entweder zur Sommer- oder zur Winterzeit passen. Und wahrscheinlich waren alle Jobs, bei denen sich Verschiebungen ergeben hatten, erst nach dem Ende der letzten Sommerzeit definiert worden (oder niemand hatte die Verschiebung vorher bemerkt).

Aber was führte zur unterschiedlichen Definition der Jobs? Nach einigem Ausprobieren (und Recherchieren) wurde klar, dass das start_date für die Zuordnung der Zeitzone verantwortlich ist. Die Dokumentation (11.2) sagt dazu: "The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval." Dazu ein Beispiel:

exec dbms_scheduler.drop_job (job_name => 'test_mpr_job');

begin
  dbms_scheduler.create_job (
    job_name        => 'test_mpr_job',
    job_type        => 'plsql_block',
    job_action      => 'begin null; end;',
    start_date      => systimestamp,
    repeat_interval => 'freq=hourly; byminute=42',
    enabled         => TRUE,
    comments        => 'Test Scheduler Start_Date.');
end;
/

select systimestamp from dual;

SYSTIMESTAMP
-------------------------------
30.03.12 10:42:15,296000 +02:00

select job_name
     , start_date
  from dba_scheduler_jobs
 where job_name like 'TEST_MPR_JOB';

JOB_NAME     START_DATE
------------ -------------------------------
TEST_MPR_JOB 30.03.12 10:42:15,265000 +02:00

-- abgerufen nach dem Lauf
select log_date
     , req_start_date
     , actual_start_date
  from dba_scheduler_job_run_details
 where job_name = 'TEST_MPR_JOB';

LOG_DATE                          REQ_START_DATE                    ACTUAL_START_DATE
--------------------------------- --------------------------------- -------------------------------
30.03.12 10:42:15,750000 +02:00   30.03.12 10:42:15,300000 +02:00   30.03.12 10:42:15,750000 +02:00

Offenbar wird der systimestamp in diesem Fall als start_date übernommen, wobei die granulareren Zeitangaben - in diesem Fall also die Angaben unterhalb der Minutenangabe - in den Zeitplan eingehen, so dass die Ausführung nicht auf 42:00,000000, sondern auf 42:15,300000 festgelegt wird (REQ_START_DATE). Der tatsächliche Start (ACTUAL_START_DATE) verzögerte sich im gegebenen Fall minimal und entspricht dann auch dem LOG_DATE.

Interessant ist allerdings, was passiert, wenn man auf die start_date-Angabe komplett verzichtet:

begin
  dbms_scheduler.create_job (
    job_name        => 'test_mpr_job',
    job_type        => 'plsql_block',
    job_action      => 'begin null; end;',
    repeat_interval => 'freq=hourly; byminute=42',
    enabled         => TRUE,
    comments        => 'Test Scheduler Start_Date.');
end;
/

select job_name
     , start_date
  from dba_scheduler_jobs
 where job_name like 'TEST_MPR_JOB'
;

JOB_NAME     START_DATE
------------ --------------------------------------
TEST_MPR_JOB 30.03.12 10:55:12,603528 EUROPE/VIENNA

Wien kam dabei etwas unerwartet, aber die Dokumentation hat dazu folgende Erklärung:
When start_date is NULL, the Scheduler determines the time zone for the repeat interval as follows:
1. It checks whether or not the session time zone is a region name. The session time zone can be set by either:
  • Issuing an ALTER SESSION statement, for example: SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
  • Setting the ORA_SDTZ environment variable.
2. If the session time zone is an absolute offset instead of a region name, the Scheduler uses the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.
3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler uses the time zone of systimestamp when the job or window is enabled.

Im gegebenen Fall ist Punkt 2 relevant: die DEFAULT_TIMEZONE für den Scheduler wurde explizit gesetzt:

select dbms_scheduler.stime
  from dual;

STIME
-----------------------------------------
30.03.12 11:00:07,645730000 EUROPE/VIENNA

Wahrscheinlich wurden also die Jobs, die die Sommerzeit korrekt umsetzten, ohne start_date-Angabe angelegt und konnten deshalb den Scheduler-Default verwenden. Alternativ könnte man das start_date auch explizit auf eine Angabe mit Zeitzone setzen:

begin
  dbms_scheduler.create_job (
    job_name        => 'test_mpr_job',
    job_type        => 'plsql_block',
    job_action      => 'begin null; end;',
    start_date      => systimestamp at time zone 'EUROPE/BERLIN',
    repeat_interval => 'freq=hourly; byminute=55',
    enabled         => TRUE,
    comments        => 'Test Scheduler Start_Date.');   
end;
/    

Die einfachste Möglichkeit zur Korrektur der fehlerhaften Angaben war dann eine explizite Änderung der start_date-Angabe mit Hilfe der set_attribute-Prozedur:

begin
    DBMS_SCHEDULER.SET_ATTRIBUTE(
        name => 'test_mpr_job'
      , attribute => 'start_date'
      , value => '28.03.12 11:00:00,000000 EUROPE/BERLIN');

Mittwoch, März 28, 2012

Zentraler Grenzwertsatz

Craig Shallahamer liefert in seinem Blog einen interessanten Artikel zum central limit theorem (dt.: Zentraler Grenzwertsatz). Ausgangspunkt ist dabei die Feststellung: "If you divide up a population into sample sets and calculate the mean of each sample set, the distribution of the means will be normal" - und mit normal verteilten Ergebnissen lässt sich bekanntlich ziemlich viel anstellen. Ich habe gelegentlich gesehen, wie komplexe Prognosesoftware auf der Basis dieses Theorems definiert wurde - draußen vor der Schulter des Orion, nahe dem Tannhäuser Tor ...

Freitag, März 23, 2012

BIDS 1.6

Wie man bei Darren Gosbell erfährt, gibt's eine neue BIDS-Version mit Support für den SQL Server 2012.

Außerdem habe ich gestern in einem SQL Server 2008 R2 System das schöne neue Profiler Event Resource Usage gesucht, über das Chris Webb vor kurzem geschrieben hatte, und von dem ich mir verspreche, endlich mal einen etwas klareren Eindruck von der Arbeit zu bekommen, die der SSAS ausführt - statt immer nur die Laufzeiten zu beobachten. Leider hab ich's nicht gefunden, da es offenbar erst mit SP1 eingebaut wurde.

Adaptive Cursor Sharing - Links

Über Adaptive Cursor Sharing (ACS) haben dieser Tage die Herren Lewis, Brooks und Gundogar geschrieben:
  • Jonathan Lewis weist darauf hin, dass ACS unterhalb von 11.2.0.3 nur für Selects aktiv wird, deren Ergebnisse komplett abgefetcht wurden (was z.B. für Web-Anwendungen eher selten eintritt).
  • Dominic Brooks untersucht das Zusammenspiel von ACS und SPM. Sein Ergebnis lautet:
    • ACS can provide SPM with bind-sensitive plans to evolve.
    • Whilst SPM can store the multiple plans that ACS requires.
    • But ACS attributes are not stored in SPM.
  • Coskan Gundogar beschäftigt sich mit den Ergebnissen des Artikels von Dominic Brooks.

Oracle Patente

Timur Akhmadeev weist in seinem Blog darauf hin, dass man eine ganze Menge über die Implementierung von Datenbank-Features aus den Patent-Dokumenten von Oracle erfahren kann - und erklärt auch, wie man sie im Netz suchen kann.

ON clause im MERGE

Zu meinen Lieblingselementen in SQL gehört das MERGE-Statement, das dazu dient, bestehende Sätze einer Zieltabelle zu aktualisieren und neue Sätze zu ergänzen - abhängig davon, ob eine gegebene Join-Bedingung zutrifft oder nicht. Ich verwende MERGE dieser Tage oft auch dann, wenn eigentlich ein INSERT oder ein UPDATE genügen würden. Die Syntax von MERGE erfordert zwar eine gewisse Gewöhnung, ist dann aber ausgesprochen einleuchtend und nachvollziehbar. Gestern ist mir aber aufgefallen, dass ich einen zentralen Aspekt des MERGE bisher nicht richtig eingeordnet hatte: nämlich die Rolle der ON Klausel, über die der Join von Quelle und Ziel definiert wird.

Die Dokumentation definiert das ON folgendermaßen:
Use the ON clause to specify the condition upon which the MERGE operation either updates or inserts. For each row in the target table for which the search condition is true, Oracle Database updates the row with corresponding data from the source table. If the condition is not true for any rows, then the database inserts into the target table based on the corresponding source table row.
Entscheidend dabei ist, dass das ON ausschließlich den Join betrifft und nicht zur Definition zusätzlicher Filterkriterien missbraucht werden kann. Dazu ein Beispiel: Ich lege zwei simple Testtabellen an, eine Quelltabelle mit 10 Sätzen und dem Kennungstext 'source' und eine Zieltabelle mit dem Text 'dest':

drop table test_merge_dest;
create table test_merge_dest
as
select rownum id
     , cast ('dest' as varchar2(6)) source_tab
  from dual
connect by level <= 10;

        ID SOUR
---------- ----
         1 dest
         2 dest
         3 dest
         4 dest
         5 dest
         6 dest
         7 dest
         8 dest
         9 dest
        10 dest

drop table test_merge_source;
create table test_merge_source
as
select rownum id
     , 'source' source_tab
  from dual
connect by level <= 10;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 source
         7 source
         8 source
         9 source
        10 source

Ein MERGE der Daten über die Id-Spalte führt dazu, dass alle Sätze der Zieltabelle durch ein Update überschrieben werden:

merge into test_merge_dest dst
using (
select *
  from test_merge_source) src
on (dst.id = src.id)
when matched then update set
  dst.source_tab = src.source_tab
when not matched then insert (
    dst.id
  , dst.source_tab
)
values (
    src.id
  , src.source_tab
);

10 Zeilen integriert.

select * from test_merge_dest;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 source
         7 source
         8 source
         9 source
        10 source

rollback;

So weit ganz harmlos. Nicht harmlos ist es aber, wenn jemand in das ON eine zusätzliche Filterbedingung für die Quelle einbauen will (was syntaktisch auch nicht naheliegend ist und mir bisher nie in den Sinn gekommen war):

merge into test_merge_dest dst
using (
select *
  from test_merge_source) src
on (dst.id = src.id and src.id <= 5)
when matched then update set
  dst.source_tab = src.source_tab
when not matched then insert (
    dst.id
  , dst.source_tab
)
values (
    src.id
  , src.source_tab
);

10 Zeilen integriert.

select * from test_merge_dest;

        ID SOURCE
---------- ------
         1 source
         2 source
         3 source
         4 source
         5 source
         6 dest
         7 dest
         8 dest
         9 dest
        10 dest
         8 source
        10 source
         6 source
         7 source
         9 source

15 Zeilen ausgewählt.

In diesem Fall erfolgt nicht etwa eine zusätzliche Filterung der Quelldaten, sondern eine Änderung der Join-Bedingung: integriert werden immer noch alle 10 Sätze der Quelle, aber 5 werden als Update verarbeitet und 5 als Insert.

Noch deutlicher wird das zugrunde liegende Problem, wenn man sich klar macht, dass das MERGE einen OUTER JOIN darstellt und dabei ist es natürlich relevant, ob man eine Bedingung als Join- oder als Filterkriterium definiert (was in der Syntax mit JOIN-Schlüsselwort deutlicher wird als in der traditionellen Schreibweise mit der Auflistung aller Bedingungen im WHERE).

Mittwoch, März 21, 2012

Extended Statistics und column usage

Dieser Tage beschäftige ich mich etwas intensiver mit extended statistics (zu denen ich gelegentlich schon ein paar Links aufgeführt habe). Und weil sie dabei nicht unbedingt genau so reagieren, wie ich mir das vorstelle, notiere ich hier mal ein paar Beobachtungen.

Zunächst lege ich eine Tabelle mit korrelierten Spaltenwerten an:

create table test_extended_stats
as
select rownum id
     , mod(rownum, 100) col1
     , mod(rownum, 100) col2
  from dual
connect by level <= 100000;

exec dbms_stats.gather_table_stats(user, 'test_extended_stats')

Die Werte für col1 und col2 sind vollständig korreliert, was der cbo natürlich nicht wissen kann. Deshalb kann die cardinality für die Einzelwerte akkurat geschätzt werden - aber nicht für die Kombination der Werte:

set autot trace
select count(*) from test_extended_stats where col1 = 1;

  COUNT(*)
----------
      1000

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |     3 |    65   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                     |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_EXTENDED_STATS |  1000 |  3000 |    65   (2)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=1)

select count(*) from test_extended_stats where col2 = 1;

  COUNT(*)
----------
      1000

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |     3 |    66   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                     |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_EXTENDED_STATS |  1000 |  3000 |    66   (4)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL2"=1)

select count(*) from test_extended_stats where col1 = 1 and col2 = 1;

  COUNT(*)
----------
      1000

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |     6 |    66   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                     |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_EXTENDED_STATS |    10 |    60 |    66   (4)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=1 AND "COL2"=1)

Wie erwartet erkennt der cbo, dass jeder Wert für col1 und col2 jeweils 1000 mal erscheint: die Selektivität ist also 1000/100000 = 0,01 und die cardinality folglich 100000 * 0,01 = 1000. Für die Kombination der Spalten werden die Wahrscheinlichkeiten dann als unabhängig betrachtet und es ergibt sich für die cardinality: 100000 * 0,01 * 0,01 = 10. Und das ist dann natürlich falsch. Das ist ein idealtypischer Anwendungsfall für extended statistics, die solche Korrelationseffekte über die interne Anlage einer virtuellen Spalte abbilden:

select dbms_stats.create_extended_stats(null, 'TEST_EXTENDED_STATS', '(col1, col2)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
--------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH

select count(*) from test_extended_stats where col1 = 1 and col2 = 1;

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |     6 |    66   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                     |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_EXTENDED_STATS |    10 |    60 |    66   (4)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=1 AND "COL2"=1)

Das funktioniert also zunächst noch nicht, was erst einmal daran liegt, dass für die neue virtuelle Spalte noch keine Statistiken existieren:

select column_name
     , sample_size
     , num_distinct
     , last_analyzed
     , histogram 
  from dba_tab_cols
 where table_name = 'TEST_EXTENDED_STATS'
 order by column_name;

COLUMN_NAME                              SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
---------------------------------------- ----------- ------------ ---------- ---------
COL1                                          100000          100 21.03.2012 NONE
COL2                                          100000          100 21.03.2012 NONE
ID                                            100000       100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH                                               NONE

Dann eben ein weiterer dbms_stats-Aufruf:

exec dbms_stats.gather_table_stats(user, 'test_extended_stats')

COLUMN_NAME                              SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
---------------------------------------- ----------- ------------ ---------- ---------
COL1                                            5498          100 21.03.2012 FREQUENCY
COL2                                            5498          100 21.03.2012 FREQUENCY
ID                                            100000       100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH                100000          100 21.03.2012 NONE

select count(*) from test_extended_stats where col1 = 1 and col2 = 1;

------------------------------------------------------------------------------------------
| Id  | Operation          | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |     1 |     6 |    66   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                     |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST_EXTENDED_STATS |  1000 |  6000 |    66   (4)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1"=1 AND "COL2"=1)

Und schon ist die cardinality korrekt. Ein paar Aspekte sind für mich bemerkenswert. Zunächst die sample_size, die gewissen Schwankungen zu unterliegen scheint: ursprünglich wurden für col1 und col2 alle 100.000 Sätze betrachtet, nach der Anlage der extended statistics aber nur noch ca. 5% davon. Da keine Anpassungen durchgeführt wurden, dürfte hier für estimate_percent der default-Wert vorliegen und laut Dokumentation ist das auto_sample_size:

select dbms_stats.get_param('ESTIMATE_PERCENT') from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
---------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

Abgesehen von Greg Rahns Plädoyer für die Verwendung der auto_sample_size, die eine schnelle und exakte Ermittlungen der Statistiken gewährleiste, habe ich nicht allzu viele Aussagen zum Verhalten dieser Option gefunden. Zumindest scheint sie auf Basis der gleichen Daten nicht unbedingt die gleiche sample-Größe hervorzurufen - und ich habe zuletzt ein paar undurchsichtige Fälle beobachtet, in denen ein gather_table_stats mit dem default-sample keine geeignete Statistikbasis lieferte. Aber diese Beobachtung bleibt erst mal anekdotisch, da ich sie bislang nicht in einem Test nachstellen kann. Eine andere Frage ist, warum im gegebenen Beispiel nur für col1 und col2 ein Histogramm erzeugt wurde, nicht aber für die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH. Grundlage für die Anlage von Histogrammen ist dabei die Verwendung der Spalten in WHERE-Bedingungen, die in sys.column_usage$ protokolliert wird (leider gibt es dazu offenbar keine externalisierte data dictionary View, wie man in Christian Antogninis Troubleshooting Oracle Performance, S. 145ff. nachlesen kann). Ein Blick in col_usage$ zeigt, dass dort tatsächlich nur col1 und col2 aufgeführt sind:

-- die Mischung der sys-Objekte mit dba_objects ist dabei meiner Faulheit geschuldet, 
-- sollte am Ergebnis aber nichts ändern ...
select c.name column_name
     , u.equality_preds
     , u.timestamp
  from sys.col_usage$ u
     , sys.col$ c
 where c.obj# = u.obj#
   and c.intcol# = u.intcol#
   and u.obj# in (select object_id 
                    from dba_objects
                   where object_name = 'TEST_EXTENDED_STATS');

COLUMN_NAME                    EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1                                        1 21.03.2012
COL2                                        1 21.03.2012

Anscheinend läuft die Aktualisierung von col_usage$ mit einer gewissen Verzögerung, denn es dauerte in meinem Test ein paar Minuten, ehe dort Einträge erschienen. Ein dbms_stats-Aufruf führt aber offenbar zur unmittelbaren Aktualisierung der col_usage$-Angaben. Aber warum wird in col_usage$ keine Verwendung für die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH protokolliert? Dazu noch mal ein etwas größeres Test-Script:

REM test_col_usage.sql

-- Tabelle löschen
drop table test_extended_stats;

-- Tabelle neu anlegen
create table test_extended_stats
as
select rownum id
     , mod(rownum, 100) col1
     , mod(rownum, 100) col2
  from dual
connect by level <= 100000;

-- extended statistics anlegen
select dbms_stats.create_extended_stats(null, 'TEST_EXTENDED_STATS', '(col1, col2)') from dual;

-- Statistiken nach Anlage der virtuellen Spalte erfassen
-- in Lauf 1 auskommentiert, in Lauf 2 aktiv
-- exec dbms_stats.gather_table_stats(user, 'test_extended_stats')

-- Query mit Einschränkung auf col1 und col2 ausführen
select count(*) from test_extended_stats where col1 = 1 and col2 = 1;

-- dba_tab_cols vor dem dbms_stats-Aufruf
select column_name
     , sample_size
     , num_distinct
     , last_analyzed
     , histogram 
  from dba_tab_cols
 where table_name = 'TEST_EXTENDED_STATS'
 order by column_name;

-- Statistiken erzeugen 
exec dbms_stats.gather_table_stats(user, 'test_extended_stats')

-- column usage
select c.name column_name
     , u.equality_preds
     , u.timestamp
  from sys.col_usage$ u
     , sys.col$ c
 where c.obj# = u.obj#
   and c.intcol# = u.intcol#
   and u.obj# in (select object_id from dba_objects where object_name = 'TEST_EXTENDED_STATS');

-- dba_tab_cols nach dem dbms_stats-Aufruf
select column_name
     , sample_size
     , num_distinct
     , last_analyzed
     , histogram
  from dba_tab_cols
 where table_name = 'TEST_EXTENDED_STATS'
 order by column_name;

Lauf 1 bringt folgende Ausgabe:

SQL> @ C:\temp\test_col_usage.sql

Tabelle wurde gelöscht.

Tabelle wurde erstellt.

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
----------------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH

  COUNT(*)
----------
      1000

COLUMN_NAME                    SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1                                                               NONE
COL2                                                               NONE
ID                                                                 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH                                     NONE

PL/SQL-Prozedur erfolgreich abgeschlossen.

COLUMN_NAME                    EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1                                        1 21.03.2012
COL2                                        1 21.03.2012

COLUMN_NAME                    SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1                                  5620          100 21.03.2012 FREQUENCY
COL2                                  5620          100 21.03.2012 FREQUENCY
ID                                  100000       100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH      100000          100 21.03.2012 NONE

Für Lauf 2 hingegen mit dem unmittelbar nach der Anlage der extended statistics durchgeführten dbms_stats-Aufruf erscheint auch die virtuelle Spalte SYS_STUFLHATC5RBD6JHJZWT$X2AAH in col_usage$ und folglich wird auch ein Histogramm erzeugt:

SQL> @ C:\temp\test_col_usage.sql

Tabelle wurde gelöscht.

Tabelle wurde erstellt.

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'TEST_EXTENDED_STATS','(COL1,COL2)')
----------------------------------------------------------------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH

PL/SQL-Prozedur erfolgreich abgeschlossen.

  COUNT(*)
----------
      1000

COLUMN_NAME                    SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1                                100000          100 21.03.2012 NONE
COL2                                100000          100 21.03.2012 NONE
ID                                  100000       100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH      100000          100 21.03.2012 NONE

PL/SQL-Prozedur erfolgreich abgeschlossen.

COLUMN_NAME                    EQUALITY_PREDS TIMESTAMP
------------------------------ -------------- ----------
COL1                                        1 21.03.2012
COL2                                        1 21.03.2012
SYS_STUFLHATC5RBD6JHJZWT$X2AAH              1 21.03.2012

COLUMN_NAME                    SAMPLE_SIZE NUM_DISTINCT LAST_ANALY HISTOGRAM
------------------------------ ----------- ------------ ---------- ---------------
COL1                                  5591          100 21.03.2012 FREQUENCY
COL2                                  5591          100 21.03.2012 FREQUENCY
ID                                  100000       100000 21.03.2012 NONE
SYS_STUFLHATC5RBD6JHJZWT$X2AAH        5591          100 21.03.2012 FREQUENCY

Das Ergebnis lässt sich regelmäßig reproduzieren, aber eine schlüssige Erklärung dafür fehlt mir noch.

Dienstag, März 20, 2012

Materialized View Fast Refresh

In den letzten Jahren habe ich Materialized Views nur sehr sporadisch eingesetzt, obwohl ich sie für ein ausgesprochen nützliches Hilfsmittel halte. Da ich mir gar nicht mehr sicher bin, ob meine Erinnerungen noch so ganz zur Realität passen, hier ein kleiner Test in 11.1.0.7. Ausgangspunkt sei eine partitionierte Tabelle mit 12M Sätzen verteilt auf 12 Monate:

create table test_tab (
    haus number
  , artikel number
  , datum date
  , umsatz number
)
partition by range (datum) (
  PARTITION test_tab_p_201201 VALUES LESS THAN (TO_DATE('20120201','YYYYMMDD'))
, PARTITION test_tab_p_201202 VALUES LESS THAN (TO_DATE('20120301','YYYYMMDD'))
, PARTITION test_tab_p_201203 VALUES LESS THAN (TO_DATE('20120401','YYYYMMDD'))
, PARTITION test_tab_p_201204 VALUES LESS THAN (TO_DATE('20120501','YYYYMMDD'))
, PARTITION test_tab_p_201205 VALUES LESS THAN (TO_DATE('20120601','YYYYMMDD'))
, PARTITION test_tab_p_201206 VALUES LESS THAN (TO_DATE('20120701','YYYYMMDD'))
, PARTITION test_tab_p_201207 VALUES LESS THAN (TO_DATE('20120801','YYYYMMDD'))
, PARTITION test_tab_p_201208 VALUES LESS THAN (TO_DATE('20120901','YYYYMMDD'))
, PARTITION test_tab_p_201209 VALUES LESS THAN (TO_DATE('20121001','YYYYMMDD'))
, PARTITION test_tab_p_201210 VALUES LESS THAN (TO_DATE('20121101','YYYYMMDD'))
, PARTITION test_tab_p_201211 VALUES LESS THAN (TO_DATE('20121201','YYYYMMDD'))
, PARTITION test_tab_p_201212 VALUES LESS THAN (TO_DATE('20130101','YYYYMMDD'))
, PARTITION test_tab_p_max VALUES LESS THAN (maxvalue)
)
/

begin

for i in 1..12 loop

insert into test_tab(haus, artikel, datum, umsatz)
select mod(rownum, 30) haus
     , round(dbms_random.value * 1000) artikel
     , add_months(to_date('01.12.2011', 'dd.mm.yyyy'), i) datum
     , round(dbms_random.value * 50) umsatz
  from dual
connect by level <= 1000000
;

end loop;

end;
/

Für diese Tabelle lege ich nun eine MView mit fast refresh Option an. Anschließend führe ich ein complete refresh durch:

create materialized view test_mv
refresh fast
as
select datum
     , haus
     , sum(umsatz) umsatz
  from test_tab
 group by datum
        , haus;
        
exec dbms_mview.refresh('test_mv', 'c')

Im 10046er Trace finde ich dafür (unter anderem) folgende Queries:

delete from "TEST"."TEST_MV"

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"("DATUM","HAUS",
  "UMSATZ") SELECT "TEST_TAB"."DATUM","TEST_TAB"."HAUS",
  SUM("TEST_TAB"."UMSATZ") FROM "TEST_TAB" "TEST_TAB" GROUP BY 
  "TEST_TAB"."DATUM","TEST_TAB"."HAUS"

Die MView wird also komplett geleert und dann wieder über ein INSERT mit dem (nicht dokumentierten) BYPASS_RECURSIVE_CHECK-Hint gefüllt. Nun ein zweiter Versuch: ich führe ein Update für Daten einer Partition aus:

update test_tab set umsatz = 5 where datum = '01.12.2012' and haus = 1;        
        
exec dbms_mview.refresh('test_mv', 'f')

Diesmal liefert tkprof folgende Queries:

DELETE FROM "TEST"."TEST_MV" 
WHERE
  ( ( (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
  'NLS_CALENDAR=GREGORIAN') <= "DATUM" AND "DATUM" < TO_DATE(' 2013-01-01 
  00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  ) ) 

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "TEST_TAB"."DATUM" , "TEST_TAB"."HAUS" , 
  SUM("TEST_TAB"."UMSATZ")  FROM "TEST_TAB" "TEST_TAB" WHERE  ( ( (TO_DATE(' 
  2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <=
   "TEST_TAB"."DATUM" AND "TEST_TAB"."DATUM" < TO_DATE(' 2013-01-01 00:00:00',
   'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  )  )GROUP BY 
  "TEST_TAB"."DATUM","TEST_TAB"."HAUS"

Demnach wird die Einschränkung auf das Datum korrekt erkannt und berücksichtigt - wobei ich nicht sehe, woher das System diese Information bekommt. Die Einschränkung auf das Haus spielt allerdings keine Rolle. Wenn ich den Test mit einer Einschränkung auf zwei Partitionen wiederhole, ergibt sich ein entsprechendes Ergebnis:

update test_tab set umsatz = 10 where datum in ('01.01.2012', '01.12.2012') and haus = 2;  

exec dbms_mview.refresh('test_mv', 'f')      

DELETE FROM "TEST"."TEST_MV" 
WHERE
  ( ( ("DATUM" < TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 
  'NLS_CALENDAR=GREGORIAN')) OR (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD 
  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <= "DATUM" AND "DATUM" < TO_DATE(' 
  2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  
  ) ) 

INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "TEST"."TEST_MV"SELECT  /*+ 
  X_DYN_PRUNE */ "TEST_TAB"."DATUM" , "TEST_TAB"."HAUS" , 
  SUM("TEST_TAB"."UMSATZ")  FROM "TEST_TAB" "TEST_TAB" WHERE  ( ( 
  ("TEST_TAB"."DATUM" < TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD 
  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) OR (TO_DATE(' 2012-12-01 00:00:00', 
  'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') <= "TEST_TAB"."DATUM" 
  AND "TEST_TAB"."DATUM" < TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD 
  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  )  )GROUP BY "TEST_TAB"."DATUM",
  "TEST_TAB"."HAUS"

Zur Beantwortung der Frage, warum das Refresh in dieser Weise erfolgt, liefert die mv_capabilities_table Antworten. Anlegen kann man diese Tabelle mit Hilfe des Scripts utlxmv.sql in RDBMS/ADMIN und bei Rob van Wijk findet man nähere Erläuterungen zu den Angaben in der Tabelle. Nach Anlage der Tabelle kann man die Prozedur DBMS_MVIEW.EXPLAIN_MVIEW für eine vorhandene MView aufrufen.

exec DBMS_MVIEW.EXPLAIN_MVIEW ('TEST_MV')

select * from mv_capabilities_table


CAPABILITY_NAME                P MSGTXT                                                            
------------------------------ - ------------------------------------------------------------------
PCT                            Y                                                                   
REFRESH_COMPLETE               Y                                                                   
REFRESH_FAST                   Y                                                                   
REWRITE                        N                                                                   
PCT_TABLE                      Y                                                                   
REFRESH_FAST_AFTER_INSERT      N Detail-Tabelle enthält kein Materialized View-Log                 
REFRESH_FAST_AFTER_ONETAB_DML  N SUM(expr) ohne COUNT(expr)                                        
REFRESH_FAST_AFTER_ONETAB_DML  N Siehe Grund, warum REFRESH_FAST_AFTER_INSERT deaktiviert ist      
REFRESH_FAST_AFTER_ONETAB_DML  N COUNT(*) ist in SELECT-Liste nicht vorhanden                      
REFRESH_FAST_AFTER_ONETAB_DML  N SUM(expr) ohne COUNT(expr)                                        
REFRESH_FAST_AFTER_ANY_DML     N Siehe Grund, warum REFRESH_FAST_AFTER_ONETAB_DML deaktiviert ist  
REFRESH_FAST_PCT               Y                                                                   
REWRITE_FULL_TEXT_MATCH        N Neuschreiben von Abfragen bei Materialized View deaktiviert       
REWRITE_PARTIAL_TEXT_MATCH     N Neuschreiben von Abfragen bei Materialized View deaktiviert       
REWRITE_GENERAL                N Neuschreiben von Abfragen bei Materialized View deaktiviert       
REWRITE_PCT                    N Neuschreiben von Abfragen bei Materialized View deaktiviert       
PCT_TABLE_REWRITE              Y                                                   

Demnach fehlt der Tabelle also ein MView Log und außerdem werden ein count(*) und ein count(expr) vermisst:

create materialized view test_mv
refresh fast
as
select datum
     , haus
     , sum(umsatz) umsatz
     , count(*) cnt
     , count(umsatz) cnt_umsatz
  from test_tab
 group by datum
        , haus;

-- Anlage MView Log mit rowid (da ich keinen PK zur Tabelle habe) und mit den relevanten Spalten
create materialized view log on test_tab with rowid (haus, artikel, datum, umsatz) including new values;
-- nach dem Aufbau des View Logs muss ein complete refresh erfolgen, ehe das View Log für
-- ein fast refresh herangezogen werden kann
exec dbms_mview.refresh('test_mv', 'c')


CAPABILITY_NAME                P MSGTXT                                                      
------------------------------ - ------------------------------------------------------------
PCT                            Y                                                             
REFRESH_COMPLETE               Y                                                             
REFRESH_FAST                   Y                                                             
REWRITE                        N                                                             
PCT_TABLE                      Y                                                             
REFRESH_FAST_AFTER_INSERT      Y                                                             
REFRESH_FAST_AFTER_ONETAB_DML  Y                                                             
REFRESH_FAST_AFTER_ANY_DML     Y                                                             
REFRESH_FAST_PCT               Y                                                             
REWRITE_FULL_TEXT_MATCH        N Neuschreiben von Abfragen bei Materialized View deaktiviert 
REWRITE_PARTIAL_TEXT_MATCH     N Neuschreiben von Abfragen bei Materialized View deaktiviert 
REWRITE_GENERAL                N Neuschreiben von Abfragen bei Materialized View deaktiviert 
REWRITE_PCT                    N Neuschreiben von Abfragen bei Materialized View deaktiviert 
PCT_TABLE_REWRITE              Y                                                   

Somit sind nun alle Voraussetzungen für ein REFRESH_FAST_AFTER_ANY_DML gegeben. Und tatsächlich führt ein Update für die Basistabelle nun im Fall des fast refresh nicht mehr zu einem delete -> insert, sondern zu einem merge auf Basis der Inhalte des MView Logs:

-- behutsam formatiert:
MERGE INTO "TEST"."TEST_MV" "SNA$" 
USING (
SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000)   */ 
       "DLT$0"."DATUM" "GB0"
     , "DLT$0"."HAUS" "GB1"
     , SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)*  DECODE(("DLT$0"."UMSATZ"), NULL, 0, 1)) "D0"
     , SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)) "D1"
     , NVL(SUM(DECODE("DLT$0"."DML$$", 'I',  1, -1)* ("DLT$0"."UMSATZ")), 0) "D2" 
  FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$  
             , "MAS$"."HAUS"
             , "MAS$"."DATUM"
             , "MAS$"."UMSATZ"  
             , DECODE("MAS$".OLD_NEW$$, 'N', 'I', 'D') DML$$  
          FROM "TEST"."MLOG$_TEST_TAB" "MAS$"   
         WHERE "MAS$".SNAPTIME$$ > :1 ) AS OF SNAPSHOT (:2) "DLT$0"  
 GROUP BY "DLT$0"."DATUM","DLT$0"."HAUS")"AV$" 
 ON (    SYS_OP_MAP_NONNULL("SNA$"."DATUM")=SYS_OP_MAP_NONNULL("AV$"."GB0") 
     AND SYS_OP_MAP_NONNULL("SNA$"."HAUS")=SYS_OP_MAP_NONNULL("AV$"."GB1")
     ) 
WHEN MATCHED THEN 
UPDATE SET 
    "SNA$"."CNT_UMSATZ"="SNA$"."CNT_UMSATZ"+"AV$"."D0"
  , "SNA$"."CNT"="SNA$"."CNT"+"AV$"."D1"
  , "SNA$"."UMSATZ"=  DECODE("SNA$"."CNT_UMSATZ"+"AV$"."D0",0,NULL,NVL("SNA$"."UMSATZ",0)
  +"AV$"."D2") 
DELETE WHERE ("SNA$"."CNT" = 0) 
WHEN NOT MATCHED THEN INSERT (
    "SNA$"."DATUM"
  , "SNA$"."HAUS"
  , "SNA$"."CNT_UMSATZ"
  , "SNA$"."CNT"
  , "SNA$"."UMSATZ") 
VALUES (
    "AV$"."GB0"
  , "AV$"."GB1"
  , "AV$"."D0"
  , "AV$"."D1"
  , DECODE ("AV$"."D0", 0, NULL, "AV$"."D2")) WHERE ("AV$"."D1" > 0)

Bemerkenswert an diesem MERGE finde ich:
  • den (nicht dokumentierten) OPT_ESTIMATE-Hint, den Christo Kutrovsky gelegentlich näher erläutert hat.
  • das "AS OF SNAPSHOT", zu dem ich im Netz nicht arg viel gefunden habe
  • die (nicht dokumentierte) Funktion SYS_OP_MAP_NONNULL, zu der man z.B. in Eddie Awads Blog Informationen findet.
Die Suche nach den relevanten Schlüsselwörtern führt immer wieder zu den gleichen Webseiten, die ich hier im Blog auch schon früher verlinkt habe:
Möglicherweise komme ich in nächster Zeit mal wieder auf das Thema zurück.

Sonntag, März 18, 2012

Visualisierung von ASH-Inhalten

Kyle Hailey stellt eine ganze Reihe hübscher Möglichkeiten zur Visualisierung von ASH-Daten vor (die natürlich auch für andere Daten verwendbar sind). Obwohl ich üblicherweise eher Zahlenkolonnen betrachte, gebe ich durchaus zu, dass solche Bilder Dinge sichtbar machen, die sonst untergehen können.

Sonntag, März 11, 2012

Selectivity Bestimmung über Index Statistiken

Randolf Geist hatte hier in einem Kommentar gelegentlich schon mal darauf hingewiesen, dass der cbo Index-Statistiken verwenden kann, um die Korrelation der Spalten eines Index zu berücksichtigen und eine sinnvolle Cardinality-Schätzung zu liefern, die im Fall stärkerer Korrelation natürlich deutlich höher liegt als durch die Kombination der Einzelselektivitäten zu erwarten wäre. Jonathan Lewis liefert jetzt in seinem Blog eine detaillierte Untersuchung zum Thema und weist darin noch auf weitere wichtige Punkte hin:
  • "The optimizer has used an “index sanity” check when calculating join cardinalities since (at least) Oracle 8i – but the check has been restricted to unique indexes. From 11g it is effectively in use for any index."
  • "The code has been back-ported to 10.2.0.4 (and 10.2.0.5) but requires you to set a value for the hidden _fix_control parameter"
  • "this makes it harder to work out if it’s safe to drop an index. Even when an index doesn’t appear in an execution plan Oracle may be using its number of distinct keys to evaluate and choose an execution plan. So, to guard against changes in plans when you drop a multi-column index, you might want to think about creating extended stats on a set of columns that matches the index definition before dropping the index."

Freitag, März 09, 2012

Interessante Kommentare im PL/SQL-Core

Morten Braten hat ein paar nette Kuriositäten in den Kommentaren des STANDARD-Packages gefunden, die man sich über ALL_SOURCES auch selbst anschauen kann. Mein persönlicher Favorit ist: "Perhaps this can be done more intelligently in the future."

Da also auch, denke ich.

Join Views

Randolf Geist hat in seinem Blog vor einigen Tagen eine längere Erläuterung zum Thema Join Views, ROWIDs And Query Transformations veröffentlicht. Darin erfährt man unter anderem:
  • dass man auf eine Join-View (also einfach eine View, die als Join mehrerer Basistabellen definiert ist), nur dann via rowid zugreifen kann, wenn eine der beteiligten Tabellen als "key-preserved table" gilt: "Oracle needs to be able to determine if there is at least one table in the view for which it is guaranteed that its rows can show up at most once in the result set of the view, which means that there need to be a minimum set of unique or primary key constraints at least if the view involves more than a single table. Furthermore Oracle needs to be able to determine this automatically, and depending on the complexity and expressions used, this is not always the case"
  • die rowid eines Satzes der Join-View ist in einem solchen Fall die rowid der "key-preserved table".
  • beim Zugriff über die rowid ist eine table elimination möglich: zugegriffen wird nur auf die "key-preserved table", da der Optimizer erkennt, dass er sich das Lesen der übrigen Tabellen der View sparen kann (jedenfalls bei den Constraint-Definitionen, die im Geist'schen Beispiel gewählt sind).
  • mit relativ geringen Änderungen am Setup des Tests kann der Herr Geist einen "ORA-01410: invalid ROWID" hervorrufen: "Simple View Merging, another Query Transformation together with Join Elimination causes Oracle 11.2 to apply the ROWID to the wrong table in case multiple possible candidates for key-preserved tables exist."
  • offenbar gibt es ein paar Varianten zum beschriebenen Bug.

Count-Aggregation

Jonathan Lewis präsentiert einen interessanten Fall, in dem ein count(*) abhängig von der Art der Verknüpfung von Teilmengen eine stark unterschiedliche Performance zeigt. Dabei weist er auch darauf hin, dass ein SORT AGGREGATE für ein COUNT keine Aggregation bedeutet, da das Zählen über einen Laufzähler erfolgen kann. Ich merke gerade, dass mir kein sinnvolles Exzerpt des Artikels gelingt und verweise deshalb auf diesen.

FTS Blocks im Buffer Cache

Bei Charles Hooper habe ich dieser Tage den Hinweis gefunden, dass über FTS gelesene Blocks keineswegs am MRU-Ende der LRU-Liste des Buffer Caches eingeordnet werden (wie das in 8i noch der Fall war), sondern seit mehreren Releases am mid-point der Liste. Die Dokumentation schreibt dazu:
When buffers must be read from disk, the database inserts the buffers into the middle of the LRU list. In this way, hot blocks can remain in the cache so that they do not need to be read from disk again. [...] Blocks read into the database cache as the result of a full scan of a large table are treated differently from other types of reads. The blocks are immediately available for reuse to prevent the scan from effectively cleaning out the buffer cache.
Mal wieder ein Fall, der zeigt, dass die Beschäftigung mit Oracle eine historische Wissenschaft ist, die nicht nur von Zuständen, sondern vor allem auch von Entwicklungen handelt.

SPM-Beispiel

Hier folgt ein minimalistisches Beispiel für die Verwendung von SPM - also SQL Plan Management -, das auf Tim Halls Darstellung des Features in seinem Blog basiert.

Die Dokumentation definiert SPM wie folgt: "SQL plan management prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information." Es geht also darum, einen bekannten Ausführungsplan für zukünftige Ausführungen festzulegen, um das plötzliche Kippen zu Plänen (etwa aufgrund von Statistik-Änderungen) zu vermeiden - was natürlich nur funktioniert, wenn der Plan aktuell verwendbar ist (also z.B. alle darin verwendeten Indizes existieren etc.). Nützlich ist ein solcher Mechanismus z.B. für große ETL-Operationen, für die mehrere unterschiedlich performante Pläne im AWR existieren, zwischen denen der CBO regelmäßig hin und her springt.

Sehr interessant ist das Feature auch deshalb, weil es offenbar Teil der Enterprise Edition ist und keine zusätzliche Lizenzierung erfordert.

Der Test erfolgt in 11.1.0.7:
-- Berechtigungen des Test-Users
select * from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST                           CONNECT                        NO  YES NO
TEST                           RESOURCE                       NO  YES NO

-- Anlage einer Test-Tabelle
create table test_spm_mpr
as
select rownum id
      , lpad('*', 100, '*') padding
   from dual
connect by level <= 1000000;

-- Ausführungsplan über autotrace anzeigen
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.45

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4227   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |   786K|  4227   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

-- Ermittlung der sql_id für die count-Query
select sql_id, sql_text
  from v$sql
 where sql_text like 'select%test_spm_mpr%'
   and sql_text not like '%v$sql%';

SQL_ID        SQL_TEXT
------------- ---------------------------------
1ndqm9sqnzr52 select count(*) from test_spm_mpr

-- Versuch, den Plan als Baseline zu registrieren
declare
   l_plans_loaded  pls_integer;
begin
   l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
     sql_id => '1ndqm9sqnzr52');

   dbms_output.put_line('plans loaded: ' || l_plans_loaded);

end;
/

declare
*
FEHLER in Zeile 1:
ORA-38171: Nicht ausreichende Berechtigungen für Vorgang mit SQL-Management-Objekt
ORA-06512: in "SYS.DBMS_SPM", Zeile 2184
ORA-06512: in Zeile 4

Die fehlende Berechtigung ist das Systemprivileg ADMINISTER SQL MANAGEMENT OBJECT, wie man im Blog der CBO-Entwickler erfährt.

-- Zuweisung des Privilegs durch jemanden, der es vergeben kann:
grant ADMINISTER SQL MANAGEMENT OBJECT to test;

Benutzerzugriff (Grant) wurde erteilt.

-- wieder in der Session von TEST:
set serveroutput on
declare
   l_plans_loaded  pls_integer;
begin
   l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
     sql_id => '1ndqm9sqnzr52');

   dbms_output.put_line('plans loaded: ' || l_plans_loaded);

end;
/
plans loaded: 1

-- Prüfung der Verwendung
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.56

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4227   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |   786K|  4227   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb163db08b5e3" used for this statement

Die Baseline wird also verwendet. Mehr zur Baseline erfährt man aus dba_sql_plan_baselines und aus einer dbms_xplan-Routine:

-- das muss natürlich wieder ein privilegierter Nutzer abfragen:
select sql_handle
     , sql_text
     , plan_name
     , enabled
     , accepted
  from dba_sql_plan_baselines
 where sql_text like '%test_spm_mpr%';

SQL_HANDLE                     SQL_TEXT                            PLAN_NAME                      ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb163db08b5e3  YES YES

select *
  from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_a3ebb163db08b5e3'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

---------------------------------------------------------------------------
SQL handle: SYS_SQL_6f076b44a3ebb163
SQL text: select count(*) from test_spm_mpr
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_a3ebb163db08b5e3
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
---------------------------------------------------------------------------

Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4231   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |  1270K|  4231   (1)| 00:00:51 |
---------------------------------------------------------------------------

Jetzt zum interessanten Punkt: was passiert, wenn der CBO einen günstigeren Plan wählen könnte? Dazu lege ich (wie vor mir der Herr Hall) einen Index an, der den Zugriff deutlich vereinfachen und die Kosten senken würde, wenn die Baseline nichts dagegen hätte:

-- Index-Anlage + Statistik-Erfassung
create index test_spm_mpr_ix on test_spm_mpr(id); 
-- ohne not null constraint wäre der Index für die Zählung allerdings nicht vervendbar
alter table test_spm_mpr modify id not null;

exec dbms_stats.gather_table_stats(user, 'TEST_SPM_MPR', cascade=>true)

-- Hard-Parse erzwingen durch Löschung des Shared Pools 
-- (!!! was man in einer Produktivumgebung lieber bleiben lassen sollte !!!)
-- auch das kann natürlich nur ein privilegierte User
alter system flush shared_pool;

-- wieder im TEST-Schema
set autot on exp
select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:01.50

Ausführungsplan
----------------------------------------------------------
Plan hash value: 2052051621

---------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |  4229   (1)| 00:00:51 |
|   1 |  SORT AGGREGATE    |              |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_SPM_MPR |  1000K|  4229   (1)| 00:00:51 |
---------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb163db08b5e3" used for this statement

Funktioniert also erwartungsgemäß: der neue (in diesem Fall: bessere) Plan wird ignoriert, weil die Baseline Vorrang hat. In dba_sql_plan_baselines findet man aber bereits eine zweite Baseline, die allerdings noch nicht akzeptiert wurde:

SQL_HANDLE                     SQL_TEXT                            PLAN_NAME                      ENA ACC
------------------------------ ----------------------------------- ------------------------------ --- ---
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb16343842f93  YES NO
SYS_SQL_6f076b44a3ebb163       select count(*) from test_spm_mpr   SYS_SQL_PLAN_a3ebb163db08b5e3  YES YES

Um den neuen, potentiell - und im gegebenen Fall auch tatsächlich - besseren Plan zu übernehmen, kann man nun eine weitere dbms_spm-Routine verwenden, die auch noch eine erläuternde Ausgabe liefert:

select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_6f076b44a3ebb163')
  from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_6F076B44A3EBB163')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_6f076b44a3ebb163
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_a3ebb16343842f93
-----------------------------------
  Plan was verified: Time used 1,485 seconds.
  Passed performance criterion: Compound improvement ratio >= 6,87
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            1349            102             13,23
  CPU Time(ms):                 156             15              10,4
  Buffer Gets:                15396           2241              6,87
  Disk Reads:                 15385            134            114,81
  Direct Writes:                  0              0
  Fetches:                      137             21              6,52
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

Eine erneute Ausführung der Test-Query zeigt, dass jetzt die neue Baseline verwendet wird:

select count(*) from test_spm_mpr;

  COUNT(*)
----------
   1000000

Abgelaufen: 00:00:00.04

Ausführungsplan
----------------------------------------------------------
Plan hash value: 569399299

---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |   613   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE       |                 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_SPM_MPR_IX |  1000K|   613   (2)| 00:00:08 |
---------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_a3ebb16343842f93" used for this statement

Man kann noch sehr viel mehr mit SPM anstellen, aber ich spare mir eine Verlängerung dieses Eintrags und verweise außer auf Tim Hall auch noch auf mehrere Artikel von Maria Colgan:

Mittwoch, März 07, 2012

SQL Server 2012 Release

Wie man bei Kasper de Jonge erfährt, steht jetzt eine evaluation version für den SQL Server 2012 zum Download bereit. Käuflich erwerben kann man die Software dann ab April.

Dienstag, März 06, 2012

SQL Patch

Im Blog der CBO-Entwickler fanden sich in jüngerer Vergangenheit zwei Beiträge zum Thema des Hints BIND_AWARE:
  • How do I force a query to use bind-aware cursor sharing? führt die Verwendung des Hints anhand von Beispielfällen vor
  • Using SQL Patch to add hints to a packaged application zeigt, wie man den BIND_AWARE-Hint mit Hilfe der Funktion SYS.DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH in ein SQL Patch-Objekt einfügen kann. Dabei scheint ein SQL Patch eine weitere Variante zu den bekannten Plan Management-Optionen (sql profile, outline, sql baseline) zu sein - jedenfalls erscheint in der Note-Sektion det dbms_xplan.display-Ausgabe ein ähnlicher Hinweis wie für die anderen Verfahren ("SQL Patch "xyz" used for this statement").
Beide Artikel riefen Nachfragen hervor, ob dieses - weitgehend undokumentierte - Feature überhaupt offiziell supported sei und wie die Lizenzierung für die Verwendung der SQL Patch-Funktionalität aussähe. In Reaktion auf diese Fragen, wurde ein weiterer Artikel veröffentlicht:
  • Additional Information on SQL Patches: darin wird darauf hingewiesen, dass SQL Patch in den Zusammenhang des SQL Repair Advisor gehört und keine besondere Lizenzierung erfordert. Außerdem wird erklärt, dass die zugehörige API nur einen SQL_TEXT und keine SQL_ID übernehmen kann.
Weitere erläuternde Beiträge zum Thema sind angekündigt.

Dom Brooks hat zum Thema in seinem Blog einen umfangreicheren Kommentar geschrieben, der unter anderem darauf hinweist, dass eine deutliche Abgrenzung von SQL Patch und SQL Baseline noch fehlt (und auch, dass die Frage des offiziellen Supports nicht endgültig beantwortet ist). In einem zweiten Artikel liefert der Herr Brooks diese Information dann selbst nach und erklärt:
SQL Baselines exist to reproduce a specific plan. In fact, a plan hash exists as part of the baseline. If, on application of the baseline, the Optimizer is unable to reproduce the desired plan, the baseline is rejected outright. On the other hand, SQL Patches have been developed primarily to get the Optimizer to avoid a particular problem path in an execution plan, specifically to avoid failures due to certain access methods, join methods, etc.
Außerdem zeigt er auch noch anhand von SYS.SQLOBJ$, SYS.SQLOBJ$DATA und SYS.SQLOBJ$AUXDATA die unterschiedliche interne Darstellung der beiden Verfahren. Enthalten sind auch noch diverse sehr übersichtliche Code-Stücke zur Anlage der fraglichen Objekte zur Plan-Stabilisierung bzw. -Beeinflussung.

Nachtrag 01.04.2012: inzwischen hat Dom Brooks noch zwei weitere Artikel veröffentlicht:
  • SQL Patch III / Parallel Baselines: zeigt, dass man mehrere Hints in einem SQL Patch unterbringen kann und dass man damit auch einen parallel Hint einbauen kann (was bei Baselines anscheinend nicht so recht funktioniert).
  • SQL Patch IV – Why is the SQL Patch applied but not effective?: zeigt, dass auch dann, wenn ein SQL Patch verwendet wird, nicht sichergestellt ist, dass die enthaltenen Hints verwendet wird; man muss in diesem Fall sehr genau auf die hint specification achten und darin die komplette query block Benamung einbauen.

Sonntag, März 04, 2012

Oracle-BI

Mark Rittman hat in seinem Unternehmens-Blog ein paar umfangreiche Erläuterungen zu den aktuellen Lösungen in Oracles BI-Portfolio untergebracht. Dankenswerterweise sammelt er dabei die Einzelartikel unter jeweils einem Einstiegsartikel, was das Verlinken deutlich abkürzt. Die Artikelserien betreffen:
  • Endeca: "Endeca focused on this e-commerce market first, and developed the MDEX engine to support this, marketing it as a column-store, rapid-development query engine that allows “faceted searches” across lots of different, “jagged” data sets (i.e. data sets that don’t have the same data model, but with some commonality between them). About a couple of years ago, Endeca took the core technology from this product and created a standalone BI tool called Endeca Latitude, complete with dashboard components, an ETL tool, dashboard and report designers, and a story that revolved around “agile BI”, based on the fact that the MDEX engine doesn’t require a strictly-defined data model. By the time of the Oracle acquisiton, Endeca’s product line looked like this, with Endeca Infront, the product behind these websites, accounting for the majority of revenue."
  • Exalytics: eine In-Memory Machine als "combination of hardware and software, with the software containing special capabilities that you can’t get just by assembling your own system, using off-the-shelf components." Der erste Artikel der Serie gibt zusätzlich einen kompakten Überblick zu Oracles BI-Angeboten.

sql profile aus dem AWR erzeugen

Schon vor längerer Zeit hat Kerry Osborne ein Script bereitgestellt, mit dessen Hilfe man aus dem automatic workload repository (AWR) ein sql profile für eine historische Query erzeugen kann. Nützlich ist das z.B. wenn die Pläne einer Query regelmäßig kippen und mehrere unterschiedlich effektive Versionen im AWR vorliegen. Voraussetzung ist dabei einmal mehr die Lizenzierung des Tuning Pack (und die Zuweisung der erforderlichen Privilegien, zu denen Advisor gehört).

Freitag, März 02, 2012

SYS_CONTEXT

Bei Uwe Hesse findet man ein nettes whoami-Script, das mit Hilfe von SYS_CONTEXT alle möglichen Informationen zum angemeldeten Benutzer liefert.