Sunday, February 1, 2009

Find Bind variable value

The usages of bind variable increase SQL query performance. Suppose you have a SQL query that execute several times and only change are in literal values, IN such case professionals are suggested to use bind variable rather than hard cote value. The impact is significant on query performance if you use bind variable.

But the problem is rice when you try to collect full query text from database, the value of the bind variable is not present in the query text. But you may need those values when you are working on that SQL query specially when you try to tune that query.

Here are two query that help you to find the valu of bind variable. The only thing you needed is the SQL ID

1. If your query was run within 30 minutes:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM v$sql_bind_capture WHERE sql_id='';


2. If your query was run more than 30 minutes ago. For this you also need a snapshot Id. By default oracle store snapshots of last 7 days:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING
FROM DBA_HIST_SQLBIND WHERE SQL_ID='' and SNAP_ID='';


Note: v$sql_bind_capture and DBA_HIST_SQLBIND stors only those bind values
which are given in HWERE clause and they do not store timstamp,lob,clob type value



Related Topics:

  1. Bind Variable

11 comments:

Anonymous said...

Thank You. Exactly what I wanted.

sap upgrade evaluation said...

As we all know that bind variables are very important in Oracle. It's necessary to find bind variable value. In this post two options are given to do the mentioned task. I tried both and they work properly. You can also have an idea of how to do it.

Anonymous said...

Excellent thanks. It's worth noting to use gv$ instead of v$ on RAC environments

Kayal m said...




Very creative post, truly this is a very good job. Your explanation is very superb and I appreciate your great efforts. I like a more valuable post from your blog...

Tableau Training in Chennai
Tableau Course in Chennai
Pega Training in Chennai
Excel Training in Chennai
Power BI Training in Chennai
Primavera Training in Chennai
Unix Training in Chennai
Tableau Training in Chennai
Tableau Course in Chennai

kiruthika said...

Very nice blog, Thank you for providing good information.
aviation institute in Chennai
cabin crew training in Chennai
diploma in airport management course in Chennai
airport ground staff training courses in Chennai
Aviation Academy in Chennai
air hostess training in Chennai
airport management courses in Chennai
ground staff training in Chennai

sandeep saxena said...

This blog was making more interesting. Keep adding more information on your page.

C C++ Training in Chennai
C Training in Chennai
C++ Training in Chennai
JMeter Training in Chennai
JMeter Training Institute in Chennai
Appium Training in Chennai
javascript training in chennai
core java training in chennai

velraj said...

The innovative thinking of you in this blog. This blog makes me very useful to learn.
Selenium Training in Chennai
Best selenium Training Institute in Chennai
selenium testing course in chennai
Selenium Course in Chennai
Selenium training in Thiruvanmiyur
Selenium Training in Velachery
Python Training in Chennai
Software testing training in chennai
Python Training in Chennai
JAVA Training in Chennai

Anbarasan14 said...

Nice post. Thanks for sharing.
Spoken English Classes in Chennai Anna Nagar
Spoken English Class in Porur
Spoken English Class in Vadapalani
Spoken English Class in Thiruvanmiyur
Spoken English Class in Chennai
Best English Speaking Classes in Mumbai
English Speaking Course in Mumbai
IELTS Training in Chennai
IELTS Coaching in Chennai
IELTS Mumbai

cynthiawilliams said...

Fabulous post admin, hats off to your creativity. I got lots of information here.
Web Designing Course in chennai
web designing training in chennai
Web Designing Institute in Chennai
Ethical Hacking Training in Chennai
PHP Training in Chennai
Angular 6 Training in Chennai
Web Designing Course in Porur
Web Designing Course in Tambaram
Web Designing Course in Adyar
Web Designing Course in Vadapalani

Chris Hemsworth said...

The article is so informative. This is more helpful for our
Best online software testing training course institute in chennai with placement
Best selenium testing online course training in chennai
Learn best software testing online certification course class in chennai with placement
Thanks for sharing.

divi said...

Thank you for this wonderful post, It really nice and informative. Keep sharing!!
web design company in velachery