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

6 comments:

Anonymous 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

sasi said...


I have to agree with everything in this post. Thanks for useful sharing information.
Hadoop Training in Chennai
Hadoop Training in Bangalore
Big Data Course in Coimbatore
Big data training in coimbatore
Big Data Hadoop Training in Chennai
Big Data Courses in Bangalore
php training in coimbatore
salesforce training in bangalore

sasi said...

The blog you shared is very good. I expect more information from you like this blog. Thankyou.
Artificial Intelligence Course in Chennai
ai courses in chennai
artificial intelligence training in chennai
ai classes in chennai
best artificial intelligence training in chennai
Hadoop Training in Bangalore
salesforce training in bangalore
Python Training in Bangalore

Reshma said...

Great post. keep sharing such a worthy information
Tally Course in chennai
Tally Course in Bangalore
Tally Training in Bangalore
Tally training coimbatore
Tally course in madurai
Tally Course in Hyderabad
Tally Training in Chennai
Tally Course in Coimbatore
Tally Training in Hyderabad
Tally institute in Bangalore
Selenium Course in Bangalore

sasi said...

The blog you shared is very good. I expect more information from you like this blog. Thankyou.
Artificial Intelligence Course in Chennai
ai courses in chennai
artificial intelligence training in chennai
ai classes in chennai
best artificial intelligence training in chennai
Hadoop Training in Bangalore
salesforce training in bangalore
Python Training in Bangalore


cathrine juliet said...

Nice article I was really impressed by seeing this blog, it was very interesting and it is very useful for me.

Big Data Hadoop Training In Chennai | Big Data Hadoop Training In anna nagar | Big Data Hadoop Training In omr | Big Data Hadoop Training In porur | Big Data Hadoop Training In tambaram | Big Data Hadoop Training In velachery