Document Type | Technical Information
Category | Monitoring/Inspection
Document Number | TMOTI018
Overview
In Tibero6, you can extract a PP Dump to check the SQL execution plan (Physical Plan, hereafter PP).
This article guides you through two methods: extracting based on already executed query information, and extracting PP information while executing the query.
Method
Extract PP Dump Based on Executed Query Information
Use the following query to retrieve the SQL's
sql_id and child_number of the executed SQL.CautionThere must be at least two results; if only one is retrieved, that PP has already been Cache Out.
SQL> select sql_id, child_number from v$sql where sql_text like '%XXXXXXX%';
Based on the retrieved information above, perform the PP Dump.
SQL> alter system dump ppc 'SQL_ID' CHILD_NUMBER;
Example
SQL> alter system dump ppc '1xv4yk2n77bx0' 18014398509488946 ;
Extract PP Dump While Executing a Query
Activate the Autotrace feature.
SQL> set autot trace exp
Execute the query you want to trace.
SQL> Run SQL
Check the SQL_ID and CHILD_NUMBER information from the Autotrace data.
Based on the information confirmed above, extract the PP Dump.
SQL> alter system dump ppc 'SQL_ID' child_number ;
Example
SQL> alter system dump ppc '42' 1801439488946;