Oracle Performance: How to Find Slow Sql

Find the Culprits!

The first step in performance tuning is to find the culprits! Well, Oracle provides a great view we can use:  V$Sql, or Gv$Sql in the case of RAC. We can simply query that view, and look for suspicious sql.

I like to find sql that has a lot of logical reads per execution, and also runs over a certain time.

Here's a simple query that I use a lot:

Look for Resource-Consumers

You can see above that there are a few jobs that take a long time to run. Of course, you can play around with the script, looking for longer or shorter runs, etc.

It's that easy!  Of course, finding the bad sql is just the start, but it's a good start!

Snappy Interviews: 100 Questions to ask Oracle DBAs

By Christopher Lawson






Oracle Performance Tuning: Find the Slow Sql