How to generate Explain Plans in different ways

Share via:

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′;

Share via:
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...

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

  1. It’s a pity you don’t have a donate button! I’d without a doubt donate to this fantastic blog! I suppose for now i’ll settle for book-marking and adding your RSS feed to my Google account. I look forward to fresh updates and will talk about this site with my Facebook group. Chat soon!

  2. Magnificent beat ! I would like to apprentice while you amend your website, how can i subscribe for a blog website? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear concept

  3. Hello there, just became alert to your blog through Google, and found that it’s truly informative. I am gonna watch out for brussels. I’ll appreciate if you continue this in future. A lot of people will be benefited from your writing. Cheers!

  4. We’re a group of volunteers and starting a new scheme in our community. Your site provided us with valuable information to work on. You have done an impressive job and our whole community will be grateful to you.

  5. 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!

  6. You really make it appear so easy with your presentation but I in finding this topic to be actually something which I think I would never understand. It sort of feels too complex and very extensive for me. I am looking ahead in your subsequent publish, I’ll try to get the hold of it!

  7. Its like you learn my thoughts! You seem to understand a lot about this, like you wrote the ebook in it or something. I feel that you could do with some to pressure the message house a bit, however other than that, this is great blog. An excellent read. I’ll certainly be back.

  8. Thank you for another informative web site. The place else could I am getting that type of info written in such an ideal way? I have a project that I’m just now running on, and I have been on the look out for such information.

  9. 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.

  10. 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

  11. 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