已索引
该文章已被其它文章引用。
参考官方文档：Administrator's Guide > 16 Managing Undo
The following example creates an undo advisor task to evaluate the undo tablespace.
The name of the advisor is 'Undo Advisor'. The analysis is based on AWR snapshots.
DECLARE
task_id NUMBER;
task_name VARCHAR2(30);
object_id NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', task_id, task_name, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(task_name, 'UNDO_TBS', null, null, null, 'null', object_id);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'TARGET_OBJECTS', object_id);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'START_SNAPSHOT', 12);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'END_SNAPSHOT', 13);
DBMS_ADVISOR.SET_TASK_PARAMETER(task_name, 'INSTANCE', 1);
DBMS_ADVISOR.execute_task(task_name);
END;
/
CREATE_TASK：This procedure creates a new Advisor task in the repository.
第1个参数是 advisor name，可以在 DBA_ADVISOR_DEFINITIONS 中查到。
第2个和第3个参数 task_id 和 task_name 是 out 类参数。
第4个参数是任务描述，最多256 characters。
CREATE_OBJECT：This procedure creates a new task object.
第1个参数：A valid Advisor task name that uniquely identifies an existing task.
第2个参数：Specifies the external object type.
第3-6个参数：Advisor-specific data.
第7个参数：The advisor-assigned object identifier. 是 out 类参数。
SET_TASK_PARAMETER：This procedure modifies a user parameter within an Advisor task or a template.
第1个参数：The Advisor task name that uniquely identifies an existing task.
第2个参数：The name of the task parameter to be modified.
第3个参数：The value of the specified task parameter.
After you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager. This information is also available in the DBA_ADVISOR_* data dictionary views (DBA_ADVISOR_TASKS, DBA_ADVISOR_OBJECTS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, and so on):
select OWNER,TASK_NAME,CREATED
from DBA_ADVISOR_TASKS
where ADVISOR_NAME = 'Undo Advisor'
order by CREATED;
OWNER TASK_NAME CREATED
----- ---------- -------------------
SYS TASK_122 2023-11-11 00:10:09col OWNER for a5;
col TYPE for a10;
col TASK_NAME for a10;
col ATTR1 for a5;
col ATTR2 for a5;
col ATTR3 for a5;
col ATTR4 for a5;
col ATTR5 for a5;
col ATTR6 for a5;
col OTHER for a10;
set lines 200;
select * from DBA_ADVISOR_OBJECTS where TASK_NAME='TASK_122';
OWNER OBJECT_ID TYPE TYPE_ID TASK_ID TASK_NAME EXECUTION_NAME ATTR1 ATTR2 ATTR3 ATTR4 ATTR5 ATTR6 ATTR7 ATTR8 ATTR9 ATTR10 OTHER
----- ---------- ---------- ---------- ---------- ---------- ------------------------------ ----- ----- ----- ----- ----- ----- ---------- ---------- ---------- ---------- ----------
SYS 1 UNDO_TBS 6 122 TASK_122 nullcol IMPACT_TYPE for a80;
col MESSAGE for a40;
select IMPACT_TYPE,MESSAGE FROM DBA_ADVISOR_FINDINGS where TASK_NAME='TASK_122';
IMPACT_TYPE MESSAGE
-------------------------------------------------------------------------------- ----------------------------------------
Some of your operations may fail due to full undo tablespace. Undo Tablespace is under pressure.
如果没有问题，则输出效果类似这样：
IMPACT_TYPE MESSAGE
---------------------------------------- ----------------------------------------
Undo tablespace is OK. The undo tablespace is OK.
set lines 200;
col PARENT_REC_IDS for a10;
col BENEFIT_TYPE for a10;
select * from DBA_ADVISOR_RECOMMENDATIONS where TASK_NAME='TASK_122';
OWNER REC_ID TASK_ID TASK_NAME EXECUTION_NAME FINDING_ID TYPE RANK PARENT_REC BENEFIT_TY BENEFIT ANNOTATION_ FLAGS F
----- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- -
SYS 1 122 TASK_122 EXEC_122 1 1 0 0 N
如果没有问题，就不会生成记录。上面的PL/SQL会报告UNDO是否OK，不会报告建议大小。
创建的TASK可以通过DBA_ADVISOR_TASKS查询到，然后可以使用下面命令删除：
exec DBMS_ADVISOR.DELETE_TASK ('TASK_122');
