1 2 Previous Next 16 Replies Latest reply: Jan 4, 2013 4:17 PM by 905989 Go to original post RSS
      • 15. Re: execution plan and stale statistics
        Thanks John,

        I ran sql trace and DBA fetched out the output from tkprof. This shows as follows:
        FD.CCY_BOUGHT,fd.AMT_BOUGHT,FD.CCY_SOLD, fd.AMT_SOLD, fd.TRADER_NAME, fd.entry_date_time, fd.STATUS_flag ,fd.rate,
        fd.PRODUCT, fd.area,a.AREA_DESCRIP,fd.portfolio, a.legal_entity_name
        FROM TABLE fd, TABLE2 a WHERE fd.TRADE_DATE <= TO_DATE(:endtime,'DD/MM/YYYY  hh24:mi:ss')
        and fd.VALUE_date >= TO_DATE(:begintime,'DD/MM/YYYY  hh24:mi:ss') AND fd.AREA ='SGFXMM' AND fd.area = a.area_code
        call     count       cpu    elapsed       disk      query    current        rows
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
        Parse        1      0.00       0.00          0          0          0           0
        Execute      1      0.01       0.01          0          0          0           0
        Fetch     1208      4.64       5.52       3596     487194          0       18093
        ------- ------  -------- ---------- ---------- ---------- ----------  ----------
        total     1210      4.65       5.53       3596     487194          0       18093
        Misses in library cache during parse: 1
        Misses in library cache during execute: 1
        Optimizer mode: CHOOSE
        Parsing user id: 2009  
        Rows     Row Source Operation
        -------  ---------------------------------------------------
          18093  MERGE JOIN CARTESIAN (cr=487194 pr=3596 pw=0 time=5413799 us)
              1   TABLE ACCESS FULL TABLE2 (cr=34 pr=0 pw=0 time=511 us)
          18093   BUFFER SORT (cr=487160 pr=3596 pw=0 time=5395444 us)
          18093    TABLE ACCESS BY INDEX ROWID TABLE (cr=487160 pr=3596 pw=0 time=5583711 us)
         665440     INDEX RANGE SCAN TABLE_IDX1 (cr=6928 pr=3508 pw=0 time=695826 us)(object id 236319)
        Elapsed times include waiting on following events:
          Event waited on                             Times   Max. Wait  Total Waited
          ----------------------------------------   Waited  ----------  ------------
          SQL*Net message to client                    1208        0.00          0.00
          db file sequential read                      3596        0.04          1.22
          SQL*Net message from client                  1208        0.09         93.50
        which makes it easier to understand with CPU time of 4.65 sec and total elapsed time of 5.53 sec returning 18,093 rows. So the lion's share was fetching those 18k+ rows. There were 3,508 physical index block reads culminating in total of 3,596 physical block reads. In total there were 487,194 logical block reads that included 3,596 physical block reads. The wait events shows that there were 3,596 db file sequential read Physical I/O that took 1.22 sec in total. I gather these were all single block reads.

        So the sql trace output shows that the plan is pretty efficient and I gather autorace is way off misleading?


        Edited by: 902986 on 04-Jan-2013 09:36
        • 16. Re: execution plan and stale statistics

          You are correct that stats for this table were locked for two years because of out of bound statistics due to 8 rogue records (they were added by mistakes for date 2018 as opposed to 2008. These were not deleted in the belief that would be fed from an external source again. However, we found that to be incorrect. So these records are removed and the skeness (or the cause of skew to data) has gone.

          We imported the schema to a test env, unlocked the stats on two tables concerned and now redoing stats and trying to see whether we see any issues etc. In so far it goes we have not seen any degradation of performance (compared to when stats were locked) and it appears that we are getting better CBO plans.

          I have read your paper and I believe that we no longer have those issues. (hopefully). However, a point to make is whether when one does gather_table_stats the old statistics are completely removed? My naiive question is by redoing stats we have hopefully thrown away the skerwed stats for those 8 records? For example would it be necessary to delete stats for those two tables and their indexes and do them again?

          1 2 Previous Next