topic Re: ST05 Index usage in Execution plan for SQL statement in Application Development and Automation Discussions

ST05 Index usage in Execution plan for SQL statement

Former Member — Fri, 26 Aug 2011 06:38:35 GMT

Observing performance problem in standrd transaction V.26 ( RVAUFSTA ). When tried to analyze the trace observed that for following select statement

SELECT OBJNR FROM JEST APPENDING CORRESPONDING FIELDS OF TABLE ONR_TAB

WHERE OBJNR IN R_S_OBJNR AND

INACT EQ SPACE AND

STAT EQ ISTAT_SAVE ORDER BY OBJNR.

the table should use primary keys OBJNR and STAT but as per trace execution plan its using standard index I created on fields

STAT

INACT

Whats the reason for this behavior. Would like to understand this.

Thanks

Re: ST05 Index usage in Execution plan for SQL statement

yuri_ziryukin — Fri, 26 Aug 2011 09:36:26 GMT

Hello.

two possible reasons come to my mind.

1. The range table for the OBJNR is empty.

2. The DB statistics for JEST table is so outdated / modified by someone, that the system either thinks that OBJNR is not selective enough or thinks that the field STAT is very selective (and normally it is not).

For point 2 please check the statistics in DB20 transaction.

Regards,

Yuri

Re: ST05 Index usage in Execution plan for SQL statement

HermannGahm — Fri, 26 Aug 2011 19:17:50 GMT

Hi Anya,

there are many reasons for this. database dependent ones and db independent ones.

Maybe the index you created has no statistics at all (in such cases the optimizer usually picks it up).

Or the clustering factors are extreme... or the parametrization is wrong, or or or or....

As a start you should give us more details (the statement as it is sent to the db, the db manufacturer and version, execution plan, statistics on tables and indexes, ... )

Kind regards,

Hermann

Re: ST05 Index usage in Execution plan for SQL statement

Former Member — Mon, 29 Aug 2011 07:16:12 GMT

Hi Hermann,

As suggested details were checked before posting query.

1. Range table for the OBJNR is empty. - Not empty

2. DB statistics for JEST table - updated

execution plan is

SELECT STATEMENT ( Estimated Costs = 100,582 , Estimated #Rows = 992,324 )

5 3 SORT ORDER BY

( Estim. Costs = 100,582 , Estim. #Rows = 992,324 )

Estim. CPU-Costs = 6,031,822,014 Estim. IO-Costs = 100,092

5 2 TABLE ACCESS BY INDEX ROWID JEST

( Estim. Costs = 93,153 , Estim. #Rows = 992,324 )

Estim. CPU-Costs = 4,994,233,044 Estim. IO-Costs = 92,748

Filter Predicates

1 INDEX RANGE SCAN JEST~I

( Estim. Costs = 4,334 , Estim. #Rows = 4,481,172 )

Search Columns: 3

Estim. CPU-Costs = 202,028,361 Estim. IO-Costs = 4,317

DB system is ORACLE Release 11.2.0.2.0. Index I is standard apart from this one customized index also exist on

OBJNR

STAT

INACT

CHGNR

Thanks

anya

Re: ST05 Index usage in Execution plan for SQL statement

HermannGahm — Mon, 29 Aug 2011 13:17:46 GMT

Hi,

Please send the statistics of the indexes and the sql statement (as it is sent to the DB from ST05) as well.

Kind regards,

Hermann

Re: ST05 Index usage in Execution plan for SQL statement

Former Member — Mon, 29 Aug 2011 14:00:51 GMT

Hi Herman,

SQL statement is

SELECT

"OBJNR"

FROM

"JEST"

WHERE

"MANDT" = :A0 AND ( "OBJNR" BETWEEN :A1 AND :A2 OR "OBJNR" BETWEEN :A3 AND :A4 OR "OBJNR" BETWEEN

:A5 AND :A6 OR "OBJNR" BETWEEN :A7 AND :A8 OR "OBJNR" BETWEEN :A9 AND :A10 OR "OBJNR" BETWEEN

:A11 AND :A12 OR "OBJNR" BETWEEN :A13 AND :A14 OR "OBJNR" BETWEEN :A15 AND :A16 OR "OBJNR"

BETWEEN :A17 AND :A18 OR "OBJNR" BETWEEN :A19 AND :A20 OR "OBJNR" BETWEEN :A21 AND :A22 OR

"OBJNR" BETWEEN :A23 AND :A24 OR "OBJNR" BETWEEN :A25 AND :A26 OR "OBJNR" BETWEEN :A27 AND :A28

OR "OBJNR" BETWEEN :A29 AND :A30 OR "OBJNR" BETWEEN :A31 AND :A32 OR "OBJNR" BETWEEN :A33 AND

:A34 OR "OBJNR" BETWEEN :A35 AND :A36 OR "OBJNR" BETWEEN :A37 AND :A38 OR "OBJNR" BETWEEN :A39

AND :A40 OR "OBJNR" BETWEEN :A41 AND :A42 OR "OBJNR" BETWEEN :A43 AND :A44 OR "OBJNR" BETWEEN

:A45 AND :A46 OR "OBJNR" BETWEEN :A47 AND :A48 OR "OBJNR" BETWEEN :A49 AND :A50 OR "OBJNR"

BETWEEN :A51 AND :A52 OR "OBJNR" BETWEEN :A53 AND :A54 OR "OBJNR" BETWEEN :A55 AND :A56 OR

"OBJNR" BETWEEN :A57 AND :A58 OR "OBJNR" BETWEEN :A59 AND :A60 OR "OBJNR" BETWEEN :A61 AND :A62

OR "OBJNR" BETWEEN :A63 AND :A64 OR "OBJNR" BETWEEN :A65 AND :A66 OR "OBJNR" BETWEEN :A67 AND

:A68 OR "OBJNR" BETWEEN :A69 AND :A70 OR "OBJNR" BETWEEN :A71 AND :A72 OR "OBJNR" BETWEEN :A73

AND :A74 OR "OBJNR" BETWEEN :A75 AND :A76 OR "OBJNR" BETWEEN :A77 AND :A78 OR "OBJNR" BETWEEN

:A79 AND :A80 OR "OBJNR" BETWEEN :A81 AND :A82 OR "OBJNR" BETWEEN :A83 AND :A84 OR "OBJNR"

BETWEEN :A85 AND :A86 OR "OBJNR" BETWEEN :A87 AND :A88 OR "OBJNR" BETWEEN :A89 AND :A90 OR

"OBJNR" BETWEEN :A91 AND :A92 OR "OBJNR" BETWEEN :A93 AND :A94 OR "OBJNR" BETWEEN :A95 AND :A96

OR "OBJNR" BETWEEN :A97 AND :A98 OR "OBJNR" BETWEEN :A99 AND :A100 OR "OBJNR" BETWEEN :A101 AND

:A102 OR "OBJNR" BETWEEN :A103 AND :A104 OR "OBJNR" BETWEEN :A105 AND :A106 OR "OBJNR" BETWEEN

:A107 AND :A108 OR "OBJNR" BETWEEN :A109 AND :A110 OR "OBJNR" BETWEEN :A111 AND :A112 OR "OBJNR"

BETWEEN :A113 AND :A114 OR "OBJNR" BETWEEN :A115 AND :A116 OR "OBJNR" BETWEEN :A117 AND :A118 OR

"OBJNR" BETWEEN :A119 AND :A120 OR "OBJNR" BETWEEN :A121 AND :A122 OR "OBJNR" BETWEEN :A123 AND

:A124 OR "OBJNR" BETWEEN :A125 AND :A126 OR "OBJNR" BETWEEN :A127 AND :A128 OR "OBJNR" BETWEEN

:A129 AND :A130 OR "OBJNR" BETWEEN :A131 AND :A132 OR "OBJNR" BETWEEN :A133 AND :A134 OR "OBJNR"

BETWEEN :A135 AND :A136 OR "OBJNR" BETWEEN :A137 AND :A138 OR "OBJNR" BETWEEN :A139 AND :A140 OR

"OBJNR" BETWEEN :A141 AND :A142 OR "OBJNR" BETWEEN :A143 AND :A144 OR "OBJNR" BETWEEN :A145 AND

:A146 OR "OBJNR" BETWEEN :A147 AND :A148 OR "OBJNR" BETWEEN :A149 AND :A150 OR "OBJNR" BETWEEN

:A151 AND :A152 OR "OBJNR" BETWEEN :A153 AND :A154 OR "OBJNR" BETWEEN :A155 AND :A156 OR "OBJNR"

BETWEEN :A157 AND :A158 OR "OBJNR" BETWEEN :A159 AND :A160 OR "OBJNR" BETWEEN :A161 AND :A162 OR

"OBJNR" BETWEEN :A163 AND :A164 OR "OBJNR" BETWEEN :A165 AND :A166 OR "OBJNR" BETWEEN :A167 AND

:A168 OR "OBJNR" BETWEEN :A169 AND :A170 OR "OBJNR" BETWEEN :A171 AND :A172 OR "OBJNR" BETWEEN :A173 AND :A174 OR "OBJNR" BETWEEN :A175 AND :A176 OR "OBJNR" BETWEEN :A177 AND :A178 OR "OBJNR"

BETWEEN :A179 AND :A180 OR "OBJNR" BETWEEN :A181 AND :A182 OR "OBJNR" BETWEEN :A183 AND :A184 OR

"OBJNR" BETWEEN :A185 AND :A186 OR "OBJNR" BETWEEN :A187 AND :A188 OR "OBJNR" BETWEEN :A189 AND

:A190 OR "OBJNR" BETWEEN :A191 AND :A192 OR "OBJNR" BETWEEN :A193 AND :A194 OR "OBJNR" BETWEEN

:A195 AND :A196 OR "OBJNR" BETWEEN :A197 AND :A198 OR "OBJNR" BETWEEN :A199 AND :A200 ) AND

"INACT" = :A201 AND "STAT" = :A202

ORDER BY

"OBJNR"

Index statistics

NONUNIQUE Index JEST~I

Column Name #Distinct

MANDT 1

STAT 201

INACT 2

Last statistics date 08/12/2011 08:47

Analyze Method Sample 2,582,101 Rows

Branch levels of B-Tree 3

Number of leaf blocks 4,338,020

Number of rows 860,700,333

Number of distinct keys 201

Re: ST05 Index usage in Execution plan for SQL statement

HermannGahm — Mon, 29 Aug 2011 14:08:05 GMT

hm...

first i would try with only a few (1 or 2) OR concatenations and check if the index is picked up then.

with many or concatenations optimizers can struggle quite often. Besides that make sure your db parameters

are set to the latest recommendations. small changes here often can cause trouble in such cases.

if the optimizer picks up the index with few or concatenations and if the parameters are up to date

i would open a message ...

Re: ST05 Index usage in Execution plan for SQL statement

volker_borowski2 — Tue, 30 Aug 2011 20:52:28 GMT

Hi,

if you already have an index in place, which you consider to be better, I'd first verify that it really is the better one.

Get yourself an sqlplus capable version of your query.

Substitute the list of selected fields with count(*) if you expect a high number of records (> 100).

execute

set linesize 150 pagesize 200 autotrace on

in sqlplus first, then run your query

and then run your query again, "hint"ing it to the desired index.

Verify both autotrace results.

It is very easy to verify, which one is better (execution time, number of GETS),

but it is not alway obvious, why the optimizer might decide against the "better" plan.

Volker