How to mark an existing SQL Plan baseline as fixed

Thanks a lot to Tim Hall for providing this simple example:

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_7ff180a4583f257d',
    plan_name       => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

The details for the plan baselines can be found like this:

SELECT sql_handle,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';

Result:

SQL_HANDLE	PLAN_NAME	ORIGIN	ENABLED	ACCEPTED	FIXED
SQL_7ff180a4583f257d	SQL_PLAN_7zwc0njc3y9bx2c993bf4	AUTO-CAPTURE	YES	YES	YES

After fixing the plan, all the plan alternatives for the SQL ID can be seen below:

SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('2mympbsn3r4rk')
ORDER BY sql_id,LAST_ACTIVE_TIME DESC;

Result:

last active	SQL_ID	PLAN_HASH_VALUE	BASELINE	SQL_PROFILE	IS_RESOLVED_ADAPTIVE_PLAN	Child Num	ELAPSED_TIME	EXECUTIONS	duration (sec)	sec per exe
04.12.2020 09:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			1	612838711	116	613	5,3
04.12.2020 09:49	2mympbsn3r4rk	3102497174	SQL_PLAN_7zwc0njc3y9bx2c993bf4		Y	2	203961	191	0	0
04.12.2020 08:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			0	2247452482	30	2247	74,9

How to find the number of executions for a specific SQL ID during a day

This query will gather data from DBA_HIST_ACTIVE_SESS_HISTORY for a specific day, and count the number of executions within that day:

select to_char(sample_time,'dd.mm.yyyy hh24') "day", count(*) "num executions"
from DBA_HIST_ACTIVE_SESS_HISTORY 
where sql_id = '7x0v1s9aq4y9t'
and to_date(to_char(sample_time,'dd.mm.yyyy')) = '05.08.2019'
group by to_char(sample_time,'dd.mm.yyyy hh24')
order by 1 desc;

day	num executions
05.08.2019 23	302
05.08.2019 22	285
05.08.2019 21	333
05.08.2019 20	300
05.08.2019 19	393
05.08.2019 18	255
05.08.2019 17	351
05.08.2019 16	426
05.08.2019 15	450
05.08.2019 14	624
05.08.2019 13	842
05.08.2019 12	621
05.08.2019 11	503
05.08.2019 10	461
05.08.2019 09	444
05.08.2019 08	279
05.08.2019 07	148
05.08.2019 06	79
05.08.2019 05	77
05.08.2019 04	80
05.08.2019 03	3396
05.08.2019 02	3680
05.08.2019 01	2808
05.08.2019 00	123

How to gather histograms on a column of a table

Use the METHOD_OPT directive in DBMS_STATS.GATHER_TABLE_STATS to collect histograms for column ARTICLE_NAME in table SALES_Q1_RESULTS. In this example, 6 buckets are defined.
Check the view DBA_TAB_COLUMNS to confirm whether or not there are histograms collected on the column:

SELECT  NUM_DISTINCT,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM
FROM    DBA_TAB_COLUMNS 
WHERE   TABLE_NAME = 'SALES_Q1_RESULTS' 
AND     COLUMN_NAME = 'ARTICLE_NAME';

NUM_DISTINCT	DENSITY	NUM_NULLS	NUM_BUCKETS	HISTOGRAM
0	0	0	0	NONE

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => 'SH', TABNAME => 'SALES_Q1_RESULTS', METHOD_OPT => 'FOR COLUMNS SIZE 6 ARTICLE_NAME');
END;
/

SELECT  NUM_DISTINCT,DENSITY,NUM_NULLS,NUM_BUCKETS,HISTOGRAM
FROM    DBA_TAB_COLUMNS 
WHERE   TABLE_NAME = 'SALES_Q1_RESULTS' 
AND     COLUMN_NAME = 'ARTICLE_NAME';

NUM_DISTINCT	DENSITY	NUM_NULLS	NUM_BUCKETS	HISTOGRAM
7	0.166666666666667	0	6	HEIGHT BALANCED

Read more in Oracles Documentation

How to solve SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Logged in as the user owning the objects, you are getting the following error when trying to use the autotrace feature:

SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution: run the necessary scripts first:

conn / as sysdba
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
grant plustrace to scott;

conn scott/password

@$ORACLE_HOME/rdbms/admin/utlxplan.sql
exit

After this is done, the errors should no longer appear.

How to load a plan from the AWR into the SMB

To load a plan found in an AWR snapshot into the SMB, you need to add it to an STS first, then load it into the SMB.
Here is how:

1. create an empty STS:

exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');

2. Note the snapshots where your plan was recorded, and add the plans to your STS:

DECLARE
  my_cur  dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN my_cur FOR
     SELECT VALUE(x)
     FROM   TABLE( dbms_sqltune.select_workload_repository(begin_snap=>5840, 
                                                           end_snap=>5841, 
                                                           basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;

     dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/

You can now check your STS for its contents:

select sqlset_name,
     sql_id,
     plan_timestamp,
     parsing_schema_name "schema",
     plan_hash_value,round(elapsed_time/1000000) "duration in seconds",
     cpu_time,
     buffer_gets,
     disk_reads,
     executions
from   dba_sqlset_statements
where  sqlset_name = 'mysts'
;

SQLSET_NAME	SQL_ID	PLAN_TIMESTAMP	schema	PLAN_HASH_VALUE	duration in seconds	CPU_TIME	BUFFER_GETS	DISK_READS	EXECUTIONS
mysts	45wmakdh9ak9s	11.09.2018 15:22:13	SH	3827183161	0	0	0	0	0
mysts	45wmakdh9ak9s	06.09.2018 16:45:26	SH	4026264003	579	85550842	24062750	101808	3278857

We can see from the output that there are actually two different plans in the STS now, one with plan_hash_value=3827183161 and another one with plan_hash_value=4026264003.

In my case I wanted only the one with plan_hash_value=3827183161.

3. Finallly, load the contents of your STS into your SMB:

VARIABLE cnt NUMBER
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
                    sqlset_name=>'mysts', basic_filter=>'plan_hash_value=3827183161', sqlset_owner=>'SYS');

print :cnt;

How to drop an STS

exec dbms_sqltune.drop_sqlset ('mysts');

Yet another example on how to create and populate an STS

Here is how to create an STS and populate it with SQL statements matching a particular SQL ID:

exec dbms_sqltune.create_sqlset(sqlset_name=>'mysts');

DECLARE
  my_cur  dbms_sqltune.sqlset_cursor;
BEGIN
  OPEN my_cur FOR
     SELECT VALUE(x)
     FROM   TABLE( dbms_sqltune.select_cursor_cache(basic_filter=>'sql_id = ''45wmakdh9ak9s''')) x;

     dbms_sqltune.load_sqlset(sqlset_name => 'mysts', populate_cursor => my_cur);
END;
/

Check the contents of the STS:

set lines 200

col name format a20
col owner format a20
col created format a30
col statement_count format 999999999999
select name,owner,created,statement_count
from dba_sqlset
where name = 'mysts';

And check the details of the statements packed in the STS, like this:

select sql_id,plan_timestamp,parsing_schema_name,plan_hash_value,round(elapsed_time/1000000) "duration in seconds",cpu_time,buffer_gets,disk_reads,executions
from   dba_sqlset_statements
where  sqlset_name = 'mysts'
fetch first 3 rows only;

SQL_ID	PLAN_TIMESTAMP	PARSING_SCHEMA_NAME	PLAN_HASH_VALUE	duration in seconds	CPU_TIME	BUFFER_GETS	DISK_READS	EXECUTIONS
45wmakdh9ak9s	18.09.2018 23:48:21	SCOTT	4026264003	7718	1331447472	336095417	828189	40286996

How to find the number of times a query has been executed

The following query was given to me by an experienced colleague. It shows the number of times different variations of a particular query has been executed. The information is collected from V$SQL which samples the contents in the Library Cache, a part of the Shared Pool.

SELECT SQL_ID, COUNT(DISTINCT SQL_ID) NUM_SQL_IDS, COUNT(CHILD_NUMBER) ANT_CHILD, SUM(EXECUTIONS) 
FROM V$SQL
WHERE SQL_TEXT LIKE ' select count(*) TOTALS from   scott.emp%'
GROUP BY ROLLUP(SQL_ID)
;

Here is parts of the output:

SQL_ID	NUM_SQL_IDS	ANT_CHILD	SUM(EXECUTIONS)
01b71y5wmr2bx	1	1	0
081ha7tszas19	1	3	2
0bfn4yj267xy0	1	2	34
0dk42bqfz6fbr	1	3	6
g1vq03475hqgc	1	3	10
g3hky4g8vu108	1	3	9
--------- Abbreviated ---------
	274	549	1940

From the output we can see that there are a number of variations of the query being executed. The numbers are being rolled up nicely at the end, letting us know that there are 274 unique SQL_IDs, 549 different child cursors with a total of 1940 executions.

This information can certainly be useful during an effort to tune the library cache.

What are 'TCP Socket (Kgas)' Waits?

Doc ID 416451.1 "What are 'TCP Socket (Kgas)' Waits?" from Oracle support explains:

A session is waiting for an external host to provide requested data over a network socket. The time that this wait event tracks does not indicate a problem, and even a long wait time is not a reason to contact Oracle Support.

and

An application that communicates with a remote host must wait until the data it will read has arrived.
The db session cannot proceed to do anything else until the external host provides the requested data over the network socket.

The proposed "solution":

From the database point of view, these waits can safely be ignored; the wait event does not represent a database issue. It merely reports the total elapsed time for a network connection to be established or for data to arrive from over the network.


Check also Doc ID 558510.1 "WAITEVENT: "TCP Socket (KGAS)" Reference Note"

How to load a plan from the cursor cache into the SMB

set serveroutput on
VARIABLE cnt NUMBER

-- Specifying both the SQL ID and the plan hash value:
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '5abzqhtfcvr73' -
                    ,plan_hash_value =>7104589 -
                    ,fixed => 'YES' -
                    ,enabled=>'YES');


-- without a specific plan hash value:
EXECUTE :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
                    sql_id => '5abzqhtfcvr73' -
                    ,fixed => 'YES' -
                    ,enabled=>'YES');
print :cnt;
exit

What is "Automatic Reoptimization" introduced in Oracle 12cR1?

Some definitions here, mostly taken directly from Oracls documentation, or quoted from Tim Hall's www.oracle-base.com

Automatic Reoptimization

Automatic Reoptimization is the ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement.

Automatic reoptimization takes two forms: Statistics Feedback and Performance Feedback.

Statistics feedback

Statistics Feedback (formerly known as cardinality feedback and first introduced in Oracle 11gR2) is a type of reoptimization that automatically improves plans for repeated queries that have cardinality misestimates.

• At the end of an execution, the optimizer compares its initial cardinality estimates to the actual number of rows returned by each operation in the plan during execution. 
• If estimates differ significantly from actual cardinalities, then the optimizer stores the correct estimates for subsequent use and the statement is marked as "reoptimizable". 
• When the query executes again, the optimizer uses the corrected cardinality estimates instead of its usual estimates, allowing a better plan to be determined
• Statistics Feedback is statement specific and is lost if the instance is restarted or the statement is aged out of the shared pool.

Performance Feedback

This form of automatic reoptimization helps improve the degree of parallelism automatically chosen for repeated SQL statements when PARALLEL_DEGREE_POLICY is set to ADAPTIVE.

• At the end of an initial execution, the optimizer compares the degree of parallelism chosen by the optimizer with the degree of parallelism computed based on the performance statistics (for example, the CPU time) gathered during the actual execution of the statement
• If the two values vary significantly, then the database marks the statement for reparsing, and stores the initial execution  statistics as feedback. This feedback helps better compute the degree of parallelism for subsequent executions.

