Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Jai Shree Ram Oracle
  • scp with ssh2 Linux/Unix
  • SQL Tracker by SID sqltrackerbysid.sql Oracle
  • restarting network in linux Linux/Unix
  • top 10 AI news today Uncategorized
  • Check_recovery.sh program to run sqlplus and return its values remotely. Linux/Unix
  • Disbaling DBA_SCHEDULER_JOBS Oracle
  • Oracle 10g Installation/Applying Patches Tips Oracle
  • My FTP Job Scheduling for www.pvmehta.com PHP/MYSQL/Wordpress
  • oradebug ipcrm ipcs Oracle
  • USE_NL and INDEX hints example Oracle
  • Oracle Recommended Patches — Oracle Database ID 756671.1 Oracle
  • To Find Orphan OS processes. Linux/Unix
  • Tutornet.US — Source of excellent learning Uncategorized
  • Space padding in korn shell 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

  • pvmehta.com SQL scripts
    Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle Oracle
  • Goog notes on X$ tables Oracle
  • Implementing Listener Security Oracle
  • OEM-Commnds Oracle
  • DBA_HIST_SQLSTAT contents Oracle
  • Parallel DML Oracle

Leave a Reply Cancel reply

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

Categories

  • AMC10 (0)
  • Ansible (0)
  • AWS (2)
  • Azure (1)
  • Django (0)
  • GIT (1)
  • Linux/Unix (150)
  • MYSQL (5)
  • Oracle (403)
  • PHP/MYSQL/Wordpress (10)
  • POSTGRESQL (1)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (18)
  • rman-dataguard (26)
  • shell (151)
  • SQL scripts (349)
  • SQL Server (6)
  • Uncategorized (9)
  • Videos (0)

Recent Posts

  • Tutornet.US — Source of excellent learning14-Oct-2026
  • OCI Infrastructure Patching14-Oct-2026
  • Free API credits for AI models14-Oct-2026
  • AMC1020-Sep-2026
  • Key Management in Oracle: The Core Issue: Missing Master Key12-May-2026
  • SAT Mathematics 10 questions and answer at the end.30-Apr-2026
  • top 10 AI news today30-Apr-2026
  • runon_allpdbs_show_conname.sh23-Apr-2026
  • runon_allcdbs_find_pdbs.sh23-Apr-2026
  • Running PDB on single node in RAC09-Apr-2026

Archives

  • 2026
  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Standby Database File Management in 10g with STANDBY_FILE_MANAGEMENT Oracle
  • Generate SSH without password authentication. Linux/Unix
  • Find_table_size.sql Oracle
  • compile_inv.sql Oracle
  • crontab syntax Linux/Unix
  • V$ROLLSTAT status is Full Oracle
  • Virtual Indexes in Oracle Oracle
  • Database logon trigger issue Oracle

Copyright © 2026 pvmehta.com.

Powered by PressBook News WordPress theme