OraFAQ Forum: Performance Tuning » Similar SQLs : Index Usage/Non-usage

Home » RDBMS Server » Performance Tuning » Similar SQLs : Index Usage/Non-usage

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wed, 21 November 2007 01:20

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

Wed, 21 November 2007 01:34

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Unreadable.

/forum/fa/1707/0/

Regards
Michel

Report message to a moderator

Wed, 21 November 2007 02:05

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

Report message to a moderator

Wed, 21 November 2007 03:53

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.

Report message to a moderator

Wed, 21 November 2007 04:01

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

Wed, 21 November 2007 04:17

Michel Cadot
Messages: 68665
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

Report message to a moderator

Wed, 21 November 2007 04:39

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.

Report message to a moderator

Wed, 21 November 2007 04:43

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%')

Report message to a moderator

Wed, 21 November 2007 04:45

Michel Cadot
Messages: 68665
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

Report message to a moderator

Wed, 21 November 2007 20:59

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

Report message to a moderator

Thu, 22 November 2007 06:02

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.

Report message to a moderator

Thu, 22 November 2007 20:54

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

Report message to a moderator

Previous Topic:	Another question related to Statspack
Next Topic:	SHARED POOL contents

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 11:21:42 CDT 2024