This forum is currently locked. You can't register or post questions at this time. (read more)

how to get explain plan for query with Bind variables

All posts relating to Oracle database administration.

Moderator: Tim...

sivakumarocp
Advisor
Posts: 260
Joined: Sat Jun 27, 2009 3:53 am
Location: India

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron