Mechanism Behind Optimizer Cost Caclculation
Date: Wed, 12 Aug 2020 09:32:05 +0530
Message-ID: <CAOzfMuoiSx7CenJZhkAn5huMRXWXFinswHwQf3FkNRMm9CWwHg_at_mail.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:
- How did Oracle calculate the cost of index range scan to be 3?
- How Oracle calculated the estimated cost of FTS on the table to be 51 (in the attached profile.txt).
- 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-lReceived on Wed Aug 12 2020 - 06:02:05 CEST