how to get explain plan for query with Bind variables

All posts relating to Oracle database administration.

Moderator: Tim...

how to get explain plan for query with Bind variables

Postby sivakumarocp » Wed Jan 09, 2013 9:12 am

Hi Tim,

One of our production OLTP environment one query is causing huge physical I/O upon checking found its been missing an index.
but I would like to generate Execution plan for that query since, it's using Bind variable I was unable to get explain plan.

is there any way to get Explain plan for queries which is using Bind variables.

also i need query to find CPU utilization for each session running in database, atleast top 10.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: how to get explain plan for query with Bind variables

Postby Tim... » Wed Jan 09, 2013 9:49 am

Hi.

It depends what you are trying to do. You can use a bind variable in SQL*Plus by defining a variable.

http://www.oracle-base.com/articles/mis ... _variables

In this way you can get an EXPLAIN PLAN for a statement that is using a bind variable.

If you mean from a running system, then the options are to pull the plan out of the V$SQL_PLAN view.

http://www.oracle-base.com/articles/9i/ ... lay_cursor

Or to use SQL Trace to trace the statement, along with wait events and bind values.

http://www.oracle-base.com/articles/mis ... tkprof.php

The top sessions based on CPU is probably easiest to get using AWR reports, or statspack if you are not licensed for Diagnostics and Tuning pack.

http://www.oracle-base.com/articles/10g ... wr-reports
http://www.oracle-base.com/articles/8i/statspack-8i.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17957
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests