Excessive DB Cpu Usage After OLTP Tables and Indexes Compressed

[Former Member]

Dear Gurus,

Our system info:

    ECC6.0 + Oracle 11.2.0.3 + AIX5.3 64bit

    IBM P561 16CPU(32 Cores), 64GB RAM

     We implemented Oracle advanced Compression Option (ACO) in our PRD DB at 05/05/2014.

After that , we found the average DB CPU utilizatoin is more that 70% all day. It's very abnormal!

Ues topas to check and can found that every active process alwasys costs about 10% CPU Utilization.

There is no specail process costs remarkable DB CPU Utilization. Please see the screenshot attached.

Use the below command, can see:

$ ps -ef  |  grep ora | wc -l | grep -v grep

     376

Dose anyone know the issue?

Best Regards,

Jason

[former_member188883 · Accepted solution]

Hi Jason,

Post implementation of ACO, database has additional tasks to perform compression of data before storing into the tables. This compression invokes additional CPU usage based on how many transactions are being executed perday on the tablespace or table on which compression has been activated.

Have you implemented compression at tablespace level or table column level.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Deepak Kori

   I'm very happy to receive your reply. Thanks for you help.

We implemented ACO according to SAP Note 1436352 - Oracle 11g Advanced Compression for SAP Systems.

We compressed 22 OLTP tables and 60 Indexes.

More details, Please see the list:

DBSERVER:oraprd> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 16 10:26:20 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 2000 linesize 1000

SQL> SELECT

  2    TABLE_NAME

  3   ,COMPRESSION

  4   ,COMPRESS_FOR

  5  FROM DBA_TABLES

  6  WHERE OWNER='SAPSR3' AND COMPRESSION ='ENABLED'

  7  ORDER BY 1 DESC;

TABLE_NAME             COMPRESS COMPRESS_FOR

------------------------------ -------- ------------

ZFI_020_DATA              ENABLED  OLTP

VBUP                           ENABLED  OLTP

VBUK                           ENABLED  OLTP

VBPA                           ENABLED  OLTP

VBKD                           ENABLED  OLTP

VBFA                           ENABLED  OLTP

VBEP                           ENABLED  OLTP

VBAP                           ENABLED  OLTP

VBAK                           ENABLED  OLTP

S033                             ENABLED  OLTP

MSEG                           ENABLED  OLTP

MKPF                            ENABLED  OLTP

LIKP                              ENABLED  OLTP

FAGL_SPLINFO_VAL     ENABLED  OLTP

FAGL_SPLINFO             ENABLED  OLTP

FAGLFLEXA                  ENABLED  OLTP

EKBE                           ENABLED  OLTP

COEP                           ENABLED  OLTP

BSIS                             ENABLED  OLTP

BSID                             ENABLED  OLTP

BKPF                           ENABLED  OLTP

ADRC                           ENABLED  OLTP

22 rows selected.

SQL> SELECT

  2    INDEX_NAME

  3   ,COMPRESSION

  4  FROM DBA_INDEXES

  5  WHERE OWNER='SAPSR3' AND COMPRESSION ='ENABLED'

  6  ORDER BY 2 DESC;

INDEX_NAME                     COMPRESS

------------------------------ --------

MSEG~S                         ENABLED

MSEG~R                         ENABLED

MSEG~M                         ENABLED

MSEG~0                          ENABLED

BSIS~1                            ENABLED

BSIS~0                            ENABLED

COEP~1                          ENABLED

COEP~0                          ENABLED

STXL~0                            ENABLED

SOC3~0                           ENABLED

LTAP~V                           ENABLED

LTAP~M                          ENABLED

LTAP~0                           ENABLED

KOCLU~0                        ENABLED

EDI40~0                          ENABLED

EBAN~SRV                     ENABLED

EBAN~B                         ENABLED

EBAN~4                          ENABLED

EBAN~3                          ENABLED

EBAN~2                          ENABLED

EBAN~1                          ENABLED

EBAN~0                          ENABLED

DBTABLOG~TAB             ENABLED

DBTABLOG~0                  ENABLED

COEPL~1                        ENABLED

COEPL~0                        ENABLED

CMFP~0                          ENABLED

CKIT~0                            ENABLED

CKIS~0                            ENABLED

CDHDR~0                        ENABLED

CDCLS~0                        ENABLED

BSAS~1                          ENABLED

BSAS~0                          ENABLED

BSAD~5                          ENABLED

BSAD~4                          ENABLED

BSAD~1                          ENABLED

BSAD~0                          ENABLED

BDCPS~1                        ENABLED

BDCPS~0                        ENABLED

BDCP~POS                     ENABLED

BDCP~1                          ENABLED

BDCP~0                          ENABLED

BALDAT~0                      ENABLED

AFVV~0                          ENABLED

AFVC~4                          ENABLED

AFVC~3                          ENABLED

AFVC~2                          ENABLED

AFVC~1                          ENABLED

