Home -> Mailing Lists -> Oracle-L -> Re: Mechanism Behind Optimizer Cost Caclculation

Re: Mechanism Behind Optimizer Cost Caclculation

From: Rakesh T <aryan.goti_at_gmail.com>
Date: Wed, 12 Aug 2020 15:53:35 +0530
Message-ID: <CAOzfMurLyN3cR=0LyT2DgFVfSJCP=fpYsKar=KhZT-Hu9DD3=g_at_mail.gmail.com> 








Hi Sayan,



Thanks, yes you are right we have cursor_sharing set to FORCE.



Below is the 10053 trace.



https://pastebin.com/1mUFd4Sd




Thanks,


Rakesh T




On Wed, Aug 12, 2020 at 11:00 AM Sayan Malakshinov <xt.and.r_at_gmail.com>
wrote:



> Also looks like you have cursor_sharing = force.


>

> ср, 12 авг. 2020 г., 8:27 Sayan Malakshinov <xt.and.r_at_gmail.com>:

>

>> Hi Rakesh,

>>

>> The main reason is that rownum predicate forces cbo switch optimizer mode

>> to first-k-rows.

>> You can post your 10053 on pastebin or gist.github.com

>>

>> ср, 12 авг. 2020 г., 7:03 rakesh <aryan.goti_at_gmail.com>:

>>

>>> Hi All,

>>>

>>> I would like to understand the mechanics behind cost calculation for the

>>> below mentioned query for both the execution plans attached. The database

>>> version is 11.2 and running on a VM with 2 CPU's. No systems stats are

>>> collected. DBMRC is set to 8 in the database.

>>>

>>> select * from ODIS.BATCH_JOB_EXECUTION_CONTEXT where

>>> JOB_EXECUTION_ID>1000 and ROWNUM<=501;

>>>

>>>

>>> MIN(JOB_EXECUTION_ID) MAX(JOB_EXECUTION_ID)


>>> --------------------- ---------------------

>>>                110275                117519

>>>

>>> Table Stats:

>>> ================

>>>

>>> OWNER                          TABLE_NAME                     PAR


>>> NUM_ROWS LAST_ANALYZED          BLOCKS CACHE


>>> ------------------------------ ------------------------------ ---

>>> ---------- ------------------ ---------- --------------------

>>> ODIS                           BATCH_JOB_EXECUTION_CONTEXT    YES


>>> 7212 11-AUG-20                2636     N


>>>

>>> Column Stats:

>>> ==================

>>>

>>> OWNER                          TABLE_NAME


>>> COLUMN_NAME                    NUM_DISTINCT NUM_BUCKETS HISTOGRAM


>>>  DENSITY


>>> ------------------------------ ------------------------------

>>> ------------------------------ ------------ ----------- ---------------

>>> ----------

>>> ODIS                           BATCH_JOB_EXECUTION_CONTEXT


>>>  SERIALIZED_CONTEXT                        0           0 NONE


>>>       0

>>> ODIS                           BATCH_JOB_EXECUTION_CONTEXT


>>>  SHORT_CONTEXT                          6502           1 NONE


>>>  .000153799

>>> ODIS                           BATCH_JOB_EXECUTION_CONTEXT


>>>  JOB_EXECUTION_ID                       7212           1 NONE


>>>  .000138658

>>>

>>> Current plan for the sql statement

>>> ===============================================

>>>

>>> PLAN_TABLE_OUTPUT


>>>

>>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>>> SQL_ID  0dk7dz1hpkymc, child number 1

>>> -------------------------------------

>>> select * from ODIS.BATCH_JOB_EXECUTION_CONTEXT where

>>> JOB_EXECUTION_ID>:"SYS_B_0" and ROWNUM<=:"SYS_B_1"

>>>

>>> Plan hash value: 3394151159

>>>

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------

>>> | Id  | Operation                           | Name

>>>  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------

>>> |   0 | SELECT STATEMENT                    |


>>>   |      1 |        |    501 |00:00:00.01 |    1378 |

>>> |*  1 |  COUNT STOPKEY                      |


>>>   |      1 |        |    501 |00:00:00.01 |    1378 |

>>> |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID|


>>> BATCH_JOB_EXECUTION_CONTEXT |      1 |    501 |    501 |00:00:00.01 |


>>>  1378 |

>>> |*  3 |    INDEX RANGE SCAN                 | SYS_C0032478


>>>  |      1 |        |    501 |00:00:00.01 |     680 |

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------

>>>

>>> Predicate Information (identified by operation id):

>>> ---------------------------------------------------

>>>

>>>    1 - filter(ROWNUM<=:SYS_B_1)

>>>    3 - access("JOB_EXECUTION_ID">:SYS_B_0)

>>>

>>> Plan from cursor with cost:

>>> =============================

>>>

>>> Plan hash value: 3394151159

>>>

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------------

>>> | Id  | Operation                           | Name

>>>  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------------

>>> |   0 | SELECT STATEMENT                    |


>>>   |       |       |   309 (100)|          |       |       |

>>> |*  1 |  COUNT STOPKEY                      |


>>>   |       |       |            |          |       |       |

>>> |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID|


>>> BATCH_JOB_EXECUTION_CONTEXT |   501 |  1250K|   309   (0)| 00:00:04 | ROWID


>>> | ROWID |


>>> |*  3 |    INDEX RANGE SCAN                 | SYS_C0032478


>>>  |       |       |     3   (0)| 00:00:01 |       |       |

>>>

>>> -----------------------------------------------------------------------------------------------------------------------------------

>>>

>>> Predicate Information (identified by operation id):

>>> ---------------------------------------------------

>>>

>>>    1 - filter(ROWNUM<=:SYS_B_1)

>>>    3 - access("JOB_EXECUTION_ID">:SYS_B_0)

>>>

>>> Based on the above min and max values for job_execution_id it is evident

>>> that application is interested in all the data in the table. But I see the

>>> optimizer is picking up index based plan. Furthermore, profile

>>> recommendations show a FTS plan.

>>>

>>> Profile Recommenedation:

>>>

>>> 2- Using SQL Profile

>>> --------------------

>>> Plan hash value: 1573603696

>>>

>>>

>>> ------------------------------------------------------------------------------------------------------------------------

>>> | Id  | Operation                | Name                        | Rows  |

>>> Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

>>>

>>> ------------------------------------------------------------------------------------------------------------------------

>>> |   0 | SELECT STATEMENT         |                             |   501 |


>>>  1250K|    51   (0)| 00:00:01 |       |       |

>>> |*  1 |  COUNT STOPKEY           |                             |       |


>>>       |            |          |       |       |

>>> |   2 |   PARTITION REFERENCE ALL|                             |   501 |


>>>  1250K|    51   (0)| 00:00:01 |     1 |     2 |

>>> |*  3 |    TABLE ACCESS FULL     | BATCH_JOB_EXECUTION_CONTEXT |   501 |


>>>  1250K|    51   (0)| 00:00:01 |     1 |     2 |

>>>

>>> ------------------------------------------------------------------------------------------------------------------------

>>>

>>> So would like understand  the below:

>>>

>>> a) How did Oracle calculate the cost of index range scan to be 3?

>>> b) How Oracle calculated the estimated cost of FTS on the table to be 51

>>> (in the attached profile.txt).

>>> c) Why didn't Oracle go for FTS on the table as the query is interested

>>> in all the rows in the table.

>>>

>>> Apologize for the lengthy email. I am trying to send this mail from

>>> yesterday onwards and it looks for some reason this mail is getting

>>> blocked. So I thought to put all the contents over email without an

>>> attachment. I do have 10053 trace but not sure how I should attach, as

>>> attachments are not going through.

>>>

>>>

>>> Thanks,

>>> Rakesh T

>>>

>>



--
http://www.freelists.org/webpage/oracle-l


Received on Wed Aug 12 2020 - 12:23:35 CEST












Original text of this message