CREATE TABLE has lot of options to go with it; it is hard to remember all of them. Most people will get lost while reading the rail road diagrams. It is fairly quick to create the table in its simple form and obtain all the parameters associated with it. Once the table is created in …
Get a complete CREATE TABLE definition using DBMS_METADATA package Read More »
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table. Column Datatype Description Notes STATEMENT_ID VARCHAR2(30) Value …
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data. This package enables the collection of profiler (performance) data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus …
How to identify PL/SQL performance bottlenecks using DBMS_PROFILER Read More »
To display the execution plan of all children associated with the SQL ID; you would have to call the table function in below format. Following steps will load two children cursors in the cursor cache By querying V$SQL, We can see that SQL_ID gfvh124qr5nzy has two childrens, 0 and 1; Let us execute DBMS_XPLAN.DISPLAY_CURSOR with …