RE: Performance improves while tracing is on but is bad when its off

  • From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
  • To: "'ChrisDavid.Taylor@xxxxxxxxxxxxxxx'" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>, 'Martin Berger' <martin.a.berger@xxxxxxxxx>
  • Date: Tue, 10 Aug 2010 08:49:45 -0400

Tom Kyte had a good writeup of this sort of thing happening: 
http://tkyte.blogspot.com/2007/09/tuning-with-sqltracetrue.html

Basically, with tracing turned on, we get a re-parse, with bind peeking...


_____________________________________________________________________________
Stephan Uzzell | MICROS Systems, Inc.

Database Administrator - OPERA Global Technical Services
7031 Columbia Gateway Dr,  Columbia, MD  21046 | * 443.285.8000x2760 | 7 
443.285.6505

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Taylor, Chris David
Sent: Tuesday, 10 August, 2010 08:39
To: 'Martin Berger'
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Performance improves while tracing is on but is bad when its off

Ok, well at least that gives me a starting point.

It is weird though that the SQL looks exactly the same with or without tracing. 
 All the bind variables are there in both cases, so I would think the sql 
cursor(s) would be the same.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

From: Martin Berger [mailto:martin.a.berger@xxxxxxxxx]
Sent: Tuesday, August 10, 2010 4:33 AM
To: Taylor, Chris David
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: Performance improves while tracing is on but is bad when its off

can you check V$SQL_SHARED_CURSOR for the reason of the mismatch?
might be a reasonable start (I hope)

hth
 Martin

Am 09.08.2010 um 21:20 schrieb Taylor, Chris David:

I'm posting this before I poke around in Google because I know it is a bit 
vague so I'm looking for ideas.

We have an INSERT statement that runs in like 20 seconds when tracing is turned 
off (see below for the trace parameters) but runs in about 1 second when 
tracing is turned on.
How does Oracle tracing (with below SQL) impact the optimizer, and/or bind 
peeking? Or, where is a good place for me to find some more information on this 
behavior?

Here's the SQL trace enabled through an "AFTER LOGON" trigger:

IF UPPER (REC.PROGRAM) LIKE '% insert_program_name_here %' and UPPER 
(REC.OSUSER) like '% insert_username_here %'
   THEN
   EXECUTE IMMEDIATE ('alter session set max_dump_file_size=unlimited');
   EXECUTE IMMEDIATE ('ALTER SESSION SET 
tracefile_identifier=''_PROGRAM_TRC''');
   EXECUTE IMMEDIATE ('ALTER SESSION SET EVENTS ''10046 TRACE NAME CONTEXT 
forever, LEVEL 12''');
END IF;

Thanks for any ideas, thoughts.


Chris Taylor

Other related posts: