Why is this VELO search slower with TREX on? SQL looks more efficient...?

[Former Member]

Hi all

We are running several vehicle searches in transaction VELO with TREX enabled. In most cases, TREX seems to correctly operate and the search is quicker than with TREX disabled, as expected.

However, in one scenario, the search with TREX enabled is significantly slower.

The criteria for this search is:

Velo Filter: Vehicle Status

Availability Data: IV

Production Date: 06.01.2014 (from and to)

We have traced the search and have seen the following SQL running:

SELECT

  "MANDT", "VGUID", "VHCLE", "MATNR", "LIFNR", "CHARG", "EQUNR", "WERKS", "LGORT", "BWTAR",

  "CUOBJ", "CUABA", "MMCTR", "MMSTA", "MMTSP", "SDCTR", "SDSTA", "SDTSP", "KUNNR", "ENDCU",

  "VHVIN", "VHCEX", "AVAIL", "VBLTY", "LOCTN", "GPRICE", "GPRICECUKY", "CDTSP", "PSTSP", "PDTSP",

  "ERNAM", "VHUSG", "CMPGN", "PCOUNT", "PCOUNT_U", "IMMATDATE", "VHSAR", "VHORD", "SHLVL",

  "ARCHIVE_FLAG", "USED_VEHICLE"

FROM

  "VLCVEHICLE"

WHERE

  "MANDT"=:A0 AND "PDTSP" BETWEEN :A1 AND :A2 AND "VGUID" IN (:A3 ,:A4 ,:A5 ,:A6 ,:A7 ) AND "MATNR"

  IN (:A8 ,:A9 ,:A10 ,:A11 ,:A12 ,:A13 ,:A14 ,:A15 ,:A16 ,:A17 ,:A18 ,:A19 ,:A20 ,:A21 ,:A22 ,:A23

  ,:A24 ,:A25 ,:A26 ,:A27 ,:A28 ,:A29 ,:A30 ,:A31 ,:A32 ,:A33 ,:A34 ,:A35 ,:A36 ,:A37 ,:A38 ,:A39

  ,:A40 ,:A41 ,:A42 ,:A43 ,:A44 ,:A45 ,:A46 ,:A47 ,:A48 ,:A49 ,:A50 ,:A51 ,:A52 ,:A53 ,:A54 ,:A55

  ,:A56 ,:A57 ,:A58 ,:A59 ,:A60 ,:A61 ,:A62 ,:A63 ,:A64 ,:A65 ,:A66 ,:A67 ,:A68 ,:A69 ,:A70 ,:A71

  ,:A72 ,:A73 ,:A74 ,:A75 ,:A76 ,:A77 ,:A78 ,:A79 ,:A80 ,:A81 ,:A82 ,:A83 ,:A84 ,:A85 ,:A86 ,:A87

  ,:A88 ,:A89 ,:A90 ,:A91 ,:A92 ,:A93 ,:A94 ,:A95 ,:A96 ,:A97 ,:A98 ,:A99 ,:A100 ,:A101 ,:A102

  ,:A103 ,:A104 ,:A105 ,:A106 ,:A107 ,:A108 ,:A109 ,:A110 ,:A111 ,:A112 ,:A113 ,:A114 ,:A115 ,:A116

  ,:A117 ,:A118 ,:A119 ,:A120 ,:A121 ,:A122 ,:A123 ,:A124 ,:A125 ,:A126 ,:A127 ,:A128 ,:A129 ,:A130

  ,:A131 ,:A132 ,:A133 ,:A134 ,:A135 ,:A136 ,:A137 ,:A138 ,:A139 ,:A140 ,:A141 ,:A142 ,:A143 ,:A144

  ,:A145 ,:A146 ,:A147 ,:A148 ,:A149 ,:A150 ,:A151 ,:A152 ,:A153 ,:A154 ,:A155 ,:A156 ,:A157 ,:A158

  ,:A159 ,:A160 ,:A161 ,:A162 ,:A163 ,:A164 ,:A165 ,:A166 ,:A167 ,:A168 ,:A169 ,:A170 ,:A171 ,:A172

  ,:A173 ,:A174 ,:A175 ,:A176 ,:A177 ,:A178 ,:A179 ,:A180 ,:A181 ,:A182 ,:A183 ,:A184 ,:A185 ,:A186

  ,:A187 ,:A188 ,:A189 ,:A190 ,:A191 ,:A192 ,:A193 ,:A194 ,:A195 ,:A196 ,:A197 ,:A198 ,:A199 ,:A200

  ,:A201 ,:A202 ,:A203 ,:A204 ,:A205 ,:A206 ,:A207 ,:A208 ,:A209 ,:A210 ,:A211 ,:A212 ,:A213 ,:A214

  ,:A215 ,:A216 ,:A217 ,:A218 ,:A219 ,:A220 ,:A221 ,:A222 ,:A223 ,:A224 ,:A225 ,:A226 ,:A227 ,:A228

  ,:A229 ,:A230 ,:A231 ,:A232 ,:A233 ,:A234 ,:A235 ,:A236 ,:A237 ,:A238 ,:A239 ,:A240 ,:A241 ,:A242

  ,:A243 ,:A244 ,:A245 ,:A246 ,:A247 ,:A248 ,:A249 ,:A250 ,:A251 ,:A252 ,:A253 ,:A254 ,:A255 ,:A256

  ,:A257 ,:A258 ,:A259 ,:A260 ,:A261 ,:A262 ,:A263 ,:A264 ,:A265 ,:A266 ,:A267 ,:A268 ,:A269 ,:A270

  ,:A271 ,:A272 ,:A273 ,:A274 ,:A275 ,:A276 ,:A277 ,:A278 ,:A279 ,:A280 ,:A281 ,:A282 ,:A283 ,:A284

  ,:A285 ,:A286 ,:A287 ,:A288 ,:A289 ,:A290 ,:A291 ,:A292 ,:A293 ,:A294 ,:A295 ,:A296 ,:A297 ,:A298

  ,:A299 ,:A300 ,:A301 ,:A302 ,:A303 ,:A304 ,:A305 ,:A306 ,:A307 ,:A308 ,:A309 ,:A310 ,:A311 ,:A312

  ,:A313 ,:A314 ,:A315 ,:A316 ,:A317 ,:A318 ,:A319 ,:A320 ,:A321 ,:A322 ,:A323 ,:A324 ,:A325 ,:A326

  ,:A327 ,:A328 ,:A329 ,:A330 ,:A331 ,:A332 ,:A333 ,:A334 ,:A335 ,:A336 ,:A337 ,:A338 ,:A339 ,:A340

  ,:A341 ,:A342 ,:A343 ,:A344 ,:A345 ,:A346 ,:A347 ,:A348 ,:A349 ,:A350 ,:A351 ,:A352 ,:A353 ,:A354

  ,:A355 ,:A356 ,:A357 ,:A358 ,:A359 ,:A360 ,:A361 ,:A362 ,:A363 ,:A364 ,:A365 ,:A366 ,:A367 ,:A368

  ,:A369 ,:A370 ,:A371 ,:A372 ,:A373 ,:A374 ,:A375 ,:A376 ,:A377 ,:A378 ,:A379 ,:A380 ,:A381 ,:A382

  ,:A383 ,:A384 ,:A385 ,:A386 ,:A387 ,:A388 ,:A389 ,:A390 ,:A391 ,:A392 ,:A393 ,:A394 ,:A395 ,:A396

  ,:A397 ,:A398 ,:A399 ,:A400 ,:A401 ,:A402 ,:A403 ,:A404 ,:A405 ,:A406 ,:A407 )

