9 Replies Latest reply: May 3, 2013 9:34 AM by 1006363 RSS

    Cost

    975791
      Hi,


      I am learning explain plan.What is cost in oracle?
      SQL>  explain plan for  select * from SQL_TEST where name like '%SQL_TEST%';
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      Plan hash value: 2761765374
      
      ------------------------------------------------------------------------------
      | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |          |  2988 | 86652 |    67   (3)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| SQL_TEST |  2988 | 86652 |    67   (3)| 00:00:01 |
      ------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      
         1 - filter("NAME" LIKE '%SQL_TEST%')
      In the above,How oracle calculate COST (67) for query?What basis?

      Cost=Input/output?(ie, For execution)

      Please share any documents about access path and join methods with EXAMPLE.

      Br,
        • 1. Re: Cost
          sb92075
          when all else fails, Read The Fine Manuals

          http://www.oracle.com/pls/db112/search?remark=quick_search&word=cost&partno=
          • 2. Re: Cost
            975791
            Hi,

            I read oracle documentation before posted here.
            I didn't see any documents How the value comes(COST 67)?
            If you know,please share it.

            Br,
            • 3. Re: Cost
              Nicolas.Gasparotto
              For a given query, for a given path, cost is time
              http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
              http://www.freelists.org/post/oracle-l/Which-plan-is-better-what-COST-really-means,14

              Nicolas.

              fixed 1st link
              Edited by: N Gasparotto on May 2, 2013 4:30 PM
              • 4. Re: Cost
                sb92075
                972788 wrote:
                Hi,

                I read oracle documentation before posted here.
                I didn't see any documents How the value comes(COST 67)?
                If you know,please share it.

                Br,
                "Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns."
                • 5. Re: Cost
                  Justin_Mungal
                  N Gasparotto wrote:
                  For a given query, for a given path, cost is time
                  http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
                  http://www.freelists.org/post/oracle-l/Which-plan-is-better-what-COST-really-means,14

                  Nicolas.

                  fixed 1st link
                  Edited by: N Gasparotto on May 2, 2013 4:30 PM
                  I've never thought of it as time but I suppose one could, for all intents and purposes. It's always been, in my mind, a literal "cost" that is assigned to a plan which is used as a means of comparison during plan selection by the optimizer. I don't really see the need to assign it a meaning of time though. Do you?
                  • 6. Re: Cost
                    Nicolas.Gasparotto
                    Justin Mungal wrote:
                    N Gasparotto wrote:
                    For a given query, for a given path, cost is time
                    http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
                    http://www.freelists.org/post/oracle-l/Which-plan-is-better-what-COST-really-means,14

                    Nicolas.

                    fixed 1st link
                    Edited by: N Gasparotto on May 2, 2013 4:30 PM
                    I've never thought of it as time but I suppose one could, for all intents and purposes. It's always been, in my mind, a literal "cost" that is assigned to a plan which is used as a means of comparison during plan selection by the optimizer. I don't really see the need to assign it a meaning of time though. Do you?
                    Agree. It's not always accurate anyway. The factors are numerous and obscure that is internally used by the optimizer. But at least it's good to know what it means for the optimizer.
                    If I pointed out the articles that I linked, it is especially to read carefully. It does not have to be used for tuning purpose. Read the comment 2 from Jonathan : +"In many cases, cost is not “very reliable” as an indicator; but if you (a) know what it means, (b) know what makes the optimizer go wrong, and (c) don’t mess too much with poorly understood parameters – then it makes sense to take note of what it is telling you."+

                    Nicolas.
                    • 7. Re: Cost
                      975791
                      Hi,

                      Thanks.

                      I read first link.I didn't get values for sreatim in sys.aux_stats$.
                      SQL> SELECT * FROM sys.aux_stats$;
                      
                      SNAME                   PNAME                        PVAL1 PVAL2
                      ----------------------- ----------------------- ---------- ---------------
                      SYSSTATS_INFO           STATUS                             COMPLETED
                      SYSSTATS_INFO           DSTART                             11-20-2006 20:4
                                                                                 9
                      
                      SYSSTATS_INFO           DSTOP                              11-20-2006 20:4
                                                                                 9
                      
                      SYSSTATS_INFO           FLAGS                            1
                      SYSSTATS_MAIN           CPUSPEEDNW              865.334492
                      SYSSTATS_MAIN           IOSEEKTIM                       10
                      SYSSTATS_MAIN           IOTFRSPEED                    4096
                      
                      SNAME                   PNAME                        PVAL1 PVAL2
                      ----------------------- ----------------------- ---------- ---------------
                      SYSSTATS_MAIN           SREADTIM
                      SYSSTATS_MAIN           MREADTIM
                      SYSSTATS_MAIN           CPUSPEED
                      SYSSTATS_MAIN           MBRC
                      SYSSTATS_MAIN           MAXTHR
                      SYSSTATS_MAIN           SLAVETHR
                      whether i need to collect system stats or not?
                      If it's,Is these values are constant or not?

                      Br,
                      • 8. Re: Cost
                        Justin_Mungal
                        972788 wrote:

                        whether i need to collect system stats or not?
                        If it's,Is these values are constant or not?

                        Br,
                        Read about WORKLOAD and NOWORKLOAD statistics. Until you capture WORKLOAD statistics the system will use values derrived from blocksize, MRBC, IOSEEKTIM, and IOTFRSPEED (reference: System Stats MBRC and MREADTIM both null and Cost-Based Oracle).

                        http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#CIHIEIIA

                        Below, you can see that the values were blank on my test system as well. I flushed the buffer cache, started the workload stats capture, ran some test queries, and then stopped the capture. After this, the values were populated.

                        Use caution and understand the implications of flushing your buffer cache before doing so (I did this in a test environment).
                        SQL> col pval2 format a25
                        SQL> SELECT * FROM sys.aux_stats$;
                        
                        SNAME                          PNAME                               PVAL1 PVAL2
                        ------------------------------ ------------------------------ ---------- -------------------------
                        SYSSTATS_INFO                  STATUS                                    COMPLETED
                        SYSSTATS_INFO                  DSTART                                    04-24-2013 04:07
                        SYSSTATS_INFO                  DSTOP                                     04-24-2013 04:07
                        SYSSTATS_INFO                  FLAGS                                   0
                        SYSSTATS_MAIN                  CPUSPEEDNW                           1406
                        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
                        
                        SQL> alter system flush buffer_cache;
                        
                        System altered.
                        
                        SQL> execute dbms_stats.gather_system_stats('start');
                        
                        PL/SQL procedure successfully completed.
                        
                        <created a few tables, ran a few count(*) queries, and a few queries with unique predicates>
                        
                        SQL> execute dbms_stats.gather_system_stats('stop');
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> SELECT * FROM sys.aux_stats$;
                        
                        SNAME                          PNAME                               PVAL1 PVAL2
                        ------------------------------ ------------------------------ ---------- -------------------------
                        SYSSTATS_INFO                  STATUS                                    COMPLETED
                        SYSSTATS_INFO                  DSTART                                    05-02-2013 16:20
                        SYSSTATS_INFO                  DSTOP                                     05-02-2013 16:22
                        SYSSTATS_INFO                  FLAGS                                   1
                        SYSSTATS_MAIN                  CPUSPEEDNW                           1406
                        SYSSTATS_MAIN                  IOSEEKTIM                              10
                        SYSSTATS_MAIN                  IOTFRSPEED                           4096
                        SYSSTATS_MAIN                  SREADTIM                            6.951
                        SYSSTATS_MAIN                  MREADTIM                             2.08
                        SYSSTATS_MAIN                  CPUSPEED                             1405
                        SYSSTATS_MAIN                  MBRC                                    5
                        SYSSTATS_MAIN                  MAXTHR                            4390912
                        SYSSTATS_MAIN                  SLAVETHR
                        I hope this helps.
                        • 9. Re: Cost
                          1006363
                          Hi,

                          A nice article on understanding about cost.

                          http://www.orafaq.com/node/2746

                          Regards,
                          Chirag