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

  • From: Martin Berger <martin.a.berger@xxxxxxxxx>
  • To: ChrisDavid.Taylor@xxxxxxxxxxxxxxx
  • Date: Tue, 10 Aug 2010 11:32:37 +0200

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: