dbms_sqltune

Please use dbms_sqltune package to see if optimizer could find a better plan and run faster. Kindly refer to the below instructions:

Run your statement & then find out its sql_id from v$sql as follows:

select sql_id, sql_text from v$sql where sql_text like '%<any sql text portion with the same used CASE>%';

Then, run the below script:

begin

dbms_sqltune.drop_tuning_task(task_name => 'my_sql_tuning_task'); --- kindly note that this task name will be specific to only one query

end;

/

declare

my_task_name varchar2(30);

begin

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '&sql_id',

task_name => 'my_sql_tuning_task',

description => 'Task to tune a query ');

END;

/

begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );

end;

/

set long 10000000

select dbms_sqltune.report_tuning_task('my_sql_tuning_task') from dual

/