1. 首页
  2. IT资讯

INSERT的统计信息以及等待等

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://www.cxybcw.com/183308.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code