I do have a query with multiple bind variables. I dont the value of those bind variables to pass, as well as I could not able to figure those values in trace file.
Now I am trying to run that sql with an additional hint (first_rows) and would like to capture that plan in baseline. How to capture baseline of that query with bind variables?
I thought I will enable automatic baseline acpture and then run this sql with hint and could see the baseline.
Any help will be appreciated!!
02-04-2013, 10:43 AM
Be sure STATISTICS_LEVEL is NOT set to BASIC then query V$SQL_BIND_CAPTURE system view.
02-06-2013, 04:27 AM
Now I have a different question. Let me explain you step by step
(1) Presently the query is running from a middleware without any hint. And making code changes (adding hint) in the middleware requires time. The query has 6 bind variables defined. I located the query in the database with sqlid XXXX.
(2) I am running the same query from sqlplus adding a hint and defining those bind variables like below
(3) I got a SQLID, e.g YYYY for the query I ran above from sqlplus.
(1) If I capture a baseline using the sqlid (YYYY) above, can I use that plan for the query coming from middleware?Hash_plan is different though.
(2) If not, how to create a baseline for the query coming from middleware with a HINT?