This discussion is archived
2 Replies Latest reply: Jan 16, 2013 3:40 AM by moreajays RSS

Old execution plan vs New execution plan

User332438 Newbie
Currently Being Moderated
Hi DBA's,

I had a scenario that there is a query which is having good execution plan after a week the plan has changed and it cause performane issues.

Now is there is any way to apply or set the old execution plan for the current query.

Please help me to fix this issue.

Thanks in advance.

Regards...
  • 1. Re: Old execution plan vs New execution plan
    Justin Cave Oracle ACE
    Currently Being Moderated
    What version of Oracle? What edition? What options are licensed?
    Do you know what the "good" plan was?

    Since this doesn't appear to have anything to do with database security, you are probably much better off posting it in a more appropriate forum (one of the nice mods will probably move it to the Database - General forum shortly if you don't close this thread and open a new one there). If you do post in a more appropriate forum, please at a minimum answer the questions I asked above.

    Justin
  • 2. Re: Old execution plan vs New execution plan
    moreajays Pro
    Currently Being Moderated
    Hi,

    Identify the sql_id (and replace it with d9vm06y4jk4z6) of the query run below & accept the sql_profile or refer recommended changes , don't know if it will return you the prev. good plan but it will give you best plan with good response /cost
    DECLARE
      my_task_name VARCHAR2(30);
      my_sqltext CLOB;
    BEGIN
      my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                               sql_id =>'d9vm06y4jk4z6',
                               scope => 'COMPREHENSIVE',
                               time_limit => 60,
                               task_name => 'sql_ims1_d9vm06y4jk4z6',
                               description => 'Task to tune a query');
    END;
    Begin
    dbms_sqltune.Execute_tuning_task (task_name => 'sql_ims1_d9vm06y4jk4z6');
    end;
    
    set long 100000
    set linesize 5000
    set pages 4000
    select dbms_sqltune.report_tuning_task('sql_ims1_d9vm06y4jk4z6') from dual;
    Thanks,
    Ajay More
    http://moreajays.blogspot.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points