[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的方式.
内容来源于网络如有侵权请私信删除

文章来源: 博客园

原文链接: https://www.cnblogs.com/lfree/p/16981366.html

你还没有登录,请先登录注册
  • 还没有人评论,欢迎说说您的想法!