今天我要做一个SQL调优，监控该SQL， 利用ASH 监控 该SQL是在sid=4848 上面跑的db file sequential read等待事件有3个参数：file＃，first block＃，和block数量。这个等待事件有3个参数P1，P2，P3，其中P1代表Oracle要读取的文件的绝对文件号，P2代表Oracle从这个文件中开始读取的起始数据块号，P3代表读取的BLOCK数量，通常这个值为1，表明是道单个BLOCK被读取。 SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# 2 from v$active_session_history ash, v$event_name enm 3 where ash.event#=enm.event# 4 and SESSION_ID=4848;SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- -------------- 4848 db file sequential read 87 409499 1 0 58744541 87 409499 4848 db file sequential read 674 178953 1 0 58744541 674 178953 4848 db file sequential read 409 491921 1 0 58744541 409 491921 4848 db file sequential read 169 310289 1 0 58744541 169 310289 4848 db file sequential read 21 811534 1 0 58744541 21 811534 4848 db file sequential read 12 494321 1 0 58744541 12 494321 4848 db file sequential read 571 359814 1 0 58744541 571 359814 4848 db file sequential read 18 782340 1 0 58744541 18 782340 4848 db file sequential read 87 409073 1 0 58744541 87 409073 4848 db file sequential read 107 362350 1 0 58744545 107 362350 4848 db file sequential read 674 179953 1 0 58744545 674 179953 4848 db file sequential read 14 562832 1 0 58744545 14 562832 4848 db file sequential read 674 170372 1 0 58744545 674 170372 4848 db file sequential read 15 434867 1 0 58744545 15 434867 4848 db file sequential read 674 180111 1 0 58744545 674 180111 4848 db file sequential read 258 336128 1 0 58744545 258 336128 4848 gc cr grant 2-way 206 1696993 1 0 58744545 206 1696993 4848 gc cr grant 2-way 87 412582 1 0 58744545 87 412582 4848 db file sequential read 15 434831 1 0 58744545 15 434831 4848 db file sequential read 677 175793 1 0 58744545 677 175793 4848 db file sequential read 571 360776 1 0 58744545 571 360776 4848 db file sequential read 250 115741 1 0 58988767 250 115741 4848 db file sequential read 247 337993 1 0 58744545 247 337993 4848 db file sequential read 169 315606 1 0 58744549 169 315606 4848 db file sequential read 246 411238 1 0 58744549 246 411238 4848 db file sequential read 258 341515 1 0 58744549 258 341515 4848 db file sequential read 258 341748 1 0 58744549 258 341748 4848 db file sequential read 17 409377 1 0 58744549 17 409377 4848 db file sequential read 674 171523 1 0 58744549 674 171523 4848 db file sequential read 409 788005 1 0 58744549 409 788005 发现很多都是 db file sequential read等待事件 SQL> select owner,object_name,object_type from dba_objects where object_id=58744549;OWNER OBJECT_NAME OBJECT_TYPE-------------------- ---------------------------------------- ------------------------------ADWU SHPMT_GDF_HIST_FCT_NEW TABLE SUBPARTITION SQL> select owner,object_name,object_type from dba_objects where object_id=58744541;OWNER OBJECT_NAME OBJECT_TYPE-------------------- ---------------------------------------- ------------------------------ADWU SHPMT_GDF_HIST_FCT_NEW TABLE SUBPARTITION 但是这个等待事件不是 发生在index 上面，而是 发生在 table上！！！也许你觉得 用object_id定位不准确，那么我用 file#,block#定位，同样发生在 table上SQL> set lines 200SQL> set pages 100SQL> set timi onSQL> col owner format a30SQL> col segment_name format a40SQL> col segment_type format a30SQL> select owner,segment_name,segment_type from dba_extents 2 where file_id=87 and 425778 between block_id and block_id+blocks-1;OWNER SEGMENT_NAME SEGMENT_TYPE------------------------------ ---------------------------------------- ------------------------------ADWU SHPMT_GDF_HIST_FCT_NEW TABLE SUBPARTITION 为什么db file sequential read 发生在 table上呢？怀疑是行迁移/行连接(具体请看Metalink)Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows) [ID 554366.1] SQL> select c.sid,c.username,b.name,a.value,trunc(a.value/(select sum(value) from v$sesstat a,v$statname b 2 where a.statistic#=b.statistic# and b.name='table fetch continued row'),4)*100 ||'%' pct_used 3 from v$sesstat a, v$statname b,v$session c where a.statistic# =b.statistic# and b.name='table fetch continued row' 4 and a.sid=c.sid and c.sid=4848; SID USERNAME NAME VALUE PCT_USED---------- ------------------------------ ------------------------------ ---------- ----------------------------------------- 4848 BW9518 table fetch continued row 8 .04% 更加让我怀疑是行迁移/行连接那么现在Analyze表 analyze table adwu.SHPMT_GDF_HIST_FCT_NEW compute statistics;由于表很大(8亿多数据)。。。等Analyze完了再更新博客。。。 SQL> select s.inst_id, s.sid, s.serial#, s.event, 2 q.sql_text 3 from gv$session s, gv$sql q, gv$session_longops l 4 where s.inst_id = q.inst_id(+) 5 and s.sql_id = q.sql_id(+) 6 and s.inst_id = l.inst_id(+) 7 and s.sid = l.sid(+) 8 and s.serial# = l.serial#(+) 9 and s.username like upper('BW9518') 10 group by s.inst_id, s.sid, s.serial#, s.username, s.logon_time, s.status, s.sql_id, s.machine, s.LAST_CALL_ET, s.event, q.sql_text, q.PLAN_HASH_VALUE 11 ; INST_ID SID SERIAL# EVENT SQL_TEXT---------- ---------- ---------- ---------------------------------------- -------------------------------------------------------------------------------- 1 4669 59 PX Deq: Execution Msg select s.inst_id, s.sid, s.serial#, s.event, q.sql_text from gv$sessio 1 4791 2987 db file sequential read analyze table adwu.SHPMT_GDF_HIST_FCT_NEW compute statistics 1 4817 3318 SQL*Net message from client select s.inst_id, s.sid, s.serial#, s.event, q.sql_text from gv$sessio 1 4820 2427 SQL*Net message from client 1 4826 2013 SQL*Net message from client 1 4848 1325 gc cr request SELECT i.initv_skid, f.prod_csu_type_code, i.time_perd_ski 2 4809 49553 db file sequential read SELECT i.initv_skid, f.prod_csu_type_code, i.ti 2 4859 10363 PX Deq: reap credit select s.inst_id, s.sid, s.serial#, s.event, q.sql_text from gv$sessio 3 4830 1778 PX Deq: reap credit select s.inst_id, s.sid, s.serial#, s.event, q.sql_text from gv$sessio 4 4827 4452 PX Deq: reap credit select s.inst_id, s.sid, s.serial#, s.event, q.sql_text from gv$sessio10 rows selected 更新---------SQL> select table_name,pct_free from ALL_TAB_PARTITIONS where table_name='SHPMT_GDF_HIST_FCT_NEW';TABLE_NAME PCT_FREE------------------------------ ----------SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 0SHPMT_GDF_HIST_FCT_NEW 039 rows selected这个表PCF_FREE=0，ANALYZE 已经没必要等了 现在已经明确问题了，表上面肯定有很多行迁移/连接，analyze 都给我来db file sequential read总结：对于 db file sequential read 表示单块读，出现该等待事件并不是 表示说 一定是读索引，也有可能是读表，如果有大量等待事件发生在表上面，那么很可能出现行迁移/连接。单块读:要么是行迁移,要么是UNDOpct_free=0 稍微update一下就产生hang迁移
0 0
