1 2 3 Previous Next 30 Replies Latest reply: Jun 17, 2009 10:18 AM by Michael C Go to original post RSS
      • 15. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
        Jonathan Lewis
        user00726 wrote:
        TRUNC(LAS   COUNT(*)
        --------- ----------
        08-JAN-09          7
        09-JAN-09         10
        12-MAR-09       1831
        01-MAY-09          4
        16
        
        
        TRUNC(LAS   COUNT(*)
        --------- ----------
        12-MAR-09       1583
        01-MAY-09         21
        27
        It looks like you have have a strategic problem here (you have to figure out the way things should be) rather than a tactical one (where hitting the thing with a hammer might fix it).

        As Mr. Burleson said - if things went faster when the previous DBA rebuilt all the indexes, then why don't you just rebuild all the indexes and see what happens. The drawback to that advice is that maybe he didn't just rebuild the indexes - still, if it's only a small system and the performance is a total disaster right now, you don't have much to lose if it doesn't help.

        The bigger issue, though, is the variation in statistics. Is it possible that your DBA rebuilt the indexes on 1st May 09 - and collected stats for a few critical objects - and those stats are now sufficiently wrong that the optimizer is failing.

        I note that some of your queries make this look like a system relating to a university campus system - and universities often have timetables run to three terms (semesters) per year. Is the long running job asking about data for NEXT term, when the statistics are telling the optimizer that the data stopped at the end of LAST term ? This would encourage the optimizer to do some incredibly stupid things for large reports.

        Tactically, you could rebuild all the indexes - I would advise against it at this point, but it probably won't make things much worse, and you might get lucky.

        Strategically you need to find out why some objects have missing statistics, why most objects last had their stats gathered in March, and why some of them had stats gathered in May (and the first schema you listed is worst of all - the stats are years out of date and you have lots of tables with stats where the indexes don't have stats). Then you need to know why the optimizer_mode is set to choose - because if some of the code is supposed to run rule-based you don't want stats on any objects accessed by that part of the code. Then you need to work out the correct strategy for statistics collection. (If you can copy the database I'd be inclined to do it and gather schema stats on the copy on all relevant schemas and see what that does for your problem ).

        Until you can sort out what your statistics collection and optimisation strategy is supposed to be, you cannot stabilise your system.

        Regards
        Jonathan Lewis
        http://jonathanlewis.wordpress.com
        http://www.jlcomp.demon.co.uk

        "Science is more than a body of knowledge; it is a way of thinking"
        Carl Sagan
        • 16. Re: what should be the value of Optimizer_index_cost_adj  in oracle 9i
          706417
          If things are so bad that rebuilding indexes is a possibility, then how about restoring/cloning the database from a backup taken before it all went wobbly, exporting the stats for the most-hit objects in the Prod DB, export the stats for the same objects in the restored/cloned DB, import the stats from the restored/cloned DB into the Prod. DB and see how things go? And I mean restore to another server!


          Related articles:

          http://www.dba-oracle.com/t_dbms_stats.htm

          http://oracledoug.com/serendipity/index.php?/archives/999-Saving-Optimizer-Stats-9i.html


          Regards - Don Lewis
          • 17. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
            Michael C
            This is a great thread (...ok, most of it :-)),

            Jonathan, can you help me understand something?

            You posted a statement +"...and get a lot of I/O protection from a cache somewhere outside Oracle"+. Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.

            Thanks,
            Michael Cunningham
            • 18. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
              701909
              Mr. Lewis,

              You need to no that not everybody who disagrees with you is DKB!!!

              When you smacked Rich Niemiec great book with a poor review you made enemies as likened when you attacked DKB.

              Noone likes people who attack there fellows and you come here not to help the OP but to put down those who do.
              • 19. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                Niall Litchfield
                Michael C wrote:
                This is a great thread (...ok, most of it :-)),

                Jonathan, can you help me understand something?

                You posted a statement +"...and get a lot of I/O protection from a cache somewhere outside Oracle"+. Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.

                Thanks,
                Michael Cunningham
                Hi Michael

                On average his disk reads from normal work (db_file_scattered_read and db_file_sequential_read) both take under 5ms to complete. Disks don't serve data that fast. Cache does. Look at what oracle thinks is a physical i/o operation and compare the elapsed time to what you know hardware is capable of.

                Niall Litchfield
                http://www.orawin.info/
                • 20. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                  Niall Litchfield
                  Absorbine Jr. wrote:
                  I added the term "-Burleson" to your suggested search string (many savvy Oracle professionals do this to improve the efficiency of their searches on Google)
                  Har thats what you hope they will do!!!!!

                  The internet is not broken the popular authors are where they should be.
                  never a good argument to confuse temporary popularity with quality. Right now the best seller on amazon.com (22:57 UTC + 1 11-JUN-2009) is a book by a US based shock jock. Liberty and Tyranny: A Conservative Manifesto. It may have many qualities but it's not likely to be the "best" book for most people. Personally I recommend http://tinyurl.com/nx4dvr for oracle professionals especially when read against the work of certain authors from the american south who value publicity over accuracy.

                  Niall
                  • 21. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                    user00726
                    Hi all,

                    I have found that some of the indexes have a height greater than 8 and also high number of deleted entries....
                    SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
                      2  ;
                    
                    OWNER                          INDEX_NAME
                    ------------------------------ ------------------------------
                    TABLE_NAME                         BLEVEL
                    ------------------------------ ----------
                    DATA3                        IDX_DRCR_FLG
                    FT_CUR_TRANS_DETAIL                     7
                    
                    DATA3                        IDX_SC_COURSE_CODE
                    OS_STU_COURSE                           8
                    
                    DATA3                        IND_IEC_COURSE_CODE
                    OT_ISS_ELG_CERT                        10
                    
                    
                    OWNER                          INDEX_NAME
                    ------------------------------ ------------------------------
                    TABLE_NAME                         BLEVEL
                    ------------------------------ ----------
                    DATA3                        IND_SPY_COURSE_CODE
                    OS_STU_PASS_YN                          4
                    
                    DATA3                        IDX_DRCR_FLAG
                    FT_UNPOSTED_TRANS_DETAIL                8
                    
                    SQL> analyze index  IDX_DRCR_FLG validate structure;
                    
                    Index analyzed.
                    
                    SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
                    ROM INDEX_STATS;
                    
                    NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                    ------------------------------ ---------- ---------- ----------- ----------
                    IDX_DRCR_FLG                            8      11120        7598 68.3273381
                    
                    SQL> analyze index IDX_SC_COURSE_CODE   validate structure;
                    
                    Index analyzed.
                    
                    SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
                    ROM INDEX_STATS;
                    
                    NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                    ------------------------------ ---------- ---------- ----------- ----------
                    IDX_SC_COURSE_CODE                      9     148591       11006  7.4069089
                    
                    SQL> analyze index
                      2   IND_IEC_COURSE_CODE  validate structure;
                    
                    Index analyzed.
                    
                    SQL>
                    SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio F
                    ROM INDEX_STATS;
                    
                    NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                    ------------------------------ ---------- ---------- ----------- ----------
                    IND_IEC_COURSE_CODE                    11      50738       28027 55.2386771
                    
                    SQL> analyze index IND_SPY_COURSE_CODE validate structure;
                    
                    Index analyzed.
                    
                    SQL>  SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
                    FROM INDEX_STATS;
                    
                    NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                    ------------------------------ ---------- ---------- ----------- ----------
                    IND_SPY_COURSE_CODE                     5      36587         379 1.03588706
                    
                    SQL> analyze index IDX_DRCR_FLAG validate structure;
                    
                    Index analyzed.
                    
                    SQL>  SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
                    FROM INDEX_STATS;
                    
                    NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                    ------------------------------ ---------- ---------- ----------- ----------
                    IDX_DRCR_FLAG                           9      10354        5146 49.7005988
                    
                    SQL>
                    Should I rebuild all those indexes.......

                    And should I also set the parameter optimizer_mode=first_rows. as out system is OLTP....

                    Edited by: user00726 on Jun 11, 2009 10:27 PM
                    • 22. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                      user00726
                      But here also I am getting error while rebuilding the same....

                      because some of the indexes are BITMAP
                      SQL> select INDEX_TYPE,index_name,table_name,blevel from dba_indexes where BLEVE
                      L>3;
                      
                      INDEX_TYPE      INDEX_NAME      TABLE_NAME                         BLEVEL
                      --------------- --------------- ------------------------------ ----------
                      BITMAP          IDX_DRCR_FLG    FT_CUR_TRANS_DETAIL                     7
                      BITMAP          IDX_SC_COURSE_C OS_STU_COURSE                           8
                                      ODE
                      
                      BITMAP          IND_IEC_COURSE_ OT_ISS_ELG_CERT                        10
                                      CODE
                      
                      BITMAP          IND_SPY_COURSE_ OS_STU_PASS_YN                          4
                                      CODE
                      
                      BITMAP          IDX_DRCR_FLAG   FT_UNPOSTED_TRANS_DETAIL                8
                      
                      SQL>
                      Pls suggest me...
                      • 23. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                        Niall Litchfield
                        well you do seem rather to want the one magic bullet, instead of taking a measured and planned approach to managing the database. You should read Jonathan's note which advised you to work out a sensible strategy for gathering statistics on both tables and indexes (note that by default in your version gathering schema stats doesn't gather stats on indexes as well as tables - that is the default for the parameter cascade is false instead of true). I'd be looking however for a strategy that most likely regularly gathered stats on all tables and indexes whilst preserving the old values.

                        Now that all said you do seem to have some indexes with extraordinarily large values for blevel given the number of rows apparently in the underlying table - especially given the column names which suggest that these are rather short columns. A rebuild might well be in order, but working out why an index on a flag column with only 11000 rows in it has a blevel of 8 would be worthwhile as well. Are these by any chance bitmap indexes?


                        Niall
                        • 24. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                          Jonathan Lewis
                          user00726 wrote:
                          But here also I am getting error while rebuilding the same....

                          because some of the indexes are BITMAP
                          SQL> select INDEX_TYPE,index_name,table_name,blevel from dba_indexes where BLEVE
                          L>3;
                          
                          INDEX_TYPE      INDEX_NAME      TABLE_NAME                         BLEVEL
                          --------------- --------------- ------------------------------ ----------
                          BITMAP          IDX_DRCR_FLG    FT_CUR_TRANS_DETAIL                     7
                          BITMAP          IDX_SC_COURSE_C OS_STU_COURSE                           8
                          ODE
                          
                          BITMAP          IND_IEC_COURSE_ OT_ISS_ELG_CERT                        10
                          CODE
                          
                          BITMAP          IND_SPY_COURSE_ OS_STU_PASS_YN                          4
                          CODE
                          
                          BITMAP          IDX_DRCR_FLAG   FT_UNPOSTED_TRANS_DETAIL                8
                          
                          SQL>
                          Pls suggest me...
                          Now we're getting somewhere - and the problem is a strategic one. We've already seen that there is probably no proper strategy for collecting stats on this database, now we see that there is probably no proper indexing strategy.

                          You've said that this is an OLTP system - but bitmap indexes and OLTP systems should not go together because (a) the mix results in locking (and deadlocking) problem and (b) bitmap indexes degenerate and waste an enormous amount of space and grow to a ridiculous blevel when subject to the typical type of DML that appears in OLTP systems. (See for example [this blog entry of mine|http://jonathanlewis.wordpress.com/2006/11/29/bitmap-indexes/] which lists three articles about bitmap indexes - you need to read the first one, the other two are less relevant to you).

                          Strategically, you need to get rid of those bitmap indexes and work out a good set of b-tree indexes to replace them (a temporary measure would be to replace each with the corresponding b-tree equivalent) - but you also need to think about colllecting histogram information (but ONLY on a small number relevant columns) to give the optimizer some idea about when to use them.

                          In the short term, and just to get your reports running faster, you probably do need to rebuild these indexes. I would suggest that you set pctfree to 67 when you do so as this may limit the "growth" problem.
                          But here also I am getting error while rebuilding the same....
                          I think there are about 20,000 possible error messages that Oracle can give for server problems - so we can't help if you don't tell us which one: e.g.
                          ORA-03113: end-of-file on communication channel
                          ORA-01652: unable to extend temp segment by  in tablespace
                          ORA-00054: resource busy and acquire with NOWAIT specified
                             etc ... etc ... etc ...
                          If I had to guess I'd gamble on ORA-00054 - which means people are updating the table when you need an exclusive lock on the table to rebuild the index, and unfortunately you can't do an online rebuild of a bitmap index until version 10g, and I think you were on 9i. If this is the case, you need to block access to the system for a while so that you can rebuild those indexes.

                          Bottom line: You need to learn about indexing then apply that learning to your system; and you need to learn about statistics - with special attention to histograms - and apply that information to your system.


                          [Addendum:] I did suggest on Wednesday afternoon that you should pick out some execution plans for expensive queries - if you had done so we might have spotted that you were using bitmap indexes much sooner. It's possible that parts of your report are running slowly BECAUSE the optimizer is using those bitmap indexes which have exploded enormously, and it's using them because the statistics are from a time when the indexes were new and small. (And if that's the case, fiddling with optimizer_index_cost_adj could make things worse because reducing it will encourage the optimizer to make even more use of those degenerate indexes).


                          Regards
                          Jonathan Lewis
                          http://jonathanlewis.wordpress.com
                          http://www.jlcomp.demon.co.uk

                          "Science is more than a body of knowledge; it is a way of thinking"
                          Carl Sagan

                          Edited by: Jonathan Lewis on Jun 12, 2009 8:17 AM
                          • 25. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                            Jonathan Lewis
                            Michael C wrote:
                            This is a great thread (...ok, most of it :-)),

                            Jonathan, can you help me understand something?

                            You posted a statement +"...and get a lot of I/O protection from a cache somewhere outside Oracle"+. Can you teach us what parameters show this information? I find statements like this amazing and I'd love to know the answer.
                            Michael

                            Niall's covered the key point. Unless you have the latest fastest discs you probably have something which quote 6 m/s as the average read time for random I/O - the statspack reports show this user as getting an average of 4 m/s, (which is about as fast as the latest discs could be under a low-concurrency load). So it's very likely that a fair number of his reads are coming from a cache. (Since he's running RAC, it's most likely to be a SAN cache rather than local memory).

                            On a busy (I/O intensive) system, even if you see 6 m/s as an average read time you can be a little "suspicious" of caching, because queueing theory warns us that if a queue takes 6 m/s without load, then by the time you hit 50% load the average response time is likely to double. (This is why [v$event_histogram in 10g|http://jonathanlewis.wordpress.com/2007/01/10/event-histograms/] in 10g is so useful - it shows you a spread of values).

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk


                            "Science is more than a body of knowledge; it is a way of thinking"
                            Carl Sagan
                            • 26. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                              user00726
                              After rebuilding the indexing
                              lf_rows an del_lf_rows
                              comes down but obviously space of all the indexing have been increased...

                              SQL>   SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
                              ,pct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IDX_SC_COURSE_CODE                      9     148563       10961 7.37801471
                                      51
                              
                              
                              SQL> alter index IDX_SC_COURSE_CODE rebuild;
                              
                              Index altered.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              no rows selected
                              
                              SQL> analyze index IDX_SC_COURSE_CODE   validate structure;
                              
                              Index analyzed.
                              
                              SQL>  SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,
                              pct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IDX_SC_COURSE_CODE                      2        114           0          0
                                      85
                              
                              
                              SQL> analyze index  IND_IEC_COURSE_CODE  validate structure;
                              
                              Index analyzed.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IND_IEC_COURSE_CODE                    11      50738       28027 55.2386771
                                      57
                              
                              
                              SQL>  alter index IND_IEC_COURSE_CODE rebuild;
                              
                              Index altered.
                              
                              SQL> analyze index  IND_IEC_COURSE_CODE  validate structure;
                              
                              Index analyzed.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IND_IEC_COURSE_CODE                     2         55           0          0
                                      84
                              
                              
                              SQL> analyze index  IND_IEC_COURSE_CODE  validate structure;
                              
                              Index analyzed.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IND_IEC_COURSE_CODE                     2         55           0          0
                                      84
                              
                              
                              SQL> analyze index IND_SPY_COURSE_CODE validate structure;
                              
                              Index analyzed.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IND_SPY_COURSE_CODE                     5      36587         379 1.03588706
                                      62
                              
                              
                              SQL> alter index
                                2  IND_SPY_COURSE_CODE rebuild;
                              
                              Index altered.
                              
                              SQL>  analyze index IND_SPY_COURSE_CODE validate structure;
                              
                              Index analyzed.
                              
                              SQL>
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IND_SPY_COURSE_CODE                     2         59           0          0
                                      81
                              
                              
                              SQL> analyze index IDX_DRCR_FLAG validate structure;
                              
                              Index analyzed.
                              
                              SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio,p
                              ct_used from index_stats;
                              
                              NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS      RATIO
                              ------------------------------ ---------- ---------- ----------- ----------
                                PCT_USED
                              ----------
                              IDX_DRCR_FLAG                           2          6           0          0
                                      61
                              
                              
                              SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
                               ;  2
                              
                              OWNER                          INDEX_NAME
                              ------------------------------ ------------------------------
                              TABLE_NAME                         BLEVEL
                              ------------------------------ ----------
                              data3                        IDX_DRCR_FLG
                              FT_CUR_TRANS_DETAIL                     7
                              
                              data3                        IDX_SC_COURSE_CODE
                              OS_STU_COURSE                           8
                              
                              data3                        IND_IEC_COURSE_CODE
                              OT_ISS_ELG_CERT                        10
                              
                              
                              OWNER                          INDEX_NAME
                              ------------------------------ ------------------------------
                              TABLE_NAME                         BLEVEL
                              ------------------------------ ----------
                              data3                        IND_SPY_COURSE_CODE
                              OS_STU_PASS_YN                          4
                              
                              data3                        IDX_DRCR_FLAG
                              FT_UNPOSTED_TRANS_DETAIL                8
                              
                              
                              SQL>
                              • 27. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                                Niall Litchfield
                                user00726 wrote:
                                After rebuilding the indexing
                                lf_rows an del_lf_rows
                                comes down but obviously space of all the indexing have been increased...
                                I don't see anything in your listing that justifies that statement, unless you mean something very odd by it indeed. As you have rebuilt your indexes, as a quick fix you might do well to gather stats on them again as well, quite clearly the stats are both out of date and inaccurate now.

                                Niall Litchfield
                                http://www.orawin.info/
                                • 28. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                                  Jonathan Lewis
                                  user00726 wrote:
                                  After rebuilding the indexing
                                  lf_rows an del_lf_rows
                                  comes down but obviously space of all the indexing have been increased...
                                  I don't quite follow what you are demonstrating - but I think you are misinterpreting the effect of the +'analyze index ... validate structure'+ command. This does NOT collect optimizer statistics, so the query against dba_indexes will still be showing the old figures.

                                  You need to execute some calls to dbms_stats (e.g. gather_table_stats with the cascade option set to true, or gather_index_stats, or gather_schema_stats) to collect new statistics.

                                  It's possible that your previous DBA used other forms of the analyze command to collect optimizer statistics, though, such as +'analyze table XXX compute statistics'+, and that may be why you thought that your analyze commands would change the optimizer stats.

                                  Regards
                                  Jonathan Lewis
                                  http://jonathanlewis.wordpress.com
                                  http://www.jlcomp.demon.co.uk


                                  New Scientist: "Would you prefer a cautious expert or a confident ignoramus"
                                  http://www.newscientist.com/article/mg20227115.500-humans-prefer-cockiness-to-expertise.html
                                  • 29. Re: waht should be the value of Optimizer_index_cost_adj  in oracle 9i
                                    user00726
                                    Jonathan Lewis :



                                    HI all,

                                    Thanks all for your valuable suggestions.....


                                    I have gathered stats on last monday
                                    SQL> select trunc(last_analyzed), count(*) from
                                            dba_indexes where owner='CADATA3' group by         trunc(last_analyzed)
                                    order by         trunc(last_analyzed)
                                      2    3    4  ;
                                    
                                    TRUNC(LAS   COUNT(*)
                                    --------- ----------
                                    14-JUN-09       1604
                                                      27
                                    
                                    SQL> select trunc(last_analyzed), count(*) from
                                            dba_tables  where owner='CADATA3' group by         trunc(last_analyzed)
                                    order by         trunc(last_analyzed)
                                      2    3    4  ;
                                    
                                    TRUNC(LAS   COUNT(*)
                                    --------- ----------
                                    08-JAN-09          7
                                    09-JAN-09         10
                                    14-JUN-09       1851
                                                       2
                                    Although we had generate reports but in future ,

                                    can you please specify me some steps what should be done to oveercome the same......