The ABAP trace shows 'Select VLCVEHICLE' as taking 369 seconds from a gross time of 444 seconds. This is an exceptionally long time for a query that would appear to be efficient (at least, according to the SQL execution plan) - it is searching on VGUID which it has obtained via TREX (but why does it still query PDTSP? Surely if TREX has returned the relevant VGUIDs then it doesn't need this - could this be the cause of the performance  issue?).

With TREX disabled, the SQL captured is like so:

SELECT "MANDT", "VGUID", "VHCLE", "MATNR", "LIFNR", "CHARG", "EQUNR", "WERKS", "LGORT", "BWTAR", "CUOBJ",

"CUABA", "MMCTR", "MMSTA", "MMTSP", "SDCTR", "SDSTA", "SDTSP", "KUNNR", "ENDCU", "VHVIN", "VHCEX", "AVAIL",

"VBLTY", "LOCTN", "GPRICE", "GPRICECUKY", "CDTSP", "PSTSP", "PDTSP", "ERNAM", "VHUSG", "CMPGN", "PCOUNT",

"PCOUNT_U", "IMMATDATE", "VHSAR", "VHORD", "SHLVL", "ARCHIVE_FLAG", "USED_VEHICLE" FROM "VLCVEHICLE" WHERE

"MANDT"=:A0 AND "AVAIL"=:A1 AND "PDTSP" BETWEEN :A2 AND :A3 AND "MATNR" IN (:A4 ,:A5 ,:A6 ,:A7 ,:A8 ,:A9 ,:A10

,:A11 ,:A12 ,:A13 ,:A14 ,:A15 ,:A16 ,:A17 ,:A18 ,:A19 ,:A20 ,:A21 ,:A22 ,:A23 ,:A24 ,:A25 ,:A26 ,:A27 ,:A28

,:A29 ,:A30 ,:A31 ,:A32 ,:A33 ,:A34 ,:A35 ,:A36 ,:A37 ,:A38 ,:A39 ,:A40 ,:A41 ,:A42 ,:A43 ,:A44 ,:A45 ,:A46

,:A47 ,:A48 ,:A49 ,:A50 ,:A51 ,:A52 ,:A53 ,:A54 ,:A55 ,:A56 ,:A57 ,:A58 ,:A59 ,:A60 ,:A61 ,:A62 ,:A63 ,:A64

,:A65 ,:A66 ,:A67 ,:A68 ,:A69 ,:A70 ,:A71 ,:A72 ,:A73 ,:A74 ,:A75 ,:A76 ,:A77 ,:A78 ,:A79 ,:A80 ,:A81 ,:A82

,:A83 ,:A84 ,:A85 ,:A86 ,:A87 ,:A88 ,:A89 ,:A90 ,:A91 ,:A92 ,:A93 ,:A94 ,:A95 ,:A96 ,:A97 ,:A98 ,:A99 ,:A100

,:A101 ,:A102 ,:A103 ,:A104 ,:A105 ,:A106 ,:A107 ,:A108 ,:A109 ,:A110 ,:A111 ,:A112 ,:A113 ,:A114 ,:A115 ,:A116

,:A117 ,:A118 ,:A119 ,:A120 ,:A121 ,:A122 ,:A123 ,:A124 ,:A125 ,:A126 ,:A127 ,:A128 ,:A129 ,:A130 ,:A131 ,:A132

,:A133 ,:A134 ,:A135 ,:A136 ,:A137 ,:A138 ,:A139 ,:A140 ,:A141 ,:A142 ,:A143 ,:A144 ,:A145 ,:A146 ,:A147 ,:A148

,:A149 ,:A150 ,:A151 ,:A152 ,:A153 ,:A154 ,:A155 ,:A156 ,:A157 ,:A158 ,:A159 ,:A160 ,:A161 ,:A162 ,:A163 ,:A164

,:A165 ,:A166 ,:A167 ,:A168 ,:A169 ,:A170 ,:A171 ,:A172 ,:A173 ,:A174 ,:A175 ,:A176 ,:A177 ,:A178 ,:A179 ,:A180

,:A181 ,:A182 ,:A183 ,:A184 ,:A185 ,:A186 ,:A187 ,:A188 ,:A189 ,:A190 ,:A191 ,:A192 ,:A193 ,:A194 ,:A195 ,:A196

,:A197 ,:A198 ,:A199 ,:A200 ,:A201 ,:A202 ,:A203 ,:A204 ,:A205 ,:A206 ,:A207 ,:A208 ,:A209 ,:A210 ,:A211 ,:A212

,:A213 ,:A214 ,:A215 ,:A216 ,:A217 ,:A218 ,:A219 ,:A220 ,:A221 ,:A222 ,:A223 ,:A224 ,:A225 ,:A226 ,:A227 ,:A228

,:A229 ,:A230 ,:A231 ,:A232 ,:A233 ,:A234 ,:A235 ,:A236 ,:A237 ,:A238 ,:A239 ,:A240 ,:A241 ,:A242 ,:A243 ,:A244

,:A245 ,:A246 ,:A247 ,:A248 ,:A249 ,:A250 ,:A251 ,:A252 ,:A253 ,:A254 ,:A255 ,:A256 ,:A257 ,:A258 ,:A259 ,:A260

,:A261 ,:A262 ,:A263 ,:A264 ,:A265 ,:A266 ,:A267 ,:A268 ,:A269 ,:A270 ,:A271 ,:A272 ,:A273 ,:A274 ,:A275 ,:A276

,:A277 ,:A278 ,:A279 ,:A280 ,:A281 ,:A282 ,:A283 ,:A284 ,:A285 ,:A286 ,:A287 ,:A288 ,:A289 ,:A290 ,:A291 ,:A292

,:A293 ,:A294 ,:A295 ,:A296 ,:A297 ,:A298 ,:A299 ,:A300 ,:A301 ,:A302 ,:A303 ,:A304 ,:A305 ,:A306 ,:A307 ,:A308

,:A309 ,:A310 ,:A311 ,:A312 ,:A313 ,:A314 ,:A315 ,:A316 ,:A317 ,:A318 ,:A319 ,:A320 ,:A321 ,:A322 ,:A323 ,:A324

,:A325 ,:A326 ,:A327 ,:A328 ,:A329 ,:A330 ,:A331 ,:A332 ,:A333 ,:A334 ,:A335 ,:A336 ,:A337 ,:A338 ,:A339 ,:A340

,:A341 ,:A342 ,:A343 ,:A344 ,:A345 ,:A346 ,:A347 ,:A348 ,:A349 ,:A350 ,:A351 ,:A352 ,:A353 ,:A354 ,:A355 ,:A356

,:A357 ,:A358 ,:A359 ,:A360 ,:A361 ,:A362 ,:A363 ,:A364 ,:A365 ,:A366 ,:A367 ,:A368 ,:A369 ,:A370 ,:A371 ,:A372

,:A373 ,:A374 ,:A375 ,:A376 ,:A377 ,:A378 ,:A379 ,:A380 ,:A381 ,:A382 ,:A383 ,:A384 ,:A385 ,:A386 ,:A387 ,:A388

,:A389 ,:A390 ,:A391 ,:A392 ,:A393 ,:A394 ,:A395 ,:A396 ,:A397 ,:A398 ,:A399 ,:A400

The SQL looks less efficient, as would be expected without TREX, but this is only taking 53 seconds to run whereas with TREX enabled it is taking over 7 minutes!

Please can you explain why this is?

The execution plan for the first query shows an estimated cost of only 2; the second shows around 5000 - however, I realised that you can't directly compare costs for different statements. But can anyone else see why the first query would take so much longer?

Thanks

Ross

[stefan_koehler · Accepted solution]

Hi Ross,

the limited execution plan via ST05 is not enough. However the following is a little bit of speculation, but the best we can do with the provided information right now.

We would need at least the access / filter predicates and the run time statistics at best (e.g for more information please check my blog post and the whole series about it).

Note, that the estimated CBO costs (or the DBMS_XPLAN publication) do not necessarily reflect the real work which needs to be done.

>> The execution plan for the first query shows an estimated cost of only 2

Yes, but please look closely at the execution plan. The cost represents only one execution, but you have an INLIST ITERATOR in it, which executes the sub tree for every (INLIST) value. However we currently don't know which IN LIST it is, but i would assume that it is for VGUID with help of the SQL context and your description of the PK.

>> My 'guess' at the moment is that the 'estimate' is wrong

It is very often wrong in a SAP environment due to the specific SAP Oracle database settings (e.g.) and the limited feature usage (or sometimes bugs).

However before we start with wild guess and crystal ball magic - please provide the detailed database structure information and the whole DBMS_XPLAN output (at best with runtime statistics).

Regards

Stefan

P.S.: It looks like the second SQL is truncated as well.

[Former Member · Accepted solution]

Thanks Stefan, I will have a read through those links

[ACE-SAP]

Hi

It's not really possible to help without having any information about the execution plan, the DB version, the table size....

At least there is one major difference between your two queries, in the 2nd one has an extra clause

"AVAIL"=:A1, the 1st one has a clause on VGUID that might be TREX related.

At least make sure statistics are up to date on your system.

Regards

[Former Member]

Hi Yves

Thanks for the response. To answer your questions:

Execution Plan for the first (long running):

SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 1 )

        4 FILTER

          Filter Predicates

            3 INLIST ITERATOR

                2 TABLE ACCESS BY INDEX ROWID VLCVEHICLE

                  ( Estim. Costs = 2 , Estim. #Rows = 1 )

                  Estim. CPU-Costs = 24,117 Estim. IO-Costs = 2

                  Filter Predicates

                    1 INDEX UNIQUE SCAN VLCVEHICLE~0

                      ( Estim. Costs = 1 , Estim. #Rows = 5 )

                      Search Columns: 2

                      Estim. CPU-Costs = 21,564 Estim. IO-Costs = 1

                      Access Predicates

Execution Plan for the second (quick running):

SELECT STATEMENT ( Estimated Costs = 5,852 , Estimated #Rows = 137 )

        3 FILTER

          Filter Predicates

            2 TABLE ACCESS BY INDEX ROWID VLCVEHICLE

              ( Estim. Costs = 5,852 , Estim. #Rows = 137 )

              Estim. CPU-Costs = 175,118,416 Estim. IO-Costs = 5,838

              Filter Predicates

                1 INDEX RANGE SCAN VLCVEHICLE~0

                  ( Estim. Costs = 1,006 , Estim. #Rows = 548,309 )

                  Search Columns: 1

                  Estim. CPU-Costs = 29,080,861 Estim. IO-Costs = 1,004

                  Access Predicates

Running Oracle 11.2.0.2.0.

Can't provide table sizes as the system is now down for maintenance. However, the table being searched is the same, VLCVEHICLE.

The query is supposed to be quicker with TREX, as it returns a VGUID list (globally unique vehicle ID) which is part of the primary key for VLCVEHICLE (that and MANDT). I can only guess that 'AVAIL' is not needed in the TREX enabled search as TREX has already provided the VGUID to search on (although I don't understand why PDTSP and MATNR are still in there too, I would have thought it would have dropped everything?). There's no indexes on AVAIL/PDTSP/MATNR either.

My 'guess' at the moment is that the 'estimate' is wrong, and due to the extra PSTSP and MATNR fields, it's not able to actually use the primary key?

Good point about stats... but don't these only relate to 'normal' indexes, not primary keys?

Cheers

Ross

[ACE-SAP]

Statistics are crucial for the CBO and the choice of the access plan whatever index are to be used.

They should be kept up to date and be scheduled daily in DB13.

588668 - FAQ: Database statistics

[Former Member]

Just checked and the statistics are up to date.

[fidel_vales]

Hi,

a wild guess to add.

The first is an "Inlist Iteration" on the primary index.

therefore It access the primary index (probably) 408 times.

That means 400 "fast" queries = slow one (guessing 4 accesses to the index + 1 access to the table = 5 buffer gets = 2000 buffer gets and some disk reads)

Second only does a range scan on the index and accesses the table. Not knowing statistical information on the index is difficult to guess the amount of BG but probably less as it is "faster"

You should get the statistical information requested

try to find the sql_id of both queries and the (formatted) output of the runtime statistical information for both (check it in sqlcache or with the script SQL_SQL_ID_KeyFigures.txt from note 1438410

[Former Member]

Gents... some good points there...

Breaking down the two queries into simple summaries on what they select on for clarity:

MANDT=:A0

PDTSP BETWEEN :A1 AND :A2

VGUID IN (:A3 ,:A4 ,:A5 ,:A6 ,:A7 )

MATNR IN (<list of 400+ conditions>)

MANDT=:A0

AVAIL=:A1

PDTSP BETWEEN :A2 AND :A3

MATNR IN (<list of 400+ conditions>)

I don't think I'd read this clearly before - I'd thought the huge inlist on the first (TREX on) was now on VGUID - which I guess wouldn't be so much of a problem. However, there are only 5 vehicles in the VGUID in list - and still the huge list of MATNR.

So I'm assuming Oracle thinks 'ooo lets use the primary key index as VGUID is there' but then has to do it 400+ times.

Where's in the second case it works out a better path and doesn't have to repeat itself.

I'm guessing that the issue is that TREX should have returned JUST the VGUID for the selection criteria (on PDTSP and MATNR) as they are the only valid vehicles, and the query should really just be on:

MANDT=:A0

VGUID IN (:A3 ,:A4 ,:A5 ,:A6 ,:A7 )

-which of course would be optimal.

Although to be honest I'm not entirely sure how TREX works. One thing is for sure - it's supposed to speed up the search, not slow it down!

But my assumption of how it would work:

User runs VELO query

Criteria passed to TREX

TREX returns the VGUIDs for that selection criteria

SAP/Oracle runs query for those relevant vehicles

So in my mind it shouldn't be passing in those other fields for selection?

[stefan_koehler]

Hi Ross,

why not just posting the requested information, so that we can help you fixing your issue?

More guessing and wild speculations don't get you / us further one centimeter.

Regards

Stefan

[Former Member]

Hi Stefan

Ah, if only life were that easy.

Several reasons:

1) I'm not the Basis guy. I have a Basis history, but I'm working in a different capacity for this client

2) The Basis guys are exceptionally busy and can't just carry out requests on demand

3) I don't have OS / DB level access

4) I'm locked out of the SAP system (the only one with enough vehicle data and TREX set up to be worth testing in) for the next week whilst upgrade work takes place

However, I'm keen to progress this as the problem has been around for some time and no-one has progressed it, and it's starting to get urgent... so I'm trying to understand the issue as best I can with the tools and resources I have.

I have requested for a similar message to be raised directly with SAP, but that has still not been done yet... so I thought I will try the experts on here, in case someone else has experienced the same / similar issue.

I'm not trying to 'guess' I'm trying to 'understand/analyse' what (little) information we do have.

Otherwise I'd happily obtain some DBMS_XPLAN information!!

Hope that clarifies

Regards

Ross

[stefan_koehler]

Hi Fidel,

>> therefore It access the primary index (probably) 408 times.

I am still pretty sure (even the detailed information is not provided yet), that VGUID is used for the INLIST ITERATOR as the estimated rows for the index unique scan are 5 (= same amount of IN LIST values of VGUID). However the cost of 5 index unique scans (1) + table access (1) seems to be way off (disregarding cpu costing for now).

I also noticed that my previous comment "The cost represents only one execution, but you have an INLIST ITERATOR in it" could be misleading for others in some way. I initially meant, that Oracle considers and calculates the cost of several IN LIST values, but in his case it looks like the cost represents only one execution (= calculation is going nuts compared to reality due to various reasons we don't know yet).

Here is just a short demo on Oracle 11.2.0.3.6 with a tiny table and an unique index, which even got higher and valid costs in such an IN LIST scenario. You also notice that the cost increases with the amount of values in the IN LIST (+1 per value for the index unique scan, which basically represents the I/O costs disregarding any cpu costing for now).

SYS@T11DB:11> create table t as select * from dba_objects;
SYS@T11DB:11> create unique index t_i on t(object_id);
SYS@T11DB:11> exec dbms_stats.gather_table_stats('SYS','T');

SYS@T11DB:11> select owner from t where object_id in (200)
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |        |       |    2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID | T    |      1 |    11 |    2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN          | T_I  |      1 |       |    1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SYS@T11DB:11> select owner from t where object_id in (200,300,400);
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |         |      |     5 (100)|          |
|   1 |  INLIST ITERATOR             |      |         |      |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |       3 |   33 |     5   (0) | 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_I  |       3 |      |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

SYS@T11DB:11> select owner from t where object_id in (200,300,400,500,600);
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |         |      |     7 (100)|          |
|   1 |  INLIST ITERATOR             |      |         |      |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |       5 |   55 |     7   (0) | 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | T_I  |       5 |      |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Regards

Stefan

P.S.: The scenario is not exactly the same as i use literals, but i already mentioned that the SAP Oracle database settings limit the CBO in several ways. I just wanted to provide a short demo to my previous comment.

[stefan_koehler]

Hi Ross,

just a short note. You can get the DBMS_XPLAN output via ST05 too. Just click on the textual representation in the icon bar. However the runtime statistics are missing in there, but you can get the access / filter predicates and some other important information.

Regards

Stefan

[Former Member]

Great, thanks - didn't know that.

Ok, as I mentioned, that system is unavailable to me now, but I have access to another 'similar' system. Database version, parameters, indexes, table structure etc *should* all be the same... there is a lot less vehicle data in there though. However, I tried running this anyway and the results are as follows:

Execution Plan

----------------------------------------------------------------------------------------------------------------------

System: JEQ

Plan hash value: 953916474

----------------------------------------------------------------------------------------------

| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |              |     1 |   249 |     2   (0)| 00:00:01 |

|*  1 |  FILTER                       |              |       |       |            |          |

|   2 |   INLIST ITERATOR             |              |       |       |            |          |

|*  3 |    TABLE ACCESS BY INDEX ROWID| VLCVEHICLE   |     1 |   249 |     2   (0)| 00:00:01 |

|*  4 |     INDEX UNIQUE SCAN         | VLCVEHICLE~0 |     5 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   3 - SEL$1 / VLCVEHICLE@SEL$1

   4 - SEL$1 / VLCVEHICLE@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(TO_NUMBER(:A1)<=TO_NUMBER(:A2))

   3 - filter("PDTSP">=TO_NUMBER(:A1) AND "PDTSP"<=TO_NUMBER(:A2) AND ("MATNR"=:A8 OR

              "MATNR"=:A9 OR "MATNR"=:A10 OR "MATNR"=:A11 OR "MATNR"=:A12 OR "MATNR"=:A13 OR

              "MATNR"=:A14 OR "MATNR"=:A15 OR "MATNR"=:A16 OR "MATNR"=:A17 OR "MATNR"=:A18 OR

              "MATNR"=:A19 OR "MATNR"=:A20 OR "MATNR"=:A21 OR "MATNR"=:A22 OR "MATNR"=:A23 OR

              "MATNR"=:A24 OR "MATNR"=:A25 OR "MATNR"=:A26 OR "MATNR"=:A27 OR "MATNR"=:A28 OR

              "MATNR"=:A29 OR "MATNR"=:A30 OR "MATNR"=:A31 OR "MATNR"=:A32 OR "MATNR"=:A33 OR

              "MATNR"=:A34 OR "MATNR"=:A35 OR "MATNR"=:A36 OR "MATNR"=:A37 OR "MATNR"=:A38 OR

              "MATNR"=:A39 OR "MATNR"=:A40 OR "MATNR"=:A41 OR "MATNR"=:A42 OR "MATNR"=:A43 OR

              "MATNR"=:A44 OR "MATNR"=:A45 OR "MATNR"=:A46 OR "MATNR"=:A47 OR "MATNR"=:A48 OR

              "MATNR"=:A49 OR "MATNR"=:A50 OR "MATNR"=:A51 OR "MATNR"=:A52 OR "MATNR"=:A53 OR

              "MATNR"=:A54 OR "MATNR"=:A55 OR "MATNR"=:A56 OR "MATNR"=:A57 OR "MATNR"=:A58 OR

              "MATNR"=:A59 OR "MATNR"=:A60 OR "MATNR"=:A61 OR "MATNR"=:A62 OR "MATNR"=:A63 OR

              "MATNR"=:A64 OR "MATNR"=:A65 OR "MATNR"=:A66 OR "MATNR"=:A67 OR "MATNR"=:A68 OR

              "MATNR"=:A69 OR "MATNR"=:A70 OR "MATNR"=:A71 OR "MATNR"=:A72 OR "MATNR"=:A73 OR

              "MATNR"=:A74 OR "MATNR"=:A75 OR "MATNR"=:A76 OR "MATNR"=:A77 OR "MATNR"=:A78 OR

              "MATNR"=:A79 OR "MATNR"=:A80 OR "MATNR"=:A81 OR "MATNR"=:A82 OR "MATNR"=:A83 OR

              "MATNR"=:A84 OR "MATNR"=:A85 OR "MATNR"=:A86 OR "MATNR"=:A87 OR "MATNR"=:A88 OR

              "MATNR"=:A89 OR "MATNR"=:A90 OR "MATNR"=:A91 OR "MATNR"=:A92 OR "MATNR"=:A93 OR

              "MATNR"=:A94 OR "MATNR"=:A95 OR "MATNR"=:A96 OR "MATNR"=:A97 OR "MATNR"=:A98 OR

              "MATNR"=:A99 OR "MATNR"=:A100 OR "MATNR"=:A101 OR "MATNR"=:A102 OR "MATNR"=:A103 OR

              "MATNR"=:A104 OR "MATNR"=:A105 OR "MATNR"=:A106 OR "MATNR"=:A107 OR "MATNR"=:A108 OR

              "MATNR"=:A109 OR "MATNR"=:A110 OR "MATNR"=:A111 OR "MATNR"=:A112 OR "MATNR"=:A113 OR

              "MATNR"=:A114 OR "MATNR"=:A115 OR "MATNR"=:A116 OR "MATNR"=:A117 OR "MATNR"=:A118 OR

              "MATNR"=:A119 OR "MATNR"=:A120 OR "MATNR"=:A121 OR "MATNR"=:A122 OR "MATNR"=:A123 OR

              "MATNR"=:A124 OR "MATNR"=:A125 OR "MATNR"=:A126 OR "MATNR"=:A127 OR "MATNR"=:A128 OR

              "MATNR"=:A129 OR "MATNR"=:A130 OR "MATNR"=:A131 OR "MATNR"=:A132 OR "MATNR"=:A133 OR

              "MATNR"=:A134 OR "MATNR"=:A135 OR "MATNR"=:A136 OR "MATNR"=:A137 OR "MATNR"=:A138 OR

              "MATNR"=:A139 OR "MATNR"=:A140 OR "MATNR"=:A141 OR "MATNR"=:A142 OR "MATNR"=:A143 OR

              "MATNR"=:A144 OR "MATNR"=:A145 OR "MATNR"=:A146 OR "MATNR"=:A147 OR "MATNR"=:A148 OR

              "MATNR"=:A149 OR "MATNR"=:A150 OR "MATNR"=:A151 OR "MATNR"=:A152 OR "MATNR"=:A153 OR

              "MATNR"=:A154 OR "MATNR"=:A155 OR "MATNR"=:A156 OR "MATNR"=:A157 OR "MATNR"=:A158 OR

              "MATNR"=:A159 OR "MATNR"=:A160 OR "MATNR"=:A161 OR "MATNR"=:A162 OR "MATNR"=:A163 OR

              "MATNR"=:A164 OR "MATNR"=:A165 OR "MATNR"=:A166 OR "MATNR"=:A167 OR "MATNR"=:A168 OR

              "MATNR"=:A169 OR "MATNR"=:A170 OR "MATNR"=:A171 OR "MATNR"=:A172 OR "MATNR"=:A173 OR

              "MATNR"=:A174 OR "MATNR"=:A175 OR "MATNR"=:A176 OR "MATNR"=:A177 OR "MATNR"=:A178 OR

              "MATNR"=:A179 OR "MATNR"=:A180 OR "MATNR"=:A181 OR "MATNR"=:A182 OR "MATNR"=:A183 OR

              "MATNR"=:A184 OR "MATNR"=:A185 OR "MATNR"=:A186 OR "MATNR"=:A187 OR "MATNR"=:A188 OR

              "MATNR"=:A189 OR "MATNR"=:A190 OR "MATNR"=:A191 OR "MATNR"=:A192 OR "MATNR"=:A193 OR

              "MATNR"=:A194 OR "MATNR"=:A195 OR "MATNR"=:A196 OR "MATNR"=:A197 OR "MATNR"=:A198 OR

              "MATNR"=:A199 OR "MATNR"=:A200 OR "MATNR"=:A201 OR "MATNR"=:A202 OR "MATNR"=:A203 OR

              "MATNR"=:A204 OR "MATNR"=:A205 OR "MATNR"=:A206 OR "MATNR"=:A207 OR "MATNR"=:A208 OR

              "MATNR"=:A209 OR "MATNR"=:A210 OR "MATNR"=:A211 OR "MATNR"=:A212 OR "MATNR"=:A213 OR

              "MATNR"=:A214 OR "MATNR"=:A215 OR "MATNR"=:A216 OR "MATNR"=:A217 OR "MATNR"=:A218 OR

              "MATNR"=:A219 OR "MATNR"=:A220 OR "MATNR"=:A221 OR "MATNR"=:A222 OR "MATNR"=:A223 OR

              "MATNR"=:A224 OR "MATNR"=:A225 OR "MATNR"=:A226 OR "MATNR"=:A227 OR "MATNR"=:A228 OR

              "MATNR"=:A229 OR "MATNR"=:A230 OR "MATNR"=:A231 OR "MATNR"=:A232 OR "MATNR"=:A233 OR

              "MATNR"=:A234 OR "MATNR"=:A235 OR "MATNR"=:A236 OR "MATNR"=:A237 OR "MATNR"=:A238 OR

              "MATNR"=:A239 OR "MATNR"=:A240 OR "MATNR"=:A241 OR "MATNR"=:A242 OR "MATNR"=:A243 OR

              "MATNR"=:A244 OR "MATNR")

   4 - access("MANDT"=:A0 AND ("VGUID"=:A3 OR "VGUID"=:A4 OR "VGUID"=:A5 OR

              "VGUID"=:A6 OR "VGUID"=:A7))

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - "MANDT"[VARCHAR2,9], "VGUID"[VARCHAR2,66], "VHCLE"[VARCHAR2,30],

       "MATNR"[VARCHAR2,54], "LIFNR"[VARCHAR2,30], "CHARG"[VARCHAR2,30],

       "EQUNR"[VARCHAR2,54], "WERKS"[VARCHAR2,12], "LGORT"[VARCHAR2,12],

       "BWTAR"[VARCHAR2,30], "CUOBJ"[VARCHAR2,54], "CUABA"[VARCHAR2,12],

       "MMCTR"[VARCHAR2,12], "MMSTA"[VARCHAR2,12], "MMTSP"[NUMBER,22], "SDCTR"[VARCHAR2,12],

       "SDSTA"[VARCHAR2,12], "SDTSP"[NUMBER,22], "KUNNR"[VARCHAR2,30], "ENDCU"[VARCHAR2,30],

       "VHVIN"[VARCHAR2,105], "VHCEX"[VARCHAR2,105], "AVAIL"[VARCHAR2,6],

       "VBLTY"[VARCHAR2,3], "LOCTN"[VARCHAR2,30], "GPRICE"[NUMBER,22],

       "GPRICECUKY"[VARCHAR2,15], "CDTSP"[NUMBER,22], "PSTSP"[NUMBER,22],

       "PDTSP"[NUMBER,22], "ERNAM"[VARCHAR2,36], "VHUSG"[VARCHAR2,6], "CMPGN"[VARCHAR2,30],

       "PCOUNT"[NUMBER,22], "PCOUNT_U"[VARCHAR2,9], "IMMATDATE"[VARCHAR2,24],

       "VHSAR"[VARCHAR2,30], "VHORD"[VARCHAR2,30], "SHLVL"[VARCHAR2,6],

       "ARCHIVE_FLAG"[VARCHAR2,3], "USED_VEHICLE"[VARCHAR2,3]

   2 - "MANDT"[VARCHAR2,9], "VGUID"[VARCHAR2,66], "VHCLE"[VARCHAR2,30],

       "MATNR"[VARCHAR2,54], "LIFNR"[VARCHAR2,30], "CHARG"[VARCHAR2,30],

       "EQUNR"[VARCHAR2,54], "WERKS"[VARCHAR2,12], "LGORT"[VARCHAR2,12],

       "BWTAR"[VARCHAR2,30], "CUOBJ"[VARCHAR2,54], "CUABA"[VARCHAR2,12],

       "MMCTR"[VARCHAR2,12], "MMSTA"[VARCHAR2,12], "MMTSP"[NUMBER,22], "SDCTR"[VARCHAR2,12],

       "SDSTA"[VARCHAR2,12], "SDTSP"[NUMBER,22], "KUNNR"[VARCHAR2,30], "ENDCU"[VARCHAR2,30],

       "VHVIN"[VARCHAR2,105], "VHCEX"[VARCHAR2,105], "AVAIL"[VARCHAR2,6],

       "VBLTY"[VARCHAR2,3], "LOCTN"[VARCHAR2,30], "GPRICE"[NUMBER,22],

       "GPRICECUKY"[VARCHAR2,15], "CDTSP"[NUMBER,22], "PSTSP"[NUMBER,22],

       "PDTSP"[NUMBER,22], "ERNAM"[VARCHAR2,36], "VHUSG"[VARCHAR2,6], "CMPGN"[VARCHAR2,30],

       "PCOUNT"[NUMBER,22], "PCOUNT_U"[VARCHAR2,9], "IMMATDATE"[VARCHAR2,24],

       "VHSAR"[VARCHAR2,30], "VHORD"[VARCHAR2,30], "SHLVL"[VARCHAR2,6],

       "ARCHIVE_FLAG"[VARCHAR2,3], "USED_VEHICLE"[VARCHAR2,3]

   3 - "MANDT"[VARCHAR2,9], "VGUID"[VARCHAR2,66], "VHCLE"[VARCHAR2,30],

       "MATNR"[VARCHAR2,54], "LIFNR"[VARCHAR2,30], "CHARG"[VARCHAR2,30],

       "EQUNR"[VARCHAR2,54], "WERKS"[VARCHAR2,12], "LGORT"[VARCHAR2,12],

       "BWTAR"[VARCHAR2,30], "CUOBJ"[VARCHAR2,54], "CUABA"[VARCHAR2,12],

       "MMCTR"[VARCHAR2,12], "MMSTA"[VARCHAR2,12], "MMTSP"[NUMBER,22], "SDCTR"[VARCHAR2,12],

       "SDSTA"[VARCHAR2,12], "SDTSP"[NUMBER,22], "KUNNR"[VARCHAR2,30], "ENDCU"[VARCHAR2,30],

       "VHVIN"[VARCHAR2,105], "VHCEX"[VARCHAR2,105], "AVAIL"[VARCHAR2,6],

       "VBLTY"[VARCHAR2,3], "LOCTN"[VARCHAR2,30], "GPRICE"[NUMBER,22],

       "GPRICECUKY"[VARCHAR2,15], "CDTSP"[NUMBER,22], "PSTSP"[NUMBER,22],

       "PDTSP"[NUMBER,22], "ERNAM"[VARCHAR2,36], "VHUSG"[VARCHAR2,6], "CMPGN"[VARCHAR2,30],

       "PCOUNT"[NUMBER,22], "PCOUNT_U"[VARCHAR2,9], "IMMATDATE"[VARCHAR2,24],

       "VHSAR"[VARCHAR2,30], "VHORD"[VARCHAR2,30], "SHLVL"[VARCHAR2,6],

       "ARCHIVE_FLAG"[VARCHAR2,3], "USED_VEHICLE"[VARCHAR2,3]