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
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 #282183 is a reply to message #282172] Wed, 21 November 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable.

./fa/1707/0/

Regards
Michel
Re: Similar SQLs : Index Usage/Non-usage [message #282188 is a reply to message #282183] Wed, 21 November 2007 02:05 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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 messageGo to next message
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 #282242 is a reply to message #282232] Wed, 21 November 2007 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@the moderator that has formated first post:

You can see the pointlessness of this, OP still don't want to format, does he worth the effort to help him?

@OP:

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Similar SQLs : Index Usage/Non-usage [message #282250 is a reply to message #282242] Wed, 21 November 2007 04:39 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Sorry. But I did a preview before posting it. It looked Ok and only then I posted it.
Next time I'll ensure that it is formatted befor posting.
Re: Similar SQLs : Index Usage/Non-usage [message #282251 is a reply to message #282232] Wed, 21 November 2007 04:43 Go to previous messageGo to next message
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 #282252 is a reply to message #282250] Wed, 21 November 2007 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Has the performance gone down due to the usage of hint (in turn, the index).

This is why Oracle does not choose to use it and this is why you should not hint.

Hints are only for experts who know how the optimizer works (or to workaround an optimizer bug).
Otherwise, let it work.

Regards
Michel
Re: Similar SQLs : Index Usage/Non-usage [message #282394 is a reply to message #282252] Wed, 21 November 2007 20:59 Go to previous messageGo to next message
rleishman
Messages: 3724
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 messageGo to next message
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
rleishman
Messages: 3724
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
Previous Topic: Another question related to Statspack
Next Topic: SHARED POOL contents
Goto Forum:
  


Current Time: Wed Dec 07 06:30:34 CST 2016

Total time taken to generate the page: 0.12272 seconds