Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • cold backup scripts to copy locally Linux/Unix
  • create PLAN_TABLE command. Oracle
  • move_arch_files.ksh Linux/Unix
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • SQLPLUS COPY command Precautions. Oracle
  • backspace in SQL Plus not working then..? Linux/Unix
  • Updated LCK.SQL file. Oracle
  • Histogram information Oracle
  • Getting started with notebook Python/PySpark
  • Deleting first line and lastline of a file using sed Linux/Unix
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • Import and export statements Oracle
  • CTAS with LONG Column for 7.x and 8 and 8i Oracle
  • Jai Shree Ram Linux/Unix
  • rm_backup_arch_file.ksh Linux/Unix

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

  • Sending SQLPLUS output in HTML format Oracle
  • Pending Distributed Transations Oracle
  • ORA-1841 Error Connecting to Upgraded Database After Set PASSWORD_LIFE_TIME Oracle
  • pvm_pre_change.sql Oracle
  • 556976.1 Oracle Clusterware: Components installed Oracle
  • Find Stale DR Physical Standby 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Find Time Consuming SQL Statements in Oracle 10g Oracle
  • Oracle Statspack survival Guide Oracle
  • Move WordPress site from one hosting service to other. PHP/MYSQL/Wordpress
  • DBMS_UTILITY PACKAGE Oracle
  • Sending email with file attachment. Linux/Unix
  • runsql_once.ksh Linux/Unix
  • TRUNCATE Privs Oracle
  • Implementation of key based authentications Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme