Solution : https://service.sap.com/sap/support/notes/618868 (SAP Service marketplace login required)
Summary :
This SAP Note provides guidance for addressing Oracle database performance issues in an SAP environment. It clarifies that the FAQ cannot resolve all performance problems but serves as a starting point. To analyze performance, the Oracle parameter TIMED_STATISTICS should be set to TRUE. This can be activated dynamically via SQL commands for immediate effect. Performance analysis includes reviewing data from transactions like ST03/N and ST04, where database time should not overly dominate overall response time. The note suggests various diagnostics, such as analyzing CPU versus wait time ratios and detailed session statistics from V$ views, to pinpoint performance bottlenecks and inefficiencies. Access and interpretation of Oracle performance data in SAP R/3 are elaborated upon with specific reference to direct database queries and potential statistical anomalies explained. Overall, this note is a critical resource for enhancing Oracle performance within SAP systems, providing both general guidelines and specific technical directions for database optimization.
Key words :
loading table <table>bb8 buffer overflow loading table <table>, transaction st04 -> detail analysis menu -> file system requests, earlywatch health check empowering workshop uscp10, includes fully-qualified primary key accesses, parameter dbs/ora/array_buf_size defines, operating system-dependent optimization solaris, st04 -> detail analysis menu -> display, detail analysis menu -> oracle session, activated traces activating traces unnecessarily, alter system set timed_statistics = true
Related Notes :
| 1854524 | Performance degradation over time for Oracle >=11.2.0.3, AIX |
| 1672954 | Oracle 11g: Usage of hugepages on Linux |
| 1431798 | Oracle 11.2.0: Database Parameter Settings |
| 1269911 | FAQ: Chained Rows |
| 1266771 | SRM 7.0 Performance Guide |
| 1223360 | Composite SAP Note: Performance optimization during import |
| 1169088 | SRM 6.0 Performance Guide |
| 1164043 | DBSL (Oracle): Maximum buffer size for array operations |
| 1137346 | Oracle 10.2.0: Patches/patch collections for Oracle 10.2.0.4 |
| 1075118 | SAP on HP-UX: FAQ |
| 1047734 | Unrealistic Oracle performance values on Windows/AMD |
| 1037755 | Performance problems with ASSM tablespaces |
| 1036194 | OS timers are not synchronized |
| 1020260 | Delivery of Oracle statistics (Oracle 10g, 11g) |
| 964344 | FAQ: Oracle mutexes |
| 950658 | Huge values shown in READTIM, WRITETIM of V$FILESTAT |
| 912918 | Massive UNDO/REDO generation when you use ASSM |
| 892296 | Enhancements in update statistics in BRCONNECT 7.00/7.10 |
| 881083 | Blocking factors on Oracle-based systems |
| 871735 | Current patch set for Oracle 10.2.0 |
| 871455 | Poor performance when accessing DBA and V$ views |
| 871096 | Oracle 10.2.0: Patches/patch collections for Oracle 10.2.0.2 |
| 869521 | Oracle <= 10g: TM locks with REBUILD ONLINE / CREATE ONLINE |
| 869006 | Composite SAP note: ORA-04031 |
| 868590 | Performance issue with SUN/Oracle 8.1.7 client |
| 857973 | Deleting clients efficiently using Oracle |
| 853576 | Oracle 10g: Performance analysis w/ ASH and Oracle Advisors |
| 842240 | FAQ: Backup strategy of large and highly-available databases |
| 838725 | Oracle dictionary statistics and system statistics |
| 832343 | FAQ: Clustering factor |
| 830576 | Parameter recommendations for Oracle 10g |
| 825653 | Oracle: Common misconceptions |
| 806554 | FAQ: I/O-intensive database operations |
| 805934 | FAQ: Database time |
| 797629 | FAQ: Oracle histograms |
| 793113 | FAQ: Oracle I/O configuration |
| 789011 | FAQ: Oracle memory areas |
| 787533 | Bad performance/ system lockup due to enqueue waits |
| 771929 | FAQ: Index fragmentation |
| 767414 | FAQ: Oracle latches |
| 766827 | Composite SAP note: Performance SCM 4.0 |
| 766349 | FAQ: Oracle SQL optimization |
| 762808 | FAQ: Keep pool |
| 750205 | High memory usage with AIX5.2/5.3 and Oracle9.2 |
| 745639 | FAQ: Oracle enqueues |
| 723894 | Brconnect: Create the statistics for all columns. |
| 717484 | FAQs: Oracle statspack |
| 716000 | Database monitor for Oracle 9/Oracle RAC SAP Release 6.X |
| 712624 | High CPU consumption by Oracle |
| 706478 | Preventing Basis tables from increasing considerably |
| 705608 | Database monitor for Oracle 9/Oracle RAC |
| 703035 | Objects with ERROR status in buffer |
| 689818 | High Paging Rates during Backup |
| 659946 | FAQ: Temporary tablespaces |
| 651060 | FAQ: Oracle Parallel Execution |
| 650631 | Composite SAP note: ORA-01003 |
| 648203 | Database objects with INVALID status |
| 619188 | FAQ: Oracle wait events |
| 618861 | Composite SAP Note: ORA-01013 |
| 596420 | System standstill during deadlock (ORA-60) |
| 592393 | FAQ: Oracle |
| 588668 | FAQ: Database statistics |
| 582427 | Error due to inconsistent Oracle DDIC |
| 579462 | Runtime parameter of the statistics collection |
| 567745 | Composite note BW 3.x performance: DB-specific setting |
| 562403 | FAQ: Oracle Net |
| 558746 | Better Oracle Data Dictionary BW Performance |
| 539921 | Current patch set for Oracle 9.2.0 |
| 521264 | Hang situations |
| 500340 | FAQ: LOBS |
| 421795 | Report SAP_ANALYZE_ALL_INFOCUBES |
| 378509 | Oracle DB parameter setting for APO |
| 354080 | Note collection for Oracle performance problems |
| 332677 | Rebuilding fragmented indexes |
| 192658 | Setting parameters for BW systems |
| 184905 | Collective note Performance BW 2.0 |
| 176754 | Problems with CBO and RBO |
| 160063 | Oracle DB parameter setting for R/3 3.x and Oracle8/Oracle9 |
| 138639 | Poor DML performance with Oracle DDIC statistics |
| 135048 | Long runtimes statements w/ ROWNUM <= condition |
| 132861 | CBO: Statistics creation with SAPDBA or BRCONNECT |
| 124361 | Oracle parameterization (R/3 >= 4.x, Oracle 8.x/9.x) |
| 122718 | CBO: Tables with special treatment |
| 47239 | Behavior of the table buffers |
| 20071 | Permanent lock after connection termination |
| 4162 | Missing "end backup" |