1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help in understanding the cost factor in explain plain

Discussion in 'SQL PL/SQL' started by reby0808, Jun 24, 2014.

  1. reby0808

    reby0808 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi All

    Can we the cost as 0 in the explain plan and also let me the minimum and maximum cost that is permissible for query in explain plan?

    Thanks in advance

    Regards
    Rebekah
     
  2. reby0808

    reby0808 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    This was actually a interview question to me what is the minimum and maximum cost through explain plan
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    COST Factor

    Cost factor is an estimated number of physical I/O operations Oracle thinks it will have to do, based on statistics.
    Over time, the basic formula has evolved from just estimated physical I/O to includ additional statistics:
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Who would ask such a question? It's a useless one if you ask me as the 'minimum' cost through 'explain plan' can vary depending on the table/object. Selecting from DUAL in 11.2.0.3 provides a COST of 2; selecting one row from EMP produces a plan with a COST of 3. A cost of 0 would indicate that Oracle did absolutely no work; even a 'no rows selected' result from DUAL produces a plan with a COST of 2:


    Code (SQL):

    SQL> SELECT * FROM dual WHERE dummy='Y'
      2  /


    no ROWS selected

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 272002086
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------


    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - FILTER("DUMMY"='Y')

    Statistics
    ----------------------------------------------------------
              6  recursive calls
              4  db block gets
              4  consistent gets
              0  physical reads
            540  redo SIZE
            333  bytes sent via SQL*Net TO client
            508  bytes received via SQL*Net FROM client
              1  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
              0  ROWS processed


    SQL>
     

    A COST of 0 is not a possibility, a COST of 1 IS possible with an index scan of some sort:


    Code (SQL):

    SQL> SELECT c_1 FROM test1;


           C_1
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10


    10 ROWS selected.

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2345822858
    ------------------------------------------------------------------------------
    | Id  | Operation        | Name      | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |    10 |    30 |     1   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | TEST1_C_1 |    10 |    30 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------


     

    The 'maximum' cost could be almost any number, depending on the number of joins, the size of the joined tables, the access path taken for each table in the join, the size of any sort segments or hash segments required ... I have seen quite large COST figures for various queries during my 26+ year career as a DBA, even on Exadata:


    Code (SQL):

    ----------------------------------------------------------------------------------
    | Id  | Operation                 | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT          |      |   232K|  8402K|  7520   (2)| 00:00:01 |
    |*  1 |  TABLE ACCESS STORAGE FULL| EMP  |   232K|  8402K|  7520   (2)| 00:00:01 |
    ----------------------------------------------------------------------------------


    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
       1 - storage("TTL_COMP">5000)
           FILTER("TTL_COMP">5000)

    Statistics
    ----------------------------------------------------------
              1  recursive calls
              1  db block gets
          71982  consistent gets
          31580  physical reads
         922372  redo SIZE
        6218619  bytes sent via SQL*Net TO client
         102373  bytes received via SQL*Net FROM client
           9261  SQL*Net roundtrips TO/FROM client
              0  sorts (memory)
              0  sorts (disk)
         138888  ROWS processed


    SQL>
     



    Here is a parallel query example with a large cost:\


    Code (SQL):

    SQL> SELECT /*+ parallel(4) */
      2  COUNT(*)
      3  FROM chicken_hr_tab
      4  WHERE suitable_for_frying = 'Yes';


      COUNT(*)
    ----------
       2621440

    Execution Plan
    ----------------------------------------------------------
    Plan hash VALUE: 2233075164
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name           | ROWS  | Bytes | Cost (%CPU)| TIME     |    TQ  |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |                |     1 |     4 | 11754   (1)| 00:00:01 |        |      |            |
    |   1 |  SORT AGGREGATE                |                |     1 |     4 |            |          |        |      |            |
    |   2 |   PX COORDINATOR               |                |       |       |            |          |        |      |            |
    |   3 |    PX SEND QC (RANDOM)         | :TQ10000       |     1 |     4 |            |          |  Q1,00 | P->S | QC (RAND)  |
    |   4 |     SORT AGGREGATE             |                |     1 |     4 |            |          |  Q1,00 | PCWP |            |
    |   5 |      PX BLOCK ITERATOR         |                |  2621K|    10M| 11754   (1)| 00:00:01 |  Q1,00 | PCWC |            |
    |*  6 |       TABLE ACCESS STORAGE FULL| CHICKEN_HR_TAB |  2621K|    10M| 11754   (1)| 00:00:01 |  Q1,00 | PCWP |            |
    ------------------------------------------------------------------------------------------------------------------------------
     

    This, again, on Exadata. It appears there is no 'right' answer to maximum cost through explain plan. One other note: EXPLAIN PLAN may not give you the correct plan in some releases of Oracle since it may rely on outdated RULE-based heuristics rather than cost-based calculations. I have seen this behavior in releases as late as 10.1.


    The question, as asked, has no usable answer since the 'minimum' depends on the execution path and the 'maximum' depends on much more. It's a poor interview question, if you ask me, and should never be asked since anyone can disprove the interviewer's results with a properly crafted and executed example.