[20221130]测试访问视图v$session几种情况的性能差异.txt
[20221130]测试访问视图v$session几种情况的性能差异.txt
--//前几天遇到的防水墙访问v$session视图的性能问题,我给测试看看三种情况下的性能差异.
1.环境:
SCOTT@book> @ 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 table job_times (sid number, time_ela number,method varchar2(20));
$ cat m20.txt
set verify off
host sleep $(echo &&3/150 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
l_appname varchar2(128);
l_module varchar2(128);
l_type varchar2(16);
l_process number;
l_osuser varchar2(30);
l_machine varchar2(64);
l_SCHEMANAME varchar2(30);
l_username varchar2(30);
l_service_name varchar2(30);
l_sid number;
l_serial# number;
begin
for i in 1 .. &&1 loop
&&5 select sys_context('userenv', 'sid') into l_sid from dual ;
&&6 with mysid as ( select /*+ materialize */ userenv('SID') n from dual )
select upper(nvl(program, 'null')),
upper(module),
type,
decode(nvl(instr(process, ':'), 0),
0,
nvl(process, 1234),
substr(process, 1, instr(process, ':') - 1)),
osuser,
machine,
SCHEMANAME,
USERNAME,
SERVICE_NAME,
sid,
serial#
into l_appname,
l_module,
l_type,
l_process,
l_osuser,
l_machine,
l_SCHEMANAME,
l_username,
l_service_name,
l_sid,
l_serial#
from sys.v_$session
&&6 ,mysid
where
&&4 sid = sys_context('userenv', 'sid')
&&5 sid = l_sid
&&6 sid = mysid.n
;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
quit
--//说明：参数1表示循环次数，参数2表示method，参数3表示设置延迟，避免开始同时执行的一些争用.
--//如果参数4,5,6 等于 '' -- -- 相当于谓词条件 sid = sys_context('userenv', 'sid')
--//如果参数4,5,6 等于 -- '' -- 相当于谓词条件 sid = l_sid
--//如果参数4,5,6 等于 -- -- '' 相当于谓词条件 sid = mysid.n
--//这样可以不用分别写三个测试脚本,仅仅通过参数控制选择执行的方式.
3.测试:
$ alias zzdate
alias zzdate='date +"trunc(sysdate)+%H/24+%M/1440+%S/86400 == %Y/%m/%d %T == timestamp'\''%Y-%m-%d %T'\''"'
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test1 {} '' -- -- >/dev/null;zzdate
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test2 {} -- '' -- >/dev/null;zzdate
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3 {} -- -- '' >/dev/null;zzdate
--//测试1:相当于谓词条件 sid = sys_context('userenv', 'sid')
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test1 {} '' -- -- >/dev/null;zzdate
trunc(sysdate)+11/24+36/1440+06/86400 == 2022/12/12 11:36:06 == timestamp'2022-12-12 11:36:06'
trunc(sysdate)+11/24+37/1440+12/86400 == 2022/12/12 11:37:12 == timestamp'2022-12-12 11:37:12'
--//相当于66秒完成.
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+11/24+36/1440+06/86400 trunc(sysdate)+11/24+37/1440+13/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
8640 129.0 89% | 4mkx2yruh1x45 2022-12-12 11:36:09 2022-12-12 11:37:12 8184 64
562 8.4 6% | 2022-12-12 11:36:07 2022-12-12 11:37:12 1 48
349 5.2 4% | 661ut1qj78akz 2022-12-12 11:36:07 2022-12-12 11:36:09 149 3
134 2.0 1% | g5saphnb8qw3f 2022-12-12 11:36:10 2022-12-12 11:37:11 88 50
3 .0 0% | 0ws7ahf1d78qa 2022-12-12 11:36:07 2022-12-12 11:36:07 3 1
2 .0 0% | 4vs91dcv7u1p6 2022-12-12 11:36:07 2022-12-12 11:36:07 2 1
2 .0 0% | 6utuby0ws1ww9 2022-12-12 11:36:25 2022-12-12 11:37:08 2 2
2 .0 0% | cm5vu20fhtnq1 2022-12-12 11:36:07 2022-12-12 11:36:07 2 1
8 rows selected.
SCOTT@book> @ sql_id 4mkx2yruh1x45
--SQL_ID = 4mkx2yruh1x45
SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION WHERE SID = SYS_CONTEXT('userenv', 'sid') ;
--//测试2:相当于谓词条件 sid = l_sid,而且我分开执行2条sql语句.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test2 {} -- '' -- >/dev/null;zzdate
trunc(sysdate)+11/24+38/1440+43/86400 == 2022/12/12 11:38:43 == timestamp'2022-12-12 11:38:43'
trunc(sysdate)+11/24+38/1440+58/86400 == 2022/12/12 11:38:58 == timestamp'2022-12-12 11:38:58'
--//仅仅需要15秒完成.
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+11/24+38/1440+43/86400 trunc(sysdate)+11/24+38/1440+59/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
695 43.4 34% | 6ztctd8mtd5ay 2022-12-12 11:38:46 2022-12-12 11:38:58 437 13
471 29.4 23% | dcp3kp34g2wbx 2022-12-12 11:38:46 2022-12-12 11:38:58 202 13
352 22.0 17% | 2022-12-12 11:38:46 2022-12-12 11:38:58 2 13
300 18.8 15% | 661ut1qj78akz 2022-12-12 11:38:44 2022-12-12 11:38:45 150 2
213 13.3 10% | 9wzdfbf670af8 2022-12-12 11:38:46 2022-12-12 11:38:58 121 13
1 .1 0% | 0s1p5fqnuysz2 2022-12-12 11:38:58 2022-12-12 11:38:58 1 1
1 .1 0% | d675kjgr4d8p4 2022-12-12 11:38:46 2022-12-12 11:38:46 1 1
7 rows selected.
SCOTT@book> @ sql_id 6ztctd8mtd5ay
--SQL_ID = 6ztctd8mtd5ay
SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION WHERE SID = :B1 ;
--//测试3: 相当于谓词条件 sid = mysid.n,使用with+materialize的缺点是产生日志.昏很慢.
--//测试失败,视乎临时表空间一直在增加,无法收回,导致磁盘满了,无法归档,整个数据库hang.明天继续测试.
--//移动临时表空间文件到/u01,重启数据库,自动建立一个新的临时表空间文件,然后继续测试.
$ ls -lh /u01/temp01.dbf
-rw-r----- 1 oracle oinstall 2.3G 2022-12-12 11:40:59 /u01/temp01.dbf
--//已经达到了2.3G.
$ ls -lh /mnt/ramdisk/book/temp01.dbf
-rw-r----- 1 oracle oinstall 21M 2022-12-14 09:15:54 /mnt/ramdisk/book/temp01.dbf
--//开始21M.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3 {} -- -- '' >/dev/null;zzdate
trunc(sysdate)+09/24+27/1440+28/86400 == 2022/12/14 09:27:28 == timestamp'2022-12-14 09:27:28'
trunc(sysdate)+09/24+30/1440+09/86400 == 2022/12/14 09:30:09 == timestamp'2022-12-14 09:30:09'
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+09/24+27/1440+28/86400 trunc(sysdate)+09/24+30/1440+10/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
19104 117.9 80% | acw3hm8jnf7jk 2022-12-14 09:27:33 2022-12-14 09:30:09 14897 156
3951 24.4 17% | 2022-12-14 09:27:33 2022-12-14 09:30:09 1 90
601 3.7 3% | 661ut1qj78akz 2022-12-14 09:27:29 2022-12-14 09:27:32 151 4
78 .5 0% | 1rhr1ht989sa3 2022-12-14 09:27:35 2022-12-14 09:30:00 67 48
2 .0 0% | 3gvgdjwb0d7q1 2022-12-14 09:29:38 2022-12-14 09:29:39 2 2
2 .0 0% | 85px9dq62dc0q 2022-12-14 09:30:08 2022-12-14 09:30:09 1 2
6 rows selected.
SCOTT@book> @ sql_id acw3hm8jnf7jk
--SQL_ID = acw3hm8jnf7jk
WITH MYSID AS ( SELECT /*+ materialize */ USERENV('SID') N FROM DUAL ) SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION ,MYSID WHERE SID = MYSID.N ;
--//测试的同时在另外的窗口执行如下:
$ seq 1500 | xargs -IQ echo 'sleep 1;ls -lh /mnt/ramdisk/book/temp01.dbf|ts.awk'| bash
[2022-12-14 09:27:27] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:28] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:29] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:30] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:31] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:32] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:33] -rw-r----- 1 oracle oinstall 100M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:34] -rw-r----- 1 oracle oinstall 142M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:35] -rw-r----- 1 oracle oinstall 148M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:36] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:37] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:38] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:39] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:40] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:41] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:43] -rw-r----- 1 oracle oinstall 152M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:44] -rw-r----- 1 oracle oinstall 152M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
--//可以发现临时表空间在缓慢增加,不过到了152M,不再增加,应该设置大于15XM,不过应该对测试影响不大.
--//我临时表空间每个UNIFORM SIZE分配1M.150个会话使用15XM基本符合.不知道为什么前面的测试临时表空间增加.
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/mnt/ramdisk/book/temp01.dbf' SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
--//再次重复测试,避免临时表空间扩展的影响.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3x {} -- -- '' >/dev/null;zzdate
trunc(sysdate)+09/24+39/1440+07/86400 == 2022/12/14 09:39:07 == timestamp'2022-12-14 09:39:07'
trunc(sysdate)+09/24+41/1440+58/86400 == 2022/12/14 09:41:58 == timestamp'2022-12-14 09:41:58'
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+09/24+39/1440+07/86400 trunc(sysdate)+09/24+41/1440+58/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
20968 122.6 84% | acw3hm8jnf7jk 2022-12-14 09:39:09 2022-12-14 09:41:57 16547 168
3611 21.1 14% | 2022-12-14 09:39:09 2022-12-14 09:41:55 1 92
300 1.8 1% | 661ut1qj78akz 2022-12-14 09:39:07 2022-12-14 09:39:08 150 2
63 .4 0% | 1rhr1ht989sa3 2022-12-14 09:39:09 2022-12-14 09:41:55 51 43
38 .2 0% | 8uc08r76472t2 2022-12-14 09:39:10 2022-12-14 09:39:11 19 2
4 .0 0% | f711myt0q6cma 2022-12-14 09:41:51 2022-12-14 09:41:57 3 4
2 .0 0% | carjduabxn2mf 2022-12-14 09:40:42 2022-12-14 09:40:43 1 2
1 .0 0% | gttzhja2tn7n7 2022-12-14 09:41:01 2022-12-14 09:41:01 1 1
8 rows selected.
SCOTT@book> @ ashtop event 1=1 trunc(sysdate)+09/24+39/1440+07/86400 trunc(sysdate)+09/24+41/1440+58/86400
Total Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
6183 36.2 25% | log file switch (checkpoint incomplete) 2022-12-14 09:39:10 2022-12-14 09:41:57 4557 76
3421 20.0 14% | latch: row cache objects 2022-12-14 09:39:12 2022-12-14 09:41:55 3365 90
3345 19.6 13% | enq: US - contention 2022-12-14 09:39:33 2022-12-14 09:41:52 2688 109
3342 19.5 13% | buffer busy waits 2022-12-14 09:39:09 2022-12-14 09:41:54 2623 143
2755 16.1 11% | 2022-12-14 09:39:08 2022-12-14 09:41:55 2243 99
2456 14.4 10% | latch: object queue header operation 2022-12-14 09:39:09 2022-12-14 09:41:55 26 71
956 5.6 4% | enq: HW - contention 2022-12-14 09:39:13 2022-12-14 09:41:52 814 62
955 5.6 4% | latch free 2022-12-14 09:39:09 2022-12-14 09:41:50 464 48
441 2.6 2% | direct path write temp 2022-12-14 09:39:09 2022-12-14 09:41:55 439 45
299 1.7 1% | ADR block file read 2022-12-14 09:39:07 2022-12-14 09:39:08 150 2
291 1.7 1% | latch: enqueue hash chains 2022-12-14 09:39:12 2022-12-14 09:41:46 170 18
145 .8 1% | latch: redo allocation 2022-12-14 09:39:15 2022-12-14 09:41:55 134 26
103 .6 0% | latch: undo global data 2022-12-14 09:39:24 2022-12-14 09:41:41 103 9
82 .5 0% | cursor: pin S 2022-12-14 09:39:52 2022-12-14 09:41:38 1 4
37 .2 0% | latch: cache buffers lru chain 2022-12-14 09:39:09 2022-12-14 09:41:55 6 18
36 .2 0% | db file sequential read 2022-12-14 09:39:42 2022-12-14 09:41:53 36 31
35 .2 0% | LGWR wait for redo copy 2022-12-14 09:39:36 2022-12-14 09:41:52 1 35
26 .2 0% | Log archive I/O 2022-12-14 09:39:48 2022-12-14 09:41:55 1 26
24 .1 0% | undo segment extension 2022-12-14 09:39:34 2022-12-14 09:41:46 18 23
16 .1 0% | latch: cache buffers chains 2022-12-14 09:39:57 2022-12-14 09:41:49 16 10
10 .1 0% | latch: redo copy 2022-12-14 09:41:53 2022-12-14 09:41:53 10 1
9 .1 0% | log file parallel write 2022-12-14 09:39:18 2022-12-14 09:41:07 1 9
5 .0 0% | log file sequential read 2022-12-14 09:39:51 2022-12-14 09:41:49 1 5
5 .0 0% | log file sync 2022-12-14 09:39:09 2022-12-14 09:41:53 1 3
5 .0 0% | reliable message 2022-12-14 09:39:42 2022-12-14 09:40:54 5 5
3 .0 0% | change tracking file synchronous write 2022-12-14 09:41:06 2022-12-14 09:41:55 1 3
1 .0 0% | enq: TX - contention 2022-12-14 09:40:35 2022-12-14 09:40:35 1 1
1 .0 0% | latch: session allocation 2022-12-14 09:41:50 2022-12-14 09:41:50 1 1
28 rows selected.
--//主要等待在log file switch (checkpoint incomplete)上.也就是使用with+materialize更慢.
--//测试的同时在另外的窗口执行如下:
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
1507
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
69
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
563
4.汇总测试结果如下:
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
test2 150 1144 171599
test1 150 6220 932951
test3 150 15363 2304477
test3x 150 16399 2459831
--//可以看出分开写最快. 6220/1144 = 5.44,也就是如果链接遇到的问题http://blog.itpub.net/267265/viewspace-2926302/,改成分
--//开写至少提高10倍以上的性能,甚至更多,因为生产系统连接的会话数量更大.
5.补充忘记记录观察redo size的大小:
SYS@book> alter system archive log current ;
System altered.
RMAN> list archivelog all completed after 'trunc(sysdate)+09/24+39/1440+07/86400';
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
5447 1 4907 A 2022-12-14 09:37:33
Name: /u01/app/oracle/archivelog/book/1_4907_896605872.dbf
5448 1 4908 A 2022-12-14 09:39:12
Name: /u01/app/oracle/archivelog/book/1_4908_896605872.dbf
5449 1 4909 A 2022-12-14 09:39:15
Name: /u01/app/oracle/archivelog/book/1_4909_896605872.dbf
5450 1 4910 A 2022-12-14 09:39:18
Name: /u01/app/oracle/archivelog/book/1_4910_896605872.dbf
5451 1 4911 A 2022-12-14 09:39:21
Name: /u01/app/oracle/archivelog/book/1_4911_896605872.dbf
5452 1 4912 A 2022-12-14 09:39:24
Name: /u01/app/oracle/archivelog/book/1_4912_896605872.dbf
5453 1 4913 A 2022-12-14 09:39:27
Name: /u01/app/oracle/archivelog/book/1_4913_896605872.dbf
5454 1 4914 A 2022-12-14 09:39:30
Name: /u01/app/oracle/archivelog/book/1_4914_896605872.dbf
5455 1 4915 A 2022-12-14 09:39:33
Name: /u01/app/oracle/archivelog/book/1_4915_896605872.dbf
5456 1 4916 A 2022-12-14 09:39:36
Name: /u01/app/oracle/archivelog/book/1_4916_896605872.dbf
5457 1 4917 A 2022-12-14 09:39:39
Name: /u01/app/oracle/archivelog/book/1_4917_896605872.dbf
5458 1 4918 A 2022-12-14 09:39:42
Name: /u01/app/oracle/archivelog/book/1_4918_896605872.dbf
5459 1 4919 A 2022-12-14 09:39:45
Name: /u01/app/oracle/archivelog/book/1_4919_896605872.dbf
5460 1 4920 A 2022-12-14 09:39:48
Name: /u01/app/oracle/archivelog/book/1_4920_896605872.dbf
5461 1 4921 A 2022-12-14 09:39:51
Name: /u01/app/oracle/archivelog/book/1_4921_896605872.dbf
5462 1 4922 A 2022-12-14 09:39:54
Name: /u01/app/oracle/archivelog/book/1_4922_896605872.dbf
5463 1 4923 A 2022-12-14 09:39:57
Name: /u01/app/oracle/archivelog/book/1_4923_896605872.dbf
5464 1 4924 A 2022-12-14 09:40:00
Name: /u01/app/oracle/archivelog/book/1_4924_896605872.dbf
5465 1 4925 A 2022-12-14 09:40:03
Name: /u01/app/oracle/archivelog/book/1_4925_896605872.dbf
5466 1 4926 A 2022-12-14 09:40:06
Name: /u01/app/oracle/archivelog/book/1_4926_896605872.dbf
5467 1 4927 A 2022-12-14 09:40:09
Name: /u01/app/oracle/archivelog/book/1_4927_896605872.dbf
5468 1 4928 A 2022-12-14 09:40:12
Name: /u01/app/oracle/archivelog/book/1_4928_896605872.dbf
5469 1 4929 A 2022-12-14 09:40:15
Name: /u01/app/oracle/archivelog/book/1_4929_896605872.dbf
5470 1 4930 A 2022-12-14 09:40:18
Name: /u01/app/oracle/archivelog/book/1_4930_896605872.dbf
5471 1 4931 A 2022-12-14 09:40:21
Name: /u01/app/oracle/archivelog/book/1_4931_896605872.dbf
5472 1 4932 A 2022-12-14 09:40:24
Name: /u01/app/oracle/archivelog/book/1_4932_896605872.dbf
5473 1 4933 A 2022-12-14 09:40:27
Name: /u01/app/oracle/archivelog/book/1_4933_896605872.dbf
5474 1 4934 A 2022-12-14 09:40:30
Name: /u01/app/oracle/archivelog/book/1_4934_896605872.dbf
5475 1 4935 A 2022-12-14 09:40:33
Name: /u01/app/oracle/archivelog/book/1_4935_896605872.dbf
5476 1 4936 A 2022-12-14 09:40:36
Name: /u01/app/oracle/archivelog/book/1_4936_896605872.dbf
5477 1 4937 A 2022-12-14 09:40:39
Name: /u01/app/oracle/archivelog/book/1_4937_896605872.dbf
5478 1 4938 A 2022-12-14 09:40:42
Name: /u01/app/oracle/archivelog/book/1_4938_896605872.dbf
5479 1 4939 A 2022-12-14 09:40:45
Name: /u01/app/oracle/archivelog/book/1_4939_896605872.dbf
5480 1 4940 A 2022-12-14 09:40:48
Name: /u01/app/oracle/archivelog/book/1_4940_896605872.dbf
5481 1 4941 A 2022-12-14 09:40:51
Name: /u01/app/oracle/archivelog/book/1_4941_896605872.dbf
5482 1 4942 A 2022-12-14 09:40:54
Name: /u01/app/oracle/archivelog/book/1_4942_896605872.dbf
5483 1 4943 A 2022-12-14 09:40:57
Name: /u01/app/oracle/archivelog/book/1_4943_896605872.dbf
5484 1 4944 A 2022-12-14 09:41:00
Name: /u01/app/oracle/archivelog/book/1_4944_896605872.dbf
5485 1 4945 A 2022-12-14 09:41:03
Name: /u01/app/oracle/archivelog/book/1_4945_896605872.dbf
5486 1 4946 A 2022-12-14 09:41:06
Name: /u01/app/oracle/archivelog/book/1_4946_896605872.dbf
5487 1 4947 A 2022-12-14 09:41:09
Name: /u01/app/oracle/archivelog/book/1_4947_896605872.dbf
5488 1 4948 A 2022-12-14 09:41:12
Name: /u01/app/oracle/archivelog/book/1_4948_896605872.dbf
5489 1 4949 A 2022-12-14 09:41:15
Name: /u01/app/oracle/archivelog/book/1_4949_896605872.dbf
5490 1 4950 A 2022-12-14 09:41:18
Name: /u01/app/oracle/archivelog/book/1_4950_896605872.dbf
5491 1 4951 A 2022-12-14 09:41:21
Name: /u01/app/oracle/archivelog/book/1_4951_896605872.dbf
5492 1 4952 A 2022-12-14 09:41:24
Name: /u01/app/oracle/archivelog/book/1_4952_896605872.dbf
5493 1 4953 A 2022-12-14 09:41:27
Name: /u01/app/oracle/archivelog/book/1_4953_896605872.dbf
5494 1 4954 A 2022-12-14 09:41:30
Name: /u01/app/oracle/archivelog/book/1_4954_896605872.dbf
5495 1 4955 A 2022-12-14 09:41:33
Name: /u01/app/oracle/archivelog/book/1_4955_896605872.dbf
5496 1 4956 A 2022-12-14 09:41:36
Name: /u01/app/oracle/archivelog/book/1_4956_896605872.dbf
5497 1 4957 A 2022-12-14 09:41:39
Name: /u01/app/oracle/archivelog/book/1_4957_896605872.dbf
5498 1 4958 A 2022-12-14 09:41:42
Name: /u01/app/oracle/archivelog/book/1_4958_896605872.dbf
5499 1 4959 A 2022-12-14 09:41:45
Name: /u01/app/oracle/archivelog/book/1_4959_896605872.dbf
5500 1 4960 A 2022-12-14 09:41:48
Name: /u01/app/oracle/archivelog/book/1_4960_896605872.dbf
5501 1 4961 A 2022-12-14 09:41:51
Name: /u01/app/oracle/archivelog/book/1_4961_896605872.dbf
5502 1 4962 A 2022-12-14 09:41:54
Name: /u01/app/oracle/archivelog/book/1_4962_896605872.dbf
5503 1 4963 A 2022-12-14 09:41:57
Name: /u01/app/oracle/archivelog/book/1_4963_896605872.dbf
$ du -cm /u01/app/oracle/archivelog/book/1_49*_896605872.dbf | tail -1
3010 total
--//接近3G
$ ls -lh /u01/app/oracle/archivelog/book/1_490[123456]_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:00 /u01/app/oracle/archivelog/book/1_4901_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:03 /u01/app/oracle/archivelog/book/1_4902_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:06 /u01/app/oracle/archivelog/book/1_4903_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:09 /u01/app/oracle/archivelog/book/1_4904_896605872.dbf
-rw-r----- 1 oracle oinstall 45M 2022-12-14 09:37:30 /u01/app/oracle/archivelog/book/1_4905_896605872.dbf
-rw-r----- 1 oracle oinstall 46M 2022-12-14 09:37:33 /u01/app/oracle/archivelog/book/1_4906_896605872.dbf
--//大概产生了3010-48*4-45-46 = 2727M.也就是不能大量使用with+materialize的方式.
--//前几天遇到的防水墙访问v$session视图的性能问题,我给测试看看三种情况下的性能差异.
1.环境:
SCOTT@book> @ 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 table job_times (sid number, time_ela number,method varchar2(20));
$ cat m20.txt
set verify off
host sleep $(echo &&3/150 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
l_appname varchar2(128);
l_module varchar2(128);
l_type varchar2(16);
l_process number;
l_osuser varchar2(30);
l_machine varchar2(64);
l_SCHEMANAME varchar2(30);
l_username varchar2(30);
l_service_name varchar2(30);
l_sid number;
l_serial# number;
begin
for i in 1 .. &&1 loop
&&5 select sys_context('userenv', 'sid') into l_sid from dual ;
&&6 with mysid as ( select /*+ materialize */ userenv('SID') n from dual )
select upper(nvl(program, 'null')),
upper(module),
type,
decode(nvl(instr(process, ':'), 0),
0,
nvl(process, 1234),
substr(process, 1, instr(process, ':') - 1)),
osuser,
machine,
SCHEMANAME,
USERNAME,
SERVICE_NAME,
sid,
serial#
into l_appname,
l_module,
l_type,
l_process,
l_osuser,
l_machine,
l_SCHEMANAME,
l_username,
l_service_name,
l_sid,
l_serial#
from sys.v_$session
&&6 ,mysid
where
&&4 sid = sys_context('userenv', 'sid')
&&5 sid = l_sid
&&6 sid = mysid.n
;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
quit
--//说明：参数1表示循环次数，参数2表示method，参数3表示设置延迟，避免开始同时执行的一些争用.
--//如果参数4,5,6 等于 '' -- -- 相当于谓词条件 sid = sys_context('userenv', 'sid')
--//如果参数4,5,6 等于 -- '' -- 相当于谓词条件 sid = l_sid
--//如果参数4,5,6 等于 -- -- '' 相当于谓词条件 sid = mysid.n
--//这样可以不用分别写三个测试脚本,仅仅通过参数控制选择执行的方式.
3.测试:
$ alias zzdate
alias zzdate='date +"trunc(sysdate)+%H/24+%M/1440+%S/86400 == %Y/%m/%d %T == timestamp'\''%Y-%m-%d %T'\''"'
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test1 {} '' -- -- >/dev/null;zzdate
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test2 {} -- '' -- >/dev/null;zzdate
zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3 {} -- -- '' >/dev/null;zzdate
--//测试1:相当于谓词条件 sid = sys_context('userenv', 'sid')
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test1 {} '' -- -- >/dev/null;zzdate
trunc(sysdate)+11/24+36/1440+06/86400 == 2022/12/12 11:36:06 == timestamp'2022-12-12 11:36:06'
trunc(sysdate)+11/24+37/1440+12/86400 == 2022/12/12 11:37:12 == timestamp'2022-12-12 11:37:12'
--//相当于66秒完成.
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+11/24+36/1440+06/86400 trunc(sysdate)+11/24+37/1440+13/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
8640 129.0 89% | 4mkx2yruh1x45 2022-12-12 11:36:09 2022-12-12 11:37:12 8184 64
562 8.4 6% | 2022-12-12 11:36:07 2022-12-12 11:37:12 1 48
349 5.2 4% | 661ut1qj78akz 2022-12-12 11:36:07 2022-12-12 11:36:09 149 3
134 2.0 1% | g5saphnb8qw3f 2022-12-12 11:36:10 2022-12-12 11:37:11 88 50
3 .0 0% | 0ws7ahf1d78qa 2022-12-12 11:36:07 2022-12-12 11:36:07 3 1
2 .0 0% | 4vs91dcv7u1p6 2022-12-12 11:36:07 2022-12-12 11:36:07 2 1
2 .0 0% | 6utuby0ws1ww9 2022-12-12 11:36:25 2022-12-12 11:37:08 2 2
2 .0 0% | cm5vu20fhtnq1 2022-12-12 11:36:07 2022-12-12 11:36:07 2 1
8 rows selected.
SCOTT@book> @ sql_id 4mkx2yruh1x45
--SQL_ID = 4mkx2yruh1x45
SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION WHERE SID = SYS_CONTEXT('userenv', 'sid') ;
--//测试2:相当于谓词条件 sid = l_sid,而且我分开执行2条sql语句.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test2 {} -- '' -- >/dev/null;zzdate
trunc(sysdate)+11/24+38/1440+43/86400 == 2022/12/12 11:38:43 == timestamp'2022-12-12 11:38:43'
trunc(sysdate)+11/24+38/1440+58/86400 == 2022/12/12 11:38:58 == timestamp'2022-12-12 11:38:58'
--//仅仅需要15秒完成.
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+11/24+38/1440+43/86400 trunc(sysdate)+11/24+38/1440+59/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
695 43.4 34% | 6ztctd8mtd5ay 2022-12-12 11:38:46 2022-12-12 11:38:58 437 13
471 29.4 23% | dcp3kp34g2wbx 2022-12-12 11:38:46 2022-12-12 11:38:58 202 13
352 22.0 17% | 2022-12-12 11:38:46 2022-12-12 11:38:58 2 13
300 18.8 15% | 661ut1qj78akz 2022-12-12 11:38:44 2022-12-12 11:38:45 150 2
213 13.3 10% | 9wzdfbf670af8 2022-12-12 11:38:46 2022-12-12 11:38:58 121 13
1 .1 0% | 0s1p5fqnuysz2 2022-12-12 11:38:58 2022-12-12 11:38:58 1 1
1 .1 0% | d675kjgr4d8p4 2022-12-12 11:38:46 2022-12-12 11:38:46 1 1
7 rows selected.
SCOTT@book> @ sql_id 6ztctd8mtd5ay
--SQL_ID = 6ztctd8mtd5ay
SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION WHERE SID = :B1 ;
--//测试3: 相当于谓词条件 sid = mysid.n,使用with+materialize的缺点是产生日志.昏很慢.
--//测试失败,视乎临时表空间一直在增加,无法收回,导致磁盘满了,无法归档,整个数据库hang.明天继续测试.
--//移动临时表空间文件到/u01,重启数据库,自动建立一个新的临时表空间文件,然后继续测试.
$ ls -lh /u01/temp01.dbf
-rw-r----- 1 oracle oinstall 2.3G 2022-12-12 11:40:59 /u01/temp01.dbf
--//已经达到了2.3G.
$ ls -lh /mnt/ramdisk/book/temp01.dbf
-rw-r----- 1 oracle oinstall 21M 2022-12-14 09:15:54 /mnt/ramdisk/book/temp01.dbf
--//开始21M.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3 {} -- -- '' >/dev/null;zzdate
trunc(sysdate)+09/24+27/1440+28/86400 == 2022/12/14 09:27:28 == timestamp'2022-12-14 09:27:28'
trunc(sysdate)+09/24+30/1440+09/86400 == 2022/12/14 09:30:09 == timestamp'2022-12-14 09:30:09'
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+09/24+27/1440+28/86400 trunc(sysdate)+09/24+30/1440+10/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
19104 117.9 80% | acw3hm8jnf7jk 2022-12-14 09:27:33 2022-12-14 09:30:09 14897 156
3951 24.4 17% | 2022-12-14 09:27:33 2022-12-14 09:30:09 1 90
601 3.7 3% | 661ut1qj78akz 2022-12-14 09:27:29 2022-12-14 09:27:32 151 4
78 .5 0% | 1rhr1ht989sa3 2022-12-14 09:27:35 2022-12-14 09:30:00 67 48
2 .0 0% | 3gvgdjwb0d7q1 2022-12-14 09:29:38 2022-12-14 09:29:39 2 2
2 .0 0% | 85px9dq62dc0q 2022-12-14 09:30:08 2022-12-14 09:30:09 1 2
6 rows selected.
SCOTT@book> @ sql_id acw3hm8jnf7jk
--SQL_ID = acw3hm8jnf7jk
WITH MYSID AS ( SELECT /*+ materialize */ USERENV('SID') N FROM DUAL ) SELECT UPPER(NVL(PROGRAM, 'null')), UPPER(MODULE), TYPE, DECODE(NVL(INSTR(PROCESS, ':'), 0), 0, NVL(PROCESS, 1234), SUBSTR(PROCESS, 1, INSTR(PROCESS, ':') - 1)), OSUSER, MACHINE, SCHEMANAME, USERNAME, SERVICE_NAME, SID, SERIAL# FROM SYS.V_$SESSION ,MYSID WHERE SID = MYSID.N ;
--//测试的同时在另外的窗口执行如下:
$ seq 1500 | xargs -IQ echo 'sleep 1;ls -lh /mnt/ramdisk/book/temp01.dbf|ts.awk'| bash
[2022-12-14 09:27:27] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:28] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:29] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:30] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:31] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:32] -rw-r----- 1 oracle oinstall 21M Dec 14 09:15 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:33] -rw-r----- 1 oracle oinstall 100M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:34] -rw-r----- 1 oracle oinstall 142M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:35] -rw-r----- 1 oracle oinstall 148M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:36] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:37] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:38] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:39] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:40] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:41] -rw-r----- 1 oracle oinstall 149M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:43] -rw-r----- 1 oracle oinstall 152M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
[2022-12-14 09:27:44] -rw-r----- 1 oracle oinstall 152M Dec 14 09:27 /mnt/ramdisk/book/temp01.dbf
--//可以发现临时表空间在缓慢增加,不过到了152M,不再增加,应该设置大于15XM,不过应该对测试影响不大.
--//我临时表空间每个UNIFORM SIZE分配1M.150个会话使用15XM基本符合.不知道为什么前面的测试临时表空间增加.
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'/mnt/ramdisk/book/temp01.dbf' SIZE 20M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
--//再次重复测试,避免临时表空间扩展的影响.
$ zzdate;seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m20.txt 2e4 test3x {} -- -- '' >/dev/null;zzdate
trunc(sysdate)+09/24+39/1440+07/86400 == 2022/12/14 09:39:07 == timestamp'2022-12-14 09:39:07'
trunc(sysdate)+09/24+41/1440+58/86400 == 2022/12/14 09:41:58 == timestamp'2022-12-14 09:41:58'
SCOTT@book> @ ashtop sql_id 1=1 trunc(sysdate)+09/24+39/1440+07/86400 trunc(sysdate)+09/24+41/1440+58/86400
Total Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
20968 122.6 84% | acw3hm8jnf7jk 2022-12-14 09:39:09 2022-12-14 09:41:57 16547 168
3611 21.1 14% | 2022-12-14 09:39:09 2022-12-14 09:41:55 1 92
300 1.8 1% | 661ut1qj78akz 2022-12-14 09:39:07 2022-12-14 09:39:08 150 2
63 .4 0% | 1rhr1ht989sa3 2022-12-14 09:39:09 2022-12-14 09:41:55 51 43
38 .2 0% | 8uc08r76472t2 2022-12-14 09:39:10 2022-12-14 09:39:11 19 2
4 .0 0% | f711myt0q6cma 2022-12-14 09:41:51 2022-12-14 09:41:57 3 4
2 .0 0% | carjduabxn2mf 2022-12-14 09:40:42 2022-12-14 09:40:43 1 2
1 .0 0% | gttzhja2tn7n7 2022-12-14 09:41:01 2022-12-14 09:41:01 1 1
8 rows selected.
SCOTT@book> @ ashtop event 1=1 trunc(sysdate)+09/24+39/1440+07/86400 trunc(sysdate)+09/24+41/1440+58/86400
Total Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- --------
6183 36.2 25% | log file switch (checkpoint incomplete) 2022-12-14 09:39:10 2022-12-14 09:41:57 4557 76
3421 20.0 14% | latch: row cache objects 2022-12-14 09:39:12 2022-12-14 09:41:55 3365 90
3345 19.6 13% | enq: US - contention 2022-12-14 09:39:33 2022-12-14 09:41:52 2688 109
3342 19.5 13% | buffer busy waits 2022-12-14 09:39:09 2022-12-14 09:41:54 2623 143
2755 16.1 11% | 2022-12-14 09:39:08 2022-12-14 09:41:55 2243 99
2456 14.4 10% | latch: object queue header operation 2022-12-14 09:39:09 2022-12-14 09:41:55 26 71
956 5.6 4% | enq: HW - contention 2022-12-14 09:39:13 2022-12-14 09:41:52 814 62
955 5.6 4% | latch free 2022-12-14 09:39:09 2022-12-14 09:41:50 464 48
441 2.6 2% | direct path write temp 2022-12-14 09:39:09 2022-12-14 09:41:55 439 45
299 1.7 1% | ADR block file read 2022-12-14 09:39:07 2022-12-14 09:39:08 150 2
291 1.7 1% | latch: enqueue hash chains 2022-12-14 09:39:12 2022-12-14 09:41:46 170 18
145 .8 1% | latch: redo allocation 2022-12-14 09:39:15 2022-12-14 09:41:55 134 26
103 .6 0% | latch: undo global data 2022-12-14 09:39:24 2022-12-14 09:41:41 103 9
82 .5 0% | cursor: pin S 2022-12-14 09:39:52 2022-12-14 09:41:38 1 4
37 .2 0% | latch: cache buffers lru chain 2022-12-14 09:39:09 2022-12-14 09:41:55 6 18
36 .2 0% | db file sequential read 2022-12-14 09:39:42 2022-12-14 09:41:53 36 31
35 .2 0% | LGWR wait for redo copy 2022-12-14 09:39:36 2022-12-14 09:41:52 1 35
26 .2 0% | Log archive I/O 2022-12-14 09:39:48 2022-12-14 09:41:55 1 26
24 .1 0% | undo segment extension 2022-12-14 09:39:34 2022-12-14 09:41:46 18 23
16 .1 0% | latch: cache buffers chains 2022-12-14 09:39:57 2022-12-14 09:41:49 16 10
10 .1 0% | latch: redo copy 2022-12-14 09:41:53 2022-12-14 09:41:53 10 1
9 .1 0% | log file parallel write 2022-12-14 09:39:18 2022-12-14 09:41:07 1 9
5 .0 0% | log file sequential read 2022-12-14 09:39:51 2022-12-14 09:41:49 1 5
5 .0 0% | log file sync 2022-12-14 09:39:09 2022-12-14 09:41:53 1 3
5 .0 0% | reliable message 2022-12-14 09:39:42 2022-12-14 09:40:54 5 5
3 .0 0% | change tracking file synchronous write 2022-12-14 09:41:06 2022-12-14 09:41:55 1 3
1 .0 0% | enq: TX - contention 2022-12-14 09:40:35 2022-12-14 09:40:35 1 1
1 .0 0% | latch: session allocation 2022-12-14 09:41:50 2022-12-14 09:41:50 1 1
28 rows selected.
--//主要等待在log file switch (checkpoint incomplete)上.也就是使用with+materialize更慢.
--//测试的同时在另外的窗口执行如下:
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
1507
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
69
SCOTT@book> select count(*) from V$TEMPSEG_USAGE ;
COUNT(*)
----------
563
4.汇总测试结果如下:
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
test2 150 1144 171599
test1 150 6220 932951
test3 150 15363 2304477
test3x 150 16399 2459831
--//可以看出分开写最快. 6220/1144 = 5.44,也就是如果链接遇到的问题http://blog.itpub.net/267265/viewspace-2926302/,改成分
--//开写至少提高10倍以上的性能,甚至更多,因为生产系统连接的会话数量更大.
5.补充忘记记录观察redo size的大小:
SYS@book> alter system archive log current ;
System altered.
RMAN> list archivelog all completed after 'trunc(sysdate)+09/24+39/1440+07/86400';
List of Archived Log Copies for database with db_unique_name BOOK
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - -------------------
5447 1 4907 A 2022-12-14 09:37:33
Name: /u01/app/oracle/archivelog/book/1_4907_896605872.dbf
5448 1 4908 A 2022-12-14 09:39:12
Name: /u01/app/oracle/archivelog/book/1_4908_896605872.dbf
5449 1 4909 A 2022-12-14 09:39:15
Name: /u01/app/oracle/archivelog/book/1_4909_896605872.dbf
5450 1 4910 A 2022-12-14 09:39:18
Name: /u01/app/oracle/archivelog/book/1_4910_896605872.dbf
5451 1 4911 A 2022-12-14 09:39:21
Name: /u01/app/oracle/archivelog/book/1_4911_896605872.dbf
5452 1 4912 A 2022-12-14 09:39:24
Name: /u01/app/oracle/archivelog/book/1_4912_896605872.dbf
5453 1 4913 A 2022-12-14 09:39:27
Name: /u01/app/oracle/archivelog/book/1_4913_896605872.dbf
5454 1 4914 A 2022-12-14 09:39:30
Name: /u01/app/oracle/archivelog/book/1_4914_896605872.dbf
5455 1 4915 A 2022-12-14 09:39:33
Name: /u01/app/oracle/archivelog/book/1_4915_896605872.dbf
5456 1 4916 A 2022-12-14 09:39:36
Name: /u01/app/oracle/archivelog/book/1_4916_896605872.dbf
5457 1 4917 A 2022-12-14 09:39:39
Name: /u01/app/oracle/archivelog/book/1_4917_896605872.dbf
5458 1 4918 A 2022-12-14 09:39:42
Name: /u01/app/oracle/archivelog/book/1_4918_896605872.dbf
5459 1 4919 A 2022-12-14 09:39:45
Name: /u01/app/oracle/archivelog/book/1_4919_896605872.dbf
5460 1 4920 A 2022-12-14 09:39:48
Name: /u01/app/oracle/archivelog/book/1_4920_896605872.dbf
5461 1 4921 A 2022-12-14 09:39:51
Name: /u01/app/oracle/archivelog/book/1_4921_896605872.dbf
5462 1 4922 A 2022-12-14 09:39:54
Name: /u01/app/oracle/archivelog/book/1_4922_896605872.dbf
5463 1 4923 A 2022-12-14 09:39:57
Name: /u01/app/oracle/archivelog/book/1_4923_896605872.dbf
5464 1 4924 A 2022-12-14 09:40:00
Name: /u01/app/oracle/archivelog/book/1_4924_896605872.dbf
5465 1 4925 A 2022-12-14 09:40:03
Name: /u01/app/oracle/archivelog/book/1_4925_896605872.dbf
5466 1 4926 A 2022-12-14 09:40:06
Name: /u01/app/oracle/archivelog/book/1_4926_896605872.dbf
5467 1 4927 A 2022-12-14 09:40:09
Name: /u01/app/oracle/archivelog/book/1_4927_896605872.dbf
5468 1 4928 A 2022-12-14 09:40:12
Name: /u01/app/oracle/archivelog/book/1_4928_896605872.dbf
5469 1 4929 A 2022-12-14 09:40:15
Name: /u01/app/oracle/archivelog/book/1_4929_896605872.dbf
5470 1 4930 A 2022-12-14 09:40:18
Name: /u01/app/oracle/archivelog/book/1_4930_896605872.dbf
5471 1 4931 A 2022-12-14 09:40:21
Name: /u01/app/oracle/archivelog/book/1_4931_896605872.dbf
5472 1 4932 A 2022-12-14 09:40:24
Name: /u01/app/oracle/archivelog/book/1_4932_896605872.dbf
5473 1 4933 A 2022-12-14 09:40:27
Name: /u01/app/oracle/archivelog/book/1_4933_896605872.dbf
5474 1 4934 A 2022-12-14 09:40:30
Name: /u01/app/oracle/archivelog/book/1_4934_896605872.dbf
5475 1 4935 A 2022-12-14 09:40:33
Name: /u01/app/oracle/archivelog/book/1_4935_896605872.dbf
5476 1 4936 A 2022-12-14 09:40:36
Name: /u01/app/oracle/archivelog/book/1_4936_896605872.dbf
5477 1 4937 A 2022-12-14 09:40:39
Name: /u01/app/oracle/archivelog/book/1_4937_896605872.dbf
5478 1 4938 A 2022-12-14 09:40:42
Name: /u01/app/oracle/archivelog/book/1_4938_896605872.dbf
5479 1 4939 A 2022-12-14 09:40:45
Name: /u01/app/oracle/archivelog/book/1_4939_896605872.dbf
5480 1 4940 A 2022-12-14 09:40:48
Name: /u01/app/oracle/archivelog/book/1_4940_896605872.dbf
5481 1 4941 A 2022-12-14 09:40:51
Name: /u01/app/oracle/archivelog/book/1_4941_896605872.dbf
5482 1 4942 A 2022-12-14 09:40:54
Name: /u01/app/oracle/archivelog/book/1_4942_896605872.dbf
5483 1 4943 A 2022-12-14 09:40:57
Name: /u01/app/oracle/archivelog/book/1_4943_896605872.dbf
5484 1 4944 A 2022-12-14 09:41:00
Name: /u01/app/oracle/archivelog/book/1_4944_896605872.dbf
5485 1 4945 A 2022-12-14 09:41:03
Name: /u01/app/oracle/archivelog/book/1_4945_896605872.dbf
5486 1 4946 A 2022-12-14 09:41:06
Name: /u01/app/oracle/archivelog/book/1_4946_896605872.dbf
5487 1 4947 A 2022-12-14 09:41:09
Name: /u01/app/oracle/archivelog/book/1_4947_896605872.dbf
5488 1 4948 A 2022-12-14 09:41:12
Name: /u01/app/oracle/archivelog/book/1_4948_896605872.dbf
5489 1 4949 A 2022-12-14 09:41:15
Name: /u01/app/oracle/archivelog/book/1_4949_896605872.dbf
5490 1 4950 A 2022-12-14 09:41:18
Name: /u01/app/oracle/archivelog/book/1_4950_896605872.dbf
5491 1 4951 A 2022-12-14 09:41:21
Name: /u01/app/oracle/archivelog/book/1_4951_896605872.dbf
5492 1 4952 A 2022-12-14 09:41:24
Name: /u01/app/oracle/archivelog/book/1_4952_896605872.dbf
5493 1 4953 A 2022-12-14 09:41:27
Name: /u01/app/oracle/archivelog/book/1_4953_896605872.dbf
5494 1 4954 A 2022-12-14 09:41:30
Name: /u01/app/oracle/archivelog/book/1_4954_896605872.dbf
5495 1 4955 A 2022-12-14 09:41:33
Name: /u01/app/oracle/archivelog/book/1_4955_896605872.dbf
5496 1 4956 A 2022-12-14 09:41:36
Name: /u01/app/oracle/archivelog/book/1_4956_896605872.dbf
5497 1 4957 A 2022-12-14 09:41:39
Name: /u01/app/oracle/archivelog/book/1_4957_896605872.dbf
5498 1 4958 A 2022-12-14 09:41:42
Name: /u01/app/oracle/archivelog/book/1_4958_896605872.dbf
5499 1 4959 A 2022-12-14 09:41:45
Name: /u01/app/oracle/archivelog/book/1_4959_896605872.dbf
5500 1 4960 A 2022-12-14 09:41:48
Name: /u01/app/oracle/archivelog/book/1_4960_896605872.dbf
5501 1 4961 A 2022-12-14 09:41:51
Name: /u01/app/oracle/archivelog/book/1_4961_896605872.dbf
5502 1 4962 A 2022-12-14 09:41:54
Name: /u01/app/oracle/archivelog/book/1_4962_896605872.dbf
5503 1 4963 A 2022-12-14 09:41:57
Name: /u01/app/oracle/archivelog/book/1_4963_896605872.dbf
$ du -cm /u01/app/oracle/archivelog/book/1_49*_896605872.dbf | tail -1
3010 total
--//接近3G
$ ls -lh /u01/app/oracle/archivelog/book/1_490[123456]_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:00 /u01/app/oracle/archivelog/book/1_4901_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:03 /u01/app/oracle/archivelog/book/1_4902_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:06 /u01/app/oracle/archivelog/book/1_4903_896605872.dbf
-rw-r----- 1 oracle oinstall 48M 2022-12-14 09:30:09 /u01/app/oracle/archivelog/book/1_4904_896605872.dbf
-rw-r----- 1 oracle oinstall 45M 2022-12-14 09:37:30 /u01/app/oracle/archivelog/book/1_4905_896605872.dbf
-rw-r----- 1 oracle oinstall 46M 2022-12-14 09:37:33 /u01/app/oracle/archivelog/book/1_4906_896605872.dbf
--//大概产生了3010-48*4-45-46 = 2727M.也就是不能大量使用with+materialize的方式.
