RE: cursor_sharing set to force on its own
- From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
- To: "yong321@xxxxxxxxx" <yong321@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Apr 2008 16:51:35 -0500
Look for a login or startup trigger that is setting this parameter.
Pat
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Yong Huang
Sent: Thursday, April 10, 2008 4:31 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
