Find Out Values Of Bind Variables Across Multiple Executions Of A SQL

In case of skewed data distribution, different values of bind variables can result in different execution plans.
Various execution plans and corresponding values of bind variables can be determined both when the statement
is still shared pool and when it has been flushed to AWR.

dba_hist_sqltext
v$sql_bind_capture
DBA_HIST_SQLBIND

http://oracleinaction.com/find-out-values-of-bind-variables-across-multiple-executions-of-a-sql/

Check for parameter fast_start_mttr_target

Success Factor COMPUTER FAILURE PREVENTION BEST PRACTICES
Recommendation
Benefit / Impact:

To optimize run time performance for write/redo generation intensive workloads.  Increasing fast_start_mttr_target from the default will reduce checkpoint writes from DBWR processes, making more room for LGWR IO.

Risk:

Performance implications if set too aggressively (lower setting = more aggressive), but a trade-off between performance and availability.  This trade-off and the type of workload needs to be evaluated and a decision made whether the default is needed to meet RTO objectives.  fast_start_mttr_target should be set to the desired RTO (Recovery Time Objective) while still maintaing performance SLAs. So this needs to be evaluated on a case by case basis.

Action / Repair:

Consider increasing fast_start_mttr_target to 300 (five minutes) from the default. The trade-off is that instance recovery will run longer, so if instance recovery is more important than performance, then keep fast_start_mttr_target at the default.

Keep in mind that an application with inadequately sized redo logs will likley not see an affect from this change due to frequent log switches so follow best practices for sizing redo logs.

Considerations for a direct writes in a data warehouse type of application: Even though direct operations aren't using the buffer cache, fast_start_mttr_target is very effective at controlling crash recovery time because it ensures adequate checkpointing for the few buffers that are resident (ex: undo segment headers).
Needs attention on rcaaa2, rcsm2, rcaaa1, rcsm1
Passed on

 

Status on rcaaa2:
WARNING => fast_start_mttr_target should be greater than or equal to 300.
rcaaa2.fast_start_mttr_target = 0

 

Status on rcsm2:
WARNING => fast_start_mttr_target should be greater than or equal to 300.
rcsm2.fast_start_mttr_target = 0
Status on rcaaa1:
WARNING => fast_start_mttr_target should be greater than or equal to 300.
rcaaa1.fast_start_mttr_target = 0

 

Status on rcsm1:
WARNING => fast_start_mttr_target should be greater than or equal to 300.
rcsm1.fast_start_mttr_target = 0

http://ravikishoredba.blogspot.in/2012/12/faststartmttrtarget-in-redo-log-tunning.html

https://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams131.htm

Should I delete my column Histograms to improve plan stability?

http://carlos-sierra.net/2012/08/13/should-i-delete-my-column-histograms-to-improve-plan-stability/
http://oradbastuff.blogspot.in/2012/01/how-to-delete-histograms-for-column.html
https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

PARENT AND CHILD CURSORS IN ORACLE

-V$SQLAREA : Contains one row for each parent cursor
-V$SQL : Contains one row for each child cursor
Relation between parent to child cursor would be one to one or one to many.

V$SQL_SHARED_CURSOR shows the reason why the statement cannot be shared.

Brief
– To enable sharing of parent cursors we should follow code formatting standards
– To enable sharing of child cursors we should ensure that
  . Optimizer mode is not changed in sessions.
  . Length of bind variables used should be same.
  . Values of NLS parameters should be same.

http://oracleinaction.com/parent-child-curosr/

What is SQL Tuning Health Check?

https://i0.wp.com/www.dba-scripts.com/wp-content/uploads/2014/08/sqltuning_health_check.png

The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs.
It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed.

The script generates an HTML report with information on data collected by the script. The script has no footprint and can be run on any system.

You must connect as SYS or with a user with the DBA role.
The script takes 2 parameters, the first one is about your licensing (Tuning or Diagnostics or None T|D|N), the second is the SQL_ID that needs to be analyzed. Not that if you want to use both the Diagnostic and Tuninbg pack you have to use T as the first parameter.

OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

One thing is sure that the default value
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set
for Data Warehouse System.

If your system is OLTP, then these 2 parameters values should be changed.  I would test the
system with different values before choosing the correct values.

Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will
perform good for OLTP system.

If you collect system statistics, then these 2 parameters play less importance role in deciding the
execution plan.

http://tamilselvang.blogspot.in/2011/11/optimizerindexcaching.html
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams159.htm#REFRN10142

