RE: Function Based Index lower cost then FTS but performs worse
Date: Tue, 25 Aug 2015 07:52:43 -0400
Message-ID: <2aa901d0df2c$8e77df00$ab679d00$_at_rsiz.com>
In addition to the previous notes, indexes containing a column name fragment "STAT" beg to be considered for considering the "DONE" status value to be defined as null. Your programs may need to be aware of this change if made to the existing status, but:
An alternative that seems especially likely in your case, since you are already using a function based index, is changing the "DONE" status to null in the function or having a virtual column where "DONE" is converted to null and using the virtual column in the function definition.
For a multi-column index you'll need to convert the other column values to null as well IN THE FUNCTIONAL INDEX if you want all the "DONE" status values to disappear from the index. You'll want to code that up in a way the null of status converts to null other column before other functions on the column are made. I'm not sure whether it is easier to code up as virtual columns or in the index specification; my experiments on using either don't really differentiate a best solution on that for index efficiency (the big deal is all null values dropping from the index). Virtual columns do have the value of sometimes being useful quite apart from the particular functional index.
None of this is to do with the "likely bug" advice, which might also solve your problem. Is the "DESC" actually of high value in some context as you use your system?
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Tuesday, August 25, 2015 2:17 AM
To: oracle-l_at_freelists.org
Subject: RE: Function Based Index lower cost then FTS but performs worse
There are a couple of bugs that appear when you have indexes with descending
columns (possibly it's only indexes that START with descending) on a table.
There's an example here
http://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/
<https://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/> of how
the wrong index can influence the optimizer's strategy, but your example
looks more like the basis issue of how the optimizer does arithmetic with
the sys_op_descend() column - as described here:
https://jonathanlewis.wordpress.com/2015/07/17/descending-indexes/
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
behalf of Chris Grabowy [cgrabowy_at_gmail.com]
Sent: 24 August 2015 23:43
To: oracle-l_at_freelists.org
Subject: Function Based Index lower cost then FTS but performs worse
A developer identified some SQL that is having a performance issue on an Oracle 11.2.0.3 database.
I created an explain plan for the SQL.
############################################################################ ######################################################
SELECT count(*)
FROM CLAIMS fc
WHERE fc.CLAIM_PAYMENT_STATUS = '02'
AND fc.PLACE_OF_SERVICE IN ('11', '12', '24', '22')
AND fc.SERVICE_FROM_DATE BETWEEN '01-JUN-2013' AND '31-MAY-2014';
Execution Plan
Plan hash value: 689374613
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------
------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 5 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 14 | | | | | |* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| CLAIMS | 3668K| 48M| 5 (0)| 00:00:01 | ROWID | ROWID | |* 3 | INDEX RANGE SCAN | CLAIMS_STAT_ORIG_XBT | 1 | | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------
-------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter("FC"."SERVICE_FROM_DATE">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
("FC"."PLACE_OF_SERVICE"='11' OR "FC"."PLACE_OF_SERVICE"='12' OR "FC"."PLACE_OF_SERVICE"='22' OR "FC"."PLACE_OF_SERVICE"='24') AND "FC"."SERVICE_FROM_DATE"<=TO_DATE(' 2014-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access(SYS_OP_DESCEND("CLAIM_PAYMENT_STATUS")=HEXTORAW('CFCDFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CLAIM_PAYMENT_STATUS"))='02')
############################################################################ ######################################################
I created an explain plan for the SQL with a full table scan hint.
############################################################################ ######################################################
SELECT /*+ FULL(fc) */ count(*)
FROM CLAIMS fc
WHERE fc.CLAIM_PAYMENT_STATUS = '02'
AND fc.PLACE_OF_SERVICE IN ('11', '12', '24', '22')
AND fc.SERVICE_FROM_DATE BETWEEN '01-JUN-2013' AND '31-MAY-2014';
Execution Plan
Plan hash value: 321060423
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------
-------------------------- | 0 | SELECT STATEMENT | | 1 | 14 | 2762K (1)| 09:12:34 | | | | 1 | SORT AGGREGATE | | 1 | 14 | | | | | | 2 | PARTITION RANGE ALL| | 3668K| 48M| 2762K (1)| 09:12:34 | 1 | 42 | |* 3 | TABLE ACCESS FULL | CLAIMS | 3668K| 48M| 2762K (1)| 09:12:34 | 1 | 42 |
----------------------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
3 - filter("FC"."SERVICE_FROM_DATE">=TO_DATE(' 2013-06-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "FC"."CLAIM_PAYMENT_STATUS"='02' AND ("FC"."PLACE_OF_SERVICE"='11' OR "FC"."PLACE_OF_SERVICE"='12' OR "FC"."PLACE_OF_SERVICE"='22' OR "FC"."PLACE_OF_SERVICE"='24') AND "FC"."SERVICE_FROM_DATE"<=TO_DATE(' 2014-05-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
############################################################################ ######################################################
The cost for using the index seems to be pretty low (5) while for the FTS its 2.7M.
The index is a function based index.
CREATE INDEX TEST.CLAIMS_STAT_ORIG_XBT ON TEST.CLAIMS (CLAIM_PAYMENT_STATUS DESC, ORIGINAL_CLAIM_ID, SUBSTR(CLAIM_ID,1,LENGTH(CLAIM_ID)-2)) PARALLEL 4 TABLESPACE DWHPART2 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 64K BUFFER_POOL DEFAULT); The query completes with a FTS but does not seem to complete with the index.
When I make the index invisible then the optimizer chooses the FTS.
I did a 10053 and I believe these are the stats for FTS
Access Path: TableScan
Cost: 2762793.85 Resp: 2762793.85 Degree: 0
Cost_io: 2751436.00 Cost_cpu: 281804015238
Resp_io: 2751436.00 Resp_cpu: 281804015238
And these are the stats for the index. Not sure what the logdef part is about?
Access Path: index (RangeScan)
Index: CLAIMS_STAT_ORIG_XBT
resc_io: 5.00 resc_cpu: 37109
ix_sel: 0.000000 ix_sel_with_filters: 0.000000
- Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
- End Logdef Adjustment ******
Cost: 5.00 Resp: 5.00 Degree: 1
The table is about 150 million rows and is partitioned.
The function based index SYS named columns have statistics
I'm scratching my head trying to figure out what I am missing that makes the optimizer favor the FBI over the FTS????
Should I dig into histograms for the FBI's sys named columns? And then that might help fix the cost?
TIA for any ideas.
Chris
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 25 2015 - 13:52:43 CEST