_DISABLE_OBJSTAT_DEL_BROADCAST = FALSE - no info found

[Former Member]

Hello guys,

I need to set this param to false in the systems, as I upgraded Oracle to 10.2.0.5, but I cannot find anywhere if this parameter can be modified online or if I need to restart the database.

DISABLEOBJSTAT_DEL_BROADCAST = FALSE

Note 830576 - Parameter recommendations for Oracle 10g

Thanks in advance for your help

[stefan_koehler · Accepted solution]

Hello Jordy,

you can use the following query to determine the properties / settings of the hidden parameter "_disable_objstat_del_broadcast".

SELECT
  x.ksppinm name,
  y.ksppstvl VALUE,
  decode(ksppity,    1,   'BOOLEAN',    2,   'STRING',    3,   'INTEGER',    4,   'PARAMETER FILE',    5,   'RESERVED',    6,   'BIG INTEGER',    'UNKNOWN') typ,
  decode(ksppstdf,    'TRUE',   'DEFAULT VALUE',    'FALSE',   'INIT.ORA') isdefault,  
  decode(bitand(ksppiflg / 256,   1),    1,   'IS_SESS_MOD(TRUE)',    'FALSE') isses_modifiable,
  decode(bitand(ksppiflg / 65536,   3),    1,   'MODSYS(NONDEFERED)',    2,   'MODSYS(DEFERED)',    3,   'MODSYS(*NONDEFERED*)',    'FALSE') issys_modifiable,
  decode(bitand(ksppstvf,   7),    1,   'MODIFIED_BY(SESSION)',    4,   'MODIFIED_BY(SYSTEM)',    'FALSE') is_modified,  
  decode(bitand(ksppstvf,   2),    2,   'ORA_STARTUP_MOD(TRUE)',    'FALSE') is_adjusted,
  ksppdesc description,
  ksppstcmnt update_comment
FROM x$ksppi x,
  x$ksppcv y
WHERE x.inst_id = userenv('Instance')
 AND y.inst_id = userenv('Instance')
 AND x.indx = y.indx
 AND x.ksppinm = '_disable_objstat_del_broadcast';

@Orkun: Your query does not work, because of it is a hidden parameter.

Regards

Stefan

[Former Member · Accepted solution]

Hello Stefan, Orkun,

Indeed:

SQL> select name,isses_modifiable from v$parameter where name='_DISABLE_OBJSTAT_DEL_BROADCAST';

no rows selected

but the script from you works:

SQL> SELECT

2 x.ksppinm name,

3 y.ksppstvl VALUE,

4 decode(ksppity, 1, 'BOOLEAN', 2, 'STRING', 3, 'INTEGER', 4, 'PARAMETER FILE', 5, 'RESERVED', 6, 'BIG INTEGER', 'UNKNOWN') typ,

decode(ksppstdf, 'TRUE', 'DEFAULT VALUE', 'FALSE', 'INIT.ORA') isdefault,

5 6 decode(bitand(ksppiflg / 256, 1), 1, 'IS_SESS_MOD(TRUE)', 'FALSE') isses_modifiable,

7 decode(bitand(ksppiflg / 65536, 3), 1, 'MODSYS(NONDEFERED)', 2, 'MODSYS(DEFERED)', 3, 'MODSYS(NONDEFERED)', 'FALSE') issys_modifiable,

8 decode(bitand(ksppstvf, 7), 1, 'MODIFIED_BY(SESSION)', 4, 'MODIFIED_BY(SYSTEM)', 'FALSE') is_modified,

9 decode(bitand(ksppstvf, 2), 2, 'ORA_STARTUP_MOD(TRUE)', 'FALSE') is_adjusted,

10 ksppdesc description,

11 ksppstcmnt update_comment

12 FROM x$ksppi x,

13 x$ksppcv y

14 WHERE x.inst_id = userenv('Instance')

15 AND y.inst_id = userenv('Instance')

16 AND x.indx = y.indx

17 AND x.ksppinm = '_disable_objstat_del_broadcast';

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

disableobjstat_del_broadcast

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

TRUE

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

BOOLEAN DEFAULT VALUE IS_SESS_MOD(TRUE) MODSYS(NONDEFERED)

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

FALSE FALSE

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

disable the object level delete statistics broadcast

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

NAME

-

---

VALUE

-

---

TYP ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE

-

---

-

---

-

---

-

---

IS_MODIFIED IS_ADJUSTED

-

---

-

---

DESCRIPTION

-

---

UPDATE_COMMENT

-

---

But how can I check now ? if it can be set online or offline ?

Great response, thanks in advance,

[Former Member · Accepted solution]

Best way to understand this, execute the statement, below and see the result;

alter system set "_DISABLE_OBJSTAT_DEL_BROADCAST" = FALSE scope=MEMORY;

if it works, you are able to change the parameter online

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Orkun,

If I put: alter system set "_DISABLE_OBJSTAT_DEL_BROADCAST" = FALSE scope=MEMORY;

and it works with no error it means it can be done online, as you said.

If it works, it means that there is no need to define scope=both after, right ?

scope=both

Alters an initialization parameter in the spfile as well as in the running instance.

scope=memory

Changes the parameter's value for the running instance only. As soon as the instance is stopped and started, this change will be lost.

Just to see if I understood right, if I set it to scope=memeory and it is not possible, I should get some error, right ?

Thanks,

J.

[Former Member · Accepted solution]

>> Just to see if I understood right, if I set it to scope=memeory and it is not possible, I should get some error, right ?

In short, yes

[Former Member · Accepted solution]

Danke

[Former Member · Accepted solution]

bitte

[stefan_koehler · Accepted solution]

Hello Jordy,

i am sorry but i have to revise the statement of Orkun in some way.

If I put: alter system set "_DISABLE_OBJSTAT_DEL_BROADCAST" = FALSE scope=MEMORY; and it works with no error it means it can be done online, as you said.

The core of his statement is correct, but if the command works without any error, it does not automatically mean that all existing sessions are using this setting from that moment. This depends on the value of the column "ISSYS_MODIFIABLE".

My previous posted query will also show you that column for the hidden parameters. For the explanation of the three different possible values - please check the official documentation:

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/dynviews_2087.htm#REFRN30176

The posted output from that query is very hard to read - so please check on your own, if the existing sessions are already working with that setting.

Regards

Stefan

[Former Member · Accepted solution]

I have set it to false in one system:

SQL> show parameter DISABLEOBJSTAT_DEL_BROADCAST;

NAME TYPE VALUE

-

---

-

---

-

---

disableobjstat_del_broadcast boolean FALSE

how I can check if the changes took effect ?

Thanks

[stefan_koehler · Accepted solution]

Hello Jordy,

how I can check if the changes took effect ?

Well as already mentioned it depends on the value of column ISSYS_MODIFIABLE.

Run the following query:

SQL> SELECT
  x.ksppinm name,
  y.ksppstvl VALUE,
  decode(bitand(ksppiflg / 65536,   3),   1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE' ) issys_modifiable,
  ksppdesc description,
FROM 
  x$ksppi x,
  x$ksppcv y
WHERE x.inst_id = userenv('Instance')
 AND y.inst_id = userenv('Instance')
 AND x.indx = y.indx
 AND x.ksppinm = '_disable_objstat_del_broadcast';

If the value of ISSYS_MODIFIABLE is "IMMEDIATE" all sessions are working with your new setting - if the value is DEFERRED you need to restart your oracle sessions to get the new setting active. Restarting the oracle session can be done by restarting each SAP work process once.

Regards

Stefan

[Former Member]

Hello guys,
> 
> I need to set this param to false in the systems, as I upgraded Oracle to 10.2.0.5, but I cannot find anywhere if this parameter can be modified online or if I need to restart the database.
> 
> DISABLEOBJSTAT_DEL_BROADCAST = FALSE
> 
> Note 830576 - Parameter recommendations for Oracle 10g
> 
> Thanks in advance for your help

Hi,

Execute the select statement, below;

select name,isses_modifiable from v$parameter where name='_DISABLE_OBJSTAT_DEL_BROADCAST';

Best regards,

Orkun Gedik

[Former Member]

While waiting for a reply, you could have tried in a test system, whether or not setting the parameter with scope = both will throw an error message.