Tuning a stubborn SQL statement

[Former Member]

Hello,

I have performance problems with this SQL statement:

SELECT
  T_00 . "FBRNR" , T_00 . "FBFNR" , T_01 . "DIVISION" , T_01 . "SERVCODE" , T_01 . "KUNSND" ,
  T_01 . "KUNRCV" , T_01 . "KUNREG" , T_01 . "LFBED" , T_00 . "ABRDAT" , T_01 . "REGNL"
FROM
  "ZFBINFO" T_00 , "ZFBDT" T_01
WHERE
  ( T_01 . "MANDT" = :A0 AND T_00 . "FBRNR" = T_01 . "FBRNR" AND T_00 . "FBFNR" = T_01 . "FBFNR" )
  AND T_00 . "MANDT" = :A1 AND "DIVISION" = :A2 AND T_00 . "FBSTAT" <= :A3;

On Oracle 9i it ran within 90 seconds and the execution plan was this:

Execution Plan
----------------------------------------------------------
Plan hash value: 298645010

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  1525K|   141M|  5595K  (1)| 09:33:38 |
|   1 |  NESTED LOOPS                |             |  1525K|   141M|  5595K  (1)| 09:33:38 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ZFBINFO     |  9152K|   305M| 97247   (1)| 00:09:59 |
|*  3 |    INDEX RANGE SCAN          | ZFBINFO~B   |  9152K|       |  6121   (2)| 00:00:38 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| ZFBDT       |     1 |    62 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | ZFBDT_____0 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     302386  consistent gets
      19689  physical reads

After an upgrade to Oracle 10g the statement runs for more than 8 minutes and the execution plan is like this:

Execution Plan
----------------------------------------------------------
Plan hash value: 4079065069
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes |TempSpc| Cost(%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |  1525K|   141M|       |   909K  (4)| 01:33:13 |
|*  1 |  HASH JOIN                   |           |  1525K|   141M|   410M|   909K  (4)| 01:33:13 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ZFBINFO   |  9152K|   305M|       | 97247  (1)| 00:09:59 |
|*  3 |    INDEX RANGE SCAN          | ZFBINFO~B |  9152K|       |       |  6121   (2)| 00:00:38 |
|*  4 |   TABLE ACCESS FULL          | ZFBDT     |    25M|  1535M|       |   716K  (4)| 01:13:26 |
--------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        495  recursive calls
         66  db block gets
    4709177  consistent gets
    4691042  physical reads

I have asked the SQL Tuning Advisor to tune this statement on Oracle 10g, but it

believes the "TABLE ACCESS FULL" on table ZFBDT is the best way for this statement.

The index ZFBDT_____0 is defined on columns MANDT, FBRNR, FBFNR, so not suprisingly

if I add a hint manually /+INDEX(T_01, "ZFBDT_____0") / then the statement runs 5 times

faster and requires only very few physical and logical reads.

How can I convince the Oracle 10g optimizer to use the primary key index on ZFBDT?

Regards,

Mark

[Former Member · Accepted solution]

The full table scan is just a consequence of the hash join. So your question should be: "How can I convince the CBO to use a nested loop join?". This will become more attractive if you increase the CBO statistic values for the distinct values / distinct keys related to the ZFBINFO selection conditions like FBSTAT.

What is the real amount of records returned from ZFBINFO for this selection? Is it really as high as expected by the CBO? Or is the FBSTAT condition perhaps much more selective than indicated by the distinct values?

Regards

Martin

[Former Member · Accepted solution]

Hello Martin,

the tables ZFBDT and ZFBINFO have got 155 million rows. When

checking the bind variables I can see the condition FBSTAT<=70.

The SQL statement selects only 58000 entries from table ZFBINFO:

FB   COUNT(*)
-- ----------
00          3
10      33151
20       5657
28         28
30      11971
40       1734
50        163
55       5934
72        437
73      17355
74      49113
75      24438
76        529
78     122828
80  139801614
82   11099678
85    3054910
90    1627482

So I say the selection on FBSTAT is really selective.

However, I don't see how manipulating the CBO statistics

(as described in SAP note 724545) would help here.

Regards,

Mark

[Former Member · Accepted solution]

You can e.g. increase NUM_DISTINCT of column FBSTAT by factor 10 using DBMS_STATS.SET_COLUMN_STATS.

[Former Member · Accepted solution]

Hello Martin,

as I said initially, the Oracle Optimizer is really stubborn.

I have raised the value for NUM_DISTINCT by a factor

of 1000, and the Optimizer still favors the full table scan.

I also tried to create a SQL profile manually, but even

that wasn't recognized by the Optimizer. (That procedure

might be too tricky.)

Why is the Oracle 10g so unteachable?

Regards,

Mark

[fidel_vales · Accepted solution]

Hi,

In Oracle 9i HASH_JOINS were not "allowed" (HASH_JOIN_ENABLED=FALSE) and that is not the case in 10g.

I think Martin has not realized that the condition on FBSTAT is "<=", therefore Oracle calculate the number of rows returned based on a % of the total table rows. This means that the NUM_DISTINCT is not used

If you are sure that the NL is faster you have two options (IMHO)

1.- Reduce the number of rows in the table, I do not like this

2.- as this seems to be a customer code, I'd add a hunt to the code.

Again two options

1) use the hint "USE_NL" instead of the "INDEX" that you tried (see note 772497 from Martin)

2) I think there is another hint (something like CARDINALITY) where you can indicate the number of rows that are expected to be returned.

I cannot find information now about it.

In any case, you can try with the first to see how it goes

Regards

Fidel

[Former Member · Accepted solution]

Can you supply the output of CBOStatistics_StatisticOverviewForTable.txt (SAP Note 1438410) for both tables?

Perhaps it's the best idea to use a &SUBSTITUTE VALUES& DBI hint in combination with histogram CBO statistics.

Regards

Martin

[Former Member · Accepted solution]

Hello Fidel,

NUM_DISTINCT is used also for range conditions (as long as it is smaller than 111).

Regards

Martin

[fidel_vales · Accepted solution]

>

> Hello Fidel,
> 
> NUM_DISTINCT is used also for range conditions (as long as it is smaller than 111).
> 
> Regards
> 
> Martin

more things to remember

What about to use the HINT CARDINALITY? like

 /*+ cardinality (T_00 5800) */ 

Edited by: Fidel Vales on Mar 23, 2011 4:21 PM

[volker_borowski2 · Accepted solution]

Hi,

are you able/permitted to change the code?

In this case a hint might be easier than twisting stats.

If it is not allowed, check if the plan changes, if you delete the entire stats on both tables.

Since your index is named ___0 it suggests that it has been created on a very old release.

I remember some trouble related to mixed dbms-stats vs. "analyze table" statistics, so

it might be a good idea to completely delete the stats (brtools and "analyze ... delete statistics")

and re-calc only with new brtools (dbms-stats), to rule out some old "analyze" values.

In addition I'd try an ST05 in between, when both tables do not have any stats.

Volker

[Former Member · Accepted solution]

Hello,

the system is currently being migrated from AIX/Oracle 9i to HP-UX/Oracle 10g.

So it isn't productive yet. The tests with the hint USE_NL looked best. As a

second alternative changing the ABAP code would have been an option,

since this is a Z-ABAP anyway.

Thanks a lot for all your helpful replies!

Mark