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

  • From: Harel Safra <harel.safra@xxxxxxxxx>
  • To: "ChrisDavid.Taylor@xxxxxxxxxxxxxxx" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • Date: Mon, 9 Aug 2010 22:28:48 +0300

Turning tracing on changes the execution environment and may cause the SQL
to use a different plan.
Could you post the plans used in both cases and execution statistics?

Harel Safra
(Sent from my phone, excuse the terseness)

ב-9 באוג 2010, בשעה 22:20, "Taylor, Chris David" <
ChrisDavid.Taylor@xxxxxxxxxxxxxxx> כתב/ה:

 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*

*Sr. Oracle DBA*

Ingram Barge Company

Nashville, TN 37205

Office: 615-517-3355

Cell: 615-663-1673

Email: 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.*

Other related posts: