Oracle绑定执行计划实验
·
Oracle绑定执行计划实验
实验环境
SQL> create user optm identified by optm;
SQL> create tablespace optmtbs datafile '+DATA/optmtbs01.dbf' size 1G autoextend on 10M;
SQL> grant connect, resource, dba to optm;
SQL> conn optm/optm
SQL> CREATE TABLE tb_2million
2 (
3 id NUMBER not null primary key,
4 name NVARCHAR2(60) not null,
5 salary int NOT NULL,
6 createtime TIMESTAMP (6) not null
7 );
SQL>
SQL> insert into tb_2million
2 select rownum,
3 dbms_random.string('*',dbms_random.value(6,20)),
4 dbms_random.value(1000,50000),
5 sysdate
6 from dual
7 connect by level<=1000000
8 order by dbms_random.random;
SQL> commit;
SQL> create table tb_16million as select * from tb_2million;
SQL> insert into tb_16million select * from tb_16million;
SQL> /
SQL> /
SQL> /
SQL> select count(*) from tb_16million;
COUNT(*)
----------
16000000
SQL> commit;
SQL> update tb_16million set id=rownum where 1 = 1;
SQL> commit;
SQL> ALTER TABLE tb_16million ADD CONSTRAINT tb_16million_pk PRIMARY KEY (id);
查看参数
# 查看优化方式
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode string ALL_ROWS
# 查看表的统计信息是否收集
SQL> select table_name,num_rows,blocks,last_analyzed from user_tables where table_name='TB_16MILLION';
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED
------------------------------------------------------------------------------------------ ---------- ---------- -------------------
TB_16MILLION 16010730 111195 2021-12-07 18:06:09
# 收集表的统计信息
SQL> exec dbms_stats.gather_table_stats(ownname=>'OPTM',tabname=>'TB_16MILLION',estimate_percent=>10,method_opt=>'for all indexed columns');
绑定执行计划
hint强制执行计划
# 查询id大于15999990的所有值,使用Oracle自动选择的执行计划
SQL> explain plan for select * from tb_16million where id > 15999990;
Explained.
SQL> select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2095815536
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1010 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_16MILLION | 10 | 1010 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TB_16MILLION_PK | 10 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">15999990)
Note
-----
- dynamic sampling used for this statement (level=2)
18 rows selected.
# 查询id大于15999990的所有值,强制执行
SQL> explain plan for select /*+full(TB_16MILLION)*/ * from tb_16million where id > 15999990;
Explained.
SQL> select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1853203275
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1010 | 30630 (2)| 00:06:08 |
|* 1 | TABLE ACCESS FULL| TB_16MILLION | 10 | 1010 | 30630 (2)| 00:06:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">15999990)
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
我们可以看到查询id大于15999990的值时候,oracle执行计划选择了索引范围扫描。
我们强制sql走全表扫描时,会生成一份新的执行计划。
spm绑定执行计划(>=11g)
# 查询sql id
SQL> col sql_text for a100
SQL> col sql_id for a50
SQL> select sql_id, sql_text from v$sql where sql_text like '%from tb_16million%';
SQL_ID SQL_TEXT
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
315jrp8cs09rt select /*+full(TB_16MILLION)*/ * from tb_16million where id > 15999990
g87zmunzqfvnd select * from tb_16million where id > 15999990
# 查询sql对应hash value(我们这里假设走全表扫描是最优的执行计划,获取全表扫描的hash value)
SQL> select * from table(dbms_xplan.display_awr('&sql',format=>'PEEKED_BINDS'));
Enter value for sql: 315jrp8cs09rt
old 1: select * from table(dbms_xplan.display_awr('&sql',format=>'PEEKED_BINDS'))
new 1: select * from table(dbms_xplan.display_awr('315jrp8cs09rt',format=>'PEEKED_BINDS'))
no rows selected
SQL>
SQL> select * from table(dbms_xplan.display_cursor('&sql'));
Enter value for sql: 315jrp8cs09rt
old 1: select * from table(dbms_xplan.display_cursor('&sql'))
new 1: select * from table(dbms_xplan.display_cursor('315jrp8cs09rt'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 315jrp8cs09rt, child number 0
-------------------------------------
select /*+full(TB_16MILLION)*/ * from tb_16million where id > 15999990
Plan hash value: 1853203275
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30602 (100)| |
|* 1 | TABLE ACCESS FULL| TB_16MILLION | 8 | 392 | 30602 (2)| 00:06:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">15999990)
18 rows selected.
# 使用SPM绑定执行计划
SQL> declare
2 m_clob clob;
3 begin
4 select sql_fulltext into m_clob from v$sql where sql_id = 'g87zmunzqfvnd' and child_number = 0;
5 dbms_output.put_line(m_clob);
6 dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '315jrp8cs09rt', plan_hash_value => 1853203275, sql_text => m_clob, fixed => 'YES', enabled => 'YES'));
7 end;
8 /
# 查看执行计划是否绑定
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ACCEPTED FIXED OPTIMIZER_COST
------------------------------ ------------------------------------------------------------ --------- --------- --------------
SQL_2ae60586c0587436 SQL_PLAN_2pth5hv05hx1q18e7e4bb YES YES 30602
# 检查sql执行计划,如果成功应该走的是全表扫描
SQL> explain plan for select * from tb_16million where id > 15999990;
Explained.
SQL> select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1853203275
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 392 | 30602 (2)| 00:06:08 |
|* 1 | TABLE ACCESS FULL| TB_16MILLION | 8 | 392 | 30602 (2)| 00:06:08 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">15999990)
Note
-----
- SQL plan baseline "SQL_PLAN_2pth5hv05hx1q18e7e4bb" used for this statement
17 rows selected.
绑定执行计划过程:先查找出需要绑定的sql id和子游标编号,使用dbms_spm.load_plan_from_cursor_cache将目标执行计划替换进去。
spm删除执行计划(>=11g)
# 查看执行计划是否绑定
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ACCEPTED FIXED OPTIMIZER_COST
------------------------------ ------------------------------------------------------------ --------- --------- --------------
SQL_2ae60586c0587436 SQL_PLAN_2pth5hv05hx1q18e7e4bb YES YES 30602
# 删除SPM绑定的执行计划
SQL> declare
2 xx PLS_INTEGER;
3 begin
4 xx :=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_2ae60586c0587436', plan_name => null);
5 end;
6 /
PL/SQL procedure successfully completed.
# 检查删除绑定执行计划是否成功
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
no rows selected
更多推荐

所有评论(0)