7 Replies Latest reply on Mar 6, 2013 2:15 AM by Hemant K Chitale

    autotrace time colum bearing no resamblance to actual tiem taken

    user5716448
      Hi,

      Using sqlplus set autotracde traceonly explain to evaluate indexing strategies before kicking off queries and finding time shown on report way lower
      than what actaully taken e.g query shows time of 00:02:21 and used another 3rd party tool to generate epxlain plan - both show about 2 minutes but euqery still running.

      Scanning 400 million rows of 800 million row table so wouldn't expect run in 2 mins - would exect way longer and this is the case still running.

      How can we get accurate estimate of how long queries will take?

      Statistics all up-to-date.

      Using 11.2.0.3

      Thanks

      Edited by: user5716448 on 05-Mar-2013 03:17
        • 1. Re: autotrace time colum bearing no resamblance to actual tiem taken
          JohnWatson
          You say the statistics are up to date, but sometimes people forget about the system statistics. What do you get from this:

          select * from sys.aux_stats$;

          --
          John Watson
          Oracle Certified Master DBA
          http://skillbuilders.com
          1 person found this helpful
          • 2. Re: autotrace time colum bearing no resamblance to actual tiem taken
            user5716448
            Hi,


            Please find below - what diff does this make?

            How do we ensure these are gathered.

            I'm not familar with gathering system stats - don't they get gathered automtically?

            SNAME                          PNAME                               PVAL1
            
            PVAL2
            
            ------------------------------------------------------------------------------------------------------------------------------------
            ---------------------------------------------------------------------------------------------------------------------------
            SYSSTATS_INFO                  STATUS
            
            COMPLETED
            
            SYSSTATS_INFO                  DSTART
            
            10-21-2011 04:48
            
            SYSSTATS_INFO                  DSTOP
            
            10-21-2011 04:48
            
            SYSSTATS_INFO                  FLAGS                                   1
            
            SYSSTATS_MAIN                  CPUSPEEDNW                     697.478992
            
            SYSSTATS_MAIN                  IOSEEKTIM                              10
            
            SYSSTATS_MAIN                  IOTFRSPEED                           4096
            
            SYSSTATS_MAIN                  SREADTIM
            
            SYSSTATS_MAIN                  MREADTIM
            
            SYSSTATS_MAIN                  CPUSPEED
            
            SYSSTATS_MAIN                  MBRC
            
            SYSSTATS_MAIN                  MAXTHR
            
            SYSSTATS_MAIN                  SLAVETHR
            
            ~
            Thanks
            • 3. Re: autotrace time colum bearing no resamblance to actual tiem taken
              JohnWatson
              It looks as though you have never gathered system statistics. Without figures for SREADTIM and MREADTIM Oracle has no idea how long single block read or a multiblock read will take. Read up on the dbms_stats.gather_system_stats procedure, they are not gathered automatically (as you can see).
              --
              John Watson
              Oracle Certified Master DBA
              http://skillbuilders.com
              • 4. Re: autotrace time colum bearing no resamblance to actual tiem taken
                user5716448
                Thanks again for response.

                One last question - how long do they usually take to gather and is there any danger gathering them whilst system live - this is a production system.

                Thanks
                • 5. Re: autotrace time colum bearing no resamblance to actual tiem taken
                  JohnWatson
                  user5716448 wrote:
                  Thanks again for response.

                  One last question - how long do they usually take to gather and is there any danger gathering them whilst system live - this is a production system.

                  Thanks
                  I would try it for half an hour, perhaps, during normal running. If it affects other sessions, you can always stop it. The benefit may be astronomical.
                  1 person found this helpful
                  • 7. Re: autotrace time colum bearing no resamblance to actual tiem taken
                    Hemant K Chitale
                    Even with updated system statistics, the time in an EXPLAIN PLAN is an estimate based on table and index statistics which may or may not be accurate.


                    Similarly, server I/O response times can vary at different times depending on the load, I/O pattern and concurrent I/O from other servers sharing the same SAN (if so configured).


                    Hemant K Chitale