Skip to content

[20170428]延迟块清除测试.txt

[20170428]延迟块清除测试.txt

–//做一个延迟块清除测试,前面的测试太乱了,思路非常不清楚.

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

SCOTT@book> alter database flashback on;
Database altered.

–//打开flashback ,就关闭了IMU,这样测试稳妥一些,不会出现奇怪的问题.

SCOTT@book> select flashback_on from v$database ;
FLASHBACK_ON
——————
YES

CREATE TABLESPACE TEA DATAFILE
‘/mnt/ramdisk/book/tea01.dbf’ SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

–//安全起见重启数据库再测试。

2.测试:
SCOTT@book> create table deptx tablespace tea as select * from dept ;
Table created.

–//dba = 6,129 .
SCOTT@book> update deptx set dname=lower(dname) where deptno=10;
1 row updated.

SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
——————————
2.21.1789

C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
———————————————————————- ———— ———— ———— ———— ———— ———— ———— —————- ———— ———— —————- —————- ——————-
ALTER SYSTEM DUMP UNDO BLOCK ‘_SYSSMU2_2996391332$’ XID 2 21 1789; 2 21 1789 3 4210 652 57 ACTIVE 1 1 02001500FD060000 00000000818AE050 2017-04-28 10:45:47
ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU2_2996391332$’;
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 4210;

SYS@book> alter system flush buffer_cache;
System altered.

SCOTT@book> alter system dump datafile ‘/mnt/ramdisk/book/tea01.dbf’ block 129;
System altered.

Block header dump: 0x01800081
Object id on Block? Y
seg/obj: 0x1677c csc: 0x03.4281a558 itc: 3 flg: – typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.4281a558
0x02 0x0002.015.000006fd 0x00c01072.028c.39 —- 1 fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01800081
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: –H-FL– lb: 0x2 cc: 3
~~~~~~~~~~~~~~~~~~~~=> 使用第2个事务槽.
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
–//lock没有清除. xid相关信息也和前面能对上.

SYS@book> ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU2_2996391332$’;
System altered.

TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x000e inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
uba: 0x00000000.028c.19 ext: 0x14 spc: 0x1510
uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
————————————————————————————————
0x14 9 0x00 0x06fc 0x000b 0x0003.42819ec6 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493346210
0x15 10 0x80 0x06fd 0x0014 0x0003.4281a5b5 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16 9 0x00 0x06fb 0x000e 0x0003.4281a41d 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493347262

–//scn = 0x0003.4281a5b5.

SCOTT@book> @ &r/scn10 0x0003.4281a5b5
C20 SCN_ SCN_BASE SCN_WRAP SCN_BASE SCN
——————– —- ——– ———— ———— ————
0x0003.4281a5b5 0003 4281a5b5 3 1115792821 14000694709

SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC GET_SYSTEM_CHANGE_NUMBER
———— ———— ————– ————- ————————
14000694616 10 accounting NEW YORK 14000694958

select 14000694616,trunc(14000694616/power(2,32)) scn_wrap,mod(14000694616,power(2,32)) scn_base from dual
14000694616 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
———— ———— ———— ———- ———-
14000694616 3 1115792728 3 4281a558

–//看看来之哪里:
$ cat scn.cmd
set count 8192
set width 210

p dba 6,129 kcbh.bas_kcbh
p dba 6,129 ktbbh.ktbbhcsc.kscnbas

p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas

p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas

p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas

quit

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd

BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a5b5 p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a558

BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)

BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558 p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x0001 (NONE)

BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x00000000

BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)

BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000
BBED> quit

–//终于清晰了.

3.提交在看看情况:(块延迟清除的情况)

SYS@book> @ &r/bh 6 129
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
—————- ——- —— —– ———- —– — ———- ———- ———- ———- ———- —————- ———–
0000000084B29130 6 129 1 data block xcur 1 0 0 0 0 0 0000000074DFE000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A74000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A76000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007500C000

SYS@book> alter system flush buffer_cache;
System altered.

