Re: Norbert Debes - trace file analyser
Date: Fri, 12 Aug 2011 10:14:51 +0100
Message-ID: <CABe10sbMW+RAMtEQVmUNnzuQq2MToS0KReZuiqX79RcyLkVnRA_at_mail.gmail.com>
On Fri, Aug 12, 2011 at 9:37 AM, Dunbar, Norman (Capgemini) < norman.dunbar.capgemini_at_environment-agency.gov.uk> wrote:
PS. I really do hate the cursor numbers in 11g by the way, it's easy to
> scan a raw trace file looking for WAIT #2 or similar, but trying to scan
> for WAIT #140136345356328 is not quite so simple. By eye anyway, it's ok
> with a reg-exp find in Context (text editor).
>
> PPS. I notice a new "verb" in the trace file, CLOSE #140136345356328. I
> presume it does what it says it does, this cursor is now closed? (Is
> there a new features in trace files manual anywhere?)
>
>
I believe there is a MOS note on some of the changes, but of course I can't
currently find it. The extract below is from an email (which I wouldn't
normally do, but as I say I believe at least most of this is available on
the support site somewhere) emphasis mine not the original authors.
The problem was that with the restructured cursor caching in 11g a
> re-executed statement would come through with different cursor number for
> each execute.* As the cursor number and the sqltext did not match from the
> last use of the cursor, the sqltext was printed out for every execution of
> a cursor*. This was a sizable problem for apps like e-Biz where is typical
> sql statement is many thousands of characters long. In some cases this
> increased the size of the trace file by an order of magnitude, and I'm sure
> that it could be even worse.
> The solution was to pass the cached cursor handle (which probably is the
> address) through and display it instead of the cursor number. This should
> mean that the text is only printed on the first parse of SQL statement and
> the size of the trace file goes back to something reasonable.
> There are a few other recent changes in trace files too such as CLOSE lines
> and row source details by default after the first execution.
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 12 2011 - 04:14:51 CDT