Check the execution plan of SQL query in Oracle

Explain plan for statement:

-- Without Executing SQL Query
explain plan for
select last_name from employees where department_id=100;

select * from table(dbms_xplan.display);

@?/rdbms/admin/utlxpls.sql;

--with Executing of SQL Query
Set autotrace on
select last_name from employees where department_id=100;
Set autotrace off


Find the SQL Id for the Statement.

select sql_text, sql_id from v$sql where lower(sql_text) like '%select * from employee%';

Manually load plan in SQL baseline with help of SQL ID

--Load the sql_id into the SQL Basesile
declare
v_sql_plan_id  pls_integer;
begin
v_sql_plan_id := dbms_spm.load_plans_from_cursor_cache(
sql_id => '0gh1gn1mv7u0u');
end;
/

Check the execution plan which is in accepted stated

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where lower(sql_text) like '%select * from employee%';

Create index on department_id column

create index on the department id column table.

After again executing if it using same full scan:

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where sql_text like '%employee%';

SQL_HANDLE                PLAN_NAME                      ENA ACC FIX
------------------------- ------------------------------ --- --- ---
SYS_SQL_1046c141c5de11a8  sql_plan_10jq1872xw4d8c079fdff YES NO  NO
SYS_SQL_1046c141c5de11a8  sql_plan_10jq1872xw4d8cf314e9e YES YES NO


Evolved process will be used to accept the new SQL execution plan that is utilizing the created index

--Evolve the new sql plan
set serveroutput on
set line 999 pages 999
select dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1046c141c5de11a8',plan_name => 'sql_plan_10jq1872xw4d8c079fdff') from dual;

Following example shows how to disable a specific SQL plan baseline by fixed the execution plan:

--Disable a specific SQL baseline plan
set serveroutput on
declare
v_sql_plan_id  pls_integer;
begin
v_sql_plan_id := dbms_spm.alter_sql_plan_baseline(
sql_handle      => 'sys_sql_1046c141c5de11a8',
plan_name       => 'sql_plan_10jq1872xw4d8cf314e9e',
attribute_name  => 'fixed',
attribute_value => 'YES');
end;
/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.