RE: cursor_sharing set to force on its own

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
--
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

Other related posts: