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
