7 Replies Latest reply on Dec 26, 2012 7:27 PM by Dean Gagne-Oracle

    size of statistics

    User416253
      Hi Team,

      Actually, while exporting the schema with expdp. It is exporting the statistics. I want to know the size of this statistics in the database. Is there a table or method that I can find the size of the statistics.

      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


      Thanks for your help.
        • 1. Re: size of statistics
          sb92075
          931804 wrote:
          Hi Team,

          Actually, while exporting the schema with expdp. It is exporting the statistics. I want to know the size of this statistics in the database. Is there a table or method that I can find the size of the statistics.

          Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
          Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
          Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS


          Thanks for your help.
          question makes no sense.
          the statistics are columns within metadata for corresponding objects.
          any column consumes less space when column contains NULL.
          • 2. Re: size of statistics
            User416253
            The reason why I asked this question.

            My total dump file is is 2.5G but while the expdp was running this statement "Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS" statement
            I was checking the dump file size its almost 1 GB. So I thought table_statistics were occupying much disk space. Now I understood that the table unloaded after table_statistics was occupying that space.

            Total estimation using BLOCKS method: 5.049 GB
            Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
            Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
            Processing object type SCHEMA_EXPORT/TABLE/TABLE
            Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
            Processing object type SCHEMA_EXPORT/TABLE/COMMENT
            Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
            Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
            Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
            Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
            Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
            Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
            Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
            Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
            Processing object type SCHEMA_EXPORT/VIEW/VIEW
            Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
            Processing object type SCHEMA_EXPORT/VIEW/COMMENT
            Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
            Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
            Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
            Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

            exported "AA"."xxxx" 1.478 GB


            Thanks for your prompt reply.
            • 3. Re: size of statistics
              Osama_Mustafa
              If i understand you correctly .

              Oracle statistics tell you the size of the tables , so you have to find table size.
              dbms_space.object_space_usage
              Refer to
              http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle
              • 4. Re: size of statistics
                Aman....
                I don't think that it's the table statistics that are occupying the size but it would be the "fragmented" space in your object that would be occupying the space. Statistics are merely the metadata , as Sb said.

                Aman....
                • 5. Re: size of statistics
                  Dean Gagne-Oracle
                  Here is how you can tell how much dump file space your statistics are taking up.

                  expdp ... keep_master=y job_name=<some_name_here>

                  This will keep the master table that Data Pump created and populated. When the job is done, then do this query:

                  select sum(dump_length), sum(orig_dump_length) from schema_running_job.some_name_here
                  where process_order > 0 and duplicate = 0 and object_type like '%STATISTICS%';

                  This will tell you how much storage was needed to export the statistics. Just an f.y.i., statistics can get extremely large when the table they are on is

                  1. partitioned
                  2. subpartitioned
                  3. has lots of columns.

                  when you are done with your query, you probably want to drop that table

                  Hope this helps.

                  Dean
                  • 6. Re: size of statistics
                    Iordan Iotzov
                    While things like large number of partitions and histograms can increase the size of the statistics, I find it unlikely that they would take significant disk space (gigabytes).

                    Here is another simple way to check the size stats occupy in the dump file.
                    Run datapump export with default parameters.
                    Then run the same export without the statistics:
                    EXCLUDE=STATISTICS 
                    And see the difference in the file size.

                    Iordan Iotzov
                    http://iiotzov.wordpress.com/
                    • 7. Re: size of statistics
                      Dean Gagne-Oracle
                      Remember, metadata is stored in the dumpfile as xml documents. This document is complete, so table statistics have table information, some column information, the histograms, etcs. For indexe statistics, the xml document will contain index information, table information, column information, histograms, etc. All this is an xml document which can get large. Some of the statistics on subpsrtitioned tables can cause the conversion code (from xml to ddl) to run out of memory, so yes, statistics can take up a large portion of the dumpfile. It doesn't have to, but it can.

                      Dean