Re: cursor_sharing set to force on its own

I have a non-RAC database that I've upgraded to 10.2.0.4. I had cursor_sharing set to similar in the spfile and it's still that value. I checked both gv$parameter and gv$spparameter and both show similar.

Finn

On 4/10/08, Elliott, Patrick <patrick.elliott_at_medtronic.com> wrote:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l