Re: Mechanism Behind Optimizer Cost Caclculation

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