Resp: you might have spotted it “here and there” in the 10053 trace file.
It may turn out important if, for example, all of sudden your SQL similar to the one below starts running in parallel (of what you may not be aware straight away), visibly slower – and most of all wasting I/O and CPU resources.
It’d be particularly annoying, provided it used to run efficiently using quite selective index on table(col) at nearly no effort.
In order to address it you may want to take a look at the “Resp” parameter in 10053 trace file.
Here it is.
So, let’s simplify the test case and let us consider such query:
select * from table where col > value
In real, I will be executing:
SQL> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 – 10e3 ;
so essentially adhering to the one above with the following relevant data:
- The table TEST01 is ~13GB in size
- It has a column ID: 1 – 5’000’000 (unique) values with index on it
- Under normal circumstances you’d be expecting almost effortless index range scan, if you consider the predicate selectivity of 0.002 and the perfect clustering factor (so no doubt the index should be used, shouldn’t it?)
Here is the summary:
SYS@CDBTST1->PDBPS> create table TESTUS.TEST01
as
select rownum as ID, sysdate-mod(rownum,365) as mydate, rpad('X',2048,'X') as padding
from dual connect by level <= 5e6 ;
Table created.
SYS@GCTEST1->PDB01> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => '&OWNER' , tabname => '&tablename',cascade => true, estimate_percent => &estimate_percent, granularity => 'ALL', degree => &parallel_slaves);
Enter value for owner: TESTUS
Enter value for tablename: TEST01
Enter value for estimate_percent: 100
Enter value for parallel_slaves: 4
PL/SQL procedure successfully completed.
SYS@CDBTST1->PDBPS> desc TESTUS.TEST01
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
MYDATE DATE
PADDING VARCHAR2(2048)
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SEG_MB LOBSIZE_MB TOTAL_MB
----------------- ------------------ ------------------------------ ------------ ------------ ------------
TESTUS.TEST01 TABLE TS01 13,065 13,065
TABLE_NAME TABLESPACE_NAME COMPRESS COMPRESS_FOR BLKSIZE BLOCKS NUM_ROWS AVG_ROW_LEN PCT_FREE INITRANS FREELISTS FREELGRPS Est[MB]from RowLen
--------------- --------------- -------- --------------- ---------- ---------------- ---------------- ----------- ---------- -------- --------- --------- ------------------
TESTUS.TEST01 TS01 DISABLED 8192 1,668,788 5,000,000 2063 10 1 10,821
OWNER TABLENAME INDEX_NAME INDEX_TYPE UNIQUENES COMPRESSION IDX_PX CLUSTF TabBlks TabRows NDV LEAF_BLOCKS COLS PAR BLEVEL LEAF_BL_KEY DATA_BL_KEY ORPHANED_E STATUS
------------------ ------------------------------ ------------------------------ --------------- --------- ------------- -------- ------------ ---------------- ---------------- --------- ----------- ---- --- ------ ----------- ----------- ---------- ----------
TESTUS TEST01 IDX_ON_TEST01 NORMAL UNIQUE DISABLED 1 1,666,667 1,668,788 5,000,000 5000000 11025 1 NO 2 1 1 NO VALID
OWNER TABLE_NA COL_ID COLUMN_NAME DATA_TYPE NDV DENSITY NUM_NULLS AVG_COL_LEN LOW_VAL HI_VAL
------ -------- ------ ------------ ------------ ------------ ----------- ---------- ----------- ----------------------------------- -----------------------------------
TESTUS TEST001 1 ID NUMBER 5,000,000 .00000020 0 6 1 5000000
TESTUS 2 MYDATE DATE 365 .00273973 0 8 2024-01-20/12:45:03 2025-01-18/12:45:03
TESTUS 3 PADDING VARCHAR2 1 1.00000000 0 2049 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Let me start from the example of dump of the 10053 trace file which represents the perfectly normal behavior, that is, being the outcome of the expected index range scan.
This section of the 10053 trace contains the two most relevant excerpts, namely the parts of comparing two costs:
Cost of full table scan (FTS) – always costed, always being a viable option
Cost of index access (I happen to have an index on the predicate in my SQL)
Note, for the time being the Resp can be ignored as it won’t tell us anything which might draw attention until the next SQL execution.
Here it is :
SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: serial
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='serial'
SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: serial
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* serial */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3360 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 10001 | 19M| 3360 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 10001 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4990000)
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Column (#1): ID(NUMBER)
AvgLen: 6 NDV: 5000000 Nulls: 0 Density: 0.000000 Min: 1.000000 Max: 5000000.000000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 12634573654.720001
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 12634573654.720001 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 12884573654.720001
Access Path: TableScan
Cost: 289341.588631 Resp: 289341.588631 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 12884573655
Resp_io: 288998.000000 Resp_cpu: 12884573655
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_ON_TEST01
Cost: 3359.741912 Degree: 1 Resp: 3359.741912 Card: 10001.002000 Bytes: 0.000000The optimizer had no doubts. It compared two numbers: 3359.741912 and 289341.588631 (the calculated cost of the index range scan with the computed cost of the full table scan) – and it chose the index scan. Absolutely no surprise (see this post for more thorough explanations of costing of full table scan vs index range scan).
As mentioned, the “Resp”, albeit present, does not report anything which could capture our attention – simply repeating the values assigned to the respective “costs”.
The SQL Plan reflects this decision – see the Plan ID = 1 (the values in the SQL Plan are rounded).
Until now.
Now I will “silently” make the full table scan of my test query to be costed as running in parallel. I will start from the modest PX = 2.
Here it is:
SQL> alter table TESTUS.TEST01 parallel 2;
SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: PX2
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PX2'
Session altered.
SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
SYS@CDBTST1->PDBPS>
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: px2
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* px2 */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3360 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 10001 | 19M| 3360 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 10001 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4990000)
10053 trace :
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 750400040.000000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 750400040.000000 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 1000400040.000000
Access Path: TableScan
Cost: 289024.677334 Resp: 160569.265186 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 1000400040
Resp_io: 160554.444444 Resp_cpu: 555777800
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_ON_TEST01
Cost: 3359.741912 Degree: 1 Resp: 3359.741912 Card: 10001.002000 Bytes: 0.000000
I obviously mean the following excerpt from the section of costing of the FTS of TEST01 table:
Access Path: TableScan
Cost: 289024.677334 Resp: 160569.265186 Degree: 0
As you surely noticed, the Resp represents (nearly) the value of the original FTS cost divided by 2 and now this value is compared with the cost of the range index scan.
In that case the cost of index access is still much lower (3359.741912 vs. 160569.265186), thus the CBO still has no doubts and it’s visible in the SQL Plan (ID = 1).
Resp btw. stands for, as I have managed to establish, “response in parallel” (or similar) – anyway, involving parallelism. So Resp is clearly related to the level of parallelism degree. It’ll be interesting to see whether it retains a linear ratio. Anticipating, it seems, it does – see the subsequent tests.
There is one more detail to mention. Namely, Cost: 289024.677334 Resp: 160569.265186 Degree: 0 – specifically, the Degree: 0.
As you can see, the 10053 is very cryptic and does not clearly reveal it had used the parallelism (of degree = 2) to calculate the final cost of the FTS.
This changes when you e.g. specifically hint your query with e.g. parallel(2) – I will enclose such example.
So, I will meticulously continue with gradual increasing of the degree level of the table TEST01 setting its degree to: 8, 64 and 90 and 100. The accompanied dumps will come along the way.
Why such weird numbers: 90 and 100 ?
If you commence with the original serial cost of the tested FTS: 289’341 (Resp: 289341.588631) and assume the the linear ratio of cost decreasing along with the increasing of the degree level, you will be able to roughly estimate the level of parallelism which will make the cost of the FTS low enough to be finally selected as the lower of the two: FTS vs. index range scan.
So, rough calculations which would lead to selecting FTS over the range index scan looks “almost” as follows: 289341 / x < 3360 – our “x” (level of parallelism of TEST01) Under (false) assumption that Resp represents straight division: Serial FTS Cost/degree of PX, the “x” would be somewhere around 86 to make the cost of the FTS lower.
As we noticed, it does not (Resp is roughly 10% bigger than the result of this division).
In reality, with other factors fixed, the breaking point will be noted at the level of parallelism of the underlying table somewhere around the PX = 100.
Underneath are the dumps with the relevant lines marked.
I will put just few comments until the breaking point with PX=100 takes place.
Just follow the CBO’s decisions based on comparing of the cost of the parallel FTS (Resp) getting closer to serial index range scan. As long as this cost does not get low enough, the SQL Plan obviously reports the index range scan.
Degree of parallelism of TEST01 = 8 :
SQL> alter table TESTUS.TEST01 parallel 8;
21:21:17 SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: PX8
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PX8'
Session altered.
21:21:17 SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
Session altered.
Elapsed: 00:00:00.00
21:21:23 SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: px8
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* px8 */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3360 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 10001 | 19M| 3360 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 10001 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4990000)
10053 trace :
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 750400040.000000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 750400040.000000 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 1000400040.000000
Access Path: TableScan
Cost: 289024.677334 Resp: 40142.316296 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 1000400040
Resp_io: 40138.611111 Resp_cpu: 138944450
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_ON_TEST01
Cost: 3359.741912 Degree: 1 Resp: 3359.741912 Card: 10001.002000 Bytes: 0.000000
Degree of parallelism of TEST01 = 64 :
SQL> alter table TESTUS.TEST01 parallel 64;
SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: serial
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PX64'
Session altered.
SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: px64
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* px64 */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3360 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 10001 | 19M| 3360 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 10001 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4990000)
10053 trace :
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 750400040.000000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 750400040.000000 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 1000400040.000000
Access Path: TableScan
Cost: 289024.677334 Resp: 5017.789537 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 1000400040
Resp_io: 5017.326389 Resp_cpu: 17368056
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_ON_TEST01
Cost: 3359.741912 Degree: 1 Resp: 3359.741912 Card: 10001.002000 Bytes: 0.000000
Degree of parallelism of TEST01 = 90 (see the both compared costs: they are very close now!)
SQL> alter table TESTUS.TEST01 parallel 90;
SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: PX8
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PX8'
Session altered.
SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: px90
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* px90 */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3360 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 10001 | 19M| 3360 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 10001 | | 25 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4990000)
10053 trace :
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 750400040.000000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 750400040.000000 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 1000400040.000000
Access Path: TableScan
Cost: 289024.677334 Resp: 3568.205893 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 1000400040
Resp_io: 3567.876543 Resp_cpu: 12350618
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_ON_TEST01
Cost: 3359.741912 Degree: 1 Resp: 3359.741912 Card: 10001.002000 Bytes: 0.000000
Degree of parallelism of TEST01 = 100 – breaking point!
Note: the Resp calculated based on the degree 100 makes the breaking point in the SQL Plan.
The CBO compares the parallel cost (Resp) of the full table scan of TEST01 with the serial index range scan and simply follows the rules.
Now the the query would run in parallel :
SQL> alter table TESTUS.TEST01 parallel 100;
SYS@CDBTST1->PDBPS> @tr10053
Enter value for file_identifier: PX100
old 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='&file_identifier'
new 1: ALTER SESSION SET TRACEFILE_IDENTIFIER='PX100'
Session altered.
SYS@GCTEST1->PDB01> alter session set events '10053 trace name context forever, level 1';
Session altered.
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3 ;
Enter value for comment: px100
old 1: select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
new 1: select /* px100 */ * from TESTUS.TEST01 where ID >= 5000000 - 10e3
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 1654899929
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10001 | 19M| 3211 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 10001 | 19M| 3211 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10001 | 19M| 3211 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS STORAGE FULL| TEST01 | 10001 | 19M| 3211 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("ID">=4990000)
filter("ID">=4990000)
Note
-----
- Degree of Parallelism is 100 because of table property
10053 trace:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TEST01[TEST01]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
kkecdn: Single Table Predicate:"TEST01"."ID">=4990000
Estimated selectivity: 0.002000 , col: #1
Table: TEST01 Alias: TEST01
Card: Original: 5000000.000000 Rounded: 10001 Computed: 10001.002000 Non Adjusted: 10001.002000
Scan IO Cost (Disk) = 288998.000000
Scan CPU Cost (Disk) = 750400040.000000
Cost of predicates:
io = NOCOST, cpu = 50.000000, sel = 0.002000 flag = 2048 ("TEST01"."ID">=4990000)
Total Scan IO Cost = 288998.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 5000000.000000 (#rows))
= 288998.000000
Total Scan CPU Cost = 750400040.000000 (scan (Disk))
+ 250000000.000000 (cpu filter eval) (= 50.000000 (per row) * 5000000.000000 (#rows))
= 1000400040.000000
Access Path: TableScan
Cost: 289024.677334 Resp: 3211.385304 Degree: 0
Cost_io: 288998.000000 Cost_cpu: 1000400040
Resp_io: 3211.088889 Resp_cpu: 11115556
****** Costing Index IDX_ON_TEST01
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_SCAN
SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER
Estimated selectivity: 0.002000 , col: #1
Access Path: index (RangeScan)
Index: IDX_ON_TEST01
resc_io: 3359.000000 resc_cpu: 27821697
ix_sel: 0.002000 ix_sel_with_filters: 0.002000
Cost: 3359.741912 Resp: 3359.741912 Degree: 1
Best:: AccessPath: TableScan
Cost: 3211.385304 Degree: 100 Resp: 3211.385304 Card: 10001.002000 Bytes: 0.000000Several comments.
- As mentioned above, the CBO compares the parallel cost (Resp) of the full table scan of TEST01: Resp: 3211.385304 with the serial index range scan: Cost: 3359.741912 and decides to apply the access path with lower cost: parallel query.
- Besides, once the Resp for parallel query has been selected as the final cost the trace file reports the applied Degree:
Best:: AccessPath: TableScan
Cost: 3211.385304 Degree: 100 Resp: 3211.385304
- I have chosen the degree of 100 somewhat arbitrarily – the breaking point would likely lie a bit lower, perhaps 98 or even 96.
I haven’t undertaken an attempt to establish any accurate formula for Resp (as mentioned, quick ‘n dirty calculation is: Serial FTS Cost divided by Degree), here’s the summary though:
| Serial FTS Cost | Cost Resp | Degree | Ratio Resp/Serial [%] | Multiplier PX |
| 289341.588631 | 160569.265186 | 2 | 55.49 | 110.99 |
| 289341.588631 | 40142.316296 | 8 | 13.87 | 110.99 |
| 289341.588631 | 5017.789537 | 64 | 1.73 | 110.99 |
| 289341.588631 | 3568.205893 | 90 | 1.23 | 110.99 |
| 289341.588631 | 3211.385304 | 100 | 1.11 | 110.99 |
What’s fairly easy to spot is the fixed ratio (“Multiplier PX”) calculated ad-hoc as:
Cost Resp/Cost_of_Serial_FTS*100 * Degree.
This would suggest that Resp holds the linear relationship.
Extreme example
Now I will run the modified test query “for real”, intercepting the runtime statistics from “SET AUTOTRACE TRACE EXPLAIN STATISTICS”.
This time I significantly decreased the cost of index range scan (down to 339) because I reduced the number of returned rows from 10’001 to 1’001, thus I produced (order of magnitude) lower selectivity, being the crucial factor while costing an index range scan.
So, the test query looks like :
select * from TESTUS.TEST01 where ID >= 5000000 – 1000 ;
There is an important thing to notice just at the beginning: the CBO does not carry out any checks against the maximum parallel query servers per instance while calculating the cost.
That means, you can use unreasonably high number in the parallel clause of the “Alter table <Table> PARALLEL N” in order to beat any – no matter how efficient – index access.
In order to prove that I will set up the degree of the table TEST01 to 1024.
Obviously, I can’t provide resources to service such high level of parallelism.
This time I skip the dump of the 10053 trace. The calculated cost in the SQL Plan of the parallel version (Resp) matches the expectations, so no need to look at the before examined sections of the 10053 again.
First, these are the values of the relevant instance parameters (maximum parallel query servers per instance: 320 for each of the two active instances).
parallel_max_servers: maximum parallel query servers per instance
INS NAME VALUE
---- ------------------------------ ----------
1 parallel_max_servers 320
2 parallel_max_servers 320 Now I will run the new test query without tricking the optimizer (index range scan will be chosen) :
SYS@CDBTST1->PDBPS> SET AUTOTRACE TRACE EXPLAIN STATISTICS
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 1000 ;
Enter value for comment: serial
old 1: select /* &comment */ * from DELME.TEST01 where ID >= 5000000 - 1000
new 1: select /* serial */ * from DELME.TEST01 where ID >= 5000000 - 1000
1001 rows selected.
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 103831627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 2016K| 339 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST01 | 1001 | 2016K| 339 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ON_TEST01 | 1001 | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=4999000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
473 consistent gets
0 physical reads
0 redo size
2093612 bytes sent via SQL*Net to client
1155 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed
Now the parallel version with the level of parallelism = 1024 :
SYS@CDBTST1->PDBPS> alter table DELME.TEST01 parallel 1024 ;
Table altered.
SYS@CDBTST1->PDBPS> select /* &comment */ * from TESTUS.TEST01 where ID >= 5000000 - 1000 ;
Enter value for comment: px_sqlm
old 1: select /* &comment */ * from DELME.TEST01 where ID >= 5000000 - 1000
new 1: select /* px_sqlm */ * from DELME.TEST01 where ID >= 5000000 - 1000
1001 rows selected.
SQL_ID: 78bnpcq613n3h
Elapsed: 00:00:01.67
Execution Plan
----------------------------------------------------------
SQL_ID: ghvz4hayrhhh5
Plan hash value: 1654899929
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1001 | 2016K| 314 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1001 | 2016K| 314 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1001 | 2016K| 314 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS STORAGE FULL| TEST01 | 1001 | 2016K| 314 (0)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ID">=4999000)
Note
-----
- Degree of Parallelism is 1024 because of table property
Statistics
----------------------------------------------------------
1922 recursive calls
0 db block gets
1784016 consistent gets
1666667 physical reads
0 redo size
35631 bytes sent via SQL*Net to client
1156 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed
The CBO carried out the following comparison: it compared the cost (339) of very efficient index range scan with the cost (Resp) of running of the same query with the level of parallelism set for the underlying table to degree=1024 and the calculated cost 314 (so, roughly the cost of serial FTS div. by 1024 increased by ~10%).
As you may guess, the cost of the serial FTS would not and did not change, regardless to by how much I would have reduced the selectivity of the predicate.
It obviously chose the parallel version, even though the resources can’t be provided.
This is clearly visible when looking at the output of the corresponding SQL Monitor for the parallel query. See the red marks (several less important lines are removed).
The elapsed times for both queries are comparable (the PX version took ~2 sec. whereas the index range scan based execution lasted below 1sec.).
This is certainly not the point – in order to execute the parallel query I have saturated available resources. It’s easy to imagine the resources engaged in execution of such query which would last much longer.
Longer – ah, that surely means bigger table, thus the serial FTS cost would be bigger.
That in turn would produce a (Resp) cost which would be higher and possibly not able to outweigh the potentially very efficient range index scan.
Well, we can continue with such speculations – then I would put against it (a higher Resp, I mean) e.g. much higher clustering factor (CLUF) and/or poorer selectivity – that would obviously make the cost of index range scan not that attractive any longer.
It’s sort of “tug of war”, if you like.
What I am trying to point out: “there’s always a bigger fish” – if you know/remember “Star Wars: The Phantom Menace” 😉
There surely will be a breaking point at which the cost of a full table scan accomplished in parallel turns out lower than the serial range index scan. It’s essentially the matter of the degree of parallelism applied for that table as the declared degree is not checked against the parameter in charge while calculating the Resp.
It may be hard to believe, but I have witnessed a situation in which a parallel full table scan was costed lower than the unique index access following an accidental altering a table to the sufficiently high degree of parallelism.
Here is the output of the SQL Monitor of the examined run :
SQL MONITOR
sqlmsqlid.sql :
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 330
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
select dbms_sqltune.REPORT_SQL_MONITOR(sql_id=>'&&sql_id', report_level=>'ALL', type=>'TEXT') as report from dual;
SYS@GCTEST1->PDB01> @sqlmsqlid
Enter value for sql_id: 78bnpcq613n3h
old 1: select dbms_sqltune.REPORT_SQL_MONITOR(sql_id=>'&&sql_id', report_level=>'ALL', type=>'TEXT') as report from dual
new 1: select dbms_sqltune.REPORT_SQL_MONITOR(sql_id=>'78bnpcq613n3h', report_level=>'ALL', type=>'TEXT') as report from dual
SQL Monitoring Report
SQL Text
------------------------------
select /* px_sqlm */ * from DELME.TEST01 where ID >= 5000000 - 1000
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (1460:51897)
SQL ID : 78bnpcq613n3h
SQL Execution ID : 16777216
Duration : 2s
DOP Downgrade : 38%
Global Stats
========================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
========================================================================================================
| 732 | 12 | 562 | 0.00 | 0.01 | 157 | 68 | 2M | 13046 | 13GB |
========================================================================================================
Parallel Execution Details (DOP=640 , Servers Requested=1024 , Servers Allocated=640)
Instances : 2
====================================================================================================================================================================
| Instance | Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
====================================================================================================================================================================
| 1 | PX Coordinator | QC | | 1.15 | 1.14 | | 0.00 | 0.01 | | 7 | | . | |
| 1 | p000 | Set 1 | 1 | 1.10 | 0.02 | 1.08 | | | | 4646 | 34 | 34MB | direct path read (1) |
| 1 | p001 | Set 1 | 2 | 1.14 | 0.01 | 1.12 | | | 0.01 | 3141 | 23 | 23MB | direct path read (1) |
| 1 | p002 | Set 1 | 3 | 1.17 | 0.03 | 1.14 | | | 0.00 | 7390 | 54 | 54MB | direct path read (1) |
| 1 | p003 | Set 1 | 4 | 1.08 | 0.03 | 1.04 | | | 0.01 | 6691 | 49 | 49MB | direct path read (1) |
| 1 | p004 | Set 1 | 5 | 1.10 | 0.02 | 1.08 | | | | 3973 | 29 | 29MB | direct path read (1) |
| 1 | p005 | Set 1 | 6 | 1.10 | 0.02 | 1.07 | | | 0.01 | 3284 | 24 | 24MB | direct path read (1) |
| 1 | p006 | Set 1 | 7 | 1.17 | 0.02 | 1.15 | | | | 5891 | 43 | 43MB | direct path read (1) |
| 1 | p007 | Set 1 | 8 | 1.09 | 0.04 | 1.05 | | | 0.00 | 11637 | 85 | 85MB | direct path read (1) |
| 1 | p008 | Set 1 | 9 | 1.10 | 0.02 | 1.08 | | | | 3288 | 24 | 24MB | direct path read (1) |
....
....
| 1 | p08o | Set 1 | 313 | 1.12 | 0.02 | 1.10 | | | 0.01 | 2877 | 21 | 21MB | direct path read (1) |
| 1 | p08p | Set 1 | 314 | 1.08 | 0.02 | 0.66 | | | 0.40 | 2192 | 16 | 16MB | direct path read (1) |
| 1 | p08q | Set 1 | 315 | 1.06 | 0.01 | 0.62 | | | 0.42 | 1779 | 13 | 13MB | direct path read (1) |
| 1 | p08r | Set 1 | 316 | 1.14 | 0.03 | 0.61 | | | 0.51 | 4521 | 33 | 33MB | |
| 1 | p08s | Set 1 | 317 | 1.16 | 0.01 | 0.80 | | | 0.35 | 1370 | 10 | 10MB | direct path read (1) |
| 1 | p08t | Set 1 | 318 | 1.08 | 0.01 | 1.06 | | | | 1781 | 13 | 13MB | direct path read (1) |
| 1 | p08u | Set 1 | 319 | 1.20 | 0.01 | 0.84 | | | 0.35 | 1644 | 12 | 12MB | direct path read (1) |
| 1 | p08v | Set 1 | 320 | 1.06 | 0.01 | 0.82 | | | 0.24 | 1370 | 10 | 10MB | direct path read (1) |
---> up to 320 PX sessions per inst#1
| 2 | p000 | Set 1 | 321 | 1.09 | 0.02 | 1.05 | | | 0.01 | 4247 | 31 | 31MB | direct path read (1) |
| 2 | p001 | Set 1 | 322 | 1.11 | 0.02 | 1.09 | | | | 4102 | 30 | 30MB | direct path read (1) |
| 2 | p002 | Set 1 | 323 | 1.06 | 0.02 | 1.03 | | | 0.02 | 3562 | 26 | 26MB | direct path read (1) |
| 2 | p003 | Set 1 | 324 | 1.17 | 0.03 | 1.08 | | | 0.06 | 7535 | 55 | 55MB | direct path read (1) |
| 2 | p004 | Set 1 | 325 | 1.08 | 0.01 | 0.90 | | | 0.17 | 2055 | 15 | 15MB | direct path read (1) |
| 2 | p005 | Set 1 | 326 | 1.15 | 0.02 | 1.04 | | | 0.08 | 3973 | 29 | 29MB | direct path read (1) |
...
...
| 2 | p08p | Set 1 | 634 | 1.17 | 0.02 | 1.07 | | | 0.08 | 4521 | 33 | 33MB | direct path read (1) |
| 2 | p08q | Set 1 | 635 | 1.14 | 0.01 | 0.61 | | | 0.52 | 1781 | 13 | 13MB | direct path read (1) |
| 2 | p08r | Set 1 | 636 | 1.19 | 0.01 | 0.78 | | | 0.40 | 1370 | 10 | 10MB | |
| 2 | p08s | Set 1 | 637 | 1.07 | 0.02 | 0.92 | | | 0.13 | 3284 | 24 | 24MB | direct path read (1) |
| 2 | p08t | Set 1 | 638 | 1.10 | 0.01 | 1.03 | | | 0.05 | 2055 | 15 | 15MB | direct path read (1) |
| 2 | p08u | Set 1 | 639 | 1.17 | 0.03 | 0.64 | | | 0.50 | 4654 | 34 | 34MB | |
| 2 | p08v | Set 1 | 640 | 1.13 | 0.03 | 1.05 | | | 0.06 | 5206 | 38 | 38MB | direct path read (1) |
---> up to 320 PX sessions per inst#2
====================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1654899929)
===============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
===============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 2 | +1 | 641 | 1001 | | | | |
| 1 | PX COORDINATOR | | | | 2 | +1 | 641 | 1001 | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1001 | 314 | 1 | +2 | 640 | 1001 | | | | |
| 3 | PX BLOCK ITERATOR | | 1001 | 314 | 1 | +2 | 640 | 1001 | | | | |
| 4 | TABLE ACCESS STORAGE FULL | TEST01 | 1001 | 314 | 2 | +1 | 13038 | 1001 | 13046 | 13GB | 100.00 | Cpu (149) |
| | | | | | | | | | | | | direct path read (502) |
===============================================================================================================================================================I hope you’ll find it useful.
Leave a Reply