Long running select on cluster tables without criteria

[Former Member]

Hello Everybody,

When I select in SAP transaction SE16 data from cluster table BSEG (ar any other cluster table like CDPOS) - i.e. corresponding db table cluster RFBLG <u>without any criteria</u> on the selection screen, I only put the Max Nr. of Hits = 1, the select timeouts.

I think the primary index is ok, because when I'm selecting according to primary index, there is no problem and it is very fast.

With transparent tables everything go well and the result when executing result without criteria with spec. number of hits is very fast. The problem is only with cluster tables.

This problem probably occurs after upgrading Oracle to 10.2.0.2.0. Our SAP_BASIS version is 620, SP SAPKB62062.

Before upgrade the same select was executed very fast.

The problem occurs not only in SE16, but also in ABAP program by selecting single record without criteria (without specifying WHERE condition):

SELECT SINGLE * FROM BSEG INTO L_BSEG.

Does anybody could help me with the problem? Whether it is some known problem of Oracle or SAP_BASIS or anything else. I've searched OSS but without success...

Thank you in advance,

Regards,

Peter Preisach

[Former Member]

SE16 is not designed for cluster table accesses without any criteria. Each real-life SELECT to BSEG works in a different way and you will not see a performance problem.

[Former Member]

Hello Peter,

a SELECT to a logical cluster table like BSEG is transformed by the SAP database interfact into a SELECT from the related physical cluster table RFBLG. Due to the cluster implementation it is necessary that an ORDER BY condition is added at this point.

This ORDER BY condition is now responsible for the fact that neither a FIRST_ROWS(1) hint nor a "WHERE ROWNUM <= :A0" condition is added. The problem is that the execution order with the hint and the ROWNUM condition would be:

1. Retreive the first 1 record

2. Sort it based on the ORDER BY

This makes obviously no sense. The ORDER BY has to be processed at first and then the first record can be retrieved. This can only be guaranteed if no hint and ROWNUM condition is specified in the first place.

Regards

Martin

[Former Member]

Hi Martin,

Thank you for your feedback.

I'm not able to change the select to add ORDER BY condition, as I am using standard transaction SE16 (display table entries) without any selection criteria (only specify max number of hits) and I've checked other systems and the same select is used for db access in ST05 and there is no performance issue...

Thank you,

Regards,

Peter

[Former Member]

Hi HP

You will have to look at the execution plans, an execution plan shows how oracle is accessing the data. You can do an SQL trace from ST05 for example. A select single will be transformed to a sql like this:

SQL Statement

<b>SELECT

/*+ FIRST_ROWS(1) */

*

FROM rfblg

WHERE rownum <= :A0</b>

Execution Plan
 SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 1 )
        2 COUNT STOPKEY
          Filter Predicates
            1 TABLE ACCESS FULL RFBLG
              ( Estim. Costs = 2 , Estim. #Rows = 1 )
              Estim. CPU-Costs = 7.411 Estim. IO-Costs = 2

Looking at the plan we can investigate further what is going wrong.

Regards

Michael

[Former Member]

Hello Michael,

Thank you for your feedback.

I was executing selection from BSEG withoout any selection criteria (any record should satisfy selection criteria), only with limitation Max. number of hits 1.

The output from SQL trace looks not good, but why, I just wanted to select first safisfying record:

SQL Statement

SELECT

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA"

FROM

"RFBLG"

WHERE

"MANDT" = :A0

ORDER BY

"MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO"

Execution Plan

SELECT STATEMENT ( Estimated Costs = 1.155.854 , Estimated #Rows = 11.590.533 )

5 2 SORT ORDER BY

( Estim. Costs = 1.155.854 , Estim. #Rows = 11.590.533 )

Estim. CPU-Costs = 60.390.187.473 Estim. IO-Costs = 1.141.264

1 TABLE ACCESS FULL RFBLG

( Estim. Costs = 1.006.386 , Estim. #Rows = 11.590.533 )

Estim. CPU-Costs = 44.986.659.844 Estim. IO-Costs = 995.518

Filter Predicates

Thank you,

Regards,

Peter

[Former Member]

Hi Peter

There is no row limitation in this sql. It is in fact reading all rows (and full sort).

There should be a <b>where ROWNUM <=</b> and probably a FIRST_ROWS Hint.

Regards

Michael

[Former Member]

I just checked on my system, i am having the same plan as you have. So this must have something to do with the cluster table implementation, it could be a bug as well.

Maybe time for a SAP message

[Former Member]

Hi Michael,

Thank you for your feedback.

I did the same, I've compared many systems and I've received the same plan in all systems, but only in our system (after the Oracle upgrade) I got this issue... I've created OSS message, SAP still didn't reply...

Thank you,

Regards,

Peter

[Former Member]

Hi Peter,

Try to run database statistics on the objects or a full database stats from OS level. Also, if you know the indexes, rebuild all the indexes of the table.

Since these are clustered tables, you could also see if reorganization of underline tables helps you. What is the size of the table?

Cheers,

Nisch