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


marogel said...


you may want to check


Mathew Stephen said...
This comment has been removed by the author.
for IT the said...

I have read your blog its very attractive and impressive. I like it your blog.

Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

Saradha Devi said...

great article thanks for sharing

Selenium Training | Selenium Training Institute in Chennai | Best Selenium Training Institutes in Chennai | Software Testing Training in Chennai

neha said...

I have been reading out a lot of your articles and that i ought to say pretty nice stuff. I will certainly bookmark your internet website
Devops Online Training

Steve Hawks said...

Much obliged to you for magnificent data I was looking for this information for my main goal.
Manual Testing course in Chennai | Manual Testing Training Chennai