Can’t change the application code to use hints (or so) but want to use the SQL plan that would be generated with HInt

If you are using Oracle 11g database and SQL Plan baselines you can use the SQL Plan even that is optimal if you can’t change the application code.

Your database should have db parameter optimizer_use_sql_plan_baselines = TRUE (Which is default)

• Open SQL Plus session
• If you have db parameter set to optimizer_capture_sql_plan_baselines = FALSE (which is default) – execute following to capture sql plans that would be generated by sql command

alter session Set optimizer_capture_sql_plan_baselines = TRUE;

• Execute the SQL statement (with hint) so it generates and captures the plan by SPM.
• Now session can be disconnected as new plan is generated and stored in the baselines.
• Obtain the SQL_Handle and Plan_name using the below sql statement

select sql_handle, plan_name, sql_text, enabled, accepted, fixed from DBA_SQL_PLAN_BASELINES   
where upper(sql_text) like '%<portion of your sql to identify>%'

• Mark the appropriate plan as FIXED using following command

SET SERVEROUTPUT ON
DECLARE
 l_plans_altered PLS_INTEGER;
BEGIN
 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
 sql_handle => '<sql_handle obtained from above query?',
 plan_name => '<Obtain right plan name from above query for the plan you want to use>',
 attribute_name => 'fixed',
 attribute_value => 'YES');

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

Now this plan is marked as fixed, it will be used for feature executions of this SQL statement.

Reference: Arup Nanda Article: http://www.oracle.com/technetwork/issue-archive/2009/09-mar/o29spm-092092.html

Read Full Post »

SQL Plan baselines created by dropped Schema

Hi,
We have heard about SPM and Plan Base line and how they help to improve the Database performance. Here I have little tricky question if some know for sure and I don’t think if Oracle has documented related to this.

We have Oracle db v 11.2.0.3 instance where we used to have a schema name :(say ‘DEVX’) and our application used to run against this schema. At some point in time, we stopped using this schema and dropped, instead we pointed our application against another schema called (say ‘DEV’), but it same application.

When I see sql plan base lines I see that plans were created by old schema (DEVX) and current schema (DEV) . But those plans created by old schema – DEVX had LAST_VERIFIED column blank where plans are ENABLED and some plans even have ACCEPTED : YES.

Saying that If LAST_VERIFIED column value is blank, are these plans are being used by optimizer and is it safe to delete plans created by old schema – DEVX ?

Read Full Post »