使用sql_profile替换执行计划

一、准备数据

SQL> select deptno,count(*) from t2 group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        40          1
        30          1
        10     524289
        20     524289
		
SQL> create index t1 on t2(deptno);

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','t2',method_opt=>'for all columns  size auto');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SYS','t2',method_opt=>'for   columns DEPTNO  size skewonly');

PL/SQL procedure successfully completed.

二、测试sql执行计划

SQL> set linesize 150

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select count(*) from t2 where deptno=40;

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bt33t77y2w9mf, child number 0
-------------------------------------
select count(*) from t2 where deptno=:"SYS_B_0"

Plan hash value: 3441024023

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1   |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=:SYS_B_0)


19 rows selected.

三、将该执行计划创建基线

SQL> SQL> declare 
  2  rs pls_integer;
  3  begin
  4  rs:=dbms_spm.load_plans_from_cursor_cache('bt33t77y2w9mf');
  5  end;
  6  /

PL/SQL procedure successfully completed.
SQL> select sql_handle,plan_name,substr(sql_text,1,30) sql_text,accepted from dba_sql_plan_baselines where enabled='YES';

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                       ACCEPT
------------------------------ ------------------------------ ------------------------------ ------
SQL_a32e7f05dacde247           SQL_PLAN_a6bmz0rdcvsk7ecfa9855 select count(*) from t2 where  YES

--3、测试新sql

SQL> select count(*) from t2 where deptno=10;

  COUNT(*)
----------
    524289

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bt33t77y2w9mf, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: bt33t77y2w9mf, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)


8 rows selected.

SQL> select count(*) from t2 where deptno=10;

  COUNT(*)
----------
    524289

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bt33t77y2w9mf, child number 1
-------------------------------------
select count(*) from t2 where deptno=:"SYS_B_0"

Plan hash value: 3441024023

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1030 (100)|          |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1   |   524K|  1536K|  1030   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_a6bmz0rdcvsk7ecfa9855 used for this statement


23 rows selected.

此处应该走全表，所以执行计划是错误的。

四、使用hint走全表

SQL> select /*+ full(t2) */ count(*) from t2 where deptno=10;

  COUNT(*)
----------
    524289

SQL> select * from table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8uvxtbn283qwz, child number 0
-------------------------------------
select /*+ full(t2) */ count(*) from t2 where deptno=:"SYS_B_0"

Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  1105 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |   524K|  1536K|  1105   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=:SYS_B_0)


19 rows selected.

五、将该sql加载进baseline

SQL> SQL> declare 
  2  rs pls_integer;
  3  begin
  4  rs:=dbms_spm.load_plans_from_cursor_cache('8uvxtbn283qwz');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_handle,plan_name,substr(sql_text,1,30) sql_text,accepted from dba_sql_plan_baselines where enabled='YES';

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                       ACCEPT
------------------------------ ------------------------------ ------------------------------ ------
SQL_859a43fd4bfd0a47           SQL_PLAN_8b6k3zp5zu2k71c6cf506 select /*+ full(t2) */ count(* YES
SQL_a32e7f05dacde247           SQL_PLAN_a6bmz0rdcvsk7c07ed1b4 select count(*) from t2 where  NO
SQL_a32e7f05dacde247           SQL_PLAN_a6bmz0rdcvsk7ecfa9855 select count(*) from t2 where  YES

六、用带hint提示的执行计划代替索引快速全扫描(连接基线)

6.1、停用原基线
SQL> declare 
  2   rs pls_integer;
  3   begin
  4     rs:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
  5     plan_name=>'SQL_PLAN_a6bmz0rdcvsk7ecfa9855',
  6     attribute_name=>'ENABLED',
  7     attribute_value=>'NO');
  8  end;
  9  /

PL/SQL procedure successfully completed.

6.2、连接新基线

SQL>   declare 
  2      ln_ps pls_integer;
  3   begin
  4      ln_ps:=dbms_spm.load_plans_from_cursor_cache
  5      (sql_id=>'8uvxtbn283qwz',                    --带hint的sql
  6      plan_hash_value=>3321871023,
  7      sql_handle=>'SQL_a32e7f05dacde247'           --不带hint的sql_handle
  8      );
  9   end;
 10   /

PL/SQL procedure successfully completed.

SQL> select count(*) from t2 where deptno=10;

  COUNT(*)
----------
    524289

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bt33t77y2w9mf, child number 1
-------------------------------------
select count(*) from t2 where deptno=:"SYS_B_0"

Plan hash value: 3321871023

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  1105 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| T2   |   524K|  1536K|  1105   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=:SYS_B_0)

Note
-----
   - SQL plan baseline SQL_PLAN_a6bmz0rdcvsk71c6cf506 used for this statement


23 rows selected.