Note that even if PARALLEL_DEGREE_POLICY is not set to ADAPTIVE, statistics feedback may influence the degree of parallelism chosen for a statement.

In short, to remember it more easily:

• statistics feedback deals with incorrect cardinality
• performance feedback deals with incorrect parallel execution

Sources:

Oracle Documentation

www.oracle-base.com

How to make use of the GATHER_PLAN_STATISTICS hint

Use the gather_plan_statistics hint to view the optimizer's estimation of rows vs. the actual returned number of rows.

Notice the case - gather_plan_statistics - I had trouble getting it to work when stated in capital letters as GATHER_PLAN_STATISTICS. So stick to lower case!

Use the hint in the SELECT part of your query, for example:

select /*+ gather_plan_statistics */
from mytable t1 join mytable t2....

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor(NULL,NULL,'TYPICAL ALLSTATS LAST'));
exit

Alternatively, after the query has run to completion, check the cursor cache for the details of your specific SQL ID, and use the display_cursor with the SQL ID explicitly set:

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( 'dnyrjuumj8psq',1,'TYPICAL ALLSTATS LAST'));
exit

Either way will work and give you the following example output:



Plan hash value: 4147659309

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |      1 |        |       |  1862 (100)|          |   3012 |00:08:14.63 |     576K|    553K|       |       |          |
|   1 |  SORT ORDER BY                           |         |      1 |      2 |   294 |  1862   (1)| 00:00:01 |   3012 |00:08:14.63 |     576K|    553K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |         |      1 |        |       |            |          |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   3 |    NESTED LOOPS                          |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.50 |     576K|    553K|       |       |          |
|   4 |     NESTED LOOPS                         |         |      1 |      2 |   294 |  1861   (1)| 00:00:01 |   3012 |00:08:45.34 |     573K|    553K|       |       |          |
|   5 |      NESTED LOOPS                        |         |      1 |      2 |   270 |  1859   (1)| 00:00:01 |   3012 |00:08:45.31 |     573K|    553K|       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1      |      1 |      1 |    31 |     3   (0)| 00:00:01 |    439 |00:00:00.27 |      89 |     73 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4 |      1 |      4 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      1 |       |       |          |
|*  8 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2      |    439 |      2 |   208 |  1856   (1)| 00:00:01 |   3012 |00:08:54.15 |     573K|    553K|       |       |          |
|*  9 |        INDEX RANGE SCAN                  | T2_IDX4 |    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:54.73 |   38436 |  37439 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN                   | T3_IDX1 |   3012 |      1 |       |     0   (0)|          |   3012 |00:00:00.01 |       9 |      2 |       |       |          |
|  11 |     TABLE ACCESS BY INDEX ROWID          | T3      |   3012 |      1 |    12 |     1   (0)| 00:00:01 |   3012 |00:00:00.01 |    3012 |      2 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Notice the column "E-Rows". It deviates substantially from the column A-Rows, which are the actual rows returned.



So the optimizer is clearly not able to make a good estimation about the number of rows.



Let's add a couple of statements which will give the optimizer better statistics. This particular database is not certified with any of the 12.1 adaptive tuning features, so I will have to feed the optimizer using other methods:



alter session set statistics_level='ALL';
alter session set optimizer_dynamic_sampling=11;


After running it a second time, the following plan is generated:



Plan hash value: 1509429641

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |        |      1 |        |       |   735K(100)|          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|   1 |  SORT ORDER BY                           |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|   372K|   372K|  330K (0)|
|*  2 |   FILTER                                 |        |      1 |        |       |            |          |   3012 |00:09:44.25 |     572K|    551K|       |       |          |
|*  3 |    HASH JOIN                             |        |      1 |   2986 |   428K|   735K  (1)| 00:00:29 |   3012 |00:09:44.25 |     572K|    551K|  1066K|  1066K|  954K (0)|
|   4 |     NESTED LOOPS                         |        |      1 |   2986 |   393K|   735K  (1)| 00:00:29 |   3012 |00:09:43.01 |     569K|    547K|       |       |          |
|   5 |      NESTED LOOPS                        |        |      1 |     10M|   393K|   735K  (1)| 00:00:29 |     12M|00:00:38.92 |   38488 |  37114 |       |       |          |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |    439 | 13609 |    72   (0)| 00:00:01 |    439 |00:00:00.01 |      76 |      0 |       |       |          |
|*  7 |        INDEX RANGE SCAN                  | T1_IDX4|      1 |    440 |       |     2   (0)| 00:00:01 |    440 |00:00:00.01 |       3 |      0 |       |       |          |
|*  8 |       INDEX RANGE SCAN                   | T2_IDX4|    439 |  23118 |       |    69   (0)| 00:00:01 |     12M|00:00:28.47 |   38412 |  37114 |       |       |          |
|*  9 |      TABLE ACCESS BY INDEX ROWID         | T2     |     12M|      7 |   728 |  1856   (1)| 00:00:01 |   3012 |00:08:47.65 |     531K|    510K|       |       |          |
|  10 |     TABLE ACCESS FULL                    | T3     |      1 |  74866 |   877K|   572   (1)| 00:00:01 |  74866 |00:00:01.22 |    3022 |   3017 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Consider the same column - the E-Rows and the A-Rows, and how much more accurate the optimizer is estimating the rows to be returned. 

Notice also that the former of these two plans generates an index lookup on the T3 table, while the ladder generates a full table scan. The FTS proved to be the most efficient, since the runtime dropped from 15 to 9 minutes.




NOTE:
You can only get the results you expect when you actually execute the statement. 
If you try to add the hint along with the EXPLAIN PLAN FOR statement, you will see warnings as these in your output:


Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level



These will of course be shown even if you do set the statistics_level to ALL. 
As Mathew McPeak points out in an aswer on stackoverflow.com

The way you are using it, you are printing the plan of the last statement you explained, not the last statement you executed. And "explain" will not execute the query, so it will not benefit from a gather_plan_statistics hint.
  

How to use dbms_spm.load_plans_from_sqlset

To load a specific SQL ID with a specific plan hash value from an STS into your SQL Plan Management Base, use:

set serveroutput on
declare
   l_num_plans PLS_INTEGER;
begin
 l_num_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=> 'LongRunningSQL1',sqlset_owner=>'DBADMIN' ,basic_filter=>'sql_id=''83fhwyajathc3'' and plan_hash_value=3643960001 ');
 DBMS_OUTPUT.put_line('Number of plans loaded: ' || l_num_plans);
end;
/
exit

Documented here

How to find the number of full table scans

From the Oracle Documentation:

