Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Friday, February 17, 2012

Use Oracle 10g Segment Advisor Usage

Print Friendly and PDF
Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It's useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor
Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space
An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

-- Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,'DD-MM-YY HH24:MI:SS') start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner='SYS'
AND job_name = 'AUTO_SPACE_ADVISOR_JOB'
ORDER BY actual_start_date;


Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

-- Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

-- Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK('Segment Advisor', :TASK_ID, :TASK_NAME);

-- Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'TABLE', '<SCHEMA>', '<TABLE NAME>', NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'INDEX', '<SCHEMA>', '<INDEX NAME>', NULL, NULL, :OBJECT_ID);

-- Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

-- Check the results in two ways:
-- Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

-- or use DBMS_SPACE package (recommended).

SELECT
  RECOMMENDATIONS RECOMMENDATION,
  C1 ACTION1,
  C2 ACTION2,
  C3 ACTION3
FROM
  TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
  TASK_ID = :TASK_ID;


-- Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);

No comments: