Home » RDBMS Server » Performance Tuning » Best way to figure out bad sql in the database
Best way to figure out bad sql in the database [message #130668] Mon, 01 August 2005 21:18 Go to next message
sumang24
Messages: 10
Registered: July 2005
Junior Member
What is the best way to figure out what sql is bad when load testing is done on the database . We need a way to pinpoint the bad sqls out in the several stored procedures when the database is put under stress test and then do performance tuning on those.

I know we can use sqltrace and tkprof what else can we do to do this humonguous task easily.

Thanks a lot!
Kevin
Re: Best way to figure out bad sql in the database [message #130677 is a reply to message #130668] Mon, 01 August 2005 22:53 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
Use tha data dictionary views - for example v$sql/v&sql_text join to v$session. Depends also on the Oracle version - in 10g you could use the new views for waits, even the EM console to spot and run throught the Advisor these statements,etc. EM is to slow for me though, and it kicks you out some times...
Also, before starting to dig into the SQL, verify you do not have table/index problems like indexes with old or no statistics, unefficiat indexes, duplicate indexes, disabled constraints, tables with no PKs, etc.
Tomorrow when I get to work, I'll post for you some of the scripts I use daily.
Hope this will help.mj
Re: Best way to figure out bad sql in the database [message #130797 is a reply to message #130668] Tue, 02 August 2005 10:45 Go to previous messageGo to next message
Frank
Messages: 7892
Registered: March 2000
Senior Member
First define what a bad sql is.
If your system is cpu bound, little will be gained by tuning IO-intensive queries.
If you don't use bind-variables, each individual query will not cause you trouble, but the lot will.
If there is one query that is performed once a month that takes 4 hours, but a query that is performed 10 times per minute takes 5 seconds, which is the 'bad sql'?

Maybe a bit exaggerated, but can you see the point?

Once you know what you are looking for, you can choose your tools. e.g. To identify queries not using bind-variables, Tom Kyte has written a script

You can use statspack to compare problem-situations with situations in which the database was known to perform well.

hth
Re: Best way to figure out bad sql in the database [message #131082 is a reply to message #130668] Thu, 04 August 2005 05:15 Go to previous message
rgopani
Messages: 15
Registered: June 2005
Location: rupesh_gopani@yahoo.com
Junior Member

use statspack utility at level 6 which gives you highest buffer get/high parse etc. sqls and tune these sql statements,simple.

[Updated on: Thu, 04 August 2005 05:17]

Report message to a moderator

Previous Topic: How to avoid full tablescan using group function?
Next Topic: Application Tuning
Goto Forum:
  


Current Time: Fri May 27 17:09:14 CDT 2022