how to get explain plan for query with Bind variables

All posts relating to Oracle database administration.

Moderator: Tim...

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.

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

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

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


It depends what you are trying to do. You can use a bind variable in SQL*Plus by defining a variable. ... _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. ... lay_cursor

Or to use SQL Trace to trace the statement, along with wait events and bind values. ... 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. ... wr-reports


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:
My blog:

Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 2 guests