This can probably be found elsewhere as well, but here’s a short wrap-up how to get the most out of your execution plans, quickly
1. Be sure the actual rows and time statistics are collected.
You can do this with
-- login as user sys
alter system set statistics_level = all;
2. Execute your bad SQL.
I can’t give you an example, because I don’t write bad SQL.
3. ;-)
4. Find your sql_id with this statement
-- these are the most important columns
select last_active_time, sql_id, child_number, sql_text
from v$sql
-- filter for your statement
where upper(sql_fulltext) like
upper('%[put some text from your SQL statement here]%')
-- this orders by the most recent activity
order by last_active_time desc;
5. Get the cursor and plan for that statement
select rownum, t.* from table(dbms_xplan.display_cursor(
-- Put the previously retrieved sql_id here
sql_id => '6dt9vvx9gmd1x',
-- The cursor child number, in case there are
-- several plans per cursor
cursor_child_no => 0,
-- Some formatting instructions to get Starts,
-- E-Rows, A-Rows and A-Time
FORMAT => 'ALL ALLSTATS LAST')) t;
6. Purge the cursors, if needed:
select address || ',' || hash_value from v$sqlarea
where sql_id = '6dt9vvx9gmd1x';
begin
sys.dbms_shared_pool.purge(
'00000000F3471988,2224337167','C',1);
end;
7. Delete all execution plans
-- login as user sys
alter system flush shared_pool;
8. Delete buffer cache (IO cache)
-- login as user sys
alter system flush buffer_cache;
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
Hi,
Besides having and upper(sql_fulltext) not like upper(‘select last_active_time%’), how do you exclude the SQL below from the list when you run it? :-)
-- these are the most important columns select last_active_time, sql_id, child_number, sql_text from v$sql -- filter for your statement where upper(sql_fulltext) like upper('%[put some text from your SQL statement here]%') -- this orders by the most recent activity order by last_active_time desc;You could add