6 Replies Latest reply: Nov 29, 2012 10:17 PM by 959742 RSS

    DBMS_STATS: When do stats get dropped?

    959742
      Hi All,

      This is probably a stupid question, but I've searched high and low and haven't found a suitable answer to the following: When do stats get dropped?

      I've read that table and index stats get dropped whenever you truncate a table, but the (minimal) testing I've done doesn't bear this out. By "testing", I mean I've tried truncating a table then exporting and viewing the table stats via DBMS_STATS.EXPORT_TABLE_STATS (well I did say 'minimal')... The table stats don't appear to have been dropped following truncation.

      Do table / index stats get dropped when you truncate / drop / delete all records from a table? Or do they not get dropped at all?
        • 1. Re: DBMS_STATS: When do stats get dropped?
          sb92075
          homerj wrote:
          Hi All,

          This is probably a stupid question, but I've searched high and low and haven't found a suitable answer to the following: When do stats get dropped?

          I've read that table and index stats get dropped whenever you truncate a table, but the (minimal) testing I've done doesn't bear this out. By "testing", I mean I've tried truncating a table then exporting and viewing the table stats via DBMS_STATS.EXPORT_TABLE_STATS (well I did say 'minimal')... The table stats don't appear to have been dropped following truncation.

          Do table / index stats get dropped when you truncate / drop / delete all records from a table? Or do they not get dropped at all?
          post complete results from SQL below

          SELECT * FROM V$VERSION;


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: DBMS_STATS: When do stats get dropped?
            959742
            sb92075,

            Ah sorry... duly noted.

            Version output is below:

            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
            PL/SQL Release 10.2.0.1.0 - Production
            CORE     10.2.0.1.0     Production
            TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
            NLSRTL Version 10.2.0.1.0 - Production

            5 rows selected.
            • 3. Re: DBMS_STATS: When do stats get dropped?
              Vedant..
              Please refer Metalink Doc Id : *Restoring Table Statistics (Oracle 10G Onwards) [ID 452011.1]*
              • 4. Re: DBMS_STATS: When do stats get dropped?
                Ankit Ashok Aggarwal
                In my opinion : Table/ Index stats get collect into oracle data dictionary views (i.e. DBA_ or ALL_ or USER_). if your database's GATHER_STATS_JOB is updating statistics automatically or you have any other scheduled DBMS scheduled job (updating statistics manually) all the changes will effectively populated into the database from immedate effect and table/index stats related with truncated/dropped/deleted records/tables will get update.

                otherwise you can drop table/index stats related to dropped/deleted/truncated tables manually using DELETE_TABLE_STATS Procedure

                Statistics are stored into the Oracle Data Dictionary, in tables owned by SYS. Views are created on these tables to retrieve data more easily.

                These views are prefixed with DBA_ or ALL_ or USER_. For ease of reading, we will use DBA_% views, but ALL_% views or USER_% views could be used as well.

                Finally Its : No Table/index stats do not drop when you truncate/drop/delete tables unless it is not happening through GATHER_STATS_JOB automatically or we do it manually or we have any manual schedulded DBMS scheduled jobs.

                Thanks.
                • 5. Re: DBMS_STATS: When do stats get dropped?
                  Nikolay Savvinov
                  Hi,

                  table stats are deleted when you call dbms_stats.delete_table_stats or when you drop the table. Why would you think that Oracle would secretly delete table stats behind your back without you asking it to? If you read it somewhere then you should post a link to that statement -- we cannot judge it without the context, but these days there are lots of stupid things posted on the internet, this could be one of them.

                  You should trust what you see (your own tests) over what you hear, especially from unknown people on the internet.

                  Best regards,
                  Nikolay
                  • 6. Re: DBMS_STATS: When do stats get dropped?
                    959742
                    You should trust what you see (your own tests) over what you hear, especially from unknown people on the internet.
                    Yes I agree, that's why I a) ran my own test and b) posted this question :)

                    Thanks Nikolay, that confirms my suspicions.