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

56 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

Paul Miller said...

Excellent post!!! Selenium automation testing tool makes your software validation process lot simpler. Keep on updating your blog with such awesome information.
Best Selenium Training Institute in Chennai|Selenium Training in Chennai

Andrew Son said...

Thanks for your information. QTP provide most precise and independent review about a software application. This automation testing tool is ideal to determine the performance and validity of a software application.Best QTP Training in Chennai | QTP training Chennai|Loadrunner Training in Chennai

Paul Miller said...

The future of software testing is on positive note. It offers huge career prospects for talented professionals to be skilled software testers. Software testing training|Software training|Software testing training in chennai

Jhon Abraham said...

Your presentation is really nice and interesting to read.It helps to improve my skillset.Keep update you blog.
Regards,
Best Informatica Training Institute in chennai | Informatica Training chennai | Informatica Training in Chennai

Jhon anderson said...

With over a million of android apps and games,new invention has reach the high quality rates.There is a huge demand for android developers in IT sectors. Android Training in Chennai offers a best training.So,start your career in android field.
Regards,
Android courses in Chennai | Android Training | Android Training Chennai

mary Brown said...

Java Training in Chennai
Online MVC Training India | Angularjs Training | Java Training in Chennai |
Java Training in CHennai | Java Training in CHennai


JMS Training Institutes in Chennai | JSP Training Institutes in Chennai | Java Spring Hibernate Training Institutes in Chennai | EJB Training Institutes in Chennai | Hibernate Training Institutes in Chennai |

JSF Training Institutes in Chennai | Java EE Training Institutes in Chennai | J2EE Training Institutes in Chennai | | | | Core Java Training Institutes in Chennai | Java Training Institutes

Java Training in CHennai | angular2 training in chennai

mary Brown said...


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

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

Online Java Training Online Java Training | Java J2EE Online Training | JavaEE Training Institute in Chennai

mary Brown said...

Angular 2 Training in Chennai angular2 Training in Chennai | angular2 Training in Chennai

Online Java Training Typescript Training in Chennai | Angular 2 Training in Chennai

Angular 2 Training in Chennai angular2 Training in Chennai | angular2 Training in Chennai

Arthur Mac said...

An interesting piece of information.Learning new technology would help oneself at hard part of their career.It is really useful.Keep sharing more about new technology.
Regards,
Hadoop Training Chennai
| Hadoop Training in Chennai | Best hadoop training institute in Chennai

Geetha Arcus infotech said...

My Arcus offer java training with 100% placement. Our java training course that includes fundamentals and advance java training program with high priority jobs. java j2ee training with placement having more exposure in most of the industry nowadays in depth manner of java

java training in chennai

Arthur Mac said...

Great information, I like this kind of blog information really very nice and more I can easily new skills are develop after reading that post.
Android Training in Chennai | Java Training in chennai | SEO Training in Chennai | Digital Marketing Training in Chennai

Arjun Rishi said...

Useful post. Keep sharing such a useful post.

web design training in chennai

Jhon Abraham said...

For getting a bright career, education is the best way to achieve our goal. Your content tells the same and keeps such an inspiring post like this.
Best DOTNET Training institute in Chennai | DOTNET Training in chennai

Mark Smith said...

Rolex Watches Authentic Mens & Ladies Rolex Datejust, President Watches for Sale at JavyEstrella.com.

Arthur Mac said...

Thanks for taking time to share this informative post.Continue sharing.
Informatica Training in Chennai | Informatica course in Chennai |

Melba henry said...

Its very interesting sharing. keep it up. thanks.
Selenium Training | Python Training | Pentaho Training | LoadRunner Training | WebSphere Training | Node.js Training

Paul Miller said...

Wonderful post!!Java is written once and used anywhere, means general it is platform independent so it can run any platform like in android, linux, etc..,.It Is general purpose programming language. It was Origin by two programming language c and c++(c with classes).Thank you for sharing your article.
Regards,
JAVA Training | JAVA Training in Chennai

Mathew Stephen said...

Excellent post.
Cloud computing course in Chennai | Cloud Training in Chennai

Abinaya V said...

That looks like the sort of thing that you need to have an awful lot of skills to use...I am looking forward to seeing what you make with it! R Programming Training | DataStage Training | SQL Training | SAS Training | Android Training | SharePoint Training

Rohit P said...

This blog has good information on oracle , nice to read them

Thanks for share

excellent seo training provided by our experts digital marketing training in bangalore | seo training in bangalore join today

Devi.Angularjs said...

Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training

Devi.Angularjs said...

Yeoman Training JavaScript Training in Chennai

Angular 2 Training in Chennai Angular 2 Training in Chennai

HTML5 Training in Chennai

JQuery Training in Chennai JQuery Training in Chennai

D3 Training in CHennai D3 Training ReactJS Training in CHennai ReactJS Training

Gulp TrainingGulp Training CommonJS Training in Chennai CommonJS Training

Devi.Angularjs said...

HTML5 Training in Chennai HTML5 Training in Chennai | Angular 2 Training in Chennai Node.js Training in CHennai Node.js Training in chennai Ecmascript 6 Training in Chennai Ecmascript 6 Training in Chennai ES6 Training in Chennai ES6 Training in Chennai

Saradha Devi said...

Yeah really lovely one
Best Selenium Training in Chennai | Android Training in Chennai | Java Training in chennai | Webdesigning Training in Chennai

Saradha Devi said...

really good article keep sharing
Best Selenium Training in Chennai | Android Training in Chennai | Java Training in chennai | Webdesigning Training in Chennai

Nikshitha S said...

Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
cloud computing training in chennai | cloud computing courses in chennai