| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Interesting problem
Dave,
>> a query that scans maybe 4 blocks and returns data instantly
>> when I run it manually but throughout the day periodically
>> it takes well over 10 seconds, which isn't acceptable.
How do you know this...and more importantly are you saying that the table only contains 4 block and thus a Full Table Scan could never read more than 4 blocks? What I'm getting at is how do you know that query is always the same. Is it the same literal (hard coded) in the WHERE clause every time? Can different WHERE conditions take longer?
>> We've been using set client info on many of our
>> production systems for some time and this helps us
>> create resource usage reports so we can identify
>> owners of bottlenecks.
This is idea is very cool...one I have never spent much time on. Want to share you docs, logic, scripts on how this is done.
Anyone have any good links or docs about; Practical (simple!!!) Oracle Resource Usage Collection / Reports.
I support a web based app for one client and bad sql often goes unseen until many sessions start doing the same thing. Meaning even bad sql is hard to "watch" goes along to the human eye the waits are short...but is computing time these waits are probably awful.
Thanks,
Chris Marquez
Oracle DBA
-----Original Message-----
From: oracle-l-bounce_at_freelists.org on behalf of David Turner
Sent: Thu 5/26/2005 2:52 PM
To: oracle-l_at_freelists.org
Subject: Interesting problem
We've been using set client info on many of our production systems for some time and this helps us create resource usage reports so we can identify owners of bottlenecks. However, we do have some older systems where this isn't implemented and the transactions are much shorted so it's very hard to identify why some of our sessions have longer response times.
For instance I have a query that scans maybe 4 blocks and returns data instantly when I run it manually but throughout the day periodically it takes well over 10 seconds, which isn't acceptable.
I've set up a script that montors the db every minute but it just doesn't appear to be catching what is causing our normally fast running queries to periodically run long. If any of you have any suggestions on diagnosing this I'd appreciate it.
The main areas I've focused on are
A minute by minute report of v$session and v$process info
Many of the standard performance tuning stats tracked via statspack
Cronjobs
Feed processes
with no luck so far.
Thx, Dave
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri May 27 2005 - 10:54:49 CDT
 |
 |