【DB笔试面试607】在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
【DB笔试面试607】在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
小麦苗DBA宝典
发布于 2019-09-29 16:05:34
发布于 2019-09-29 16:05:34
♣
题目部分
在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
♣
答案部分
可以使用coe_load_sql_profile.sql脚本直接固定执行计划，该脚本也可以实现直接把sqlprofile直接迁移到其它库中。
很多DBA习惯于使用coe_xfr_sql_profile.sql脚本来固定SQL执行计划，但是这个脚本操作起来比较麻烦，而且容易出错。这个脚本的正确用途是用来做不同数据库之间SQL执行计划的固定。最方便的脚本是：coe_load_sql_profile.sql，使用这个脚本，只需要输入几个参数，就能完成快速恢复执行计划的任务。
需要注意的是，该脚本不能以SYS用户执行，否则会报如下的错误：
DECLARE
*
ERROR at line 1:
ORA-19381: cannot create staging table in SYS schema
ORA-06512: at "SYS.DBMS_SMB", line 313
ORA-06512: at "SYS.DBMS_SQLTUNE", line 6306
ORA-06512: at line 64
示例如下：
1.建立测试表和数据
SYS@dlhr> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
LHR@dlhr> create table scott.test as select * from dba_objects;
Table created.
LHR@dlhr> create index scott.idx_test_01 on scott.test(object_id);
Index created.
LHR@dlhr> exec dbms_stats.gather_table_stats('scott','test',cascade=>true);
PL/SQL procedure successfully completed.
LHR@dlhr> update scott.test set object_id=10 where object_id>10;
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select OBJECT_ID ,count(1) from scott.test group by OBJECT_ID;
OBJECT_ID COUNT(1)
---------- ----------
6 1
7 1
5 1
8 1
3 1
2 1
10 87076
4 1
9 1
9 rows selected.
2.执行查询语句
执行原有的查询语句，查看执行计划发现走索引，实际上这时表中大部分行的OBJECT_ID都已经被更新为10，所以走索引是不合理的。
LHR@dlhr> set autot traceonly explain stat
LHR@dlhr>
LHR@dlhr> select * from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3384190782
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13060 consistent gets
0 physical reads
0 redo size
9855485 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
LHR@dlhr> select /*+ full(test)*/* from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6973 consistent gets
0 physical reads
0 redo size
4159482 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
3.查询上面两个语句的SQL_ID、PLAN_HASH_VALUE
LHR@dlhr> set autot off
LHR@dlhr>
LHR@dlhr> col sql_text format a100
LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select * from scott.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------------------------------------- ------------- ---------------
select * from scott.test where object_id=10 cpk9jsg2qt52r 2317948335
LHR@dlhr> select sql_text,sql_id,plan_hash_value from v$sql where sql_text like 'select /*+ full(test)*/* from scott.test where object_id=10%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
---------------------------------------------------------------------------------------------------- ------------- ---------------
select /*+ full(test)*/* from scott.test where object_id=10 06c2mucgn6t5g 1357081020
4.把coe_load_sql_profile.sql放在$ORACLE_HOME/rdbms/admin下，或者放在/tmp下都可以。
5.使用coe_load_sql_profile.sql脚本
[ZHLHRSPMDB2:oracle]:/oracle>cd /tmp
[ZHLHRSPMDB2:oracle]:/tmp>
[ZHLHRSPMDB2:oracle]:/tmp>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 09:15:14 2016
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, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
LHR@dlhr > @/home/oracle/coe_load_sql_profile.sql
Parameter 1:
ORIGINAL_SQL_ID (required)
Enter value for 1: cpk9jsg2qt52r
Parameter 2:
MODIFIED_SQL_ID (required)
Enter value for 2: 06c2mucgn6t5g
PLAN_HASH_VALUE AVG_ET_SECS
-------------------- --------------------
1357081020 .058
Parameter 3:
PLAN_HASH_VALUE (required)
Enter value for 3: 1357081020
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "cpk9jsg2qt52r"
MODIFIED_SQL_ID: "06c2mucgn6t5g"
PLAN_HASH_VALUE: "1357081020"
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.3')
0004 DB_VERSION('11.2.0.3')
0005 ALL_ROWS
0006 OUTLINE_LEAF(@"SEL$1")
0007 FULL(@"SEL$1" "TEST"@"SEL$1")
0008 END_OUTLINE_DATA
dropping staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"
staging table "STGTAB_SQLPROF_CPK9JSG2QT52R" did not exist
creating staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"
packaging new sql profile into staging table "STGTAB_SQLPROF_CPK9JSG2QT52R"
PROFILE_NAME
------------------------------
CPK9JSG2QT52R_1357081020
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
-------------------- ------------------------------ ------------------------------ ------- --------
10910590721604799112 CPK9JSG2QT52R_1357081020 DEFAULT MANUAL ENABLED
SQL>SELECT description
2 FROM dba_sql_profiles WHERE name = :name;
DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORIGINAL:CPK9JSG2QT52R MODIFIED:06C2MUCGN6T5G PHV:1357081020 SIGNATURE:10910590721604799112 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;
****************************************************************************
* Enter LHR password to export staging table STGTAB_SQLPROF_cpk9jsg2qt52r
****************************************************************************
Export: Release 11.2.0.3.0 - Production on Tue Sep 12 10:39:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
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_CPK9JSG2QT52R 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 LHR file=STGTAB_SQLPROF_cpk9jsg2qt52r.dmp tables=STGTAB_SQLPROF_cpk9jsg2qt52r ignore=Y
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (
profile_name => 'CPK9JSG2QT52R_1357081020',
replace => TRUE,
staging_table_name => 'STGTAB_SQLPROF_cpk9jsg2qt52r',
staging_schema_owner => 'LHR' );
END;
/
adding: coe_load_sql_profile_cpk9jsg2qt52r.log (deflated 76%)
adding: STGTAB_SQLPROF_cpk9jsg2qt52r.dmp (deflated 89%)
adding: coe_load_sql_profile.log (deflated 62%)
deleting: coe_load_sql_profile.log
coe_load_sql_profile completed.
SQL>
6.查看产生的sql profile，此时原语句在不加hint的情况下也走全表扫了select * from dba_sql_profiles;
SQL>set line 9999
SQL> SELECT b.name,to_char(d.sql_text) sql_text, extractvalue(value(h),'.') as hints
2 FROM dba_sql_profiles d,SYS.SQLOBJ$DATA A,
SYS.SQLOBJ$ B,
4 TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(A.COMP_DATA),
5 '/outline_data/hint'))) h
where a.signature = b.signature
7 and a.category = b.category
8 and a.obj_type = b.obj_type
9 and a.plan_id = b.plan_id
10 and a.signature=d.signature
11 and D.name = 'CPK9JSG2QT52R_1357081020';
NAME SQL_TEXT HINTS
------------------------------ -------------------------------------------------- --------------------------------------------------
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 BEGIN_OUTLINE_DATA
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 IGNORE_OPTIM_EMBEDDED_HINTS
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 DB_VERSION('11.2.0.3')
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 ALL_ROWS
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 OUTLINE_LEAF(@"SEL$1")
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 FULL(@"SEL$1" "TEST"@"SEL$1")
CPK9JSG2QT52R_1357081020 select * from scott.test where object_id=10 END_OUTLINE_DATA
7.验证SQL Profile是否生效
SYS@dlhr> set autot traceonly explain stat
SYS@dlhr> select * from scott.test where object_id=10;
87076 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 351 (2)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 98 | 351 (2)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=10)
Note
-----
- SQL profile "CPK9JSG2QT52R_1357081020" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6973 consistent gets
0 physical reads
0 redo size
4159482 bytes sent via SQL*Net to client
64375 bytes received via SQL*Net from client
5807 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
87076 rows processed
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-08-02，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读