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