Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Oracle Identifiers Oracle
  • All About Trace Fils Oracle
  • Adding Datafile on Primary Server and Impact on Standby Server Oracle
  • executing Function from SQLPLUS prompt Oracle
  • AWR license Oracle
  • reset Sequence Oracle
  • Formatter Explain plan Output 1 Oracle
  • How to change hostname in Linux Linux/Unix
  • Find All internal Parameters Oracle
  • chk_space_SID.ksh Linux/Unix
  • Oracle 10g Wait Model Oracle
  • Search and replace pattern Linux/Unix
  • myfile Oracle
  • Renaming the column name Oracle
  • Goldengate document from Porus Oracle

Explain Plan Doesn’T Change For Sql After New Statistics Generated

Posted on 06-May-2008 By Admin No Comments on Explain Plan Doesn’T Change For Sql After New Statistics Generated

If you use dbms_stats in 10g it will not automatically invalidate dependant

cursors. This is the default behaviour as of 10.

To revert to older previous behaviour you will have to set the parameter

no_invalidate to false.

.

no_invalidate

Does not invalidate the dependent cursors if set to TRUE.

The procedure invalidates the dependent cursors immediately if set to FALSE.

This is the default.

Begin

dbms_stats.gather_table_stats(……., no_invalidate => false);

End;

/

or you can set defaultparameters DBMS_STATS.SET_PARAM:

This procedure sets default values for parameters of DBMS_STATS procedures.

You can use the GET_PARAM Function to get the current default value of a parameter.

Syntax

DBMS_STATS.SET_PARAM (

pname IN VARCHAR2,

pval IN VARCHAR2);

Solution

Testcase/Examples

==============

alter system flush shared_pool;

drop table t;

create table t( c1 number);

Begin

For i In 1..1000 Loop

Insert Into t

values (I);

End Loop;

End;

/

select * from t where c1=10;

select sql_text, invalidations from v$sql

where sql_text like ‘%select * from t%’

/

–==> invalidations 0

Begin

dbms_stats.gather_table_stats(‘SYSTEM’,’t’,estimate_percent => 100,

cascade => True);

End;

/

select * from t where c1=10;

select sql_text, invalidations from v$sql

where sql_text like ‘%select * from t%’

;

— ==> invalidations 0

Begin

dbms_stats.gather_table_stats(‘SYSTEM’,’t’,estimate_percent => 100,

cascade => True, no_invalidate => false);

End;

/

select * from t where c1=10;

select sql_text, invalidations from v$sql

where sql_text like ‘%select * from t%’

;

— ==> invalidations 1

RESULT:

11 INVALIDATIONS is always 0 and 1 if no_invalidate => false

10.2.0.3 INVALIDATIONS is always 0 and 1 if no_invalidate => false

9.2.0.8 INVALIDATIONS is always 1 after the gather stats

.

Oracle, SQL scripts

Post navigation

Previous Post: alter database backup controlfile to trace
Next Post: send email from unix mailx with attachment.

Related Posts

  • 284785.1 How to check RAC Option is currently linked into the Oracle Binary Oracle
  • sess1.sql Oracle
  • Multiple listeners Oracle
  • Insert cause enqueue locks Oracle
  • TRUNCATE table and disabling referential constraints. Oracle
  • Flowers Resize datafiles Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Generate SSH without password authentication. Linux/Unix
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • dbinv.sql Oracle
  • True Session Wait Activity in Oracle 10g Verygood Oracle
  • Example of How To Resize the Online Redo Logfiles Note:1035935.6 Oracle
  • sess_server.sql Oracle
  • Processes Parameter decision Oracle
  • Running some SQL on multiple databases connecting using monitoring userid and password Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme