RE: Interesting problem

Dave

, another possibility is to put the query in question into a stored procedure, which starts a 10046 trace when called, and stops the trace after the query completes.

A restriction of this approach is that you wouldn't see the stat lines, but you would see the waits, parse, fetch exec,...

The stat lines are written only after the cursor closes (see http://asktom.oracle.com/pls/ask/f?p=4950:61:::::P61_ID:17000122666475 )

Vlado Barun, M.Sc.
Senior Data Architect, Cadre5
www.cadre5.com
Knoxville, TN

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult
Sent: Thursday, May 26, 2005 3:30 PM
To: dnt9000_at_yahoo.com
Cc: oracle-l_at_freelists.org
Subject: 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



--
http://www.freelists.org/webpage/oracle-l