v$dead_cleanup on 12.2.0.1

Oracle在11.2.0.4和后续的版本分别引入了V$DEAD_CLEANUP监控dead processes、killed sessions。按照官方文档对V$DEAD_CLEANUP的描述
“V$DEAD_CLEANUP shows the dead processes and killed sessions present in the instance and their cleanup status.”。
本文在Oracle 12.2.0.1环境下进行测试了2次,一次视图的状态信息为RESOURCES FREED,一次为CLEANUP PENDING。
使用json用户登录数据库,获取到的session信息如下

SQL> col username for a10
SQL> select sid,serial#,paddr,saddr,username,status from v$session where username = 'JSON';

SID SERIAL# PADDR SADDR USERNAME STATUS
---------- ---------- ---------------- ---------------- ---------- ----------------
138 63930 000000007DCC8E48 000000007C147988 JSON INACTIVE

SQL> set linesize 200
SQL> col tracefile for a70
SQL> select addr,pid,spid,stid,execution_type,tracefile from v$process where addr = '000000007DCC8E48';

ADDR PID SPID STID EXECUTION_ TRACEFILE
---------------- ---------- ---------- -------- ---------- ----------------------------------------------------------------------
000000007DCC8E48 65 19675 19675 PROCESS /orasoft/oracle/diag/rdbms/ywdb/ywdb/trace/ywdb_ora_19675.trc

Kill杀掉会话

SQL> alter system kill session '138,63930';

系统已更改。

SQL> select sid,serial#,paddr,saddr,username,status from v$session where username = 'JSON';

SID SERIAL# PADDR SADDR USERNAME STATUS
---------- ---------- ---------------- ---------------- ---------- ----------------
138 63930 000000007DCC8E48 000000007C147988 JSON KILLED

SQL> col type for a20
SQL> col cleanup_owner for a30
SQL> col state for a30

SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE PADDR SADDR ROOT_ADDR CLEANUP_OWNER STATE DEAD_TIME CON_ID
-------------------- ---------------- ---------------- ---------------- ------------------------------ 
KILLED SESSION 000000007DCC8E48 000000007C147988 000000007C147988 OWNER PROCESS RESOURCES FREED 102 3

SQL> select addr,pid,spid,stid,execution_type,tracefile from v$process where addr = '000000007DCC8E48';

未选定行

进程信息已经从v$process中删除

再次进行测试

SQL> alter system kill session '134,10324';

系统已更改。

SQL> col name for a5
SQL> col name for a8
SQL> select sid,serial#,username,status,saddr,paddr from v$session where username = 'JSON';

       SID    SERIAL# USERNAME STATUS           SADDR            PADDR
---------- ---------- -------- ---------------- ---------------- ----------------
       134      10324 JSON     KILLED           000000007C151068 000000007DCA3A08
SQL> col type for a25
SQL> col CLEANUP_OWNER for a25
SQL> col state for a25
SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE                      PADDR            SADDR            ROOT_ADDR        CLEANUP_OWNER             STATE                      DEAD_TIME     CON_ID
------------------------- ---------------- ---------------- ---------------- ------------------------- ------------------------- ---------- ----------
KILLED SESSION            000000007DCA3A08 000000007C151068 000000007C151068 OWNER PROCESS             CLEANUP PENDING                  274          3

SQL> select * from V$CLEANUP_PROCESS;

NAME     PADDR            SADDR            STATE                     DEAD_IN_CLEANUP  CLEANUP_TIME TIME_SINCE_LAST_CLEANUP NUM_CLEANED     CON_ID
-------- ---------------- ---------------- ------------------------- ---------------- ------------ ----------------------- ----------- ----------
PMON     000000007DC87AD8 000000007C28A900 IDLE                      00                          0                 1889383           0          0
CLMN     000000007DC88B68 000000007C3B13D8 IDLE                      00                          0                 1889383           0          0

SQL> select type,paddr,saddr,root_addr,cleanup_owner,state,dead_time,con_id from V$DEAD_CLEANUP t;

TYPE                      PADDR            SADDR            ROOT_ADDR        CLEANUP_OWNER             STATE                      DEAD_TIME     CON_ID
------------------------- ---------------- ---------------- ---------------- ------------------------- ------------------------- ---------- ----------
KILLED SESSION            000000007DCA3A08 000000007C151068 000000007C151068 OWNER PROCESS             CLEANUP PENDING                  587          3

采用event=’10246 trace name context forever, level 10′ 跟踪pmon和clmn进程并没有得到有趣的信息。

Footnote:
对session进行kill,在alert日志文件中会进行记录信息

BLUE(3):Opening pdb with no Resource Manager plan active
Pluggable database BLUE opened read write
Completed: alter pluggable database blue open
2017-03-14T13:00:22.029236+08:00
BLUE(3):KILL SESSION for sid=(266, 1517):
BLUE(3):  Reason = alter system kill session
BLUE(3):  Mode = KILL SOFT -/-/-
BLUE(3):  Requestor = USER (orapid = 63, ospid = 14620, inst = 1)
BLUE(3):  Owner = Process: USER (orapid = 62, ospid = 14611)
BLUE(3):  Result = ORA-0
2017-03-14T13:12:41.833469+08:00
发表在 12c | 留下评论

Oracle profile parameter IDLE_TIME and CONNECT_TIME

通过创建profile然后授权给某个用户,可以限制用户的资源使用情况,限制的内容可以是CPU资源使用、内存资源使用和会话时间等,Oracle官方手册可以参考CREATE PROFILE
本文中只对IDLE_TIME和CONNCT_TIME进行测试。
手册中对这2个参数做了简单的说明

CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes.

IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

下面是实验过程,实验环境是11.2.0.4
在将这2个参数开启之间需要在会话级别或者全局级别开启resource_limit,默认为FALSE即为不开启。

SQL> show parameter resource_limit

PARAMETER_NAME                                               TYPE                   VALUE
------------------------------------------------------------ ---------------------- --------------------
resource_limit                                               boolean                FALSE
SQL> alter system set resource_limit = true scope=both;

系统已更改。

SQL> show parameter resource_limit

PARAMETER_NAME                                               TYPE                   VALUE
------------------------------------------------------------ ---------------------- --------------------
resource_limit                                               boolean                TRUE

测试脚本

rem
rem     Script: demo_profile.sql
rem     Author: Leo Zhang
rem     Dated:  2017.03.08
rem     Purpose:   Demostrate the profile parameter IDLE_TIME and CONNECT_TIME

--场景1:测试IDLE_TIME为1分钟
--session 1
alter user t_user profile default;
drop profile pro_test;
create profile pro_test limit
idle_time 1;

alter user t_user profile pro_test;

--session 2
sqlplus  t_user/t_user@t

--session1
col program for a20
col username for a10
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;
exec DBMS_LOCK.SLEEP(60);
select sysdate from dual;
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;


--场景2:测试CONNECT_TIME为1分钟

--session 1
alter user t_user profile default;
drop profile pro_test;
create profile pro_test limit
connect_time 1;
alter user t_user profile pro_test;

--session 2
sqlplus  t_user/t_user@t
create sequence SEQ_T
minvalue 1
nomaxvalue 
start with 1
increment by 1
cache 50;

create table T
(
  id     NUMBER,
  t_time TIMESTAMP(6)
);

set timing on

DECLARE
BEGIN
  FOR i IN 1 .. 1000000000 LOOP
      INSERT INTO T
        SELECT SEQ_T.NEXTVAL, SYSDATE FROM DUAL;
  END LOOP;
  COMMIT;
END;
/

--session 1 
col program for a20
col username for a10
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;
exec DBMS_LOCK.SLEEP(60);
select sysdate from dual;
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;

测试结果

场景1:

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:00:05

SQL> exec DBMS_LOCK.SLEEP(60);

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:05

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:13

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:21

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

在1分钟多之后,会话并没有被KILL掉。再等待一会之后,PMON进程的trc文件中出现了

*** 2017-03-09 09:02:28.604
killing idle session 0xd14616a0 num=48 serial=471 uid=239

SID=48的会话被KILL掉。
再过一会进行查询

SQL> select username,status,program,type from v$session where username  = 'T_USER';

USERNAME   STATUS           PROGRAM              TYPE
---------- ---------------- -------------------- --------------------
T_USER     SNIPED           sqlplus.exe          USER

会话状态从INACTIVE变成了SNIPPED。
按照v$session手册中对status的描述,SNIPED – An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again.

在session 2会话窗口随便查询一个语句,PMON后台trc中继续出现

*** 2017-03-09 09:04:40.875
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=30, post_num=19
deleting session 0xd14616a0 sid=48 serial=471
deletion of orphaned session 0xd14616a0 successful

对IDLE_TIME是1分钟、2分钟都进行了测试,都无法在接近时间内pmon回收掉这样的会话。

场景2

--session 2 output
SQL> set timing on
SQL>
SQL> DECLARE
  2  BEGIN
  3    FOR i IN 1 .. 1000000000 LOOP
  4        INSERT INTO T
  5          SELECT SEQ_T.NEXTVAL, SYSDATE FROM DUAL;
  6    END LOOP;
  7    COMMIT;
  8  END;
  9  /
DECLARE
*
第 1 行出现错误:
ORA-02399: 超出最大连接时间, 您将被注销
ORA-06512: 在 line 4


已用时间:  00: 00: 59.39

--session 1 output
SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        88       2765 T_USER     ACTIVE   sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:26:57

SQL> exec DBMS_LOCK.SLEEP(60);

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:27:57

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

未选定行

会话已经被回收

PMON进程trace信息

*** 2017-03-09 09:26:58.899
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=71

*** 2017-03-09 09:27:01.144
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=72
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=73
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=73
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=74
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=75

*** 2017-03-09 09:27:16.668
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=76

*** 2017-03-09 09:27:17.087
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=77
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=78
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=79
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=80
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=81
PMON ignoring post

*** 2017-03-09 09:27:42.632
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=82

*** 2017-03-09 09:27:43.112
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=83
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=84
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=85
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=86
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=87
PMON ignoring post

*** 2017-03-09 09:27:46.427
PMON woken up to process network events

*** 2017-03-09 09:27:49.429
PMON woken up to process network events

*** 2017-03-09 09:27:52.279
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=88
PMON woken up to process network events
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=89
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=90
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=91
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=92
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=93
PMON ignoring post

*** 2017-03-09 09:27:58.826
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=94
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=95
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=96
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=97

*** 2017-03-09 09:28:06.859
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=98
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=99
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=100
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=101
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=102
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=103
PMON ignoring post

*** 2017-03-09 09:28:10.260
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=104
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=105
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=106
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=107
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=108
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=109
PMON ignoring post

*** 2017-03-09 09:28:13.720
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=110

*** 2017-03-09 09:28:13.820
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=111
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=112
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=113
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=114
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=115
PMON ignoring post

Oracle推荐使用Database Resource Manager 来建立资源限制管理,DBRM可以提供更细致的管理方式,具体请查看Managing Resources with Oracle Database Resource Manager.

发表在 DBRM, Trace | 留下评论

v$session process column’s format xxxx:yyyy

今天在使用PLSQL developer的时候发现一个有趣的现象,在使用PLSQL初次登录数据库后,在v$session中的action字段会记录为Primary Session,当初始化SQL Window和Command Window的时候并执行语句之后,在v$session中的action字段会记录对应操作类型的数据:SQL Window – New和Command Window – New。
测试环境为Oracle 11.2.0.4 + Windows7 + PLSQL DEVELOPER9

SQL> col process for a10
SQL> col spid for a10
SQL> col module for a20
SQL> col action for a20
SQL>
SQL> select s.saddr,
  2         s.sid,
  3         s.serial#,
  4         s.PADDR,
  5         s.process,
  6         s.PORT,
  7         s.module,
  8         s.action,
  9         p.PID,
 10         p.SPID
 11    From v$session s, v$process p
 12   where s.PADDR = p.ADDR
 13     and s.username = 'OE';

SADDR                   SID    SERIAL# PADDR            PROCESS          PORT MODULE               ACTION                      PID SPID
---------------- ---------- ---------- ---------------- ---------- ---------- -------------------- -------------------- ---------- ----------
000000012679D3C0       2377      48789 00000001270EF7D8 4928:3556       57720 PL/SQL Developer     SQL Window - New            524 6495
00000001289CB350         31      52907 00000001271CAEC8 4928:3556       57435 PL/SQL Developer     Primary Session             944 6085
0000000126165BE0       1155      33125 00000001271D0260 4928:3556       57451 PL/SQL Developer     SQL Window - New            954 7765
00000001242C1FA0       4041      59393 000000012826CE30 4928:3556       57735 PL/SQL Developer     SQL Window - New           1015 11196
0000000125E4BDC0       3428      60151 00000001271F3AD0 4928:3556       57765 PL/SQL Developer     Command Window - New       1022 13257

SQL>

查看进程4928在windows上的端口信息

C:\>netstat -ano | findstr 4928
  TCP    192.168.2.59:57435     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57451     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57720     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57735     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57765     192.168.1.10:1521      ESTABLISHED     4928
  UDP    127.0.0.1:53149        *:*                                  4928

查询进程4928在windows的线程信息。

C:\PSTools>pslist -dmx 4928

PsList v1.4 - Process information lister
Copyright (C) 2000-2016 Mark Russinovich
Sysinternals - www.sysinternals.com

Process and thread information for PC-20161024TGGX:

Name                Pid      VM      WS    Priv Priv Pk   Faults   NonP Page
plsqldev           4928  232312   81728   71140   71524    36987     57  394
 Tid Pri    Cswtch            State     User Time   Kernel Time   Elapsed Time
3556  10  28148940     Wait:UserReq  0:00:07.597   0:00:13.899    6:02:12.939
1368   7         1   Wait:Suspended  0:00:00.000   0:00:00.000    6:02:12.775
6516  12      1180     Wait:UserReq  0:00:00.000   0:00:00.015    6:02:12.705
 828   8         2     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:12.598
1284   9       288   Wait:Suspended  0:00:00.015   0:00:00.000    6:02:04.045
5608  11        33     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:04.020
6400   9      9356     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:04.014
3432   8       534       Wait:Queue  0:00:00.046   0:00:00.000    6:02:03.975
5448   9        30       Wait:Queue  0:00:00.000   0:00:00.000    6:02:03.361
4588   9        87   Wait:Suspended  0:00:00.000   0:00:00.000    6:01:47.594
6328  10         5   Wait:Suspended  0:00:00.000   0:00:00.000    3:37:26.221
7896  10       124   Wait:Suspended  0:00:00.000   0:00:00.000    3:16:04.232
7864  10         5   Wait:Suspended  0:00:00.000   0:00:00.000    3:12:55.948
4944  10        27   Wait:Suspended  0:00:00.000   0:00:00.000    3:11:20.407
8508   8        13       Wait:Queue  0:00:00.000   0:00:00.000    0:02:39.330
5424   8        26       Wait:Queue  0:00:00.000   0:00:00.000    0:02:39.010
5700   8         8       Wait:Queue  0:00:00.000   0:00:00.000    0:00:55.701

在PL/SQL DEVELOPER登录到数据库后,Process的内容是固定的,格式为XXXX:YYYY,都是windows下PL/SQL DEVELOPER所在的进程号+主线程号。
当通过PLSQL DEVELOPER通过LOG OFF和LOG ON登录新的用户之后,原有的PROCESS保持不变,v$session中的其他信息改变。
关于windows的进程和线程介绍请查看微软文档关于Processes and Threads的说明。

Footnote

v$session视图对应x$ksuse固态表,process字段对应其ksusepid列。

发表在 Infrastructure, PLSQL | 留下评论

PL/SQL developer delete rows using select for update

今天测试了一下PL/SQL DEVELOPER工具进行SELECT FOR UPDATE删除数据的时候数据库是如何处理的。测试的场景如下图红色圈圈部分。

jietu

采用的方法是logminer挖掘日志,挖掘的脚本如下

alter system set utl_file_dir = '/arch' scope=spfile;
shutdown immediate
startup

BEGIN
  DBMS_LOGMNR_D.build (
    dictionary_filename => 'dict.ora',
    dictionary_location => '/arch');
END;
/

BEGIN
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.new,
    logfilename => '/oradata2/arch/1_345_927379454.dbf');
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.addfile,
    logfilename => '/oradata2/arch/1_346_927379454.dbf'); 
END;
/

BEGIN
  DBMS_LOGMNR.start_logmnr(
    dictfilename => '/arch/dict.ora');
end;
/

create table blue.t as select * From v$logmnr_contents;

BEGIN
  DBMS_LOGMNR.end_logmnr;
END;
/

对于select for update直接通过”-“删除记录的方式的测试结果通过logminer抓取的结果如下

SQL> select sql_redo,sql_undo from t where table_name = 'T_DUP' order by timestamp;

SQL_REDO                                                                         SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
create table t_dup(id number);
insert into "BLUE"."T_DUP"("ID") values ('1');                                   delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAA';
insert into "BLUE"."T_DUP"("ID") values ('1');                                   delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAB';
select * from "BLUE"."T_DUP" where ROWID = 'AAAvC3AAEAAAJ7sAAA' for update;
delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAA';    insert into "BLUE"."T_DUP"("ID") values ('1');

又测试了delete from table_name where rowid = ”,在数据层记录是和上面的输出一样的。
抓取tns的报文信息发现在执行”-“删除数据的时候,报文信息如下

where rowid = :plsqldev_rowid for update nowait
DELETE FROM WHERE ROWID = :PLSQLDEV_ROWID
图像 | Posted on by | 留下评论

Orace Text Index-part7-Memory parameter

创建Text Index的时候,在将in-memory中的token表和mapping表的内容写到DR前缀的表之前,Text Index生成的TOKEN信息是cache在内存中的。从cache中写出到物理磁盘上后再次生成新的token信息和mapping信息保存在内存中,这样反复进行直到完成Text Index的创建,其中控制cache多少数据由memory相关参数来决定,所以在创建Text Index的时候分配的memory参数就很重要了。
Oracle建议将参数设置的越大越好,一方面可以加速创建的过程,另一方面可以减少最终INDEX的碎片化,但是太大了也会造成swapping,并且越大的设置也就相应的需要更多的资源来完成回滚。
关于Text Index memory的系统参数包括DEFAULT_INDEX_MEMORY和 MAX_INDEX_MEMORY,在CREATE TEXT INDEX的时候也可以指定memory参数来定义使用的内存大小,文中的测试部分使用指定memory参数大小的方式进行了对比。

Text Index的参数可以通过查询CTX_PARAMETERS视图来获得。

11.2.0.4环境的输出
SQL> SELECT PAR_NAME, PAR_VALUE / 1024 / 1024
  2    FROM CTXSYS.CTX_PARAMETERS
  3   WHERE PAR_NAME IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY');

PAR_NAME                       PAR_VALUE/1024/1024
------------------------------ -------------------
DEFAULT_INDEX_MEMORY                            12       --12M
MAX_INDEX_MEMORY                              1024      --1024M

12.1.0.2和12.1.0.1的输出
SQL> SELECT PAR_NAME, PAR_VALUE / 1024 / 1024
  2    FROM CTXSYS.CTX_PARAMETERS
  3   WHERE PAR_NAME IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY');
PAR_NAME                       PAR_VALUE/1024/1024
------------------------------ -------------------
DEFAULT_INDEX_MEMORY                            64        --64M
MAX_INDEX_MEMORY                            262144       --256G

增大MAX_INDEX_MEMORY参数和DEFAULT_INDEX_MEMORY可以通过使用ctxsys用户的ctx_adm包

begin
 ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','1024M');
 ctxsys.ctx_adm.set_parameter('DEFAULT_INDEX_MEMORY','512M');
end;
/

测试下memory参数对Text Index的创建效率和对应情况下生成的TOKEN表大小的影响。
测试环境为Linux 11.2.0.4文件系统非归档模式,开启异步IO,CLOB内数据格式比较复杂,CLOB列的大小大概是4539.8M,创建Text Index开启并行度为4。
测试脚本:

SET TIMING ON
SET SERVEROUTPUT ON
DROP INDEX IDX1_T_LEXER;
CREATE INDEX  IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 64M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 128M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 256M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 512M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

DROP INDEX IDX1_T_LEXER;
CREATE INDEX IDX1_T_LEXER ON T_LEXER(DATA_ECHO_AREA) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER MYLEX STOPLIST MY_STOPLIST MEMORY 1024M') PARALLEL 4;
select segment_name,bytes/1024/1024 from user_segments where segment_name like 'DR%';

测试结果如下:

MEMORY参数设置 DR$IDX1_T_LEXER$I大小(MB) DR$IDX1_T_LEXER$X大小(MB) 创建使用时间(seconds)
64M 4781 1833 4443.189
128M 4582 1769 3594.388
256M 4439 1698 3017.122
512M 4295 1634 1932.463
1024M 4025 1563 2089.415

FOOTNOTE:
2017.2.27 增加被创建列的CLOB大小,使用ROUND(DBMS_LOB.GETLENGTH(DATA_ECHO_AREA) / 1024, 2)计算大小为4539.8 MB。

发表在 Domain Index, index, LOB | 留下评论

Oracle 12cR2 -Part1-table with json column overview

Oracle12开始支持JavaScript Object Notation (JSON) 数据,支持包括事务、索引、查询和视图等关系数据库的特性。
在Oracle 12.2版本下对json进行了很多增强,增加的特性请参考changes in Release 12.2
测试环境为Linux 86_64下的Oracle 12.2.0.1,测试脚本如下

drop table t_json purge;

create table t_json
(id varchar2(32) NOT NULL PRIMARY KEY,
data_echo_area CLOB
constraint data_echo_area_json check (data_echo_area is json)
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 100, 
    "first_name": "Steven", 
    "last_name": "King", 
    "email": "SKING",
    "job_id": "AD_PRES", 
    "salary": 24000.0, 
    "manager_id": null, 
    "department_id": 90,
    "address":{"city": "Oxford",
              "STATE_PROVINCE": "Oxford",
              "STREET_ADDRESS": "Magdalen Centre, The Oxford Science Park"
              }
   }'
);

insert into t_json(id,data_echo_area) values
(sys_guid(),
'{
    "employee_id": 101, 
    "first_name": "Neena", 
    "last_name": "Kochhar", 
    "email": "NKOCHHAR", 
    "job_id": "AD_VP", 
    "salary": 17000.0, 
    "manager_id": 100, 
    "department_id": 90,
    "address":{"city": "South Brunswick",
               "STATE_PROVINCE": "New Jersey",
               "STREET_ADDRESS": "2007 Zagora St"
               }
   }'
);
commit;

首先创建包含json类型的字段并添加check约束保证加载的数据都是符合json格式的数据,当然也可以不加这个约束,但是需要保证INSERT的数据都是完全符合json格式的数据。当存在约束并加载不符合json格式数据的时候会报ORA-2290错误。
生成json格式数据可以参考get json from table using python

查询json类型数据的时候使用dot标识,例如

SQL> col employee_id for a15
SQL> col LAST_NAME for a20
SQL> col DEPARTMENT_ID for a10
SQL> select t.id,t.data_echo_area.employee_id,t.data_echo_area.last_name,t.data_echo_area.department_id from T_JSON t;

ID                               EMPLOYEE_ID     LAST_NAME            DEPARTMENT
-------------------------------- --------------- -------------------- ----------
487897D071801468E0530B01A8C03972 100             King                 90
487897D071811468E0530B01A8C03972 101             Kochhar              90

当查询address这个not-scalar的数据的时候,返回的也是json格式的文本。

SQL> select t.id,t.data_echo_area.address from T_JSON t;

ID                               ADDRESS
-------------------------------- ------------------------------------------------------------
4878DB92BD00152AE0530B01A8C0C923 {"city":"Oxford","STATE_PROVINCE":"Oxford","STREET_ADDRESS":
                                 "Magdalen Centre, The Oxford Science Park"}

4878DB92BD01152AE0530B01A8C0C923 {"city":"South Brunswick","STATE_PROVINCE":"New Jersey","STR
                                 EET_ADDRESS":"2007 Zagora St"}

查询包含json数据类型的列的数据字典有DBA_JSON_COLUMNS, USER_JSON_COLUMNS和ALL_JSON_COLUMNS.
下面测试下json check constraint的几种形式和json相关视图的关系

SQL> col TABLE_NAME for a8
SQL> col COLUMN_NAME for a15
SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB


SQL> alter table T_JSON disable constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

OWNER TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
----- -------- --------------- --------- -------------
JSON  T_JSON   DATA_ECHO_AREA  TEXT      CLOB

SQL> 
SQL> 
SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));

1 row created.

SQL> commit; 

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> 
SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json or length(data_echo_area) > 1000);

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected

SQL> alter table t_json drop constraint data_echo_area_json;

Table altered.

SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json);
alter table t_json add constraint data_echo_area_json check (data_echo_area is json)
                                  *
ERROR at line 1:
ORA-02293: cannot validate (JSON.DATA_ECHO_AREA_JSON) - check constraint violated


SQL> alter table t_json add constraint data_echo_area_json check (data_echo_area is json) novalidate;

Table altered.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';

no rows selected


SQL>  insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0));
 insert into t_json(id,data_echo_area) values (sys_guid(),rpad('leo',4000,0))
*
ERROR at line 1:
ORA-02290: check constraint (JSON.DATA_ECHO_AREA_JSON) violated


SQL> insert into t_json(id,data_echo_area) values
  2  (sys_guid(),
  3  '{
  4      "employee_id": 101, 
  5      "first_name": "Neena", 
  6      "last_name": "Kochhar", 
  7      "email": "NKOCHHAR", 
  8      "job_id": "AD_VP", 
  9      "salary": 17000.0, 
 10      "manager_id": 100, 
 11      "department_id": 90,
 12      "address":{"city": "South Brunswick",
 13                 "STATE_PROVINCE": "New Jersey",
 14                 "STREET_ADDRESS": "2007 Zagora St"
 15                 }
 16     }'
 17  );

1 row created.

SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> select * from DBA_JSON_COLUMNS where owner = 'JSON';
OWNER      TABLE_NA COLUMN_NAME     FORMAT    DATA_TYPE
---------- -------- --------------- --------- -------------
JSON       T_JSON   DATA_ECHO_AREA  TEXT      CLOB

当disable掉check约束的时候,此时可以从json的数据字典表查看到jsob字段的信息;
当drop掉check约束的时候,json的数据字典视图中不再显示t_json表的json字段信息;
当增加json约束和其他的非json约束的时候,json的数据字典中不显示json的字段信息;
在constaint为novalidate的时候,非json格式的数据无法INSERT到存在json格式的表中,且此时json相关的数据字典中没有json字段信息;
当INSERT到表中json格式的数据后,json的数据字典信息中记录了json字段的信息。

发表在 12c, json, LOB | 留下评论

ORA-28040: No matching authentication protocol in version 12.2.0.1 using 11g client

今天在体验Oracle 12.2.0.1的时候,遇到使用Oracle 11.2.0.1客户端连接数据库的时候报错ORA-28040 No Matching Authentication Protocol。
Oracle的版本是下载的exadata版本,文件为V839960-01.zip。
按照mos文档12c: ORA-28040 After Upgrade: No Matching Authentication Protocol (Doc ID 1957995.1)的描述,在数据库从11g升级到12c或者直接安装12c之后,从低版本的客户端或JDBC连接数据库后会报ORA-28040 No Matching Authentication Protocol错误。这个问题是由于在12c数据库中的默认设置造成的,涉及到的参数为:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=n
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=n
查阅了一下12.1和12.2.文档的关于SQLNET的说明,在两个版本中默认都是11,也就是允许11g的客户端进行连接,但是我的测试过程中使用11g的客户端依然报错,等到3月15日发行Linux版本再测试下。
对参数ALLOWED_LOGON_VERSION_CLIENT的说明,12.1中描述为

(1) 12a for Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later;
(2) 12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended);
(3) 11 for Oracle Database 11g authentication protocols (default);
(4) 10 for Oracle Database 10g authentication protocols;
(5) 8 for Oracle8i authentication protocol

12.2中描述为

(1) 12a for Oracle Database 12c Release 1 (12.1.0.2) or later (strongest protection)
Note:Using this setting, the clients can only authenticate using a de-optimized password version. For example, the 12C password version;
(2) 12 for the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (stronger protection)
Note:Using this setting, the clients can only authenticate using a password hash value that uses salt. For example, the 11G or 12C password versions;
(3) 11 for Oracle Database 11g authentication protocols (default);
(4) 10 for Oracle Database 10g authentication protocols;
(5) 8 for Oracle8i authentication protocol

不同版本客户端和服务器之间的支持矩阵说明请查阅MOS文档
Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)。

发表在 12c, sqlnet | 留下评论