The view DBA_HIST_SEG_STAT view displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

 SELECT O.OBJECT_NAME,O.OWNER, SUM(S.TABLE_SCANS_TOTAL) "# of FTS",(SELECT NUM_ROWS FROM DBA_TABLES T WHERE T.TABLE_NAME = O.OBJECT_NAME) "num rows total"
 FROM DBA_HIST_SEG_STAT S JOIN DBA_OBJECTS O ON (S.OBJ# = O.DATA_OBJECT_ID)
 WHERE O.OWNER NOT IN ( 'SYS','DBSNMP','XDB')
 AND O.OBJECT_TYPE = 'TABLE'
 GROUP BY S.OBJ#,O.OBJECT_NAME,O.OWNER
 ORDER BY 4 DESC;

OBJECT_NAME	OWNER	# of FTS	num rows total
TABLE1	USER1	5702	1583895743
TABLE2	USER1	11	588921781
TABLE3	USER1	6195	241550246

As can be seen from above, the extreme number of rows in TABLE1, coupled with 5702 full table scans, should bring it to your attention as a candidate for partitioning!

What is the difference between a bind-sensitive and bind-aware cursor?

The terms "bind-sensitive" and "bind-aware" are used when explaining Oracles Adaptive Cursor Sharing (ACS) feature.

Adaptive cursors sharing makes it possible for a single statement to use different execution plans, depending on the values of its bind variables.

Oracle explains the difference between bind-sensitive and bind-aware in their documentation like this:


"A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable."

and

"A bind-aware cursor is a bind-sensitive cursor that is eligible to use different plans for different bind values."


In other words, when a bind-sensitive cursor have been executed, the database saves the execution statistics for the new values, and compares them with the values used previously.

If (and only if) there is a significant change, Oracle marks the cursor as bind-aware.
During future executions of the cursor, the CBO will generate a plan based on the bind values and their cardinality estimates.

Query to search for details about a specific SQL ID in the cursor cache

I have plenty of examples that Oracle 12cR1 selects poor execution plans over good ones.
This is a recent example from one of my databases. Notice that there are two different plans for the same SQL ID, one that seems to take long per exection, and a good plan that executes each statement very quickly:

SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID =('fczwz45f4d46q')
ORDER BY LAST_ACTIVE_TIME DESC;

Result:

Last active	SQL_ID	PLAN_HASH_VALUE	BASELINE	SQL_PROFILE	Child Num	ELAPSED_TIME	EXECUTIONS	duration (sec)	sec per exe
16.08.2017 10:57	fczwz45f4d46q	2259800413		SYS_SQLPROF_0156739adc130001	0	350642099493	311	350642	1127,5
16.08.2017 01:39	fczwz45f4d46q	927328071		SYS_SQLPROF_0156739adc130001	9	2675614671	26048	2676	0,1

In this case, my goal is to create a fixed baseline for the execution plan with hash value 927328071.

How to use the dbms_explan.display_cursor function to find the execution plan of a query in the Cursor Cache

set linesize 200
set pagesize 0
select * from table
(dbms_xplan.display_cursor( '0syc4upspr638',3,'TYPICAL'));
exit

Where
- the first argument is the SQL id
- the second argument cursor child number
- the third argument is the format string. TYPICAL is the default value.


See the Oracle Documentation for more information on how to use dbms_xplan

How to explain a SQL statement using bind variables

Sometimes I am asked to analyze an SQL statement picked up from Enterprise Manager Cloud Control.
These SQL statements only reveal the bind variables used, and not their actual values.

To get a more realistic explain plan, ask your developers to provide you with the actual literal values used in the SQL statement, and then prepare a script that contains the following:

connect username/password
SET TIMING ON
SET LINESIZE 200
SET PAGESIZE 0

var b9 number;
var b8 number;
var b7 number;
var b6 varchar2(3);
var b5 varchar2(3);
var b4 varchar2(4);
var b3 varchar2(1);
var b2 varchar2(2);
var b1 varchar2(2);

exec :B9 := 12345;
exec :B8 := 6127737;
exec :B7 := 2013;
exec :B6 := 'KOC';
exec :B5 := 'PPQ';
exec :B4 := 'OOPQ';
exec :B3 := '7';
exec :B2 := 'YM';
exec :B1 := 'WT';

EXPLAIN PLAN FOR
  SELECT ...
  FROM ...
  WHERE COL1 NOT IN (:B6, :B5, :B4)
  AND COL2 = :8
  AND COL3 = :9;


SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Run the script using sqlplus.

Such a script can also come in handy if you're asked to perform changes to the tables' structure, and then execute the actual script to see how much time it takes to execute.
In this case, remove the EXPLAIN PLAN clause and the call to DBMS_XPLAN.DISPLAY, and you'll have all you need to execute the actual SQL statements you're tuning.

Some important tuning parameters in Oracle 12c

Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their current setting:

col name format a40
col value format a20
col description format a80
set lines 200

SELECT NAME,VALUE,DESCRIPTION 
FROM   V$SYSTEM_PARAMETER
WHERE NAME IN ( 'optimizer_adaptive_features',
                'optimizer_adaptive_reporting_only',
                'optimizer_features_enable',
                'optimizer_use_sql_plan_baselines',
                'optimizer_capture_sql_plan_baselines',
                'optimizer_dynamic_sampling',
                'optimizer_adaptive_plans',
                'optimizer_adaptive_statistics');

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including

* adaptive plan (adaptive join methods and bitmap pruning)
* automatic re-optimization
* SQL plan directives
* adaptive distribution Methods

OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Dynamic statistics were called dynamic sampling in versions prior to 12c.

Range of values: 0 to 11. Default depends on the value of the parameter optimizer_features_enable:

• If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

At level 2, dynamic statistics are used "if at least one table in the statement has no statistics".

At level 4, Oracle is less restrictive and will use dynamic statistics when "at least one table in the statement has no statistics, the statement has one or more expressions used in the WHERE clause predicates... or the statement uses complex predicates".

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

Notice that Oracle says the following about level 11:

There are cases where the optimizer will automatically decide to use 11, for example:

•The query will run in parallel.
•The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).


