Oracle中使用coe_load_sql_profile脚本固定执行计划

最新推荐文章于 2024-04-19 13:33:10 发布

lzyever

最新推荐文章于 2024-04-19 13:33:10 发布

阅读量812

点赞数 29

分类专栏： Oracle 文章标签： oracle sql 数据库

本文链接：https://blog.csdn.net/lzyever/article/details/136850881

版权

Oracle 专栏收录该内容

132 篇文章 5 订阅

订阅专栏

coe_load_sql_profile.sql 是Oracle数据库环境下用于迁移或固定SQL执行计划的一个脚本，它可以帮助DBA将特定SQL语句的高效执行计划转化为SQL Profile，并将其应用到目标数据库中。

SQL Profile是一种Oracle数据库中用来指导优化器选择特定执行计划的方法。

操作演示：使用 coe_load_sql_profile.sql 脚本来固定执行计划

1、准备演示环境

此操作需要以具有足够权限（如DBA权限）的非SYS用户，本操作中的scott用户已经具有了dba权限。

[oracle@db11g ~]$ sqlplus scott/scott
SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 19 16:56:53 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SCOTT@PROD11G> 
--查看一下emp表
SCOTT@PROD11G> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

--给ename列创建一个索引emp_idx1
SCOTT@PROD11G> create index emp_idx1 on emp(ename);

Index created.

--运行SQL并查看执行计划
SCOTT@PROD11G> set autot trace

SCOTT@PROD11G> select ename from emp where ename='name';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 654424206

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     6 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| EMP_IDX1 |     1 |     6 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("ENAME"='name')


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

SCOTT@PROD11G> 

-- 可以看到，执行计划走了索引扫描INDEX RANGE SCAN
-- 下面使用hint强制走全表扫描

SCOTT@PROD11G> select /*+ FULL (EMP) */ ename from emp where ename='name';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

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

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

   1 - filter("ENAME"='name')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        333  bytes sent via SQL*Net to client
        513  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SCOTT@PROD11G> 

-- 可以看到使用hint强制sql走了全表扫描
-- 下面查看sql的sql_id,plan_hash_value,sql_text

select sql_id ,plan_hash_value, sql_text from v$sql where sql_text like 'select%from emp%ename=%';
SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------------------------------
66q0r9w4df2f5       654424206 select ename from emp where ename='name'
3w056txhg3vdu      3956160932 select /*+ FULL (EMP) */ ename from emp where ename='name'

2、使用 coe_load_sql_profile脚本固定执行计划

下面执行 coe_load_sql_profile.sql 脚本来固定全表扫描的执行计划。

--执行coe_load_sql_profile.sql 脚本

SCOTT@PROD11G> @coe_load_sql_profile.sql

Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 66q0r9w4df2f5    --输入源SQL的SQL_ID

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 3w056txhg3vdu    --输入要固定执行计划的SQL的SQL_ID


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          3956160932                 .002

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 3956160932     --输入要固定执行计划的SQL的PLAN_HASH_VALUE

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "66q0r9w4df2f5"
MODIFIED_SQL_ID: "3w056txhg3vdu"
PLAN_HASH_VALUE: "3956160932"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for modified SQL_ID &&modified_sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>
SQL>SET ECHO OFF;
0001 BEGIN_OUTLINE_DATA
0002 IGNORE_OPTIM_EMBEDDED_HINTS
0003 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
0004 DB_VERSION('11.2.0.4')
0005 OPT_PARAM('_table_scan_cost_plus_one' 'false')
0006 ALL_ROWS
0007 OUTLINE_LEAF(@"SEL$1")
0008 FULL(@"SEL$1" "EMP"@"SEL$1")
0009 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_66Q0R9W4DF2F5"
creating staging table "STGTAB_SQLPROF_66Q0R9W4DF2F5"
packaging new sql profile into staging table "STGTAB_SQLPROF_66Q0R9W4DF2F5"

PROFILE_NAME
------------------------------
66Q0R9W4DF2F5_3956160932
SQL>REM
SQL>REM SQL Profile
SQL>REM ~~~~~~~~~~~
SQL>REM
SQL>SELECT signature, name, category, type, status
  2    FROM dba_sql_profiles WHERE name = :name;

           SIGNATURE NAME                           CATEGORY                       TYPE    STATUS
-------------------- ------------------------------ ------------------------------ ------- --------
10938475214343355302 66Q0R9W4DF2F5_3956160932       DEFAULT                        MANUAL  ENABLED
SQL>SELECT description
  2    FROM dba_sql_profiles WHERE name = :name;

DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:66Q0R9W4DF2F5 MODIFIED:3W056TXHG3VDU PHV:3956160932 SIGNATURE:10938475214343355302 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;

****************************************************************************
* Enter SCOTT password to export staging table STGTAB_SQLPROF_66q0r9w4df2f5
****************************************************************************

Export: Release 11.2.0.4.0 - Production on Tue Mar 19 17:32:01 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:  ---此处要输入操作用户scott的密码

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported

About to export specified tables via Conventional Path ...
. . exporting table   STGTAB_SQLPROF_66Q0R9W4DF2F5          1 rows exported
Export terminated successfully without warnings.


If you need to implement this Custom SQL Profile on a similar system,
import and unpack using these commands:

imp SCOTT file=STGTAB_SQLPROF_66q0r9w4df2f5.dmp tables=STGTAB_SQLPROF_66q0r9w4df2f5 ignore=Y

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => '66Q0R9W4DF2F5_3956160932',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_66q0r9w4df2f5',
staging_schema_owner => 'SCOTT' );
END;
/

updating: coe_load_sql_profile_66q0r9w4df2f5.log (deflated 76%)
updating: STGTAB_SQLPROF_66q0r9w4df2f5.dmp (deflated 89%)
  adding: coe_load_sql_profile.log (deflated 62%)

deleting: coe_load_sql_profile.log


coe_load_sql_profile completed.
SQL>

--当看到【coe_load_sql_profile completed.】时，表示执行计划固定完成。
--下面验证一下，直接执行sql然后查看执行计划

SCOTT@PROD11G> select ename from emp where ename='name';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

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

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

   1 - filter("ENAME"='name')

Note
-----
   - SQL profile "66Q0R9W4DF2F5_3956160932" used for this statement


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

SCOTT@PROD11G> 

--可以看到SQL已经走了全表扫描，并且可以看到使用了SQL profile【SQL profile "66Q0R9W4DF2F5_3956160932" used for this statement】固定了执行计划

3、查看SQL profile

创建完成后，可以查询USER_SQL_PROFILES或DBA_SQL_PROFILES视图来确认SQL Profile是否成功创建和应用到了相应的SQL语句上。

SCOTT@PROD11G> select * from dba_sql_profiles;

NAME                           CATEGORY                        SIGNATURE
------------------------------ ------------------------------ ----------
SQL_TEXT
--------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
LAST_MODIFIED
---------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
TYPE    STATUS   FOR    TASK_ID TASK_EXEC_NAME                 TASK_OBJ_ID
------- -------- --- ---------- ------------------------------ -----------
TASK_FND_ID TASK_REC_ID
----------- -----------
66Q0R9W4DF2F5_3956160932       DEFAULT                        1.0938E+19
select ename from emp where ename='name'
19-MAR-24 05.32.00.000000 PM
19-MAR-24 05.32.00.000000 PM
ORIGINAL:66Q0R9W4DF2F5 MODIFIED:3W056TXHG3VDU PHV:3956160932 SIGNATURE:109384752
14343355302 CREATED BY COE_LOAD_SQL_PROFILE.SQL
MANUAL  ENABLED  NO

-- 可以从dba_sql_profiles查询到sql profile ：【66Q0R9W4DF2F5_3956160932】

4、总结

通过固定执行计划的方法，即使在Oracle优化器可能因为统计信息变化而选择不同执行计划的情况下，SQL Profile也能强制数据库按照已知高效的执行计划来执行SQL语句。这有助于保持应用程序的性能稳定性。

lzyever

关注

29
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Oracle中使用coe_load_sql_profile脚本固定执行计划

通过固定执行计划的方法，即使在Oracle优化器可能因为统计信息变化而选择不同执行计划的情况下，SQL Profile也能强制数据库按照已知高效的执行计划来执行SQL语句。这有助于保持应用程序的性能稳定性。
复制链接

扫一扫