讨论自适应游标共享和sql计划管理的相互影响,要记住它们是负责执行不同任务的.ACS自适应游标共享控制在特定执行时间一个子游标是否被共享.对于每个执行的查询,自适应游标会考虑当前的绑定变量值并决定一个存在的子游标是否能被共享或者优化器将给一个机会对于当前的绑定变量值找到更好的执行计划.SPM(sql计划管理)控制着哪个执行计划会被优化器选中.如果一个子游标是ind-aware,那么决定是否共享是不会理睬这个查询是不是由sql计划管理所控制.但是一旦查询和它的当前绑定变量被发送给优化器sql计划管理会约束优化器选择执行计划,而不会考虑这个查询现在是否正在由自适应游标进行优化.
让我们来看一下例子,有许多方法将执行计划加载到sql计划管理中,但是为了简单起见,测试时将手动从游标缓存中加载执行计划将使用下面的语句来创建一个名叫employees_jy的表,下面的语句是向employees_jy表中插入多行记录,在job列上数据有大量的倾斜,且在表上只创建一个索引.
SQL> drop table employees_jy purge;
Table dropped
SQL> create table employees_jy as select * from hr.employees;
Table created
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
104 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
208 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
416 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
832 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
1664 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_VP','AD_PRES');
3328 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_PRES');
6658 rows inserted
SQL> insert into employees_jy
2 select * from employees_jy where job_id not in ('AD_PRES');
13316 rows inserted
SQL> commit;
Commit complete
SQL> create index EMP_DEPARTMENT_JY_IX on employees_jy (department_id);
Index created
SQL> begin
2 dbms_stats.gather_table_stats(null, 'employees_jy');
3 end;
4 /
PL/SQL procedure successfully completed
sys@JINGYONG> select job_id,count(*) from employees_jy group by job_id
2 order by 2;
JOB_ID COUNT(*)
---------- ----------
AD_PRES 1
AD_VP 8
AD_ASST 256
AC_ACCOUNT 256
AC_MGR 256
PU_MAN 256
PR_REP 256
MK_REP 256
MK_MAN 256
HR_REP 256
FI_MGR 256
SA_MAN 1280
IT_PROG 1280
PU_CLERK 1280
FI_ACCOUNT 1280
ST_MAN 1280
SH_CLERK 5120
ST_CLERK 5120
SA_REP 7680
已选择19行。
下面将执行一个简单的查询将这个employees_jy与hr.departments表使用department_id进行连接
使用job_id对表employees_jy进行过滤并产生聚集结果.
select /*+ bind_aware */ d.department_name,avg(e.salary) from employees_jy e,hr.departments d where e.job_id=:job and e.department_id=d.department_id group by d.departmentd_name;
我们为了加快在游标缓存中得到bind-aware游标,对上面的查询语句使用了bind_aware提示.
如果我们对job_id使用三种不同的绑定变量值,AD_PRES,SA_MAN和SA_REP来执行上面的查询,那么优化器会选择三种不同的执行计划.
sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
2 from employees_jy e,hr.departments d
3 where e.job_id='AD_PRES'
4 and e.department_id=d.department_id
5 group by d.department_name;
AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
24000 Executive
sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical'));
Plan hash value: 912418101
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 79 (100)| |
| 1 | HASH GROUP BY | | 3 | 165 | 79 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3 | 165 | 78 (2)| 00:00:01 |
| 4 | VIEW | VW_GBC_5 | 3 | 117 | 77 (2)| 00:00:01 |
| 5 | HASH GROUP BY | | 3 | 99 | 77 (2)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | EMPLOYEES_JY | 3 | 99 | 76 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
2 from employees_jy e,hr.departments d
3 where e.job_id='SA_MAN'
4 and e.department_id=d.department_id
5 group by d.department_name;
AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
12200 Sales
sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);
Plan hash value: 2162091158
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 80 (100)| |
| 1 | HASH GROUP BY | | 27 | 1323 | 80 (3)| 00:00:01 |
|* 2 | HASH JOIN | | 1505 | 73745| 79 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES_JY | 27 | 50127| 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 1519 | 432| 76 (0)| 00:00:01 |
------------------------------------------------------------------------------------
sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
2 from employees_jy e,hr.departments d
3 where e.job_id='SA_REP'
4 and e.department_id=d.department_id
5 group by d.department_name;
AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
8396.55172 Sales
sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);
Plan hash value: 4206419095
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 81 (100)| |
| 1 | HASH GROUP BY | | 27 | 1323 | 81 (3)| 00:00:01 |
|* 2 | HASH JOIN | | 9050 | 433K| 80 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMPLOYEES_JY | 9136 | 294K| 76 (0)| 00:00:01 |
------------------------------------------------------------------------------------
下面我们加载两个执行计划到sql计划管理中,再来使用绑定变量值AD_PRES,SA_REP来执行查询,这里有两个子游标有不同的执行计划.
SQL> select child_number,plan_hash_value
2 from v$sql
3 where sql_id='48ndug79z68zn'
4 ;
CHILD_NUMBER PLAN_HASH_VALUE
------------ ---------------
0 912418101
1 4206419095
sys@JINGYONG> var loaded number
sys@JINGYONG> exec :loaded:=dbms_spm.load_plans_from_cursor_cache('48ndug79z68zn');
PL/SQL 过程已成功完成。
sys@JINGYONG> print loaded
LOADED
----------
2
现在如果我们同样使用上面三个绑定变量值来执行查询,sql计划管理将会约束优化器从sql计划基线中的两个可接受
的执行计划中选择,我们还是使用相同的执行顺序来看一下优化器会选择哪一个.
select /*+ bind_aware */ avg(e.salary),d.department_name 2 from employees_jy e,hr.departments d 3 where e.job_id='AD_PRES' 4 and e.department_id=d.department_id 5 group by d.department_name; AVG(E.SALARY) DEPARTMENT_NAME ------------- ------------------------------ 24000 Executive sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical') ); Plan hash value: 912418101 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 79 (100)| | | 1 | HASH GROUP BY | | 3 | 165 | 79 (3)| 00:00:01 | | 2 | NESTED LOOPS | | | | | | | 3 | NESTED LOOPS | | 3 | 165 | 78 (2)| 00:00:01 | | 4 | VIEW | VW_GBC_5 | 3 | 117 | 77 (2)| 00:00:01 | | 5 | HASH GROUP BY | | 3 | 99 | 77 (2)| 00:00:01 | |* 6 | TABLE ACCESS FULL | EMPLOYEES_JY | 3 | 99 | 76 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| | | 8 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline SQL_PLAN_5rjzd2w0wwnak39ef2806 used for this statement
对于这个绑定变量值,选择了正确的执行计划没有因为sql计划基线而混淆.这是因为这个执行计划被加载到
sql计划基线中且是可接受的.所以优化器允许选择它.
select /*+ bind_aware */ avg(e.salary),d.department_name 2 from employees_jy e,hr.departments d 3 where e.job_id='SA_MAN' 4 and e.department_id=d.department_id 5 group by d.department_name; AVG(E.SALARY) DEPARTMENT_NAME ------------- ------------------------------ 12200 Sales sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical') ); Plan hash value: 4206419095 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 81 (100)| | | 1 | HASH GROUP BY | | 27 | 1323 | 81 (3)| 00:00:01 | |* 2 | HASH JOIN | | 9050 | 433K| 80 (2)| 00:00:01 | | 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| EMPLOYEES_JY | 9136 | 294K| 76 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Note ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline SQL_PLAN_15f1skdhjq6mx641797f3 used for this statement
对于这个绑定变量值,优化器选择了一个在sql计划基数中不存在的执行计划,所以我们选择了一个可以接受的最好的执行计划来执行这个查询,优化器提出将基于成本的执行计划添加到sql计划基线中,但它将不会被考虑直到它已经被改进之前.
SQL> select sql_handle,plan_name,accepted
2 from dba_sql_plan_baselines
3 where sql_handle='SYS_SQL_5bc7ed1701ce5152';
SQL_HANDLE PLAN_NAME ACCEPTED
------------------------------ ------------------------------ --------
SYS_SQL_5bc7ed1701ce5152 SQL_PLAN_5rjzd2w0wwnak39ef2806 YES
SYS_SQL_5bc7ed1701ce5152 SQL_PLAN_15f1skdhjq6mx641797f3 YES
SYS_SQL_5bc7ed1701ce5152 SQL_PLAN_5rjzd2w0wwnakecea1efa NO
SQL> select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_5bc7ed1701ce5152','SQL_PLAN_5rjzd2w0wwnakecea1efa','basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_5bc7ed1701ce5152
SQL text: select /*+ bind_aware */ avg(e.salary),d.department_name from
employees_jy e,hr.departments d where e.job_id=:job and
e.department_id=d.department_id group by d.department_name
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_5rjzd2w0wwnakecea1efa Plan id: 2162091158
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2162091158
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 80 (100)| |
| 1 | HASH GROUP BY | | 27 | 1323 | 80 (3)| 00:00:01 |
|* 2 | HASH JOIN | | 1505 | 73745| 79 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMPLOYEES_JY | 27 | 50127| 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPARTMENTS | 1519 | 432| 76 (0)| 00:00:01 |
------------------------------------------------------------------------------------
sys@JINGYONG> select /*+ bind_aware */ avg(e.salary),d.department_name
2 from employees_jy e,hr.departments d
3 where e.job_id='SA_REP'
4 and e.department_id=d.department_id
5 group by d.department_name;
AVG(E.SALARY) DEPARTMENT_NAME
------------- ------------------------------
8396.55172 Sales
sys@JINGYONG> select * from table(dbms_xplan.display_cursor(null,null,'typical')
);
Plan hash value: 4206419095
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 81 (100)| |
| 1 | HASH GROUP BY | | 27 | 1323 | 81 (3)| 00:00:01 |
|* 2 | HASH JOIN | | 9050 | 433K| 80 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMPLOYEES_JY | 9136 | 294K| 76 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline SQL_PLAN_15f1skdhjq6mx641797f3 used for this statement
和我们所期待的一样,和原来得到的执行计划一样,因为这个执行计划被加载到sql计划基线中了.因为第二个与第三个查询使用了相同的执行计划,而在游标缓存中只有一个能被共享.因此现在这个游标将会匹配与SA_MAN或SA_REP(或者在它们两者之间)有相似选择性的绑定变量.