Home » RDBMS Server » Performance Tuning » Similar SQLs : Index Usage/Non-usage
Similar SQLs : Index Usage/Non-usage [message #282172] |
Wed, 21 November 2007 01:20 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
See the following explain plans. Both the SQLs are similar except the way one predicate has been structured.
When I use the predicate as SUBSTR(RECORD_KEY,1,2)='TP', it utilizing the combined index,
but when I use the predicate as RECORD_KEY like'TP%', it is not utilizing the index.
Please explain the reason.
The index is created as
create index i1 on x_TPD_STG_TL_CS_EXTRACTED_RECS (stg_update_date_time,record_key)
SQL> ed
Wrote file afiedt.buf
1 explain plan for
2 SELECT x_TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY, stg_update_date_time, x_TPD_STG_TL_CS_EXTRACT
3 FROM
4 x_TPD_STG_TL_CS_EXTRACTED_RECS
5 WHERE
6 LOAD_FLAG IN('I','U')
7 --AND RECORD_KEY like'TP%'
8 AND SUBSTR(RECORD_KEY,1,2)='TP'
9* AND stg_update_date_time between date '2007-11-20' - interval '12' hour and date '2007-11-21'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2422139671
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 982 | 49100 | 750 (1)| 00:00:09 |
|* 1 | TABLE ACCESS BY INDEX ROWID| X_TPD_STG_TL_CS_EXTRACTED_RECS | 982 | 49100 | 750 (1)| 00
|* 2 | INDEX RANGE SCAN | I1 | 982 | | 389 (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("LOAD_FLAG"='I' OR "LOAD_FLAG"='U')
2 - access("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-19 12:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter(SUBSTR("RECORD_KEY",1,2)='TP')
=========================================================================================
SQL> explain plan for SELECT x_TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY, stg_update_date_time, x_TPD_
STG_TL_CS_EXTRACTED_RECS.PRODUCT_HOLDING_STATUS, x_TPD_STG_TL_CS_EXTRACTED_RECS.PRODUCT_HOLDING_REF_
NUMBER, x_TPD_STG_TL_CS_EXTRACTED_RECS.JOINT_LIFE_TYPE, x_TPD_STG_TL_CS_EXTRACTED_RECS.OUT_OF_FORCE_
DATE, x_TPD_STG_TL_CS_EXTRACTED_RECS.OUT_OF_FORCE_REASON_CODE, x_TPD_STG_TL_CS_EXTRACTED_RECS.PARENT
_PRODUCT_HOLDING_REF_NUM, x_TPD_STG_TL_CS_EXTRACTED_RECS.TAX_JURISDICTION, x_TPD_STG_TL_CS_EXTRACTED
_RECS.JOINT_OWNER_INDICATOR, x_TPD_STG_TL_CS_EXTRACTED_RECS.DUE_END_DATE_OF_CONTRACT, x_TPD_STG_TL_C
S_EXTRACTED_RECS.Q_DATE_WITH_PROFIT_STATUS, x_TPD_STG_TL_CS_EXTRACTED_RECS.LATEST_WITH_PROFIT_STATUS
, x_TPD_STG_TL_CS_EXTRACTED_RECS.DATA_SOURCE, x_TPD_STG_TL_CS_EXTRACTED_RECS.SOURCE_EXTRACT_DATE_TIM
E, x_TPD_STG_TL_CS_EXTRACTED_RECS.PRODUCT_TYPE, x_TPD_STG_TL_CS_EXTRACTED_RECS.PRODUCT_DESCRIPTION,
x_TPD_STG_TL_CS_EXTRACTED_RECS.OCDB_REFERENCE_NUMBER, x_TPD_STG_TL_CS_EXTRACTED_RECS.NPSW_INDICATOR,
x_TPD_STG_TL_CS_EXTRACTED_RECS.DONOR_POLICY_INDICATOR, x_TPD_STG_TL_CS_EXTRACTED_RECS.A_DATE_WITH_P
ROFIT_STATUS
2 FROM
3 x_TPD_STG_TL_CS_EXTRACTED_RECS
4 WHERE
5 LOAD_FLAG IN('I','U')
6 AND RECORD_KEY like'TP%'
7 --AND SUBSTR(RECORD_KEY,1,2)='TP'
8 AND stg_update_date_time between date '2007-11-20' - interval '12' hour and date '2007-11-21'
9 /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1417266292
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39042 | 1906K| 4266 (6)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| X_TPD_STG_TL_CS_EXTRACTED_RECS | 39042 | 1906K| 4266 (6)| 00:00:52 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
1 - filter("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-19 12:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "RECORD_KEY" LIKE 'TP%' AND ("LOAD_FLAG"='I' OR "LOAD_FLAG"='U') AND
"STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
[Updated on: Wed, 21 November 2007 02:12] by Moderator Report message to a moderator
|
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282188 is a reply to message #282183] |
Wed, 21 November 2007 02:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
It's just the optimizer. It doesn't realise that the statements are semantically the same.
It cannot tell how many rows will match a SUBSTR() or LIKE based predicate, and it guesses different numbers for each. One of them makes an index scan look good, the other makes a full scan look good.
It's just bad luck.
If you want to force the use of an index you will have to supply a hint. Do a search on CARDINALITY HINT or INDEX HINT.
Ross Leishman
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282228 is a reply to message #282188] |
Wed, 21 November 2007 03:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
Thanks rleishman.
Does the above suggest that a full table scan is better than using an index in this example. And the CBO has done that precisely when we did not have wrapped our column with a function ?
Please clarify.
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282232 is a reply to message #282183] |
Wed, 21 November 2007 04:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
I tried using Hints in the above SQL. It has forced the CBO to utilize the index but I'm not very clear about comparing performance with/without the hints.
Actually I'm unable to comfortably infer the difference that the cost column shows. Has the performance gone down due to the usage of hint (in turn, the index).
Please explain.
SQL> ed
Wrote file afiedt.buf
1 explain plan for SELECT /*+ index(x_TPD_STG_TL_CS_EXTRACTED_RECS i1) */ x_TPD_STG_TL_CS_EXTRAC
2 FROM
3 x_TPD_STG_TL_CS_EXTRACTED_RECS
4 WHERE
5 LOAD_FLAG IN('I','U')
6 AND RECORD_KEY like'TP%'
7 --AND SUBSTR(RECORD_KEY,1,2)='TP'
8* AND stg_update_date_time between date '2007-11-20' - interval '12' hour and date '2007-11-21'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2422139671
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 39042 | 1906K| 14714 (1)| 00:02:57 |
|* 1 | TABLE ACCESS BY INDEX ROWID| X_TPD_STG_TL_CS_EXTRACTED_RECS | 39042 | 1906K| 14714 (1)| 0
|* 2 | INDEX RANGE SCAN | I1 | 39046 | | 388 (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("LOAD_FLAG"='I' OR "LOAD_FLAG"='U')
2 - access("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-19 12:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"RECORD_KEY" LIKE 'TP%' AND "STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-21 00:00:00', 'yyyy-mm-d
hh24:mi:ss'))
filter("RECORD_KEY" LIKE 'TP%')
[Edit: Hope OP always remember to add code tags in future posting.] Taj
[Updated on: Wed, 21 November 2007 06:35] by Moderator Report message to a moderator
|
|
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282251 is a reply to message #282232] |
Wed, 21 November 2007 04:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
I tried to be more selective of the record_key making it like 'TP4%' (the no. of rows selected comes down by almost 10%. Now it uses the index).
If so what is the threshold (at what point does CBO decides to go for index over full table scan )?
SQL> ed
Wrote file afiedt.buf
1 explain plan for SELECT x_TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY, stg_update_date_time, x_TPD_
2 FROM
3 x_TPD_STG_TL_CS_EXTRACTED_RECS
4 WHERE
5 LOAD_FLAG IN('I','U')
6 AND RECORD_KEY like 'TP4%'
7 --AND SUBSTR(RECORD_KEY,1,2)='TP'
8* AND stg_update_date_time between date '2007-11-20' - interval '12' hour and date '2007-11-21'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2422139671
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4252 | 207K| 1949 (1)| 00:00:24 |
|* 1 | TABLE ACCESS BY INDEX ROWID| X_TPD_STG_TL_CS_EXTRACTED_RECS | 4252 | 207K| 1949 (1)| 00:
|* 2 | INDEX RANGE SCAN | I1 | 4253 | | 388 (1)| 00:00:05 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("LOAD_FLAG"='I' OR "LOAD_FLAG"='U')
2 - access("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-19 12:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"RECORD_KEY" LIKE 'TP4%' AND "STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-21 00:00:00', 'yyyy-mm-
hh24:mi:ss'))
filter("RECORD_KEY" LIKE 'TP4%')
|
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282394 is a reply to message #282252] |
Wed, 21 November 2007 20:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
As you have discovered, ignore the COST figure, it is useless.
The one that runs faster is - generally speaking - the better plan. If CBO is choosing the wrong plan, you have to ask yourself why?
It could be - as Michel stipulates - a bug in the optimizer. But it could be that you are using complex predicates or combinations of predicates that make it difficult to estimate the number of rows returned. This is where CBO guesses.
If it guesses wrong then it's not a bug, it's just reached the limits of its capability. You can extend these limits by giving it more information. That's why I like the CARDINALITY hint. It gives the optimiser information that it does not currently have, but still allows it to work out the best plan.
Ross Leishman
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282554 is a reply to message #282394] |
Thu, 22 November 2007 06:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
hidnana
Messages: 87 Registered: November 2007 Location: chennai
|
Member |
|
|
Thanks rleishman.
Pls. see the below plans for the SQLs estimated for 100% sample (1 million rows).
Still the 1st plan uses index i1 even with the 'substr' whereas the 2nd plan doesn't use it despite using 'like %'.
Optimzer now does have 100% sample, isn't it ? Is it not enought for it to estimate correctly ?
Pls. explain.
Wrote file afiedt.buf
1 explain plan
2 for
3 SELECT x_TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY, stg_update_date_time, x_TPD_STG_TL_CS_EXTRACT
4 FROM
5 x_TPD_STG_TL_CS_EXTRACTED_RECS
6 WHERE
7 LOAD_FLAG IN('I','U')
8 --AND RECORD_KEY like 'TP%'
9 AND SUBSTR(RECORD_KEY,1,2)='TP'
10* AND stg_update_date_time between date '2007-11-21' - interval '12' hour and date '2007-11-22'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2422139671
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2000 | 97K| 1520 (1)| 00:00:19 |
|* 1 | TABLE ACCESS BY INDEX ROWID| X_TPD_STG_TL_CS_EXTRACTED_RECS | 2000 | 97K| 1520 (1)| 00:
|* 2 | INDEX RANGE SCAN | I1 | 2000 | | 788 (1)| 00:00:10 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - filter("LOAD_FLAG"='I' OR "LOAD_FLAG"='U')
2 - access("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-20 12:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
filter(SUBSTR("RECORD_KEY",1,2)='TP')
17 rows selected.
====================================================================================================
Wrote file afiedt.buf
1 explain plan for
2 SELECT x_TPD_STG_TL_CS_EXTRACTED_RECS.RECORD_KEY, stg_update_date_time, x_TPD_STG_TL_CS_EXTRACT
3 FROM
4 x_TPD_STG_TL_CS_EXTRACTED_RECS
5 WHERE
6 LOAD_FLAG IN('I','U')
7 AND RECORD_KEY like 'TP%'
8 --AND SUBSTR(RECORD_KEY,1,2)='TP'
9* AND stg_update_date_time between date '2007-11-21' - interval '12' hour and date '2007-11-22'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1417266292
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79521 | 3882K| 4270 (7)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| X_TPD_STG_TL_CS_EXTRACTED_RECS | 79521 | 3882K| 4270 (7)| 00:00:52 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
1 - filter("STG_UPDATE_DATE_TIME">=TO_DATE('2007-11-20 12:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "RECORD_KEY" LIKE 'TP%' AND ("LOAD_FLAG"='I' OR "LOAD_FLAG"='U') AND
"STG_UPDATE_DATE_TIME"<=TO_DATE('2007-11-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
15 rows selected.
|
|
|
Re: Similar SQLs : Index Usage/Non-usage [message #282646 is a reply to message #282554] |
Thu, 22 November 2007 20:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Statistics are not magic. They generally cannot tell you how many rows match an equals condition, let alone a LIKE condition or SUBSTR().
It doesn't matter how big the sample.
For the third (and final?) time, when you provide predicates like these, Oracle guesses. When it guesses wrong, you need hints.
Ross Leishman
|
|
|
Goto Forum:
Current Time: Mon Aug 26 10:04:51 CDT 2024
|