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 |
|---|---|---|---|---|---|---|---|---|
| 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部落格 ” ，連結：http://blog.itpub.net/29446986/viewspace-1315310/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- latch: cache buffers chainsAI
- 深入理解latch: cache buffers chainsAI
- Trouble shooting latch: cache buffers chainsAI
- ORACLE等待事件latch: cache buffers chainsOracle事件AI
- 等待事件_cache_buffers_chains_latch事件AI
- latch：cache buffers chains解決步驟AI
- latch：cache buffers chains的優化思路AI優化
- cache buffers chains and cache buffers lru chainsAI
- buffer cache實驗6-latch:cache buffers lru chainsAI
- latch：cache buffers chains的最佳化思路AI
- latch: cache buffers chains---AWR實戰分析AI
- latch: cache buffers chains故障處理總結(轉載)AI
- 解決一例latch:cache buffers chains小記AI
- cache buffers chains vs cache buffers lru chainAI
- 一次latch cache buffers chains問題的處理AI
- 處理 latch_cache_buffers_chains等待事件一例AI事件
- Latch: cache buffer chains (%)AI
- cache buffers LRU chain latchAI
- latch free(cache buffers chain)AI
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- latch: cache buffers chains-熱塊的簡單模擬實驗AI
- 用於排查cache buffers chainsAI
- Cache Buffers chains,存在共享模式？AI模式
- Bug 3797171 cache buffers chains latch contention increased in 10g-3797171.8AI
- cbc latch或cache buffer chains latch系列一AI
- buffer cache實驗5-latch:cache buffers chainAI
- 等待事件_cache_buffers_lru_chain_latch事件AI
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 熱點塊競爭和解決--cache buffers chainsAI
- 0821Cache Buffers chains與共享模式疑問4AI模式
- 0330Cache Buffers chains與共享模式疑問AI模式
- 【恩墨學院】深入剖析：關於cache buffers chains的經典案例處理詳解?AI
- 0330Cache Buffers chains與共享模式疑問2AI模式
- 1104Cache Buffers chains與共享模式疑問3AI模式
- 1104Cache Buffers chains與共享模式疑問4AI模式
- 客戶資料庫出現大量cache buffer chains latch資料庫AI
- 使用了索引就一定能避免cache buffers chains爭用嗎索引AI
- CACHE BUFFER CHAINSAI