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

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 9 Aug 2010 14:20:51 -0500

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<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.

Other related posts: