• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to check Stale statistics

How to check Stale statistics

August 15, 2015 by techgoeasy 8 Comments

Stats can become stale when the data in the table changes substantially.Up to date statistics are important to generate good execution plans

How Oracle decide if the stats has become stale

Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables:

Parameter setting required to Track the table changes

Prior to Oracle 10g, automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on table.
Depending on the MONITORING flag, the GATHER_STATS_JOB job collected “GATHER EMPTY” and “GATHER STALE” on the flagged objects.

In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored.Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.
By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled. It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g and above
By setting these parameter,Oracle tracks the approximate number of INSERT, UPDATE, and DELETE operations for the oracle table since the last time statistics were gathered. This information on “changes made” is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS.

Oracle uses these views to identify tables that have stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.

How to check Stale statistics

The below PLSQL procedure find out all the tables in SCOTT schema which is stale stats

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT', objlist=>ObjList, options=>'LIST STALE');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

The below sql can also be used to find out insert,updates,deletes

select u.TIMESTAMP,
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(d.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 3000 or u.updates > 3000 or u.deletes > 3000)
order by t.last_analyzed
/

If you want to run this on whole database

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(objlist=>ObjList, options=>'LIST STALE');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

If you want see the tables where stats is empty, we can use below

SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(objlist=>ObjList, options=>'LIST EMPTY');
FOR k in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(k).ownname || '.' || ObjList(k).ObjName || ' ' || ObjList(k).ObjType || ' ' || ObjList(k).partname);
END LOOP;
END;
/

Now once you find the tables list, you can generate statistics on these tables.

exec dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME');

We can also run below command to generate stats on all the stale object in the schema

exec dbms_stats.gather_schema_stats(ownname => '<schema name>', cascade => TRUE, options => 'GATHER AUTO');

Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.

Related Articles

ora-38029: object statistics are locked
ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)
Gathering Statistics in Release 11i and R12
Incremental Statistics Gathering in 11g
How to set Table Monitoring in Oracle and Relationship with STATISTICS_LEVEL

Filed Under: Oracle, Oracle Database Tagged With: How to check Stale statistics

Reader Interactions

Comments

  1. Ganesh Singh says

    July 23, 2018 at 4:46 pm

    Both the above queries are not running ..

    Reply
    • techgoeasy says

      July 24, 2018 at 4:02 am

      It was working for me and that is the reason ,I pasted on blog. Please let me know the errors you are getting, then i can fix it.

      Reply
      • Ganesh Singh says

        July 24, 2018 at 7:03 am

        SQL> SET SERVEROUTPUT ON
        DECLARE
        ObjList dbms_stats.ObjectTab;
        BEGIN
        SQL> 2 3 4 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SYSTEM’, objlist=>O
        bjList, options=>’LIST STALE’);
        5 FOR k in ObjList.FIRST..ObjList.LAST
        LOOP
        6 7 dbms_output.put_line(ObjList(i).ownname ||’.’|| ObjList(k).ObjName ||
        ” || ObjList(k).ObjType ||”|| ObjList(k).partname);
        8 END LOOP;
        9 END;
        10 /
        dbms_output.put_line(ObjList(i).ownname ||’.’|| ObjList(k).ObjName || ” || ObjL
        ist(k).ObjType ||”|| ObjList(k).partname);
        *
        ERROR at line 7:
        ORA-06550: line 7, column 30:
        PLS-00201: identifier ‘I’ must be declared
        ORA-06550: line 7, column 1:
        PL/SQL: Statement ignored

        ================================================================================================================================================================
        SQL> select u.TIMESTAMP,
        t.last_analyzed,
        2 3 u.table_name,
        4 u.inserts,
        5 u.updates,
        6 u.deletes,
        7 d.num_rows,
        8 decode(num_rows,0,’Table Stats indicate No Rows’,
        9 nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,’999.99′)
        10 ,’Null Value in USER_TAB_MODIFICATIONS’)
        11 ) percent
        12 from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
        13 where u.table_name = t.table_name
        14 and d.table_name = t.table_name
        15 and d.owner = ‘SYSTEM’
        16 and (u.inserts > 3000 or u.updates > 3000 or u.deletes > 3000)
        17 order by t.last_analyzed
        18 /
        decode(num_rows,0,’Table Stats indicate No Rows’,
        *
        ERROR at line 8:
        ORA-00918: column ambiguously defined

        Thank you for giving attention..

        Reply
        • techgoeasy says

          July 25, 2018 at 4:49 pm

          Thanks for replying back. I have corrected the sql in the post. Please retry and let me know if that works

          Reply
  2. Ganesh Singh says

    July 24, 2018 at 7:01 am

    SET SERVEROUTPUT ON
    DECLARE
    ObjList dbms_stats.ObjectTab;
    BEGIN
    SQL> 2 3 4 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SYSTEM’, objlist=>ObjList, options=>’LIST STALE’);
    5 FOR k in ObjList.FIRST..ObjList.LAST
    LOOP
    6 7 dbms_output.put_line(ObjList(i).ownname ||’.’|| ObjList(k).ObjName || ” || ObjList(k).ObjType ||”|| ObjList(k).partname);
    8 END LOOP;
    9 END;
    10 /
    dbms_output.put_line(ObjList(i).ownname ||’.’|| ObjList(k).ObjName || ” || ObjList(k).ObjType ||”|| ObjList(k).partname);
    *
    ERROR at line 7:
    ORA-06550: line 7, column 30:
    PLS-00201: identifier ‘I’ must be declared
    ORA-06550: line 7, column 1:
    PL/SQL: Statement ignored

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

    SQL> select u.TIMESTAMP,
    t.last_analyzed,
    2 3 u.table_name,
    4 u.inserts,
    5 u.updates,
    6 u.deletes,
    7 d.num_rows,
    8 decode(num_rows,0,’Table Stats indicate No Rows’,
    9 nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,’999.99′)
    10 ,’Null Value in USER_TAB_MODIFICATIONS’)
    11 ) percent
    12 from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
    13 where u.table_name = t.table_name
    14 and d.table_name = t.table_name
    15 and d.owner = ‘SYSTEM’
    16 and (u.inserts > 3000 or u.updates > 3000 or u.deletes > 3000)
    17 order by t.last_analyzed
    18 /
    decode(num_rows,0,’Table Stats indicate No Rows’,
    *
    ERROR at line 8:
    ORA-00918: column ambiguously defined

    Reply
  3. Ganesh Singh says

    July 26, 2018 at 11:57 am

    Where you have corrected the queries .. I am getting the same error code that you have mention on the main page. Nothing showing on the reply.. So, I thing main code on page is same. Please update it.

    Reply
  4. Ganesh Singh says

    July 26, 2018 at 12:09 pm

    2nd query is fine but 1st one giving error now..

    SQL> SET SERVEROUTPUT ON
    DECLARE
    ObjList dbms_stats.ObjectTab;
    SQL> 2 3 BEGIN
    4 DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SYSTEM’, objlist=>ObjList, options=>’LIST STALE’);
    5 FOR k in ObjList.FIRST..ObjList.LAST
    6 LOOP
    7 dbms_output.put_line(ObjList(k).ownname || ‘.’ || ObjList(k).ObjName || ‘ ‘ || ObjList(k).ObjType || ‘ ‘ || ObjList(k).partname);
    8 END LOOP;
    9 END;
    10 /
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 5

    Reply
    • techgoeasy says

      July 26, 2018 at 12:30 pm

      hi Ganesh

      Please use on some other schema where stats is already generated.

      thank

      Reply

Leave a Reply Cancel reply

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

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us