Re: Mechanism Behind Optimizer Cost Caclculation

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