This discussion is archived
2 Replies Latest reply: Feb 1, 2013 11:10 PM by damorgan RSS

oracle statistics performance impact

225452 Newbie
Currently Being Moderated
We had empty tables and loaded more then 100 Million rows and our DBA gather the statiscis. Now the tables are truncated and we need to load the same data again in the empty tables.
My question is applying the statistics of the last run will improve the performace or there is no performance gain by applying the statistics.

I am not a DBA and so apologies if i am asking wrong question. Thanks

Edited by: anuragtandon on Feb 1, 2013 9:47 PM
  • 1. Re: oracle statistics performance impact
    karan Pro
    Currently Being Moderated
    If you are truncating and doing the bulk load everyday then its a volatile table for sure. Let me paste here what oracle white paper says "A volatile table is one where the volume of data changes dramatically over time. For example, an orders queue table, which at the start of the day the table is empty. As the day progresses and orders are placed the table begins to fill up. Once each order is processed it is deleted from the tables, so by the end of the day the table is empty again.

    If you relied on the automatic statistics gather job to maintain statistics on such tables then the statistics would always show the table was empty because it was empty over night when the job ran. However, during the course of the day the table may have hundreds of thousands of rows in it.

    In such cases it is better to gather a representative set of statistics for the table during the day when the table is populated and then lock them. Locking the statistics will prevent the automatic statistics gathering job from over writing them. Alternatively, you could rely on dynamic sampling to gather statistics on these tables. The Optimizer uses dynamic sampling during the compilation of a SQL statement to gather basic statistics on the tables before optimizing the statement. Although the statistics gathered by dynamic sampling are not as high a quality or as complete as the statistics gathered using the DBMS_STATS package, they should be good enough in most cases."

    SOURCE http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
  • 2. Re: oracle statistics performance impact
    damorgan Oracle ACE Director
    Currently Being Moderated
    My recommendation, if you know your data, is that you use DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS.

    Demos here:
    http://www.morganslibrary.org/reference/pkgs/dbms_stats.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points