CURSOR_SHARING = FORCE

Listers,

The on again and off again topic of bugs and random errors such as the ORA-00600 series has come up. On a Windows 2000 Server (SP2) running 8.1.7.0.0, we encountered a reproducible (on their machine) ORA-00600 error
(don't have it in front of me since I'm at home but think it was [17182] in
the first bracket) when using cursor_sharing=force.

The error would be thrown if we had a trailing space in a literal string, boiled down to a sample case using "select 'x ' from dual". If we flipped cursor sharing back to EXACT, the 600 error would go away, back to FORCE, error again (slightly changing the statement's case or formatting each time to force a hard parse). I can't reproduce here at home, also Win2000 and 8.1.7.0.0. So, while flipping cursor_sharing back and forth solves/reproduces the problem on the client's machine, it doesn't on mine. It appears it could be a combination of parameters/issues, in conjunction with cursor_sharing=force, that causes FORCE to throw the 600 error and EXACT to behave just fine. We also reproduced on a LINUX server with a copy of the DB with all the same parameters.

A TAR was opened to get a green light on use prior to setting it to FORCE in DEV, and while support would not guarantee no bugs would be encountered, they weren't aware of any problems in 8.1.7.0.0. We have updated the TAR with a test case, and have provided support with the init parameters and their values, but, it might take some time for them (or us) to hit on the combination. It will also be some time before the client will be able to apply all patches. So, I was hoping someone else might have run into this and know a combination of things that could cause the error. Otherwise, we will need to wait until they get up to date on patches, try again, and if the problem still reproduces, trouble-shoot with support starting from there.

FWIW, the app uses literals *only*. Using both statspack and BSTAT/ESTAT for info, parsing was responsible for a little more than 50% of CPU activity. After setting to FORCE, this dropped to less than 1%. Contention on the shared pool and library latches was greatly alleviated as well.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).