Below is the standard recommended checklist from SAP related to Oracle database performance for SAP application.
It mentioned through below point wise with related SAP Note in details.
1) Install latest Bundle Patch.
The most recent Bundle Patch of SBP and an actual Bundle Patch of GI,EXA,ODA is always located on Marketplace or listed in the below notes
1431752 – Oracle 10.2.0: Patches/Patch Collections for 10.2.0.5
1431795 – Oracle 11.2.0: Patches/Patch collections for 11.2.0.1
1503709 – Oracle 11.2.0: Patches / Patch collections for 11.2.0.2
1631931 – Oracle 11.2.0: Patches / Patch collections for 11.2.0.3
1949250 – Oracle 11.2.0: Patches / Patch collections for 11.2.0.4
1915316 – Database: Patches for 12.1.0.2
2507228 – Database: Patches for 12.2.0.1
2660044 – Patches for 18c: Database
2799920 – Patches for 19c: Database
You can use the below SQL command to get an info about the current SBP/Patch stand of the database
SQL> select COMMENTS,ACTION,VERSION,ACTION_TIME from DBA_REGISTRY_HISTORY order by action_time;
NOTE: As of Oracle Database Release 12c, use DBA_REGISTRY_SQLPATCH instead!
SQL> select DESCRIPTION,ACTION,STATUS,ACTION_TIME from DBA_REGISTRY_SQLPATCH;
2) Compare result of ‘opatch lsinventory’ with above selects on DBA_REGISTRY_HISTORY / DBA_REGISTRY_SQLPATCH
In case ‘opatch lsinventory’ shows Bundle that is not visible in above Oracle views, the Bundle Patch installation is incomplete!
Possible cause an solution:
– Bundle Patch installation failed -> SOLUTION: Follow SAP note 2184669 Troubleshooting 12.1 catsbp post-installation issues
OR
– catsbp or datapatch was not executed -> SOLUTION:Follow SAP Knowledge Base Article 2125248 Is it necessary to execute catsbp.sql/catbundle.sql/postinstall.sql
3) Run the ‘Automated Parameter Check’ script and perform the necessary necessary changes if needed.
1171650 – Automated Oracle DB parameter check
4) Make sure the Oracle Dictionary and System statistics are collected:
838725 – Oracle dictionary statistics and system statistics
5) Re-initialize DBSTATC and re-introduce customized changes:
403704 – BRCONNECT – enhanced function for Oracle DBA
The customized changes is expected that they are properly documented as this reset of DBSTATC is recommended after each upgrade.
6) Import the pre-configured statistics if not yet done
1020260 – Delivery of Oracle statistics (Oracle >= 10g)
7) If the problem is listed in the below note perform the necessary steps to solve it
176754 – Problems with CBO and RBO
8) Check ‘Avg.Proc:Time’,’O CPU Time’,’O DB Time’ values in ST03 transaction where the most time was spent.
If most time is spent on O CPU Time, the issue is not caused by the Oracle Database, so the reason must reside somewhere else.
9) If a specific transaction,program or client is affected, use transaction STAD to get more details about the characteristics and execution times.
When available, best option to quickly check for more clues.
10) If the whole system is slow, use ST01 to trace e.g. DB Access (SQL Trace), Table Buffer Trace, or Kernel Functions.
11) Create 3 AWR/ASH reports in HTML form covering the time frame before/during/after the issue happens
1738665 – ASH and AWR report creation in SQLPLUS and DBACOCKPIT
With this we can compare the database execution times/different characteristics and may find meaningful conclusions on the cause if Database is the responsible.
For more information about Oracle statistics it is important to read the note below:
588668 – FAQ: Database statistics
In case there performance problems happens during SAP Upgrade or Installation, please refer to below SAP Knowledge Base Articles:
1919276 Performance issues when running a SAP Upgrade
1918774 Performance issues when running an SAP Installation or System Copy