生产中采购管理系统的订单查询form查询缓慢；比如在查询2015000004采购版本时；结果窗口POXPOEPO要很长时间才能打开，有时甚至达半个小时；才会弹出结果集form界面。同时form在初次打开时最耗时间；随后再打开时，情况会有所好转；
同时在此form之前的POXPOVPO有时打开也很慢
通过trace及分析，发现以下几个问题
大量db file sequential read和gc current block 2-way
语句执行过程中产生大量的dbfile sequential read和gc current block 2-way其中时间主要消耗在获取结果集中；
********************************************************************************
SQL ID: 08jf13kjhjz2k PlanHash: 1858060575
SELECT /*+FIRST_ROWS */ 1
FROM
MTL_CONSUMPTION_TRANSACTIONS MCT,MTL_MATERIAL_TRANSACTIONS MMT WHERE
MMT.TRANSACTION_ID = MCT.TRANSACTION_ID ANDMMT.TRANSACTION_SOURCE_ID = :B2
AND MMT.INVENTORY_ITEM_ID = :B1 ANDNVL(MCT.CONSUMPTION_PROCESSED_FLAG,'N')
<> 'Y' AND ROWNUM < 2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- -------------------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 16 0.00 0.00 0 0 0 0
Fetch 15 1.83 196.36 21380 94847 0 14
------- ------ -------- ---------- ---------- -------------------- ----------
total 33 1.83 196.37 21380 94847 0 14
Misses in library cacheduring parse: 1
Misses in library cacheduring execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 44 (recursive depth: 1)
Number of plan statisticscaptured: 2
Rows (1st) Rows (avg) Rows(max) Row Source Operation
---------- -------------------- ---------------------------------------------------
0 0 1 COUNT STOPKEY (cr=28473 pr=136 pw=0 time=984748 us)
0 0 1 NESTED LOOPS (cr=28473 pr=136pw=0 time=984738 us cost=31 size=26 card=1)
0 0 1 NESTED LOOPS (cr=28472 pr=136pw=0 time=983910 us cost=31 size=26 card=1)
0 0 1 TABLE ACCESS BY INDEX ROWID MTL_MATERIAL_TRANSACTIONS (cr=28471 pr=136pw=0 time=982564 us cost=28 size=17 card=1)
140382 70652 140382 INDEX RANGE SCAN MTL_MATERIAL_TRANSACTIONS_N2(cr=469 pr=4 pw=0 time=42921 us cost=5 size=0 card=177)(object id 119955)
0 0 1 INDEX RANGE SCAN TRANSACTION_ID (cr=2 pr=0 pw=0 time=1343 us cost=2size=0 card=1)(object id 212844)
0 0 1 TABLE ACCESS BY INDEX ROWIDMTL_CONSUMPTION_TRANSACTIONS (cr=0 pr=0 pw=0 time=821 us cost=3 size=9 card=1)
Elapsed times includewaiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
library cache lock 5 0.00 0.00
library cache pin 5 0.00 0.00
Disk file operations I/O 153 0.00 0.00
db file sequential read 435010 0.58 4124.76
gc current block 2-way 3038 0.12 1.84
gc cr grant 2-way 11 0.00 0.00
latch: gc element 5 0.00 0.00
latch: object queue header operation 4 0.00 0.00
gc current block congested 4 0.00 0.00
********************************************************************************
2.相关数据表中数据量庞大
进一步检查查询所涉及的MTL_CONSUMPTION_TRANSACTIONS和MTL_MATERIAL_TRANSACTIONS中数据量巨大；其中MTL_CONSUMPTION_TRANSACTIONS表中数据量达3.7亿多条，仅一个统计条目的查询就消耗了7分多钟；而MTL_CONSUMPTION_TRANSACTIONS记录数也有1000多万条；
SQL> select count(*) fromMTL_CONSUMPTION_TRANSACTIONS;
COUNT(*)
----------
11683476
Executed in67.221 seconds
SQL> select count(*) fromMTL_MATERIAL_TRANSACTIONS;
COUNT(*)
----------
376463701
Executed in471.42 seconds
3.索引效率低，有大量回表产生
查询过程虽然走了索引，但是其索引效率很低，仅本次执行就花费了614秒。执行计划预估结果集大小与实际结果集大小偏差大；
SQL Text
------------------------------
SELECT /*+FIRST_ROWS */
1
FROMMTL_CONSUMPTION_TRANSACTIONS MCT, MTL_MATERIAL_TRANSACTIONS MMT
WHEREMMT.TRANSACTION_ID = MCT.TRANSACTION_ID
ANDMMT.TRANSACTION_SOURCE_ID = :B2
ANDMMT.INVENTORY_ITEM_ID = :B1
ANDNVL(MCT.CONSUMPTION_PROCESSED_FLAG, 'N') <> 'Y'
AND ROWNUM< 2
Global Information
------------------------------
Status : DONE (ALL ROWS)
InstanceID : 2
Session : APPS (2038:55095)
SQL ID : 08jf13kjhjz2k
SQLExecution ID : 33578527
ExecutionStarted : 03/22/2018 14:49:35
FirstRefresh Time : 03/22/2018 14:49:42
Last RefreshTime : 03/22/2018 14:59:49
Duration : 614s
Module/Action : e:PO:frm:POXPOEPO/PO/112_VMI_PO_WCBP_SUPER
Service : PROD
Program : frmweb@ebsapp (TNS V1-V3)
PLSQL EntryIds (Object/Subprogram) : 149550,1
PLSQLCurrent Ids (Object/Subprogram) : 149550,1
Fetch Calls : 1
Binds
=====================================
| Name | Position | Type | Value |
=====================================
| :B2 | 1 | NUMBER | 4 |
| :B1 | 2 | NUMBER | 7796 |
=====================================
Global Stats
============================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read |Read |
| Time(s) | Time(s) |Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
============================================================================
| 614 | 3.70 | 610 | 0.24 | 1 | 60921 | 51701 | 404MB |
============================================================================
SQL Plan Monitoring Details(Plan Hash Value=1858060575)
=============================================================================================
| Id | Operation | Name | Rows | Rows |
| | | | (Estim) |(Actual) |
=============================================================================================
| 0 | SELECT STATEMENT | | | |
| 1 | COUNT STOPKEY | | | |
| 2 | NESTED LOOPS | | 1 | |
| 3 | NESTED LOOPS | | 1| |
| 4 | TABLE ACCESS BY INDEX ROWID | MTL_MATERIAL_TRANSACTIONS | 1 | 0 |
| | | | | |
| 5 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N2| 177 | 141K |
| 6 | INDEX RANGE SCAN |TRANSACTION_ID | 1 | |
| 7 | TABLE ACCESS BY INDEX ROWID |MTL_CONSUMPTION_TRANSACTIONS | 1 | |
=============================================================================================
查看索引最近一次收集统计信息的时间，并不久远；
select last_analyzed from dba_indexes where index_name ='MTL_MATERIAL_TRANSACTIONS_N2'
LAST_ANALYZED
-------------
2018-03-18
通过以上分析建议有以下两种建议方案
方案一：修改索引，提高索引效率
通过上述分析可以看出性能主要产生在索引MTL_MATERIAL_TRANSACTIONS_N2，尽管统计信息并不过旧，但是此索引的执行效率还是不高，索引产生大量回表读，正是这个原因，造成查询效率低，执行时间长。
为避免大量回表的产生，建议把索引MTL_MATERIAL_TRANSACTIONS_N2由原来的（TRANSACTION_SOURCE_ID，ORGANIZATION_ID，TRANSACTION_DATE）的联合索引修改为由（TRANSACTION_SOURCE_ID，INVENTORY_ITEM_ID ，ORGANIZATION_ID，TRANSACTION_DATE）组成的联合索引；也就是在原来的联合索引中添加INVENTORY_ITEM_ID字段；
同时，为减少对生产系统中影响可以采取先创建新索引，再删除原索引，最后把新索引名变量为原索引的方式。具体过程如下：
1）创建新索引
创建新索引MTL_MATERIAL_TRANSACTIONS_N2_N，此过程在测试系统中执行时间为01:28:45.90。
CREATE INDEX MTL_MATERIAL_TRANSACTIONS_N2_N ON INV.MTL_MATERIAL_TRANSACTIONS (TRANSACTION_SOURCE_ID , INVENTORY_ITEM_ID , ORGANIZATION_ID , TRANSACTION_DATE )
PCTFREE 10INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS1 MAXEXTENTS 2147483645
PCTINCREASE0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOLDEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE APPS_TS_TX_IDX;
2）删除旧索引
此过程在测试环境执行大约用时10秒左右。
drop index MTL_MATERIAL_TRANSACTIONS_N2;
3）重命名索引
此过程在测试环境执行用时为：00:00:00.05
ALTER INDEX MTL_MATERIAL_TRANSACTIONS_N2_N RENAMETO MTL_MATERIAL_TRANSACTIONS_N2;
4）重建索引前后在测试系统中测试结果对比
通过对比可以发现，仅执行时间从优化前553s缩小到优化后.000116s，此sql执行效率得到提升。
优化前执行信息：
Global Information
------------------------------
Status : DONE (ALL ROWS)
InstanceID : 1
Session : APPS (2634:51725)
SQL ID : 08jf13kjhjz2k
SQLExecution ID : 16777594
ExecutionStarted : 03/26/2018 09:04:21
FirstRefresh Time : 03/26/2018 09:04:27
Last RefreshTime : 03/26/2018 09:13:34
Duration : 553s
Module/Action : e:PO:frm:POXPOEPO/PO/112_VMI_PO_WCBP_SUPER
Service : EBSUAT
Program : frmweb@ebsuat (TNS V1-V3)
PLSQL EntryIds (Object/Subprogram) : 149550,1
PLSQLCurrent Ids (Object/Subprogram) : 149550,1
FetchCalls : 1
Binds
==========================================
| Name | Position | Type | Value |
==========================================
| :B2 | 1 | NUMBER | 4 |
| :B1 | 2 | NUMBER | 7796 |
==========================================
Global Stats
============================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read |Read |
| Time(s) | Time(s) |Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
============================================================================
| 553 | 2.23 | 551 | 0.07 | 1 | 50287 | 49244 | 385MB |
============================================================================
SQL Plan Monitoring Details(Plan Hash Value=1858060575)
=============================================================================================
| Id | Operation | Name | Rows | Rows |
| | | | (Estim) |(Actual) |
=============================================================================================
| 0 | SELECT STATEMENT | | | |
| 1 | COUNT STOPKEY | | | |
| 2 | NESTED LOOPS | | 1 | |
| 3 | NESTED LOOPS | | 1 | |
| 4 | TABLE ACCESS BY INDEX ROWID | MTL_MATERIAL_TRANSACTIONS | 1 | 0 |
| | | | | |
| 5 | INDEX RANGE SCAN |MTL_MATERIAL_TRANSACTIONS_N2 | 31 | 125K |
| 6 | INDEX RANGE SCAN |TRANSACTION_ID | 1 | |
| 7 | TABLE ACCESS BY INDEX ROWID |MTL_CONSUMPTION_TRANSACTIONS | 1 | |
=============================================================================================
优化后执行信息
Global Information
------------------------------
Status : DONE (ALL ROWS)
InstanceID : 1
Session : APPS (1511:3327)
SQL ID : 12bfdjdzhmxc3
SQLExecution ID : 16777216
ExecutionStarted : 03/26/2018 17:40:31
First RefreshTime : 03/26/2018 17:40:31
Last RefreshTime : 03/26/2018 17:40:31
Duration : .000116s
Module/Action : SQL*Plus/-
Service : EBSUAT
Program : sqlplus@ebsuat (TNS V1-V3)
FetchCalls : 2
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.00| 0.00 | 0.00 | 2 | 9 |
=================================================
SQL Plan Monitoring Details(Plan Hash Value=79251601)
==============================================================================================
| Id | Operation | Name | Rows | Rows |
| | | | (Estim)|(Actual) |
==============================================================================================
| 0 | SELECT STATEMENT | | | 1 |
| 1 | COUNT STOPKEY | | | 1 |
| 2 | NESTED LOOPS | | 1 | 1 |
| 3 | NESTED LOOPS | | 1 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID | MTL_MATERIAL_TRANSACTIONS | 1 | 1 |
| 5 | INDEX RANGE SCAN |MTL_MATERIAL_TRANSACTIONS_N2_N | 1| 1 |
| 6 | INDEX RANGE SCAN |TRANSACTION_ID | 1 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID |MTL_CONSUMPTION_TRANSACTIONS | 1 | 1 |
=============================================================================================
方案二：对过期数据进行定时清理归档
此查询影响速度的根本原因是表中数据量庞大所至；所以长远解决方法可以对记录进行归档和清理；清理时注意做好备份；
清理方法可以参考oracle官方文档：
《INVTransaction Purge Which Inventory Purge Program Should Be Used To PurgeTransaction Data (文档 ID 299003.1)》
《InventoryPurging Programs Information (文档 ID 1351271.1)》
157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
