Einschränkungen für Compression-Operationen

Noch ein erhellender Artikel von Randolf Geist, diesmal zu den Einschränkungen für table und index compression in verschiedenen Oracle-Versionen.

MDX-Ergebnisse in TSQL einbetten

Der Blog-Artikel MDX + T-SQL: Combining relational and multi-dimensional data into one query result set erläutert detailliert, wie man die Ergebnisse einer MDX-Abfrage über die Verwendung von LinkedServer und OpenQuery in eine SQL-Abfrage integrieren kann. Eine sinnvolle Verwendung dieser Option ist vermutlich die Verknüpfung der Inhalte von SSAS-DMVs, über die Vidas Matelis gelegentlich geschrieben hatte.

Tabellen mit mehr als 255 Spalten

Randolf Geist hat mal wieder einen sehr interessanten Blog-Artikel geschrieben, diesmal über das Thema der Statistikerhebung für Tabellen mit mehr als 255 Spalten. Zunächst erläutert er darin, warum es grundsätzlich keine besonders gute Idee ist, Oracle-Tabellen mit so vielen Spalten zu verwenden:

Oracle stores rows of conventional heap tables with more than 255 columns (where at least one column value after the 255th is non-null) in multiple row pieces even when the entire row may fit into a single block (and up to 11.2 doesn't support basic and OLTP heap table compression on tables with more than 255 columns). This leads to something that is sometimes called "intra-row chaining" which means that Oracle needs to follow the row piece pointer to access columns after the 255th one leading to multiple logical I/Os per row, up to four for a row approaching the hard limit of 1,000 columns.

Dann folgt ein nettes Stück Test-Code zum Erzeugen einer solchen Tabelle; ich habe solche Tabellen allerdings auch schon produktiv gesehen, man kommt schnell zu einer solchen Spaltenanzahl, wenn man unterschiedliche Dinge in eine Tabelle packt und durch Satzarten unterscheidet - aber ich schweife ab...

Und schließlich dann das titelgebende Thema der Probleme beim Anlegen von Statistiken für solche Monster (um's ganz kurz zu machen: die Statistikerfassung muss die Tabelle mehrfach lesen, um die Statistiken anzulegen). Wie oft beim Herrn Geist ist der Artikel etwas länger, aber das ist kein Schade.

SSAS-Instanz-Monitoring mit Perfmon

zuletzt habe ich mich damit beschäftigt, wie man die Serverbelastung auf einem Windows Server 2003 beim Processing (oder auch im normalen Abfrage-Betrieb) einer Analysis Services Datenbank (SQL Server 2008) sinnvoll überwachen kann. Das Tool der Wahl war dabei perfmon. Damit kann man ein neues „Leistungsindikatorenprotokoll“ (vermutlich ist die englische Version „Counter Log“) anlegen, diverse Indikatoren hinzufügen, eine Protokolldatei im blg-Format angeben und bei Bedarf auch einen Zeitplan angeben. Wichtig ist anscheinend, dass man auf der Seite „Allgemein“ bei „Ausführen als“ statt "standard" einen echten Benutzer angibt. Das so erstellte Protokoll kann man sich dann folgendermaßen anzeigen lassen:

• Unter Konsolenstamm/Systemmonitor im Kontextmenü „Neues Fenster“ auswählen
• In der Symbolleiste über dem (leeren) Anzeigefenster die Eigenschaften auswählen (Strg + Q; bzw. das 4 Symbol von rechts)
• Im Tab „Quelle“ die erzeugte Protokolldatei angeben und unter „Daten“ die gewünschten Indikatoren

So weit, so gut. Allerdings macht mir diese Form der Protokollierung wenig Freude, da es z.B. nicht möglich zu sein scheint, die Fenster für die Indikatorenwahl zu resizen, und weil mir Diagramme in manchen Fällen weniger sagen als Zahlen.

Alternativ kann man die Protokollierung aber auch in eine Datenbank schreiben lassen, und das ist dann schon mehr nach meinem Geschmack. Die Anleitung unter http://www.netadmintools.com/art577.html erläutert das Vorgehen recht umfassend. Hier noch mal die Kurzfassung:

• Anlage einer (zunächst leeren) ProtokolldatenbankDefinition einer ODBC-Connection (System-DSN); abweichend von der Anleitung musste ich statt der festen Portangabe die (per default eingestellt) Option „Dynamically determine port“ verwenden, da der Connection-Tests mit Port 1433 fehlschlug
• Anlage eines Counter Logs mit der Auswahl einer „SQL-Datenbank“ als Protokolldateityp und Einstellung der ODBC-DSN über „Konfigurieren“; Start des Logs
• Wenn keine Rechteprobleme auftreten (s. Ereignislog), dann werden jetzt in der Datenbank drei Tabellen angelegt
• DisplayToID: mit Informationen zu den Protokollierungsläufen
• CounterDetails: mit den Indikatoren und ihren Ids (was ich für deutlich übersichtlicher halte als die Darstellung in den Auswahlfenstern, zumal man darin via SQL natürlich sortieren und filtern kann)
• CounterData: die protokollierten Werte mit Zeitstempeln

Man kann diese Informationen joinen und darauf dann allerlei Queries absetzen und auf diese Weise dann wahrscheinlich auch herausbekommen, welche Indikatoren überhaupt interessant sind und protokolliert werden sollten.

Die Metadaten einer Protokollierung können im html-Format gespeichert werden (im Kontextmenü eines einzelnen Protokolls: Einstellungen speichern unter ...). Aus einer solchen Sicherungen lässt sich dann auch ein neues Protokoll erzeugen (im Kontextmenü des Knotens Leistungsindikatorenprotokoll: Neue Protokolleinstellungen von ...)

Der Punkt, an dem ich mir Probleme vorstellen kann, sind mal wieder die Berechtigungen (lokale oder Domänen-Accounts; Admin-Rechte; Zugriff auf perfmon und DB etc.), aber zumindest prinzipiell funktioniert das Loggen in eine DB offenbar problemlos.

Vermutlich könnte man die Ergebnisse auch noch irgendwie mit den Inhalten einer OLAPQueryLog-Tabelle verknüpfen, dazu vielleicht gelegentlich mehr.

Für Windows 2008 sieht das Vorgehen übrigens anders aus, denn dort kann man kein ODBC-Ziel für die Protokollierung angeben. Stattdessen ist aber Folgendes möglich: man kann eine DataCollector-Log-Datei (die normalerweise im blg-Format angelegt wird) mit Hilfe des relog-Tools an ein ODBC-Ziel schicken, also auf Kommandozeile:

relog C:\PerfLogs\Admin\2010-07-12\DataCollector012010-07-12_1445.blg -o SQL:pmon!

Wobei der Name der ODBC-Verbindung ist. In der Zieldatenbank werden dadurch die drei perfmon-log-Tabellen angelegt (CounterData, CounterDetails, DisplayToID), und mit den geloggten Daten gefüllt. Theoretisch kann man die Logs mit relog auch noch Filtern (oder auch in andere Log-Formate konvertieren).Vermutlich kann man den reolg-Befehl als automatischen Task ans Ende der Log-Erstellung setzen.

current SQL

Zu meinen Hobbies gehört das Schreiben und Überarbeiten von SQL-Scripts. Aufgrund der häufigen Überarbeitungen ist es eigentlich nicht sinnvoll, solche Scripte in einem statischen Blog unterzubringen, aber da der Blog relativ gut erreichbar ist, während meine google-site in gesicherten Netzen oft geblockt wird, bringe ich dieses doch einmal hier unter. Besonders aufregend ist es nicht, aber für mich ziemlich hilfreich: zunächst suche ich mir die Module aus, die SQL-Queries abgesetzt haben, und dann folgt eine Query auf v$sql, die man auf ein Modul, ein Schema, eine Laufzeit in Sekunden oder eine SQL_ID einschränken kann.

set pagesize 100
column module format a60
column SORTS format 9999999
column row_cnt format 9999999
column execs format 99999999
column substr_sql format a50
column parsing_schema_name format a30
column row_cnt format 999999999

select module, count(*)
  from v$sql
 group by module
 order by module;

select to_char(sysdate, 'hh24:mi:ss') curtime
     , parsing_schema_name
     , sql_id
     , child_number
     , substr(sql_text, 1, 100) sql_text
     , executions
     , round(elapsed_time/1000000) elapsed_sec
     , round(elapsed_time/case when executions = 0 then 1 else executions end /1000000) ela_sec_per_exec
     , buffer_gets
     , trunc(buffer_gets/decode(executions, 0, 1, executions)) lio_per_exec
     , rows_processed
     , round(cpu_time/1000000) cpu_time
     , round(user_io_wait_time/1000000) user_io_wait_time
     , round(plsql_exec_time/1000000) plsql_exec_time
     , round(java_exec_time/1000000) java_exec_time
     , to_char(last_active_time, 'dd.mm.yyyy hh24:mi') last_active_time
     , parse_calls
     , disk_reads
     , direct_writes
     , fetches
     -- , serializable_aborts
     -- , end_of_fetch_count
     -- , loads
     -- , version_count
     -- , invalidations
     -- , avg_hard_parse_time
     -- , application_wait_time
     -- , concurrency_wait_time
     -- , cluster_wait_time
     , px_servers_executions
     , sorts
     , sharable_mem
     -- , total_sharable_mem
     , typecheck_mem
  from v$sql
 where upper(module) like upper('%&module%') 
   and parsing_schema_name like upper('%&schema%')
   and round(elapsed_time/1000000) >= nvl('&secs', 0)  
   and sql_id like '%&sql_id%'
 order by elapsed_time desc;

SQL_PLAN_BASELINE

Eigentlich wollte ich seit einiger Zeit etwas über Chrsitian Antogninis hochinteressantes Buch Troubleshooting Oracle Performance schreiben, in dem ich schon seit geraumer Zeit lese, aber irgendwie komme ich nicht dazu. Stattdessen kam mir bei der Lektüre in seinem Blog die Idee, mal einen Blick auf das Thema SQL Plan Management in Oracle 11 zu werfen. Laut Oracle-Doku dient das Plan Management zu Folgendem:

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Man kann also für ein Statement einen offiziell abgesegneten Plan festlegen, der für alle folgenden Ausführungen der Query maßgeblich ist. Wenn ein potentiell besserer Plan berechnet wird, dann wird er von der Datenbank gespeichert, aber erst nach expliziter Bestätigung durch den DBA für weitere Ausführungen verwendet. Bei Tim Hall findet sich eine instruktive Einführung in das Thema.

Trotzdem folgt hier ein kurzer Versuch: Zunächst lege ich eine Testtabelle an:

-- Session 1 
create table test_baseline(col1 not null, col2, col3)
as
select rownum col1
     , lpad(' ', 200, '*') col2
     , lpad(' ', 200, '*') col3
  from dual
connect by level <= 1000000

In einer zweiten Session aktiviere ich jetzt das Erfassen von Plan Baselines, zähle die Sätze meiner Testtabelle und deaktiviere die Planerfassung wieder:

-- Session 2 
ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
SELECT /* baseline_test */ COUNT(*) FROM test_baseline;
ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

Der Zugriff kann natürlich nur über FTS erfolgen, da noch kein Index für die Tabelle existiert:

SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.90

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

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 10800   (1)| 00:02:32 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |   814K| 10800   (1)| 00:02:32 |
----------------------------------------------------------------------------

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


Statistiken
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      28644  consistent gets
      28572  physical reads
          0  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Den fehlenden Index lege ich nun in Session 1 an:

SQL> create index test_baseline_idx on test_baseline(col1);

Index wurde erstellt.

Abgelaufen: 00:00:07.45
SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.93

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

----------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 | 10830   (1)| 00:02:32 |
|   1 |  SORT AGGREGATE    |               |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |  4701K| 10830   (1)| 00:02:32 |
----------------------------------------------------------------------------

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


Statistiken
----------------------------------------------------------
        112  recursive calls
         23  db block gets
      28684  consistent gets
      28726  physical reads
       4592  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         11  sorts (memory)
          0  sorts (disk)
          1  rows processed

Der Index wird vom cbo offenbar kalt lächelnd ignoriert, aber immerhin liefert autotrace auch die Begründung dafür: SQL plan baseline "SYS_SQL_PLAN_8713193eb8066074" used for this statement.

Details zur Baseline liefert die View DBA_SQL_PLAN_BASELINES:

select *
  from DBA_SQL_PLAN_BASELINES
 where PLAN_NAME = 'SYS_SQL_PLAN_8713193eb8066074';

-- hier nur die Spalte SQL_HANDLE
SQL_HANDLE
------------------------
SYS_SQL_0c6637d282f8ddb7

Mit Hilfe der Prozedur evolve_sql_plan_baseline aus dem Package dbms_spm kann ich dann prüfen, ob eine geeigneterer Plan zum Statement existiert und diesen gegebenenfalls akzeptieren:

If interrogated by the user (parameter verify = 'YES'), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = 'YES').

Der Vergleich der Pläne fällt (natürlich) zugunsten des indizierten Zugriffs aus:

SQL> r
  1  SELECT dbms_spm.evolve_sql_plan_baseline(
  2                sql_handle => 'SYS_SQL_9f943b5f8713193e',
  3                plan_name  => '',
  4                time_limit => 10,
  5                verify     => 'yes',
  6                commit     => 'yes'
  7              )
  8*   FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_9F943B5F8713193E',PLAN_NA
--------------------------------------------------------------------------------

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

Inputs:
-------
  SQL_HANDLE = SYS_SQL_9f943b5f8713193e
  PLAN_NAME  =
  TIME_LIMIT = 10
  VERIFY     = yes
  COMMIT     = yes

Plan: SYS_SQL_PLAN_8713193efefc82f9
-----------------------------------
  Plan was verified: Time used 1,77 seconds.
  Passed performance criterion: Compound improvement ratio >= 25,65
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):            1469            104             14,13
  CPU Time(ms):                1456            103             14,14
  Buffer Gets:                28587           1110             25,75
  Disk Reads:                 27260           1099              24,8
  Direct Writes:                  0              0
  Fetches:                      608             32                19
  Executions:                     1              1

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

Offenbar gibt's da eine Verbesserungsmöglichkeit, weil der neue Plan (mit Index-Zugriff) effizienter ist, so dass der neue Plan akzeptiert wird.

SQL> SELECT * 
       FROM table(dbms_xplan.display_sql_plan_baseline
                             ('SYS_SQL_0c6637d282f8ddb7', NULL, 'basic'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_0c6637d282f8ddb7
SQL text: SELECT /* baseline */ COUNT(*) FROM test_baseline
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7b8066074
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2118400422

--------------------------------------------
| Id  | Operation          | Name          |
--------------------------------------------
|   0 | SELECT STATEMENT   |               |
|   1 |  SORT AGGREGATE    |               |
|   2 |   TABLE ACCESS FULL| TEST_BASELINE |
--------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_82f8ddb7fefc82f9
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3816238667

---------------------------------------------------
| Id  | Operation             | Name              |
---------------------------------------------------
|   0 | SELECT STATEMENT      |                   |
|   1 |  SORT AGGREGATE       |                   |
|   2 |   INDEX FAST FULL SCAN| TEST_BASELINE_IDX |
---------------------------------------------------

Wenn ich die Testquery jetzt noch einmal ausführe, verwendete sie nun auch den Index, da der verbesserte Plan über den Prozeduraufruf akzeptiert wurde:

SQL> SELECT /* test */ COUNT(*) FROM test_baseline;

Abgelaufen: 00:00:00.29

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

-----------------------------------------------------------------------------------
| Id  | Operation             | Name              | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |     1 |   430   (2)| 00:00:07 |
|   1 |  SORT AGGREGATE       |                   |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| TEST_BASELINE_IDX |   814K|   430   (2)| 00:00:07 |
-----------------------------------------------------------------------------------

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


Statistiken
----------------------------------------------------------
         20  recursive calls
         14  db block gets
       1187  consistent gets
        161  physical reads
       5916  redo size
        342  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Ich hatte mit diesem Test ein paar Schwierigkeiten, aber nach einem Blick in Tim Halls Erläuterungen wurde der Fall deutlich klarer.

SQL Server und Oracle

Jonathan Lewis hat für das simple-talk journal eine interessante Reihe von Artikeln begonnen, in denen er SQL Server Konzepte mit den entsprechenden Oracle-Konzepten vergleicht (was mir deshalb gut gefällt, weil ich den SQL Server auch durch den idealtypischen Vergleich mit Oracle zu begreifen versuche):

• Oracle to SQL Server: Crossing the Great Divide, Part 1: behandelt terminologische Fragen, die Rolle von Instanzen, Datenbanken und Schemas, die Verwendung von Daten- und Logdateien und diverse spezifische Oracle-Konstrukte (dual, rownum, CTAS) und ihre Entsprechungen im SQL Server.
• Oracle to SQL Server: Crossing the Great Divide, Part 2: behandelt verschiedene Wege zur Erzeugung von Testdaten mit bestimmten Eigenschaften (wie Jonathan Lewis sie üblicherweise für Oracle-Tests verwendet)
• Oracle to SQL Server, Crossing the Great Divide, Part 3: erläutert die Details der Datenspeicherung (Pages, Extents) für Tabellen und Indizes. Ein Ergebnis der Untersuchung ist, dass die Verwendung von Heap Tabellen (also Tabellen ohne clustered index) bei Updates anscheinend zu Problemen führen kann, da mit Row-Migration zu rechnen ist (weil der SQL Server offenbar keinen Platz für Datenänderungen reserviert). Vermutlich sollte man einfach immer einen cli definieren, da sein Fehlen an verschiedenen Stellen unerfreuliche Effekte hervorrufen kann.
• Oracle to SQL Server: Putting the Data in the Right Place: erläutert das Verhalten von clustered indexes hinsichtlich der Speichernutzung.

In allen Teilen finden sich interessante Analysescripts, die auf den internen DMVs basieren. Im Rahmen der Erläuterungen hat sich auch meine Erinnerung bestätigt, dass eine Tabelle mit clustered index im SQL Server nur ein physikalisches Objekt umfasst: die Blattknoten des Index enthalten die pages der Tabelle.