poor performance on table - what index?

[Former Member]

Hi,

Can anyone sugest what what a index should look like to improve performance on table SWWWIHEAD for the query below?

It is taking way to long if it uses the primary index only. I created another index , but oracle dont want to use it.

By the way, SWWW_OUTBOX is a view containing tables SWWWIHIST and SWWWIHEAD.

SQL Statement

-

---

SELECT

"CLIENT" , "WI_ID" , "WI_TYPE" , "WI_TEXT" , "WI_STAT" , "WI_CD" , "WI_CT" , "METH_EDATE" ,

"METH_ETIME" , "METH_USER" , "METHOD" , "WI_PRIO" , "NOTE_EXIST" , "WI_LANG" , "WI_RH_TASK" ,

"NO_DEADL" , "WLC_DISPL" , "WF_TYPE" , "WI_AAGENT" , "PROCCAT" , "CREA_TMP"

FROM

"SWW_OUTBOX"

WHERE

"CLIENT" = :A0 AND "METH_USER" = :A1 AND "METHOD" IN ( :A2 , :A3 , :A4 , :A5 , :A6 , :A7 ) AND

"METH_EDATE" >= :A8 AND "WI_STAT" IN ( :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 ) AND

"WI_TYPE" IN ( :A17 , :A18 , :A19 )

Execution Plan

-

---

System: RQ1

-

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

-

---

0	SELECT STATEMENT		106	19716	226 (1)
1	NESTED LOOPS		106	19716	226 (1)
2	INLIST ITERATOR
3	TABLE ACCESS BY INDEX ROWID	SWWLOGHIST	106	6678	162 (0)
4	INDEX RANGE SCAN	SWWLOGHIST~1	19		161 (1)
5	TABLE ACCESS BY INDEX ROWID	SWWWIHEAD	1	123	1 (0)
6	INDEX UNIQUE SCAN	SWWWIHEAD~0	1		0 (0)

-

---

Predicate Information (identified by operation id):

-

---

4 - access("T1"."CLIENT"=:A0 AND ("T1"."METHOD"=:A2 OR

"T1"."METHOD"=:A3 OR "T1"."METHOD"=:A4 OR "T1"."METHOD"=:A5 OR

"T1"."METHOD"=:A6 OR "T1"."METHOD"=:A7) AND "T1"."METH_EDATE">=:A8 AND

"T1"."METH_USER"=:A1)

filter("T1"."METH_USER"=:A1)

5 - filter(("T2"."WI_TYPE"=:A17 OR "T2"."WI_TYPE"=:A18 OR

"T2"."WI_TYPE"=:A19) AND ("T2"."WI_STAT"=:A9 OR "T2"."WI_STAT"=:A10 OR

"T2"."WI_STAT"=:A11 OR "T2"."WI_STAT"=:A12 OR "T2"."WI_STAT"=:A13 OR

"T2"."WI_STAT"=:A14 OR "T2"."WI_STAT"=:A15 OR "T2"."WI_STAT"=:A16))

6 - access("T2"."CLIENT"=:A0 AND "T1"."WI_ID"="T2"."WI_ID")

[former_member204746 · Accepted solution]

what index have you created? what is its definition?

what is your Oracle version? which patches were installed.

[Former Member · Accepted solution]

Created an index SWWWIHEAD~Z3. Fileds: CLIENT, WI_STAT,WI_TYPE.

Using Oracle 10.2.0.2 with pacthes up to July.

[Former Member · Accepted solution]

Hi

Check this SAP Note 1227227 - SQL statement runs endlessly or times out (kkompr), There is a bug in oracle patch 2 for 10g.

Note spks about a parameter, check whether this parameter is set in SPFILE.

Regards

KT

[Former Member · Accepted solution]

Hi,

Thanks had a look at the note, but unfortunately this is not relevant.

Any advice on what fields I can use for an index?

[Former Member · Accepted solution]

Hi,

The fields can be identified based on the usage, check in SE16 the fields will have full descriptions.

Can check with team or user who is accesing the table and create an index only to those fields.

Regards

KT

[Former Member · Accepted solution]

KT, thanks for the reply, but this is not useful.

I know what fields are available via SE16. I have done a SQL analysis based on the explain plan. I identified the required fileds for an index as described earlier, but this does not get used by the query.

