测试Oracle 11g 固定执行计划-Baseline

最新推荐文章于 2025-06-04 16:15:48 发布

iteye_19543

最新推荐文章于 2025-06-04 16:15:48 发布

阅读量285

点赞数

CC 4.0 BY-SA版权

文章标签： oracle培训 oracle学习 cuug培训 ocp com

本文链接：https://blog.youkuaiyun.com/iteye_19543/article/details/82577355

本文介绍了一种通过修改SQL查询中的提示来改变执行计划的方法，并展示了如何使用Oracle 11g中的SQL Plan Baseline功能来固定特定的执行计划。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[img]http://dl2.iteye.com/upload/attachment/0096/3569/6998ae11-efc4-33f9-896a-0009c759389d.jpg[/img]

一，选定sql_id

SQL> pagesize 300
SQL> set linesize 300
SQL> set autotrace on
SQL> var name varchar2(10);
SQL> exec :name :='IT';
select department_name
from hr.departments dept
where department_id in (select department_id from hr.employees emp)
and department_name=:name;

DEPARTMENT_NAME
------------------------------
IT

1 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2605691773

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 1 | 19 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - filter("DEPARTMENT_NAME"=:NAME)
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

SQL> select sql_id from v$sql where sql_fulltext like'select department_name%';
SQL_ID
-------------
bd8mzf35svfm3
上面的sql现在的执行计划是情况，假设如上的执行计划效率底下，我们想用其它的执行计划(全表扫描emp)来代替它，并用oracle 11g中的sql plan baseline来固定次SQL的执行。

二,添加Hint的sql：

select department_name
from hr.departments dept
where department_id in (select/*+FULL(emp)*/ department_id from hr.employees emp)
and department_name=:name;
Execution Plan
----------------------------------------------------------
Plan hash value: 2317224448

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 19 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
2 - filter("DEPARTMENT_NAME"=:NAME)

SQL> select sql_id from v$sql_plan where plan_hash_value=2317224448;

SQL_ID
-------------
5kuqnnugsrhj3

此计划sql_id:5kuqnnugsrhj3 hash_value:2317224448