ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 自适应游标共享(ACS)与sql计划管理(SPM)的相互影响

自适应游标共享(ACS)与sql计划管理(SPM)的相互影响

原创 Linux操作系统 作者:eric0435 时间:2013-10-24 22:55:43 0 删除 编辑

讨论自适应游标共享和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(或者在它们两者之间)有相似选择性的绑定变量.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-774991/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
系统架构师 Oracle ACE

注册时间:2011-10-12

  • 博文量
    566
  • 访问量
    6238688