From personal experience, this parameter is important! If it's turned off, I would recommend setting it to the default value. I have seen many cases where queries will actually perform better only by setting this parameter from 0 (off) to the default value.

Read more about the different Levels:
Oracle 12cR1
Oracle 12cR2

The two parameters appearing last in the list, optimizer_adaptive_plans and optimizer_adaptive_statistics, were added in 12.2, and replace the parameter optimizer_adaptive_features.

What does 1048575 in the Pstop column in the explain plan indicate?

The answer lies in the dictionary view ALL_PART_TABLES (or DBA_PART_TABLES).
The column partition_count is the total number of partitions, but for interval partitioned tables, this number is always 1048575.


When you explain a SQL statement that is accessing a partitioned table using interval partitioning, you may notice how the number 1048575 always shows up in the Pstop column of your EXPLAIN PLAN output.

My table and the local index is partitoned as follows:

CREATE TABLE MYTABLE
(
  SEKNUM           NUMBER(19)            DEFAULT "MYTAB_MYSEQ"."NEXTVAL" NOT NULL,
  COL1             VARCHAR2(200 CHAR)    NOT NULL,
  COL2             VARCHAR2(100 CHAR)    NOT NULL,
PARTITION BY RANGE (SEKNUM)
INTERVAL( 100000)
);

CREATE UNIQUE INDEX UK_SEK ON HENDELSER_PART_ST1_S.HENDELSER
(SEKVENSNUMMER)
  LOCAL;

This gives me a table with about 200 partitions.

When executing a query using the > operator, like below:

set lines 200
set autot on explain

SELECT count(*)
FROM   mytable
WHERE  seknum > 430000;

It would generate the following plan

--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     6 | 41342   (4)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|            |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
|*  3 |    INDEX RANGE SCAN       | UK_SEK     |    20M|   115M| 41342   (4)| 00:00:02 |     5 |1048575|
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SEKNUM">430000)

The Pstop value can be confusing. Obviously, you don't have that many partitions.
To verify that partition pruning is indeed working, change the operator from > ("greater than") to < ("less than"), and rerun the query:

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

| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT          |            |     1 |     6 |   887   (4)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE           |            |     1 |     6 |            |          |       |       |

|   2 |   PARTITION RANGE ITERATOR|            |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

|*  3 |    INDEX RANGE SCAN       | UK_SEK     |   431K|  2525K|   887   (4)| 00:00:01 |     1 |     5 |

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



Predicate Information (identified by operation id):

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



3 - access("SEKNUM"<430000)

You now see that the Pstop column is 5, so Oracle search partitions 1 through 4 for the rows.

These are:

set lines 200
col partition_name format a30
col high_value format a20

select partition_name,partition_position,high_value
from dba_tab_partitions 
where table_NAME='MYTABLE'
and table_owner='SCOTT'
and partition_position between 1 and 4;

PARTITION_NAME                 PARTITION_POSITION HIGH_VALUE          
------------------------------ ------------------ --------------------
P1_INIT                                         1 100001              
SYS_P9545                                       2 200001              
SYS_P9548                                       3 300001              
SYS_P9551                                       4 400001              

4 rows selected.