RE: Low level tracing of a session during soft parse

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 18 Nov 2015 07:11:03 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282B1842_at_EXMBX01.thus.corp>

Thanks for the feedback - I'll have to check the number when I get time. I'm fairly sure I used to get something from one of the 10,000 range and I was fairly sure it was this one.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

From: Michael Cunningham [napacunningham_at_gmail.com] Sent: 18 November 2015 03:55
To: Jonathan Lewis; tanel_at_tanelpoder.com Cc: oracle-l_at_freelists org
Subject: Re: Low level tracing of a session during soft parse

FYI, here is what I found

I could not get any information from 10270 trace while simply executing the sql statement wither during a hard parse or soft parse.

From the Doc ID 296377.1

alter system set events 'immediate trace name cursortrace level 577, address <hash_value>'; (levels 578-580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)

this turns off tracing alter system set events 'immediate trace name cursortrace level 2147483648, address 1';

Turning off cursortrace worked for me on: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Thanks for the help,
Michael

On Tue, Nov 17, 2015 at 11:19 AM, Michael Cunningham <napacunningham_at_gmail.com<mailto:napacunningham_at_gmail.com>> wrote: Thank you gentlemen, I will look at both of these today.

On Tue, Nov 17, 2015 at 10:21 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> wrote:

I think the event you want is 10270 and, from memory, the trace levels are a bitmap so 1,2,4,8, etc. try 15 or 8191 for lots of information.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Michael Cunningham [napacunningham_at_gmail.com<mailto:napacunningham_at_gmail.com>] Sent: 17 November 2015 17:14
To: oracle-l_at_freelists org
Subject: Low level tracing of a session during soft parse

Hello all,

I have failed in my attempts so far, but what I'm trying to do is trace a session during a soft parse where I will see in the trace that the child cursor was found in the library cache and there is no need for a hard parse. It's part of a research interest of mine.

I'm still searching for blogs that have done this already, but have not found one yet.
I've tried strace with no success
I'm also looking for trace events (like 10046) that will trace this info.

So far:
10053 won't show anything because the child cursor already has an execution plan 10046 level 12 does not show what I'm looking for

Any help is greatly appreciated.

--

Michael Cunningham

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 18 2015 - 08:11:03 CET

This message: [ Message body ]
Next message: Deas, Scott: "Flushing Bad Plan - No Longer in Shared Pool"
Previous message: Chitale, Hemant K: "RE: rman"
In reply to: Michael Cunningham: "Re: Low level tracing of a session during soft parse"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message