Home > fundamental > Oracle常见变更风险分析

Oracle常见变更风险分析

February 22nd, 2013 Leave a comment Go to comments

整理自之前分享过的线上常见变更风险分析,也可以从这里下载完整的doc:

Oracle常见变更风险分析

常见变更风险分析

一、 Create sequence

http://wiki.alipay.net/ops/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%93%8D%E4%BD%9C%E6%89%8B%E5%86%8C-%E5%88%9B%E5%BB%BA%E3%80%81%E5%88%A0%E9%99%A4%E3%80%81%E4%BF%AE%E6%94%B9sequence

这个操作本身来说没有什么风险,毕竟不会涉及到高并发或者消耗大量的cpu,io什么的,也不会引起其他什么对象失效。

nocache的风险:

高并发访问的情况下,Seq的风险最大的还是在cache的设置,设置的太小会导致row cache lock等待。

SQL> select * from dba_sequences where SEQUENCE_NAME=’SEQ_TEST’ and SEQUENCE_OWNER=’QIFENG’;

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

——————– ——————– ———- ———- ———— – - ———- ———–

QIFENG SEQ_TEST 1 1.0000E+18 1 N N 0 2713785

用简单的select nextval去测试:

for(my $i=0 ; $i<10000 ; $i++){

$rv=$dbh->selectrow_array(“select seq_test.nextval from dual”);

}

开100个并发去跑,看下db的表现:

SQL> col username for a10

SQL> col event for a40

SQL> col p123 for a20

SQL> set lines 160

SQL> select SID,SERIAL#,USERNAME,EVENT,P1||’/'||P2||’/'||P3 p123,status,SQL_ID from v$session where username=’QIFENG’ order by 1,2

2 /

SID SERIAL# USERNAME EVENT P123 STATUS SQL_ID

———- ———- ———- —————————————- ——————– ——– ————-

5278 9551 QIFENG log file sync 7062/0/0 ACTIVE

5281 9835 QIFENG log file sync 7062/0/0 ACTIVE

5287 9710 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5288 10378 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5291 9848 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5296 10388 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5300 9710 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5304 10302 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5310 9999 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5316 10058 QIFENG log file sync 7062/0/0 ACTIVE

5319 9907 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5325 9969 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

5328 464 QIFENG row cache lock 13/0/5 ACTIVE gsu6m5ksj9431

写的本地盘,日志速度不给力,所以row cache lock的同时伴随着log file sync。

SQL> col PARAMETER1 for a20

SQL> col PARAMETER2 for a20

SQL> col PARAMETER3 for a20

SQL> col NAME for a20

SQL> set lines 160

SQL> select name,PARAMETER1,PARAMETER2,PARAMETER3 from v$event_name where name=’row cache lock’;

NAME PARAMETER1 PARAMETER2 PARAMETER3

——————– ——————– ——————– ——————–

row cache lock cache id mode request

SQL> select * from v$rowcache where CACHE#=13;

CACHE# TYPE SUBORDINATE# PARAMETER COUNT USAGE FIXED GETS GETMISSES SCANS SCANMISSES SCANCOMPLETES

———- ———– ———— ——————————– ———- ———- ———- ———- ———- ———- ———- ————-

MODIFICATIONS FLUSHES DLM_REQUESTS DLM_CONFLICTS DLM_RELEASES

————- ———- ———— ————- ————

13 PARENT dc_sequences 5 5 0 1622089 12 0 0 0

1622015 1622015 0 0 0

因为sequence cache为0的情况下,每次select nextval都要去修改字典里的LAST_NUMBER,而每次修改都要以exclusive的方式(mode 5)获取同一对象的row cache lock,并发一高自然要等待。

调整cache:

SQL> select * from dba_sequences where SEQUENCE_NAME=’SEQ_TEST’ and SEQUENCE_OWNER=’QIFENG’;

SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

——————– ——————– ———- ———- ———— – - ———- ———–

QIFENG SEQ_TEST 1 1.0000E+18 1 N N 1000 2713785

并发的表现:

SQL> select SID,SERIAL#,USERNAME,EVENT,P1||’/'||P2||’/'||P3 p123,status,SQL_ID from v$session where username=’QIFENG’ order by 1,2;

SID SERIAL# USERNAME EVENT P123 STATUS SQL_ID

———- ———- ———- —————————————- ——————– ——– ————-

5277 8204 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5278 9569 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5281 10139 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5283 15625 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5284 20086 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5286 20137 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

