The Segment Advisor in Oracle 19c is a powerful feature designed to identify inefficient segments within your database and recommend appropriate actions to optimize space usage. It works by analyzing the fragmentation, growth trends, and access patterns of your database segments. Based on this analysis, it provides recommendations on whether you should perform actions such as shrinking or reorganizing these segments.
You might be wondering, “Isn’t this what the Automatic Database Diagnostic Monitor (ADDM) does?” Well, not quite. While both the Segment Advisor and ADDM are part of Oracle’s suite of performance management tools, they serve different purposes. The ADDM is more of a diagnostic tool that automatically detects and reports performance problems with the database. It does this by analyzing data from the Automatic Workload Repository (AWR), which is a built-in repository in Oracle databases that collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The ADDM and AWR work hand-in-hand to ensure optimal database performance.
On the other hand, the Segment Advisor focuses specifically on segment-level optimization. It’s like a specialist doctor who focuses on a specific area, while the ADDM is like a general practitioner who gives an overall health check.
In a previous post, we discussed how to shrink a fragmented table, a process that can help reclaim wasted space and improve the performance of your database. The Segment Advisor takes this a step further by not only identifying which tables could benefit from such an operation, but also providing detailed recommendations on how to carry out these optimizations. This makes it an invaluable tool in maintaining the health and efficiency of your Oracle database.
The DBMS_ADVISOR package.
The DBMS_ADVISOR is a part of Oracle’s server manageability suite of advisors. It’s a set of expert procedures that identifies and helps resolve performance problems relating to database server components.
Here are some of the main procedures in the DBMS_ADVISOR package:
- CREATE_TASK: Creates a new Advisor task in the repository.
- RESET_TASK: Resets a task to its initial state.
- EXECUTE_TASK: Executes the specified task.
- INTERRUPT_TASK: Stops a currently executing task, ending its operations as it would at a normal exit.
- SET_DEFAULT_TASK_PARAMETER: Modifies a default task parameter.
- SET_TASK_PARAMETER: Sets the specified task parameter value.
- CANCEL_TASK: Cancels a currently executing task operation.
- DELETE_TASK: Deletes the specified task from the repository.
- CREATE_FILE: Creates an external file from a PL/SQL CLOB variable, which is useful for creating scripts and reports.
- CREATE_OBJECT: Creates a new task object.
- GET_REC_ATTRIBUTES: Retrieves specific recommendation attributes from a task.
- GET_TASK_SCRIPT: Creates and returns an executable SQL script of the Advisor task’s recommendations in a buffer.
- MARK_RECOMMENDATION: Sets the annotation_status for a particular recommendation.
- QUICK_TUNE: Performs an analysis on a single SQL statement.
- UPDATE_OBJECT: Updates a task object.
- UPDATE_REC_ATTRIBUTES: Updates an existing recommendation for the specified task.
- UPDATE_TASK_ATTRIBUTES: Updates a task’s attributes.
Please note that some of these procedures might be deprecated and Oracle recommends not using them in new applications. Always check the latest Oracle documentation for the most up-to-date information.
DBMS_ADVISOR: A Hands-On Approach”
Now, we’re going to take a practical look at Oracle’s DBMS_ADVISOR. We’ll start by creating a table filled with a million records. Then, to make things interesting, we’ll delete 90% of those records, intentionally fragmenting the table.
With our fragmented table ready, we’ll turn to the DBMS_ADVISOR. This tool will analyze our table and provide recommendations on how to optimize it. It’s a hands-on way to understand how the DBMS_ADVISOR works and how it can help improve the performance of your Oracle database.
SQL> drop TABLESPACE TS_TEST INCLUDING CONTENTS and DATAFILES;
TABLESPACE TS_TEST dropped.
SQL> CREATE TABLESPACE TS_TEST
DATAFILE '/u01/app/oracle/oradata/ORCL/pdbts/ts_test01.dbf'
SIZE 50M LOGGING EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
TABLESPACE TS_TEST created.
--Create a Segment Advisor Task
DECLARE
task_name VARCHAR2(128) := 'advisor_task';
task_desc VARCHAR2(128) := 'Advisor for segments in TS_TEST';
task_id NUMBER;
object_id NUMBER;
objectname VARCHAR2(100);
objecttype VARCHAR2(100);
BEGIN
dbms_advisor.create_task('Segment Advisor', task_id,task_name,task_desc,NULL);
dbms_advisor.create_object(task_name,'TABLESPACE','TS_TEST',' ',' ',NULL, ' ',object_id) ;
dbms_advisor.set_task_parameter(task_name,'RECOMMEND_ALL','TRUE');
END;
/
PL/SQL procedure successfully completed.
SQL> select advisor_id, advisor_name from DBA_ADVISOR_DEFINITIONS
where advisor_name = 'Segment Advisor';
ADVISOR_ID ADVISOR_NAME
---------- ---------------------------------------
5 Segment Advisor
--Execute the task.
DECLARE
task_name VARCHAR2(128) := 'advisor_task';
BEGIN
dbms_advisor.EXECUTE_TASK(task_name);
END;
/
PL/SQL procedure successfully completed.
-- Create a fragmented test table.
SQL> create table test ( n number, c varchar2(200) )
tablespace TS_TEST;
Table TEST created.
begin
for i in 1..1000000
loop
insert into test values ( i, 'Some text for record # ' || to_char(i) );
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> delete from test where n > 100;
999,900 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, fs.bytes*100/ df.bytes PCT_FREE
FROM dba_data_files df ,dba_free_space fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TS_TEST';
TABLESPACE FREE BYTES PCT_FREE
------------------------------ ---------- ---------- ----------
TS_TEST 5242880 52428800 10
--Execute the advisor task again.
DECLARE
task_name VARCHAR2(128) := 'advisor_task';
BEGIN
dbms_advisor.RESET_TASK(task_name);
dbms_advisor.EXECUTE_TASK(task_name);
END;
/
PL/SQL procedure successfully completed.
SQL> SELECT attr1, attr2, message
FROM dba_advisor_findings f, dba_advisor_objects o
WHERE f.task_name = o.task_name
AND f.object_id = o.object_id
AND f.task_name = 'advisor_task';
ATTR1 ATTR2 MESSAGE
------- ------- --------------------------------------------------------------------------------------------------
SYS TEST Enable row movement of the table SYS.TEST and perform shrink, estimated savings is 46136048 bytes.
-- Remove the advisor task.
DECLARE
task_name VARCHAR2(128) := 'advisor_task';
BEGIN
dbms_advisor.DELETE_TASK(task_name);
END;
/
PL/SQL procedure successfully completed.
It’s important to note that if the DBMS_ADVISOR already contains the result of a previous execution, you’ll need to reset it before running it again. If you don’t, you might encounter the following error message indicating that the advisor task is already executed. Resetting the advisor ensures that you’re starting with a clean slate and allows the DBMS_ADVISOR to accurately analyze the current state of your table.
ORA-13630: The task advisor_task contains execution results and cannot be executed.
.........
13630. 00000 - "The task %s contains execution results and cannot be executed."
*Cause: The user attempted to execute a task that already contains
execution results.
*Action: Reset the task to its initial state and retry the operation.
To wrap up.
By using tools like Oracle’s DBMS_ADVISOR and understanding how to interpret and implement its recommendations, you can effectively manage fragmentation in your database. This not only helps in optimizing space usage but also ensures that your database operations run smoothly and efficiently.
Remember, a well-maintained database is the backbone of any application. So, keep a close eye on fragmentation and take proactive steps to manage it. Your database, and your users, will thank you for it.
Was this post helpful? Consider subscribing or leaving a comment.