SYS@book> @ &r/bh 6 129
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
—————- ——- —— —– ———- —– — ———- ———- ———- ———- ———- —————- ———–
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074DFE000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A74000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 0000000074A76000 DEPTX
0000000084B29130 6 129 1 data block free 0 0 0 0 0 0 000000007500C000

SCOTT@book> commit ;
Commit complete.

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU2_2996391332$’;
System altered.

TRN CTL:: seq: 0x028c chd: 0x001b ctl: 0x0015 inc: 0x00000000 nfb: 0x0002
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c01077.028c.26 scn: 0x0003.4281871a
Version: 0x01
FREE BLOCK POOL::
uba: 0x00c01077.028c.26 ext: 0x14 spc: 0x1142
uba: 0x00c01072.028c.39 ext: 0x14 spc: 0x2dc
uba: 0x00000000.028c.25 ext: 0x14 spc: 0x11cc
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84
uba: 0x00000000.021b.01 ext: 0x2 spc: 0x1f84

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
————————————————————————————————
0x14 9 0x00 0x06fc 0x000b 0x0003.42819ec6 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493346210
0x15 9 0x00 0x06fd 0xffff 0x0003.4281a86c 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493348218
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x16 9 0x00 0x06fb 0x000e 0x0003.4281a41d 0x00c01072 0x0000.000.00000000 0x00000001 0x00000000 1493347262

–//当前已经提交,事务scn=0x0003.4281a86c
SCOTT@book> @ &r/scn10 0x0003.4281a86c
C20 SCN_ SCN_BASE SCN_WRAP SCN_BASE SCN
——————– —- ——– ———— ———— ————
0x0003.4281a86c 0003 4281a86c 3 1115793516 14000695404

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a5b5

BBED> p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a558

BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)

BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558

BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x0001 (NONE)

BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x00000000

BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)

BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000

BBED> quit

–//可以发现块没有写入提交信息.因为块dba=6,129不在数据缓存,采用延迟块提交.

4.现在访问看看:

SCOTT@book> select ora_rowscn,deptx.*,dbms_flashback.get_system_change_number from scott.deptx where rownum=1;
ORA_ROWSCN DEPTNO DNAME LOC GET_SYSTEM_CHANGE_NUMBER
———— ———— ————– ————- ————————
14000695404 10 accounting NEW YORK 14000695738

select 14000695404,trunc(14000695404/power(2,32)) scn_wrap,mod(14000695404,power(2,32)) scn_base from dual
14000695404 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
———— ———— ———— ———- ———-
14000695404 3 1115793516 3 4281a86c

–//现在ora_rowscn=14000695404,与undo事务槽的scn=0x0003.4281a86c.
select 14000695738,trunc(14000695738/power(2,32)) scn_wrap,mod(14000695738,power(2,32)) scn_base from dual
14000695738 SCN_WRAP SCN_BASE SCN_WRAP16 SCN_BASE16
———— ———— ———— ———- ———-
14000695738 3 1115793850 3 4281a9ba

SCOTT@book> alter system checkpoint ;
System altered.

SCOTT@book> alter system dump datafile ‘/mnt/ramdisk/book/tea01.dbf’ block 129;
System altered.

Block header dump: 0x01800081
Object id on Block? Y
seg/obj: 0x1677c csc: 0x03.4281a9ba itc: 3 flg: – typ: 1 – DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C— 0 scn 0x0003.4281a558
0x02 0x0002.015.000006fd 0x00c01072.028c.39 C— 0 scn 0x0003.4281a86c
0x03 0x0000.000.00000000 0x00000000.0000.00 —- 0 fsc 0x0000.00000000
bdba: 0x01800081
data_block_dump,data header at 0x7f97dcb9a274

..
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 26 fb: –H-FL– lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b

–//看见了吗,提交了,块的lock表示也清除了.flag=C—. scn=0x0003.4281a86c

$ $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=scn.cmd
BBED> p dba 6,129 kcbh.bas_kcbh
ub4 bas_kcbh @8 0x4281a9ba p dba 6,129 ktbbh.ktbbhcsc.kscnbas
ub4 kscnbas @28 0x4281a9ba p dba 6,129 ktbbh.ktbbhitl[0].ktbitflg
ub2 ktbitflg @60 0x8000 (KTBFCOM)

BBED> p dba 6,129 ktbbh.ktbbhitl[0].ktbitbas
ub4 ktbitbas @64 0x4281a558

BBED> p dba 6,129 ktbbh.ktbbhitl[1].ktbitflg
ub2 ktbitflg @84 0x8000 (KTBFCOM) p dba 6,129 ktbbh.ktbbhitl[1].ktbitbas
ub4 ktbitbas @88 0x4281a86c p dba 6,129 ktbbh.ktbbhitl[2].ktbitflg
ub2 ktbitflg @108 0x0000 (NONE)

BBED> p dba 6,129 ktbbh.ktbbhitl[2].ktbitbas
ub4 ktbitbas @112 0x00000000

BBED> quit

–//终于完成了完整的分析过程,理解许多知识.

Advertisements

[20170426]为什么是4秒.txt

[20170426]为什么是4秒.txt

–//在开发程序时我一般会强调开发尽量不要写一些自定义函数,往往可能导致CPU忙。今天测试遇到一个问题:
–//原始的测试来之nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html

–//先重复测试。

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
—————————— ————– ——————————————————————————–
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

2.普通函数:
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);

SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;

RETURN l_dname;
END;
/

SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
EMPNO ENAME DEPTNO C20
———- ———- ———- ——————–
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:14.02

–//这样每一次调用都需要1秒。如果改用标量子查询。

SCOTT@book> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
EMPNO ENAME DEPTNO C20
———- ———- ———- ——————–
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:03.01

–//因为标量子查询,缓存了相同的结果,deptno仅仅有3个值,这样需要3秒。自己还真没想到标量子查询有这样效果!!

3. DETERMINISTIC Functions:

–//一般如果在在某个函数定义索引,需要DETERMINISTIC,表示返回结果固定。其实即使不固定,也可以这样定义。
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
DETERMINISTIC
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);

SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;

RETURN l_dname;
END;
/

SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
EMPNO ENAME DEPTNO C20
———- ———- ———- ——————–
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:04.02

–//4秒,为什么呢?仅仅3个值,按照道理应该3秒,而不是4秒,这个问题先放在后面解析。

4.RESULT CACHE
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
RESULT_CACHE
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);

SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;

RETURN l_dname;
END;
/

SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
..
14 rows selected.
Elapsed: 00:00:03.01

–//再次执行因为结果缓存了。很快返回。

SCOTT@book> select empno, ename, deptno, get_dept(deptno) dname from emp;
14 rows selected.
Elapsed: 00:00:00.00

5.回到前面,采用 DETERMINISTIC Functions为什么是4秒呢?

–//重新定义函数
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
DETERMINISTIC
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);

SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;

RETURN l_dname;
END;
/

–//如果仔细阅读原文:
As we can see here that execution time went down to near 4 seconds which was originally near 14 seconds. We can notice
that using scalar sub-query was little faster because Deterministic cache is maintained per fetch instead of total SQL
execution. I prefer to modify my function to deterministic only when I know that this function is used in various SQLs
and all SQL can not be modified (or required alot of effort) with scalar sub-query.

SCOTT@book> show array
arraysize 200
–//仔细看就明白了,实际与array大小有关,这样就很容易理解为什么是4秒,因为返回第1行是单独1个逻辑读。

–如果我设置array=2
20 1
30 30 1
20 30 2
30 10 2
20 10 2
30 20 2
30 20 2
10 1
–//这样13秒验证是否正确。

SCOTT@book> set array 2
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
14 rows selected.
Elapsed: 00:00:13.02

–//如果我设置array=3
20 1
30 30 20 2
30 30 10 2
20 10 30 3
20 30 20 2
10 1
–//这样11秒验证是否正确。
SCOTT@book> set array 3
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;

14 rows selected.
Elapsed: 00:00:11.02

–//如果我设置array=4
set array 4
20 1
30 30 20 30 2
30 10 20 10 3
30 20 30 20 2
10 1
–//应该是9秒:
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp ;
14 rows selected.
Elapsed: 00:00:09.01

–//如果你仔细观察输出时的停顿,可以发现是4条4条输出的,这里非常不好理解!!
–//但是如果改用标量子查询,结果就是3秒。

SCOTT@book> select empno, ename, deptno, (select get_dept(deptno) from dual )c20 from emp;
14 rows selected.
Elapsed: 00:00:03.01

–//还有1个简单的验证方法就是排序输出看看。
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp order by deptno;
14 rows selected.
Elapsed: 00:00:03.01

–//这样数据先进入排序区,arraysize大小就变得没有关系,有回到3秒。
–//其中细节可以自己体会。

–//这个测试终于让我明白oracle一些逻辑读细节。

总结
1.oracle 逻辑读不能跨块。
2.oracle 逻辑读第1行作为一个逻辑读,接着读取数量array作为第2个逻辑读。
3.做一个特殊情况:
SCOTT@book> create table empx as select * from emp order by deptno;
Table created.

SCOTT@book> select * from empx ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——————- ———- ———- ———-
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
14 rows selected.

SCOTT@book> set array 7
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from empx ;
EMPNO ENAME DEPTNO C20
———- ———- ———- ——————–
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
Elapsed: 00:00:04.02

10 1
10 10 20 20 20 20 20 2
30 30 30 30 30 30 1

–//如果设置6,多2秒。
SCOTT@book> set array 6
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from empx ;
EMPNO ENAME DEPTNO C20
———- ———- ———- ——————–
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
Elapsed: 00:00:06.01

10 1
10 10 20 20 20 20 2
20 30 30 30 30 30 2
30 1

–//OK正确。

4.不过我自己还是有一点不明白的,oracle的输出是按照array定义的数量输出的,不知道为什么?
按照我的理解输出应该是1,array数量-1,array数量…,有谁能给出合理解析呢?

[20140505]delete histogram.txt

昨天看了https://jonathanlewis.wordpress.com/2014/05/01/delete-histogram/
介绍删除直方图的方法,这样比较快捷.

SCOTT@test> @ver
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

SCOTT@test> create table t as select rownum id1 , mod(rownum,100)+1 id2, ‘test’ name from dual connect by level execute dbms_stats.gather_table_stats(user,’t’,cascade=>true,method_opt=>’for all columns size 1,for columns id1 size 254 ,for columns id2 size 254′);
PL/SQL procedure successfully completed.

SCOTT@test> SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name=’T’;
COLUMN_NAME NUM_DISTINCT HISTOGRAM
——————– ———— —————
ID1 10000 HEIGHT BALANCED
ID2 100 FREQUENCY
NAME 1 NONE

SCOTT@test> execute dbms_stats.set_column_stats(ownname=>user,tabname=>’T’,colname=> ‘id1′,distcnt=>NULL);
PL/SQL procedure successfully completed.

SCOTT@test> select column_name,num_distinct,histogram from dba_tab_columns where owner=user and table_name=’T’;
COLUMN_NAME NUM_DISTINCT HISTOGRAM
——————– ———— —————
ID1 10000 NONE
ID2 100 FREQUENCY
NAME 1 NONE

SCOTT@test> select * from dba_tab_histograms where owner=user and table_name=’T’ and column_name=’ID1′;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
—— ———- ——————– ————— ————– ———-
SCOTT T ID1 0 1
SCOTT T ID1 1 10000

[20140207]putty也有linux版本.txt

[20140207]putty也有linux版本.txt

今天无意中发现putty也有linux版本。下载地址如下:

http://pkgs.repoforge.org/putty/

下载对应的版本就可以使用了。

Hello world!

Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.

Here are some suggestions for your first post.

  1. You can find new ideas for what to blog about by reading the Daily Post.
  2. Add PressThis to your browser. It creates a new blog post for you about any interesting  page you read on the web.
  3. Make some changes to this page, and then hit preview on the right. You can always preview any post or edit it before you share it to the world.