Re: Interesting problem

Dave,

Is 'I'd love to set 10046 but  can't afford to do it for all processes 
and cannot catch the culprit' a correct interpretation of what you say? 
Unfortunately, you cannot catch the problem after it has occurred. It 
really depends on how your query is called. If it is called only by some 
sessions, and repeatedly, and if the table which is accessed is pretty 
typical of the query (ie you don't find it in most queries) - I know, 
quite a lot of conditions - perhaps you could check V$ACCESS and switch 
trace on whenever you notice (polling every minute) somebody accesses 
the table and pray for the problem to reproduce. Another solution, 
possibly more focused but otherwise a similar idea, would be to get  
SQL_ADDRESS and HASH_VALUE for the said statement  in V$SQL , and check 
for their popping up in V$SESSION. By the way, it might be interesting 
to check how many  copies of the text (how many children cursors) you 
have in V$SQL. It may happen that the same query applies to similarly 
named tables in distinct schemas and the high-water-mark of one of the 
tables (for instance) is very high (V$ACCESS could also hint at this 
type of case).

HTH

Stephane Faroult

David Turner wrote:

>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

Other related posts: