2 Replies Latest reply: Jan 16, 2013 5:40 AM by moreajays RSS

    Old execution plan vs New execution plan

      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.

        • 1. Re: Old execution plan vs New execution plan
          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.

          • 2. Re: Old execution plan vs New execution plan

            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
              my_task_name VARCHAR2(30);
              my_sqltext CLOB;
              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');
            dbms_sqltune.Execute_tuning_task (task_name => 'sql_ims1_d9vm06y4jk4z6');
            set long 100000
            set linesize 5000
            set pages 4000
            select dbms_sqltune.report_tuning_task('sql_ims1_d9vm06y4jk4z6') from dual;
            Ajay More