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.
It depends what you are trying to do. You can use a bind variable in SQL*Plus by defining a variable.
https://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.
https://oracle-base.com/articles/9i/ ... lay_cursor
Or to use SQL Trace to trace the statement, along with wait events and bind values.
https://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.
https://oracle-base.com/articles/10g ... wr-reports
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 1 guest