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.


Check for parameter fast_start_mttr_target

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.


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

Should I delete my column Histograms to improve plan stability?


-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.

– 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.

What is SQL Tuning Health Check?

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.


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.

perform good for OLTP system.

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