当前位置：首页 > news >正文

Oracle 使用 sql profile 固定执行计划

news 2025/7/26 22:12:24

测试使用 sql profile 固定执行计划：

Oracle 10g之前有outlines,10g之后 sql profile 。如果针对非绑定变量的sql,outlines则效果不佳，不建议使用。

1、准备测试用表
SQL> create table zzh_ob as select * from dba_objects;
SQL> create index ind_obid on zzh_ob(object_id);
SQL> select object_id from zzh_ob where rownum<2;
OBJECT_ID
----------
16
SQL> exec dbms_stats.gather_table_stats(user,'zzh_ob',cascade=>true);

原sql执行计划:
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 3485916696

------------------------------------------------------------------------------------------------
| Id | Operation                           | Name     | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   127 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID BATCHED| ZZH_OB   |     1 |   127 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN                  | IND_OBID |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=16)

新sql执行计划
SQL> select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |   127 |   388   (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| ZZH_OB |     1 |   127 |   388   (1)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=16)

2、获取新sql的sql_id
SQL> col sql_id for a20
SQL> col sql_text for a120
SQL> select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%';
SQL_ID               SQL_TEXT
-------------------- ------------------------------------------------------------------------------------------------------------------------
g5btxvsh51ct5        EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
4kn9w9q3xf73k        EXPLAIN PLAN SET STATEMENT_ID='PLUS1200001' FOR select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16
68x7hd7uaqqk1        select sql_id,sql_text from v$sql where sql_text like '%full(zzh_ob)%'
1syfgv2q5ggn2        select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16

3、获取新sql的outline
SQL> set pagesize 1000
SQL> select * from table(dbms_xplan.display_cursor('1syfgv2q5ggn2',null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1syfgv2q5ggn2, child number 0
-------------------------------------
select /*+ full(zzh_ob) */ * from zzh_ob where object_id=16

Plan hash value: 474274488

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |   388 (100)|          |
|* 1 | TABLE ACCESS FULL| ZZH_OB |     1 |   127 |   388   (1)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------

Outline Data
-------------

/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "ZZH_OB"@"SEL$1")
      END_OUTLINE_DATA
*/

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

1 - filter("OBJECT_ID"=16)

32 rows selected.

4、创建sql profile(SQLPROFILE_01)
SQL> declare
   v_hints sys.sqlprof_attr;
   begin
   v_hints:=sys.sqlprof_attr(
     'BEGIN_OUTLINE_DATA',
     'IGNORE_OPTIM_EMBEDDED_HINTS',
     'OPTIMIZER_FEATURES_ENABLE(''19.1.0'')',
'DB_VERSION(''19.1.0'')',
     'ALL_ROWS',
     'OUTLINE_LEAF(@"SEL$1")',
     'FULL(@"SEL$1" "ZZH_OB"@"SEL$1")',
     'END_OUTLINE_DATA');
   dbms_sqltune.import_sql_profile(
     'select * from zzh_ob where object_id=16',
     v_hints,'SQLPROFILE_01',
    force_match=>true,replace=>false);
end;
/

5、查看是否使用 sql profile
SQL> set autot trace explain
SQL> select * from zzh_ob where object_id=16;
Execution Plan
----------------------------------------------------------
Plan hash value: 474274488

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

1 - filter("OBJECT_ID"=16)

Note
-----
- SQL profile "SQLPROFILE_01" used for this statement

Statistics
----------------------------------------------------------
        247 recursive calls
          0 db block gets
       1544 consistent gets
          2 physical reads
          0 redo size
       2685 bytes sent via SQL*Net to client
        401 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
          1 rows processed

查询已经创建的 sql profile ：
SELECT name, created, category, sql_Text from dba_sql_profiles ORDER BY created DESC;

查看全文

http://www.lryc.cn/news/514011.html