I observed many sqls having null value against sql_plan_baseline field in v$sql view, even if they have been executed several times. Database version is 11.2.0.3 and optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines are both set to TRUE.

As per my understanding I was expecting baselines against sqls executed more than once. There must be something I am missing. Thanks in advance for any help in this regard.

Are there any outlines applied on these particular SQLs. If yes then try removing those outlines. Also, you can try enabling the baseline at the session level and running the SQL you mentioned more than once in the session. Post this you can check whether the baseline reflects in the data dictionary table for the particular SQL.

Sorry for quite a late reply. Last few days I was devoid of any network.I lost the last sql. But the purpose of question was primarily to clear my doubt. Similar to the last sql I have another sql doing multiple select operation. They are executed thousand times but no baseline got created. As mentioned earlier the db version is 11.2.0.3 and optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines are both set to TRUE.

None of your baselines are 'Accepted'. So they are not going to be used to override a plan proposed by the optimizer.

Now, this situation seems unusual in itself, because if a SQL has a baseline captured, and there is no existing baseline yet, then the first one is created in an ACCEPTED state, but those that follow are not.

Did you do anything to manipulate your baselines? Such as, import them from another system, or explicitly create them from a SQL Tuning Set or from the cursor cache? Or did you drop one?

To make the baselines available for use, you would use DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE.