cursor_sharing changes value on its own on RAC

显示全部楼层 · 2017-3-27 08:04:23

On both our 10.2.0.2 and 10.2.0.4 RAC (RH Linux), cursor_sharing is shown as force in gv$parameter but exact in gv$spparameter for all instances. dba_hist_parameter only has the value exact. Even though the value force exists in gv$parameter, isdefault is TRUE. But ismodified is MODIFIED (isadjusted is FALSE), while alert.log since DB creation on all nodes show no "alter system" command to change this parameter. 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 these two databases in setup.
Sessions also take this value, most of the time!. I login as sys, system, or a regular user and type "show parameter cursor_sharing" and it shows force. Then I just 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 show force, even after flushing shared_pool. I bounced this instance. gv$parameter still shows force for all instances but "show parameter" in this bounced instance shows exact now!
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.
Anybody else had this problem with this parameter changing on its own? What's your value in gv$parameter, gv$spparameter, and "show parameter cursor_sharing"? If you use EM, does the history page show different values?
Yong Huang

千问 · 2017-3-27 08:04:23

hehe...whatelse we can trust?

千问 · 2017-3-27 08:04:23

问问流云

千问 · 2017-3-27 08:04:23

abc@dw> select value from gv$parameter where name='cursor_sharing';
VALUE
-------------------------------------------------------------------------
下面全为EXACT

千问 · 2017-3-27 08:04:23

Yong Huang,on our 10.2.0.4 RAC,it shows as:
SQL> select inst_id,value,isdefault,ismodified,isadjusted
from gv$parameter where name='cursor_sharing';
INST_ID VALUE
ISDEFAULT ISMODIFIED ISADJ
---------- ------------------------------ --------- ---------- -----
1 EXACT
TRUEFALSEFALSE
4 EXACT
TRUEFALSEFALSE
3 EXACT
TRUEFALSEFALSE
2 EXACT
TRUEFALSEFALSE

SQL> show parameter cursor
NAME
TYPEVALUE
------------------------------------ ----------- ------------
cursor_sharing
stringEXACT
SQL> select inst_id,valuefrom gv$spparameter where name='cursor_sharing';
INST_ID VALUE
---------- ------------------------------
4
1
3
2
The database was migrated from 10.2.0.3 two weeks before.

千问 · 2017-3-27 08:04:23

Thanks, blue_prince. In fact, the first two weeks after we upgraded this 10.2.0.3 to 10.2.0.4, we had EXACT in gv$parameter and show parameter, and null in gv$spparameter. Today I notice FORCE in show parameter and gv$parameter. But it could exist some time ago. I don't know what could have triggered this.
Do you use EM? If so, can you check the init param history page? Keep an eye on it. It may suddenly change and then change back.
Yong Huang

千问 · 2017-3-27 08:04:23

原帖由 Yong Huang 于 2008-4-10 11:59 发表
Thanks, blue_prince. In fact, the first two weeks after we upgraded this 10.2.0.3 to 10.2.0.4, we had EXACT in gv$parameter and show parameter, and null in gv$spparameter. Today I notice FORCE in show parameter and gv$parameter. But it could exist some time ago. I don't know what could have triggered this.
Do you use EM? If so, can you check the init param history page? Keep an eye on it. It may suddenly change and then change back.
Yong Huang
Yong Huang,I checked parameter history page on EM,there were no records of cursor_sharing since the database created.
It is really strange as you mentioned.Did other one change the value?The change would be logged in the alert log,I just tested on 10.2.0.4.
[ 本帖最后由 blue_prince 于 2008-4-10 13:14 编辑 ]

千问 · 2017-3-27 08:04:23

I checked another 10.2.0.1 database,the default value of cursor_sharing is exact when the database created.And any change of this parameter can be logged in the alert log.

千问 · 2017-3-27 08:04:23

good job

千问 · 2017-3-27 08:04:23

Problem found. User error. There's a trigger in an application user's schema created as "after logon on database... execute immdeiate 'alter session set cursor_sharing=force'". After disabling the trigger, problem is solved. The trigger should have been created with the word "schema" instead of "database".
It takes a few seconds' thinking to realize that a regular user's trigger can have such effect on anybody's logon, just because it has the "logon on database" phrase. But it's us DBA's error because we created it.
Yong Huang