Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,101
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Explain Plan.

Posted by FatDBA on August 23, 2012

Explain Plan is a great way to tune your queries.
As a bonus for using Explain Plan, you will learn more about how the DBMS works “behind the scenes”, enabling you to write efficient queries the first time around.

Explain Plan executes your query and records the “plan” that Oracle devises to execute your query. By examining this plan, you can find out if Oracle is picking the right indexes and joining your tables in the most efficient manner. There are a few different ways to utilize Explain Plan.

The first thing you will need to do is make sure you have a table called plan_table available in your schema.

If this table is not there run this script provided by oracle to create that table
ORACLE_HOME/rdbms/admin/utlxplan.sql .. for UNIX plat formas and
ORACLE_HOME\rdbms\admin\utlxplan.sql .. for WINDOWS platforms

SQL> select * from plan_table;

no rows selected

SQL> explain plan for select * from etr where team=’cis’;
Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3490786915

—————————————————————————-
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT  |        |     2 |    30 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ETR |     2 |    30 |     3   (0)| 00:00:01 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“TEAM”=’cis’)

13 rows selected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 3490786915

—————————————————————————-
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————-
|   0 | SELECT STATEMENT  |        |     2 |    30 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ETR |     2 |    30 |     3   (0)| 00:00:01 |
—————————————————————————-

Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–

1 – filter(“TEAM”=’cis’)

13 rows selected.

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 )

w

Connecting to %s

 
%d bloggers like this: