11gR2新特性:STANDBY_MAX_DATA_DELAY

Active Data Guard 是 Oracle 11g 的亮点特性之一,而在11G release 2中对Active Data Guard引入了更多诱人的新特性,这些特性将Active Data Guard打造成Oracle 读写分离或报表查询的理想方案之一。

 

STANDBY_MAX_DATA_DELAY是11gr2中对Active Data Guard的最大增强(buffer)之一,这是一个可以在会话级别指定的参数(session parameter),该参数指定了在Primary Database已commit提交的变化与standby Database数据库上涉及相关变化的查询之间所允许的时间延迟,单位为second 秒(Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when those same changes can be queries  on the standby database)。

 

使用该STANDBY_MAX_DATA_DELAY参数的语法如下:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY ={ NONE | INTEGER }

 

注意事项

  • 该参数无法为SYS用户所用,在SYS用户的SESSION下设置该参数将被忽略
  • 若没有指定STANDBY_MAX_DATA_DELAY,即使用其默认值NONE,那么无论主备库之间有多大的延迟,在Physical Standby上的查询都会被执行
  • 若查询延迟超过STANDBY_MAX_DATA_DELAY所指定的值那么,将报ORA-03172错误:

 

03172, 00000, "STANDBY_MAX_DATA_DELAY of %s seconds exceeded"
// *Cause:  Standby recovery fell behind the STANDBY_MAX_DATA_DELAY
//          requirement.
// *Action: Tune recovery and retry the query later, or switch to another
//          standby database within the data delay requirement.

在实际运用中STANDBY_MAX_DATA_DELAY保证了在Standby数据库上所作的报表查询不会得到过于陈旧的结果(stale result),通过该参数我们可以指定一个报表应用所容许的数据时间延迟。

当然也可以指定不容许任何数据延迟,即设置STANDBY_MAX_DATA_DELAY为零,以便做到实时数据查询。

配置Primary 与 Standby 数据库之间的实时查询或者说零延迟查询有以下注意事项:

  • 只有特定的应用程序才会对数据延迟有零容忍的需求,注意你的应用程序是否有如此苛刻的要求
  • 在Standby数据库上执行的查询语句必须返回和主库上查询的完全一致的结果
  • 必须设置STANDBY_MAX_DATA_DELAY 为0
  • 在查询开始的那一刻,Standby数据库必须同步到与Primary数据库一致的Current Scn
  • 若结果没有在200ms内返回,则查询会因ORA-03172而终止
  • Primary数据库必须采用最大可用(max availability)或最大保护(maximum protection)模式
  • redo 传输必须使用SYNC 选项
  • 必须启用 Real-Time Query 特性

 

实际使用

 

以下我们通过演示来了解该STANDBY_MAX_DATA_DELAY的效果:

SQL> select * from v$version;  

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com & www.askmaclean.com

Primary Database  SQL> conn maclean/maclean
Connected.

Primary Database SQL> select database_role,protection_mode from v$database;

DATABASE_ROLE    PROTECTION_MODE
---------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY

Primary Database SQL>  create table TSMDD tablespace users as select * From dba_objects;
Table created.

Standby Database SQL> conn maclean/maclean
Connected.

Standby Database SQL> select database_role,protection_mode from v$database;

DATABASE_ROLE    PROTECTION_MODE
---------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY

注意STANDBY_MAX_DATA_DELAY是一个会话参数session parameter,而非实例参数instance parameter

Standby Database SQL> select name from v$system_parameter where name='standby_max_data_delay';

no rows selected

Standby Database SQL> alter session set STANDBY_MAX_DATA_DELAY=0;

Session altered.

Standby Database SQL> select count(*) from TSMDD; 

  COUNT(*)
----------
     13378

 

实际测试可以发现当STANDBY_MAX_DATA_DELAY=0时,并不是查询语句执行时间超过200ms就返回ORA-03172错误,而是指从查询开始的200ms内,若备库没有追上主库的Current SCN时出现ORA-03172。

 

Standby Database SQL> alter session set STANDBY_MAX_DATA_DELAY=0;

Session altered.

Standby Database SQL> set timing on;

Standby Database SQL> select count(1) from TSMDD a, TSMDD b;

  COUNT(1)
----------
 178970884

Elapsed: 00:00:05.34

Standby Database SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.

在主库上执行大数据量的insert操作,但是不提交commit;

Primary Database SQL> insert into /*+ append */  tsmdd select * from tsmdd;

此时在Standby 数据库 上执行查询语句将触发ORA-3172错误

Standby Database SQL> select count(*) from tsmdd
                     *
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

Standby Database SQL>  /
select count(*) from tsmdd
*
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

 

以上查询语句执行过程中的10046 trace如下:

 

PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692536000853
hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'

select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692536000852
WAIT #47828795969456: nam='standby query scn advance'
ela= 201440 p1=770798 p2=0 p3=20 obj#=13873 tim=1316692536202337
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 25 driver id=1650815232
break?=1 p3=0 obj#=13873 tim=1316692536202528
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 144 driver id=1650815232
break?=0 p3=0 obj#=13873 tim=1316692536202694
WAIT #47828795969456: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1
p3=0 obj#=13873 tim=1316692536202715

*** 2011-09-22 19:55:37.983
WAIT #47828795969456: nam='SQL*Net message from client' ela= 1781108 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692537983884
CLOSE #47828795969456:c=0,e=24,dep=0,type=0,tim=1316692537984068

===============================================================================================

PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692537984172
hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'
select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692537984171
WAIT #47828795969456: nam='standby query scn advance' ela= 200546 p1=770914
p2=0 p3=20 obj#=13873 tim=1316692538184822
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 10 driver
id=1650815232 break?=1 p3=0 obj#=13873 tim=1316692538184998
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 103 driver
id=1650815232 break?=0 p3=0 obj#=13873 tim=1316692538185154
WAIT #47828795969456: nam='SQL*Net message to client' ela= 1 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692538185182

 

注意这里出现的standby query scn advance等待事件,显然该等待事件是为了确认Primary与Standby之间的Scn差距,但这又是一个Internal的undocumented 等待事件。我猜测是P1是Standby数据库的Current Scn,而p3可能是Primary 与 Standby之间的Scn 差距。OBJ#是查询对象的object_id:

 

SQL> col owner for a20
SQL> col object_name for a20

SQL> select owner,object_name from dba_objects where object_id=13873;

OWNER                OBJECT_NAME
-------------------- --------------------
MACLEAN              TSMDD

 

使用技巧

 

在实际的使用过程中我们没有必要每次登录会话查询都去指定STANDBY_MAX_DATA_DELAY参数,可以通过创建AFTER LOGON触发器来简化工作。

在11 g Release 2中引入了USERENV Context的一种新属性DATABASE_ROLE,使用该属性可以便捷地定位用户所登录数据库的角色是Primary 还是 Standby,11g的SQL 和 PL/SQL客户端程序均可以通过 SYS_CONTEXT 函数获取该数据库角色信息。

通过创建以下登陆后触发器可以做到当应用程序登录到启用实时查询的Standby数据库上后即自动设置合适的STANDBY_MAX_DATA_DELAY参数。这样即避免了修改应用程序的代码,有做到了配置合理的最大数据延迟。

CREATE OR REPLACE TRIGGER AUTO_SMDD
  AFTER LOGON ON USER.SCHEMA
BEGIN
  IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) THEN
    execute immediate 'alter session set standby_max_data_delay=5';
  END IF;
END;

 

注意以上trigger 只需要在Primary Database上以应用相关用户身份建立即可,会同步到Standby上:

 

Primary Database SQL>  conn maclean/maclean
Connected.

Primary Database SQL> CREATE OR REPLACE TRIGGER AUTO_SMDD
  2    AFTER LOGON ON MACLEAN.SCHEMA
  3  BEGIN
  4    IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) THEN
  5      execute immediate 'alter session set standby_max_data_delay=0';
  6    END IF;
  7  END;
  8  /
Trigger created.

Slide:11g新特性-在线实施补丁online patching

Setup Oracle Direct NFS Client

在Oracle 11g中引入了Direct Network File System(Oracle Direct NFS)的新特性,通过一个打包在Oracle内核中的NFS客户机以改善实例使用NFS时的性能,同时进一步完善了通过NFS实现RAC的解决方案。常规的NFS客户端软件一般由操作系统供应商提供,这类NFS客户端不会专门为Oracle数据文件的IO做优化。而通过内建的Oracle Direct NFS,数据库将可以直接访问NFS服务器上的文件,避免由OS内核NFS造成的额外开销。Oracle宣称由以上优化所带来的性能提升,在DSS环境中超过40%,而在OLTP环境中超过10%(详见<Oracle Database 11g  Direct NFS Client a white paper>)。

接下来我们将通过实例来演示如何构建Oracle Direct NFS客户机,实际上这并不困难;Direct NFS客户机会以如下顺序查找装载点的设置信息:

  • $ORACLE_HOME/dbs/oranfstab 作用域为$ORACLE_HOME相关的数据库
  • /etc/oranfstab 作用域为主机上所有可用数据库
  • 最后为/etc/mtab配置文件,以确定可用的NFS装载点

我们一般推荐使用$ORACLE_HOME/dbs/oranfstab来配置Direct NFS客户机;该oranfstab配置文件可以包括Server,path,export以及mount参数,各参数代表的属性如下:

  • Server:NFS服务器名
  • Path:到达NFS服务器的最多4个网络路径,可以是IP或者主机名
  • Export:从NFS服务器导出的路径
  • Mount:NFS的本地装载点

需要注意的是如果是在使用NFS实现RAC的情景中,那么必须使用/etc/oranfstab配置文件。并且该配置文件应当在所有节点上时同步的。
在正式启用Direct NFS客户机前,NFS文件系统应当已由常规NFS方式mount并且可用。为了启用Direct NFS client,我们还需要将标准的Oracle磁盘管理库(Oracle Disk Manager (ODM) library)替换为支持Direct NFS client的NFS ODM。可以通过建立从标准ODM库指向NFS ODM库的符号链接来完成以上工作,但是需要注意的是以上操作仅能在实例关闭的情况下才能实施并且有效。

SQL> shutdown immediate;

[maclean@rh2 ~]$ cd $ORACLE_HOME/lib
[maclean@rh2 lib]$ mv libodm11.so libodm11.so.old
[maclean@rh2 lib]$ ln -s libnfsodm11.so libodm11.so
[maclean@rh2 lib]$ ls -l libodm11.so
lrwxrwxrwx 1 maclean oinstall 14 Feb 18 19:27 libodm11.so -> libnfsodm11.so

接下来我们将正式启用Direct NFS Client,并会简单测试其性能:

[root@rh2 ~]# showmount -e  nas
Export list for nas:
/d01 rh2

/* 以常规方式装载NFS文件系统 */
[root@rh2 ~]# mount -t nfs nas:/d01 /d01

[maclean@rh2 ~]$ cat $ORACLE_HOME/dbs/oranfstab
server: nas
path: 192.168.1.188
export: /d01  mount: /d01

SQL> startup;

启动阶段告警日志会出现以下信息:
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
.................
Direct NFS: attempting to mount /d01 on filer nas defined in oranfstab
Direct NFS: channel config is:
     channel id [0] local [] path [192.168.1.188]
Direct NFS: mount complete dir /d01 on nas mntport 998 nfsport 2049

SQL> desc v$dnfs_servers;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 SVRNAME                                            VARCHAR2(255)
 DIRNAME                                            VARCHAR2(1024)
 MNTPORT                                            NUMBER
 NFSPORT                                            NUMBER
 WTMAX                                              NUMBER
 RTMAX                                              NUMBER

/*可以通过查询V$DNFS_SERVERS动态视图来了解NFS客户机的装载情况 */

