Category: performance

Using Baselines

DBMS_SQLTUNE.CREATE_SQLSET();
DBMS_SQLTUNE.SQLSET_CURSOR();
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY();
DBMS_SQLTUNE.LOAD_SQLSET();
DBMS_SQLTUNE.SELECT_SQLSET();

DBMS_SPM.LOAD_PLANS_FROM_SQLSET();
OR
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE();

dba_sql_plan_baselines;

Parameters :
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES

Use basic_filter clause to restrict this sqlset to only one particular sql_id.

Example : BASIC_FILTER => ‘sql_id = ”g3wubsadyrt37”’

Ref :
http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

https://rnm1978.wordpress.com/2011/06/28/oracle-11g-how-to-force-a-sql_id-to-use-a-plan_hash_value-using-sql-baselines/

Advertisements

ADAPTIVE CURSOR SHARING

So, we can have different execution plans and different plan hash value for the same sql_ids. So, if the execution plan changes the plan hash value will also change. From Oracle 11g we have Adaptive Cursor Sharing (ACS) which allows the server to compare the effectiveness of execution plans between executions with different bind variable values and if notices suboptimal plans, allows certain bind variable values, to use different execution plans for the same statement hence creating a new child cursor.

A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for. This cursor was marked bind sensitive because the histogram on the C2 column was used to compute the selectivity of the predicate “where C2 = :N1”. Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

V$SQL_CS_STATISTICS
V$SQL_CS_SELECTIVITY
V$SQL_CS_HISTOGRAM

V$SQL_SHARED_CURSOR
V$SQLAREA

V$SQL column has :
IS_BIND_SENSITIVE
IS_BIND_AWARE

select * from employees;
SELECT * FROM EMPLOYEES;

Hints : BIND_AWARE and NO_BIND_AWARE

ref :
https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1
https://blogs.oracle.com/optimizer/entry/how_do_i_force_a
http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm#i79423
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html

Adaptive Execution Plans

Adaptive Query Optimization

  • adaptive plans
    join methods
    parallel distrubution methods

  • adaptive statistics
    dynamic statistics
    automatic reoptimization
    sql plan directives

=============
Adaptive Plans in Oracle Database 12c Release 1 (12.1)
Adaptive SQL Plan Management (SPM) in Oracle Database 12c Release 1 (12.1)
Adaptive Query Optimization in Oracle Database 12c Release 1 (12.1)

=============
Parameters :
optimizer_adaptive_features
optimizer_adaptive_reporting_only