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 column has :
select * from employees;
SELECT * FROM EMPLOYEES;
Hints : BIND_AWARE and NO_BIND_AWARE
Enabling Automatic Statistics Gathering
Automatic statistics gathering is enabled by default when a database is created, or when a database is upgraded from an earlier database release. You can verify that the job exists by viewing the
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
In situations when you want to disable automatic statistics gathering, the most direct approach is to disable the
GATHER_STATS_JOB as follows:
BEGIN DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); END; /
Well thats for 10g. But as of 11g below applies :
DBMS_AUTO_TASK_ADMIN.ENABLE ( client_name IN VARCHAR2, operation IN VARCHAR2, window_name IN VARCHAR2);
Oracle 11g includes three automated database maintenance tasks:
- Automatic Optimizer Statistics Collection – Gathers stale or missing statistics for all schema objects (more info). The task name is ‘auto optimizer stats collection’.
- Automatic Segment Advisor – Identifies segments that could be reorganized to save space (more info). The task name is ‘auto space advisor’.
- Automatic SQL Tuning Advisor – Identifies and attempts to tune high load SQL (more info). The task name is ‘sql tuning advisor’.
Here are some interesting links related to MUTEX and Library Cache PINs :
Andrey Nikolaev has written some excellent articles on Mutex :