Solution : https://service.sap.com/sap/support/notes/871455 (SAP Service marketplace login required)
Summary :
SAP Note addresses performance issues accessing Oracle Dictionary views like DBA_SEGMENTS, USER_SEGMENTS, and various partition and index views, leading to slow DB02 or ST04 operations and external monitoring tools reporting poor performance. To mitigate these issues, SAP recommends different methods per Oracle release. For Oracle 8.1.7, users should refer to Note 519407 and download necessary scripts from the SAP Service Marketplace, ensuring the application of the latest scripts post-patch installation. For Oracle 9.2.0.5 or higher, SAP suggests ensuring statistics creation for Oracle DDIC in BW systems only, and implementing or undoing changes via provided scripts based on system checks of current configurations.
Key words :
dba_segments / user_segments dba_extents / user_extents dba_free_space / user_free_space dba_free_space_coalesced / user_free_space_coalesced dba_data_files / dba_temp_files dba_tab_partitions / user_tab_partitions dba_ind_partitions / user_ind_partitions dba_ind_columns / user_ind_columns dba_lobs / user_lobs dba_undo_extents dba_indexes / user_indexes, external monitoring tools show poor performance, connect <sapuser>/<password>drop synonym <sapuser>, sql$oracle_home/rdbms/admin/prvtspad, oracle 10g / 11g make generally, sql@<oracle_home>/rdbms/admin/catspace, oracle data dictionary views listed, $oracle_home/rdbms/admin/dbmsspc, oracle shared cursor cache, evaluate full table scans
Related Notes :
| 1110749 | Performance on DBA_EXTENTS/DBA_FREE_SPACE_COALESCED |
| 1102676 | SAP_USER_EXTENTS/SAP_USER_SEGMENTS do not exist in RSRV |
| 1019965 | Long initialization times for BRSPACE and BRCONNECT 6.40 |
| 1015485 | Performance problems displaying an Oracle session using 10g |
| 838725 | Oracle dictionary statistics and system statistics |
| 713211 | Poor performance of database collector and DB02 |
| 712624 | High CPU consumption by Oracle |
| 662219 | Add. info. on upgrading to SAP Web AS 6.40 ORACLE |
| 618868 | FAQ: Oracle performance |
| 582427 | Error due to inconsistent Oracle DDIC |
| 575280 | Add info about upgrade to SAP Web AS 6.20 ORACLE 9.2.0 |
| 574676 | Oracle 8i: ORA-00600 [ktsapsext-1] |
| 558746 | Better Oracle Data Dictionary BW Performance |
| 558197 | upgrade hangs in PARCONV_UPG, XPRAS_UPG, SHADOW_IMPORT_UPG2, |
| 539921 | Current patch set for Oracle 9.2.0 |
| 519407 | Performance problems accessing the datadictionary views |
| 458932 | Composite SAP note: ORA-00235 |
| 362060 | Current patch set for Oracle 8.1.7 |
| 354293 | DBVerify reports corrupt block in freespace area |
| 354080 | Note collection for Oracle performance problems |
| 23345 | Consistency check of ORACLE database |