Overview of SQL Plan management

Overview of SQL Plan Management

SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build aSQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.

The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time Because only accepted plans are in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.

When automatic plan capture is enabled, the database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer. The plan history includes relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment.

To enable automatic plan capture, set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is FALSE.

To load plans from a SQL tuning set, use the LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package. The following example loads the plans stored in the SQL tuning set named tset1:

DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1'); END; / 

To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before using theLOAD_PLANS_FROM_SQLSET function as described in this section.

like this

BEGIN   DBMS_SQLTUNE.CREATE_SQLSET(     sqlset_name => 'my_sql_tuning_set',      description  => 'I/O intensive workload'); END; / 

-- Load the SQL set from the Automatic Workload Repository (AWR). DECLARE   l_cursor  DBMS_SQLTUNE.sqlset_cursor; BEGIN   OPEN l_cursor FOR     SELECT VALUE(p)     FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (                     765,  -- begin_snap                     766,  -- end_snap                     NULL, -- basic_filter                     NULL, -- object_filter                     NULL, -- ranking_measure1                     NULL, -- ranking_measure2                     NULL, -- ranking_measure3                     NULL, -- result_percentage                     10)   -- result_limit                   ) p;    DBMS_SQLTUNE.load_sqlset (     sqlset_name     => 'my_sql_tuning_set',     populate_cursor => l_cursor); END; / 

To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. In the following example, Oracle Database loads the plans located in the shared SQL area for the SQL statement identified by its sql_id:

DECLARE   my_plans PLS_INTEGER; BEGIN   my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd'); END; 

/

To view the plans stored in the SQL plan baseline for a given statement, use the DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package. The following example displays one or more execution plans for the specified SQL statement, specified by the handle (sql_handle):

SELECT * FROM TABLE(      DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(          sql_handle=>'SYS_SQL_209d10fabbedc741',          format=>'basic')); 

Alternatively, you can display a single plan by supplying a plan name (plan_name).

You can also display SQL plan baseline information using a SELECT statement directly on the DBA_SQL_PLAN_BASELINES view, as shown in the following example:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED  FROM   DBA_SQL_PLAN_BASELINES;   SQL_HANDLE                PLAN_NAME                      ENA  ACC    FIX ------------------------------------------------------------------------ SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741a57b5fc2  YES  NO     NO SYS_SQL_209d10fabbedc741  SYS_SQL_PLAN_bbedc741f554c408  YES  YES    NO

ENABLED : Bu SQL için yeni planlar oluşturulduğunda Baseline 'a alınır.

ACCEPTED : Kullanılan exec plan'ı tutan Baseline

FIXED : Artık bu SQL için yeni plan aranmaz. Yeni plan oluştuğunda base line'a alınmaz 

The optimizer does not add new plans to a fixed SQL plan baseline. Because the optimizer does not automatically add new plans, the database does not evolve a fixed SQL plan baseline when you execute DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. However, you can evolve a fixed SQL plan baseline by manually loading new plans into it from the shared SQL area or a SQL tuning set.

During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.

Each time the database compiles a SQL statement, the optimizer does the following:

1. Uses a cost-based search method to build a best-cost plan

2. Tries to find a matching plan in the SQL plan baseline

3. Does either of the following depending on whether a match is found:

   • If found, then the optimizer proceeds using the matched plan

   • If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost

     
     

     
     

SQL MONITORING

Oracle 11g automatically monitors SQL statements if they are run in parallel, or consume 5 or more seconds of CPU or I/O in a single execution. This allows resource intensive SQL to be monitored as it is executing, as well as giving access to detailed information about queries once they are complete.

SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL', and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.

The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.

select /*+ monitor */. ....

Monitored statements can be identified using the V$SQL_MONITOR view.

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.

When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.

