面试宝典:介绍下Oracle数据库动态性能视图V$ADVISOR_PROGRESS

最新推荐文章于 2025-11-24 15:52:13 发布

原创最新推荐文章于 2025-11-24 15:52:13 发布 · 708 阅读

19 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #面试 #oracle

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述

Oracle 19c V$ADVISOR_PROGRESS 动态性能视图详解

核心作用

V$ADVISOR_PROGRESS 是 Oracle 数据库顾问框架的关键监控视图，主要用于：

实时监控顾问任务：跟踪数据库顾问任务的执行进度
展示任务状态：提供任务开始时间、运行时长和完成百分比
资源消耗统计：记录任务消耗的CPU和I/O资源
性能瓶颈识别：发现执行缓慢或停滞的顾问任务
预估完成时间：基于当前速度预测任务结束时间

关键特性

实时更新：任务执行期间动态刷新进度数据
多顾问支持：适用于所有数据库顾问（SQL调优、段顾问、内存顾问等）
进度量化：提供精确的完成百分比
资源跟踪：监控CPU和I/O消耗
自动清理：任务完成后记录自动消失

字段详解（Oracle 19c）

任务标识字段

字段名	数据类型	描述
`TASK_ID`	NUMBER	顾问任务的唯一标识符
`TASK_NAME`	VARCHAR2(128)	用户定义的顾问任务名称
`ADVISOR_NAME`	VARCHAR2(64)	顾问名称（如：SQL Tuning Advisor, Segment Advisor）

进度状态字段

字段名	数据类型	描述
`START_TIME`	TIMESTAMP	任务开始执行的时间
`END_TIME`	TIMESTAMP	任务完成时间（未完成时为NULL）
`STATUS`	VARCHAR2(20)	当前状态： - `INITIALIZING` - `EXECUTING` - `COMPLETED` - `INTERRUPTED` - `ERROR`
`PROGRESS`	NUMBER	完成百分比（0~100）
`SOFAR`	NUMBER	已完成的工作单位数
`TOTALWORK`	NUMBER	总工作单位数
`UNITS`	VARCHAR2(64)	工作单位描述（如：“operations”）

资源消耗字段

字段名	数据类型	描述
`CPU_USED`	NUMBER	任务消耗的CPU时间（秒）
`IO_USED`	NUMBER	任务消耗的I/O操作次数
`ESTIMATED_COMPLETION_TIME`	TIMESTAMP	预计完成时间（基于当前速度）

附加信息字段

字段名	数据类型	描述
`EXECUTION_TYPE`	VARCHAR2(10)	执行类型： - `EXECUTE`：手动执行 - `AUTO`：自动任务
`METRIC_ID`	NUMBER	进度指标的ID
`ERROR_MESSAGE`	VARCHAR2(4000)	错误详细信息（当状态为ERROR时）

基表与底层原理

底层结构：X$ADV_PROGRESS（顾问进度内部表）

数据来源：

顾问任务执行引擎
进度监控子系统
资源跟踪器

工作原理：

当顾问任务启动时，在内存中创建进度记录
任务执行期间定期更新进度指标
资源使用数据从V$SYSSTAT等视图获取
完成百分比基于已完成工作单位与总量计算
内存结构通过X$ADV_PROGRESS维护
V$ADVISOR_PROGRESS提供该结构的可读视图

数据生命周期：

任务开始时创建
执行期间更新
任务完成后删除（视图不再显示）

核心使用场景

1. 实时任务监控

SELECT task_name, advisor_name, progress, sofar, totalwork
FROM v$advisor_progress
WHERE status = 'EXECUTING';

2. 长时间任务识别

SELECT task_name, start_time, (SYSDATE - start_time)*24*60 AS minutes_running
FROM v$advisor_progress
WHERE status = 'EXECUTING'
ORDER BY minutes_running DESC;

3. 资源消耗分析

SELECT task_name, cpu_used, io_used
FROM v$advisor_progress
WHERE status = 'COMPLETED'
ORDER BY cpu_used DESC;

4. 自动任务管理

SELECT task_name, execution_type, status
FROM v$advisor_progress
WHERE execution_type = 'AUTO';

5. 任务异常诊断

SELECT task_name, error_message
FROM v$advisor_progress
WHERE status = 'ERROR';

常用查询 SQL 示例

1. 活动任务概览

SELECT 
  task_id,
  task_name,
  advisor_name,
  status,
  progress || '%' AS completion,
  TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') AS started,
  ROUND((SYSDATE - start_time)*24*60, 2) AS minutes_running
FROM v$advisor_progress
WHERE end_time IS NULL;

2. 进度详情

SELECT 
  task_name,
  sofar,
  totalwork,
  ROUND(sofar/totalwork*100, 2) AS progress_pct,
  units,
  estimated_completion_time
FROM v$advisor_progress
WHERE status = 'EXECUTING';

3. 资源消耗报告

SELECT 
  task_name,
  advisor_name,
  cpu_used,
  io_used,
  (end_time - start_time) * 24*60 AS duration_min
FROM v$advisor_progress
WHERE status = 'COMPLETED'
ORDER BY cpu_used DESC;

4. 任务异常检测

SELECT task_name, status, error_message
FROM v$advisor_progress
WHERE status IN ('INTERRUPTED', 'ERROR');

5. 自动任务监控

SELECT 
  task_name,
  start_time,
  end_time,
  status,
  progress
FROM v$advisor_progress
WHERE execution_type = 'AUTO'
ORDER BY start_time DESC;

6. 进度趋势分析

SELECT 
  TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI') AS minute,
  task_name,
  MAX(progress) AS max_progress
FROM (
  SELECT SYSDATE AS sample_time, task_name, progress
  FROM v$advisor_progress
  WHERE status = 'EXECUTING'
)
GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24:MI'), task_name
ORDER BY minute;

顾问任务操作示例

1. 创建SQL调优任务

DECLARE
  l_task_name VARCHAR2(30);
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'g8x9j5s2a1b3c',
    scope => 'COMPREHENSIVE',
    time_limit => 600,
    task_name => 'my_sql_tuning_task');
END;
/

2. 执行任务并监控

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'my_sql_tuning_task');
END;
/

-- 监控进度
SELECT progress, sofar, totalwork
FROM v$advisor_progress
WHERE task_name = 'my_sql_tuning_task';

3. 中断长时间任务

BEGIN
  DBMS_SQLTUNE.INTERRUPT_TUNING_TASK(task_name => 'my_sql_tuning_task');
END;
/

-- 检查状态
SELECT status FROM v$advisor_progress
WHERE task_name = 'my_sql_tuning_task';

4. 恢复中断任务

BEGIN
  DBMS_SQLTUNE.RESUME_TUNING_TASK(task_name => 'my_sql_tuning_task');
END;
/

性能优化建议

1. 任务并行化

-- 设置并行度（仅适用于支持并行的顾问）
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(
    task_name => 'my_task',
    parameter => 'PARALLEL_DEGREE',
    value => 4);
END;
/

2. 资源限制

-- 设置最大执行时间（秒）
BEGIN
  DBMS_ADVISOR.SET_TASK_PARAMETER(
    task_name => 'my_task',
    parameter => 'TIME_LIMIT',
    value => 3600);
END;
/

3. 避免资源争用

-- 在低峰期执行资源密集型任务
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'nightly_advisor',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN my_advisor_procedure; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE);
END;
/

4. 进度监控脚本

#!/bin/bash
# monitor_advisor.sh

TASK_NAME=$1

while true
do
  sqlplus -s / as sysdba <<EOF
  SET PAGESIZE 1000
  SET LINESIZE 200
  COL task_name FOR A30
  COL progress FOR A10
  
  SELECT 
    task_name,
    progress || '%' AS progress,
    TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
    ROUND((SYSDATE - start_time)*24*60, 2) minutes_running,
    estimated_completion_time
  FROM v\$advisor_progress
  WHERE task_name = '$TASK_NAME';
EOF
  sleep 30
done

重要注意事项

权限要求：
- SELECT_CATALOG_ROLE
- ADVISOR权限
- 特定任务的执行权限
数据可见性：
- 只显示当前用户有权限查看的任务
- SYS用户可查看所有任务
- 任务完成后记录立即消失

*与DBA_ADVISOR_视图区别：

特性	V$ADVISOR_PROGRESS	DBA_ADVISOR_TASKS
实时性	实时进度	任务元数据
持久性	仅任务执行期间	永久记录
进度信息	包含进度百分比	无进度信息
资源消耗	包含CPU/I/O统计	无资源统计

典型顾问类型：
- SQL Tuning Advisor
- Segment Advisor
- Memory Advisor
- Undo Advisor
- MTTR Advisor

常见问题处理：

-- 强制删除卡住的任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('my_task');

-- 重置顾问框架
ALTER SYSTEM FLUSH SHARED_POOL;

-- 诊断顾问框架错误
SELECT * FROM V$ADVISOR_ERRORS;

通过 V$ADVISOR_PROGRESS，DBA 可以有效地监控和管理数据库顾问任务的执行，优化系统维护窗口，并及时处理执行异常，确保数据库优化工作顺利进行。

欢迎关注我的公众号《IT小Chen》