标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 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-600 kcratr_nab_less_than_odr 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 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,884)
- DB2 (22)
- MySQL (82)
- Oracle (1,709)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (662)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
- OBET-Oracle Block Editor Tool使用说明
标签归档:sql profile
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 使用,执行计划改变导致数据库负载过高
执行计划改变导致数据库负载过高
核心生产库突然负载飙升,我的压力来了,通过分析是一条sql的执行计划改变引起该故障,最终通过sql profile固定执行计划解决该问题
数据库主机负载
这里明显表现系统load 偏高,而且还在上升中;top的进程中,占用cpu都计划100%
top - 16:25:39 up 123 days, 1:42, 4 users, load average: 46.19, 45.08, 43.93 Tasks: 1469 total, 28 running, 1439 sleeping, 0 stopped, 2 zombie Cpu(s): 45.9%us, 1.1%sy, 0.0%ni, 47.1%id, 5.2%wa, 0.1%hi, 0.6%si, 0.0%st Mem: 264253752k total, 262605260k used, 1648492k free, 413408k buffers Swap: 33554424k total, 458684k used, 33095740k free, 67110504k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2622 oracle 18 0 150g 34m 28m R 100.0 0.0 11:58.21 oracleq9db1 (LOCAL=NO) 15881 oracle 21 0 150g 35m 28m R 100.0 0.0 72:14.20 oracleq9db1 (LOCAL=NO) 17214 oracle 19 0 150g 38m 32m R 100.0 0.0 20:47.44 oracleq9db1 (LOCAL=NO) 17705 oracle 21 0 150g 34m 28m R 100.0 0.0 27:33.00 oracleq9db1 (LOCAL=NO) 6110 oracle 19 0 150g 34m 28m R 99.8 0.0 12:33.34 oracleq9db1 (LOCAL=NO) 6876 oracle 19 0 150g 34m 27m R 99.5 0.0 12:43.90 oracleq9db1 (LOCAL=NO) 17205 oracle 23 0 150g 34m 27m R 99.5 0.0 21:37.18 oracleq9db1 (LOCAL=NO) 24629 oracle 20 0 150g 35m 29m R 99.5 0.0 28:10.62 oracleq9db1 (LOCAL=NO) 26959 oracle 19 0 150g 34m 27m R 99.5 0.0 47:17.87 oracleq9db1 (LOCAL=NO) 7655 oracle 18 0 150g 30m 25m R 98.5 0.0 2:28.45 oracleq9db1 (LOCAL=NO) 16377 oracle 18 0 150g 34m 28m R 98.5 0.0 36:07.11 oracleq9db1 (LOCAL=NO) 24637 oracle 18 0 150g 37m 30m R 98.2 0.0 26:39.15 oracleq9db1 (LOCAL=NO) 6106 oracle 21 0 150g 40m 33m R 97.2 0.0 11:37.75 oracleq9db1 (LOCAL=NO) 28785 oracle 18 0 150g 34m 28m R 96.9 0.0 24:03.29 oracleq9db1 (LOCAL=NO) 24278 oracle 17 0 150g 31m 26m S 96.5 0.0 3:15.51 oracleq9db1 (LOCAL=NO) 24283 oracle 17 0 150g 33m 28m S 96.5 0.0 6:25.26 oracleq9db1 (LOCAL=NO) 7098 oracle 18 0 150g 32m 27m R 94.6 0.0 2:20.22 oracleq9db1 (LOCAL=NO) 6874 oracle 17 0 150g 34m 28m R 87.0 0.0 12:02.92 oracleq9db1 (LOCAL=NO) 18206 oracle 16 0 150g 34m 27m R 86.1 0.0 16:11.28 oracleq9db1 (LOCAL=NO) 7096 oracle 17 0 150g 29m 24m R 85.4 0.0 3:01.72 oracleq9db1 (LOCAL=NO)
数据库等待事件
SID EVENT
---------- ----------------------------------------------------------------
183 gc cr request
185 latch: cache buffers chains
239 db file sequential read
292 gc cr request
406 gc cr request
410 db file sequential read
463 gc current request
572 gc buffer busy acquire
575 gc buffer busy acquire
577 latch: cache buffers chains
629 db file sequential read
747 gc cr request
919 latch: cache buffers chains
974 gc cr request
1033 log file sync
1141 db file parallel write
1153 gc cr request
1199 db file sequential read
1378 db file sequential read
1495 gc cr request
1540 db file parallel write
1547 gc buffer busy acquire
1662 gc cr request
1715 gc buffer busy acquire
1770 SQL*Net message to client
1830 latch: cache buffers chains
1884 gc cr request
2113 db file sequential read
2173 db file sequential read
2229 rdbms ipc reply
2292 db file sequential read
2341 db file sequential read
2348 gc cr request
2460 gc cr request
2632 gc cr request
2684 gc cr request
2687 db file sequential read
2749 db file sequential read
2913 gc cr request
2967 db file sequential read
3038 gc cr request
3087 SQL*Net message to client
3089 gc cr request
3194 db file sequential read
3195 db file sequential read
3309 latch: cache buffers chains
3371 gc cr request
3485 gc cr request
3535 gc cr request
可以这里有很多gc cr request等待和cache buffers chains等待,第一反应就是很可能系统某条sql执行计划不正确导致逻辑读剧增.
分析awr报告
故障时候的逻辑读 top sql可以看出来cdwjdd67x27mh的sql逻辑读异常大

分析正常时间点awr报告中cdwjdd67x27mh逻辑读情况

通过对比可以发现两个awr中,cdwjdd67x27mh 语句执行次数差不多,但是单次逻辑读从800多突变为34000多,增加了40多倍
cdwjdd67x27mh 语句分析
执行计划
17:36:08 sys@Q9DB>select * from table(dbms_xplan.display_awr('cdwjdd67x27mh'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cdwjdd67x27mh
--------------------
select * from ( select * from GE_TAOBAO_BILL o
WHERE 1=1 and
o.CREATED_TIME >= :1 and o.CREATED_TIME < :2
and o.REC_SITE_ID = :3
and o.STATUS_ID = :4
and o.SERVICE_TYPE = :5 ) where rownum
<= 36000
Plan hash value: 647855111
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | |
| 1 | COUNT STOPKEY | | | | | | |
| 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR | | 1 | 455 | 5 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL | 1 | 455 | 5 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN | IDX_TAOBAO_BILL_CR_ISDP_S | 1 | | 4 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID cdwjdd67x27mh
--------------------
select * from ( select * from GE_TAOBAO_BILL o
WHERE 1=1 and
o.CREATED_TIME >= :1 and o.CREATED_TIME < :2
and o.REC_SITE_ID = :3
and o.STATUS_ID = :4
and o.SERVICE_TYPE = :5 ) where rownum
<= 36000
Plan hash value: 2979024279
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| | ||
| 1 | COUNT STOPKEY | | | | | | ||
| 2 | FILTER | | | | | | ||
| 3 | PARTITION RANGE ITERATOR | | 1 | 455 | 13 (0)| 00:00:01 | KEY | KEY |
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID| GE_TAOBAO_BILL | 1 | 455 | 13 (0)| 00:00:01 | KEY | KEY |
| 5 | INDEX RANGE SCAN | IDX_TAOBAO_BILL_CR_REC_S | 4 | | 9 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------
这里可以发现cdwjdd67x27mh在数据库中有两个执行计划
通过awr数据,看执行计划的变化情况
10:50:29 sys@Q9DB>select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
10:50:30 2 from dba_hist_sqlstat a, dba_hist_snapshot b
10:50:30 3 where sql_id ='cdwjdd67x27mh'
10:50:30 4 and a.snap_id = b.snap_id
10:50:30 5 order by instance_number,begin_interval_time desc;
INSTANCE_NUMBER SNAP_ID SQL_ID PLAN_HASH_VALUE BEGIN_INTERVAL_TIME
--------------- ---------- ------------- --------------- ----------------------------------------
1 20719 cdwjdd67x27mh 2979024279 12-MAY-14 05.00.12.753 PM
1 20719 cdwjdd67x27mh 647855111 12-MAY-14 05.00.12.753 PM
1 20719 cdwjdd67x27mh 2979024279 12-MAY-14 05.00.12.702 PM
1 20719 cdwjdd67x27mh 647855111 12-MAY-14 05.00.12.702 PM
1 20718 cdwjdd67x27mh 647855111 12-MAY-14 04.00.24.197 PM
1 20718 cdwjdd67x27mh 2979024279 12-MAY-14 04.00.24.197 PM
1 20718 cdwjdd67x27mh 647855111 12-MAY-14 04.00.24.172 PM
1 20718 cdwjdd67x27mh 2979024279 12-MAY-14 04.00.24.172 PM
1 20717 cdwjdd67x27mh 647855111 12-MAY-14 03.11.22.251 PM
1 20717 cdwjdd67x27mh 2979024279 12-MAY-14 03.11.22.251 PM
1 20717 cdwjdd67x27mh 647855111 12-MAY-14 03.11.22.188 PM
1 20717 cdwjdd67x27mh 2979024279 12-MAY-14 03.11.22.188 PM
………………
1 20696 cdwjdd67x27mh 2979024279 11-MAY-14 07.00.07.142 PM
1 20696 cdwjdd67x27mh 2979024279 11-MAY-14 07.00.07.105 PM
1 20695 cdwjdd67x27mh 2979024279 11-MAY-14 06.00.12.771 PM
1 20695 cdwjdd67x27mh 2979024279 11-MAY-14 06.00.12.707 PM
1 20694 cdwjdd67x27mh 2979024279 11-MAY-14 05.00.48.249 PM
1 20694 cdwjdd67x27mh 2979024279 11-MAY-14 05.00.48.170 PM
1 20693 cdwjdd67x27mh 2979024279 11-MAY-14 04.00.37.841 PM
…………
2 20719 cdwjdd67x27mh 2979024279 12-MAY-14 05.00.12.753 PM
2 20719 cdwjdd67x27mh 2979024279 12-MAY-14 05.00.12.702 PM
2 20718 cdwjdd67x27mh 2979024279 12-MAY-14 04.00.24.197 PM
2 20718 cdwjdd67x27mh 2979024279 12-MAY-14 04.00.24.172 PM
2 20717 cdwjdd67x27mh 2979024279 12-MAY-14 03.11.22.251 PM
2 20717 cdwjdd67x27mh 2979024279 12-MAY-14 03.11.22.188 PM
这里可以清晰看到,执行计划在1节点中发生震荡(两个执行计划都有选择),仔细看两个执行计划,会发现就是查询的时候所使用的index不同而已,继续分析两个index
IDX_TAOBAO_BILL_CR_ISDP_S CREATED_TIME 1 IDX_TAOBAO_BILL_CR_ISDP_S IS_DISPART 2 IDX_TAOBAO_BILL_CR_ISDP_S STATUS_ID 3 IDX_TAOBAO_BILL_CR_REC_S REC_SITE_ID 1 IDX_TAOBAO_BILL_CR_REC_S CREATED_TIME 2 IDX_TAOBAO_BILL_CR_REC_S STATUS_ID 3
分析数据分布情况(其他列省略)
TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANAL ------------------------------ ------------------------------ ------------ --------- GE_TAOBAO_BILL CREATED_TIME 287080448 05-MAY-14 GE_TAOBAO_BILL REC_SITE_ID 13176 05-MAY-14 GE_TAOBAO_BILL IS_DISPART 2 05-MAY-14 GE_TAOBAO_BILL STATUS_ID 7 05-MAY-14
这里可以看到数据库在正常的时候使用IDX_TAOBAO_BILL_CR_REC_S没有问题,但是在某些情况下选择使用IDX_TAOBAO_BILL_CR_ISDP_S这个就有问题,导致逻辑读过高,其实对于该sql语句,是因为IDX_TAOBAO_BILL_CR_REC_S 索引不合理导致,如果创建CREATED_TIME,REC_SITE_ID,STATUS_ID,就不会因为传输的值范围不同而使用IDX_TAOBAO_BILL_CR_ISDP_S的情况。针对该情况,因为表非常大,短时间内无法修改index,只能考虑使用 sql profile固定执行计划
sql profile固定计划
SQL>@coe_xfr_sql_profile.sql cdwjdd67x27mh
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
2979024279 .011
647855111 5.164
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 2979024279
Values passed:
~~~~~~~~~~~~~
SQL_ID : "cdwjdd67x27mh"
PLAN_HASH_VALUE: "2979024279"
Execute coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
on TARGET system in order to create a custom SQL Profile
with plan 2979024279 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_cdwjdd67x27mh_2979024279.sql
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 select * from ( select
7 *
8
9 from
10
11 GE_TAOBAO_BILL
12
13 o
14
15 WHERE 1=1
16
17
18
19
20
21
22
23
24
25
26
27 and o.CREATED_TIME >= :1
28
29
30 and o.CREATED_TIME < :2
31
32
33 and o.REC_SITE_ID = :3
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 and o.STATUS_ID = :4
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64 and o.SERVICE_TYPE = :5 ) where rownum <= 36000
65 ]';
66 h := SYS.SQLPROF_ATTR(
67 q'[BEGIN_OUTLINE_DATA]',
68 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
69 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
70 q'[DB_VERSION('11.2.0.3')]',
71 q'[FIRST_ROWS]',
72 q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
73 q'[MERGE(@"SEL$2")]',
74 q'[OUTLINE(@"SEL$1")]',
75 q'[OUTLINE(@"SEL$2")]',
76 q'[INDEX_RS_ASC(@"SEL$F5BB74E1" "O"@"SEL$2" ("GE_TAOBAO_BILL"."REC_SITE_ID" "GE_TAOBAO_BILL"."CREATED_TIME" "GE_TAOBAO_BILL"."STATUS_ID"))]',
77 q'[END_OUTLINE_DATA]');
78 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
79 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
80 sql_text => sql_txt,
81 profile => h,
82 name => 'coe_cdwjdd67x27mh_2979024279',
83 description => 'coe cdwjdd67x27mh 2979024279 '||:signature||'',
84 category => 'DEFAULT',
85 validate => TRUE,
86 replace => TRUE,
87 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
88 END;
89 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
18414135509058398362
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_cdwjdd67x27mh_2979024279 completed
固定执行计划后,系统负载恢复正常
load恢复正常,单个进程占用cpu 也正常
top - 18:25:29 up 123 days, 4:25, 3 users, load average: 17.59, 16.72, 16.10 Tasks: 1559 total, 6 running, 1551 sleeping, 0 stopped, 2 zombie Cpu(s): 6.2%us, 1.2%sy, 0.0%ni, 84.3%id, 7.4%wa, 0.1%hi, 0.9%si, 0.0%st Mem: 264253752k total, 262395300k used, 1858452k free, 305928k buffers Swap: 33554424k total, 467420k used, 33087004k free, 66811412k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3010 oracle 15 0 150g 26m 21m S 23.6 0.0 0:08.60 oracleq9db2 (LOCAL=NO) 26936 oracle 15 0 150g 33m 28m S 22.9 0.0 0:12.53 oracleq9db2 (LOCAL=NO) 9691 oracle 15 0 150g 34m 28m D 21.0 0.0 0:48.73 oracleq9db2 (LOCAL=NO) 9694 oracle 15 0 150g 34m 28m S 17.4 0.0 0:45.74 oracleq9db2 (LOCAL=NO) 14366 oracle 15 0 150g 33m 28m R 17.4 0.0 0:25.11 oracleq9db2 (LOCAL=NO) 2471 oracle -2 0 150g 180m 37m S 16.7 0.1 10795:55 ora_lms3_q9db2 2463 oracle -2 0 150g 180m 37m S 15.7 0.1 10648:38 ora_lms1_q9db2 2459 oracle -2 0 150g 180m 37m S 14.8 0.1 10726:42 ora_lms0_q9db2 2467 oracle -2 0 150g 180m 36m S 14.8 0.1 10980:33 ora_lms2_q9db2 13324 oracle 15 0 150g 31m 26m S 14.1 0.0 0:22.75 oracleq9db2 (LOCAL=NO) 16740 oracle 15 0 150g 34m 28m R 13.4 0.0 0:43.85 oracleq9db2 (LOCAL=NO) 1908 oracle 15 0 150g 27m 22m S 11.8 0.0 0:03.95 oracleq9db2 (LOCAL=NO) 9689 oracle 15 0 150g 33m 27m S 11.8 0.0 0:46.01 oracleq9db2 (LOCAL=NO) 19410 oracle 15 0 150g 31m 26m S 11.8 0.0 0:43.18 oracleq9db2 (LOCAL=NO) 14102 oracle 15 0 150g 31m 25m S 11.5 0.0 2:17.23 oracleq9db2 (LOCAL=NO) 1914 oracle 15 0 150g 29m 24m S 11.1 0.0 0:04.77 oracleq9db2 (LOCAL=NO) 31106 oracle 15 0 150g 34m 28m S 9.8 0.0 1:24.85 oracleq9db2 (LOCAL=NO) 31139 oracle 15 0 150g 30m 24m S 9.8 0.0 1:21.75 oracleq9db2 (LOCAL=NO) 2498 oracle 15 0 150g 42m 35m S 7.9 0.0 3838:43 ora_lgwr_q9db2 28108 oracle 15 0 150g 36m 29m S 7.9 0.0 0:18.19 oracleq9db2 (LOCAL=NO) 2392 oracle 15 0 150g 35m 17m S 7.5 0.0 5304:57 ora_lmd0_q9db2

加我微信(17813235971)
加我QQ(107644445)

