自適應遊標共享(ACS)與sql計劃管理(SPM)的相互影響
討論自適應遊標共享和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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 11g自適應遊標與 SQL 計劃管理SQL
- Oracle遊標共享（Cursor Sharing）--常規遊標共享和自適應遊標共享（ACS）Oracle
- Oracle自適應共享遊標Oracle
- 11G Adaptive Cursor Sharing(ACS)自適應遊標測試APT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（上）OracleAPT
- Oracle自適應共享遊標——Adaptive Cursor Sharing（下）OracleAPT
- 揭秘自適應遊標共享技術(Adaptive Cursor Sharing)APT
- Oracle 11.1 自適應遊標Oracle
- 揭祕自適應遊標共享技術(Adaptive Cursor Sharing)APT
- 繫結變數之自適應遊標共享(Adaptive Cursor Sharing)變數APT
- 【SPM】Oracle計劃管理器SPM介紹及用例Oracle
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 自適應網頁設計/響應式Web設計網頁Web
- 【最佳化】SPM(上）自動捕獲sql執行計劃並演進SQL
- 關於sql語句的遊標共享問題SQL
- 【CURSOR】Oracle繫結變數、執行計劃對遊標的影響Oracle變數
- 5G和AI的相互影響AI
- HTML5—-響應式（自適應）網頁設計HTML網頁
- cluster factor對執行計劃的影響
- 淺談cursor_sharing取值對SQL共享的影響（上）SQL
- 淺談cursor_sharing取值對SQL共享的影響（下）SQL
- 索引及排序對執行計劃的影響索引排序
- SQL 遊標SQL
- 【SPM】Oracle如何固定執行計劃Oracle
- zt_Oracle9i,10g,11g 使用繫結變數的區別及與cursor_sharing的關係_自適應遊標共享Oracle變數
- Oracle遊標共享，父遊標和子游標的概念Oracle
- CLUSTERING_FACTOR影響執行計劃
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- oracle cardinality對於執行計劃的影響Oracle
- not-null約束對執行計劃的影響Null
- 一個自適應的函式指標函式指標
- 再說索引與Null值對於Hints及執行計劃的影響索引Null
- 網頁設計：響應式VS.自適應，哪種設計更好呢？網頁
- PL/SQL 遊標SQL
- SQL Server遊標SQLServer
- SQL 遊標cursorSQL
- PL/SQL-遊標和遊標變數的使用SQL變數