5291 10085 QIFENG SQL*Net message from client 1297371904/1/0 INACTIVE

二、 硬解析、软解析、软软解析的library cache lock的情况

硬解析:

SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.26

SQL> /

System altered.

Elapsed: 00:00:00.24

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 528

Statement processed.

SQL> select count(*) from qifeng.test where rownum=1;

COUNT(*)

———-

1

Elapsed: 00:00:00.02

SQL> oradebug tracefile_name

/opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_31917.trc

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_31917.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bcf15170 KGL Lock addr = 0x0x69ded0f60 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf15170 mtx=0x6bcf152a0(0) cdp=0

name=select count(*) from qifeng.test where rownum=1

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69dee7eb0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69dee7eb0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69dee7eb0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69dee7eb0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcf15170 KGL Lock addr = 0x0x69ded0f60 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf15170 mtx=0x6bcf152a0(1) cdp=1

name=select count(*) from qifeng.test where rownum=1

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69dee7eb0 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

软解析:

SQL> col sql_text for a50

SQL> select sql_id,sql_text,EXECUTIONS from v$sqlarea where sql_text like ‘select count(*) from qifeng.test where %’;

SQL_ID SQL_TEXT EXECUTIONS

————- ————————————————– ———-

cd5md26gwbpkc select count(*) from qifeng.test where rownum=1 1

Elapsed: 00:00:00.00

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 528

Statement processed.

SQL> select count(*) from qifeng.test where rownum=1;

COUNT(*)

———-

1

Elapsed: 00:00:00.00

SQL> oradebug tracefile_name

/opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_32418.trc

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_32418.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bcf15170 KGL Lock addr = 0x0x69dee7d40 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf15170 mtx=0x6bcf152a0(1) cdp=1

name=select count(*) from qifeng.test where rownum=1

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69deab0f0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69deab0f0 mode = S

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcf15170 KGL Lock addr = 0x0x69dee7d40 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf15170 mtx=0x6bcf152a0(1) cdp=1

name=select count(*) from qifeng.test where rownum=1

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x69deab0f0 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

软软解析:

SQL> select distinct sid from v$mystat;

SID

———-

5475

Elapsed: 00:00:00.00

SQL> select count(*) from qifeng.test where rownum=1;

COUNT(*)

———-

1

Elapsed: 00:00:00.00

SQL> /

COUNT(*)

———-

1

Elapsed: 00:00:00.00

SQL> /

COUNT(*)

———-

1

Elapsed: 00:00:00.00

SQL> col SQL_TEXT for a80

SQL> select USER_NAME,SQL_TEXT from v$open_cursor where sid=5475;

USER_NAME SQL_TEXT

———- ——————————————————————————–

SYS BEGIN DBMS_OUTPUT.ENABLE(1000000); END;

SYS select count(*) from qifeng.test where rownum=1

SYS BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

SYS table_1_ff_14f_0_0_0

SYS select USER_NAME,SQL_TEXT from v$open_cursor where sid=5475

Elapsed: 00:00:00.01

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 528

Statement processed.

SQL> select count(*) from qifeng.test where rownum=1;

COUNT(*)

———-

1

Elapsed: 00:00:00.00

SQL> oradebug tracefile_name

/opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_32626.trc

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_32626.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kgllkdl hd = 0x0x6bcf15170 KGL Lock addr = 0x0x69df2bcc0 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf15170 mtx=0x6bcf152a0(1) cdp=1

name=select count(*) from qifeng.test where rownum=1

So

sql解析类型 对table持有的library cache lock类型
硬解析 N,S
软解析 N,S
软软解析

三、 add primary key

SQL> alter table qifeng.test drop constraint test_pk;

Table altered.

Elapsed: 00:00:00.80

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 528

Statement processed.

SQL> alter table qifeng.test add constraint test_pk primary key (id);

Table altered.

Elapsed: 00:00:31.30

SQL> oradebug tracefile_name

/opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_9745.trc

SQL> ext

SP2-0042: unknown command “ext” – rest of line ignored.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_9745.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bdeb3d50 KGL Lock addr = 0x0x6a1eb4708 mode = N

LIBRARY OBJECT HANDLE: handle=6bdeb3d50 mtx=0x6bdeb3e80(1) cdp=0

name=alter table qifeng.test add constraint test_pk primary key (id)

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9de30 mode = X

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bdbeca48 KGL Lock addr = 0x0x6a1e9e788 mode = N

LIBRARY OBJECT HANDLE: handle=6bdbeca48 mtx=0x6bdbecb78(0) cdp=0

name=CREATE UNIQUE INDEX “QIFENG”.”TEST_PK” on “QIFENG”.”TEST”(“ID”) NOPARALLEL

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1eb4930 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kglget hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1e9ea68 mode = X

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kgllkdl hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1eb4930 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bdbeca48 KGL Lock addr = 0x0x6a1e9e788 mode = N

LIBRARY OBJECT HANDLE: handle=6bdbeca48 mtx=0x6bdbecb78(1) cdp=1

name=CREATE UNIQUE INDEX “QIFENG”.”TEST_PK” on “QIFENG”.”TEST”(“ID”) NOPARALLEL

KGLTRCLCK kgllkdl hd = 0x0x6bda5e5f0 KGL Lock addr = 0x0x6a1e9c958 mode = N

LIBRARY OBJECT HANDLE: handle=6bda5e5f0 mtx=0x6bda5e720(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcea0130 KGL Lock addr = 0x0x6a1e9e560 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea0130 mtx=0x6bcea0260(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

KGLTRCLCK kglget hd = 0x0x6bcea0130 KGL Lock addr = 0x0x6a1e9ca10 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea0130 mtx=0x6bcea0260(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

KGLTRCLCK kglget hd = 0x0x6bda5e5f0 KGL Lock addr = 0x0x6a1e9cb80 mode = N

LIBRARY OBJECT HANDLE: handle=6bda5e5f0 mtx=0x6bda5e720(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1e9ea68 mode = X

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1eb4598 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9de30 mode = X

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1eb4930 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kgllkdl hd = 0x0x6bda5e5f0 KGL Lock addr = 0x0x6a1e9cb80 mode = N

LIBRARY OBJECT HANDLE: handle=6bda5e5f0 mtx=0x6bda5e720(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcea0130 KGL Lock addr = 0x0x6a1e9ca10 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea0130 mtx=0x6bcea0260(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9e058 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bdeb3d50 KGL Lock addr = 0x0x6a1eb4708 mode = N

LIBRARY OBJECT HANDLE: handle=6bdeb3d50 mtx=0x6bdeb3e80(1) cdp=0

name=alter table qifeng.test add constraint test_pk primary key (id)

KGLTRCLCK kgllkdl hd = 0x0x6bcea1260 KGL Lock addr = 0x0x6a1e9ea68 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea1260 mtx=0x6bcea1390(0) cdp=0

name=QIFENG.TEST_PK

KGLTRCLCK kgllkdl hd = 0x0x6bcecccf0 KGL Lock addr = 0x0x6a1e9de30 mode = N

LIBRARY OBJECT HANDLE: handle=6bcecccf0 mtx=0x6bcecce20(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bda5e5f0 KGL Lock addr = 0x0x6a1e9cb80 mode = N

LIBRARY OBJECT HANDLE: handle=6bda5e5f0 mtx=0x6bda5e720(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcea0130 KGL Lock addr = 0x0x6a1e9ca10 mode = N

LIBRARY OBJECT HANDLE: handle=6bcea0130 mtx=0x6bcea0260(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

可以看粗,添加primary key的大部分时间都是以X的方式锁了qifeng.test表,最耗时的就是create unique index了,这一过程包含在了test的X锁持有过程中,另外,从整个trace文件可以看出add primary key后台执行过程的一些端倪。

所以,add primary key(X lock)会阻塞硬解析跟软解析的sql(S lock)

简单测试下:

开了5个session,分别做以下事情:

Session1 添加 pk:

SQL> alter table qifeng.test add constraint test_pk primary key(id);

Table altered.

Elapsed: 00:00:32.25

Session2 select 硬解析:

SQL> select * from qifeng.test where id=1;

ID NAME

———- ——————————

1

Elapsed: 00:00:30.35

Session3 select 软解析:

SQL> select * from qifeng.test where id=2;

ID NAME

———- ——————————

2

Elapsed: 00:00:00.15

SQL> /

ID NAME

———- ——————————

2

Elapsed: 00:00:28.33

Session4 select 软软解析:

SQL> select * from qifeng.test where id=3;

ID NAME

———- ——————————

3

Elapsed: 00:00:00.14

SQL> select * from qifeng.test where id=3;

ID NAME

———- ——————————

3

Elapsed: 00:00:00.10

SQL> select * from qifeng.test where id=3;

ID NAME

———- ——————————

3

Elapsed: 00:00:00.11

SQL> /

ID NAME

———- ——————————

3

Elapsed: 00:00:00.11

最终session2,session3阻塞住了,只有session4 是ok的:

SQL> l

1* select t.sid,t.event,t.state,t.seconds_in_wait from v$session t where username=’QIFENG’

SQL> /

SID EVENT STATE SECONDS_IN_WAIT

———- —————————— ——————- —————

5471 library cache lock WAITING 7

5472 SQL*Net message from client WAITING 4

5492 library cache lock WAITING 7

Elapsed: 00:00:00.02

SQL> /

SID EVENT STATE SECONDS_IN_WAIT

———- —————————— ——————- —————

5471 library cache lock WAITING 10

5472 SQL*Net message from client WAITING 7

5492 library cache lock WAITING 10

Elapsed: 00:00:00.01

四、 add primary key using index(normal/unique):

unique index:上述add primary key过程中的create unique index步骤就被该索引代替了,所以速度很快,也是比较保险的一种方式。

Normal index:上述add primary key过程的create unique index会被如下步骤代替:

LIBRARY OBJECT HANDLE: handle=6bb6b4638 mtx=0x6bb6b4768(0) cdp=0

name= select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from “QIFENG”.”TEST” A, (select /*+ all_rows */ “ID” from “QIFENG”.”TEST” A where( “ID” is not null) group by “ID” having count(1) > 1) B where( “A”.”ID” = “B”.”ID”)

所以,add primary key using normal index的危险性跟add primary key是一样的!

五、 add unique key

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_29320.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bb566b00 KGL Lock addr = 0x0x69def41d8 mode = N

LIBRARY OBJECT HANDLE: handle=6bb566b00 mtx=0x6bb566c30(0) cdp=0

name=alter table qifeng.test add constraint test_uk unique (id)

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69ded7520 mode = X

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bd10e1a8 KGL Lock addr = 0x0x69ded7188 mode = N

LIBRARY OBJECT HANDLE: handle=6bd10e1a8 mtx=0x6bd10e2d8(0) cdp=0

name=CREATE UNIQUE INDEX “QIFENG”.”TEST_UK” on “QIFENG”.”TEST”(“ID”) NOPARALLEL

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69ded6bc8 mode = N

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kglget hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69dee7df8 mode = X

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69ded6bc8 mode = N

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bd10e1a8 KGL Lock addr = 0x0x69ded7188 mode = N

LIBRARY OBJECT HANDLE: handle=6bd10e1a8 mtx=0x6bd10e2d8(1) cdp=1

name=CREATE UNIQUE INDEX “QIFENG”.”TEST_UK” on “QIFENG”.”TEST”(“ID”) NOPARALLEL

KGLTRCLCK kgllkdl hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69dee7df8 mode = X

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69ded7520 mode = X

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb566b00 KGL Lock addr = 0x0x69def41d8 mode = N

LIBRARY OBJECT HANDLE: handle=6bb566b00 mtx=0x6bb566c30(1) cdp=0

name=alter table qifeng.test add constraint test_uk unique (id)

KGLTRCLCK kgllkdl hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69dee7df8 mode = N

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfae7c0 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bbad52f8 KGL Lock addr = 0x0x69ded6bc8 mode = N

LIBRARY OBJECT HANDLE: handle=6bbad52f8 mtx=0x6bbad5428(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bce51a28 KGL Lock addr = 0x0x69dfaf900 mode = N

LIBRARY OBJECT HANDLE: handle=6bce51a28 mtx=0x6bce51b58(0) cdp=0

name=QIFENG.TEST

并发select(硬解析、软解析、软软解析)测试:

SQL> l

1* select t.sid,t.event,t.state,t.seconds_in_wait from v$session t where username=’QIFENG’

SQL> /

SID EVENT STATE SECONDS_IN_WAIT

———- —————————————- ——————- —————

5467 library cache lock WAITING 24

5470 SQL*Net message from client WAITING 19

5473 library cache lock WAITING 24

Elapsed: 00:00:00.01

类似add primary key,sql的大部分时间都是要X锁qifeng.test表的。

所以,add unique key(X lock)会阻塞硬解析跟软解析的sql(S lock)

六、 add unique key using index(normal/unique)

unique index:会使用该索引,索引速度很快,也是比较安全的一种方式。

Normal index:原先create unique index的步骤会被如下步骤代替:

KGLTRCLCK kglget hd = 0x0x6bb6b2e98 KGL Lock addr = 0x0x69eaa77d0 mode = N

LIBRARY OBJECT HANDLE: handle=6bb6b2e98 mtx=0x6bb6b2fc8(0) cdp=0

name=

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from “QIFENG”.”TEST” A, (select /*+ all_rows */ “ID” from “QIFENG”.”TEST” A where( “ID” is not null) group by “ID” having count(1) > 1) B where( “A”.”ID” is not null) and( sys_op_map_nonnull(“A”.”ID”) = sys_op_map_nonnull(“B”.”ID”))

所以,add unique key using normal index同样是很危险的!

七、 modify not null

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_30222.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bd6f98d0 KGL Lock addr = 0x0x69dfb00e8 mode = N

LIBRARY OBJECT HANDLE: handle=6bd6f98d0 mtx=0x6bd6f9a00(0) cdp=0

name=alter table qifeng.test modify id not null

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69dfae708 mode = X

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bdeb5c50 KGL Lock addr = 0x0x69dfb01a0 mode = N

LIBRARY OBJECT HANDLE: handle=6bdeb5c50 mtx=0x6bdeb5d80(0) cdp=0

name= select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from “QIFENG”.”TEST” A where( “ID” is null)

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bdeb5c50 KGL Lock addr = 0x0x69dfb01a0 mode = N

LIBRARY OBJECT HANDLE: handle=6bdeb5c50 mtx=0x6bdeb5d80(1) cdp=1

name= select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from “QIFENG”.”TEST” A where( “ID” is null)

KGLTRCLCK kglget hd = 0x0x6bcf3e5a8 KGL Lock addr = 0x0x69dee7f68 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf3e5a8 mtx=0x6bcf3e6d8(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

KGLTRCLCK kglget hd = 0x0x6bd2d76e0 KGL Lock addr = 0x0x69ded6830 mode = N

LIBRARY OBJECT HANDLE: handle=6bd2d76e0 mtx=0x6bd2d7810(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69df34400 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69dfae708 mode = X

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bd2d76e0 KGL Lock addr = 0x0x69ded6830 mode = N

LIBRARY OBJECT HANDLE: handle=6bd2d76e0 mtx=0x6bd2d7810(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcf3e5a8 KGL Lock addr = 0x0x69dee7f68 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf3e5a8 mtx=0x6bcf3e6d8(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

KGLTRCLCK kgllkdl hd = 0x0x6bd6f98d0 KGL Lock addr = 0x0x69dfb00e8 mode = N

LIBRARY OBJECT HANDLE: handle=6bd6f98d0 mtx=0x6bd6f9a00(1) cdp=0

name=alter table qifeng.test modify id not null

KGLTRCLCK kgllkdl hd = 0x0x6bcedcbc8 KGL Lock addr = 0x0x69dfae708 mode = N

LIBRARY OBJECT HANDLE: handle=6bcedcbc8 mtx=0x6bcedccf8(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bd2d76e0 KGL Lock addr = 0x0x69ded6830 mode = N

LIBRARY OBJECT HANDLE: handle=6bd2d76e0 mtx=0x6bd2d7810(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_2

KGLTRCLCK kgllkdl hd = 0x0x6bcf3e5a8 KGL Lock addr = 0x0x69dee7f68 mode = N

LIBRARY OBJECT HANDLE: handle=6bcf3e5a8 mtx=0x6bcf3e6d8(0) cdp=0

name=QIFENG.SYS_PLSQL_154131_DUMMY_1

同add primary key,modify not null(X lock)会阻塞硬解析跟软解析的sql(S lock),阻塞的时间约等于全表扫的时间

八、 create unique index/create index

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_10525.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bb8121e8 KGL Lock addr = 0x0x69df1a7f8 mode = N

LIBRARY OBJECT HANDLE: handle=6bb8121e8 mtx=0x6bb812318(0) cdp=0

name=create unique index qifeng.test_uk on qifeng.test(id)

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = S

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df1bc18 mode = N

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kglget hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df0eb08 mode = X

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df1bc18 mode = N

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = X

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = X

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df0eb08 mode = X

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bb8121e8 KGL Lock addr = 0x0x69df1a7f8 mode = N

LIBRARY OBJECT HANDLE: handle=6bb8121e8 mtx=0x6bb812318(1) cdp=0

name=create unique index qifeng.test_uk on qifeng.test(id)

KGLTRCLCK kgllkdl hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df1bc18 mode = N

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df77f70 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bda31488 KGL Lock addr = 0x0x69df0eb08 mode = N

LIBRARY OBJECT HANDLE: handle=6bda31488 mtx=0x6bda315b8(0) cdp=0

name=QIFENG.TEST_UK

KGLTRCLCK kgllkdl hd = 0x0x6bb811fd8 KGL Lock addr = 0x0x69df18d28 mode = N

LIBRARY OBJECT HANDLE: handle=6bb811fd8 mtx=0x6bb812108(0) cdp=0

name=QIFENG.TEST

这个很有意思,跟add pk不同的是:add pk是先x表再x uk index,而create uk index是先x uk index再x 表,但是后者光凭这个没法知道x table锁持有的时间长短。用10046再trace下,根据10049 table X lock首尾附件cursor的内容,去10046 trace里面获取到2个相对时间,相减后可以得知x table lock持有的时间大概占用46414us(放大后的时间),所以从library cache lock的获取上来说,并发不会感受到有阻塞,因为时间实在太短了。Unique index跟index不同的是,创建unique index写索引数据的同时会检查数据是否需符合唯一性约束(这一点我只是从写日志的情况看出的),另外如果检测到了违反唯一性的数据,不会去X table。

实际的测试也是select硬解析、软解析、软软解析都没有阻塞。

这个地方的风险点在于并发的DML。因为DML需要对表加SubExclusive(SX,mode=3)锁,而create index在一开始就需要加Share(S,mode=4)锁,两者不兼容。这也就是为什么我们建index的时候只考虑有DML的并发的情况来选择是否加online参数,select的sql基本可以无视。

附:对enqueue的trace可以用event 10704来跟踪,例如:oradebug event 10704 trace name context forever,level 10

九、 create index/create index online

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_18098.trc|grep -B2 -i -E “^name=.*QIFENG.*”|grep -v “^–”

KGLTRCLCK kglget hd = 0x0x6bc9d18a8 KGL Lock addr = 0x0x69deffec0 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9d18a8 mtx=0x6bc9d19d8(0) cdp=0

name=create index qifeng.test_ind on qifeng.test(id) online

KGLTRCLCK kglget hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defab90 mode = S

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

。。。。。。

KGLTRCLCK kglget hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defaa20 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defab90 mode = X

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defaa20 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bc9d1488 KGL Lock addr = 0x0x69defa010 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9d1488 mtx=0x6bc9d15b8(0) cdp=0

name=create table “QIFENG”.”SYS_JOURNAL_161484″ (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE “ZHIFB_DATA”

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = X

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bba3adb8 KGL Lock addr = 0x0x69df18120 mode = N

LIBRARY OBJECT HANDLE: handle=6bba3adb8 mtx=0x6bba3aee8(0) cdp=0

name=CREATE UNIQUE INDEX “QIFENG”.”SYS_IOT_TOP_161485″ on “QIFENG”.”SYS_JOURNAL_161484″(“C0″,”RID”) INDEX ONLY TOPLEVEL TABLESPACE “ZHIFB_DATA” NOPARALLEL

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defa968 mode = N

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defa968 mode = N

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bba29288 KGL Lock addr = 0x0x69defde68 mode = N

LIBRARY OBJECT HANDLE: handle=6bba29288 mtx=0x6bba293b8(0) cdp=0

name=QIFENG.SYS_IOT_TOP_161485

KGLTRCLCK kglget hd = 0x0x6bba29288 KGL Lock addr = 0x0x69defa238 mode = X

LIBRARY OBJECT HANDLE: handle=6bba29288 mtx=0x6bba293b8(0) cdp=0

name=QIFENG.SYS_IOT_TOP_161485

KGLTRCLCK kgllkdl hd = 0x0x6bba29288 KGL Lock addr = 0x0x69defde68 mode = N

LIBRARY OBJECT HANDLE: handle=6bba29288 mtx=0x6bba293b8(0) cdp=0

name=QIFENG.SYS_IOT_TOP_161485

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defa968 mode = N

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defa968 mode = N

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba3adb8 KGL Lock addr = 0x0x69df18120 mode = N

LIBRARY OBJECT HANDLE: handle=6bba3adb8 mtx=0x6bba3aee8(1) cdp=1

name=CREATE UNIQUE INDEX “QIFENG”.”SYS_IOT_TOP_161485″ on “QIFENG”.”SYS_JOURNAL_161484″(“C0″,”RID”) INDEX ONLY TOPLEVEL TABLESPACE “ZHIFB_DATA” NOPARALLEL

KGLTRCLCK kgllkdl hd = 0x0x6bc9d1488 KGL Lock addr = 0x0x69defa010 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9d1488 mtx=0x6bc9d15b8(1) cdp=0

name=create table “QIFENG”.”SYS_JOURNAL_161484″ (C0 NUMBER, opcode char(1), partno number, rid rowid, primary key( C0 , rid )) organization index TABLESPACE “ZHIFB_DATA”

KGLTRCLCK kgllkdl hd = 0x0x6bc9f71e8 KGL Lock addr = 0x0x69deffc98 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9f71e8 mtx=0x6bc9f7318(0) cdp=0

name=QIFENG.TEST_IND

KGLTRCLCK kgllkdl hd = 0x0x6bba29288 KGL Lock addr = 0x0x69defa238 mode = X

LIBRARY OBJECT HANDLE: handle=6bba29288 mtx=0x6bba293b8(0) cdp=0

name=QIFENG.SYS_IOT_TOP_161485

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defaa20 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = X

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defab90 mode = X

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bba29288 KGL Lock addr = 0x0x69defde68 mode = N

LIBRARY OBJECT HANDLE: handle=6bba29288 mtx=0x6bba293b8(0) cdp=0

name=QIFENG.SYS_IOT_TOP_161485

KGLTRCLCK kgllkdl hd = 0x0x6bc9f71e8 KGL Lock addr = 0x0x69df18400 mode = X

LIBRARY OBJECT HANDLE: handle=6bc9f71e8 mtx=0x6bc9f7318(0) cdp=0

name=QIFENG.TEST_IND

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defa968 mode = N

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69defe650 mode = S

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kglget hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defab90 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69df15490 mode = X

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69defab90 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kglget hd = 0x0x6bc9f71e8 KGL Lock addr = 0x0x69df18400 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9f71e8 mtx=0x6bc9f7318(0) cdp=0

name=QIFENG.TEST_IND

。。。。。。

KGLTRCLCK kgllkdl hd = 0x0x6bba320c0 KGL Lock addr = 0x0x69deff620 mode = X

LIBRARY OBJECT HANDLE: handle=6bba320c0 mtx=0x6bba321f0(0) cdp=0

name=QIFENG.SYS_JOURNAL_161484

KGLTRCLCK kgllkdl hd = 0x0x6bc9f71e8 KGL Lock addr = 0x0x69defdf20 mode = X

LIBRARY OBJECT HANDLE: handle=6bc9f71e8 mtx=0x6bc9f7318(0) cdp=0

name=QIFENG.TEST_IND

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69df15490 mode = X

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

KGLTRCLCK kgllkdl hd = 0x0x6bc9d18a8 KGL Lock addr = 0x0x69deffec0 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9d18a8 mtx=0x6bc9d19d8(1) cdp=0

name=create index qifeng.test_ind on qifeng.test(id) online

KGLTRCLCK kgllkdl hd = 0x0x6bc9f71e8 KGL Lock addr = 0x0x69defdf20 mode = N

LIBRARY OBJECT HANDLE: handle=6bc9f71e8 mtx=0x6bc9f7318(0) cdp=0

name=QIFENG.TEST_IND

KGLTRCLCK kgllkdl hd = 0x0x6bbb15e28 KGL Lock addr = 0x0x69df15490 mode = N

LIBRARY OBJECT HANDLE: handle=6bbb15e28 mtx=0x6bbb15f58(0) cdp=0

name=QIFENG.TEST

从trace文件可以发现create index online的过程实际上x lock了表2次。结合10046的信息来看,第一次锁是因为要建journal表跟表上的索引等操作,这个会非常快;第二次锁的持有过程中也只有元信息的一些操作(例如删除journal等),两次的libriary cache lock都很短。所以,select基本不会hung住。

这个地方的风险点也在于并发的DML:

SQL> set time on

16:27:58 SQL> oradebug setmypid

Statement processed.

16:28:04 SQL> oradebug UNLIMIT

Statement processed.

16:28:04 SQL> oradebug event 10704 trace name context forever,level 10

Statement processed.

16:28:04 SQL>

16:28:04 SQL>

16:28:33 SQL> create index qifeng.test_ind on qifeng.test(id) online;

Index created.

Elapsed: 00:03:15.79

16:31:49 SQL> oradebug tracefile_name

/opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_483.trc

16:31:56 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

[oracle@fd_perf4 ~]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_483.trc|grep -B1 -e mode|grep -B1 TM-00025a13

*** 2012-11-26 16:28:34.014 àcreate index开始获取的enq mode2(SubShare)

ksqgtl *** TM-00025a13-00000000 mode=2 flags=0×401 timeout=21474836 ***

*** 2012-11-26 16:28:34.052 àenq等待 convert到mode4(Share),but被session2 block住了

ksqcnv: TM-00025a13,00000000 mode=4 timeout=21474836

*** 2012-11-26 16:29:14.030 à成功获取到mode4并convert到mode2,这个时间点跟session2第一条更新释放SX的时间吻合

ksqcnv: TM-00025a13,00000000 mode=2 timeout=21474836

- àsession2中间的大事务的journal数据是在这2者之间做的,即create index+merge journal放在一起(mode=2)

*** 2012-11-26 16:30:02.482 àenq等待cnv到mode4,做最后的journal的merge(因为在上一步merge的过程中可能还会有并发dml进来,这一步会完全的share住表阻止后续的DML然后做第二次merge journal),这一步同样被session2最后一个小更新block住了直到释放为止

ksqcnv: TM-00025a13,00000000 mode=4 timeout=21474836

Session2的并发操作:

16:28:16 SQL> update test set id=0 where id=0;

0 rows updated.

Elapsed: 00:00:00.12

16:28:17 SQL>

16:29:13 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

16:29:14 SQL> update test set id=id+1 where rownum<=200000;

commit;

update test set id=0 where id=0;

200000 rows updated.

Elapsed: 00:00:10.58

16:29:24 SQL>

Commit complete.

Elapsed: 00:00:00.02

16:29:24 SQL>

0 rows updated.

Elapsed: 00:00:00.10

16:29:24 SQL> 16:29:24 SQL> commit;

\

Commit complete.

Elapsed: 00:00:00.00

16:31:45 SQL>

可以认为online的作用就是利用mvlog功能,通过三次增量的方式(create index,merge lot,share lock+merge little)来减弱对并发dml的影响,只有最终merge小量数据的时候才share住表。

十、 add column/modify column

同样会x lock表做些元信息更改,虽然很快(毫秒级),但是add多个column的时候最好还是写在一条语句里面,这样总共只需x lock一次,降低风险(虽然该操作风险已经非常小了)

理论上,并发的DML(enq mode=3)会阻碍该操作(特别是大的DML),因为需要对表获取X enqueue lock,不过实际测试是100个session并发去做小的更新,add column还是很顺利的,偶尔会报ORA-00054: resource busy and acquire with NOWAIT specified。

十一、 Drop column

同样的方法,10049 trace出来,library cache lock X TABLE的时间在毫秒级,大部分的时间是事先的一些recursive sql占用大部分耗时。

Enq-TM的获取情况

[oracle@fd_perf4 udump]$ cat /opt/oracle/admin/fd_perf4/udump/fd_perf4_ora_7555.trc|grep -B1 -e mode|grep -B1 25a13

*** 2012-11-29 11:22:25.421

ksqgtl *** TM-00025a13-00000000 mode=5 flags=0×401 timeout=0 ***

*** 2012-11-29 11:22:58.171

ksqcmi: TM,25a13,0 mode=0 timeout=0

[oracle@fd_perf4 udump]$ timed out waiting for input: auto-logout

所以drop column会阻塞并发的DML操作。

此外,所有的跟表相关的DDL操作都会使相关的sql重新硬解析,这也是需要注意的地方,特别是plan的变化。

 

十二、 总结

SO

DDL操作 Library cache lock层面是否存在阻塞并发SQL(硬解/软解/软软解)的风险 Enq-TM 层面是否会存在阻塞并发DML的风险 更安全的方式
Alter table add primary key Y/Y/N Y Alter table add primary key using unique index
Alter table add primary key using normal index Y/Y/N Y Alter table add primary key using unique index
Alter table add primary key using unique index N/N/N N
Alter table add unique key Y/Y/N Y Alter table add unique key using unique index
Alter table add unique key using normal index Y/Y/N Y Alter table add unique key using unique index
Alter table add unique key using unique index N/N/N N
Alter table modify not null Y/Y/N Y
Create unique/normal index N/N/N Y Create unique/normal index online
Create unique/normal index online N/N/N N(很高并发的时候有危险)
Alter table add/modify column N/N/N N add多个字段放到一条sql里面执行
Alter table drop column N/N/N Y 不允许

Reference:

library cache lock和library cache pin到底是什么

library cache lock和library cache pin到底是什么(续)

Oracle数据库里什么情况下select操作会hang住

Library cache内部机制详解

Index Rebuild Online 过程(9i)完整版

DSI405 enqueue章节

Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.