how to get explain plan for query with Bind variables

[sivakumarocp]

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.

[Tim...]

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...