Erzeugen von Testfällen mit DBMS

DBA Community - Juni 2011

Erzeugen von Testfällen mit DBMS_SQLDIAG

von Ulrike Schwinn, ORACLE Deutschland B.V. & Co. KG

Der Erfolg beim Tunen von SQL Statements oder gar die Behebung von aufgetretenen Fehlern bei der SQL-Ausführung ist häufig davon abhängig, einen reproduzierbaren Testfall zur Verfügung zu stellen. Dies kann eine sehr umständliche und zeitaufwändige Aufgabe sein, da nicht nur die Abfrage und die Tabelle selbst, sondern häufig auch zugehörige Objekte wie Synonyme, Indizes usw. erzeugt werden müssen. Der sogenannte Test Case Builder (auch TCB), mit Teilfunktionen auch schon in 10.2.0.4 vorhanden, bietet hier eine Möglichkeit, diesen Vorgang stark zu vereinfachen.

Die Idee dahinter ist, alle Informationen, die zu einem SQL Statement gehören, automatisch zu speichern und die Möglichkeit zu bieten, einen Import dieser Informationen in eine weitere Datenbank zu ermöglichen. Dies kann beispielsweise dann sinnvoll sein, wenn eine Analyse durch spezielle Consultants oder durch den Oracle Support erforderlich ist. Die Informationen, die TCB exportiert und importiert, umfassen nicht nur das Statement, sondern auch die notwendigen Tabellen und Indizes, PL/SQL Objekte, Optimizer Statistiken und die relevanten Initialisierungsparameter.

Implementiert ist der Test Case Builder über das Package DBMS_SQLDIAG. Teilfunktionen sind auch graphisch über die Support Workbench des Enterprise Managers zu nutzen. Dabei enthält DBMS_SQLDIAG eine Vielzahl von Funktionen und Prozeduren, die nicht nur helfen einen Testfall aufzubauen, sondern auch Diagnosen durchzuführen und sogar SQL Patches einzuspielen. In diesem Tipp konzentrieren wir uns auf das Exportieren und Importieren einer Testumgebung und die aktuelle Funktionalität in der 11.2.0.2 Datenbank.

Wie schon erwähnt, besteht das Package DBMS_SQLDIAG aus einer Vielzahl von Unterprogrammen. Die folgende Liste beschreibt die relevanten Programme für den Export und den Import:

EXPORT_SQL_TESTCASE zum Exportieren der Testumgebung unter Angabe von SQL_ID, SQL_Text usw.
EXPORT_SQL_TESTCASE_DIR_BY_INC zum Exportieren einer Testumgebung unter Angabe einer "Incident Id"
EXPORT_SQL_TESTCASE_DIR_BY_TXT zum Exportieren einer Testumgebung unter Angabe eines SQL-Texts
IMPORT_SQL_TESTCASE zum Import einer mit TCB exportierten Testumgebung

Im Beispiel nutzen wir folgende Abfrage auf die Tabelle SALES, die im Demo-Schema SH zu finden ist. Der ausführende User mu� DBA Privilegien besitzen, allerdings sollte nicht der User SYS genutzt werden.

SELECT * FROM sales WHERE amount_sold > 30 ORDER BY prod_id;

Damit die Dateien exportiert werden können, ist ein logisches Directory notwendig. Es lautet hier DIAGDIR und verweist auf ein existierendes physikalisches Verzeichnis. Das Exportieren der Testumgebung dieser Abfrage kann dann folgendermassen aussehen:

declare 
   mytestcase clob; 
   begin 
     dbms_sqldiag.export_sql_testcase 
      (directory  => 'DIAGDIR', 
       sql_text   => 'select * FROM sales WHERE amount_sold > 30 ORDER BY prod_id',
       user_name  => 'SH', 
       exportData => TRUE, 
       testcase   => mytestcase); 
end; 
/

In unserem Fall wird der Parameter "exportData" auf TRUE (der Standard ist FALSE) eingestellt, damit auch die Daten und nicht nur die Definitionen exportiert werden. Je nach Umfang der Informationen dauert die Ausführung von DBMS_SQLDIAG.EXPORT_SQL_TESTCASE einige Minuten. Im Verzeichnis DIAGDIR finden sich nach der erfolgreichen Ausführung 14 unterschiedliche Dateien im XML, SQL oder DMP Format. Bis auf "xxxxmain.xml" sind die Dateien für den internen Gebrauch oder als Informationsquelle gedacht und müssen in der Regel vor der eigenen Nutzung editiert werden. Die Datei-Namen verschiedener Durchläufe sind unterschiedlich; der Präfix innerhalb eines Durchlaufs (hier "oratcb4_00205EE90001") bleibt allerdings erhalten. Folgende Liste gibt einen kurzen Überblick und eine Beschreibung der Dateien.

oratcb4_00205EE90001dpexp.dmp	EXPDP Dump-Datei der genutzten Objekte
oratcb4_00205EE90001dpexp.log	EXPDP Logdatei
oratcb4_00205EE90001README.txt	Beschreibung des Import
oratcb4_00205EE90001dpexp.sql	internes EXPDP-Skript für TCB
oratcb4_00205EE90001dpimp.sql	internes IMPDP-Skript für TCB
oratcb4_00205EE90001sql.xml	das SQL Statement
oratcb4_00205EE90001ts.xml	Tablespace-Liste
oratcb4_00205EE90001ol.xml	Objekt-Liste
oratcb4_00205EE90001xpls.sql	Explain Plan
oratcb4_00205EE90001xplo.sql	Explain Plan mit Outline Hints
oratcb4_00205EE90001xplf.sql	Explain Plan mit DBMS_SQLDIAG
oratcb4_00205EE90001ssimp.sql	Import der Systemstatistiken
oratcb4_00205EE90001xpl.txt	Explain Plan Logdatei
oratcb4_00205EE90001main.xml	Metadatendatei

Nun kann man mit einem einfachen Aufruf die Informationen in eine andere Datenbank importieren. Dazu wird als Argument der Name eines logischen Directories der Datenbank (hier auch DIAGDIR) und der Name der "xxxxmain.xml" Datei angegeben. Auch hier ist ein User mit DBA Privilegien nötig. Verwendet man ein leeres Schema z.B. TESTUSER, um die Informationen in die gleiche Datenbank zu importieren, sieht der Aufruf folgendermassen aus.

connect testuser/password

execute dbms_sqldiag.import_sql_testcase(directory=>'DIAGDIR',filename=>'oratcb1_004C1E910001main.xml');

-- Ueberpruefung der Objekte
SQL> SELECT object_name, object_type
     FROM user_objects
     ORDER BY 2;


OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
SALES_PROMO_BIX           INDEX
SALES_PROD_BIX            INDEX
SALES_CUST_BIX            INDEX
FW_PSC_S_MV_SUBCAT_BIX    INDEX
FW_PSC_S_MV_CHAN_BIX      INDEX
FW_PSC_S_MV_PROMO_BIX     INDEX
FW_PSC_S_MV_WD_BIX        INDEX
I_SNAP$_MYMV              INDEX
SALES_TIME_BIX            INDEX
SALES_CHANNEL_BIX         INDEX
SALES_PROMO_BIX           INDEX PARTITION
...
MYMV                      MATERIALIZED VIEW
FWEEK_PSCAT_SALES_MV      TABLE
CAL_MONTH_SALES_MV        TABLE
SALES                     TABLE
MYMV                      TABLE
SALES                     TABLE PARTITION
...
183 rows selected.

-- Ueberpruefung der Statistiken
SQL> SELECT table_name, last_analyzed 
     FROM user_tab_statistics;

TABLE_NAME                     LAST_ANAL
------------------------------ ---------
CAL_MONTH_SALES_MV             20-APR-11
FWEEK_PSCAT_SALES_MV           20-APR-11
MYMV                           20-APR-11
SALES                          20-APR-11
SALES                          20-APR-11
...

Nach dem Import sind die notwendigen Objekte und die entsprechenden Statistiken im Schema TESTUSER vorhanden. Die Testumgebung ist erstellt!

Nun können die importierten Objekte und die zusätzlichen Informationen in den Dateien (siehe Beschreibung oben) genutzt werden, um das Statement auszuführen. Das Statement selbst befindet sich in der oratcb4_00205EE90001sql.xml Datei. Die Outline Hints aus dem Skript oratcb4_00205EE90001xplo.sql geben beispielsweise zusätzliche Informationen zur Ausführung in der Testumgebung. Folgendes Beispiel zeigt eine mögliche Anwendung.

SQL> set autotrace on explain
SQL> SELECT /*+ BEGIN_OUTLINE_DATA
                BITMAP_TREE(@"SEL$1" "SALES"@"SEL$1" AND (("SALES"."PROMO_ID")))
                OUTLINE_LEAF(@"SEL$1")
                NO_PARALLEL
                ALL_ROWS
                OPT_PARAM('optimizer_index_cost_adj' 1)
                DB_VERSION('11.2.0.2')
                OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
                IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ * FROM sales
                WHERE amount_sold > 30 ORDER BY prod_id;

...

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
       141        791 14-NOV-01          4        999             1       31.26
       141       2621 14-NOV-01          4        999             1       31.26
       141       3289 15-OCT-01          4        999             1       31.26

507466 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 899219946

--------------------------------------------------------------------------------
-----------------------------------------------

| Id  | Operation                           | Name            | Rows  | Bytes |T
empSpc| Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
-----------------------------------------------

|   0 | SELECT STATEMENT                    |                 |   503K|    13M|
      |  4347   (2)| 00:00:01 |       |       |

|   1 |  SORT ORDER BY                      |                 |   503K|    13M|
   23M|  4347   (2)| 00:00:01 |       |       |

|   2 |   PARTITION RANGE ALL               |                 |   503K|    13M|
      |   323  (11)| 00:00:01 |     1 |    28 |

|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |   503K|    13M|
      |   323  (11)| 00:00:01 |     1 |    28 |

|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |       |       |
      |            |          |       |       |

|   5 |      BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX |       |       |
      |            |          |     1 |    28 |

--------------------------------------------------------------------------------
-----------------------------------------------


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

   3 - filter("AMOUNT_SOLD">30)

Möchte man mehr zu den der Nutzung von DBMS_SQLDIAG erfahren, findet man weitere Informationen im Oracle� Database PL/SQL Packages and Types Reference 11g Release 2 (11.2) Handbuch.

Zurück zur Übersicht

Zurück zur Community-Seite