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 :)

19 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