Thanks for the question, John.

Asked: May 14, 2016 - 3:51 am UTC

Last updated: January 28, 2021 - 9:55 am UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Good Evening,

I came across dbms_stats.set_global_prefs ('no_invalidate', 'TRUE'). By setting this value to TRUE, Oracle "Does not invalidate the dependent cursors if set to TRUE" when gathering statistics. This seems like the no brainer option over the default 'AUTO_INVALIDATE'. What's the catch? Is it if the stats changed significantly, we want to cursors to get invalidated? Can we really gather statistics without causing any cursor to get invalidated? One of the complains for years has been that gathering statistics will invalidate all cursors. Is 'no_invalidate', 'TRUE' the solution to that decades old problem?

Thanks,

John

and Connor said...

There are three kinds of invalidation

1) no_invalidate=>false (ie, we *will* invalidate, man I hate double negatives)
====================
- gather stats
- boom, all cursors toast
- we pick up the new stats, but potential but big parse storm

2) no_invalidate=>true (ie, we will not invalidate)
====================
- gather stats
- all cursors remain
- indeterminate when we'll use those new stats...could be days or weeks ? And if things go bad "next week", how will you know it was due to the stats you collect last week ?

3) no_invalidate=>auto
====================
- gather stats
- all cursors "remain" (see next)
- over the next 5 hours, we decide on invalidation (or reparsing)

I prefer this one...and with Support's blessing, you can alter the 5 hours to whatever you want. My personal preference is lower for the reason as #2 above, I'd like to know pretty soon if my stats cause a drama, not discover it 5 hours later.

Hope this helps.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 15, 2016 - 3:19 am UTC

That is a clear as rocky mountain water. Thanks, Connor!

BTW: I agree, it should just be 'invalidate'. Where is most of Oracle new features developed? The original was developed in the US, but not sure where it is being developed since.

thanks

John

May 16, 2016 - 12:58 am UTC

"Where is most of Oracle new features developed?"

We're a global organisation

AUTO_INVALIDATE

Rajeshwaran, Jeyabal, September 11, 2017 - 12:55 pm UTC

Team:

you can alter the 5 hours to whatever you want.

with AUTO_INVALIDATE seems to be false by default, two questions.

a) how do we say it is 5 hours here ?
b) could you also so us, how that could be altered?

demo@ORA12C> with function foo
  2     return varchar2 as
  3     begin
  4             if dbms_stats.to_no_invalidate_type(dbms_stats.get_param('NO_INVALIDATE')) then
  5                     return 'True';
  6             else
  7                     return 'False';
  8             end if;
  9     end;
 10  select foo x1, dbms_stats.get_param('NO_INVALIDATE') x2 from dual;
 11  /

X1         X2
---------- ------------------------------
False      DBMS_STATS.AUTO_INVALIDATE

demo@ORA12C>

September 13, 2017 - 7:29 am UTC

I cant tell you want that parameter is (it's hidden) but MOS Note 1477717.1 will be assistance :-)

As with all hidden parameters, you should set that under the guidance of Oracle Support.

bc, May 09, 2018 - 6:56 pm UTC

The parameter that you are looking for is

_optimizer_invalidation_period

It's description says - time window for invalidation of cursors of analyzed objects

Query that you can run to identify all of your hidden parameters

select ksppinm, ksppdesc from x$ksppi where substr(ksppinm,1,1) = '_'

With this knowledge comes power, and with power comes great responsibility. Discuss your needs with the wonderful folks at Oracle support prior to changing any values.

Schema level preference

Rajeshwaran, Jeyabal, January 27, 2021 - 1:14 pm UTC

Team:

was reading about statistics perference from docs at the below link.
https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/options-for-optimizer-statistics-gathering.html#GUID-FEE5B9A2-413E-42E8-86BF-0C12B818A95C

The below demo was from 19.9 database.
could you pleae help us to understand, though we got 'incremental' preference set at schema level to "True", why it returns "false" upon reterival?

demo@PDB1> grant connect,resource,create table to demo2 identified by "demo2";

Grant succeeded.

demo@PDB1> alter user demo2 default tablespace users;

User altered.

demo@PDB1> alter user demo2 quota unlimited on users;

User altered.

demo@PDB1> create table demo2.t
  2  partition by hash(user_id)
  3  partitions 2
  4  as select * from all_users;

Table created.

demo@PDB1> col prefs for a20
demo@PDB1> select dbms_Stats.get_prefs('incremental','demo2','T') prefs from dual;

PREFS
--------------------
FALSE

demo@PDB1> exec dbms_Stats.set_table_prefs('demo2','T','incremental','true');

PL/SQL procedure successfully completed.

demo@PDB1> select dbms_Stats.get_prefs('incremental','demo2','T') prefs from dual;

PREFS
--------------------
TRUE

demo@PDB1>
demo@PDB1> select dbms_Stats.get_prefs('incremental','demo2')  prefs from dual;

PREFS
--------------------
FALSE

demo@PDB1> exec dbms_Stats.set_schema_prefs('demo2','incremental','true');

PL/SQL procedure successfully completed.

demo@PDB1> select dbms_Stats.get_prefs('incremental','demo2')  prefs from dual;

PREFS
--------------------
FALSE

demo@PDB1> select dbms_Stats.get_prefs('incremental','demo2','T')  prefs from dual;

PREFS
--------------------
TRUE

demo@PDB1>

January 28, 2021 - 9:55 am UTC

schema preferences are a syntactical convenience, ie, they loop through the objects in the schema and set the object level preferences. There is no "schema-level" repository of preferences as such.

eg

SQL> exec dbms_stats.set_schema_prefs('scott','incremental','true')

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_prefs('incremental','scott','emp') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','SCOTT','EMP')
------------------------------------------------------------------------------------------
TRUE

1 row selected.

SQL> create table scott.new_table ( x int );

Table created.

SQL> select dbms_stats.get_prefs('incremental','scott','new_table') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL','SCOTT','NEW_TABLE')
------------------------------------------------------------------------------------------
FALSE

1 row selected.

Because NEW_TAB was created AFTER we set the schema preference (which really just set it for each object in the scott schema that existed when we called it)

Breadcrumb

Question and Answer