| Best way to figure out bad sql in the database [message #130668] |
Mon, 01 August 2005 21:18  |
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  |
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 |
Frank
Messages: 7901
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