SQL> col svrname for a10
SQL> col dirname for a10
SQL> select svrname,dirname,mntport,nfsport from v$dnfs_servers;

SVRNAME    DIRNAME       MNTPORT    NFSPORT
---------- ---------- ---------- ----------
nas        /d01              998       2049

/* 此外V$DNFS_FILES视图提供了当前Direct NFS打开的文件
         V$DNFS_CHANNELS视图提供了DNFS为服务器打开的网络路径
         V$DNFS_STATS视图提供了DNFS的性能统计信息 */

SQL> set timing on;
SQL> create tablespace nfs_perf datafile '/d01/perf01.dbf' size 2000M;
Tablespace created.

Elapsed: 00:00:30.91

SQL> shutdown immediate;

/* 通过还原libodm11.so库文件和删除oranfstab配置文件可以禁用Direct NFS */

[maclean@rh2 lib]$ cp libodm11.so.old libodm11.so
[maclean@rh2 lib]$ rm $ORACLE_HOME/dbs/oranfstab

SQL> select svrname,dirname,mntport,nfsport from v$dnfs_servers;
no rows selected

SQL> set timing on;
SQL> create tablespace nfs_kernel datafile '/d01/kernel01.dbf' size 2000M;
Tablespace created.

Elapsed: 00:00:29.45

/* 似乎从简单的数据文件创建时间上体现不出Oracle Direct Nfs的优势...*/

That's ok 

Dream features in Oracle Database 12g?

We are wondering some new features from Oracle database 12g in the immediate future:

  1. Oracle database 12g automatic upgrade,do not need human intervention anymore
  2. Oracle database 12g self-organizing Real application cluster, any instance created on any platform can be added into the cluster anytime
  3. Oracle database 12g automatic detected recovery, It can be recovered by itself like terminator!
  4. Oracle database 12g super compression, human don’t need care about compression any more, Oracle will compress data appropriately

to be continued……………

11g r2中对闪回数据归档的增强

11g r1中引入了闪回数据归档新特性,其工作原理是为针对启用了归档方式的表,FBDA进程将创建对应于该表的内部历史表。历史表将拥有原始表的所有列加上某些时间戳列以便跟踪事务处理的变化,具体如下:

SQL> desc SYS_FBA_HIST_75718;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                                                VARCHAR2(4000)
 STARTSCN                                           NUMBER
 ENDSCN                                             NUMBER
 XID                                                RAW(8)
 OPERATION                                          VARCHAR2(1)
 T1                                                 NUMBER(38)
 T2                                                 VARCHAR2(20)
/* 注意其中T1,T2为原始表上的应用数据列 */

[Read more…]

11g新特性-SQL PLUS 错误日志

习惯使用SQLPLUS管理Oracle的朋友肯定不会对使用show errors命令来确认PL/SQL匿名块或过程执行错误感到陌生。似乎在11g以前这是唯一的一种勘错途径了,可惜的是这部分show errors显示的错误信息往往不够全面同时也无法立即保存下来。11g r1中引入了新的错误日志特性,以便DBA或应用开发人员在调试PL/SQL程序时更高效地排除错误。通过set errorlogging命令可以在SQL执行期间将所有的错误记录存储在一个特定的错误日志表中。默认情况下,set errorlogging会将错误记录写入到名为SPERRORLOG的表中。可以客制化该表的表名,错误日志表记录错误的各种属性:1.引发错误用户的用户名;2.错误发生时间;3.包含引发错误语句的脚本名;4.用户自定义的标识符;5.SP2,ORA,PLS错误消息;6.具体引发错误的语句。

SQL> desc sperrorlog;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(256)
 TIMESTAMP                                          TIMESTAMP(6)
 SCRIPT                                             CLOB
 IDENTIFIER                                         VARCHAR2(256)
 MESSAGE                                            CLOB
 STATEMENT                                          CLOB

SQL> show errorlogging ; 
errorlogging is OFF

/* 默认情况下错误日志功能是关闭的,我们需要手动打开它 */

SQL> set errorlogging on;