Can you soemone please sugest which fileds to use in an index based on the sql explain plan provided originally?

[Former Member · Accepted solution]

Adriaan,

if I correctly understood the execution plan you showed, costs are mainly caused by table SWWLOGHIST, not by SWWWIHEAD.

The primary index SWWWIHEAD~0 seems optimal to me.

As for SWWLOGHIST, not sure if an index other than SWWLOGHIST~1 might give a better result.

And by the way, as you didn't mention it so far:

I presume statistics for both tables are up to date?

And sometimes it may help to rebuild old indexes also, especially in those cases where data has been deleted recently.

regards

[fidel_vales · Accepted solution]

Hello Adriaan,

A bit of "guessing" as not all data is available

The access path shown by you is quite OK.

Your query has the following conditions (adding the table for clarity)

SWWWIHEAD."CLIENT" = :A0 AND
SWWLOGHIST."METH_USER" = :A1 AND
SWWLOGHIST."METHOD" IN ( :A2 , :A3 , :A4 , :A5 , :A6 , :A7 ) AND
SWWLOGHIST."METH_EDATE" >= :A8 AND
SWWWIHEAD."WI_STAT" IN ( :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 ) AND
SWWWIHEAD."WI_TYPE" IN ( :A17 , :A18 , :A19 )

Oracle enters using the index SWWLOGHIST~1 (CLIENT, METHOD, METH_EDATE, METH_USER)

As you see, all fields match the conditions. The problem is that we have an "=" after an ">":

-------------------------
|NONUNIQUE SWWLOGHIST~1 |
-------------------------
|Column Name |Condition |
-------------------------
|CLIENT      |       =  | 
|METHOD      |      IN  | 
|METH_EDATE  |       >  | 
|METH_USER   |       =  | 
-------------------------

Then it does a INDEX UNIQUE SCAN SWWWIHEAD~0 (because the join condition between the two tables in the view) and that is good (it will return 1 or 0 rows always)

So, first way to improve it would be to switch METH_USER and METH_EDATE. as the index would match better the conditions (it is a standard SAP index so may be you can create one new, see next)

The only index that you could create that could improve somehow this specific query would to add also "METH_ETIME" and "WI_ID" at the end of the "changed" SWWLOGHIST~1, something like:

CLIENT

METHOD

METH_USER

METH_EDATE

METH_ETIME

WI_ID

That way Oracle would not do any access from the index to the table.

So far the theory.

I assume that the query is slow. Have you analyze "why"?, where is expending most of the time (accessing what?, doing what?), how big the table is? (have you checked the note 706478 Preventing Basis tables from increasing considerably)?

[Former Member]

yes, probably true. but what confused me was that the query was actually on a view. There were 2 tables involved and from the traces I identified (propably incorrectly) that the poor access path was on SWWWIHEAD rather that SWWLOGHIST, especially as SWWWIHEAD was only using the primary index. I was therefore focusing in the wrong table.

[Former Member]

Fidel you are a genius! Thank you, the index you sugested fixed the issue. Query runtime down from minutes to 5 seconds.

[former_member204746]

in fact, it was simple. he suggested to create an index with all fields from the WHERE clause of your select statement.

this should always work as Oracle will find a perfect index match for its needs.

[fidel_vales]

Hi,

>

> in fact, it was simple. he suggested to create an index with all fields from the WHERE clause of your select statement.
> 
> this should always work as Oracle will find a perfect index match for its needs.

That is not 100% correct.

If you take a look at the index that oracle was using it also had all fields from the WHERE clause of the select statement.

Therefore, your explanation is not complete, it has to be something else in the indexes I suggested (remember that I suggested two different things)

I think it is explained on my post the reason for both suggestions and why each would improve the query (with the available data)

[Former Member]

hi ,

Reduce the size of this basis table is also a good start if you want get back on perf

Look at note 706478 : preventing size of basis table

good luck

br

eric

[stefan_koehler]

Hello Adriaan,

just a simple question: Have you collected statistics after you have created the index SWWWIHEAD~Z3?

If yes, we would need more information about the data distribution, maybe collected histograms, etc.

The easist way to provide all the information that is needed is to post a 10053 CBO trace.

Regards

Stefan