实验环境

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

更多推荐