sql执行计划：把SQL语句拆分为每个的操作步骤组合，按照一定的顺序执行得出结果，查看并看懂执行计划是调优的关键步骤
查看执行计划的方法
DBMS_XPLAN包
sql*plus AUTO trace
V$SQL_PLAN、DBA_HIST_SQL_PLAN
?/rdbms/admin/awrsqrpt.sql
工具类：toad、pl/SQL DEV
跟踪dump：
大多数人比较喜欢用工具直接看，以下来说明这些方式的不同用处
一、DBMS_XPLAN包
10g以后可以查看AWR中的语句执行计划，也可以查看当前内存中游标的执行计划。
主要有以下五个方法：
DISPLAY - 格式化并显示plan table中内容，类似工具的F5（EXPLAIN PLAN FOR &）
DISPLAY_AWR -格式和显示存储在AWR中的SQL语句的执行计划。数据来源: DBA_HIST_SQL_PLAN,DBA_HIST_SQLTEXT
DISPLAY_CURSOR -格式和显示任何加载游标执行计划的内容。数据来源：V$SQL_PLAN和V$SQL_PLAN_STATISTICS_ALL
DISPLAY_SQL_PLAN_BASELINE - 显示一个或多个执行计划的SQL语句通过SQL handle 标识 。数据来源：DBA_SQL_PLAN_BASELINES
DISPLAY_SQLSET - 格式和显示存储在sql调优集中的执行计划内容。数据来源：ALL_SQLSET_STATEMENTS、ALL_SQLSET_PLANS
最常用的DISPLAY_AWR和DISPLAY_CURSOR，来阐述具体的调用方式，显示的内容
（一）、DISPLAY_CURSOR
显示当前shared pool-&library cache中的执行计划
DBMS_XPLAN.DISPLAY_CURSOR(
cursor_child_no
'TYPICAL');
说明（常用说明）
SQL的唯一标识，取SELECT SQL_ID FROM V$SQL，为null的情况下，取上一条语句的执行计划
cursor_child_no
子游标的id号，为null，取出全部子游标的执行计划
输出格式化默认TYPICALADVANCED
+PEEKED_BINDSALLSTATS
#默认格式输出select * from
table(dbms_xplan.display_cursor('bjqjt2dfvya84',null))
bjqjt2dfvya84, child number 0
-------------------------------------
select *　from emp where empno=7521
Plan hash value:
--------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_EMP |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7521)
＃格式化为ADVANCED +PEEKED_BINDS
select * from
table(dbms_xplan.display_cursor('bjqjt2dfvya84',null,'ADVANCED +PEEKED_BINDS'))
bjqjt2dfvya84, child number 0
-------------------------------------
select *　from emp where empno=7521
Plan hash value:
--------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_EMP |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7521)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],
"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]
＃格式化ALLSTATS
两种处理方式： * hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
select /*+gather_plan_statistics*/
*　from emp where empno=7521
select * from
table(dbms_xplan.display_cursor('dvj95t2z1gh2a',null,'ALLSTATS'))
dvj95t2z1gh2a, child number 0
-------------------------------------
select /*+gather_plan_statistics*/
*　from emp where empno=7521
Plan hash value:
------------------------------------------------------------------------------------------------
| Operation
| Starts | E-Rows | A-Rows |
| Buffers |
------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
1 |00:00:00.01 |
TABLE ACCESS BY INDEX ROWID| EMP
1 |00:00:00.01 |
INDEX UNIQUE SCAN
| PK_EMP |
1 |00:00:00.01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7521)
A-ROW是实际的行数，E-ROW是优化器评估的
（二）、DISPLAY_AWR
显示AWR中的SQL的执行计划，对分析历史SQL慢的相当有帮组
DBMS_XPLAN.DISPLAY_AWR(
plan_hash_value
NUMBER DEFAULT NULL,
NUMBER DEFAULT NULL,
VARCHAR2 DEFAULT TYPICAL);
SQL的唯一标识，取SELECT SQL_ID FROM DBA_HIST_SQLTEXT
PLAN_HASH_VALUE
指定SQL语句的PLAN_HASH_VALUE,忽略取全部的SQL_ID下的执行计划
默认取V$DATABASE.DATABASE_ID
输出格式化默认TYPICALADVANCED
+PEEKED_BINDS
#默认输出的格式
select * from
table(dbms_xplan.display_awr('bjqjt2dfvya84'));
SQL_ID bjqjt2dfvya84
--------------------
select *　from emp where empno=7521
Plan hash value:
--------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_EMP |
--------------------------------------------------------------------------------------
#通过格式化ADVANCED +PEEKED_BINDS
select * from
table(dbms_xplan.display_awr('bjqjt2dfvya84',FORMAT=&'ADVANCED +PEEKED_BINDS'));
SQL_ID bjqjt2dfvya84
--------------------
select *　from emp where empno=7521
Plan hash value:
--------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_EMP |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / EMP@SEL$1
2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
二、sqlplus& autotrace
scott@GULL& set autotrace用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]}
[EXP[LAIN]] [STAT[ISTICS]]
可以体现执行语句的执行计划和统计信息
scott@GULL& SET autotrace
scott@GULL& select *　from emp where empno=7521;
----------------------------------------------------------
Plan hash value:
--------------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:01 |
TABLE ACCESS BY INDEX ROWID| EMP
(0)| 00:00:01 |
INDEX UNIQUE SCAN
| PK_EMP |
(0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7521)
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
scott@GULL&
三、V$SQL_PLAN
直接取数据字典的源数据
scott@GULL& col id format 99;
scott@GULL& col parent_id format 99999;
scott@GULL& col operation format a25;
scott@GULL& col options format a20;
scott@GULL& select id,parent_id,operation,options from V$SQL_PLAN where sql_id='bjqjt2dfvya84';
ID PARENT_ID OPERATION
--- --------- ------------------------- --------------------
SELECT STATEMENT
0 TABLE ACCESS
BY INDEX ROWID
UNIQUE SCAN
四、?/rdbms/admin/awrsqrpt.sql
查看AWR中执行计划，调用这个脚本后，相关的直观展现
scott@GULL& @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
Inst Num Instance
----------- ------------ -------- ------------
1 gullstby
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
输入 report_type 的值:
Type Specified:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Inst Num DB Name
------------ -------- ------------ ------------ ------------
for database Id
1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.
Pressing &return& without
specifying a number lists all completed snapshots.
输入 num_days 的值:
Listing the last day's Completed Snapshots
Snap Started
------------ ------------ --------- ------------------ -----
368 01 6月
369 01 6月
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:
Begin Snapshot Id specified: 368
输入 end_snap 的值:
Snapshot Id specified: 369
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
输入 sql_id 的值:
bjqjt2dfvya84
SQL ID specified:
bjqjt2dfvya84
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_368_369.html.
To use this name,
press &return& to continue, otherwise enter an alternative.
输入 report_name 的值:
/home/oracle/0601.html
五、工具类：toad（ctrl+e）、pl/SQL DEV(f5)
工具类查看执行计划，非常快捷、简单、能清楚知道每部步骤的优先级，但是他们实际调用的是EXPLAIN PLAN for，写入plan_TABLE，是优化器的预评估，不是正在SQL执行时的执行计划。简单分析是有用的，但是在你的应用程序中SQL很慢，常用的SQL用toad等很快，此时就不要在用工具快捷方式去查看执行计划，要去分析正在执行的执行计划，采用以上的内容方式。
toad(ctrl+e)
10046这个事件是查看SQL具体的执行步骤，等待的是什么事件，实际内部调用的是哪些SQL。SQL语句执行很慢时，可以查看具体等待的是什么，导致执行这么慢。还有对我们了解create 、truncate具体在执行哪些语句有帮组。
SQL& conn /as sysdba
SQL& ALTER SESSION SET EVENTS '10046 trace name context forever, level 28';
会话已更改。
SQL& select *　from scott.emp where empno=7521;
SQL& select c.value || '/' || d.instance_name || '_ora_' ||
a.spid || '.trc' ||
case when e.value is not null then '_'||e.value end trace
from v$process a, v$session b, v$parameter c, v$instance d, v$parameter e
where a.addr = b.paddr
and b.sid = userenv('sid')
and c.name = 'user_dump_dest'
and e.name = 'tracefile_identifier';
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_3288.trc 查看日志文件的内容
PARSING IN CURSOR #448 len=41 dep=0 uid=0 oct=3 lid=0 tim=3287 hv= ad='d4180e00' sqlid='89mgdagbbc2j4'
select *　from scott.emp where empno=7521
END OF STMT
PARSE #448:c=60990,e=224928,p=20,cr=444,cu=0,mis=1,r=0,dep=0,og=1,plh=,tim=3287
EXEC #448:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=,tim=3287
WAIT #448: nam='SQL*Net message to client' ela= 1 driver id= #bytes=1 p3=0 obj#=5473 tim=3295
WAIT #448: nam='Disk file operations I/O' ela= 29 FileOperation=2 fileno=4 filetype=2 obj#=75336 tim=3586
WAIT #448: nam='db file sequential read' ela= 11120 file#=4 block#=155 blocks=1 obj#=75336 tim=4730
WAIT #448: nam='db file sequential read' ela= 428 file#=4 block#=151 blocks=1 obj#=75335 tim=5277
FETCH #448:c=0,e=12010,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=,tim=5332
STAT #448 id=1 cnt=1 pid=0 pos=1 obj=75335 op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=2 pw=0 time=11994 us cost=1 size=38 card=1)'
STAT #448 id=2 cnt=1 pid=1 pos=1 obj=75336 op='INDEX UNIQUE SCAN PK_EMP (cr=1 pr=1 pw=0 time=11487 us cost=0 size=0 card=1)'
WAIT #448: nam='SQL*Net message from client' ela= 297 driver id= #bytes=1 p3=0 obj#=75335 tim=5736
FETCH #448:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=,tim=5773
WAIT #448: nam='SQL*Net message to client' ela= 1 driver id= #bytes=1 p3=0 obj#=75335 tim=5791
采用tkprof格式化10046，看起来更加的清晰
[oracle@gull2 ~]$ tkprof /u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_3288.trc /home/oracle/3288.trc
查看3288.trc
SQL ID: 89mgdagbbc2j4 Plan Hash:
select *afrom scott.emp where empno=7521
------- ------
-------- ---------- ---------- ---------- ----------
----------
------- ------
-------- ---------- ---------- ---------- ----------
----------
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)
Row Source Operation
---------- ---------- ----------
---------------------------------------------------
TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=2 pw=0 time=11994 us cost=1 size=38 card=1)
INDEX UNIQUE SCAN PK_EMP (cr=1 pr=1 pw=0 time=11487 us cost=0 size=0 card=1)(object id 75336)
Elapsed times include waiting on following events:
Event waited on
Total Waited
----------------------------------------
----------
------------
SQL*Net break/reset to client
SQL*Net message to client
SQL*Net message from client
Disk file operations I/O
db file sequential read
10053这个事件是查看为什么SQL语句评估出发的执行计划是这样的，优化器如何评估出来这个执行计划的，记录优化器的计算值
SQL& conn /as sysdba
已处理的语句
oradebug event 10053 trace name context forever ,level 1;
已处理的语句
select *　from
empno=7521;
EMPNO ENAME
---------- ------------------------------ --------------------------- ----------
-------------- ---------- ---------- ----------
22-2月 -81
SQL& oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orclstby/orcl/trace/orcl_ora_9109.trc
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus@gull2 (TNS V1-V3) action=
sql_id=b250dw0hyz0rs plan_hash_value=- problem_type=3
----- Current SQL Statement for this session (sql_id=b250dw0hyz0rs) -----
select *　from
empno=7521
sql_text_length=49
sql= select *　from
empno=7521
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------------+-----------------------------------+
| Operation
| Bytes | Cost
-----------------------------------------------+-----------------------------------+
| SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID | EMP
00:00:01 |
INDEX UNIQUE SCAN
-----------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("EMPNO"=7521)
Content of other_xml column
===========================
db_version
: 11.2.0.3
parse_schema
plan_hash_2
Outline Data:
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
END_OUTLINE_DATA
1、查看真实的执行计划，使用DBMS_XPLAN.DISPLAY_CUROR这个过程，可以清晰的展示SHARED_POOL-》libarary中的执行计划，不需要你重新去运行SQL语句，根据不同的格式输出你需要的内容，可以查看出统计信息是否陈旧、谓词中的字段类型是否有隐式转化（导致索引不能使用）等
2、DBMS_XPLAN.DISPLAY_AWR，查看AWR中的执行计划，是否存在多个执行计划，代价比较高-》消耗性能厉害的，更加直观的可以调用?/rdbms/admin/awrsqrpt.sql
3、sql*plus autotrace 需要重新去运行一遍SQL语句，查看现在运行的这个语句的执行计划，无法查看到，你程序中运行的那条语句的执行计划
4、工具调用（如toad 、PL/SQL DEV），实际采用的EXPLAIN PLAN FOR... 保存到plan_TABLE，是优化器的预评估。
5、10046的事件使用的范围在调试一个语句执行很慢，想清楚具体等待的是什么，慢在哪个阶段，还可以查看一些DDL的语句，内部语句的构造的方式，但查看执行计划，10046不是特别的方便，需要重新执行SQL语句，到服务器端拿日志文件
6、10053的事件可以用来分析 SQL语句为什么会做这个执行计划（如没有走索引，而是走了全表扫描）
Views(...) Comments()2011年12月 总版技术专家分月排行榜第三2011年9月 总版技术专家分月排行榜第三
2011年6月 MS-SQL Server大版内专家分月排行榜第一
2011年 总版技术专家分年内排行榜第四2010年 总版技术专家分年内排行榜第九2009年 总版技术专家分年内排行榜第八
2011年10月 总版技术专家分月排行榜第一
2011年 总版技术专家分年内排行榜第四2010年 总版技术专家分年内排行榜第九2009年 总版技术专家分年内排行榜第八
2011年10月 总版技术专家分月排行榜第一
2016年10月优秀大版主2016年8月论坛优秀版主2015年4月优秀版主2014年11月论坛优秀版主
2016年4月 荣获微软MVP称号2015年4月 荣获微软MVP称号2014年4月 荣获微软MVP称号2013年4月 荣获微软MVP称号2009年1月 荣获微软MVP称号2012年4月 荣获微软MVP称号2011年4月 荣获微软MVP称号2010年4月 荣获微软MVP称号
本帖子已过去太久远了，不再提供回复功能。2012人阅读
杂类（22）
一、触发器写法：IF
EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')
DISABLE TRIGGER [DDLTriggertTrace] ON DATABASE
USE [你的数据库名]
/****** Object:
DdlTrigger [DDLTriggertTrace]
Script Date: 12/27/:48 ******/
EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTriggertTrace')DROP TRIGGER [DDLTriggertTrace] ON DATABASE
USE [你的数据库名]
/****** Object:
DdlTrigger [DDLTriggertTrace]
Script Date: 12/27/:48 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TRIGGER [DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
SET NOCOUNT ON ;
DECLARE @EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息，以XML格式保存。
DECLARE @ip VARCHAR(32) = ( SELECT
client_net_address
sys.dm_exec_connections
session_id = @@SPID
AuditDB.dbo.DDLEvents
( EventType ,
EventDDL ,
EventXML ,
DatabaseName ,
SchemaName ,
ObjectName ,
HostName ,
IPAddress ,
ProgramName ,
@EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(100)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'NVARCHAR(MAX)') ,
@EventData ,
DB_NAME() ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
'NVARCHAR(255)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(255)') ,
HOST_NAME() ,
PROGRAM_NAME() ,
SUSER_SNAME() ;
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
触发器使用了一个叫AuditDB.dbo.DDLEvents的表，这个AuditDB库是自己建的，名字可以不同。表结构：
USE [AuditDB]
EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__DDLEvents__Event__7E6CC920]') AND type = 'D')
ALTER TABLE [dbo].[DDLEvents] DROP CONSTRAINT [DF__DDLEvents__Event__7E6CC920]
USE [AuditDB]
/****** Object:
Table [dbo].[DDLEvents]
Script Date: 12/27/:33 ******/
EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DDLEvents]') AND type in (N'U'))
DROP TABLE [dbo].[DDLEvents]
USE [AuditDB]
/****** Object:
Table [dbo].[DDLEvents]
Script Date: 12/27/:33 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[DDLEvents] ADD
DEFAULT (getdate()) FOR [EventDate]
&二、CDC介绍：
在SQLServer2008之前，对数据变更的捕获通常使用触发器、时间戳等低效高成本的功能来实现，所以很多系统都没有做数据变更或者仅仅对核心表做监控。
自从SQLServer2008引入了数据变更捕获功能（后面简称为CDC）后，解决了很多烦恼问题，如客户2个月前更改了一个数据，但是由于服务器空间不足，经常只能保留一个月的备份。此时客户为了逃避因为更改数据带来的较大负面影响，往往会把责任推给软件开发商或者运维商，如果此时有变更捕获的话，就有证据了，当然分析日志也能实现，但是比较痛苦。
适用环境：
仅在SQLServer2008（含）以后的企业版、开发版和评估版中可用。
CDC功能主要捕获SQLServer指定表的增删改操作，由于任何操作都会写日志（哪怕truncate），所以CDC的捕获来源于日志文件。日志文件会把更改应用到数据文件中，同时也会标记符合要求的数据标记为需要添加跟踪的项。然后通过一些配套函数，最后写入到数据仓库中。大概流程：
步骤：本文中以：AdventureWorks为例
第一步、对目标库显式启用CDC：
在当前库使用sys.sp_cdc_enable_db。返回0（成功）或1（失败）。注意，无法对系统数据库和分发数据库启用该功能。且执行者需要用sysadmin角色权限。
该存储过程的作用域是整个目标库。包含元数据、DDL触发器、cdc架构和cdc用户。
使用以下代码启用：
USE AdventureWorks
sys.sp_cdc_enable_db;
在一开始直接执行时，出现了报错信息：
消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第193 行
无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体&dbo& 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。
这里引出了另外一个知识点：错误号 15517 的错误
这种错误会在很多地方出现，如还原数据库的时候也会有可能出现。共同点是：某个/些存储过程使用了具有WITHEXECUTE AS 的选项。使其在当前库具有了某个架构，但是当在别的地方执行时，由于没有这个架构，所以就报错，解决方法：
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sa]
经过检查，uspUpdateEmployeeHireInfo这个存储过程的确有：WITH EXECUTE AS CALLER
使用sa的原因是即使sa被禁用，sa还是存在的。所以不会报错。
现在重新执行：
USE AdventureWorks
EXECUTE sys.sp_cdc_enable_db;
启用成功，然后通过以下语句检查是否成功：
is_cdc_enabled,CASEWHEN
is_cdc_enabled=0THEN
'CDC功能禁用'ELSE
'CDC功能启用'END
sys.databases
WHERE&& NAME
= 'AdventureWorks'
创建成功后，将自动添加CDC用户和CDC架构。
创建这两个用户、架构的原因是因为CDC要求独占方式使用这两个架构，所以要单独创建。如果存在了非CDC功能创建的CDC用户、架构的话，则需要先删除该cdc命名的架构，才能开启。
第二步、对目标表启用CDC：
使用db_owner角色的成员执行sys.sp_cdc_enable_table为每个需要跟踪的表创建捕获实例。然后通过sys.tables目录视图中的is_tracked_by_cdc列来判断是否创建成功。
默认情况下会对表的全部列做捕获。如果只需要对某些列做捕获，可以使用@captured_column_list参数指定这些列。
如果要把更改表放到文件组里的话，最好创建单独的文件组（最起码与源表独立）。
如果不想控制访问角色，则@role_name必须显式设置为null。
sys.sp_cdc_enable_table
&&&&[ @source_schema = ] 'source_schema',
&&&&[ @source_name = ] 'source_name' ,
&&&&[ @role_name = ] 'role_name'
&&&&[,[ @capture_instance = ] 'capture_instance' ]
&&&&[,[ @supports_net_changes = ] supports_net_changes ]
&&&&[,[ @index_name = ] 'index_name' ]
&&&&[,[ @captured_column_list = ] 'captured_column_list' ]
&&&&[,[ @filegroup_name = ] 'filegroup_name' ]
&&[,[ @partition_switch = ] 'partition_switch' ]
把HumanResources.Department 这个表开启变更捕获。
USE AdventureWorks
EXEC sys.sp_cdc_enable_table@source_schema=
'HumanResources',
&&& @source_name
= 'Department',@role_name
然后查询是否成功：
&&&&&&& is_tracked_by_cdc
&&&&&&& CASE
WHEN is_tracked_by_cdc
= 0 THEN 'CDC功能禁用'
&&&&&&&&&&&& ELSE
'CDC功能启用'
&&&&&&& END
sys.tables
WHERE&& OBJECT_ID=
OBJECT_ID('HumanResources.Department')
对表开启以后，可以在下图中看到多了很多cdc架构开头的表：
启动之后，可以看到SQLServer代理里面的作业，也出现了这两个作业：
下面列出相关的存储过程：
Sys.sp_cdc_add_job
Sys.sp_cdc_generate_wrapper_function
Sys.sp_cdc_change_job
Sys.sp_cdc_get_captured_columns
Sys.sp_cdc_cleanup_change_table
Sys.sp_cdc_get_ddl_history
Sys.sp_cdc_disable_db
& 建议先禁用表，再禁用库
Sys.sp_cdc_help_change_data_capture
Sys.sp_cdc_disable_table
Sys.sp_cdc_help_jobs
Sys.sp_cdc_drop_job
Sys.sp_cdc_scan
Sys.sp_cdc_enable_db
Sys.sp_cdc_start_job
Sys.sp_cdc_enable_table
Sys.sp_cdc_stop_job
Cdc.fn_cdc_get_all_changes_&capture_instance&
Sys.fn_cdc_has_column_changed
Cdc.fn_cdc_get_net_changes_&capture_instance&
Sys.fn_cdc_increment_lsn
Sys.fn_cdc_decrement_lsn
Sys.fn_cdc_is_bit_set
Sys.fn_cdc_get_column_ordinal
Sys.fn_cdc_map_lsn_to_time
Sys.fn_cdc_get_max_lsn
Sys.fn_cdc_map_time_to_lsn
Sys.fn_cdc_get_min_lsn
下面开始从头到尾做一个实际案例：
先检查原库的内容：可以看到系统表里面只有一个dbo.sysdiagrams表
然后看看SQLServer代理：可以看到也没有相关的作业
步骤二：对数据库启用CDC数据库级别功能。必须显式启用：
Step1、执行以下语句：
USE AdventureWorks
EXEC sys.sp_cdc_enable_db
某些数据库可能存在一些存储过程包含有：execute as 等语句，此时会报错：
文字描述：
消息22830，级别16，状态1，过程sp_cdc_enable_db_internal，第186 行
无法更新元数据来指示已对数据库AdventureWorks 启用了变更数据捕获。执行命令'SetCDCTracked(Value = 1)' 时失败。返回的错误为15517: '无法作为数据库主体执行，因为主体&dbo& 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。请使用此操作和错误来确定失败的原因并重新提交请求。
消息266，级别16，状态2，过程sp_cdc_enable_db_internal，第0 行
EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。
消息266，级别16，状态2，过程sp_cdc_enable_db，第0 行
EXECUTE 后的事务计数指示BEGIN 和COMMIT 语句的数目不匹配。上一计数= 0，当前计数= 1。
消息3998，级别16，状态1，第1 行
在批处理结束时检测到不可提交的事务。该事务将回滚。
如果出现这个错误，目前的解决方法是执行下面语句，原因已在开头说明，对于没有使用EXECUTE AS的库，一般不会有这样的问题：
ALTER AUTHORIZATION
ON DATABASE::[AdventureWorks]
然后再次执行，就成功开启了：
USE AdventureWorks
EXEC sys.sp_cdc_enable_db
现在检查是否成功：
is_cdc_enabled ,
&&&&&&& CASE
WHEN is_cdc_enabled
= 0 THEN 'CDC功能禁用'
&&&&&&&&&&&& ELSE
'CDC功能启用'
&&&&&&& END
sys.databases
NAME = 'AdventureWorks'
现在检查表和作业：
作业没有改变
角色权限中多出了：
步骤三：对某些表开启捕获：
这里选择HumanResources.Department、Person.ADDRESS、Person.Contact
先来看开启之前的样子：
然后对该表开启：
USE AdventureWorks;
sys.sp_cdc_enable_table
&&& @source_schema
= N'HumanResources'
& , @source_name
= N'Department'
& , @role_name
= N'cdc_Admin'--可以自动创建
& , @capture_instance=DEFAULT
可以看到在创建的同时,也创建了两个作业: cdc.AdventureWorks_cleanup和cdc.AdventureWorks_capture
也可以看到多了一个角色CDC_ADMIN，是在上面语句中动态创建的：
按照上面步骤把另外两个表也开启了：
USE AdventureWorks;
sys.sp_cdc_enable_table
&&& @source_schema
= N'Person'
& , @source_name
= N'ADDRESS'
& , @role_name
= N'cdc_Admin'--可以自动创建
& , @capture_instance=DEFAULT
sys.sp_cdc_enable_table
&&& @source_schema
= N'Person'
& , @source_name
= N'Contact'
& , @role_name
= N'cdc_Admin'--可以自动创建
& , @capture_instance=DEFAULT
可以从系统表中看到：
了3个表，并且是刚才开启CDC功能的表。现在来检查是否开启成功：
&&&&&&& is_tracked_by_cdc
&&&&&&& CASE
WHEN is_tracked_by_cdc
= 0 THEN 'CDC功能禁用'
&&&&&&&&&&&& ELSE
'CDC功能启用'
&&&&&&& END
sys.tables
OBJECT_ID IN(
OBJECT_ID('HumanResources.Department'),
&&&&&&&&&&&&&&&&&&&&&& OBJECT_ID('Person.ADDRESS'),
&&&&&&&&&&&&&&&&&&&&&& OBJECT_ID('Person.Contact')
结果如下：
步骤三：检验：
下面来改动数据：
然后把表中的数据复制一份：
INTO HumanResources.Department
&&&&&&&&& GroupName
&&&&&&&&& ModifiedDate
&&&&&&& SELECT&
Name + '1'
&&&&&&&&&&&&&&& GroupName
+ '1' ,
&&&&&&&&&&&&&&& GETDATE()
ModifiedDate
&&&&&&& FROM&&& HumanResources.Department
结果如下：
然后从cdc.HumanResources_Department_CT 表查询：
可以看到的确多了16条记录。这部分记录证明了，有16条数据从监控到目前为止做了改动，现在再来改动一下：
DELETE& FROM
HumanResources.Department
WHERE DepartmentID&17
再查询cdc表，可以看到又多了16条记录：
现在来做下update的实验：
UPDATE& HumanResources.Department
SET ModifiedDate=GETDATE()
现在来分析一下这个表：
可以在联机丛书上查看：cdc.&capture_instance&_CT &&可以看到，这样命名的表，是用于记录源表更改的表。对于insert/delete操作，会有对应的一行记录，而对于update，会有两行记录。
&&&&&& 对于__$operation列：1 = 删除、2= 插入、3= 更新（旧值）、4= 更新（新值）
&&& 对于__$start_lsn列：由于更改是来源与数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
&&&&&& 但是微软不检查直接查询这类表，建议使用cdc.fn_cdc_get_all_changes_&捕获实例&和cdc.fn_cdc_get_net_changes_&capture_instance&&
下文开始，来熟悉各种函数、存储过程的使用，并尝试一些不正常的操作。
日常使用情景：
1、查询已经开启的捕获实例：
由于可能不记得或者不知道开启了什么表的捕获，所以可以使用以下语句来查找：
--返回所有表的变更捕获配置信息
sys.sp_cdc_help_change_data_capture;
可以看到以下截图：
查看对某个实例(即表)的哪些列做了捕获监控:
EXEC sys.sp_cdc_get_captured_columns
@capture_instance =
'HumanResources_Department'
-- sysname
也可以从下面中查找配置信息：
FROM msdb.dbo.cdc_jobs
2、查看当前配置使用sp_cdc_help_jobs：
从上文可以看到，启用cdc之后会自动创建了两个作业，可以先使用以下语句来查看：
sp_cdc_help_jobs
得到的结果：
对于一个大型的OLTP系统，由于数据更改会非常频繁，变更表中的数据会非常多，如果存放过久（最久可以存放100年），那对数据库空间是非常大的挑战。此时可以调整上图中cdc.AdventureWorks_cleanup
中retention（单位：分钟）。
3、修改配置：sp_cdc_change_job：
--显示原有配置：
EXEC sp_cdc_help_jobs
--更改数据保留时间为分钟
sys.sp_cdc_change_job
&&& @job_type
= N'cleanup',
&&& @retention=100
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
--再次查看
EXEC sp_cdc_help_jobs
得到以下结果：
证明修改成功，此处注意，修改后要先停用（如果已经启用），再启用，才能生效。
4、停止/启用、删除/创建作业：
停止/开始作业，可以使用以下语句：
--停用作业
EXEC sys.sp_cdc_stop_jobN'cleanup'
--启用作业
EXEC sys.sp_cdc_start_jobN'cleanup'
删除作业：
EXEC sys.sp_cdc_drop_job@job_type
= N'cleanup'
-- nvarchar(20)
--查看作业
EXEC sys.sp_cdc_help_jobs
可以看到现在只剩下一个作业了:
创建作业：
EXEC sys.sp_cdc_add_job
&&& @job_type
= N'cleanup',
&&& @start_job
&&& @retention
--查看作业
EXEC sys.sp_cdc_help_jobs
下面看到已经创建成功:
5、DDL变更捕获：
CDC除了捕获数据变更之外，还能捕获DDL操作的变化。前提是先要确保SQLServer 代理的启用，其实CDC功能都需要确保sql 代理正常运行，因为所有操作都通过代理中的两个作业来实现的。
现在先来对HumanResources.Department 表修改一下，把name的长度加长：
ALTER TABLE
HumanResources.Department
ALTER COLUMN
Name NVARCHAR(120)
然后查询ddl记录表：
FROM&&& cdc.ddl_history
可以看到：
由于在截图之前已经试了几次，所以里面有3条数据，之所以试了几次，就是因为上面所说的，忘了开SQL代理，所以查不出数据，所以切记要开启SQL代理。
6、使用CDC的函数来获取更改：
A、使用cdc.fn_cdc_get_all_changes_HumanResources_Department 函数报告捕获实例HumanResources_Department 的当前所有可用更改：
@from_lsn binary(10),
@to_lsn binary(10)
SET @from_lsn
&& sys.fn_cdc_get_min_lsn('HumanResources_Department')
SET @to_lsn&&
= sys.fn_cdc_get_max_lsn()
FROM cdc.fn_cdc_get_all_changes_HumanResources_Department
& (@from_lsn,
N'all update old');
B、获取某个时间段的更改信息：
先根据日志序列号（logsequence number ,LSN）来获取跟踪变更数据：
Sys.fn_cdc_map_time_to_lsn获取变更范围内的最大、最小LSN值。可以使用：
Ssmallest largest less than or equal.
如查询某个时间段插入的数据：
--插入数据
INSERT INTO
HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test','abc',GETDATE())
INSERT INTO
HumanResources.Department(name,GroupName,ModifiedDate)
VALUES('test1','abc1',GETDATE())
--检查数据
@bglsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('smallest
greater than or equal',' 12:00:00.997')
@edlsn VARBINARY(10)=sys.fn_cdc_map_time_to_lsn('largest
less than or equal',GETDATE())
SELECT DepartmentID,GroupName,Name
FROM cdc.HumanResources_Department_CT
WHERE [__$operation]=2
AND [__$start_lsn]
BETWEEN @bglsn
AND @edlsn
得到以下结果：
C、sys.fn_cdc_map_lsn_to_time 查询变更时间：
[__$operation] ,
&&&&&& CASE
[__$operation] WHEN 1
THEN '删除'
WHEN 2 THEN
WHEN 3 THEN '更新（捕获的列值是执行更新操作前的值）'
&&&&&& WHEN 4
THEN '更新（捕获的列值是执行更新操作后的值）'
END [类型],
&&&&&&& sys.fn_cdc_map_lsn_to_time([__$start_lsn])
[更改时间]
&&&&&&& name
&&&&&&& DepartmentID
&&&&&&& GroupName
&&&&&&& ModifiedDate
cdc.HumanResources_Department_CT
注意,由于该表刚好有一个modfieddate字段,所以和更改时间相同.
D、获取LSN边界：
SELECT sys.fn_cdc_get_max_lsn()[数据库级别的最大LSN],
sys.fn_cdc_get_min_lsn('cdc.HumanResources_Department_CT')[捕获实例的lsn]
结果如下：
这两个值可以用于上面提到的函数里面用于筛选数据之用。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：114235次
积分：1643
积分：1643
排名：第19987名
原创：40篇
转载：61篇
评论：16条
(2)(3)(1)(1)(1)(1)(2)(2)(1)(2)(1)(3)(1)(4)(7)(4)(7)(14)(5)(2)(1)(1)(3)(1)(3)(6)(2)(1)(1)(2)(2)(4)(1)(4)(5)