Oder wie löst man das Henne und Ei Problem?
Der Oracle CBO – Cost Based Optimizer – bietet eine Möglichkeit, ihm beim Arbeiten zuzusehen. Gerade die ersten Versionen – der CBO wurde mit Oracle 7.0 eingeführt und seit diesem Zeitpunkt laufend verbessert – lieferten oft nicht die erhofften Ausführungspläne. Um herauszufinden, wo der CBO falsch abbiegt, wurde der EVENT 10053 – auch parsing event – eingeführt und konnte wie folgt genutzt werden:
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
select /* TEST1 */ d.department_name, d.department_id,
e.employee_id, e.last_name
from departments d join employees e
on (d.department_id = e.department_id)
where e.first_name like 'A%';
ALTER SESSION SET EVENTS '10053 trace name context forever, off';Dadurch wurde ein (Anfangs USER_DUMP_DEST, jetzt die DIAG_DEST der Datenbank im trace Verzeichnis) Tracefile erzeugt, in dem der CBO genau protokolliert, was er sich zu dem Statement gedacht hat. In aktuellen Oracle Versionen kann man sich den Namen des korrekten Tracefiles auch direkt auslesen:
SELECT value
FROM v$diag_info
WHERE name='Default Trace File';
VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db19cdb/DB19CDB/trace/DB19CDB_ora_19058.trcDas Erzeugen des Parsing Traces ist also nicht ganz einfach. Die mit Oracle 11g eingeführte Variante, bei der man im Vorfeld sagt, für welche SQL_ID ein Parsing Trace durchgeführt werden soll, ist nicht gerade praktikabel.
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:9jq15wr8r4v7d]';
select /* TEST1 */ d.department_name, d.department_id, e.employee_id, e.last_name
from departments d join employees e on (d.department_id = e.department_id)
where e.first_name like 'A%';
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*] off'; Bei beiden Methoden muss man das Statement ausführen – und noch viel schlimmer: Wenn das Statement schon geparsed vorliegt, überspringt der CBO das Parsing und damit gibt es auch kein Tracefile! Läuft man in dieses Problem, kann man entweder den gesamten Shared Pool flushen (empfehlen wir nicht!) oder zumindest das entsprechende Statement aus dem Shared Pool entfernen.
Zuerst Informationen zum Statement auslesen:
select sql_id, ADDRESS, HASH_VALUE, substr(sql_text,1,30) as SQL_TEXT
from v$sqlarea
where sql_text like 'select /* TEST1 */%';
SQL_ID ADDRESS HASH_VALUE SQL_TEXT
------------- ---------------- ---------- -------------------------------
9jq15wr8r4v7d 000000017E25CBA0 3513937133 select /* TEST1 */ d.departmenDann mittels DBMS_SHARED_POOL.PURGE eliminieren:
exec sys.DBMS_SHARED_POOL.PURGE ('000000017E25CBA0, 3513937133', 'C'); Leider verliert man damit alle anderen Informationen aus V$SQL, V$SQL_PLAN, V$SQLAREA, etc. die man möglicherweise für eine weitere Analyse noch benötigen würde.
Wäre es nicht super, wenn wir uns Informationen zu einem Statement aus V$SQLAREA heraussuchen und dann einen Parsing Trace machen könnten? Ja, das geht auch wirklich ab Oracle 12c! Das Package DBMS_SQLDIAG enthält eine DUMP_TRACE Prozedur. Allerdings findet man dazu in der Oracle Dokumentation keine Informationen.
Das erste Problem: für die Nutzung benötigt man eine Oracle Enterprise Edition sowie das Diagnostic Pack. Der Benutzer muss die ADVISOR (oder DBA) Rolle zugewiesen haben, um das Package DBMS_SQLDIAG nutzen zu dürfen – siehe: Oracle 19c PL/SQL Packages and Types Reference.
desc DBMS_SQLDIAG
...
PROCEDURE DUMP_TRACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SQL_ID VARCHAR2 IN
P_CHILD_NUMBER NUMBER IN DEFAULT
P_COMPONENT VARCHAR2 IN DEFAULT
P_FILE_ID VARCHAR2 IN DEFAULT
...- P_SQL_ID … gewünschte SQL_ID
- P_CHILD_NUMBER … Child Number aus V$SQL
- P_COMPONENT … Entweder Optimizer oder Compiler
- P_FILE_ID … ein beliebiger String, der ein Postfix im Tracefile Namen wird.
Hört sich ja perfekt an! Wir suchen uns das Statement aus V$SQL (oder V$SQLAREA) heraus:
select sql_id, child_number, ADDRESS, HASH_VALUE,
substr(sql_text,1,30) as SQL_TEXT
from v$sql
where sql_text like 'select /* TEST1 */%';
SQL_ID CHILD_NUMBER ADDRESS HASH_VALUE SQL_TEXT
------------- ------------ ---------------- ---------- --------------------
9jq15wr8r4v7d 0 000000017E25CBA0 3513937133 select /* TEST1 */und erzeugen uns mit diesen Informationen einen Optimizer Parsing Trace:
execute DBMS_SQLDIAG.DUMP_TRACE( -
p_sql_id=>'9jq15wr8r4v7d', -
p_child_number=>0, -
p_component=>'Optimizer', -
p_file_id=>'TRC10053');schnell noch den Tracefilenamen ermitteln:
SELECT value
FROM v$diag_info
WHERE name='Default Trace File';
VALUE
-----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db19/DB19/trace/DB19_ora_21573_TRC10053.trcund am Server im Filesystem das File ansehen!
Abhängig von der Oracle Version kann es sein, dass das File nicht erzeugt wird. Das liegt daran, dass das geparste Statement schon in V$SQL_PLAN liegt und der Optimizer sich die Arbeit nicht mehr macht.
Oracle hat dies als BUG anerkannt:
Bug 19683821 : 10053 TRACE IS NOT GETTING GENERATED WHEN THE SQL IS GOING FOR A SOFT PARSE.
Leider ist es ein interner Bug und somit nicht einsehbar…
Es gibt aber einen Workaround, der immer funktionieren sollte:
execute DBMS_SQLDIAG.DUMP_TRACE( -
p_sql_id=>'9jq15wr8r4v7d', -
p_child_number=>0, -
p_component=>'Compiler', -
p_file_id=>'TRACE_10053'); Einfach beim Aufruf bei der Komponente nicht Optimizer sondern Compiler angeben. In diesem Fall wird das Statement offensichtlich wirklich neu geparsed. So wie es aussieht, umgeht die „Componente = Compiler“ die Prüfung, ob schon ein Ausführungsplan vorliegt, und erzeugt diesen auf alle Fälle.
Referenzen
- Oracle 19c PL/SQL Packages and Types Reference
- How to Obtain Tracing of Optimizer Computations (EVENT 10053) (Doc ID 225598.1)
- LSH/DMW: Flush Bad SQL Plan from Shared Pool (Doc ID 2438768.1)
- Using DBMS_SQLDIAG.DUMP_TRACE on a Parsed SQL ID Does not Generate any Trace Entries (Doc ID 2909982.1)