How to generate Explain Plans in different ways

1. EXPLAIN PLAN FOR SELECT <STATEMENT>

set linesize 300
SELECT * FROM TABLE(dbms_xplan.display);

2. FROM MEMORY – Get the execution plan which is generated by CBO

Use DBMS_XPLAN package:
select * from table (dbms_xplan.display_cursor (‘&sqlid’) ) ;

For the above, you need to have SQL ID. To get the SQL ID, select from V$SQLAREA

select inst_id, substr(sql_text,1,100) SQL_TEXT, sql_id
from gv$sqlarea where sql_text like ‘%RNUM%’
and sql_text not like ‘%sqlarea%’
order by LAST_LOAD_TIME;

3. OEM

Performance Tab:
Give the SQL ID, then it will give a table for PLAN.

4. Different explain plans for one SQL ID

Set pagesize 400 \n
set linesize 300 \n
select * from table (dbms_xplan.display_awr (‘&sqlid’) ) ; \n

5. Using dba_hist_sql_plan:

select dist from dba_hist_sql_plan where sql_id=’gyp67dqx5d733′;

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 5.00 out of 5)
Loading...

13 thoughts on “How to generate Explain Plans in different ways

  1. When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get three e-mails with the same comment. Is there any way you can remove people from that service? Cheers!

  2. hello there and thank you for your information – I’ve certainly picked up something new from right here. I did however expertise some technical issues using this site, since I experienced to reload the site lots of times previous to I could get it to load correctly. I had been wondering if your hosting is OK? Not that I’m complaining, but slow loading instances times will sometimes affect your placement in google and can damage your high-quality score if ads and marketing with Adwords. Anyway I am adding this RSS to my e-mail and could look out for a lot more of your respective intriguing content. Ensure that you update this again very soon.

  3. Unquestionably believe that that you stated. Your favorite reason appeared to be at the net the simplest thing to understand of. I say to you, I definitely get annoyed at the same time as other folks think about worries that they just do not recognise about. You controlled to hit the nail upon the top as well as defined out the entire thing with no need side-effects , other folks can take a signal. Will likely be again to get more. Thank you

  4. You really make it seem really easy together with your presentation but I find this matter to be really one thing that I believe I would by no means understand. It sort of feels too complicated and very broad for me. I’m having a look ahead to your next post, I’ll try to get the dangle of it!

Add Comment