— 查看自适应执行计划的使用情况
SELECT * FROM v$sql
WHERE is_adaptive_plan = ‘YES’;– 查看执行计划中的自适应决策
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => ‘sql_id_value’,
format => ‘ADAPTIVE’
));– 监控自适应执行计划的性能
SELECT sql_id, plan_hash_value, executions, elapsed_time,
is_adaptive_plan
FROM v$sql
WHERE is_adaptive_plan = ‘YES’;

— 创建测试表
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
salary NUMBER
);CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50),
location VARCHAR2(50)
);– 插入测试数据
INSERT INTO departments VALUES (10, ‘Sales’, ‘New York’);INSERT INTO departments VALUES (20, ‘HR’, ‘London’);INSERT INTO departments VALUES (30, ‘IT’, ‘Tokyo’);INSERT INTO employees VALUES (1, ‘John’, ‘Doe’, 10, 5000);INSERT INTO employees VALUES (2, ‘Jane’, ‘Smith’, 20, 6000);INSERT INTO employees VALUES (3, ‘Mike’, ‘Johnson’, 10, 5500);INSERT INTO employees VALUES (4, ‘Lisa’, ‘Williams’, 30, 7000);COMMIT;– 执行连接查询
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;– 查看SQL ID
SELECT sql_id, sql_text FROM v$sql
WHERE sql_text LIKE ‘%employees e JOIN departments d%’ AND rownum = 1;– 查看自适应执行计划
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => ‘sql_id_value’,
format => ‘ADAPTIVE’
));

SQL> SELECT e.first_name, e.last_name, d.department_name
2 FROM employees e
3 JOIN departments d ON e.department_id = d.department_id
4 WHERE e.salary > 5000;EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_NAME
———- ——— ——————–
Jane Smith HR
Mike Johnson Sales
Lisa Williams IT

SQL> SELECT sql_id, sql_text FROM v$sql
2 WHERE sql_text LIKE ‘%employees e JOIN departments d%’ AND rownum = 1;SQL_ID SQL_TEXT
————- ————————————————–
mno345 SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.salary > 5000

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
2 sql_id => ‘mno345’,
3 format => ‘ADAPTIVE’
4 ));Plan hash value: 3986184993

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 3 | 210 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 210 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 3 | 210 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 126 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0012345 | 1 | | 0 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 28 | 1 (0)| 00:00:01 |
————————————————————————————

Predicate Information (identified by operation id):
—————————————————

3 – filter(“E”.”SALARY”>5000)
4 – access(“E”.”DEPARTMENT_ID”=”D”.”DEPARTMENT_ID”)

Note
—–
– this is an adaptive plan

— 调整自适应执行计划参数
ALTER SYSTEM SET optimizer_adaptive_features = TRUE SCOPE=BOTH;ALTER SYSTEM SET optimizer_dynamic_sampling = 4 SCOPE=BOTH;– 监控自适应执行计划的使用情况
SELECT sql_id, plan_hash_value, executions, elapsed_time,
is_adaptive_plan
FROM v$sql
WHERE is_adaptive_plan = ‘YES’ ORDER BY elapsed_time DESC;– 分析自适应执行计划的效果
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(
sql_id => ‘sql_id_value’,
format => ‘ADAPTIVE ALLSTATS LAST’
));

— 检查自适应执行计划是否启用
SELECT * FROM v$parameter WHERE name LIKE ‘optimizer_adaptive%’;– 查看自适应执行计划的统计信息
SELECT * FROM v$sql_plan_statistics_all
WHERE sql_id = ‘sql_id_value’;– 禁用自适应执行计划进行对比测试
ALTER SESSION SET optimizer_adaptive_features = FALSE;– 重新启用自适应执行计划
ALTER SESSION SET optimizer_adaptive_features = TRUE;