cursor_sharing set to force on its own

From: Yong Huang <>
Date: Thu, 10 Apr 2008 14:31:27 -0700 (PDT)
Message-ID: <>

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

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 RAC database. There's nothing really different between the 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 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
Received on Thu Apr 10 2008 - 16:31:27 CDT

Original text of this message