Skip to Content
avatar image
Former Member

Performance and temporary tables

Hello,

The query performance on our system is extremely bad.

In ST03N we see that it is due to the DB time. When running RSRT in Debug mode on one of the worst queries we see in the acces path that the temporary tables /BI0/06* get the following message:

"Explanation of query block number: 3 step: 2

Performance appears to be bad

No index is used. Sequential tablespace scan

Method:

. join each row of composite table, with matching rows

. of new table (nested loop Join).

. data pages are read in advance

. pure sequential prefetch is used

. new table:

. SAPD13./BI0/0600000153

. table space locked in mode: N

. Accesstype: sequential tablespace scan.

"

The temporary table does have a primary index, but no secondary.

We're on DB2 and BW 3.1 support package 16. The solution most probably lies in a not yet implemented support-package, however, implementing new support pachkages is not possible at the moment.

Does anybody now what to do to correct the index/statistics on the temporary table?

Thanks a lot,

Max

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 22, 2007 at 01:37 PM

    did you try transaction se14? these temporary tables re not supposed to have a secondary index.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 22, 2007 at 01:46 PM

    Yes, I looked there, but cannot find a solution in SE14; I don't know what to do there.

    Add comment
    10|10000 characters needed characters exceeded