Performance problem after upgrade to 11.2.0.4 and COMPRESS for OLTP on view CAUFV

[mario_bisonti2]

Hallo.

We are upgrading our SAP ECC 6.0 systems.

After migration from:
windows 2003 + Oracle 11.2.0.3 + NO COMPRESSION

to

windows 2008 + Oracle 11.2.0.4 + COMPRESS FOR OLTP

I have jobs that use the view CAUFV very very slow

Before the upgrade near 10000 secs. now on 33000 not yet finished.

The bad select is:

SELECT

  *

FROM

  "CAUFV"

WHERE

  "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND

  "LOEKZ"=:A6 AND "IDAT2"=:A7

Do anyone know any issue on Oracle with the compression activated?

What could I check?

thanks a lot for your help.

Mario

[stefan_koehler · Accepted solution]

Hi Mario,

> Do anyone know any issue on Oracle with the compression activated?

It is probably not an issue / bug of the compression itself (e.g like this one ), but rather based on the patch set upgrade and physically changed data structure (+ stats). The probable error is located in the generated / used execution plan i guess.

The easiest way would be to run a SQL monitoring report for this particular SQL ID and then provide all the corresponding object and statistic data with SQLd360.

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<SQL_ID>', type => 'HTML', report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

SQLd360 can be downloaded from Github and here is the documentation: SQLd360, SQL diagnostics collection made faster | Mauro Pagano

Regards

Stefan

[mario_bisonti2 · Accepted solution]

Thanks Stefan.

I will try to investigate using the tool that you mentioned (if I am able...)

Furthermore, I executed:

brconnect -u / -c -f stats -t system_stats

brconnect -u / -c -f stats -t oradict_stats

brconnect -c -u / -f stats -t all -f collect -p 4

But the problem persist.

Other query that use CAUFV have the same problem.

I found this note "176754 - Problems with CBO and RBO"

Point 12. CBO: The optimum index is not used in View or Join with OR operations

There is a mentioned on CAUFV

But..how colud I solve?

And why isn't the same problem on 11.2.0.3

Thanks a lot!

Mario

[stefan_koehler · Accepted solution]

Hi Mario,

> But..how colud I solve? And why isn't the same problem on 11.2.0.3

We / you can only answer these questions with the requested information. The BR*connect runs were more like a shot in the dark as we / you don't know the root cause of your issue.

However in general the optimizer evolves from patchset to patchset (or from one-off-patch to one-of-patch, etc.) and you also introduced advanced compression which can have a huge impact on the physical object structure. All these are major influencing factors on the CBO (and runtime engine)

Regards

Stefan

[mario_bisonti2 · Accepted solution]

Do you think that, to bypass the compression issue, I could decompress AUFK and AFKO so I can exclude that the problem is due on compression action?

How to decompress the tables ?

Are the following commands right?

brspace -u / -c force -f tbreorg -a dtab -t AUFK

brspace -u / -c force -f tbreorg -a dtab -t AFKO

Thanks!

[stefan_koehler · Accepted solution]

Hi Mario,

the same approach as with the "shot in the dark" BR*connect runs. Why not analyzing the problem first before applying solutions blindly? It is just guessing and costs you a lot of more time than finding the root cause and fixing it systematically.

Regards

Stefan

[mario_bisonti2 · Accepted solution]

Ok...I try.

Could you support me?

I find SqlID using transaction ST04

Furthermore, I am on Windows, Oracle is in Bundle with SAP so I haven't OracleTuning Pack.
I suppose that I have to use sqld360 with the option 'N'

Thanks a lot

[stefan_koehler · Accepted solution]

Hi Mario,

> I find SqlID using transaction ST04

Yes, you can find it in ST04(old), DBACOCKPIT or ST05 (EXPLAIN PLAN).

> Furthermore, I am on Windows, Oracle is in Bundle with SAP so I haven't OracleTuning Pack.

The diagnostic + tuning pack is included, if you have purchased your Oracle database license by SAP. For more details please check SAPnote #740897.

Regards

Stefan

[mario_bisonti2 · Accepted solution]

Ok.

Step by step I will arrive..

I don't understand if I have to do this:

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL C:\Temp\report_sql_monitor.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => 'fzzczryngjtu4', type => 'HTML', report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

but it produces an empty report

or this:

cd sqld360-master

sqlplus / as sysdba

SQL> @sqld360.sql fzzczryngjtu4 T 31

that it has worked very much and it has produced a .zip file

Thanks a lot for your patience

[stefan_koehler · Accepted solution]

Hi Mario,

the PL/SQL procedure "DBMS_SQLTUNE.report_sql_monitor" only works, if the SQL is running (or the corresponding data is not aged out of shared pool yet). In general it is something like a real time SQL monitoring feature where you can see if the CBO has done something wrong (any many other work load related data) while the SQL is running.

So just start your query once again, wait 5 to 10 minutes and then run the procedure again.

Regards

Stefan

[mario_bisonti2 · Accepted solution]

Ah ok.
I will run it.

But I note this:

the statement is:

SELECT

  *

FROM

  "CAUFV"

WHERE

  "MANDT"=:A0 AND "PLNBEZ"=:A1 AND "GLTRP" BETWEEN :A2 AND :A3 AND "WERKS"=:A4 AND "GETRI"=:A5 AND

  "LOEKZ"=:A6 AND "IDAT2"=:A7

In the not compressed and not upgraded system the plan is:

SELECT STATEMENT ( Estimated Costs = 439 , Estimated #Rows = 0 )

       7 FILTER

         Filter Predicates

           6 NESTED LOOPS

               4 NESTED LOOPS

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

                 Estim. CPU-Costs = 15.901.779 Estim. IO-Costs = 436

                   2 TABLE ACCESS BY INDEX ROWID AFKO

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

                     Estim. CPU-Costs = 15.898.090 Estim. IO-Costs = 436

                     Filter Predicates

                       1 INDEX SKIP SCAN AFKO~P

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

                         Search Columns: 2

                         Estim. CPU-Costs = 15.847.061 Estim. IO-Costs = 431

                         Access Predicates Filter Predicates

                   3 INDEX UNIQUE SCAN AUFK~0

                     Search Columns: 2

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

                     Access Predicates

               5 TABLE ACCESS BY INDEX ROWID AUFK

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

                 Filter Predicates

Instead , in the compressed and upgraded system the plan is:

SELECT STATEMENT ( Estimated Costs = 705 , Estimated #Rows = 0 )

       6 FILTER

         Filter Predicates

           5 NESTED LOOPS

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

             Estim. CPU-Costs = 327.264.588 Estim. IO-Costs = 667

               3 NESTED LOOPS

                 ( Estim. Costs = 705 , Estim. #Rows = 41 )

                 Estim. CPU-Costs = 327.264.588 Estim. IO-Costs = 667

                   1 TABLE ACCESS FULL AUFK

                     ( Estim. Costs = 623 , Estim. #Rows = 41 )

                     Estim. CPU-Costs = 326.479.485 Estim. IO-Costs = 586

                     Filter Predicates

                   2 INDEX UNIQUE SCAN AFKO~0

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

                     Search Columns: 2

                     Estim. CPU-Costs = 9.021 Estim. IO-Costs = 1

                     Access Predicates

               4 TABLE ACCESS BY INDEX ROWID AFKO

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

                 Estim. CPU-Costs = 19.383 Estim. IO-Costs = 2

                 Filter Predicates

So TABLE FULL ACCESS AUFK could be the problem?

What could I do?

Thanks

[stefan_koehler · Accepted solution]

Hi Mario,

once again. Please provide the requested information - the NL join order changed as well. We need to know why this change happened and this can only be answered with the requested information.

Regards

Stefan

[mario_bisonti2 · Accepted solution]

You are right but I am not able to capture that session id

I ran

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL C:\Temp\report_sql_monitor_list.htm

SELECT DBMS_SQLTUNE.report_sql_monitor_list (type => 'HTML', report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

but in the list, there isn't my sqlid=fzzczryngjtu4 even if I see from ST04

I have been able to use SQLd360  and I have a sqld360_tes_fzzczryngjtu4_stesvv01_20150612_1147.zip

but where could I find in this zip extracted the information that could help to solve the issue?

Thanks.

[Former Member]

Hello Mario , good afternoon Stefan !

Mario , I think you have missed this stats provided by SAP (these 2 tables should have special stats)

1020260 - Delivery of Oracle statistics (Oracle >= 10g)

Best Regards , Sergo.

[stefan_koehler]

Hi Sergo,

great hint - thanks for mentioning. I received the SQLd360 output from Mario last Friday and it is custom code (Z*). The execution plan (+ predicates) looks like this.

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |        |       |   705 (100)|          |
|*  1 |  FILTER                       |        |        |       |            |          |
 |   2 |   NESTED LOOPS                |        |      1 |  1173 |   705   (6)| 00:00:04 |
|   3 |    NESTED LOOPS               |        |     41 |  1173 |   705   (6)| 00:00:04 |
|*  4 |     TABLE ACCESS FULL         | AUFK   |     41 | 14760 |   623   (6)| 00:00:04 |
|*  5 |     INDEX UNIQUE SCAN         | AFKO~0 |      1 |       |     1   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| AFKO   |      1 |   813 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(:A3>=:A2)
4 - filter(("T0002"."IDAT2"=:A7 AND "T0002"."WERKS"=:A4 AND
            "T0002"."LOEKZ"=:A6 AND "T0002"."MANDT"=:A0))
5 - access("T0001"."MANDT"=:A0 AND "T0001"."AUFNR"="T0002"."AUFNR")
6 - filter(("T0001"."PLNBEZ"=:A1 AND "T0001"."GETRI"=:A5 AND
            "T0001"."GLTRP">=:A2 AND "T0001"."GLTRP"<=:A3))

So the delivered custom statistics for AUFK.PSPEL, DISTINCT_KEYS of AUFK~D and AFKO.AUFNT, AFKO.PRONR and DISTINCT_KEYS of AFKO~3 do not apply here in this case.

Regards

Stefan

[mario_bisonti2]

Thanks a lot for your support.

I reinstalled the system with no compression and the jobs are faster near half time before the upgrade...

So, I am thinking that "COMPRESS FOR OLTP" is no too good for my scenario.

thanks a lot

Mario