sql plan management – how to fix an execution plan

In the past stored outlines were used to fix execution plans
In 11g the next level to control execution plans is implemented
its possible to force the optimizer to use one or more execution plans for a statement, uncontrolled plan changes are disabled.

take a closer look at the following examples – test it!

more details can be found in “ORACLE documentation -Oracle® Database Performance Tuning Guide – Chapter 15 Using SQL Plan Management”
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/optplanmgmt.htm#BABEAFGG

Note in http://support.oracle.com SQL PLAN MANAGEMENT [ID 456518.1]


— sqlplan management

— Roland Graeff, Germany
— tested on 11.2

— test table

create table tst (col1 number, col2 varchar2(20));
insert into tst values (1,’A’);
insert into tst values (2,’B’);
commit;

select /* RGFTST */ col2 from tst where col2 = ‘A’;

— to test/show the used execution plan
explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4148258400

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 24 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TST | 2 | 24 | 3 (0)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
*/

— select sql_id
select sql_id, plan_hash_value, sql_text
from v$sql
where sql_text like ‘%RGFTST%’
/

— use the accordingly sql_id in the next plsql block
— to put the execution plan to the sqlplan management

SET SERVEROUTPUT ON
DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.load_plans_from_cursor_cache(
sql_id => ‘8d6dshztauuct’,fixed => ‘YES’);
DBMS_OUTPUT.put_line(‘Plans Loaded: ‘|| my_plans);
END;
/

— list the sqlplan baselines
SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

— create index
create index i1 on tst (col2)
/

— in normal circumstances the index i1 should be used
— to check the used execution plan

explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 4148258400

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TST | 1 | 12 | 2 (0)| 00:00:01 |
————————————————————————–

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
– SQL plan baseline “SYS_SQL_PLAN_a5df9d5502e4a954″ used for this statement
*/

— to change attributes for this sqlplan

SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle=>’SYS_SQL_67a764aaa5df9d55′,
attribute_name=>’ENABLED’,
attribute_value=>’NO’);
DBMS_OUTPUT.PUT_LINE(‘Plans altered: ‘ || v_cnt);
END;
/

SELECT sql_handle, plan_name, enabled, accepted,fixed
FROM dba_sql_plan_baselines
/

— to show the changes
explain plan for
select /* RGFTST */ col2 from tst where col2 = ‘A’;
select * from table(dbms_xplan.display);

— sample output
/*
PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 1816312439

————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I1 | 1 | 12 | 1 (0)| 00:00:01 |
————————————————————————-

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

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – access(“COL2″=’A’)

Note
—–
– dynamic sampling used for this statement
*/

–to remove sqlplan
declare
drop_plan PLS_INTEGER;
BEGIN
drop_plan :=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SYS_SQL_67a764aaa5df9d55′,plan_name=>’SYS_SQL_PLAN_a5df9d5502e4a954′);
END;
/

declare
drop_plan PLS_INTEGER;
BEGIN
drop_plan :=dbms_spm.drop_sql_plan_baseline(sql_handle=>’SYS_SQL_67a764aaa5df9d55′,plan_name=>null);
END;
/

— export / transfer baselines to a different instance
— create a staging table
execute dbms_spm.CREATE_STGTAB_BASELINE(table_name=>’RGRAEFF_STAGE_BL’,table_owner=>’RGRAEFF’);

— export them to the staging table
variable var varchar2(30);
execute :var:=dbms_spm.PACK_STGTAB_BASELINE(table_name=>’RGRAEFF_STAGE_BL’ ,table_owner=>’RGRAEFF’);
execute dbms_output.put_line(‘Baselines exportiert: ‘||:var);

— now this table can be exported/imported via datapump to the target instance
— with dbms_spm.UNPACK_STG_TAB_BASELINE the exported baselines can be loaded

— remove test tables
drop table tst purge;

How to check the SQL Statement Plan?

When you do an explain plan, then the appropriate DBMS_XPLAN api is
DBMS_XPLAN.DISPLAY.
When you do an actual execution - i.e. not with explain plan - then
DBMS_XPLAN.DISPLAY_CURSOR is the appropriate api.

Troubleshooting for DBMS_XPLAN.DISPLAY_CURSOR
Error:-
NOTE: cannot fetch plan for SQL_ID: 3pb80q4uks11n, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

grant select ON v_$session TO &&1;
grant select on v_$sql_plan to &&1;
grant select on v_$sql to &&1;

set serverouput off;