Execution time of same program makes big difference

[Former Member]

Hello all,

The execution time of same program in PRD system and QAS system makes big difference.

The difference of data is not much(as system copy was run on a regular time schedule. And the system enviroments are exactly the same. However, while the program only cost 2-3 seconds in QAS, it cost 7-8 minutes in PRD.

It only happens when trying to join some tables together.

I've checked the execution plans of same search, they are different:

QAS:

SQL Statement

-

---

SELECT

T_00.RANL , T_00.XALLB , T_00.REPKE , T_00.REWHR , T_00.HKONT , T_00.ZTMNAIBRX , T_00.GSART ,

T_00.ZTMHOYMNX , T_00.ZTMSBKBNX , T_00.ZTMSHDAYZ , T_00.ZTMMBHZKP , T_01.BAL_SH_CUR ,

T_01.ZTMSIHONP , T_02.SECURITY_ID , T_02.SECURITY_ACCOUNT

FROM

ZTM0108 T_00, ZTM0135 T_01, TRACV_POSCONTEXT T_02

WHERE

T_00.MANDT = '350' AND T_00.BUKRS = 'MC51' AND T_00.ZTMMCSNGX = '200806' AND

T_02.SECURITY_ACCOUNT = '0001' AND T_01.MANDT = '350' AND T_01.BUKRS = T_00.BUKRS AND

T_01.ZTMMCSNGX = T_00.ZTMMCSNGX AND T_01.PARTNER = T_00.REPKE AND T_02.MANDT = '350' AND

T_02.SECURITY_ID = T_00.RANL

Execution Plan

-

---

SELECT STATEMENT ( Estimated Costs = 666 , Estimated #Rows = 72 )

--- 12 HASH JOIN

( Estim. Costs = 666 , Estim. #Rows = 72 )

Estim. CPU-Costs = 37,505,220 Estim. IO-Costs = 663

Access Predicates

-- 9 HASH JOIN

( Estim. Costs = 268 , Estim. #Rows = 51 )

Estim. CPU-Costs = 18,679,663 Estim. IO-Costs = 267

Access Predicates

-- 6 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 38 )

Estim. CPU-Costs = 264,164 Estim. IO-Costs = 25

-- 4 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 27 )

Estim. CPU-Costs = 258,494 Estim. IO-Costs = 25

-- 2 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

( Estim. Costs = 25 , Estim. #Rows = 24 )

Estim. CPU-Costs = 253,454 Estim. IO-Costs = 25

Filter Predicates

1 INDEX RANGE SCAN DIFT_POS_IDENT~SA

( Estim. Costs = 1 , Estim. #Rows = 554 )

Search Columns: 1

Estim. CPU-Costs = 29,801 Estim. IO-Costs = 1

Access Predicates

3 INDEX RANGE SCAN TRACT_POSCONTEXTID

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

5 INDEX UNIQUE SCAN TZPA~0

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

--- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

( Estim. Costs = 242 , Estim. #Rows = 2,540 )

Estim. CPU-Costs = 10,811,361 Estim. IO-Costs = 241

7 INDEX RANGE SCAN ZTM0108~0

( Estim. Costs = 207 , Estim. #Rows = 2,540 )

Search Columns: 3

Estim. CPU-Costs = 9,790,330 Estim. IO-Costs = 207

Access Predicates Filter Predicates

--- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

( Estim. Costs = 397 , Estim. #Rows = 2,380 )

Estim. CPU-Costs = 11,235,469 Estim. IO-Costs = 396

-

---

10 INDEX RANGE SCAN ZTM0135~0

( Estim. Costs = 323 , Estim. #Rows = 2,380 )

Search Columns: 3

Estim. CPU-Costs = 10,288,477 Estim. IO-Costs = 323

Access Predicates Filter Predicates

PRD:

Execution Plan

-

---

SELECT STATEMENT ( Estimated Costs = 209 , Estimated #Rows = 1 )

--- 12 NESTED LOOPS

( Estim. Costs = 208 , Estim. #Rows = 1 )

Estim. CPU-Costs = 18.996.864 Estim. IO-Costs = 207

-- 9 NESTED LOOPS

( Estim. Costs = 120 , Estim. #Rows = 1 )

Estim. CPU-Costs = 10.171.528 Estim. IO-Costs = 119

-- 6 NESTED LOOPS

Estim. CPU-Costs = 27.634 Estim. IO-Costs = 0

-- 4 NESTED LOOPS

Estim. CPU-Costs = 27.424 Estim. IO-Costs = 0

1 INDEX RANGE SCAN TZPA~0

Search Columns: 1

Estim. CPU-Costs = 5.584 Estim. IO-Costs = 0

Access Predicates

--- 3 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Filter Predicates

2 INDEX RANGE SCAN DIFT_POS_IDENT~PT

Search Columns: 1

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

5 INDEX RANGE SCAN TRACT_POSCONTEXTID

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

--- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

( Estim. Costs = 120 , Estim. #Rows = 1 )

Estim. CPU-Costs = 10.143.893 Estim. IO-Costs = 119

7 INDEX RANGE SCAN ZTM0108~0

( Estim. Costs = 119 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 10.142.167 Estim. IO-Costs = 119

Access Predicates Filter Predicates

--- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

( Estim. Costs = 89 , Estim. #Rows = 1 )

Estim. CPU-Costs = 8.825.337 Estim. IO-Costs = 88

-

---

10 INDEX RANGE SCAN ZTM0135~0

( Estim. Costs = 88 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 8.823.742 Estim. IO-Costs = 88

Access Predicates Filter Predicates

Could anyone tell me the reason? I've found note 724545 but not sure.

And, how to read the execution plan?(1 first or 12 first?)

Best Regards,

Robin

[fidel_vales]

Hi,

Before I enter into the possible differences (different data volumen, different statistics, different parameters, different patches ...) there is something I do not get from the query:

SELECT
T_00.RANL , T_00.XALLB , T_00.REPKE , T_00.REWHR , T_00.HKONT , T_00.ZTMNAIBRX , T_00.GSART ,
T_00.ZTMHOYMNX , T_00.ZTMSBKBNX , T_00.ZTMSHDAYZ , T_00.ZTMMBHZKP , T_01.BAL_SH_CUR ,
T_01.ZTMSIHONP , T_02.SECURITY_ID , T_02.SECURITY_ACCOUNT
FROM
ZTM0108 T_00, ZTM0135 T_01, TRACV_POSCONTEXT T_02
WHERE
T_00.MANDT = '500' AND T_00.BUKRS = 'MC51' AND T_00.ZTMMCSNGX = '200806' AND
T_02.SECURITY_ACCOUNT = '0001' AND T_01.MANDT = '500' AND T_01.BUKRS = T_00.BUKRS AND
T_01.ZTMMCSNGX = T_00.ZTMMCSNGX AND T_01.PARTNER = T_00.REPKE AND T_02.MANDT = '500' AND
T_02.SECURITY_ID = T_00.RANL

Is this query send like this to oracle from SAP.

I mean, SAP uses bind variables and here there are no bind variables.

have you typed it in SQL plus?

Not using bind variables give more information to Oracle, and, if you have histograms, for example, a total different plan could be chosen if the data distribution is different.

And, from my experience, usually that is one of the reason of different execution plans

[Former Member]

And, how to read the execution plan?(1 first or 12 first?)

1 is first.

Besides that the formatting of the plans sucks, please try to format as . The plan of the prd system seems to be from a totally different SQL statement.

Regards

Michael

[Former Member]

Hello Michael.

Thank you.

However, the sql statement is same:

QAS:

SQL Statement

-

---

SELECT

T_00.RANL , T_00.XALLB , T_00.REPKE , T_00.REWHR , T_00.HKONT , T_00.ZTMNAIBRX , T_00.GSART ,

T_00.ZTMHOYMNX , T_00.ZTMSBKBNX , T_00.ZTMSHDAYZ , T_00.ZTMMBHZKP , T_01.BAL_SH_CUR ,

T_01.ZTMSIHONP , T_02.SECURITY_ID , T_02.SECURITY_ACCOUNT

FROM

ZTM0108 T_00, ZTM0135 T_01, TRACV_POSCONTEXT T_02

WHERE

T_00.MANDT = '350' AND T_00.BUKRS = 'MC51' AND T_00.ZTMMCSNGX = '200806' AND

T_02.SECURITY_ACCOUNT = '0001' AND T_01.MANDT = '350' AND T_01.BUKRS = T_00.BUKRS AND

T_01.ZTMMCSNGX = T_00.ZTMMCSNGX AND T_01.PARTNER = T_00.REPKE AND T_02.MANDT = '350' AND

T_02.SECURITY_ID = T_00.RANL

Execution Plan

-

---

SELECT STATEMENT ( Estimated Costs = 666 , Estimated #Rows = 72 )

--- 12 HASH JOIN

( Estim. Costs = 666 , Estim. #Rows = 72 )

Estim. CPU-Costs = 37,505,220 Estim. IO-Costs = 663

Access Predicates

-- 9 HASH JOIN

( Estim. Costs = 268 , Estim. #Rows = 51 )

Estim. CPU-Costs = 18,679,663 Estim. IO-Costs = 267

Access Predicates

-- 6 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 38 )

Estim. CPU-Costs = 264,164 Estim. IO-Costs = 25

-- 4 NESTED LOOPS

( Estim. Costs = 25 , Estim. #Rows = 27 )

Estim. CPU-Costs = 258,494 Estim. IO-Costs = 25

-- 2 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

( Estim. Costs = 25 , Estim. #Rows = 24 )

Estim. CPU-Costs = 253,454 Estim. IO-Costs = 25

Filter Predicates

1 INDEX RANGE SCAN DIFT_POS_IDENT~SA

( Estim. Costs = 1 , Estim. #Rows = 554 )

Search Columns: 1

Estim. CPU-Costs = 29,801 Estim. IO-Costs = 1

Access Predicates

3 INDEX RANGE SCAN TRACT_POSCONTEXTID

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

5 INDEX UNIQUE SCAN TZPA~0

Search Columns: 2

Estim. CPU-Costs = 210 Estim. IO-Costs = 0

Access Predicates

--- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

( Estim. Costs = 242 , Estim. #Rows = 2,540 )

Estim. CPU-Costs = 10,811,361 Estim. IO-Costs = 241

7 INDEX RANGE SCAN ZTM0108~0

( Estim. Costs = 207 , Estim. #Rows = 2,540 )

Search Columns: 3

Estim. CPU-Costs = 9,790,330 Estim. IO-Costs = 207

Access Predicates Filter Predicates --- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

( Estim. Costs = 397 , Estim. #Rows = 2,380 )

Estim. CPU-Costs = 11,235,469 Estim. IO-Costs = 396 - 10 INDEX RANGE SCAN ZTM0135~0 ( Estim. Costs = 323 , Estim. #Rows = 2,380 ) Search Columns: 3 Estim. CPU-Costs = 10,288,477 Estim. IO-Costs = 323 Access Predicates Filter Predicates PRD: SQL Statement - SELECT T_00.RANL , T_00.XALLB , T_00.REPKE , T_00.REWHR , T_00.HKONT , T_00.ZTMNAIBRX , T_00.GSART , T_00.ZTMHOYMNX , T_00.ZTMSBKBNX , T_00.ZTMSHDAYZ , T_00.ZTMMBHZKP , T_01.BAL_SH_CUR , T_01.ZTMSIHONP , T_02.SECURITY_ID , T_02.SECURITY_ACCOUNT FROM ZTM0108 T_00, ZTM0135 T_01, TRACV_POSCONTEXT T_02 WHERE T_00.MANDT = '500' AND T_00.BUKRS = 'MC51' AND T_00.ZTMMCSNGX = '200806' AND T_02.SECURITY_ACCOUNT = '0001' AND T_01.MANDT = '500' AND T_01.BUKRS = T_00.BUKRS AND T_01.ZTMMCSNGX = T_00.ZTMMCSNGX AND T_01.PARTNER = T_00.REPKE AND T_02.MANDT = '500' AND T_02.SECURITY_ID = T_00.RANL Execution Plan - SELECT STATEMENT ( Estimated Costs = 209 , Estimated #Rows = 1 )

--- 12 NESTED LOOPS

| ( Estim. Costs = 208 , Estim. #Rows = 1 )

| Estim. CPU-Costs = 18.996.864 Estim. IO-Costs = 207

|-- 9 NESTED LOOPS

| | ( Estim. Costs = 120 , Estim. #Rows = 1 )

| | Estim. CPU-Costs = 10.171.528 Estim. IO-Costs = 119

| |-- 6 NESTED LOOPS

| | | Estim. CPU-Costs = 27.634 Estim. IO-Costs = 0

| | |-- 4 NESTED LOOPS

| | | | Estim. CPU-Costs = 27.424 Estim. IO-Costs = 0

| | | |-----1 INDEX RANGE SCAN TZPA~0

| | | | Search Columns: 1

| | | | Estim. CPU-Costs = 5.584 Estim. IO-Costs = 0

| | | | Access Predicates

| | | --- 3 TABLE ACCESS BY INDEX ROWID DIFT_POS_IDENT

| | | | Estim. CPU-Costs = 210 Estim. IO-Costs = 0

| | | | Filter Predicates

| | | -

---

2 INDEX RANGE SCAN DIFT_POS_IDENT~PT

| | | Search Columns: 1

| | | Estim. CPU-Costs = 210 Estim. IO-Costs = 0

| | | Access Predicates

| | -

---

5 INDEX RANGE SCAN TRACT_POSCONTEXTID

| | Search Columns: 2

| | Estim. CPU-Costs = 210 Estim. IO-Costs = 0

| | Access Predicates

| --- 8 TABLE ACCESS BY INDEX ROWID ZTM0108

| | ( Estim. Costs = 120 , Estim. #Rows = 1 )

| | Estim. CPU-Costs = 10.143.893 Estim. IO-Costs = 119

| -

---

7 INDEX RANGE SCAN ZTM0108~0

| ( Estim. Costs = 119 , Estim. #Rows = 1 )

| Search Columns: 4

| Estim. CPU-Costs = 10.142.167 Estim. IO-Costs = 119

| Access Predicates Filter Predicates

--- 11 TABLE ACCESS BY INDEX ROWID ZTM0135

| ( Estim. Costs = 89 , Estim. #Rows = 1 )

| Estim. CPU-Costs = 8.825.337 Estim. IO-Costs = 88

-

---

10 INDEX RANGE SCAN ZTM0135~0

( Estim. Costs = 88 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 8.823.742 Estim. IO-Costs = 88

Access Predicates Filter Predicates

Only difference is the client.

I see that QAS use index SA on table DIFT_POS_IDENT first, while PRD deal with table TZPA first...Is it the reason?

Best Regards,

Robin

[Former Member]

Ok, i was not aware that TRACV_POSCONTEXT is a view on DIFT_POS_IDENT/TRACT_POSCONTEXT/TZPA

I see that QAS use index SA on table DIFT_POS_IDENT first, while PRD deal with table TZPA first...Is it the reason?

You are right, this could be the reason, but the plans are completely different. On QAS you have hash joins, on PROD you have nested loops.

Can you please compare the CBO stats on both systems, especially for tables DIFT_POS_IDENT and TZPA. In the prod plan there is no Estim. #Rows, i have seen this before, maybe a refresh of the stats will help here.

Regards

Michael

[Former Member]

Did you compare Oracle parameters for both systems?

maybe hash_join_enabled is true for QAS, but false for PROD ?

regards

Edited by: Joe Bo. on Oct 1, 2008 2:59 PM

[Former Member]

Hello Joe,

Thank you for the advice.

However, I couldn't find it in v$parameter and v$system_parameter, neither in note 830576.

Is this parameter no longer used in 10g?

BR,

Robin

[Former Member]

Hello Michael,

brconnect -u <User>/********* -c -f stats -p 2 -t all was run periodically.

How to check if the statistics are not updated correctly?

BR,

Robin

[Former Member]

Hello Robin,

hash_join_enabled is one of those parameters from Oracle 9i that are obsolete in Oracle 10g.

I asked this question just in case, because you hadn't told us your Oracle version so far. (Or did I miss something?)

Sorry for that not very helpful advice.

Comparing Oracle parameters might be a good idea nevertheless; not sure if there is Oracle 10g parameter having a similar effect.

regards

[Former Member]

Hi,

Might be a stupid thing to ask, but have you checked if the indexes as shown in QAS exist in Prodn system as well.

Thanks and Best Regards,

Sunil.

[Former Member]

How to check if the statistics are not updated correctly?

If you run brconnect -f stats regularly and they run without warning/error, you should be fine.

But sometimes you still need to verify single stats manually. You seem to already have the execution plans, just click on the table/index names in the SAPGui and you can see the stats. Another possibility, if you want to check stats for table X, goto ST05 -> enter SQL statement:

select * from X -> Explain -> click on X to see stats

The SQL will not be executed, only an explain is beeing made. Now compare the stats between QAS and PROD.

Regards

Michael