Blog - comments

-- Here is a quick script to display which objects are locked in Share. Parameters: owner tablename....
Hey...I think you forgot that Hotspot have a JIT compiler too. The difference is in the time wherer ...
Anderson

Thanks for the content..

vani
How to use IOZONE for chechking deduplication, I can see -+w ,-+y options but there is not proper do...
dojo
Hi Franck, In 11g, I use another way to check the Oracle schemas attached to the options. The view d...
Blog Franck Pachot Best practice for the sending of an Oracle execution plan

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Best practice for the sending of an Oracle execution plan

You have a query that takes too long and you want help to analyze the execution plan? Then you need to get it with relevant information, and correctly formatted. Autotrace is not a good option as it does not bind the variables in the same way as your application. Explain plan only shows estimations, but if we have a performance issue, this probably means that the estimation is wrong. I prefer SQL Monitoring when we have Tuning Pack, or the cursor plan with row-source statistics in all other cases. If you post it in a forum, don't forget to keep it formatted or it's impossible to read.

Here are the two ways I prefer to get an execution plan, depending on whether you have the tuning pack licence or not.

 

Method 1 - Without Tuning Pack

I set the sqlplus environment to nicely spool to text file and set the STATISTICS_LEVEL to ALL in order to gather plan execution statistics:

 

set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

 

Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables:

 

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

 

Finally I get the execution plan to a text file:

 

spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds +cost'));
spool off

 

The plan will have both the estimations (E-Rows) and the actual number of rows (A-Rows) from the last execution. Note that if it is a parallel query statement, you must omit the 'last' in the format or you will have statistics only for the coordinator process.

 

Method 2 - With Tuning Pack

When you have tuning pack, you have access to the great SQL monitoring feature.

SQL> show parameter pack

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

 

I set the sqlplus environment to nicely spool to html file:

 

set pagesize 0 linesize 10000 trimspool on serveroutput off long 1000000000 longc 1000000000 echo off feedback off

 

Then I execute the query. Don't forget to set the current_schema to the user that executes the statement, and to bind variables.

One difference here: the MONITOR hint to force SQL Monitoring.

 

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT /*+ monitor */ * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

 

Finally, get the execution plan to a html file:

 

spool plan.htm
select dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'active') from dual;
spool off

 

The html file is very small but will be rendered by an online flash automatically loaded from the oracle.com website.

You can see both output, plan.txt, and plan.htm from this zip: xplans.zip

 

Here is how they look like (but please never send me screenshots of execution plans...):

 

CapturePlantxt.PNG

 

And the colorful active report from SQL Monitoring:

 

CapturePlanHtm.PNG

 

The goal of this blog post is to simply (copy/paste) the formatting and the plan gathering code, so if you have any improvement ideas, please share.

Rate this blog entry:
6

Franck Pachot is Consultant at dbi services. He has 20 years of experience in Oracle databases. Through his expertise as a DBA, Oracle expert, data architect, and performance specialist, he is able to cover all database areas: architecture, data modeling, database design, tuning, operation, and training. Franck Pachot knows how to enable an efficient collaboration between the developers and the operational team when it comes to troubleshooting issues or performance tuning. He has passed the OCP certifications from 8i to 12c, is also Certified Expert for Oracle Database 11g Performance Tuning, and now achived the highest level of certification: Oracle Master Certified OCM 11g. Prior to joining dbi services, Franck Pachot was Oracle Consultant at Trivadis in Lausanne. Previously, he worked in several countries and environements, always as a consultant. Franck Pachot holds a Master of Business Informatics from the University of Paris-Sud. His branch-related experience covers Financial Services / Banking, Public Sector, Food, Transport and Logistics, Pharma, etc.


    O_Database12c_Admin_Professional_clrOCE_ODb11gPerfTun_clr11gocm_logo

Comments

  • Guest
    Martin Preiss Thursday, 15 May 2014

    Hello Franck,

    just my 2 cents: I think it is always a good idea to create plans as plain text (for sql monitor: calling dbms_sqltune.report_sql_monitor in sql) - of course the html version is nice but I prefer to search, copy and paste in a text representation.

    Regards

    Martin

  • Franck Pachot
    Franck Pachot Thursday, 15 May 2014

    Hi Martin,
    I totally agree. But when I have the active html report, there is the xml in it and I can get the text version with $ORACLE_HOME/rdbms/xml/orarep/sqlmonitor/sqlmonitorText.xsl
    And in 12c xml is compressed, then I pipe it to | base64 -id | openssl zlib -d
    I have some ugly scripts to do that. I have to share them one day...
    Regards,
    Franck.

  • Guest
    Martin Preiss Thursday, 15 May 2014

    that was an impressively fast reaction - thank you!

  • Guest
    vani Monday, 07 July 2014

    Thanks for the content..

Leave your comment

Guest Tuesday, 22 July 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter