SAP Basis Tech Supports

Symptom
You require information about the space utilization of segments.

Reason and Prerequisites
The Segment Advisor is delivered as of Oracle 10g as a tool that you can use to identify and implement optimization options for segments (tables, indexes …). For example, you can use the Segment Advisor to identify segments that utilize space poorly (Note 821687). In addition, the system reports segments that have an unnecessarily high number of chained rows.
A current license for the Oracle Diagnostics Pack and the Oracle Tuning Pack is a prerequisite for using the Segment Advisor.
By default, the Segment Advisor runs within the framework of a scheduled administration job in the Oracle database. You can display the results of these types of jobs using the Oracle Enterprise Manager or the Oracle Dictionary views. You can find an overview of the
automatic Segment Advisor runs in view DBA_AUTO_SEGADV_SUMMARY. The DBA_AUTO_SEGADV_CTL view contains information about which objects (tablespaces, segments) were edited in which task. You can find more details on this in the Administrator’s Guide in the Oracle online documentation.
The Segment Advisor may cause time-consuming database statements, for example:
SQL_ID 6mcpb06rctk0x:
call dbms_space.auto_space_advisor_job_proc()SQL_ID 8szmwam7fysa3:
insert into wri$_adv_objspace_trend_data select timepoint, …SQL_ID cfxfxjk00tq0m:
SELECT
TASKS.TASK_ID, REC.ID REC_ID, ACT.OBJ_ID,
SUBSTR(OBJ.ATTR1, 1, 30)
SEG_OWNER, SUBSTR(OBJ.ATTR2, 1, 30) SEG_NAME,
SUBSTR(D.OBJECT_TYPE, 1, 18)
SEG_TYPE, SUBSTR(OBJ.ATTR3, 1, 30) PART_NAME,
TO_CHAR(SUBSTR(OBJ.ATTR4, 1, 30)) TBS_NAME
FROM
DBA_ADVISOR_TASKS TASKS, SYS.WRI$_ADV_REC_ACTIONS RACT,
SYS.WRI$_ADV_OBJECTS OBJ, DBA_ADVISOR_OBJECT_TYPES D,
SYS.WRI$_ADV_RECOMMENDATIONS REC, SYS.WRI$_ADV_ACTIONS ACT
WHERE
TASKS.ADVISOR_ID = 5 AND TASKS.TASK_ID = RACT.TASK_ID AND
RACT.TASK_ID = REC.TASK_ID AND RACT.REC_ID = REC.ID AND
NVL(REC.ANNOTATION, 0) <> 3 AND RACT.TASK_ID = ACT.TASK_ID AND
RACT.ACT_ID = ACT.ID AND ACT.TASK_ID = OBJ.TASK_ID AND
ACT.OBJ_ID = OBJ.ID AND OBJ.TYPE = D.OBJECT_TYPE_ID AND
NVL(SUBSTR(OBJ.ATTR1, 1, 30), ‘ ‘) = NVL(:B5 , ‘ ‘) AND
NVL(SUBSTR(OBJ.ATTR2, 1, 30), ‘ ‘) = NVL(:B4 , ‘ ‘) AND
NVL(SUBSTR(D.OBJECT_TYPE, 1, 18), ‘ ‘) = NVL(:B3 , ‘ ‘) AND
NVL(SUBSTR(OBJ.ATTR3, 1, 30), ‘ ‘) = NVL(:B2 , ‘ ‘) AND
NVL(TO_CHAR(SUBSTR(OBJ.ATTR4, 1, 30)), ‘ ‘) = NVL(:B1 , ‘ ‘)SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE,
TABLESPACE_NAME, TABLESPACE_ID
FROM SYS_DBA_SEGS WHERE SEGMENT_OBJD = :B1SQL_ID 7wgks43wrjtrz:
SELECT U.SPACE_USED, U.SPACE_ALLOCATED FROM
TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF( :B1 , :B2 , :B3 , 0,
:B4 , ‘TRUE’, :B5 )) USQL_ID gnux0zb3sxduk:
SELECT
TIMEPOINT, DELTA_SPACE_USAGE, DELTA_SPACE_ALLOC, TOTAL_SPACE_USAGE,
TOTAL_SPACE_ALLOC, INSTANCE_NUMBER, OBJN
FROM
TABLE(DBMS_SPACE.OBJECT_GROWTH_TREND_SWRF(:B1 , :B2 , :B3 , :B4 ))
ORDER BY
TIMEPOINTSQL_ID 0xcprwqg912w6:
SELECT
TABLE_NAME
FROM
DBA_INDEXES
WHERE
OWNER = :B2 AND INDEX_NAME = :B1 AND INDEX_TYPE = ‘IOT – TOP’
In addition, a large number of entries are written into the WRI$_ADV_OBJECTS AWR table.
In this context, you should also note the bug affecting Oracle 10.2.0.2 or lower that is described in Note 1001925. If necessary, you can deactivate the automatic runs of the Segment Advisor as follows:
EXEC DBMS_SCHEDULER.DISABLE(’AUTO_SPACE_ADVISOR_JOB’);
If you still require the results of the Automatic Segment Advisors, you can schedule the runs less frequently. You can use the following commands to swtch to weekly execution (where start_date can also be adjusted accordingly):
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE ( -
schedule_name => ‘SEGMENT_ADVISOR_WEEKLY’, -
start_date => TO_DATE(’01.02.2009 20:00:00′, -
‘dd.mm.yyyy hh24:mi:ss’), -
repeat_interval => ‘FREQ=WEEKLY’);
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE ( -
name => ‘AUTO_SPACE_ADVISOR_JOB’, -
attribute => ‘SCHEDULE_NAME’, -
value => ‘SEGMENT_ADVISOR_WEEKLY’);
This note deals specifically with the option of using SQLPLUS to carry out manual analyses and evaluations of individual segments or tablespaces.
Solution
SQLPLUS can be used to create and evaluate Segment Advisor analyses as follows:
1. Log on to SQLPLUS with SYSDBA privileges.2. First, the following global parameters are declared for the execution of the subsequent commands:
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;3. A Segment Advisor task can now be created:
EXEC DBMS_ADVISOR.CREATE_TASK(’Segment Advisor’, -
:TASK_ID, :TASK_NAME);4. Objects that are to be examined as part of the analysis can now be assigned to this task. You can specify individual tables and indexes, or complete tablespaces. The relevant commands appear as follows:Tablespaces
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘TABLESPACE’, -
‘<tablespace_name>’, NULL, NULL, NULL, NULL, :OBJECT_ID);Tables
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘TABLE’, -
‘<owner>’, ‘<table_name>’, NULL, NULL, :OBJECT_ID);Indexes
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘INDEX’, -
‘<owner>’, ‘<index_name>’, NULL, NULL, :OBJECT_ID);5. By default, the system only examines objects for which Segment Shrinking can be carried out (see Note 910389). To analyze ALL specified segments instead, you can set the RECOMMEND_ALL parameter to TRUE:
EXEC DBMS_ADVISOR.SET_TASK_PARAMETER(:TASK_NAME, -
‘RECOMMEND_ALL’, ‘TRUE’);6. The task can now be executed. In this step, Oracle collects all required data, and saves the results:
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);7. The results can now be read by means of views such as DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS and DBA_ADVISOR_OBJECTS. However, the easiest way to display the results is by means of DBMS_SPACE.ASA_RECOMMENDATIONS, which is available as of Oracle 10.2:
SELECT
RECOMMENDATIONS RECOMMENDATION,
C1 ACTION1,
C2 ACTION2,
C3 ACTION3
FROM
TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
TASK_ID = :TASK_ID;
The RECOMMENDATION column contains the recommendation, while the ACTION columns contain information about the technical implementation. A typical result could be:Recommended:
Enable row movement of the table <owner>.< table_name> and perform shrink, estimated savings is <bytes> bytes.Actions:
alter table “<owner>”.”<table_name>” enable row movement
alter table “<owner>”.”<table_name>” shrink space COMPACT
alter table “<owner>”.”<table_name>” shrink space
Caution: Before you use segment shrinking, see Note 910389.8. If the task is no longer required, it can be deleted as follows:
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);