总结：物化视图统计信息陈旧导致job 执行时间过长
pro_db JOB执行连续超过2小时，数据库名是：pro_db；
JOB号是：355；时长为：37；执行内容是：月重测率查询预计算
问题：平时24小时执行完成，昨天40多小时才跑完
1、查看job对应的package和存储过程 MES_BS_RETEST_MONTH_STATISTICS.collect_month_retest_data
2、查看昨天执行时间较久的sql ,对应存储过程找到执行慢的sql
select sql_id,count(1) from v$active_session_history where sample_time > sysdate -2 group by sql_id having count(1) > 70000 order by 2 desc
SQL_ID COUNT(1)
------------- ----------
7a419ghx6t2dp 144133
8hd059g1z8aar 66613
select sql_text,sql_fulltext from v$sql where sql_id ='8hd059g1z8aar';
SQL语句：
for cur_op in (select t.opcode
from tblop t
where t.eattribute2 = 'Assemble')
loop
begin
insert into tblmonthretestbarcode
(orgid,
startdate,
enddate,
segname,
modelname,
pcb,
opcode,
eccode,
cuser,
cdate,
ctime,
muser,
mdate,
mtime)
WITH TMP AS
(select P.RCARD,
P.RCARDSEQ,
P.MOCODE,
p.segname,
m.modelname,
p.opcode,
p.eattribute8,
p.eattribute10
from tblonwip p, tblmitem m, tblmo2pcb pcb
where p.mdate >= V_BEGIN_DAY
and p.mdate < V_END_DAY
and p.itemcode = m.mitemcode
and p.orgid = m.orgid
and p.eattribute8 = pcb.pcb
and pcb.eattribute3 = '1'
and p.orgid = pcb.orgid
and p.orgid = p_orgid
and p.opcode = cur_op.opcode
and p.mocode > 0
and p.action in ('NG')
and exists (select 1
from tblonwip p1
where p1.eattribute8 = p.eattribute8
AND P1.OPCODE = P.OPCODE
and p.mocode > 0
and p.segname = p1.segname
and p1.action = 'GOOD'
AND p.mdate >= V_BEGIN_DAY
and p.mdate < V_END_DAY)
AND NOT EXISTS
(SELECT 1
FROM TBLTSREPAIREREPORT T
WHERE T.PCB = P.EATTRIBUTE8
AND T.FRMOPCODE = P.OPCODE
and t.tsstatus IN ('tsstatus_reflow', 'tsstatus_ts','tsstatus_repairing')
and t.tsdate >= V_BEGIN_DATE
and t.tsdate < V_END_DATE
))
select p_orgid,
V_BEGIN_DATE,
V_END_DATE,
t.segname,
t.modelname,
t.eattribute8,
t.opcode,
ecb.ecode,
v_user,
v_date,
v_time,
v_user,
v_date,
v_time
from tmp t, tblts ts, tbltserrorcode ecb
where t.rcard = ts.rcard(+)
and t.mocode = ts.mocode(+)
and t.rcardseq = ts.rcardseq(+)
and ts.tsid = ecb.tsid(+)
and not exists
(select 1
from tmp t1
where t1.eattribute8 = t.eattribute8
and t1.opcode = t.opcode
and t1.eattribute10 > t.eattribute10);
3、查看sql最近的执行时间情况和对应的hash_plan_value ，确实存在多个执行计划
SQL> @sql_exec_profile.sql
指定时间段（单位小时）内，sql的执行情况 ,time_per_exec 单位：s
BEGIN_TIME END_TIME SQL_ID EXEC_D GETS_D TIME_D ROWS_D TIME_PER_EXEC GETS_PER_EXEC ROWS_PER_EXEC
------------ ------------ ------------- --------------- ----------- --------- ---------- ------------- ------------- -------------
2018032504 2018032605 8hd059g1z8aar 129 ########### 82176 410519 637 230068454 3182
SQL> @sql_child_cursor_diff.sql
PLAN_HASH_VALUE EXECUTIONS AVG_ET_SECS AVG_BUFFER_GETS
--------------- ---------- ----------- ---------------
980628206 69 403.429 92127763
1748294907 32 526.673 69668744
3636756332 15 594.239 236501734
4152519531 13 2198.769 241550057
4、对比执行计划差异 ，差别在于对物化视图 TBLMITEM_B 的查询走全表或者索引范围扫描
Plan hash value: 980628206
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 34 | INSERT STATEMENT | | | | 6833K(100)| | | |
| 1 | 33 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | 20 | LOAD AS SELECT | | | | | | | |
| 3 | 19 | FILTER | | | | | | | |
| 4 | 15 | FILTER | | | | | | | |
| 5 | 14 | NESTED LOOPS SEMI | | 41 | 5371 | 6833K (1)| 22:46:37 | | |
| 6 | 9 | NESTED LOOPS | | 41 | 4469 | 6827K (1)| 22:45:32 | | |
| 7 | 6 | NESTED LOOPS | | 41 | 4018 | 6827K (1)| 22:45:31 | | |
| 8 | 3 | PARTITION HASH ALL | | 67 | 5360 | 6827K (1)| 22:45:29 | 1 | 32 |
| 9 | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TBLONWIP | 67 | 5360 | 6827K (1)| 22:45:29 | ROWID | ROWID |
| 10 | 1 | INDEX RANGE SCAN | IND_TBLONWIP_6 | 104M| | 435K (1)| 01:27:12 | 1 | 32 |
| 11 | 5 | TABLE ACCESS BY INDEX ROWID | TBLMO2PCB | 1 | 18 | 3 (0)| 00:00:01 | | |
| 12 | 4 | INDEX UNIQUE SCAN | TBLMO2PCB_IND_1 | 1 | | 2 (0)| 00:00:01 | | |
| 13 | 8 | MAT_VIEW ACCESS BY INDEX ROWID | TBLMITEM_B | 1 | 11 | 2 (0)| 00:00:01 | | |
| 14 | 7 | INDEX RANGE SCAN | PK_TBLMITEM_B | 1 | | 1 (0)| 00:00:01 | | |
| 15 | 13 | PARTITION RANGE ALL | | 192K| 4126K| 131 (0)| 00:00:02 | 1 | 56 |
| 16 | 12 | PARTITION LIST SINGLE | | 192K| 4126K| 131 (0)| 00:00:02 |KEY(AP)|KEY(AP)|
| 17 | 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TBLONWIP | 192K| 4126K| 131 (0)| 00:00:02 | KEY | KEY |
| 18 | 10 | INDEX RANGE SCAN | IND_TBLONWIP_3 | 19 | | 112 (0)| 00:00:02 | KEY | KEY |
| 19 | 18 | FILTER | | | | | | | |
| 20 | 17 | TABLE ACCESS BY INDEX ROWID | TBLTSREPAIREREPORT | 1 | 40 | 4 (0)| 00:00:01 | | |
| 21 | 16 | INDEX RANGE SCAN | IND_TBLTSREPAIREREPORT_3 | 1 | | 3 (0)| 00:00:01 | | |
| 22 | 32 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 23 | 31 | HASH JOIN RIGHT ANTI | | 41 | 15580 | 293 (0)| 00:00:04 | | |
| 24 | 22 | VIEW | | 41 | 1312 | 2 (0)| 00:00:01 | | |
| 25 | 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9DCBD3_812A4378 | 41 | 2501 | 2 (0)| 00:00:01 | | |
| 26 | 30 | NESTED LOOPS OUTER | | 41 | 14268 | 291 (0)| 00:00:04 | | |
| 27 | 27 | NESTED LOOPS OUTER | | 41 | 11931 | 127 (0)| 00:00:02 | | |
| 28 | 24 | VIEW | | 41 | 8979 | 2 (0)| 00:00:01 | | |
| 29 | 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9DCBD3_812A4378 | 41 | 2501 | 2 (0)| 00:00:01 | | |
| 30 | 26 | TABLE ACCESS BY INDEX ROWID | TBLTS | 1 | 72 | 4 (0)| 00:00:01 | | |
| 31 | 25 | INDEX RANGE SCAN | IND_TBLTS_2 | 1 | | 3 (0)| 00:00:01 | | |
| 32 | 29 | TABLE ACCESS BY INDEX ROWID | TBLTSERRORCODE | 1 | 57 | 4 (0)| 00:00:01 | | |
| 33 | 28 | INDEX RANGE SCAN | IND_TBLTSERRORCODE_2 | 1 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4152519531
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | 34 | INSERT STATEMENT | | | | 7527K(100)| | | |
| 1 | 33 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | 20 | LOAD AS SELECT | | | | | | | |
| 3 | 19 | FILTER | | | | | | | |
| 4 | 15 | FILTER | | | f| | | | |
| 5 | 14 | NESTED LOOPS SEMI | | 4824 | 617K| 7483K (1)| 24:56:44 | | |
| 6 | 9 | HASH JOIN | | 4824 | 513K| 6851K (1)| 22:50:19 | | |
| 7 | 7 | NESTED LOOPS | | 4824 | 461K| 6851K (1)| 22:50:13 | | |
| 8 | 5 | NESTED LOOPS | | 7901 | 461K| 6851K (1)| 22:50:13 | | |
| 9 | 3 | PARTITION HASH ALL | | 7901 | 617K| 6827K (1)| 22:45:29 |1 | 32 |
| 10 | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TBLONWIP | 7901 | 617K| 6827K (1)| 22:45:29 | ROWID | ROWID |
| 11 | 1 | INDEX RANGE SCAN | IND_TBLONWIP_6 | 104M| | 435K (1)| 01:27:12 |1 | 32 |
| 12 | 4 | INDEX UNIQUE SCAN | TBLMO2PCB_IND_1 | 1 | | 2 (0)| 00:00:01 | | |
| 13 | 6 | TABLE ACCESS BY INDEX ROWID | TBLMO2PCB | 1 | 18 | 3 (0)| 00:00:01 | | |
| 14 | 8 | MAT_VIEW ACCESS FULL | TBLMITEM_B | 60039 | 644K| 486 (1)| 00:00:06 | | |
| 15 | 13 | PARTITION RANGE ALL | | 22M| 475M| 131 (0)| 00:00:02 |1 | 56 |
| 16 | 12 | PARTITION LIST SINGLE | | 22M| 475M| 131 (0)| 00:00:02 |KEY(AP)|KEY(AP)|
| 17 | 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TBLONWIP | 22M| 475M| 131 (0)| 00:00:02 | KEY | KEY |
| 18 | 10 | INDEX RANGE SCAN | IND_TBLONWIP_3 | 19 | | 112 (0)| 00:00:02 | KEY | KEY |
| 19 | 18 | FILTER | | | | | | | |
| 20 | 17 | TABLE ACCESS BY INDEX ROWID | TBLTSREPAIREREPORT | 1 | 40 | 4 (0)| 00:00:01 | | |
| 21 | 16 | INDEX RANGE SCAN | IND_TBLTSREPAIREREPORT_3 | 1 | | 3 (0)| 00:00:01 | | |
| 22 | 32 | LOAD TABLE CONVENTIONAL | | | | | | | |
| 23 | 31 | HASH JOIN RIGHT ANTI | | 4824 | 1790K| 34019 (1)| 00:06:49 | | |
| 24 | 22 | VIEW | | 4824 | 150K| 25 (0)| 00:00:01 | | |
| 25 | 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9DCBE8_812A4378 | 4824 | 287K| 25 (0)| 00:00:01 | | |
| 26 | 30 | NESTED LOOPS OUTER | | 4824 | 1639K| 33994 (1)| 00:06:48 | | |
| 27 | 27 | NESTED LOOPS OUTER | | 4824 | 1370K| 14694 (1)| 00:02:57 | | |
| 28 | 24 | VIEW | | 4824 | 1031K| 25 (0)| 00:00:01 | | |
| 29 | 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9DCBE8_812A4378 | 4824 | 287K| 25 (0)| 00:00:01 | | |
| 30 | 26 | TABLE ACCESS BY INDEX ROWID | TBLTS | 1 | 72 | 4 (0)| 00:00:01 | | |
| 31 | 25 | INDEX RANGE SCAN | IND_TBLTS_2 | 1 | | 3 (0)| 00:00:01 | | |
| 32 | 29 | TABLE ACCESS BY INDEX ROWID | TBLTSERRORCODE | 1 | 57 | 4 (0)| 00:00:01 | | |
| 33 | 28 | INDEX RANGE SCAN | IND_TBLTSERRORCODE_2 | 1 | | 3 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
5、查看物化视图 tblmitem_b的统计信息和索引列 ，其中列 MITEMCODE 与表tblonwip的mitemcode列对应，该列为sequence生成的，递增的。
SQL>select table_name,num_rows,last_analyzed from dba_tables where table_name ='TBLMITEM_B';
TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
TBLMITEM_B 60044 2018-03-24 14:07:17
SQL>select index_name,column_name from dba_ind_columns where index_name ='PK_TBLMITEM_B';
INDEX_NAME COLUMN_NAME
------------------------------ --------------------
PK_TBLMITEM_B ORGID
PK_TBLMITEM_B MITEMCODE
6、查看物化视图 tblmitem_b 的刷新方式,为 fast on demand 每分钟增量刷新一次 ，存在统计信息陈旧的问题，即mitemcode列的实际值可能大于统计信息中索引列存储的最大值，导致CBO走错执行计划
7、使用sql_profile固定执行计划
@coe_xfr_sql_profile.sql
@coe_xfr_sql_profile_8hd059g1z8aar_980628206.sql
35
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
