select * from v$segment_statistics where OBJ# IN('56308','56307') order by VALUE desc;
select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
select * from v$session_event a,(select SID from v$mystat where rownum<=1) b
where a.SID=b.sid order by TIME_WAITED desc;
测试目的
1、测试数据，在进行插入INSERT的时候（常规）有索引和没有索引的对象的统计信息，会话的统计信息，会话的等待事件
测试1
create table test_a
(it int,
a varchar2(20),
b varchar2(20),
c varchar2(20),
d varchar2(20),
z varchar2(20),
x varchar2(20),
m varchar2(20));
create table test_b
(it int,
a varchar2(20),
b varchar2(20),
c varchar2(20),
d varchar2(20),
z varchar2(20),
x varchar2(20),
m varchar2(20));
create index test_b_i
on test_b(it);
建立表然后查看其OBJECT_ID
SQL> select object_name,object_id from dba_objects where object_name in ('TEST_A','TEST_B','TEST_B_I');
OBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------- ----------
TEST_B_I 56399
TEST_B 56398
TEST_A 56397
通过
select * from v$segment_statistics where OBJ# IN('56399','56398','56397') order by VALUE desc;
可以查看基本是没有什么统计信息的。
开启2个会话
会话1 sid
SQL> select * from v$mystat where rownum<=1;
SID STATISTIC# VALUE
---------- ---------- ----------
311 0 1
会话2 sid
SQL> select * from v$mystat where rownum<=1;
SID STATISTIC# VALUE
---------- ---------- ----------
318 0 1
会话1 311 进行插入数据，注意表TEST_A没有索引
declare
i number(10);
begin
for i in 1..4000000
loop
insert into test_a
values(i,'aa','bb','cc','dd','zz','xx','mm');
end loop;
end;
完成后，查看本表的大小为大约144MB
我注意到我的电脑上执行了500秒，当然我自己的笔记本我查看我的ORCALE I/O大约读写总共每秒8M，这和服务器没法比，但是我们做测试足够了，
我们查看本会话的等待信息
查看会话的UNDO REDO 物理 逻辑读取
select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
311 session logical reads 4206665
311 physical read total IO requests 310
311 physical read total bytes 4833280
311 physical write total IO requests 294
311 physical write total multi block requests 140
311 physical write total bytes 148979712 ---表的大小
311 physical reads 2
311 physical reads cache 2
311 physical read IO requests 2
311 physical read bytes 16384
311 redo synch writes 1
311 redo entries 4057334
311 redo size 1139031256 --生成的日志量达到1G
311 redo buffer allocation retries 50
311 redo log space requests 48
311 redo log space wait time 314
311 redo ordering marks 36035
311 redo subscn max counts 50228
311 undo change vector size 255930480--生成的UNDO达到240M
311 data blocks consistent reads - undo records applied 88
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
311 no work - consistent read gets 38616
311 rollbacks only - consistent read gets 88
311 rollback changes - undo records applied 28
SQL> select OBJECT_NAME, STATISTIC_NAME ,value from v$segment_statistics where OBJ# IN('56397') order by VALUE desc;
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ---------------------------------------------------------------- ----------
TEST_A space allocated 150994944 -表的大小144MB
TEST_A logical reads 4081600 400W行收
TEST_A db block changes 4078592 400W行数据
TEST_A physical writes 17543 *8K=144M
而在等待事件方面
SID EVENT TIME_WAITED_MICRO
---------- ---------------------------------------------------
311 SQL*Net message from client 407159322
311 control file sequential read 7920935
311 Data file init write 4506108
311 log file switch completion 3157821
311 db file sequential read 1668481
311 events in waitclass Other 1474582
311 log buffer space 322594
311 control file parallel write 39322
311 db file single write 15686
311 latch: shared pool 7792
311 undo segment extension 683
311 SQL*Net message to client 70
311 buffer busy waits 567
311 latch: cache buffers chains 5
视乎没有过多的等待，
再次查看时间模型
SQL> select * from v$sess_time_model where sid=311 order by value desc;
SID STAT_ID STAT_NAME VALUE
---------- ---------- ---------------------------------------------------------------- ----------
311 3649082374 DB time 499403928
311 2821698184 sql execute elapsed time 499158001
311 2748282437 DB CPU 470820708 --耗费在这里
311 2643905994 PL/SQL execution elapsed time 15402111
311 1431595225 parse time elapsed 208995
311 372226525 hard parse elapsed time 191950
311 1311180441 PL/SQL compilation elapsed time 31235
311 1990024365 connection management call elapsed time 22499
311 1159091985 repeated bind elapsed time 1142
原来ORACLE认为事件都花费到了DB CPU，在这里可以看到insert语句实际生成的日志量很大，主要耗时主要在于写日志和UNDO生成，所以减少日志生成量是行之有效的办法
这里如果表144M+240M（undo)+1100m(LOGFILE)，而我的硬盘WIDNOWS资源管理器显示I/O写入才4M-5M每秒，算一下耗费为371秒，当然还有一些其他的等待和物理逻辑读读取
肯定有一些，这样如果我们降低了1100M的logfile的写那么速度将大大提高，当然没有什么办法，除非直接路径读，这仅仅在NOLOGING的APPEND才有效。
会话2 318 进行插入数据，注意表TEST_B有索引
SQL> select sum(blocks)*8/1024 from dba_extents where segment_name='TEST_B';
SUM(BLOCKS)*8/1024
------------------
144
SQL> select sum(blocks)*8/1024 from dba_extents where segment_name='TEST_B_I';
SUM(BLOCKS)*8/1024
------------------
72
完成后表依然是144M索引72M
执行847秒
SQL> select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
318 session logical reads 16118622 --是TABLE和INDEX逻辑读的和，下面能够看到
318 physical read total IO requests 493
318 physical read total bytes 7643136
318 physical write total IO requests 462
318 physical write total multi block requests 220
318 physical write total bytes 234110976 --144+72（表和索引大小的和）
318 physical reads 9
318 physical reads cache 9
318 physical read IO requests 9
318 physical read bytes 73728
318 redo synch writes 1
318 redo entries 8144541
318 redo size 2163473416 --日志量 比不加索引基本加了一倍 2G
318 redo buffer allocation retries 100
318 redo log space requests 95
318 redo log space wait time 580
318 redo ordering marks 96246
318 redo subscn max counts 117359
318 undo change vector size 659101200 -UNDO量 600M
318 data blocks consistent reads - undo records applied 167
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
318 no work - consistent read gets 60478
318 rollbacks only - consistent read gets 167
318 rollback changes - undo records applied 116
SQL> select OBJECT_NAME, STATISTIC_NAME ,value from v$segment_statistics where OBJ# IN('56398','56399') order by VALUE desc;
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ---------------------------------------------------------------- ----------
TEST_B space allocated 150994944 --144M
TEST_B_I space allocated 75497472 ---72M
TEST_B_I space used 59663936
TEST_B_I logical reads 11730176 ---每次访问IDNEX 2次I/O 一次访问索引找到ROWID一次通过ROWID找到块
TEST_B_I db block changes 4095232
TEST_B logical reads 4084896 -- 行数
TEST_B db block changes 4077216
TEST_B physical writes 17598
TEST_B_I physical writes 8626
TEST_B_I physical reads 2
TEST_B physical reads 1
SQL> select *
2 from v$session_event a
3 where a.SID = 318
4 order by TIME_WAITED desc;
SID EVENT TIME_WAITED_MICRO WAIT_CLASS
---------- --------------------------------------------------------------------------------- ----------------------------------------------------------------
318 SQL*Net message from client 1319113152 Idle
318 control file sequential read 7206186 System I/O
318 log file switch completion 5741095 Configuration
318 Data file init write 4569783 User I/O
318 db file sequential read 2524870 User I/O
318 events in waitclass Other 1130755 Other
318 log buffer space 390022 Configuration
318 control file parallel write 47887 System I/O
318 db file single write 15586 User I/O
318 undo segment extension 2 Configuration
318 latch: shared pool 115 Concurrency
318 SQL*Net message to client 54 Network
318 latch: cache buffers chains 2 Concurrency
318 buffer busy waits 63 Concurrency
SQL> select * from v$sess_time_model where sid=318 order by value desc;
SID STAT_ID STAT_NAME VALUE
---------- ---------- ---------------------------------------------------------------- ----------
318 3649082374 DB time 847221759
318 2821698184 sql execute elapsed time 847104972
318 2748282437 DB CPU 815738287
318 2643905994 PL/SQL execution elapsed time 15512073
318 1431595225 pars e time elapsed 671462
318 372226525 hard parse elapsed time 649587
318 1311180441 PL/SQL compilation elapsed time 100303
318 1990024365 connection management call elapsed time 7010
318 1159091985 repeated bind elapsed time 714
同样判断这里，大量的时间实际上是耗在写UNDO和LOGFILE，算一下
SQL> select (144+72+600+2200)/4 from dual;
(144+72+600+2200)/4
-------------------
754
大约754秒，而整个语句也才执行了850秒所以INSERT 大量的数据一定的耗时在LOGFILE,UNDO的生成，特别厉害。这也是为什么NOLOGGING+APPEND的insert会那么块的原因。因为没LOGFILE和UNDO
下面是INSERT APPEND的信息你能看到米有UNDO和LOGFILE基本
SQL> select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
2 where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
317 session logical reads 91460
317 physical read total IO requests 1482
317 physical read total multi block requests 1121
317 physical read total bytes 148881408
317 physical write total IO requests 856
317 physical write total multi block requests 690
317 physical write total bytes 293068800
317 physical reads 17552
317 physical reads cache 17552
317 physical read IO requests 1157
317 physical read bytes 143785984
317 physical writes 17565
317 physical writes direct 17565
317 physical write IO requests 550
317 physical write bytes 143892480
317 physical writes non checkpoint 17565
317 redo synch writes 1
317 physical reads cache prefetch 16395
317 redo entries 18405
317 redo size 1342764
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
317 redo ordering marks 28
317 redo subscn max counts 17520
317 undo change vector size 11208
317 data blocks consistent reads - undo records applied 17
317 no work - consistent read gets 37812
317 cleanouts only - consistent read gets 17488
317 rollbacks only - consistent read gets 17
317 rollback changes - undo records applied 28
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-773627/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/7728585/viewspace-773627/
扫一扫
2019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
