A two-part question about the FILTER operation appeared on the Oracle-L list server a couple of days ago. The first part was a fairly common question – one that’s often prompted by the way the optimizer used to behave in older versions of Oracle. Paraphrased, it was: “Why is the total cost of the query so high compared to the sum of its parts?”
Here’s the query, and the execution plan.
INSERT INTO TEMP
SELECT DISTINCT 'id',
PHT.emplid
FROM PHOTO PHT
WHERE 1 =1
AND PHT.PHOTO IS NOT NULL
AND NOT EXISTS
(SELECT 'x'
FROM TEMP TMP
WHERE PHT.EMPLID=TMP.EMPLID_SRCH
AND TMP.OPRID = 'id'
)
;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 21210 | 3334K| 5802K (2)| 00:03:47 |
| 1 | LOAD TABLE CONVENTIONAL | TEMP | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | PHOTO | 21211 | 3334K| 313 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEMP | 1 | 17 | 380 (2)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT 0 FROM " TEMP" "TMP" WHERE
"TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id'))
3 - filter("PHT"."PHOTO" IS NOT NULL)
4 - filter("TMP"."EMPLID_SRCH"=:B1 AND "TMP"."OPRID"='id')
Note that the “not exists” subquery against temp runs as a filter subquery with a cost of 380 for the tablescan. Combine that with the cost of 313 for the driving tablescan of photo and you might wonder why the resulting cost isn’t something like 693 – and in some old versions of Oracle that’s probably how it would be reported.
Historically the optimizer has been very bad about producing a final cost when queries have included subqueries – whether as filter subqueries in the predicate section or as scalar subqueries in the select list. Sometimes the cost would simply vanish from the final cost, sometimes it would be added just once to the final cost regardless of how many times the subquery might actually execute.
In this example the subquery against temp is a correlated subquery and might have to run once for every row in photo where the column photo was not null. At best it would have to run at least once for every distinct value of the photo.emplid column (the correlation column) found in those rows. In recent versions of Oracle the optimizer has tried to introduce some estimate of how many times the subquery would run as part of its calculation of the total cost. So (to a crude approximation) 5802K = 313 + N * 380. Unfortunately if we try to work backwards to N we find it would be about 15,267 which is about 72% of the 21,200 rows estimated as the result of the tablescan of photo – I haven’t tried to investigate the algorithms yet but presumably the optimizer makes some allowances somewhere for “self caching” as the subquery runs.
The more interesting part of the question came when the OP decided to test the effect of getting rid of the subquery. Check the costs in the resulting plan:
INSERT INTO TEMP
SELECT DISTINCT 'id',
PHT.emplid
FROM PHOTO PHT
WHERE 1 =1
AND PHT.UC_PBI_PHOTO IS NOT NULL;
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 21211 | 3334K| 3659 (1)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | TEMP | | | | |
|* 2 | TABLE ACCESS FULL | PHOTO | 21211 | 3334K| 3659 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PHT"."PHOTO" IS NOT NULL)
Note how the cost of the tablescan of photo has gone up from 313 in the previous query to 3,659 in the simpler query! How can a tablescan that drives a subquery have a lower cost than the tablescan on its own? Bear in mind that in both cases the Cost attributed to the operation “Table Access Full” is purely about scanning the rows in the photo table and is (or should be) entirely disconnected from the cost and frequency of the subquery.
The clue is in the table definition. The column photo.photo is a BLOB.
Models
I think there are potentially two errors in the optimizer displayed by this example. The first is that it’s adding in a cost that it shouldn’t even be considering; the second is that it’s being inconsistent in the way that it’s deriving that cost.
To demonstrate what I think is happening, I built a variant of the OP’s example as follows:
rem
rem Script: optimizer_lob_costs.sql
rem Author: Jonathan Lewis
rem Dated: May 2018
rem Purpose:
rem
rem Last tested
rem 12.2.0.1
rem 12.1.0.2
rem
create table photo (
emplid varchar2(11) not null,
photo clob,
other_col varchar2(1000)
)
lob (photo)
store as
photo_lob(
disable storage in row
cache
logging
)
;
create unique index ph_uk on photo(emplid);
insert /*+ append */ into photo
select
lpad(2 * rownum,10,0),
rpad('x',1000),
rpad('x',1000)
from
all_objects
where
rownum <= 10000 -- > comment to avoid wordpress format issue
;
commit;
create table temp(
oprid varchar2(30),
emplid_srch varchar2(11)
)
;
insert /*+ append */ into temp
select
'id',
lpad(2 * rownum,10,0)
from
all_objects
where
rownum <= 1000 -- > comment to avoid wordpress format issue
;
commit;
execute dbms_stats.gather_table_stats(user,'photo',method_opt=>'for all columns size 1', cascade=>true)
execute dbms_stats.gather_table_stats(user,'temp', method_opt=>'for all columns size 1', cascade=>true)
I’ve changed the BLOB to a CLOB defined with storage in row disabled, and I’ve introduced a varchar2() column of the same size as the CLOB column. I’ve declared the correlating column not null and created a unique index on it. Here are the two queries I want to review – slightly simplified versions of the original:
explain plan for
insert into temp(emplid_srch)
select
distinct pht.emplid
from
photo pht
where
1 = 1
and pht.photo is not null
-- and pht.other_col is not null
and not exists (
select /*+ no_unnest */
null
from
temp tmp
where
pht.emplid=tmp.emplid_srch
)
;
select * from table(dbms_xplan.display);
explain plan for
insert into temp(emplid_srch)
select
distinct pht.emplid
from photo pht
where 1 =1
and pht.photo is not null
-- and pht.other_col is not nulL
;
select * from table(dbms_xplan.display);
As you can see I’ve had to include a /*+ no_unnest */ hint in my SQL to get the FILTER operation to appear in the plan (the OP had the hidden parameter “_unnest_subquery” set to false); I’ve also allowed for two variants of each query, one referencing the CLOB column the other referencing the varchar2() column. The only results I’ll show are for the queries accessing the CLOB, and here are the plans first with, then without, the subquery. Check the cost of the tablescan of the photo table in the two cases:
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 9999 | 956K| 10458 (3)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | TEMP | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS FULL | PHOTO | 10000 | 957K| 216 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEMP | 1 | 11 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "TEMP" "TMP"
WHERE "TMP"."EMPLID_SRCH"=:B1))
3 - filter("PHT"."PHOTO" IS NOT NULL)
4 - filter("TMP"."EMPLID_SRCH"=:B1)
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 10000 | 957K| 285 (2)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | TEMP | | | | |
|* 2 | TABLE ACCESS FULL | PHOTO | 10000 | 957K| 285 (2)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PHT"."PHOTO" IS NOT NULL)
With the subquery in place the tablescan of photo reports a cost of 285, in the absence of the subquery it reports a cost of 216, a difference of 69. Repeating the test but using the varchar2() column the cost of the tablescan was 213 in both cases – suggesting that the variation was due to the column being a LOB.
With no further clues in the plan it looked like one of those rare occasions when I have to look at the 10053 (optimizer) trace file – and this is what I got from the 12.1.0.2 trace, looking at the section headed “SINGLE TABLE ACCESS PATH” for the photo table. First the base query without the subquery:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PHOTO[PHT]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Column (#2): PHOTO(LOB)
AvgLen: 87 NDV: 0 Nulls: 0 Density: 0.000000
Table: PHOTO Alias: PHT
Card: Original: 10000.000000 Rounded: 10000 Computed: 10000.000000 Non Adjusted: 10000.000000
Scan IO Cost (Disk) = 210.000000
Scan CPU Cost (Disk) = 13571440.480000
Total Scan IO Cost = 210.000000 (scan (Disk))
+ 70.000000 (io filter eval) (= 0.007000 (per row) * 10000.000000 (#rows))
= 280.000000
Total Scan CPU Cost = 13571440.480000 (scan (Disk))
+ 9138463.200000 (cpu filter eval) (= 913.846320 (per row) * 10000.000000 (#rows))
= 22709903.680000
Note the “Total Scan IO Cost” described at line 13 includes a component at line 12 labelled “(io filter eval)” – why, for the predicate “photo is null”, would we do any special I/O when that predicate can be met in the basic table scan.
(Note: A predicate like “lob_column is null” means there is no lob locator in place, so no lob access need be done for that test. In fact the related, but very different, predicate “length(lob_column) = 0” meaning the lob locator exists but the lob is “empty” could also be satisfied during the tablescan without reference to the physical lob segment(s) because the length of the lob is included in the lob locator.)
Let’s assume that the optimizer is incorrectly assuming the run-time engine will have to access the lob in some way to determine that the lob is null. The worst case scenario is that Oracle will start by accessing the LOBindex – so why don’t we check how big the LOBindex is. The first step I took was to check the object_id of the LOBindex and then do a tree dump (which showed 66 leaf blocks) and then I checked the segment header block and dumped that with the following results:
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x01400447 ext#: 0 blk#: 70 ext size: 127
#blocks in seg. hdr's freelists: 4
#blocks below: 70
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 194295 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x01400401 length: 127
See the “Highwater::” information at line 6 – the allocated space in the segment is the first 70 blocks of the first extent. That’s (almost certainly) where the incremental cost of 70 (single block I/Os) comes from. (And I did couple of big updates to the LOB, designed to expand the LOBindex without changing the segment size of the underlying table, to corroborate that hypothesis.)
This brings us to the question of why the cost of the tablescan drops when the subquery is included. Again we generate the 10053 trace and examine the details under the “SINGLE TABLE ACCESS PATH”:
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for PHOTO[PHT]
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
Table: PHOTO Alias: PHT
Card: Original: 10000.000000 Rounded: 10000 Computed: 10000.000000 Non Adjusted: 10000.000000
Scan IO Cost (Disk) = 210.000000
Scan CPU Cost (Disk) = 13571440.480000
Total Scan IO Cost = 210.000000 (scan (Disk))
+ 3.500000 (io filter eval) (= 0.000350 (per row) * 10000.000000 (#rows))
= 213.500000
Total Scan CPU Cost = 13571440.480000 (scan (Disk))
+ 656923.160000 (cpu filter eval) (= 65.692316 (per row) * 10000.000000 (#rows))
= 14228363.640000
In this case the “(io filter eval)” at line 10 is only 3.5 – and if you know your optimizer and how it handles subqueries you’re allowed to guess that could be one of Oracle’s standard guesses of 5% coming into play. (Again, growing the index seemed to corroborate this hypothesis.)
So here’s (possible) bug number 2: the first bug is adding a cost for accessing the LOBindex when there should be no need to access the index at all – the execution plan says we will get 10,000 rows from the table, the filter predicate does report a cardinality reduced by just 1 on a column that has been declared with a uniqueness constraint, but a fairly commonly used “guess factor” of 5% is used as an indicator of the number of times the lob predicate will be tested. The various bits of the arithmetic are not consistent with each other.
Summary notes:
If you have a tablescan with a predicate that references a lob column then the cost of the tablescan includes the cost of the lob access – and there are cases where lob access is not needed but still gets costed {this is bug number 1 – the predicates are column is/is not null, and length(column) = / != 0)}.
If the lob data itself does not need to be accessed then the size of the lob index – which you can’t easily find – may have a significant impact on the cost of the tablescan.
If the query also includes predicates that result in the optimizer guessing about cardinality effects (1%, 5%, 0.25% are common guesses) then that guess may be used to scale the assumed (and potentially irrelevant) cost of the lob access. (There is scope for further experimentation in this area to examine the effects of “non-guess” predicates and the assumed order of application of predicates, e.g. are lob predicates costed as the last to be applied, does the algorithm for costing matched the execution order.)
As often happens it’s easy to see that there are oddities in the arithmetic that affect the cost of a query in ways that might make the optimizer pick a silly execution plan. Unfortunately it’s not easy to predict when you’re likely to see the effects of these oddities; the best we can do is remember that there is an anomaly with costing lob-based predicates and hope that we think of it when we see the optimizer picking a bad plan for reasons that initially are not obvious.
