Thursday, October 3, 2013

Simple Way to Get SQL ID of Large and Similar Queries in Oracle

I frequently get clients' request to tune number of queries related to some application or reports. One of the first things to start the tuning process is to get the SQL_ID of the queries. On a busy system, it can be quite a hassle to find out SQL_ID of a specific query.


The most simple way to get SQL_ID of query is to add comment in the query text and then get the SQL_ID from v$SQL view on the basis of that comment. Here is a working example:

select /* MYCOMMENT */ name,age,salary
from user.mytable
where age > 78 order by name;

COL SQL_TEXT format a45

select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
from v$sql
where upper(sql_text) like '%MYCOMMENT%'
and sql_text not like '%/* MYCOMMENT1 */%' ;

Enjoy query fishing :)

30 comments:

marogel said...

Fahd,

you may want to check
http://carlos-sierra.net/2013/09/12/function-to-compute-sql_id-out-of-sql_text/

Cheers
Matthias

john bill said...

well list found it very informative and wonderful keep updates, see oracle recruitment for jobs information.

Steve Hawks said...

There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

Oracle Training Center in Chennai
Oracle Course in Chennai

Roshini RS said...

I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
Regards,
Python Training in Chennai

Andria BZ said...

Thanks for sharing this niche useful informative post to our knowledge, Actually SAP is ERP software that can be used in many companies for their day to day business activities it has great scope in future.
Regards,
SAP courses in chennai|SAP institutes in chennai|SAP Training Institute in Chennai|sap course in Chennai

Pooja Doss said...

There are lots of information about latest technology and how to get trained in them, like Best Hadoop Training In Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..

Pooja Doss said...

I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
SalesForce Training in Chennai

Pooja Doss said...

Pretty article! I found some useful information in your blog, it was awesome to read,thanks for sharing this great content to my vision, keep sharing..
Unix Training In Chennai

Pooja Doss said...

This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post,thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
Android Training In Chennai In Chennai

Pooja Doss said...

I have read your blog and i got a very useful and knowledgeable information from your blog.You have done a great job.
SAP Training in Chennai

Pooja Doss said...

Oracle Training in chennai
Thanks for sharing such a great information..Its really nice and informative..

Pooja Doss said...

Selenium Training in Chennai
Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

Pooja Doss said...

Data warehousing Training in Chennai
I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly..

Pooja Doss said...

Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
Websphere Training in Chennai

Pooja Doss said...

Oracle DBA Training in Chennai
Thanks for sharing this informative blog. I did Oracle DBA Certification in Greens Technology at Adyar. This is really useful for me to make a bright career..

Amirtha rao said...


I have read your blog, it was good to read & I am getting some useful info's through your blog keep sharing... Informatica is an ETL tools helps to transform your old business leads into new vision. Learn Informatica training in chennaifrom corporate professionals with very good experience in informatica tool.
Regards,
Best Informatica Training In Chennai|Informatica training center in Chennai|Informatica training chennai

Diya Patel said...

This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
Informatica Training in chennai | QTP Training in Chennai



PLC Training In Chennai said...

PLC Training in Chennai
PLC Training Institute in Chennai
PLC Training Center in Chennai
PLC Training Centre in Chennai
PLC SCADA Training in Chennai
SCADA Training in Chennai
Best PLC Training in Chennai
Best PLC Training Institute in Chennai
DCS Training in Chennai
Automation Training in Chennai
Automation Training Institute in Chennai
Process Automation Training in Chennai
Industrial Automation Training in Chennai
HMI Training in Chennai
VFD Training in Chennai
Inplant Training in Chennai
Embedded Training in Chennai
VLSI Training in Chennai
IT Training in Chennai
AutoCad Training in Chennai
Web Designing Training in Chennai

Mervin Parmar said...

I'am planning to join Oracle certified course. I want some expert advice whether i should go for it or not.
oracle training in chennai|oracle training institutes in chennai|FITA Chennai

complaints

Nikshitha S said...

Very nice I gathered good information from this content.
Selenium training in chennai | Selenium testing training in chennai | Selenium training chennai | FITA Velachery

Priya said...

Great content thanks for sharing this informative blog which provided me technical information keep posting.
Android training in chennai | Android course in chennai | FITA Academy reviews .


Nasreen Basu said...

too good piece of information, I had come to know about your site from my friend sajid, bangalore,i have read atleast 7 posts of yours by now, and let me tell you, your web-page gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hats off to you! Thanks a lot once again, Regards, informatica mdm training in hyderabad,informatica training in hyderabad

caroline jesi said...

Everything is fine, am happy about your blog. Thanks admin for sharing the unique content, you have done a great job I appreciate your effort and I hope you will get more positive comments from the web users.
Regards,
Oracle Training in Chennai|Oracle DBA Training in Chennai

alex martin said...

Bed Bug Treatment Leesburg VA I am really impressed with your efforts and really pleased to visit this post.

kanchana said...

Your blog is awesome..You have clearly explained about it ...Its very useful for me to know about new things..Keep on blogging..
Hadoop training in chennai

kanchana said...

Great blog..You have clearly explained about the concept..Step by step explanation is too good to understand..Its very useful for me to understand..Keep on sharing..
Informatica training in chennai

Dhivya Shree said...

Informatica is one of the most trusted and effective ETL software which is known globally. Informatica is one data warehousing ETL tool. Informatica ha very go opportuities for job across the world.
informatica training in chennai | informatica training institute in Chennai | informatica classes in Chennai

Jeanne Davies said...

Superb submit in addition to fantastic web site,Buzz App I enjoy this kind of intriguing articles or blog posts keep the idea an individual.

Melisa said...

I stand by your thoughts. It is important to make sure that the software application working precisely for the reason it originally designed for. Thus, load testing is mandatory for every application or system before deployment. Loadrunner Training in Chennai | Loadrunner training institute in Chennai|Qtp training in Chennai|Qtp training Chennai

Nikshitha S said...

The strategy you have posted on the oracle hepled me to get into the next level of this technology and had lot of informations in it.
oracle training in chennai | oracle training institutes in chennai