topic Re: ST05 in Application Development and Automation Discussions

ST05

Former Member — Thu, 30 Apr 2009 17:34:01 GMT

Hi Expert,

Got a few questions here.

1. What does access predicates and filter predicates in ST05/Performance trace means? Does it influence anything when creating an index?

2. When you create an index, does the order of the column/field matters?

Thanks in advance.

Re: ST05

HermannGahm — Thu, 30 Apr 2009 18:24:28 GMT

Hi Eida,

> What does access predicates and filter predicates in ST05/Performance trace means?

this information in an (ORACLE, since db specific) execution plan tells you what columns

of your where condition could be used to restrict the number of index pages that need to be

read (access predicates) and what columns of your where condition could be used for filtering

after reading index or table pages (filter predicates). Since the access predicates restrict the

number of pages to be read they minimize i/o. The filter pages do not restict the number of

pages to be read (not minimizing i/o) but they do restrict the amount of data that is sent

from the db to the application server where your ABAP program runs.

> Does it influence anything when creating an index?

Hm... what do you mean by this question? The access and filter predicates just show you

how your where condition is applied on index and/or table blocks... only for existing indexes

and tables only, of course.

> When you create an index, does the order of the column/field matters?

Yes it does. In general put these cloumns that are used with EQUAL (=) or IN ( which could

be seen as a concatenated or iterated equal / = ) operator, at the beginning of an index.

The classic phone book example:

An index is created on LASTNAME, FIRSTNAME, STREET.

This indexes can support the following where conditions:

where lastname = ? and firstname = ?

where lastname = ?

where lastname = ? and street = ?

where lastname = ? and firstname =? and street = ?

(and others with ranges, between, like, ......)

If you would have created the index in a different order:

e.g. FIRSTNAME, LASTNAME, STREET

these 2 where conditions:

where lastname = ?

where lastname = ? and street = ?

would NOT be supported by this index since they are missing

the fistname which is the first field in the street.

So for your frequently executed sql statements put those fields in

the beginning of the index which are used regular with equal (=) or IN condition

(and which are slective of course).

Hope this helps,

Hermann

Re: ST05

MarcinPciak — Thu, 30 Apr 2009 18:29:16 GMT

Hi,

As for the second question: yes the order of columns does matter both during creation and usage.

Regards

Marcin

Re: ST05

Former Member — Tue, 05 May 2009 08:34:17 GMT

Order does matter and that's why we maintain order in WHERE clause.

Re: ST05

ThomasZloch — Tue, 05 May 2009 08:51:15 GMT


> Order does matter and that's why we maintain order in WHERE clause.

Please don't mix things up. Order of fields does matter when an index is designed and created (see Hermann's answer), order of the fields in the WHERE-clause does not matter reg. index usage, it might matter reg. program readability, but that's a different story.

Thomas

Re: ST05

Former Member — Tue, 05 May 2009 09:00:32 GMT

the order of the field in the WHERE-clause does not influence the decision which index is used.

Everybody you thinks differently should think about, how he would design a design logic,

only a simple one following the order of WHERE-clause or trying different orders.

the order of the fields in the index is essential, it is like a printed telephone book, a new order is a different book.

However, the statements are stored, to used the same processing later again, the decision which index to use can take longer than a simple SELECT SINGLE. To find the stored statement again, the WHERE condition should have the same order, otherwise this cache becomes larger and slower. That is the effect of the order of the fields in the WHERE-condition.

Re: ST05

Former Member — Tue, 05 May 2009 13:28:35 GMT


> Hi Expert,
> 
> Got a few questions here.
> 
> 1. What does access predicates and filter predicates in ST05/Performance trace means? Does it influence anything when creating an index?
> 2. When you create an index, does the order of the column/field matters?
> 
> 
> Thanks in advance.

Hi Eida,

1. good statements made by others

2. Index

creation

When it comes to design an index you have to KNOW the queries that your application will fire to the table.

There is no general rule except that the index should fit with the access pattern.

I.e.

maybe your app would have a lot of queries of these types:

select * from tab where col1 = x and col2 = y

select * from tab where col2 = y

it would make sense to order the index keys that way: (COL2,COL1)

discriminating myth: "You should set the most discriminating index column in front ."

COL1: 10000000

COL2: 1000000

myth suggested order: (COL1, COL2)

If you consider the columns C1 and C2 as vectors and have to compare them togehther in a WHERE it doesn't matter which comes first.

Furthermore, techniques like index key compression imply that you put the least discriminant in front

to get a good compression factor.

bye

Re: ST05

Former Member — Tue, 05 May 2009 13:39:09 GMT

yes

..use the fileds in order how it is there in the Table or use the order of the index created..

check in DB05 transaction which Index is using..

regards,

Prabhudas

Re: ST05

HermannGahm — Tue, 05 May 2009 15:20:22 GMT

Hi Yukonkid,

>Furthermore, techniques like index key compression imply that you put the least discriminant in

> front to get a good compression factor.

indeed. That's another good argument why order of index fields should not be based on nr. of distinct keys only... . Thank you. I never thought of this in this combination so far

Kind regards,

Hermann

Re: ST05

HermannGahm — Tue, 05 May 2009 15:38:40 GMT

Hi Thomas,

readability is another reason why we should use one order in where clause.

as well as different cursors in statement caches (for different sql strings)

as well as behaviour in st05 regarding identical selects ... all of them are reasons

for trying to use one specific order (e.g. like defined in ddic) of fields in where clause.

cursor cache:

The attached 3 sql statements are 3 different statements regarding their strings.

3 times prepare in st05. So they will be processed in 3 different cursors.

The db optimizer will come up with the SAME execution plan for all 3 statements

and use CPU time for 3 times parsing.

These execution plans will allocate space for 3 statements in the cursor cache

and so on.

st05:

Since the attached 3 sql statements are technically different ST05 shows no

identical selects.

If we would have used only one order for the field list we would have saved some

ressources on db and we would have seen that the selects are identical in ST05...

and yes... the ABAP program would be nicer regarding readability.

Kind regards,

Hermann



  select msgnr arbgb sprsl
  from t100 BYPASSING BUFFER
  into CORRESPONDING FIELDS OF t100
  where
  msgnr = '010' and
  arbgb = 'something' and
  sprsl = 'DE'.

  ENDSELECT.

  select msgnr arbgb sprsl
  from t100 BYPASSING BUFFER
  into CORRESPONDING FIELDS OF t100
  where
  arbgb = 'something' and
  msgnr = '010' and
  sprsl = 'DE'.

  ENDSELECT.

    select msgnr arbgb sprsl
  from t100 BYPASSING BUFFER
  into CORRESPONDING FIELDS OF t100
  where
  sprsl = 'DE' and
  msgnr = '010' and
  arbgb = 'something'.

  ENDSELECT.


      499 T100       PREPARE            0 SELECT WHERE "MSGNR" = :A0 AND "ARBGB" = :A1 AND "SPRSL" = :A2
        5 T100       OPEN               0 SELECT WHERE "MSGNR" = '010' AND "ARBGB" = 'something' AND "SPRSL" = 'D'
   10.638 T100       FETCH       0   1403
      448 T100       PREPARE            0 SELECT WHERE "ARBGB" = :A0 AND "MSGNR" = :A1 AND "SPRSL" = :A2
        5 T100       OPEN               0 SELECT WHERE "ARBGB" = 'something' AND "MSGNR" = '010' AND "SPRSL" = 'D'
    1.594 T100       FETCH       0   1403
      462 T100       PREPARE            0 SELECT WHERE "SPRSL" = :A0 AND "MSGNR" = :A1 AND "ARBGB" = :A2
        5 T100       OPEN               0 SELECT WHERE "SPRSL" = 'D' AND "MSGNR" = '010' AND "ARBGB" = 'something'
    1.793 T100       FETCH       0   1403

Re: ST05

ThomasZloch — Tue, 05 May 2009 16:05:02 GMT

I understand the argument about the unnecessary parsing of SQL statements, but I would like to know, what effect does this have on the overall performance of a typical SAP installation? I assume much less than 1%? (comparing systems where this coding rule is being followed and those where it's not)

Thomas

Re: ST05

HermannGahm — Tue, 05 May 2009 16:15:59 GMT

Hi Thomas,

i think it it could be neglected, since repeated executions will reuse the cursors.

Todays SQL statement caches are rather big as well. But it is not so long ago

when they were in the range of Megabytes (not Gigabytes like today).

When there is a low hitratio on the statement cache (due to a small size) and or

a high number of different sql statements it maybe leads to a little overhead

on cpu usage due to parsing. And if we have to regularly prepare the execution plan

again and again (due to low hitratio in the cache) it would affect also execution

time of the statement. This would be a rather rare case i think.

So far (last 10 years), i never came across that this "issue" played an important or

significant role... . I think it is nice to have... as long as we don't have really small statenent

caches and low hitratios on them.

Of course I'm only talking about regular OPEN SQL

which is not using HINTS like &SUBSTITUTE VALUES& in a LOOP or something

Kind regards,

Hermann

Edited by: Hermann Gahm on May 5, 2009 6:24 PM

Re: ST05

ThomasZloch — Tue, 05 May 2009 19:19:26 GMT

Hermann, many thanks for these clarifications.

Another example where a simple question turns into an interesting discussion, only the original poster was never seen again...

Good night

Thomas

Re: ST05

Former Member — Tue, 05 May 2009 19:22:20 GMT

Thanks for all the post. I'm still digesting all the inputs.

Re: ST05

HermannGahm — Wed, 06 May 2009 06:42:14 GMT

Hi Thomas,

just another comment.... :

If i remember right... you work mostly on ORACLE.

For ORACLE 10g you can run the following SELECT

(in the ABAP Report RSORADJV for example):


SELECT
    stat_name,
    round(value/1000000) time_waited_sec
FROM
    v$sys_time_model
WHERE stat_name LIKE 'DB%' 
OR    stat_name LIKE '%parse%' 
ORDER BY time_waited_sec desc

you will get an output like this:


STAT_NAME                                        TIME_WAITED_SEC

DB time                                          10928573
DB CPU                                           5810338
parse time elapsed                               44891
hard parse elapsed time                          32916
failed parse elapsed time                        497
hard parse (sharing criteria) elapsed time       380
hard parse (bind mismatch) elapsed time          47
failed parse (out of shared memory) elapsed time 0

all times are in seconds.

Now you can see how much time is needed for parsing.

You can see as well how much CPU time (in total) is used.

This time includes parsing... and recursive sql and scanning

the buffer cache and latches and and... . And you get the total db time

this time includes DB CPU + i/o + time for enque + everything else

for what db consumes time with... .

You can see how big the influence of parsing is with respect

to DB time and DB CPU time.

If you have time... ... you can then order all your field lists and

where conditions... run the query again... and see if parsetimes

decrease (what should be the case). But I can't promise you if

you would FEEL a difference in overall DB performance...

And btw: As a rule of thumb we assume that in a well tuned system

DB CPU time is 40 % of DB time.... if it is less... we assume that

there is still improvement potential regarding SQL tuning... indexes...

Kind regards,

Hermann

and just another adition:


SELECT * 
FROM
V$SGASTAT
WHERE
name = 'sql area'

shows you how big your sql area is inside the shared pool is.

The hitratios can be seen on the main screen in ST04:



Shared Pool
...
SQL Area getratio  %          100,0
         pinratio  %          100,0
...

Edited by: Hermann Gahm on May 6, 2009 8:46 AM

Re: ST05

ThomasZloch — Wed, 06 May 2009 09:10:53 GMT

> ABAP Report RSORADJV

very interesting, many thanks.

> If you have time... ... you can then order all your field lists and

> where conditions...

you guessed it, not enough time, let alone the administrational effort of moving code changes to production...

> And btw: As a rule of thumb we assume that in a well tuned system

> DB CPU time is 40 % of DB time.... if it is less... we assume that

> there is still improvement potential regarding SQL tuning... indexes...

36% here, I'll have a look...

Cheers

Thomas

Re: ST05

HermannGahm — Wed, 06 May 2009 11:27:07 GMT

Hi Thomas,

don't nail down the 40 %... your 36 % ... to me sounds already like a well tuned system...

If you would have said... 10 % CPU... i would recommend sql cache and wait event analysis...

but 36 % is quite ok i would say...

Kind regards,

Hermann

Re: ST05

Former Member — Fri, 08 May 2009 08:09:26 GMT

@YukonKid

> discriminating myth: "You should set the most discriminating index column in front ."

> COL1: 10000000

> COL2: 1000000

> myth suggested order: (COL1, COL2)

> If you consider the columns C1 and C2 as vectors and have to compare them togehther in

> a WHERE it doesn't matter which comes

sorry, I am not convinced! The actual execution is never an issue, it is using the complete correct data. But optimizer decides which path to use based on assumptions, and there a good index design matters!

Plus your example is simply not good, we are not BW, the numbers are much smaller!

The question is the following, not huge and nearly huge, but huge and medium

COL1: 10.000.000

COL2: 10.000 or 1.000

Siegfried

Re: ST05

Former Member — Fri, 08 May 2009 08:17:34 GMT

> I understand the argument about the unnecessary parsing of SQL statements, but I would like to

> know, what effect does this have on the overall performance of a typical SAP installation? I assume

> much less than 1%? (comparing systems where this coding rule is being followed and those where it's > not)

the 1% is probably correct, if you count the parsing of all duplicate statements in the cursor cache and

compare them with all DB executions .... this must be much much less than 1%. But is this really interesting?

There is one argument which is sufficient for me, the statictics of the cursor cache. If statements which are in principle identical a spread over different entries, the I get no good overview on the DB performance. Maybe there is statement with problems which has 10 WHERE-conditions, then it can be distributed over ten or more entries and it will not be the top line.

Siegfried

Re: ST05

Former Member — Fri, 08 May 2009 08:26:13 GMT

> And btw: As a rule of thumb we assume that in a well tuned system

> DB CPU time is 40 % of DB time.... if it is less... we assume that

> there is still improvement potential regarding SQL tuning... indexes...

less is better, even with percentages ...

I don't like these overall percentages too much, they are good for the tuning of a whole system.

People here look at particular applications, and there is no general rule for DB-percentages.

Check the standard benchmarks they a range start from 20%.