标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,666)
- DB2 (22)
- MySQL (73)
- Oracle (1,528)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (559)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (77)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
标签归档:hint
sql profile改变hint的执行计划以及coe脚本实现sql id中无PLAN HASH VALUE的sql profile生成
在sql profile中使用的过程中,有以下几个问题,这里通过测试确认了几个问题:
1.coe_xfr_sql_profile可以执行sql_id中无PLAN HASH VALUE的执行计划
2.在sql使用过程中,sql profile是否会覆盖hint,通过测试证明sqlprofile可以覆盖hint的执行计划
3.coe_load_sql_profile可以使用于通过修改hint(改变sql id,然后通过指定两次不同的sql id实现sql profile固定hint的sql的执行计划)
测试sqlprofile会影响hint
SQL> create table t_xifenfei as select object_id,object_name from user_objects; Table created. SQL> create index idx_t_xifenfei_id on t_xifenfei(OBJECT_ID); Index created. SQL> create index idx_t_xifenfei2_id on t_xifenfei(OBJECT_ID,1); Index created. ---使用hint等方式确定三种方式执行计划 SQL> SET LINES 150 SQL> SET AUTOT ON SQL> SET PAGES 150 SQL> select * from t_xifenfei t where OBJECT_ID=10; <---默认使用IDX_T_XIFENFEI_ID index no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 308895000 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 6 recursive calls 0 db block gets 13 consistent gets 1 physical reads 0 redo size 410 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 SQL> SET AUTOT OFF SQL> SET LINES 150 SQL> SET AUTOT ON SQL> SET PAGES 150 SQL> select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_xifenfei t where OBJECT_ID=10; <---指定使用idx_t_xifenfei2_id index no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2143066642 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI2_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 11 consistent gets 1 physical reads 0 redo size 410 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 SQL> SET AUTOT OFF SQL> SET LINES 150 SQL> SET AUTOT ON SQL> SET PAGES 150 SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10; <---指定使用idx_t_xifenfei1_id index no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 308895000 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI_ID | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 11 consistent gets 0 physical reads 0 redo size 410 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 SQL> SET AUTOT OFF --查询三种情况下sql_id SQL> col SQL_TEXT for a50 SQL> SELECT SQL_TEXT,SQL_ID FROM V$SQL WHERE sql_text like 'select%OBJECT_ID=10'; SQL_TEXT SQL_ID -------------------------------------------------- ------------- select /*+ INDEX(T idx_t_xifenfei2_id)*/* from t_x 5291sfrd2p35y ifenfei t where OBJECT_ID=10 select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xi 143q33ff4f06w fenfei t where OBJECT_ID=10 select * from t_xifenfei t where OBJECT_ID=10 b5zuac0zqm9nw --使用sqlprofile固定其他两个未使用index IDX_T_XIFENFEI2_ID的sql使用该索引 SQL> DECLARE 2 SQL_FTEXT CLOB; 3 BEGIN 4 SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'b5zuac0zqm9nw'; 5 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( 6 SQL_TEXT => SQL_FTEXT, 7 PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'), 8 NAME => 'PROFILE_b5zuac0zqm9nw', 9 REPLACE => TRUE, 10 FORCE_MATCH => TRUE 11 ); 12 END; 13 / PL/SQL procedure successfully completed. SQL> SQL> DECLARE 2 SQL_FTEXT CLOB; 3 BEGIN 4 SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '143q33ff4f06w'; 5 DBMS_SQLTUNE.IMPORT_SQL_PROFILE( 6 SQL_TEXT => SQL_FTEXT, 7 PROFILE => SQLPROF_ATTR('INDEX(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")'), 8 NAME => 'PROFILE_143q33ff4f06w', 9 REPLACE => TRUE, 10 FORCE_MATCH => TRUE 11 ); 12 END; 13 / PL/SQL procedure successfully completed. --验证查询效果 SQL> SET LINES 150 SQL> SET AUTOT ON SQL> SET PAGES 150 SQL> select * from t_xifenfei t where OBJECT_ID=10; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2143066642 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI2_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=2) - SQL profile "PROFILE_b5zuac0zqm9nw" used for this statement <--使用sql profile Statistics ---------------------------------------------------------- 37 recursive calls 0 db block gets 23 consistent gets 1 physical reads 0 redo size 410 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select /*+ INDEX(T idx_t_xifenfei_id)*/* from t_xifenfei t where OBJECT_ID=10; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2143066642 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 79 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI2_ID | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - dynamic sampling used for this statement (level=2) - SQL profile "PROFILE_143q33ff4f06w" used for this statement <--使用sql profile,hint未被正常使用,证明sql profile影响hint,使得sql使用sql profile而不hint Statistics ---------------------------------------------------------- 10 recursive calls 0 db block gets 16 consistent gets 1 physical reads 0 redo size 410 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
这里可以知道,在有sqlprofile的情况下,可以影响以前的hint提示,使得hint无效,继续使用sql profile,感谢北京–weejar的试验证明
使用coe_load_sql_profile方式指定修改sql后的执行计划
SQL> @/tmp/coe_load_sql_profile.sql Parameter 1: ORIGINAL_SQL_ID (required) Enter value for 1: b5zuac0zqm9nw Parameter 2: MODIFIED_SQL_ID (required) Enter value for 2: 5291sfrd2p35y PLAN_HASH_VALUE AVG_ET_SECS -------------------- -------------------- 2143066642 .004 Parameter 3: PLAN_HASH_VALUE (required) Enter value for 3: 2143066642 Values passed to coe_load_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ORIGINAL_SQL_ID: "b5zuac0zqm9nw" MODIFIED_SQL_ID: "5291sfrd2p35y" PLAN_HASH_VALUE: "2143066642" 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 ALL_ROWS 0006 OUTLINE_LEAF(@"SEL$1") 0007 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID") 0008 END_OUTLINE_DATA dropping staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW" creating staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW" packaging new sql profile into staging table "STGTAB_SQLPROF_B5ZUAC0ZQM9NW" PROFILE_NAME ------------------------------ B5ZUAC0ZQM9NW_2143066642 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 -------------------- ------------------------------ ------------------------------ ------- -------- 6715790053022671751 B5ZUAC0ZQM9NW_2143066642 DEFAULT MANUAL ENABLED SQL>SET ECHO OFF; **************************************************************************** * Enter CHF password to export staging table STGTAB_SQLPROF_b5zuac0zqm9nw **************************************************************************** Export: Release 11.2.0.4.0 - Production on Mon Jun 1 00:10:11 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Password: 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 ZHS16GBK 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_B5ZUAC0ZQM9NW 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 CHF file=STGTAB_SQLPROF_b5zuac0zqm9nw.dmp tables=STGTAB_SQLPROF_b5zuac0zqm9nw ignore=Y BEGIN DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF ( profile_name => 'B5ZUAC0ZQM9NW_2143066642', replace => TRUE, staging_table_name => 'STGTAB_SQLPROF_b5zuac0zqm9nw', staging_schema_owner => 'CHF' ); END; / updating: coe_load_sql_profile_b5zuac0zqm9nw.log (deflated 76%) updating: STGTAB_SQLPROF_b5zuac0zqm9nw.dmp (deflated 89%) adding: coe_load_sql_profile.log (deflated 62%) deleting: coe_load_sql_profile.log coe_load_sql_profile completed. SQL>SET LINES 150 SQL>SET AUTOT ON SQL>SET PAGES 150 select * from t_xifenfei t where OBJECT_ID=10;SQL> no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2143066642 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 474 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 6 | 474 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI2_ID | 2 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - SQL profile "B5ZUAC0ZQM9NW_2143066642" used for this statement <------sql直接使用coe_load_sql_profile固定执行计划成功 Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 7 consistent gets 1 physical reads 0 redo size 410 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
使用coe_load_sql_profile也可以sql_id中没有PLAN HASH VALUE的执行计划,另外还可以实现直接把sqlprofile直接迁移到其他库中
coe_xfr_sql_profile固定没有sql_id没有直接PLAN HASH VALUE的执行计划
SQL> @/tmp/coe_xfr_sql_profile Parameter 1: SQL_ID (required) Enter value for 1: b5zuac0zqm9nw PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 308895000 .005 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 2143066642 <---该PLAN_HASH_VALUE不存在该sql_id对应的PLAN_HASH_VALUE中 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "b5zuac0zqm9nw" PLAN_HASH_VALUE: "2143066642" SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&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 SQL_ID &&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>SET TERM OFF; Execute coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql on TARGET system in order to create a custom SQL Profile with plan 2143066642 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL>@coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql 11.4.4.4 2015/06/01 carlos.sierra $ SQL>REM SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved. SQL>REM SQL>REM AUTHOR SQL>REM carlos.sierra@oracle.com SQL>REM SQL>REM SCRIPT SQL>REM coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script is generated by coe_xfr_sql_profile.sql SQL>REM It contains the SQL*Plus commands to create a custom SQL>REM SQL Profile for SQL_ID b5zuac0zqm9nw based on plan hash SQL>REM value 2143066642. SQL>REM The custom SQL Profile to be created by this script SQL>REM will affect plans for SQL commands with signature SQL>REM matching the one for SQL Text below. SQL>REM Review SQL Text and adjust accordingly. SQL>REM SQL>REM PARAMETERS SQL>REM None. SQL>REM SQL>REM EXAMPLE SQL>REM SQL> START coe_xfr_sql_profile_b5zuac0zqm9nw_2143066642.sql; SQL>REM SQL>REM NOTES SQL>REM 1. Should be run as SYSTEM or SYSDBA. SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege. SQL>REM 3. SOURCE and TARGET systems can be the same or similar. SQL>REM 4. To drop this custom SQL Profile after it has been created: SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_b5zuac0zqm9nw_2143066642'); SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license SQL>REM for the Oracle Tuning Pack. SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below. SQL>REM By doing so you can create a custom SQL Profile for the original SQL>REM SQL but with the Plan captured from the modified SQL (with Hints). SQL>REM SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL>REM SQL>VAR signature NUMBER; SQL>VAR signaturef NUMBER; SQL>REM SQL>DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 PROCEDURE wa (p_line IN VARCHAR2) IS 5 BEGIN 6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line); 7 END wa; 8 BEGIN 9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE); 10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE); 11 -- SQL Text pieces below do not have to be of same length. 12 -- So if you edit SQL Text (i.e. removing temporary Hints), 13 -- there is no need to edit or re-align unmodified pieces. 14 wa(q'[select * from t_xifenfei t where OBJECT_ID=10]'); 15 DBMS_LOB.CLOSE(sql_txt); 16 h := SYS.SQLPROF_ATTR( 17 q'[BEGIN_OUTLINE_DATA]', 18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', 20 q'[DB_VERSION('11.2.0.4')]', 21 q'[ALL_ROWS]', 22 q'[OUTLINE_LEAF(@"SEL$1")]', 23 q'[INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" "IDX_T_XIFENFEI2_ID")]', 24 q'[END_OUTLINE_DATA]'); 25 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 26 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE); 27 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 28 sql_text => sql_txt, 29 profile => h, 30 name => 'coe_b5zuac0zqm9nw_2143066642', 31 description => 'coe b5zuac0zqm9nw 2143066642 '||:signature||' '||:signaturef||'', 32 category => 'DEFAULT', 33 validate => TRUE, 34 replace => TRUE, 35 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 36 DBMS_LOB.FREETEMPORARY(sql_txt); 37 END; 38 / PL/SQL procedure successfully completed. SQL>WHENEVER SQLERROR CONTINUE SQL>SET ECHO OFF; SIGNATURE --------------------- 6715790053022671751 SIGNATUREF --------------------- 445801536248906164 ... manual custom SQL Profile has been created COE_XFR_SQL_PROFILE_b5zuac0zqm9nw_2143066642 completed SQL>set autot on SQL>set lines 150 SQL>set pages 150 SQL>select * from t_xifenfei t where OBJECT_ID=10; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2143066642 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 474 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 6 | 474 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_T_XIFENFEI2_ID | 2 | | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=10) Note ----- - SQL profile "coe_b5zuac0zqm9nw_2143066642" used for this statement Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 7 consistent gets 1 physical reads 0 redo size 410 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 SQL>
通过验证,证明在sql_id中没有对应的PLAN HASH VALUE之时,也可以通过coe_xfr_sql_profile指定PLAN HASH VALUE来固定某个sql_id的执行计划.
以前写过相关关于sql profile的文章:sql profile 使用,执行计划改变导致数据库负载过高