Get the Execution Plan from/using SQL Tuning Set (STS)

A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:

• A set of SQL statements
• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
• Associated execution plans and row source statistics for each SQL statement (optional)

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning.

Manage SQL tuning sets using Enterprise Manager. If Enterprise Manager is unavailable, then you can manage SQL tuning sets using the DBMS_SQLTUNE package procedures.

DISPLAY_SQLSET:

DBMS_XPLAN.DISPLAY_SQLSET fuction provides the way to display the plan of a statement stored in the SQL tuning set. To use the DISPLAY_SQLSET functionality, the calling user must have SELECT privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS. All these privileges are automatically granted as part of the SELECT_CATALOG role.

Syntax:

DBMS_XPLAN.DISPLAY_SQLSET(

   sqlset_name        IN  VARCHAR2, 

   sql_id                 IN  VARCHAR2,

   plan_hash_value  IN NUMBER := NULL,

   format                IN  VARCHAR2  := 'TYPICAL', 

   sqlset_owner       IN  VARCHAR2  := NULL)

  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Examples:

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqhqbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLAP_optimization_001":

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       'OLTP_optimization_001','gwp663cqhqbf', 3693697075));

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLAP_optimization_001','gwp663cqhqbf'));

To display runtime statistics for the SQL statement included in the preceding statement:

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLAP_optimization_001', 'gwp663cqhqbf', NULL, 'ALLSTATS LAST');

-Asifkhan P.