Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 148,962
  • Archives

  • Categories

  • Subscribe

  • Advertisements

Viagra For SQL’s. DBMS_STATS (NO_INVALIDATE argument/parameter) ?

Posted by FatDBA on August 20, 2013

While trying to get any resolution for one of our old problem (Database Hangs while Gathering Stats – Schema Wise), one of the oracle support analyst recommends us to gather stats next time with no_invalidate parameter. We actually never tested/tried this option with DBMS_STATS procedure before.

Let me put some light over this argument/parameter and how it can act as a Viagra for your SQL’s. Fast, Instant …

DBMS_STATS provides us an argument to control invalidation of SQL plans available and are sitting in your Library Cache and are used by your CBO. This is quite decisive when used to control SQL executions and query optimizations and performance.

It has three arguments types.
dbms_stats.set_param (no_invalidate false):  
This option will orders that a change in statistics always invalidates the dependent sql cursors/plans immediately upon a change to the statistics. Very much similar to ‘ALTER SYSTEM FLUSH SHARED POOL’.
dbms_stats.set_param (no_invalidate true):  In this case, a change in statistics will never nullify or voids current SQL execution plans in Library Cache.
dbms_stats.set_param (no_invalidate dbms_stats.auto_invalidate):  This is the default (Since Oracle 10g). This default type says that Oracle will not invalidate the old plan immediately after new statistics are generated.

Now a question:
After how many seconds, minutes, hours or probably days, old plans gets invalidate in case of no_invalidate.auto_invalidate which is a Default ??
Ans: Many claims that a hidden parameter _optimizer_invalidation_period dictates the maximum time before invalidation. Which is default of 18000 seconds (5 hours)

SQL> select     rpad(i.ksppinm, 35) || ‘ = ‘ || v.ksppstvl parameter,
i.ksppdesc description,
v.ksppstdf dflt
from    x$ksppi         i,
x$ksppcv        v
where   v.indx = i.indx
and     v.inst_id = i.inst_id
and     i.ksppinm like ‘_optimizer_invalidation_period’
order by 1;

PARAMETER                                                                                DESCRIPTION                                                                             DFLT
—————————————————————–    ——————————————————————————————-    ———–
_optimizer_invalidation_period      = 18000            time window for invalidation of cursors of analyzed objects         TRUE

Thanks
Prashant Dixit
“Sharing is Good”

Advertisements

One Response to “Viagra For SQL’s. DBMS_STATS (NO_INVALIDATE argument/parameter) ?”

  1. extra said

    Thank you for one more wonderful report. Exactly where otherwise may perhaps everyone wardrobe kind of info in such a best technique of crafting? I’ve a display subsequent 7 days, and i’m with the search for such information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: