m6米乐安卓版下载-米乐app官网下载
暂无图片
3
暂无图片
暂无图片
暂无图片

insert 引起的 db file sequential read -m6米乐安卓版下载

原创 布衣 2023-11-29
169

背景

  最近有开发反馈每个月1号insert表比平时要慢20-30分钟左右。然后取操作时间段的ash报告发现正好是相关insert 引起的db file sequential read等待事件:
image.png
  等待事件发生的io基本都是索引表空间
image.png
  考虑到影响到insert效率的一般均为索引,结合db file sequential read,基本可以诊断为数据库在将索引从磁盘读取到sga时导致的慢。

介绍:db file sequential read

  db file sequential read是一种io读请求相关的等待。与”db file scattered read“不同,因为”sequential read“是将数据读到连续的内存(注意:这里指的是读到相连的内存,不是说读取的是连续的数据块。同时一次”scattered read“可以读多个块,将他们分散到sga的不同buffer)。这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。

  一次”sequential read“通常是单块读,尽管可能看到对于多个块的”sequential read“。这种等待也可能在数据文件头读取中看到(p2=1表明是读取文件头)。

根据生产情况复现:insert 引起 db file sequential read 示例:

-- 创建测试表tmp 保留源表(sys_p14220)分区数据 sql> create table tmp_sys_p14220 as select * from t1 partition (sys_p14220); table created. -- 删除源表sys_p14220 分区数据 sql> alter table t1 truncate partition (sys_p14220) update indexes; table truncated. -- 打开10046 会话跟踪: sql> alter session set events '10046 trace name context forever,level 12'; session altered. -- 往表中插入大量数据 sql> insert into t1 select * from tmp_sys_p14220 where create_date>to_date('20231015','yyyymmdd') and create_date<to_date('20231016','yyyymmdd'); 794497 rows created. sql> commit; commit complete. -- 插入完成后,关闭10046 sql> alter session set events '10046 trace name context off'; session altered. -- 查看trace 文件目录 : sql> select tracefile from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat)); tracefile -------------------------------------------------------------------------------- /u01/oracle/diag/rdbms/two/two/trace/two_ora_3234.trc -- 查看trace 文件 : [root@twodb ~]#cat /u01/oracle/diag/rdbms/two/two/trace/two_ora_3234.trc | less ===================== parsing in cursor #139756929115448 len=79 dep=0 uid=0 oct=2 lid=0 tim=1700483278294940 hv=3792282112 ad='513cc04a0' sqlid='2aj9hjzj0m8h0' insert into t1 select * from tmp_sys_p14220 where create_date>to_date('20231015','yyyymmdd') and create_date<to_date('20231016','yyyymmdd'); end of stmt parse #139756929115448:c=76989,e=346233,p=154,cr=198,cu=0,mis=1,r=0,dep=0,og=1,plh=3131486614,tim=1700483278294939 wait #139756929115448: nam='db file scattered read' ela= 6579 file#=6 block#=203 blocks=5 obj#=77121 tim=1700483278301925 wait #139756929115448: nam='db file sequential read' ela= 14 file#=6 block#=27920 blocks=1 obj#=76912 tim=1700483278302668 wait #139756929115448: nam='db file sequential read' ela= 42 file#=6 block#=27919 blocks=1 obj#=76912 tim=1700483278302802 wait #139756929115448: nam='db file sequential read' ela= 16 file#=6 block#=27904 blocks=1 obj#=76912 tim=1700483278303077 wait #139756929115448: nam='db file scattered read' ela= 2241 file#=8 block#=128 blocks=8 obj#=77121 tim=1700483278313280 wait #139756929115448: nam='db file scattered read' ela= 25 file#=6 block#=209 blocks=7 obj#=77121 tim=1700483278322700 ===================== sql> select object_name from all_objects where object_id='77121'; object_name ------------------------------ tmp_sys_p14220 sql> select object_name from all_objects where object_id='76912'; object_name ------------------------------ t1 -- 格式化trace 文件 : [root@twodb ~]#tkprof /u01/oracle/diag/rdbms/two/two/trace/two_ora_3234.trc two_ora_3234.sql -- 查看格式化trace : [root@twodb ~]#cat two_ora_3234.sql ******************************************************************************** sql id: 28uudbqtq2949 plan hash: 3131486614 insert into t1 select * from tmp_sys_p14220 where create_date>to_date('20231015','yyyymmdd') and create_date<to_date('20231016','yyyymmdd'); call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- parse 1 0.00 0.00 0 0 0 0 execute 1 35.34 46.81 672898 1484472 3205992 794497 fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 35.34 46.81 672898 1484472 3205992 794497 misses in library cache during parse: 1 optimizer mode: all_rows parsing user id: sys number of plan statistics captured: 1 rows (1st) rows (avg) rows (max) row source operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 load table conventional (cr=1484760 pr=672898 pw=0 time=46826365 us) 794497 794497 794497 table access full tmp_sys_p14220(cr=1374956 pr=672881 pw=0 time=7750080 us cost=374455 size=312265 116 card=778716) elapsed times include waiting on following events: event waited on times max. wait total waited ---------------------------------------- waited ---------- ------------ disk file operations i/o 5 0.00 0.00 db file scattered read 5734 0.12 5.56 db file sequential read 447 0.01 0.12 log buffer space 49 1.11 6.74 log file switch completion 4 0.41 0.92 sql*net message to client 1 0.00 0.00 sql*net message from client 1 0.00 0.00 ********************************************************************************

解决思路:

1、删除表中没有必要的索引;

-- 删除无用主键索引: -- 删除主键约束: sql> alter table t1 drop constraint pk_t1_id; -- 删除索引 sql> drop index pk_t1_id;

2、卸载表中无用的历史数据,并通过在线重建索引的方法整理索引碎片,使索引尽量的小从而提高加载索引的效率,减少io;

-- 分区索引拼接重建sql select 'alter index '|| index_name || ' rebuild partition '|| partition_name || ' online tablespace 表名称;' from user_ind_partitions where index_name in ('索引名称');

3、尝试做索引保持(前提是db_keep_cache_size是够大)示例如下:

sql> show parameter db_keep_cache_size name type value ------------------------------------ ----------- -------- db_keep_cache_size big integer 200m -- 分区索引 sql> alter index two.idx01 modify default attributes storage (buffer_pool keep); 索引已更改。 -- 查看 sql> select index_name,buffer_pool from dba_indexes where index_name='idx01'; index_name buffer_ ------------------------------ ------- idx01 -- 普通索引 sql>alter index two.idx1 storage ( buffer_pool keep); 索引已更改。 sql> select index_name,buffer_pool from dba_indexes where buffer_pool='keep'; index_name buffer_ ------------------------------ ------- idx1 keep
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
z
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论

网站地图