【DB笔试面试607】在Oracle中，coe_load_sql_profile.sql脚本的作用是什么？
- 2019 年 10 月 10 日
- 筆記
♣
题目部分
在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程序员面试笔试宝典》，作者：李华荣。