AFVC~0                          ENABLED

AABLG~0                        ENABLED

RFBLG~0                        ENABLED

LIPS~0                            ENABLED

GLPCA~7                        ENABLED

GLPCA~3                        ENABLED

GLPCA~2                        ENABLED

GLPCA~1                        ENABLED

GLPCA~0                        ENABLED

ACCTIT~1                        ENABLED

ACCTIT~0                        ENABLED

ACCTCR~0                      ENABLED

60 rows selected.

Best Regards,

Jason

[Former Member · Accepted solution]

Hi,

I could see few of the cluster tables (which are not eligible) with compression enabled which shouldn't be.

Decompress the cluster tables first, check SAP note 1847870 - Oracle 11g: Advanced Compression - Known issues and solution.

Regards,

Nick Loy

[former_member188883 · Accepted solution]

Hi Jason,

As per SAP note 1436352 - Oracle 11g Advanced Compression for SAP Systems.

Point (4) Performance:

1. Overall system throughput is not negatively impacted and may improve.
   Should you experience very long runtimes (i.e. 5-10 times slower) for certain operations (like mass inserts in BW PSA or ODS tables/partitions) then you should set the event 10447 level 50 in the spfile/init.ora. This will reduce the overhead for insertion into compressed tables/partitions.
   • Amount of redo data generated can be up to 30% higher
   • Up to 60% less physical reads from the database
   • Up to  5% less physical writes to the database
   • Up to 10% improvement in database buffer hit rate
   • No impact on cpu utilization for regular transaction workload.
     Specific operations such as R3load imports (when not using the loadprocedure fast option) or client copies may run slower.

There is an expected increase in CPU utilization which need deep analysis and fine tuning.

Suggestion here would be to begin with SAP note

1847870 - Oracle 11g: Advanced Compression - Known issues and solution

712624 - High CPU consumption by Oracle

Follow relevant guidelines on patch application and parameter settings.

Post this you may require further monitoring and analysis of performance on these tables.

Regards,

Deepak Kori

[Former Member · Accepted solution]

Hi Nick,

      Thanks for your help.

      I have checked our 22 OLTP tablses via SE11 one by one.

All of them are transparent tables.

Best Regards,

Jason

[ACE-SAP · Accepted solution]

Hello Jason,

Indeed all the tables are transparent... but you should not compress table VBAP because it has more than 255 columns.

Have you tried analyzing AWR report to find which queries are heavily consuming CPU resources ?

Best regards

1436352 - Oracle Database 11g Advanced Compression for SAP Systems

Restrictions:

Tables with more than 255 columns are not compressed. Do not compress tables with more than 255 columns.

[fidel_vales · Accepted solution]

Hi,

you should check what statements are eating your CPU.

But, as you are talking about having compressed some tables, execute the script Segments_Tables_CriticalCompressedTables_11g+ from note 1438410 and, if there is ANY table reported, then you should "uncompress it"

in addition, do you have at least, the SBP from February this Year?

If not, it is indicated on the HOTNEWS note 1847870 a long list of critical issues with table compression

[Former Member · Accepted solution]

Hi Deepak,

   Thanks for your help.

We didn't found SAP response time and certain operation become slow.

Just found the DB CPU utilizaion is between 69% to 99.9%.

Can't identified particular process which occupying very high CPU resorese.

I send an incident to sap and told me to make some tunings as below:

-------------------------------------------------------------------------------

please make some change to the parameter settings:

PARAMETER NAME                             WHAT YOU NEED TO DO

_ktb_debug_flags                                    add with value "8"

_fix_control (7168184)                             add with value "7168184:OFF"

_fix_control (6120483)                             add with value "6120483:OFF"

_securefiles_concurrency_estimate         add with value "50"

optimizer_index_caching                        delete

Then please monitor if the CPU usage is still very high.

---------------------------------------------------------------------------------

Do you think the solutoin above can work?

You're right. I do really need deep analysis and fine tuning.

I'll try my best.

I uploaded two attachment.

《SAP PRD DB AWR》 http://pan.baidu.com/s/1bnvlP2v

《DB Performance Overview and Response Time》 http://pan.baidu.com/s/1o6r1BRk

Help you may give me more clews after reading them at your convenient time.

Warm Regards,

Jason

[Former Member · Accepted solution]

Hi Yves KERVADEC,

  Thanks for your reply.

  I have checked and found that VBAP only has 216 columns.

  SQL> SELECT COUNT(*) FROM ALL_TAB_COLUMNS WHERE OWNER='SAPSR3' AND TABLE_NAME='VBAP';

        COUNT(*)

  ----------

   216

Best Regards,

Jason

[Former Member · Accepted solution]

Hi Fidel Vales,

   Thank you for your help.

     I have executed 'Segments_Tables_CriticalCompressedTables_11g+' in the attacment of sap note 1438410. The exectuion result is: 'no rows selected'.

    We didn't apply the latest SBP only if I can ferret out the root reason about this issue.

The latest SBP is: SBP 11.2.0.3.10 201406 for IBM AIX on POWER Systems.

  I executed 'Configuration_Patches_SBPLevel_11g+.txt' in note 1438410 and can see:

   VERSION    SBP_IMPLEMENTATION_ SBP_REGISTRY        SBP_FIX_CONTROL_MIN SBP_FIX_CONTROL_MAX

  ---------- ------------------- ------------------- ------------------- -------------------

   11.2.0.3.0        n/a              n/a                 n/a                 n/a

SQL> select * from dba_registry_history;

  ACTION_TIME                         ACTION          NAMESPACE  VERSION            ID BUNDL COMMENTS

----------------------------------- --------------- ---------- ---------- ---------- ----- -------------------------

05-MAY-13 10.06.50.440102 AM        VIEW RECOMPILE                           8289601       view recompilation

05-MAY-13 10.06.50.458222 AM        UPGRADE         SERVER     10.2.0.5.0                  Upgraded from 10.2.0.2.0

05-MAY-13 12.26.35.480744 PM        VIEW INVALIDATE                          8289601       view invalidation

05-MAY-13 12.26.35.512614 PM        UPGRADE         SERVER     11.2.0.3.0                  Upgraded from 10.2.0.5.0

05-MAY-13 12.27.57.509153 PM        APPLY           SERVER     11.2.0.3            0 PSU   Patchset 11.2.0.2.0

Do you have any more furthur suggestions?

BTW,I uploaded two attachments:

《SAP PRD DB AWR》 http://pan.baidu.com/s/1bnvlP2v

《DB Performance Overview and Response Time》 http://pan.baidu.com/s/1o6r1BRk

Hope you may give me more clews after reading them at your convenient time.

Best Regards,

Jason

[fidel_vales · Accepted solution]

Hi,

Ok, for one it is good that you do not have any compressed table that should not be compressed.

For other it is very bad that you do not have any SBP installed for 11.2.0.3 and more if you are using advance table compression. There are several known bugs mentioned on the SAP HOTNEWS note 1847870. Having the SBP mentioned there is a requirement for using advance table compression.

From my point of view, you must install the mentioned SBP or uncompress.

the links you provide go to a Chinese website all in Chinese, so I do not know what to press. There is a button on the top that might mean download but it does not work on the version of IE that I have

[Former Member · Accepted solution]

Hi Fidel Vales,

    Thanks for you professinal and quick reply.

There are still some questions make me confused.

    We can apply 1406 or 1402 SBP, We also can update 11.2.0.3 to 11.2.0.4, Which solution is the best reliable one?

(# SBP-SAP11203P_1406, SBP-SAP11203V3P_1402 , Oracle 11.2.0.4)

I think We can apply 1402 first and to see whether the issue(huge DB CPU consumption) could be resolved.

   Additonnally, Our QAS was resotred with PRD DB backup. The OLTP tables and indexes are also compressed in QAS.

    QAS DB CPU utilization is very common (nearly 0% usr, 100% idle).  Surely, there are no business activities in QAS.   It's easy to understand.  But PRD DB CPU is very busy whole day even in a very low business activity period(includes Jobs) in PRD.

   To compare with PRD DB, QAS is so different?!

   To apply SBP in QAS, it's hard to reflect/simulate the scene in PRD DB.

   I still can't have clear/precise evidence to prove/support my SBP updating plan.

In SAP Note 712624 - High CPU consumption by Oracle

    '32. Oracle 11.2.0.2, 11.2.0.3: OLTP Table Compression with ASSM', We didn't use ASSM and RAC, just single Oracle instacne.

In  SAP Note 1847870 - Oracle 11g - Advanced Compression - Known issues and solution

     Still can't find the case/description like ours.

Regards,

Jason

[fidel_vales · Accepted solution]

where to go depends on you company "policy", some companies are "more" afraid than others.

I would go for 11.2.0.4 (plus current SBP for this version too) as it is the latest patch set for 11g, and from January next year extended support will be offered for free for one year only to this patch set.

on the AWR report you attached the top statement consuming CPU do not seem to suffer a compression bug but lack of proper index support for the queries.

Basically you are asking us to guess what happens in your system.
Of course, without proper information it is not possible to give you a good answer but only guesses and it might be that one hits.

I still can't have clear/precise evidence to prove/support my SBP updating
plan.

You do not have clear evidence of anything. Only that the CPU in your DB server has increased, and the information you provided to use is also not good.

you take the screenshot, you have the oracle process, then map it to an SAP workprocess and take a look what it is doing.

If you want clear/precise evidence go to a proper tuning training or open an incident and provide access at OS level and SAP level and then it might be that someone

[Former Member]

I would go for 11.2.0.4 (plus current SBP for this version

Agree with Fidel...It's always better to go with latest patch and the bug fixes.

Regards,

Nick Loy