Solution : https://service.sap.com/sap/support/notes/927813 (SAP Service marketplace login required)
Summary :
The SAP Note details the utilization and optimization of segments in Oracle databases via the Segment Advisor, available from Oracle 10g. This tool helps identify segments with poor space utilization and excessive chained rows. It includes automatic and manual operations; the former runs through scheduled jobs in the Oracle database, while the latter requires specific commands executed in SQLPLUS with SYSDBA privileges. Recommendations for reducing execution frequency or disabling the Segment Advisor are provided to enhance performance. Modifications through DBMS_SCHEDULER are discussed alongside methods for managing and analyzing segment space using predefined tasks and parameters in DBMS_ADVISOR and DBMS_SPACE.
Key words :
index_type = 'iot - top' sql_id 7b3f5hbmkauk6, time-consuming database statements, wri$_adv_actions actwhere tasks, wri$_adv_objects awr table, enable row movementalter table, evaluate segment advisor analyses, automatic segment advisor completely, theautomatic segment advisor runs, shrink space compactalter table, wri$_adv_objspace_trend_data select timepoint
Related Notes :
| 1438410 | SQL: Script collection for Oracle |
| 1295200 | Oracle 10g or higher: Space statistics based on DBMS_SPACE |
| 974781 | Oracle internal maintenance jobs |
| 910389 | FAQ: Oracle Segment Shrinking |
| 853576 | Oracle 10g: Performance analysis w/ ASH and Oracle Advisors |
| 821687 | FAQ: Space utilization and fragmentation in Oracle |
| 771929 | FAQ: Index fragmentation |