Solution : https://service.sap.com/sap/support/notes/766349 (SAP Service marketplace login required)
Summary :
SAP Note discusses the criticality of SQL optimization, outlining methods to identify, assess, and enhance resource-intensive SQL statements to improve system performance. Detailing the identification of statements causing high I/O and CPU demands through analysis tools like SAP's ST04 Transaction and other monitoring services. It highlights the necessity for regular SQL statement reviews for efficient system functioning, tackling resultant complications like buffer busy waits. Information on executed SQL statements is accessible via the Shared Cursor and can be further drilled down by transaction records for optimization. Techniques include adjusting buffer reads, disk reads, and understanding cursor behaviors, alongside examining ABAP source code alignment and optimizations therein, ensuring better data management and system efficiency without compromising security.
Key words :
select ltap~lgnum ltap~tanum ltap~tapos ltap~vltyp ltap~nltyp ltak~bwlvs ltak~bdatu ltak~bzeit, index rowid ltap index range scan ltap~0 sort join table access, selecting transaction st04-> detail analysis menu-> sql request, index rowid ltak index range scan ltak~, ltak~tanum = ltap~tanum, sap j2ee engine visual administrator activate, ltak~lgnum = ltap~lgnum, sap early watch alert services, single highly processing-intensive sql statement, previously barely usable system function
Related Notes :
| 1601951 | Self Service 'SQL Statement Tuning' - Prerequisites and FAQ |
| 1128663 | Oracle 10g: SQL trace using auditing |
| 1100926 | FAQ: Network performance |
| 1093107 | Costly SQL statement due to synchronous matchcodes |
| 1037755 | Performance problems with ASSM tablespaces |
| 913247 | Performance problems due to open changes |
| 912620 | FAQ: Oracle indexes |
| 896717 | Problems with tables in ASSM tablespaces |
| 881083 | Blocking factors on Oracle-based systems |
| 853576 | Oracle 10g: Performance analysis w/ ASH and Oracle Advisors |
| 832343 | FAQ: Clustering factor |
| 825653 | Oracle: Common misconceptions |
| 806554 | FAQ: I/O-intensive database operations |
| 797629 | FAQ: Oracle histograms |
| 793113 | FAQ: Oracle I/O configuration |
| 772497 | FAQ: Oracle Hints |
| 771929 | FAQ: Index fragmentation |
| 767414 | FAQ: Oracle latches |
| 762808 | FAQ: Keep pool |
| 756335 | Statistics in tables w/ heavily fluctuating volumes of data |
| 750631 | Approximations for cost calculation of the CBO |
| 745639 | FAQ: Oracle enqueues |
| 724545 | Adjusting the CBO statistics manually using DBMS_STATS |
| 723879 | Explain: System displays an incorrect access path |
| 712624 | High CPU consumption by Oracle |
| 706478 | Preventing Basis tables from increasing considerably |
| 651060 | FAQ: Oracle Parallel Execution |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |
| 613872 | Oracle traces with ORADEBUG |
| 602667 | Perform a SQL Trace on an oracle utility or external program |
| 588668 | FAQ: Database statistics |
| 563359 | Performance optimization for tables with LOB columns |
| 502782 | Composite SAP Note ora-4030 |
| 354080 | Note collection for Oracle performance problems |
| 329914 | Expensive SQL statements composite note |
| 191492 | Performance: Customer developments in MM/WM |
| 187906 | Performance: Customer developments in PP and PM |
| 185530 | Performance: Customer developments in SD |
| 176754 | Problems with CBO and RBO |
| 135048 | Long runtimes statements w/ ROWNUM <= condition |
| 132861 | CBO: Statistics creation with SAPDBA or BRCONNECT |
| 131372 | CBO: Tuning of expensive SQL statements |
| 130480 | Database Hints in Open SQL for Oracle |
| 122718 | CBO: Tables with special treatment |
| 48230 | Parameters for the SELECT ... FOR ALL ENTRIES statement |
| 3807 | Error messages regarding rollback and undo segments |