latch:cache buffers chains案例
今天一数据库生产大量的latch:cache buffer chains等待事件，且CPU 100%。通过ASH发现该时间段发现latch:cache buffer chains对应的p1为504403190311174992，且对语句为acsdn1wgy2fg7，代入p1转后后的16进制(p1raw)值到x$bh视图里查找出来的热块正好是该语句关联的表。因此最后通过优化此语句来解决此问题
Top User Events
| Event | Event Class | % Event | Avg Active Sessions |
|---|---|---|---|
| CPU + Wait for CPU | CPU | 46.09 | 26.00 |
| latch: cache buffers chains | Concurrency | 16.25 | 9.17 |
| db file sequential read | User I/O | 12.52 | 7.06 |
| gc cr grant 2-way | Cluster | 2.91 | 1.64 |
| gc buffer busy acquire | Cluster | 2.29 |
1.29 |
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|---|
| latch: cache buffers chains | 16.25 | "504403190311174992","155","0" | 0.16 | address | number | tries |
| db file sequential read | 12.54 | "1","37805","1" | 0.00 | file# | block# | blocks |
| gc buffer busy acquire | 2.29 | "188","5253","4" | 0.01 | file# | block# | class# |
| db file scattered read | 2.00 | "33","1226116","128" | 0.00 | file# | block# |
blocks |
Top SQL with Top Events
| SQL ID | Planhash | Sampled # of Executions | % Activity | Event | % Event | Top Row Source | % RwSrc | SQL Text |
|---|---|---|---|---|---|---|---|---|
| acsdn1wgy2fg7 | 3040716915 | 374 | 43.55 | CPU + Wait for CPU | 28.09 | INDEX - RANGE SCAN | 7.78 | select * from (select row_.*, ... |
|
acsdn1wgy2fg7
|
3040716915
|
374
|
43.5541768225127409705295812098382450698
|
latch: cache buffers chains | 15.44 | INDEX - RANGE SCAN | 5.64 |
| acsdn1wgy2fg7 | select * from (select row_.*, rownum rownum_ from ( SELECT T.REGION, T.REGION qryRegion, T.FLOW_ID flowId, decode(T.LOCK_STATUS, 'LOCKED', 'PROCESSING', T.FLOW_STATUS) flowStatus, T.RES_KIND_ID resKindId, T.ORG_LEVEL orgLevel, T.ORG_ID orgId, (SELECT ORG.DISPLAYNAME FROM PSI_ORGANIZATION ORG WHERE ORG.ORGID = T.ORG_ID) AS orgName, T.APPLY_CYCLE applyCycle, T.CYCLE_TYPE cycleType, T.APPLY_TYPE applyType, T.APPLY_CLASS applyClass, T.CHANNEL_TYPE channelType, T.PROJECT_ID projectId, T.IF_BASE ifBase, T.PARENT_FLOW_ID parentFlowId, T.LINK_MAN linkMan, T.CONTACT_PHONE contactPhone, TO_CHAR(T.RECEIVE_DATE, 'YYYY-MM-DD') receiveDate, TO_CHAR(T.RECEIVE_LAST_DATE, 'YYYY-MM-DD') receiveLastDate, TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') createDate, T.CREATE_OPER createOper, (SELECT M.OPERNAME FROM OPERATOR M WHERE M.OPERID = T.CREATE_OPER) || '(' || T.CREATE_OPER || ')' createOperName, T.CREATE_ORG_ID createOrgId, (SELECT ORG.ORGNAME FROM PSI_ORGANIZATION ORG WHERE ORG.ORGID = T.CREATE_ORG_ID) AS createOrgName, T.DES_ORG_ID desOrgId, (SELECT ORG.ORGNAME FROM PSI_ORGANIZATION ORG WHERE ORG.ORGID = T.DES_ORG_ID) AS desOrgName, T.MEMO memo, T.LOCK_STATUS lockStatus, T.STATUS_DATE statusDate, T.DES_REGION desRegion, T.SRC_ORG_ID srcOrgId, (SELECT SUPPLIER_ID FROM PSI_FLOW_APPLY_BATCH M WHERE M.FLOW_ID = T.FLOW_ID AND ROWNUM = 1) supplierId, (SELECT SETTLE_MODE FROM PSI_FLOW_APPLY_BATCH M WHERE M.FLOW_ID = T.FLOW_ID AND ROWNUM = 1) settleMode, (SELECT ORG.ORGNAME FROM PSI_ORGANIZATION ORG WHERE ORG.ORGID = T.SRC_ORG_ID) AS srcOrgName, T.ADDRESS address, trim(to_char(NVL(T.SUM_PRICE, 0)/100, '99999999999999999990.00')) sumPrice, trim(to_char(NVL(T.PAY_PRICE, 0)/100, '99999999999999999990.00')) payPrice, T.SUM_APPLY_NUM sumApplyNum, NVL((SELECT SUM(M.STOCKUP_NUM) FROM PSI_FLOW_APPLY_BATCH M WHERE M.FLOW_ID=T.FLOW_ID GROUP BY M.FLOW_ID), '0') stockupNum, NVL((SELECT SUM(M.ARRIVE_NUM) FROM PSI_FLOW_APPLY_BATCH M WHERE M.FLOW_ID=T.FLOW_ID GROUP BY M.FLOW_ID), '0') arriveNum, T.PAY_TYPE payType, T.PAY_STATUS payStatus, T.ORDER_PRIV_DESC orderPrivDesc, trim(to_char(NVL(T.ORDER_PRIV_DISC, 0)/100, '99999999999999999990.00')) orderPrivDisc, NVL((SELECT COUNT(1) FROM PSI_FLOW_PAYMENT_LOG P, PSI_FLOW_PAYMENT_ORDER O WHERE O.FLOW_ID = T.FLOW_ID AND P.PAY_OID = O.PAY_OID AND P.TRADE_STATUS = 'TRADE_SUCCESS'), 0) payTimes, TRIM(to_char(NVL(T.ALREADY_PAY_PRICE, 0)/100, '99999999999999999990.00')) alreadyPayPrice FROM PSI_FLOW_APPLY T, PSI_FLOW_ROLE D WHERE 1=1 AND T.REGION = D.REGION AND T.FLOW_ID = D.FLOW_ID AND T.LOCK_STATUS <> 'LOCKED' AND D.ORG_ID=:1 AND D.ROLE_ID IN ( 'PSI0700026', 'PSI070202401', 'PSI070202601', 'PSI070902601' ) AND T.FLOW_STATUS in ('UNCONSIGNMENT') AND EXISTS (SELECT 1 FROM PSI_DICT_ITEM DICT WHERE T.APPLY_TYPE = DICT_ID AND DICT.GROUP_ID = 'MOBAPPLY_AGENT') ORDER BY T.STATUS_D ATE DESC )row_ where rownum <= 10 ) where rownum_ >= 1 |
######转换十进制为16进制格式（注意16进制是16位数字，不够在前面补0）
SQL> select to_char('504403190311174992','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_CHAR('504403190311174992','XX
--------------------------------
700000776124350
########通过event p1raw 查找热块对象
Using the P1RAW from the above example (00000400837D7800).
The address of the latch that the process is waiting for. The hexadecimal value of P1 (P1RAW) can be used to determine which latch is waited for thus:
SELECT a.Hladdr,
a.File#,
a.Dbablk,
a.Tch,
a.Obj,
b.Object_Name
FROM X$bh a,
Dba_Objects b
WHERE (a.Obj = b.Object_Id OR a.Obj = b.Data_Object_Id)
AND a.Hladdr = '&P1RAW_Value'
UNION
SELECT Hladdr,
File#,
Dbablk,
Tch,
Obj,
NULL
FROM X$bh
WHERE Obj IN
(SELECT Obj
FROM X$bh
WHERE Hladdr = '&1RAW_Value'
MINUS
SELECT Object_Id
FROM Dba_Objects
MINUS
SELECT Data_Object_Id FROM Dba_Objects)
AND Hladdr = '&P1RAW_Value'
ORDER BY 4;
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
0700000776124350 44 514942 0 1869764 IM_INV_REINFORCE_USE
0700000776124350 52 244876 0 1865728 IM_INV_TELNUM
0700000776124350 76 780102 0 1869794 IM_INV_TELNUM
0700000776124350 104 975011 0 1935890 IDX_IMINVMOBTEL
0700000776124350 177 251526 0 1874943 PK_UCP_PASSWORD
0700000776124350 189 51711 0 1838539 T_UCP_ORGACHILD
0700000776124350 31 851099 1 1869582 IM_INV_MOBTEL
0700000776124350 43 236378 1 2031913 PSI_FLOW_STOCKOUT_BATCH
0700000776124350 60 1936086 2 2175694 PSI_FLOW_STOCKOUT
0700000776124350 74 52627 2 1952322 IDX_IMTELNUMTAIL_INV_ID
0700000776124350 75 195781 2 1869798 IM_INV_TELNUM
HLADDR FILE# DBABLK TCH OBJ OBJECT_NAME
---------------- ---------- ---------- ---------- ---------- ------------------------------
0700000776124350 126 1494749 2 1958805 IDX_STOCKENTITY_OUTBATCHID
0700000776124350 156 316109 2 1922466 PK_IMINVTELNUM
0700000776124350 167 1357839 2 1941784 IDX_IMTELNUMTYPE
0700000776124350 170 433201 2 1958741 IDX_STOCKENTITY_OUTBATCHID
0700000776124350 167 1493249 3 1941784 IDX_IMTELNUMTYPE
0700000776124350 178 429617 3 1933918 PK_IMINVTELNUM
0700000776124350 169 255110 5 2103604 IDX_FLOWAPPLYBATCH_FLOWID＝＝＝＝＝》该对象tch最高因此热卖为该对象
18 rows selected.
SQL> select index_owner,index_name,column_name,column_position from dba_ind_columns where index_name='PK_IMINVTELNUM';
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------------------------------- ----------------
IM PK_IMINVTELNUM INV_ID 1
IM PK_IMINVTELNUM REGION 2
select index_owner,index_name,column_name,column_position from dba_ind_columns where index_name='IDX_FLOWAPPLYBATCH_FLOWID';
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ---------------------------------------- ----------------
IM IDX_FLOWAPPLYBATCH_FLOWID FLOW_ID 1
对热块对应的语句如为acsdn1wgy2fg7
SQL_ID acsdn1wgy2fg7, child number 1
-------------------------------------
var v_1 varchar2(30);
exec :v_1:='SD.LU.01.01';
select *
from (select row_.*, rownum rownum_
from (SELECT /*+index(T,ind_test)*/ T.REGION,
T.REGION qryRegion,
T.FLOW_ID flowId,
decode(T.LOCK_STATUS,
'LOCKED',
'PROCESSING',
T.FLOW_STATUS) flowStatus,
T.RES_KIND_ID resKindId,
T.ORG_LEVEL orgLevel,
T.ORG_ID orgId,
(SELECT ORG.DISPLAYNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.ORG_ID) AS orgName,
T.APPLY_CYCLE applyCycle,
T.CYCLE_TYPE cycleType,
T.APPLY_TYPE applyType,
T.APPLY_CLASS applyClass,
T.CHANNEL_TYPE channelType,
T.PROJECT_ID projectId,
T.IF_BASE ifBase,
T.PARENT_FLOW_ID parentFlowId,
T.LINK_MAN linkMan,
T.CONTACT_PHONE contactPhone,
TO_CHAR(T.RECEIVE_DATE, 'YYYY-MM-DD') receiveDate,
TO_CHAR(T.RECEIVE_LAST_DATE, 'YYYY-MM-DD') receiveLastDate,
TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') createDate,
T.CREATE_OPER createOper,
(SELECT M.OPERNAME FROM OPERATOR M
WHERE M.OPERID = T.CREATE_OPER) || '(' ||
T.CREATE_OPER || ')' createOperName,
T.CREATE_ORG_ID createOrgId,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.CREATE_ORG_ID) AS createOrgName,
T.DES_ORG_ID desOrgId,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.DES_ORG_ID) AS desOrgName,
T.MEMO memo,
T.LOCK_STATUS lockStatus,
T.STATUS_DATE statusDate,
T.DES_REGION desRegion,
T.SRC_ORG_ID srcOrgId,
(SELECT SUPPLIER_ID
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
AND ROWNUM = 1) supplierId,
(SELECT SETTLE_MODE
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
AND ROWNUM = 1) settleMode,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.SRC_ORG_ID) AS srcOrgName,
T.ADDRESS address,
trim(to_char(NVL(T.SUM_PRICE, 0) / 100,
'99999999999999999990.00')) sumPrice,
trim(to_char(NVL(T.PAY_PRICE, 0) / 100,
'99999999999999999990.00')) payPrice,
T.SUM_APPLY_NUM sumApplyNum,
NVL((SELECT SUM(M.STOCKUP_NUM)
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
GROUP BY M.FLOW_ID),
'0') stockupNum,
NVL((SELECT SUM(M.ARRIVE_NUM)
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
GROUP BY M.FLOW_ID),
'0') arriveNum,
T.PAY_TYPE payType,
T.PAY_STATUS payStatus,
T.ORDER_PRIV_DESC orderPrivDesc,
trim(to_char(NVL(T.ORDER_PRIV_DISC, 0) / 100,
'99999999999999999990.00')) orderPrivDisc,
NVL((SELECT COUNT(1)
FROM PSI_FLOW_PAYMENT_LOG P,
PSI_FLOW_PAYMENT_ORDER O
WHERE O.FLOW_ID = T.FLOW_ID
AND P.PAY_OID = O.PAY_OID
AND P.TRADE_STATUS = 'TRADE_SUCCESS'),
0) payTimes,
TRIM(to_char(NVL(T.ALREADY_PAY_PRICE, 0) / 100,
'99999999999999999990.00')) alreadyPayPrice
FROM PSI_FLOW_APPLY T, PSI_FLOW_ROLE D
WHERE 1 = 1
AND T.REGION = D.REGION
AND T.LOCK_STATUS <> 'LOCKED'
AND D.ORG_ID = :v_1
AND D.ROLE_ID IN ('PSI0700026', 'PSI070202401',
'PSI070202601', 'PSI070902601')
AND T.FLOW_STATUS in ('UNCONSIGNMENT')
AND EXISTS
(SELECT 1
FROM PSI_DICT_ITEM DICT
WHERE T.APPLY_TYPE = DICT_ID
AND DICT.GROUP_ID = 'MOBAPPLY_AGENT')
ORDER BY T.STATUS_DATE DESC ) row_
where rownum <= 10)
where rownum_ >= 1
SnapId PLAN_HASH_VALUE DATE_TIME EXECUTIONS_DELTA LIO/exec AVG_CPUTIME AVG_ETIME AVG_PIO AVG_ROW
---------- --------------- -------------------- ---------------- ---------- ----------- ---------- ---------- ----------
16610 3040716915 2014/10/30_1500_1600 236 330700.258 1.16122881 2.29135151 324.533898 30.8559322
16611 3040716915 2014/10/30_1600_1700 564 2950210.93 8.81691489 55.1938254 115.524823 22.8475177
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:48.11 | 5180K| 6258 | | | |
| 1 | TABLE ACCESS BY INDEX ROWID | T_UCP_ORGAINFO | 2770 | 1 | 2770 |00:00:00.05 | 8323 | 0 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 2770 | 1 | 2770 |00:00:00.03 | 5545 | 0 | | | |
| 3 | NESTED LOOPS OUTER | | 2202 | 1 | 2202 |00:00:01.86 | 8153 | 745 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T_UCP_STAFFBASICINFO | 2202 | 1 | 2202 |00:00:01.82 | 6544 | 745 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_T_UCP_STAFFBASICINFO | 2202 | 1 | 2202 |00:00:00.37 | 4342 | 215 | | | |
|* 6 | INDEX RANGE SCAN | PK_UCP_STAFFMAC | 2202 | 1 | 19 |00:00:00.02 | 1609 | 0 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T_UCP_ORGAINFO | 2276 | 1 | 2276 |00:00:00.02 | 6837 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 2276 | 1 | 2276 |00:00:00.01 | 4554 | 0 | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | T_UCP_ORGAINFO | 2273 | 1 | 2273 |00:00:00.02 | 6828 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 2273 | 1 | 2273 |00:00:00.01 | 4548 | 0 | | | |
|* 11 | COUNT STOPKEY | | 5121 | | 5121 |00:00:10.18 | 748K| 2468 | | | |
| 12 | PARTITION RANGE ALL | | 5121 | 2 | 5121 |00:00:10.15 | 748K| 2468 | | | |
| 13 | TABLE ACCESS BY LOCAL INDEX ROWID | PSI_FLOW_APPLY_BATCH | 448K| 2 | 5121 |00:00:09.73 | 748K| 2468 | | | |
|* 14 | INDEX RANGE SCAN | IDX_FLOWAPPLYBATCH_FLOWID | 448K| 2 | 5121 |00:00:03.89 | 743K| 164 | | | |
|* 15 | COUNT STOPKEY | | 5121 | | 5121 |00:00:05.05 | 748K| 0 | | | |
| 16 | PARTITION RANGE ALL | | 5121 | 2 | 5121 |00:00:05.03 | 748K| 0 | | | |
| 17 | TABLE ACCESS BY LOCAL INDEX ROWID | PSI_FLOW_APPLY_BATCH | 448K| 2 | 5121 |00:00:04.60 | 748K| 0 | | | |
|* 18 | INDEX RANGE SCAN | IDX_FLOWAPPLYBATCH_FLOWID | 448K| 2 | 5121 |00:00:03.18 | 743K| 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | T_UCP_ORGAINFO | 1 | 1 | 1 |00:00:00.01 | 4 | 0 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_T_UCP_ORGAINFO | 1 | 1 | 1 |00:00:00.01 | 3 | 0 | | | |
| 21 | SORT GROUP BY NOSORT | | 5121 | 1 | 5121 |00:00:15.37 | 1809K| 739 | | | |
| 22 | PARTITION RANGE ALL | | 5121 | 2 | 22840 |00:00:15.30 | 1809K| 739 | | | |
| 23 | TABLE ACCESS BY LOCAL INDEX ROWID | PSI_FLOW_APPLY_BATCH | 1106K| 2 | 22840 |00:00:14.16 | 1809K| 739 | | | |
|* 24 | INDEX RANGE SCAN | IDX_FLOWAPPLYBATCH_FLOWID | 1106K| 2 | 22840 |00:00:10.36 | 1803K| 528 | | | |
| 25 | SORT GROUP BY NOSORT | | 5121 | 1 | 5121 |00:00:11.73 | 1809K| 0 | | | |
| 26 | PARTITION RANGE ALL | | 5121 | 2 | 22840 |00:00:11.69 | 1809K| 0 | | | |
| 27 | TABLE ACCESS BY LOCAL INDEX ROWID | PSI_FLOW_APPLY_BATCH | 1106K| 2 | 22840 |00:00:10.76 | 1809K| 0 | | | |
|* 28 | INDEX RANGE SCAN | IDX_FLOWAPPLYBATCH_FLOWID | 1106K| 2 | 22840 |00:00:07.65 | 1803K| 0 | | | |
| 29 | SORT AGGREGATE | | 5121 | 1 | 5121 |00:00:03.31 | 24962 | 2302 | | | |
| 30 | NESTED LOOPS | | 5121 | | 1843 |00:00:03.28 | 24962 | 2302 | | | |
| 31 | NESTED LOOPS | | 5121 | 2 | 4155 |00:00:01.45 | 21441 | 993 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | PSI_FLOW_PAYMENT_ORDER | 5121 | 2 | 4155 |00:00:01.08 | 13638 | 872 | | | |
|* 33 | INDEX RANGE SCAN | IDX_PSIFLOWPAYORDER_FLOWID | 5121 | 2 | 4155 |00:00:00.62 | 10250 | 511 | | | |
|* 34 | INDEX RANGE SCAN | IDX_PSIFLOWPAYMENTLOG_PAYOID | 4155 | 1 | 4155 |00:00:00.36 | 7803 | 121 | | | |
|* 35 | TABLE ACCESS BY GLOBAL INDEX ROWID | PSI_FLOW_PAYMENT_LOG | 4155 | 1 | 1843 |00:00:01.81 | 3521 | 1309 | | | |
|* 36 | VIEW | | 1 | 10 | 10 |00:00:48.11 | 5180K| 6258 | | | |
|* 37 | COUNT STOPKEY | | 1 | | 10 |00:00:48.11 | 5180K| 6258 | | | |
| 38 | VIEW | | 1 | 12 | 10 |00:00:48.11 | 5180K| 6258 | | | |
|* 39 | SORT ORDER BY STOPKEY | | 1 | 12 | 10 |00:00:48.11 | 5180K| 6258 | 48128 | 48128 |43008 (0)|
|* 40 | HASH JOIN | | 1 | 12 | 5121 |00:00:00.22 | 8973 | 4 | 1286K| 1083K| 1488K (0)|
| 41 | TABLE ACCESS BY GLOBAL INDEX ROWID| PSI_FLOW_ROLE | 1 | 14 | 7150 |00:00:00.04 | 1557 | 4 | | | |
|* 42 | INDEX RANGE SCAN | IDX_FLOWROLE_ORGROLE | 1 | 4 | 7150 |00:00:00.01 | 58 | 0 | | | |
| 43 | NESTED LOOPS | | 1 | | 10333 |00:00:00.14 | 7416 | 0 | | | |
| 44 | NESTED LOOPS | | 1 | 54 | 10333 |00:00:00.03 | 498 | 0 | | | |
| 45 | SORT UNIQUE | | 1 | 4 | 10 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
|* 46 | INDEX RANGE SCAN | PK_PSIDICTITEM | 1 | 4 | 10 |00:00:00.01 | 2 | 0 | | | |
| 47 | PARTITION RANGE ALL | | 10 | 94 | 10333 |00:00:00.03 | 496 | 0 | | | |
|* 48 | INDEX RANGE SCAN | IDX_PSI_FLOW_APPLY_TYPESTATUS | 180 | 94 | 10333 |00:00:00.02 | 496 | 0 | | | |
|* 49 | TABLE ACCESS BY LOCAL INDEX ROWID| PSI_FLOW_APPLY | 10333 | 13 | 10333 |00:00:00.09 | 6918 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORG"."ORGAID"=:B1)
5 - access("BASIC"."STAFFID"=:B1)
6 - access("MAC"."STAFFID"=:B1)
filter("BASIC"."STAFFID"="MAC"."STAFFID")
8 - access("ORG"."ORGAID"=:B1)
10 - access("ORG"."ORGAID"=:B1)
11 - filter(ROWNUM=1)
14 - access("M"."FLOW_ID"=:B1)
15 - filter(ROWNUM=1)
18 - access("M"."FLOW_ID"=:B1)
20 - access("ORG"."ORGAID"=:B1)
24 - access("M"."FLOW_ID"=:B1)
28 - access("M"."FLOW_ID"=:B1)
33 - access("O"."FLOW_ID"=:B1)
34 - access("P"."PAY_OID"="O"."PAY_OID")
35 - filter("P"."TRADE_STATUS"='TRADE_SUCCESS')
36 - filter("ROWNUM_">=1)
37 - filter(ROWNUM<=10)
39 - filter(ROWNUM<=10)
40 - access("T"."REGION"="D"."REGION" AND "T"."FLOW_ID"="D"."FLOW_ID")
42 - access("D"."ORG_ID"=:V_1)
filter(("D"."ROLE_ID"='PSI0700026' OR "D"."ROLE_ID"='PSI070202401' OR "D"."ROLE_ID"='PSI070202601' OR "D"."ROLE_ID"='PSI070902601'))
46 - access("DICT"."GROUP_ID"='MOBAPPLY_AGENT')
48 - access("T"."APPLY_TYPE"="DICT_ID" AND "T"."FLOW_STATUS"='UNCONSIGNMENT')
49 - filter("T"."LOCK_STATUS"<>'LOCKED')
INDEX_NAME INDEX_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ----------------
IDX_FLOWAPPLYBATCH_FLOWID IM PSI_FLOW_APPLY_BATCH FLOW_ID 1
var v_1 varchar2(30);
exec :v_1:='SD.LU.01.01';
select *
from (select row_.*, rownum rownum_
from (SELECT /*+index(T,ind_test)*/ T.REGION,
T.REGION qryRegion,
T.FLOW_ID flowId,
decode(T.LOCK_STATUS,
'LOCKED',
'PROCESSING',
T.FLOW_STATUS) flowStatus,
T.RES_KIND_ID resKindId,
T.ORG_LEVEL orgLevel,
T.ORG_ID orgId,
(SELECT ORG.DISPLAYNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.ORG_ID) AS orgName,
T.APPLY_CYCLE applyCycle,
T.CYCLE_TYPE cycleType,
T.APPLY_TYPE applyType,
T.APPLY_CLASS applyClass,
T.CHANNEL_TYPE channelType,
T.PROJECT_ID projectId,
T.IF_BASE ifBase,
T.PARENT_FLOW_ID parentFlowId,
T.LINK_MAN linkMan,
T.CONTACT_PHONE contactPhone,
TO_CHAR(T.RECEIVE_DATE, 'YYYY-MM-DD') receiveDate,
TO_CHAR(T.RECEIVE_LAST_DATE, 'YYYY-MM-DD') receiveLastDate,
TO_CHAR(T.CREATE_DATE, 'YYYY-MM-DD HH24:MI:SS') createDate,
T.CREATE_OPER createOper,
(SELECT M.OPERNAME FROM OPERATOR M
WHERE M.OPERID = T.CREATE_OPER) || '(' ||
T.CREATE_OPER || ')' createOperName,
T.CREATE_ORG_ID createOrgId,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.CREATE_ORG_ID) AS createOrgName,
T.DES_ORG_ID desOrgId,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.DES_ORG_ID) AS desOrgName,
T.MEMO memo,
T.LOCK_STATUS lockStatus,
T.STATUS_DATE statusDate,
T.DES_REGION desRegion,
T.SRC_ORG_ID srcOrgId,
(SELECT SUPPLIER_ID
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
AND ROWNUM = 1) supplierId,
(SELECT SETTLE_MODE
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
AND ROWNUM = 1) settleMode,
(SELECT ORG.ORGNAME
FROM PSI_ORGANIZATION ORG
WHERE ORG.ORGID = T.SRC_ORG_ID) AS srcOrgName,
T.ADDRESS address,
trim(to_char(NVL(T.SUM_PRICE, 0) / 100,
'99999999999999999990.00')) sumPrice,
trim(to_char(NVL(T.PAY_PRICE, 0) / 100,
'99999999999999999990.00')) payPrice,
T.SUM_APPLY_NUM sumApplyNum,
NVL((SELECT SUM(M.STOCKUP_NUM)
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
GROUP BY M.FLOW_ID),
'0') stockupNum,
NVL((SELECT SUM(M.ARRIVE_NUM)
FROM PSI_FLOW_APPLY_BATCH M
WHERE M.FLOW_ID = T.FLOW_ID
GROUP BY M.FLOW_ID),
'0') arriveNum,
T.PAY_TYPE payType,
T.PAY_STATUS payStatus,
T.ORDER_PRIV_DESC orderPrivDesc,
trim(to_char(NVL(T.ORDER_PRIV_DISC, 0) / 100,
'99999999999999999990.00')) orderPrivDisc,
NVL((SELECT COUNT(1)
FROM PSI_FLOW_PAYMENT_LOG P,
PSI_FLOW_PAYMENT_ORDER O
WHERE O.FLOW_ID = T.FLOW_ID
AND P.PAY_OID = O.PAY_OID
AND P.TRADE_STATUS = 'TRADE_SUCCESS'),
0) payTimes,
TRIM(to_char(NVL(T.ALREADY_PAY_PRICE, 0) / 100,
'99999999999999999990.00')) alreadyPayPrice
FROM PSI_FLOW_APPLY T, PSI_FLOW_ROLE D
WHERE 1 = 1
AND T.REGION = D.REGION
AND T.LOCK_STATUS <> 'LOCKED'
AND D.ORG_ID = :v_1
AND D.ROLE_ID IN ('PSI0700026', 'PSI070202401',
'PSI070202601', 'PSI070902601')
AND T.FLOW_STATUS in ('UNCONSIGNMENT')
AND EXISTS
(SELECT 1
FROM PSI_DICT_ITEM DICT
WHERE T.APPLY_TYPE = DICT_ID
AND DICT.GROUP_ID = 'MOBAPPLY_AGENT')
ORDER BY T.STATUS_DATE DESC ) row_
where rownum <= 10)
where rownum_ >= 1
10 rows selected.
Elapsed: 00:00:50.96
创建降序索引以后执行时间由50.96s降为0.15s 性能提升339倍
create index ind_test on im.PSI_FLOW_APPLY (STATUS_DATE desc) parallel 16;
Elapsed: 00:00:00.15
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29446986/viewspace-1315310/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
