RE: cursor_sharing set to force on its own
Date: Thu, 10 Apr 2008 16:51:35 -0500
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0E66DB7D2A@MSPM1BMSGM103.ent.core.medtronic.com>
Look for a login or startup trigger that is setting this parameter.
Pat
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
Sent: Thursday, April 10, 2008 4:31 PM
To: oracle-l_at_freelists.org
Subject: cursor_sharing set to force on its own
On a 10.2.0.4 RAC database, cursor_sharing was shown as force in gv$parameter for all instances but we didn't specify it in spfile (gv$spparameter was empty for this param at the time). dba_hist_parameter only has the value exact. Alert.log files on all nodes since DB creation show no "alter system" command that changed this parameter.
Sessions also take this value, most of the time! I logged in as sys, system, or a regular user and type "show parameter cursor_sharing" and it showed force. Then I did
SQL> alter system set cursor_sharing = exact scope = both sid = '*';
System altered.
SQL> create pfile='/tmp/junk2' from spfile;
File created.
The created pfile shows:
$ grep -i cursor_sharing /tmp/junk2
*.cursor_sharing='EXACT'
I logged in again. "Show parameter" and v$parameter still showed force, even after flushing shared_pool. I bounced this instance. gv$parameter still showed force for all instances but "show parameter" in this bounced instance showed exact! Today I did the same test (bounced that instance). Even "show parameter" shows force.
EM (Enterprise Manager) has the Initialization Parameter History page. Since dba_hist_parameter never has the value force, I think EM (dbsnmp) simply logs in and gets its own session parameter and records it.
We don't have this problem in our 10.2.0.3 RAC database. There's nothing really different between the 10.2.0.3 and this database in setup (compared to that one, this DB has db_cache_advice off, db_writer_processes 2 instead of 1, dispatchers empty, filesystemio_options all, shared_servers 0).
We run 10.2.0.4 RAC, x86_64 Linux. Anybody else has this problem?
Yong Huang
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.
To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Apr 10 2008 - 16:51:35 CDT