Hi Guys,
In today’s post I will try to explain the cost, stats (system & object level), join order, cardinality & the selectivity calculations about execution plan entries and will take help of the infamous 10053 optimizer traces. Though 10053 is notoriously complicated, detailed and extensive even for simple SQL statements but always has the wealth of information about optimizer decisions like plan directives, peeked bind values, predicate move around, Join elimination stats, query transformations, fix controls, join eliminations, join orders (easily complicated) & other computations or calculations. So, I will try to keep it as simple as I can.
About ways of generating the report, you can access it via ALTER SESSION, ORADEBUG or even through DBMS_SQLDIAG.DUMP_TRACE (does not require diagnostic pack). There are multiple use cases of using 10053, but the most common is when you want to compare any best or worst plans and you want to see the difference in cost calculations.
Alright, so this is the complete SQL statement followed up by its execution plan and predicate details.
SELECT * FROM (SELECT n.object_inst_id, n.name, n.type, rownum idx
FROM testsch.PDI_FBI_HASHAH1 n join testsch.PDI_FBI_HASHAH1_b on TESTID_name = :"SYS_B_00" and owning_object_id = n.object_inst_id
and n.name = NVL(null, n.name) AND n.status = NVL(:"SYS_B_01", :"SYS_B_02")
AND n.type = NVL(:"SYS_B_03", n.type) AND NVL(n.test_allocation,:"SYS_B_04") = NVL(NVL(null, n.test_allocation),:"SYS_B_05")
AND NVL(n.number_usage, :"SYS_B_06")=:"SYS_B_07" AND ( n.owner IS NULL OR instr(lower(n.owner), :"SYS_B_08") > :"SYS_B_09" )
AND n.assigned_csn_id IS NULL AND n.locked_on IS NULL AND n.lock_owner IS NULL
AND (n.lock_id IS NULL OR n.LOCK_DATE <= (sysdate - NVL(n.lock_period,
(SELECT dixi_trait_values FROM testsch.PDI_HASHIS_MERGEDINFO_DATA WHERE HASH_BATTR_SHA = :"SYS_B_10")) / :"SYS_B_11" / :"SYS_B_12"))
AND target_object_id = :"SYS_B_13" ORDER BY TO_NUMBER(n.name)) OUTPUTS WHERE OUTPUTS.idx between :"SYS_B_14" and :"SYS_B_15"
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 364 | 107 (0)| 00:00:01 |
|* 1 | VIEW | | 2 | 364 | 107 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | 2 | 340 | 107 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| PDI_FBI_HASHAH1 | 6719 | 505K| 106 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | PDI_FBI_NNAME_UTYPE_STA_NPFIX | 3065 | | 7 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | PDI_HASHIS_MERGEDINFO_DATA | 1 | 28 | 3 (0)| 00:00:01 | --> How the CBO decided cost 3 ?
|* 7 | INDEX RANGE SCAN | IDX_TRNNUMBERB_SMOKE | 1 | 93 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OUTPUTS"."IDX"<=10 AND "OUTPUTS"."IDX">=1)
4 - filter("N"."TYPE"='MOBILE' AND ("N"."OWNER" IS NULL OR INSTR(LOWER("N"."OWNER"),'dna oyj')>0)
AND NVL("N"."NUMBER_USAGE",'External')='Internal' AND "N"."ASSIGNED_CSN_ID" IS NULL AND
NVL("N"."TEST_ALLOCATION",'EAI')=NVL(NVL(NULL,"N"."TEST_ALLOCATION"),'EAI') AND "N"."LOCKED_ON" IS NULL
AND "N"."LOCK_OWNER" IS NULL AND ("N"."LOCK_ID" IS NULL OR
"N"."LOCK_DATE"<=SYSDATE@!-NVL("N"."LOCK_PERIOD", (SELECT "DIXI_TRAIT_VALUES" FROM
"TESTSCH"."PDI_HASHIS_MERGEDINFO_DATA" "PDI_HASHIS_MERGEDINFO_DATA" WHERE
"HASH_BATTR_SHA"='MY_CALL_DURATION'))/60/24))
5 - access("N"."STATUS"='Available')
filter("N"."STATUS"='Available')
6 - filter("HASH_BATTR_SHA"='MY_CALL_DURATION')
7 - access("TESTID_NAME"='ind.fatdba.application.dss.more.production.BESTID.PDINumberBestSubClass'
AND "OWNING_OBJECT_ID"="N"."OBJECT_INST_ID" AND "TARGET_OBJECT_ID"=21)
Let’s try to understand how optimizer come up with the cost numbers, how it derives these values, on what basis and what all it considers during the calculations. I will explaining about one of the access operation at Line ID 6 of the plan where it’s doing a TABLE ACCESS FULL on table PDI_HASHIS_MERGEDINFO_DATA and has a final cost of 3 associated with it.
So, what it is ? what all was happened under the hood by the optimizer to get this value of 3 for this operation ?
This is the subquery for which we will be checking the cost calculations within 10053 trace files.
(SELECT dixi_trait_values FROM testsch.PDI_HASHIS_MERGEDINFO_DATA WHERE HASH_BATTR_SHA = :"SYS_B_10")) / :"SYS_B_11" / :"SYS_B_12"))
Let me replace the sys generated values with their original values, for this I will be checking under “Peeked values of the binds in SQL statement” section. The same you can get from ‘predicate information’ section of the execution plan too, but let’s stick to 10053 for the moment.
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=01 mxl=128(73) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=01 csi=873 siz=128 off=0
kxsbbbfp=7f3791745ee0 bln=128 avl=73 flg=09
value="ind.fatdba.application.dss.more.production.BESTID.PDINumberBestSubClass"
...
......
........
Bind#10
oacdty=01 mxl=32(19) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=01 csi=873 siz=32 off=0
kxsbbbfp=7f3791745d48 bln=32 avl=19 flg=09
value="MY_CALL_DURATION"
Bind#11
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f3791745d20 bln=22 avl=02 flg=09
value=60
Bind#12
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f3791745cf8 bln=22 avl=02 flg=09
value=24
Okay, let’s place them in the subquery.
SELECT dixi_trait_values FROM testsch.PDI_HASHIS_MERGEDINFO_DATA WHERE HASH_BATTR_SHA='MY_CALL_DURATION/60/24';
These are the contents of the column.
SQL> SELECT dixi_trait_values FROM testsch.PDI_HASHIS_MERGEDINFO_DATA;
DIXI_TRAIT_VALUES
-----------------
60
120
200
10
60
Let’s check for access path analysis for this SQL.
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PDI_HASHIS_MERGEDINFO_DATA Alias: PDI_HASHIS_MERGEDINFO_DATA
#Rows: 5 SSZ: 0 LGR: 0 #Blks: 5 AvgRowLen: 31.00 NEB: 0 ChainCnt: 0.00 SPC: 0 RFL: 0 RNF: 0 CBK: 0 CHR: 0 KQDFLG: 1
#IMCUs: 0 IMCRowCnt: 0 IMCJournalRowCnt: 0 #IMCBlocks: 0 IMCQuotient: 0.000000
try to generate single-table filter predicates from ORs for query block SEL$3 (#0)
finally: "PDI_HASHIS_MERGEDINFO_DATA"."DIXI_TRAIT_VALUES"=:B1
=======================================
SPD: BEGIN context at query block level
=======================================
Query Block SEL$3 (#0)
Applicable DS directives:
Return code in qosdSetupDirCtx4QB: NODIR
=====================================
SPD: END context at query block level
=====================================
Access path analysis for PDI_HASHIS_MERGEDINFO_DATA
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PDI_HASHIS_MERGEDINFO_DATA[PDI_HASHIS_MERGEDINFO_DATA]
SPD: Return code in qosdDSDirSetup: NOQBCTX, estType = TABLE
Column (#1): CONF_ATTR_NAME(VARCHAR2)
AvgLen: 24 NDV: 5 Nulls: 0 Density: 0.200000
Table: PDI_HASHIS_MERGEDINFO_DATA Alias: PDI_HASHIS_MERGEDINFO_DATA
Card: Original: 5.000000 Rounded: 1 Computed: 1.000000 Non Adjusted: 1.000000
Scan IO Cost (Disk) = 3.000000
Scan CPU Cost (Disk) = 36377.200000
Total Scan IO Cost = 3.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5.000000 (#rows))
= 3.000000
Total Scan CPU Cost = 36377.200000 (scan (Disk))
+ 250.000000 (cpu filter eval) (= 50.000000 (per row) * 5.000000 (#rows))
= 36627.200000
Access Path: TableScan
Cost: 3.002419 Resp: 3.002419 Degree: 0
Cost_io: 3.000000 Cost_cpu: 36627
Resp_io: 3.000000 Resp_cpu: 36627
Best:: AccessPath: TableScan
Cost: 3.002419 Degree: 1 Resp: 3.002419 Card: 1.000000 Bytes: 0.000000
.....
.......
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE: Bypassed; QB has a single table SEL$3 (#0)
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: PDI_HASHIS_MERGEDINFO_DATA[PDI_HASHIS_MERGEDINFO_DATA]#0
***********************
Best so far: Table#: 0 cost: 3.002419 card: 1.000000 bytes: 28.000000
***********************
If you check above you will see it considers the CPU & IO to generate the final cost values and that’s what we already know. Cost is the amount of work the optimizer estimates to run your query via a specific plan. The optimizer generally prefers lower cost plans. The optimizer’s cost model accounts for the IO, CPU, and network resources that will be used by the query.
IO_COST: I/O cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.
CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.
At the same time it checks for dictionary stats to get table level information i.e. average row lenght, blocks, total rows and takes help of metrics NDV (nuber of distinct values), nulls, density of rows etc. to calculate the original cardinality of 5 (number of distinct entries) and the rounded cardinality of 1 – this SQL has resulted only into 1 row as an output, hence 1 is the rounded cardinality.
So, the main elements are ‘Scan IO Cost (Disk)’, ‘Scan CPU Cost (Disk)’ which finally derives the ‘Total Scan IO Cost’ value of 3.
That’s how the optimizer has calculated this value of 3 for this specific access operation, similarly you understand any of the other access entries in plan, and their associated costs.
If you want to read more on 10053 traces, I recommend you a great article by Wolfgang Breitling, Jonathan Lewis (his book cost-based Oracle fundamentals) & few posts from Maria Colgan.
Hope It Helped
Prashant Dixit
Like this:
Like Loading...