SQL> show errorlogging ; 
errorlogging is ON TABLE SYS.SPERRORLOG

/* 可以看到成功打开错误日志功能,并会将后续的错误日志写入到当前用户(SYS)名下的SPERRORLOG表 */

SQL> select 1 from abc;
select 1 from abc
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> set linesize 200;
SQL> col username for a20;
SQL> col message for a40
SQL> col statement for a40;

/* session A */
SQL> select 1 from abc;
select 1 from abc
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select username,message,statement from sperrorlog;

USERNAME             MESSAGE                                  STATEMENT
-------------------- ---------------------------------------- ----------------------------------------
SYS                  ORA-00942: table or view does not exist  select 1 from abc

/* 此时在session B中查询 */


SQL> select username,message,statement from sperrorlog;
no rows selected

/* 换而言之当errorlogging被激活后,发生错误时Oracle会将错误日志追加到SPERRORLOG表上,但并不commit; */
/* 这可能导致V$LOCK动态性能视图中显示大量的TX锁,虽然是虚惊:) */

[Read more…]

Gmail priority inbox帮助你减少工作量

全世界平均每天发送2940亿封电子邮件,而脑力劳动者每周花在邮件上的时间大约为13个小时。在过去的几个月里,出现过不少用以帮助用户有效使用Gmail的工具。今天,Google推出了自家的priority inbox。如果priority inbox的选项被激活,它会将您的收件箱分成三个部分:重要的邮件,打星号的邮件,其他所有邮件。该系统会自动识别邮件的重要性,并将那些紧急邮件在收件箱中置顶。

Gmail将允许用户进一步客制化Priority Inbox。你可以选择显示那些你关心的版块(好比说那些重要的,未读的,以星星标记的邮件)。当然你也可以很简单地关闭Priority Inbox功能。这一切客制化工作都可以简单地从Gmail的设置菜单中完成。

Google计划将这一令人振奋的特性向每位Gmail用户推广。

11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示

11g中引入一些功能强大的hint提示,其中就包括了IGNORE_ROW_ON_DUPKEY_INDEX。其官方定义为:
“The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX  causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.”

针对具有唯一性约束的键,若程序设计时没有考虑到插入具有重复键值的行会引发ORA-00001 unique constraint violated错误,进而可能导致程序过程终止的问题的话;直接修改程序将会是十分复杂的工程。所幸我们在11g中有了”IGNORE_ROW_ON_DUPKEY_INDEX”提示,在INSERT单表的语句中加入该hint可以让Oracle静默地(silently)忽略那些具有重复键值的插入行,而不触发ORA-00001错误,允许程序继续运行下去,这可以说是一种十分简便的折中方案。要在生产环境中使用该特性,我们有必要对比一下其同使用Exception处理违反唯一约束间的性能差别。

SQL> drop table youyus ;
Table dropped.
SQL> create table youyus (t1 int ,t2 varchar2(20),t3 varchar2(30)) tablespace users;
Table created
SQL> create unique index youyus_uk on youyus(t1) tablespace users;
Index created
/*清理现场,添加唯一约束索引*/
SQL> alter system set optimizer_dynamic_sampling=1;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
/* 以下过程在0-30000的整数内随机取200000次值,可以确保尝试INSERT大量重复t1键值的行,
以便测试使用DUP_VAL_ON_INDEX Exception时的各项性能参数;此处以及之后我们都将commit置于loop循环外,
从而避免大量commit影响我们的实验结果*/
declare
rnd int;
begin
/* 使用exception处理重复键值插入违反约束的问题*/
for i in 1 .. 200000 loop
BEGIN
select round(dbms_random.value * 30000) into rnd from dual;
insert into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
exception
when DUP_VAL_ON_INDEX then
continue;
end;
end loop;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time from v$sql where sql_text like 'declare%exception%';
PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
4392268   88296566     92345066            621020
/* 逝去时间92s,CPU时间为88s,PLSQL执行时间4s*/
SQL> select count(*) from youyus;
COUNT(*)
----------
29958
SQL> truncate table youyus;
Table truncated.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
/* 在插入前判断插入值是否违反唯一约束应当是一种不错的想法,不过写起来多少有些"麻烦"*/
declare
dup_count int;
rnd       int;
begin
/* 使用插入前判断(check before insert)是否违反唯一约束的方式*/
for i in 1 .. 200000 loop
BEGIN
select round(dbms_random.value * 30000) into rnd from dual;
select count(*) into dup_count from youyus where t1 = rnd;
IF (dup_count = 0) then
insert into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
END IF;
END;
END LOOP;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time  from v$sql where sql_text like 'declare%check%';
PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
2153769   15709301     18265730            679813
/* PLSQL执行时间缩短到2s,整个过程的CPU时间大幅减少到15s*/
/***  以上对比可以得出Exception处理是一种CPU敏感操作的结论  ***/
SQL> select count(*) from youyus;
COUNT(*)
----------
29968
SQL> truncate table youyus;
Table truncated.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
declare
rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
for i in 1 .. 200000 loop
select round(dbms_random.value * 30000) into rnd from dual;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,YOUYUS_UK) */
into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
end loop;
commit;
end;
SQL> select plsql_exec_time,cpu_time,elapsed_time,user_io_wait_time  from v$sql where sql_text like 'declare%IGNORE%';
PLSQL_EXEC_TIME   CPU_TIME ELAPSED_TIME USER_IO_WAIT_TIME
--------------- ---------- ------------ -----------------
2377262   78452903     84209306            623539
SQL> select count(*) from youyus;
COUNT(*)
----------
29959
/*** 
IGNORE_ROW_ON_DUPKEY_INDEX hint模式下,
CPU_TIME对比Exception模式时减少11%,但仍远高于插入前预检查模式;
就修改程序的复杂度而言IGNORE_ROW_ON_DUPKEY_INDEX模式要低于使用Exception模式,
而Exception模式又要低于CHECK_BEFORE_INSERT模式;CHECK_BEFORE_INSERT模式的CPU成本最低,但修改程序时的成本时间最高 
***/
/*需要注意的是IGNORE_ROW_ON_DUPKEY_INDEX提示与我们以往使用的hint略有不同,不正确使用它将导致报错*/
declare
rnd int;
begin
/* 使用IGNORE_ROW_ON_DUPKEY_INDEX hint方式*/
for i in 1 .. 200000 loop
select round(dbms_random.value * 30000) into rnd from dual;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(YOUYUS,I_AM_ERROR) */
into youyus
(t1, t2, t3)
values
(rnd, 'DUPLICATE', 'INSERT TEST');
end loop;
commit;
end;
/
ERROR at line 1:
ORA-38913: Index specified in the index hint is invalid
ORA-06512: at line 7

上述三者各环节耗时图示:

总结一句,IGNORE_ROW_ON_DUPKEY_INDEX为lazy developer专备。

11g新特性之IO校准(IO Calibration)

IO校准(IO Calibration)特性可以帮助我们了解存储系统的真实性能,以进一步判断I/O性能问题是由数据库还是存储系统自身引起的。I/O校准特性(IO Calibration)通过对Oracle数据文件的随机I/O访问存储介质,其结论值更符合数据库IO性能的真实情况。
在使用该特性前,我们要确保满足以下条件:

  • 调用该存储过程需要用到SYSDBA权限
  • TIME_STATISTICS 参数为true:

SQL> show parameter timed_statistics;

NAME TYPE VALUE
———————————— ———– ——————————
timed_statistics boolean TRUE

  • 必须打开异步IO;注意在使用文件系统时,FILESYSTEMIO_OPTIONS需设为SETALL或ASYNC,否则Oracle不会启用异步IO。
  • 可以通过以下查询检验是否启用了异步IO:
SQL> SELECT NAME, ASYNCH_IO
2    FROM V$DATAFILE F, V$IOSTAT_FILE I
3   WHERE F.FILE# = I.FILE_NO
4     AND FILETYPE_NAME = 'Data File';
NAME                                                                             ASYNCH_IO
-------------------------------------------------------------------------------- ---------
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSTEM_65DN8HXT_.DBF       ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_SYSAUX_65DN8J18_.DBF       ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_UNDOTBS1_65DN8J1S_.DBF     ASYNC_ON
D:\TOOLS\ADMINSTRATOR\11G\ORADATA\PROD\DATAFILE\O1_MF_USERS_65DN8J2X_.DBF        ASYNC_ON

IO Calibration特性可以通过DBMS_RESOURCE_MANAGER.CALIBRATE_IO存储过程调用;该过程会对Oracle数据文件引发一系列IO敏感的只读工作负载(由1MB大小的随机IO组成),从而判断存储系统所能持续的最大IOPS(每秒最大IO请求数)和MBPS(每秒IO传输速率)。为了使结果更具代表性,应当保持IO Calibration测试过程中整个数据库是空闲的,没有其他IO负载损耗。

让我们具体来看一下CALIBRATE_IO存储过程的调用方法:

 -- Initiate an I/O calibration
--
-- Input arguments:
--  num_physical_disks   - Approximate number of physical disks in
--                         the database storage
--  max_latency          - Maximum tolerable latency in milliseconds for
--                         database-block-sized IO requests
--
-- Output arguments:
--  max_iops       - Maximum number of I/O requests per second that can be
--                   sustained.  The I/O requests are randomly-distributed,
--                   database-block-sized reads.
--  max_mbps       - Maximum throughput of I/O that can be sustained,
--                   expressed in megabytes per second. The I/O requests
--                   are randomly-distributed, 1 megabyte reads.
--  actual_latency - Average latency of database-block-sized I/O
--                   requests at "max_iops" rate, expressed in milliseconds.
--
PROCEDURE calibrate_io (
num_physical_disks  IN  PLS_INTEGER DEFAULT 1,
max_latency         IN  PLS_INTEGER DEFAULT 20,
max_iops            OUT PLS_INTEGER,
max_mbps            OUT PLS_INTEGER,
actual_latency      OUT PLS_INTEGER
);

其中输入参数num_physical_disks用以指定数据库存储所用物理磁盘的近似数;max_latency指定了客户所能容忍的最大IO 延迟时间,单位为ms;一般我们认为IO平均等待在10ms左右是一种较好的表现,远大于这个数字往往说明IO负载过高了,这里我们假定15ms是我们的deadline。

SQL> set serveroutput on;
SQL> DECLARE
2    lat  INTEGER;
3    iops INTEGER;
4    mbps INTEGER;
5  BEGIN
6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat);
7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 15, iops, mbps, lat);
8
9    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10    DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11    dbms_output.put_line('max_mbps = ' || mbps);
12  end;
13  /
max_iops = 127
latency  = 15
max_mbps = 38
PL/SQL procedure successfully completed
/*平均延迟为15ms时,最大iops为127,mbps为38*/
/*执行过程中也可以通过V$IO_CALIBRATION_STATUS视图了解进度*/
SQL> select * from V$IO_CALIBRATION_STATUS;
STATUS        CALIBRATION_TIME
------------- --------------------------------------------------------------------------------
IN PROGRESS   11-8月 -10 12.02.35.121 下午
SQL> alter session set nls_timestamp_format='HH24:MI';
Session altered
SQL> col start_time for a10;
SQL> col end_time for a10;
SQL> select * from DBA_RSRC_IO_CALIBRATE;
START_TIME END_TIME     MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY NUM_PHYSICAL_DISKS
---------- ---------- ---------- ---------- ---------- ---------- ------------------
12:55      12:59             127         38         14         15                  2
/*DBA_RSRC_IO_CALIBRATE视图记录了IO CALIBRATION的历史信息*/

[gview file=”http://askmaclean.com/wp-content/uploads/resource/calibrate_io.pdf”]

How to check and disable Adaptive Cursor Sharing in 11g

_optimizer_adaptive_cursor_sharing=false disables the feature.

There are 2 new columns in V$sql , IS_BIND_SENSITIVE and IS_BIND_AWARE that indicate the status for individual cursors.

1.) The parameter “_optimizer_adaptive_cursor_sharing” can be changed “on the fly”. This means if you issue an ‘alter system set “_optimizer_adaptive_cursor_sharing” = false |true; ‘ will be reflected in any existing session.

Remember, to disable ACS in 11g ,you should also set alter  system set “_optimizer_extended_cursor_sharing_rel”=’NONE’;

The parameter can be set at session or system level.
When set to NONE it stops the code from maintaining the internal statistical data about the binds.

 

And I advise you set “_optimizer_extended_cursor_sharing” = NONE .

 

2.) show parameter will always retrieve non-default settings also for hidden parameters:

sho parameter adapt
_optimizer_adaptive_cursor_sharing boolean FALSE

1.) non-default hidden (=underscore) parameters are shown with “show parameter ”
2.) the setting of hidden (=underscore) parameters are not supposed to be queried by end users.
3.) You may use 10053 tracing for obtaining the information for optimizer related parameters

sqlplus
set lines 200
set null null
set pages 99
set timi on
set time on

alter session set max_dump_file_size=unlimited;
alter session set events ‘10053 trace name context forever, level 1’;

— execute a statement causing a hardparse:

select /* a new comment */ * from dual;

exit

-> Use an editor or an unix command ( ie grep) and search for the _optimizer_adaptive_cursor_sharing parameter in the tracefile.

If you want to restore Optimizer_enabled_features from 11.2.0.1 to 10.2.0.4 , then you set:

alter session set "_optimizer_undo_cost_change" = '10.2.0.4'; -- 11.2.0.1
alter session set "_optimizer_null_aware_antijoin" = false; -- true
alter session set "_optimizer_extend_jppd_view_types" = false; -- true
alter session set "_replace_virtual_columns" = false; -- true
alter session set "_first_k_rows_dynamic_proration" = false; -- true
alter session set "_bloom_pruning_enabled" = false; -- true
alter session set "_optimizer_multi_level_push_pred" = false; -- true
alter session set "_optimizer_group_by_placement" = false; -- true
alter session set "_optimizer_extended_cursor_sharing_rel" = none; -- simple
alter session set "_optimizer_adaptive_cursor_sharing" = false; -- true
alter session set "_optimizer_improve_selectivity" = false ; -- true
alter session set "_optimizer_enable_density_improvements" = false; -- true
alter session set "_optimizer_native_full_outer_join" = off; -- force
alter session set "_optimizer_enable_extended_stats" = false; -- true
alter session set "_nlj_batching_enabled" = 0; -- 1
alter session set "_optimizer_extended_stats_usage_control" = 255; -- 224
alter session set "_bloom_folding_enabled" = false; -- true
alter session set "_optimizer_coalesce_subqueries" = false; -- true
alter session set "_optimizer_fast_pred_transitivity" = false; -- true
alter session set "_optimizer_fast_access_pred_analysis" = false; -- true
alter session set "_optimizer_unnest_disjunctive_subq" = false; -- true
alter session set "_optimizer_unnest_corr_set_subq" = false; -- true
alter session set "_optimizer_distinct_agg_transform" = false; -- true
alter session set "_aggregation_optimization_settings" = 32; -- 0
alter session set "_optimizer_connect_by_elim_dups" = false; -- true
alter session set "_optimizer_eliminate_filtering_join" = false; -- true
alter session set "_connect_by_use_union_all" = old_plan_mode; --true
alter session set "_optimizer_join_factorization" = false; -- true
alter session set "_optimizer_use_cbqt_star_transformation" = false; -- true
alter session set "_optimizer_table_expansion" = false ; -- true
alter session set "_and_pruning_enabled" = false ; -- true
alter session set "_optimizer_distinct_placement" = false ; -- true
alter session set "_optimizer_use_feedback" = false ; -- true
alter session set "_optimizer_try_st_before_jppd" = false ; -- true

沪ICP备14014813号

沪公网安备 31010802001379号