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

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

Chris Hemsworth said...

The article is so informative. This is more helpful for our
selenium training in chennai
selenium online courses best selenium online training
selenium testing training
selenium classes
Thanks for sharing.

Naveen S said...

This is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted. Thank you so much for this article.

Learn Best Digital Marketing Course in Chennai

Digital Marketing Course Training with Placement in Chennai

Best Big Data Course Training with Placement in Chennai

Big Data Analytics and Hadoop Course Training in Chennai

Best Data Science Course Training with Placement in Chennai

Data Science Online Certification Course Training in Chennai

Learn Best Android Development Course Training Institute in Chennai

Android Application Development Programming Course Training in Chennai

Learn Best AngularJS 4 Course Online Training and Placement Institute in Chennai

Learn Digital Marketing Course Training in Chennai

Digital Marketing Training with Placement Institute in Chennai

Learn Seo Course Training Institute in Chennai

Learn Social Media Marketing Training with Placement Institute in Chennai

zuan said...

Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
Web Designing Training Institute in Chennai | web design training class in chennai | web designing course in chennai with placement
Mobile Application Development Courses in chennai
Data Science Training in Chennai | Data Science courses in Chennai
Professional packers and movers in chennai | PDY Packers | Household Goods Shifting
Web Designing Training Institute in Chennai | Web Designing courses in Chennai
Google ads services | Google Ads Management agency
Web Designing Course in Chennai | Web Designing Training in Chennai

revathi said...

best php training in chennai
best php developer institution chennai
best php training with placement in chennai
best php training center in chennai
best php course in chennai

priya said...

php online training in chennai
php programming center in chennai
php class in chennnai
php certification course
php developer training institution chennai
php training in chennnai
php mysql course in chennai
php institute in chennnai
php course in chennnai
php training with placement in chennnai
php developer course

priya said...

appium online training
appium training centres in chennai
best appium training institute in chennnai
apppium course
mobile appium in chennnai
mobile training in chennnai
appium training institute in chennnai

lokesh said...

I feel so happy to read this blog..Thanks for sharing this informative blog..Nice post...
Data Science Course in Chennai
Data Science Courses in Bangalore
Data Science Course in Coimbatore
Data Science Course in Hyderabad
Data Science Institute in Marathahalli
Data Science Training in Bangalore
Best Data Science Courses in Bangalore
Data Science Institute in Bangalore
Spoken English Classes in Bangalore
DevOps Training in Bangalore

mindreader said...

Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.
Java Training in Chennai
Java Training Institute in Chennai
Java course in chennai
Java Training classes
Java Training
Java programming classes
core Java course

Anonymous said...

Great efforts put to find the list of articles that are very useful to know. I’m thoroughly enjoying your blog. And Good comments create relations. You’re doing great work. Keep it up.

Magento Development Training Course in Chennai Zuan Education

Selenium Training Course in Chennai Zuan Education

w3webschool said...

Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.
Digital Marketing Course In Kolkata

devasuresh121@gmail.com said...

Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.

7 tips to start a career in digital marketing

“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

we have offered to the advanced syllabus course digital marketing for available join now

more details click the link now

https://www.webdschool.com/digital-marketing-course-in-chennai.html

devasuresh121@gmail.com said...

Amazing blog is very informative.

Web designing trends in 2020

When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.


we have offered to the advanced syllabus course web design and development for available join now

more details click the link now

https://www.webdschool.com/web-development-course-in-chennai.html

adam said...

Thanks for the interesting blog that you have implemented here. Very helpful and innovative. Waiting for your next upcoming article.
Mobile app development training
App development training
Mobile app development course
Mobile development course
Mobile application development course
Learn mobile application development

Anurag Srivastava said...

I have been reading for the past two days about your blogs and topics, still on fetching! Wondering about your words on each line was massively effective.... Candidates can check their result easily within few seconds through this page when the officials release Sarkari result. On our webpage, we are providing all the State Government/ Central Government/ PSC/ Entrance Exam Results

Mithun said...

Thanks for the informative article About Java. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.
Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

Rajesh said...

It is actually a great and helpful piece of information about Java. I am satisfied that you simply shared this helpful information with us. Please stay us informed like this. Thanks for sharing.

Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

akshaya said...

I thank you for taking the initiative to help people with your highly resourceful blog. I am requesting you to post many useful blogs like this. Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery

subha said...

Good job! Fruitful article. I like this very much. It is very useful for my research. It shows your interest in this topic very well. I hope you will post some more information about the software. shaare more
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai

prathyusha said...

Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data science training Hyderabad

360digitmg said...

Gangaur Realtech is a professionally managed organisation specializing in real estate services where integrated services are provided by professionals to its clients seeking increased value by owning, occupying or investing in real estate.
Data Science Training in Hyderabad

Anonymous said...

Google offers advertisements which appear in search results on google.com with the use of Google AdWords or advertisements that
appear on other websites through the Display Network and Google’s AdSense program.
With google ads you can appear in the top for searched keywords.Thus you will receive more relevant customers for your business.
Google Ads Services
You can get Apple-certified repairs and service at the Apple Store or with one of our Apple Authorized Service Providers.
mobile phone repair in North Olmsted

Python said...

Thank you for sharing.
Data Science Online Training
Python Online Training
Salesforce Online Training