标签云
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,682)
- DB2 (22)
- MySQL (73)
- Oracle (1,544)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (67)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (565)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- 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)
-
最近发表
- 断电引起的ORA-08102: 未找到索引关键字, 对象号 39故障处理
- ORA-00227: corrupt block detected in control file
- 手工删除19c rac
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
分类目录归档:Oracle性能优化
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 使用,执行计划改变导致数据库负载过高
Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED
从Oracle 12C开始执行计划中可能会出现TABLE ACCESS BY INDEX ROWID BATCHED,官方的解释:TABLE ACCESS BY INDEX ROWID BATCHED:means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.主要意思:对于一个块中多个rowid,通过批量减少访问快的次数.而作为12.1的新特性,数据库是通过_optimizer_batch_table_access_by_rowid来控制的
数据库版本12.1
SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0
TABLE ACCESS BY INDEX ROWID BATCHED执行计划
SQL> set autot traceonly exp stat; SQL> var b1 number; SQL> set lines 150 SQL> set pages 10000 SQL> exec :b1:=18868701138; PL/SQL procedure successfully completed. SQL> SELECT BRAND_ID FROM T_USERTYPE_FULLNO WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2; Execution Plan ---------------------------------------------------------- Plan hash value: 942613467 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| XIFENFEI | 1 | 15 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_XIFENFEI | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<2) 3 - access("BILL_ID"=LTRIM(:B1,'0')) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
_optimizer_batch_table_access_by_rowid参数为true
SQL> col name for a32 SQL> col value for a24 col description for a70 set linesize 150 select a.ksppinm name,b.ksppstvl value,a.ksppdesc description SQL> SQL> SQL> 2 from x$ksppi a,x$ksppcv b 3 where a.inst_id = USERENV ('Instance') 4 and b.inst_id = USERENV ('Instance') 5 and a.indx = b.indx 6 and upper(a.ksppinm) LIKE upper('%¶m%') 7 order by name 8 / Enter value for param: _optimizer_batch_table_access_by_rowid old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by_rowid%') NAME VALUE DESCRIPTION ------------------------------------- ------------------------ ---------------------------------------------- _optimizer_batch_table_access_by_rowid TRUE enable table access by ROWID IO batching
设置_optimizer_batch_table_access_by_rowid为false,执行计划由TABLE ACCESS BY INDEX ROWID BATCHED变为TABLE ACCESS BY INDEX ROWID
SQL> set autot traceonly exp stat; SQL> var b1 number; SQL> set lines 150 SQL> set pages 10000 SQL> exec :b1:=18868701138; PL/SQL procedure successfully completed. SQL> alter session set "_optimizer_batch_table_access_by_rowid"=false; Session altered. SQL> SELECT BRAND_ID FROM XIFENFEI WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2; Execution Plan ---------------------------------------------------------- Plan hash value: 2797551150 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 15 | 2 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| XIFENFEI | 1 | 15 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IND_XIFENFEI | 1 | | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<2) 3 - access("BILL_ID"=LTRIM(:B1,'0')) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 552 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
optimizer_features_enable修改为11.2之后,_optimizer_batch_table_access_by_rowid会联锁变为fasle
SQL> alter session set optimizer_features_enable = '11.2.0.3'; Session altered. SQL> col name for a52 col value for a24 col description for a50 set linesize 150 select a.ksppinm name,b.ksppstvl value,a.ksppdesc description from x$ksppi a,x$ksppcv b where a.inst_idSQL> SQL> SQL> SQL> 2 3 = USERENV ('Instance') and b.inst_id = USERENV ('Instance') and a.indx = b.indx and upper(a.ksppinm) LIKE upper('%¶m%') order by name 4 5 6 7 8 / Enter value for param: _optimizer_batch_table_access_by old 6: and upper(a.ksppinm) LIKE upper('%¶m%') new 6: and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by%') NAME VALUE DESCRIPTION ------------------------------------------ -------------- ----------------------------------------- _optimizer_batch_table_access_by_rowid FALSE enable table access by ROWID IO batching
这里可以看出来,在调整optimizer_features_enable参数后,会直接影响某些数据库相关的优化器参数,例如:_optimizer_batch_table_access_by_rowid
11g中 connect by 语句执行计划改变
从10.2.0.3升级到11.2.0.4的朋友,如果细心会发现,以下sql在11.2.0.4中执行效率变低(该sql主要是获取连接用户获取权限信息)
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
如果你接触是Oracle版本比较多,而且还比较细心,你可能会进一步发现在11.2.0.2中该条sql是:select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 也就是说使用了/*+ connect_by_filtering */提示.我这里通过简单测试说明问题.
在11.2.0.4环境中
14:16:19 SQL> set autot trace exp stat 14:16:20 SQL> set time on 14:16:20 SQL> set timing on 14:16:20 SQL> var a1 number; 14:16:20 SQL> exec :a1:=6; PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.00 14:16:20 SQL> select privilege#,level from sysauth$ connect by grantee#=prior 14:16:20 SQL> privilege# and privilege#>0 start with grantee#=:a1 and privilege#>0 14:16:22 SQL> / 已用时间: 00: 00: 00.01 执行计划 ---------------------------------------------------------- Plan hash value: 2624122540 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 7 | 182 | 3 (34)| 00:00:01 | |* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | | | 2 | INDEX FAST FULL SCAN | I_SYSAUTH1 | 618 | 4944 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GRANTEE#"=PRIOR "PRIVILEGE#") filter("PRIVILEGE#">0 AND "GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed
这里可以看出来这里使用的执行计划使用了CONNECT BY NO FILTERING WITH START-WITH,逻辑读为7.
10.2.0.3环境中
14:32:57 SQL> set lines 150 14:33:00 SQL> set autot trace exp stat 14:33:01 SQL> set time on 14:33:01 SQL> set timing on 14:33:01 SQL> var a1 number; 14:33:01 SQL> exec :a1:=6; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 14:33:01 SQL> select privilege#,level from sysauth$ connect by grantee#=prior 14:33:01 SQL> privilege# and privilege#>0 start with grantee#=:a1 and privilege#>0 ; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2620769641 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 24 | 2 (0)| 00:00:01 | |* 1 | CONNECT BY WITH FILTERING| | | | | | |* 2 | INDEX RANGE SCAN | I_SYSAUTH1 | 3 | 24 | 2 (0)| 00:00:01 | | 3 | NESTED LOOPS | | | | | | | 4 | CONNECT BY PUMP | | | | | | |* 5 | INDEX RANGE SCAN | I_SYSAUTH1 | 3 | 24 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GRANTEE#"=PRIOR "PRIVILEGE#") filter("PRIVILEGE#">0) 2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0) 5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 583 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed
这里执行计划使用的为CONNECT BY WITH FILTERING,而且逻辑读为4,对于这个sql来说,使用CONNECT BY WITH FILTERING执行效率更高.
这里可以很明显的看到:connect by查询的执行计划从10g的CONNECT BY WITH FILTERING变为了11g中的CONNECT BY NO FILTERING WITH SW (UNIQUE),从而使得执行计划发生改变。但是Oracle一般有个特性,就是当引入新特性之时,一般都会伴随隐含参数或者event来屏蔽新特性.这里也例外,我们可以通过”_optimizer_connect_by_elim_dups” = false和”_connect_by_use_union_all” = “old_plan_mode”来屏蔽11g中关于connect by执行计划的改变,使得执行计划恢复到10G的CONNECT BY WITH FILTERING方式
14:30:45 SQL> alter session set "_optimizer_connect_by_elim_dups" = false; 会话已更改。 已用时间: 00: 00: 00.00 14:30:46 SQL> alter session set "_connect_by_use_union_all" = "old_plan_mode"; 会话已更改。 已用时间: 00: 00: 00.00 14:30:46 SQL> set autot trace exp stat 14:30:46 SQL> set time on 14:30:46 SQL> set timing on 14:30:46 SQL> var a1 number; 14:30:46 SQL> exec :a1:=6; PL/SQL 过程已成功完成。 已用时间: 00: 00: 00.00 14:30:46 SQL> select privilege#,level from sysauth$ connect by grantee#=prior 14:30:46 SQL> privilege# and privilege#>0 start with grantee#=:a1 and privilege#>0 ; 已用时间: 00: 00: 00.01 执行计划 ---------------------------------------------------------- Plan hash value: 2620769641 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 16 | 2 (0)| 00:00:01 | |* 1 | CONNECT BY WITH FILTERING| | | | | | |* 2 | INDEX RANGE SCAN | I_SYSAUTH1 | 2 | 16 | 2 (0)| 00:00:01 | | 3 | NESTED LOOPS | | | | | | | 4 | CONNECT BY PUMP | | | | | | |* 5 | INDEX RANGE SCAN | I_SYSAUTH1 | 2 | 16 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GRANTEE#"=PRIOR "PRIVILEGE#") filter("PRIVILEGE#">0) 2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0) 5 - access("GRANTEE#"=PRIOR "PRIVILEGE#" AND "PRIVILEGE#">0) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed
11.2.0.2中也许是考虑到connect by 不够成熟,因此使用了hint /*+ connect_by_filtering */ 来固定执行计划
14:22:09 SQL> select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior 14:22:09 SQL> privilege# and privilege#>0 start with grantee#=:a1 and privilege#>0 14:22:10 SQL> / 已用时间: 00: 00: 00.00 执行计划 ---------------------------------------------------------- Plan hash value: 2620769641 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 182 | 8 (25)| 00:00:01 | |* 1 | CONNECT BY WITH FILTERING| | | | | | |* 2 | INDEX RANGE SCAN | I_SYSAUTH1 | 2 | 16 | 2 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 5 | 105 | 4 (0)| 00:00:01 | | 4 | CONNECT BY PUMP | | | | | | |* 5 | INDEX RANGE SCAN | I_SYSAUTH1 | 2 | 16 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("GRANTEE#"=PRIOR "PRIVILEGE#") filter("PRIVILEGE#">0) 2 - access("GRANTEE#"=TO_NUMBER(:A1) AND "PRIVILEGE#">0) 5 - access("GRANTEE#"="connect$_by$_pump$_002"."prior privilege# " AND "PRIVILEGE#">0) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed
CONNECT BY NO FILTERING WITH SW (UNIQUE)和CONNECT BY WITH FILTERING,没有明显的优劣之分,只有在特定的情况下,进行了实际的测试,选择合适自己的sql的执行计划