Column Datatype Description
KEY NUMBER Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
STATUS VARCHAR2(19) SQL execution status:
QUEUED - SQL statement is queued
EXECUTING - SQL statement is still executing
DONE (ERROR) - Execution terminated with an error
DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
DONE (ALL ROWS) - Execution terminated and all rows were fetched
DONE - Execution terminated (parallel execution)
USER# NUMBER User ID of the database user who issued the SQL being monitored
USERNAME VARCHAR2(30) User name of the database user who issued the SQL being monitored
MODULEFoot 1 VARCHAR2(48) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTIONFootref 1 VARCHAR2(32) Name of the executing action when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
SERVICE_NAME VARCHAR2(64) Service name of the user session
CLIENT_IDENTIFIER VARCHAR2(64) Client identifier from the user session
CLIENT_INFO VARCHAR2(64) Client information for the user session
PROGRAM VARCHAR2(48) Name of the OS program that issued the monitored SQL
PLSQL_ENTRY_OBJECT_ID NUMBER Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_OBJECT_ID NUMBER Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
PLSQL_SUBPROGRAM_ID NUMBER Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
FIRST_REFRESH_TIME DATE Time when monitoring of the SQL statement started, generally a few seconds after execution start time
LAST_REFRESH_TIME DATE Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
REFRESH_COUNT NUMBER Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
SID NUMBER Session identifier executing (or having executed) the SQL statement being monitored
PROCESS_NAME VARCHAR2(5) Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
SQL_ID VARCHAR2(13) SQL identifier of the statement being monitored
SQL_TEXT VARCHAR2(2000) Up to the first 2000 characters of the text of the SQL being monitored
IS_FULL_SQLTEXT VARCHAR2(1) Indicates whether the SQL_TEXT column has the entire SQL text (Y) or not (N)
SQL_EXEC_START DATE Time when the execution started
SQL_EXEC_ID NUMBER Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
SQL_PLAN_HASH_VALUE NUMBER SQL Plan hash value
EXACT_MATCHING_SIGNATURE NUMBER Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
FORCE_MATCHING_SIGNATURE NUMBER Same as EXACT_MATCHING_SIGNATURE but literals in the SQL text are replaced by binds
SQL_CHILD_ADDRESS RAW(4 | 8) Address of the child cursor (can be used with SQL_ID to join with V$SQL)
SESSION_SERIAL# NUMBER Session serial number executing the statement being monitored
PX_IS_CROSS_INSTANCE VARCHAR2(1) Indicates whether the SQL statement ran parallel across multiple instances (Y) or not (N)
PX_MAXDOP NUMBER Maximum degree of parallelism for any plan operation executed on behalf of the monitored SQL
PX_MAXDOP_INSTANCES NUMBER Number of database instances touched at the maximum degree of parallelism
PX_SERVERS_REQUESTED NUMBER Total number of parallel execution servers requested to execute the monitored SQL
PX_SERVERS_ALLOCATED NUMBER Actual number of parallel execution servers allocated to execute the query
PX_SERVER# NUMBER Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
PX_SERVER_GROUP NUMBER Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
PX_SERVER_SET NUMBER Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
PX_QCINST_ID NUMBER Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
PX_QCSID NUMBER Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
ERROR_NUMBER VARCHAR2(40) Error number encountered in case a SQL fails to execute successfully (for example, 932 in case of ORA-00932)
ERROR_FACILITY VARCHAR2(4) Error facility in case a SQL fails to execute successfully (for example, ORA in case of ORA-00932)
ERROR_MESSAGE VARCHAR2(256) Detailed error message displayed corresponding to the error number and error facility when a SQL fails to execute successfully
BINDS_XML CLOB Information about bind variables used with the SQL, such as name, position, value, data type, and so on (stored in XML format)
OTHER_XML CLOB Additional information about SQL execution stored in XML format
ELAPSED_TIME NUMBER Elapsed time (in microseconds); updated as the statement executes
QUEUING_TIME NUMBER Duration of time (in microseconds) spent by SQL in the statement queue
CPU_TIME NUMBER CPU time (in microseconds); updated as the statement executes
FETCHES NUMBER Number of fetches associated with the SQL statement; updated as the statement executes
BUFFER_GETS NUMBER Number of buffer get operations; updated as the statement executes
DISK_READS NUMBER Number of disk reads; updated as the statement executes
DIRECT_WRITES NUMBER Number of direct writes; updated as the statement executes
IO_INTERCONNECT_BYTES NUMBER Number of I/O bytes exchanged between Oracle Database and the storage system
PHYSICAL_READ_REQUESTS NUMBER Number of physical read I/O requests issued by the monitored SQL
PHYSICAL_READ_BYTES NUMBER Number of bytes read from disks by the monitored SQL
PHYSICAL_WRITE_REQUESTS NUMBER Number of physical write I/O requests issued by the monitored SQL
PHYSICAL_WRITE_BYTES NUMBER Number of bytes written to disks by the monitored SQL
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds); updated as the statement executes
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds); updated as the statement executes
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds); updated as the statement executes

SET LINESIZE 200 COLUMN sql_text FORMAT A80  SELECT sql_id, status, sql_text FROM   v$sql_monitor WHERE  username = 'SCOTT';

SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF  SPOOL /host/report_sql_monitor.htm SELECT DBMS_SQLTUNE.report_sql_monitor(   sql_id       => '526mvccm5nfy4',   type         => 'HTML',   report_level => 'ALL') AS report FROM dual; SPOOL OFF

Examples of the output for each available TYPE are displayed below.

• TEXT
• HTML
• XML

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

REPORT_SQL_MONITOR_LIST

The REPORT_SQL_MONITOR_LIST function was added in Oracle 11g Release 2 to generate a summary screen, similar to that on the "Monitored SQL Executions" page of Enterprise Manager.

SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF  SPOOL /host/report_sql_monitor_list.htm SELECT DBMS_SQLTUNE.report_sql_monitor_list(   type         => 'HTML',   report_level => 'ALL') AS report FROM dual; SPOOL OFF

V$SQL_PLAN_MONITOR displays plan level monitoring statistics for each SQL statement found in V$SQL_MONITOR. Each row in V$SQL_PLAN_MONITOR corresponds to an operation of the execution plan being monitored. As with V$SQL_MONITOR, statistics exposed in V$SQL_PLAN_MONITOR are generally updated every second when the statement executes. These statistics are recycled on the same basis as V$SQL_MONITOR.