分类： Oracle
2014-06-24 15:48:44
寿险财务，集团财务均有因为该问题导致性能下降，系统cpu负载急剧上升的情况。
如集团财务以下语句，运行缓慢，cpu升高到95%以上，大量latch free等待，用户反映缓慢。
INSERT INTO CGL_FSG_REPORT_RESULT_TEMP1
SELECT /*+ index(ggt CGL_GB_GLBAL_TEMP1_n1) */
:B8,
:B7,
FAAT.AXIS_SET_ID,
FAAT.AXIS_SEQ,
:B5,
SUM((GGT.PERIOD_NET_DR - GGT.PERIOD_NET_CR) *
DECODE(FAAT.SIGN, '+', 1, -1)),
SUM((CASE
WHEN :B4 = 30 AND :B4 = RRA.STANDARD_AXIS_ID THEN
(GGT.BEGIN_BALANCE_DR - GGT.BEGIN_BALANCE_CR)
ELSE
(GGT.PERIOD_NET_DR - GGT.PERIOD_NET_CR + GGT.BEGIN_BALANCE_DR -
GGT.BEGIN_BALANCE_CR)
END) * DECODE(FAAT.SIGN, '+', 1, -1))
FROM CGL_GB_GLBAL_TEMP1 GGT,
CGL_FSG_ACCT_ASSIGN_TEMP1 FAAT,
RG_REPORT_AXES RRA
WHERE GGT.SEGMENT2 BETWEEN FAAT.SEGMENT2_LOW AND FAAT.SEGMENT2_HIGH
AND GGT.SEGMENT3 BETWEEN FAAT.SEGMENT3_LOW AND FAAT.SEGMENT3_HIGH
AND GGT.SEGMENT4 BETWEEN FAAT.SEGMENT4_LOW AND FAAT.SEGMENT4_HIGH
AND GGT.SEGMENT5 BETWEEN FAAT.SEGMENT5_LOW AND FAAT.SEGMENT5_HIGH
AND GGT.SEGMENT6 BETWEEN FAAT.SEGMENT6_LOW AND FAAT.SEGMENT6_HIGH
AND GGT.SEGMENT7 BETWEEN FAAT.SEGMENT7_LOW AND FAAT.SEGMENT7_HIGH
AND GGT.SEGMENT8 BETWEEN FAAT.SEGMENT8_LOW AND FAAT.SEGMENT8_HIGH
AND FAAT.AXIS_SET_ID = RRA.AXIS_SET_ID
AND FAAT.AXIS_SEQ = RRA.AXIS_SEQ
AND GGT.SOURCE = DECODE(RRA.PARAMETER_NUM,
0,
'F0',
1,
'F1',
2,
'F2',
3,
'F3',
4,
'F4',
'G')
AND GGT.CURRENCY_CODE = :B6
AND ((RRA.STANDARD_AXIS_ID IS NULL AND GGT.PERIOD_NAME = :B5) OR
(:B4 = 30 AND RRA.STANDARD_AXIS_ID = :B4 AND
GGT.PERIOD_NAME = :B3))
AND GGT.SET_OF_BOOKS_ID = :B2
AND FAAT.AXIS_SET_ID = :B1
GROUP BY FAAT.AXIS_SET_ID, FAAT.AXIS_SEQ, GGT.PERIOD_NAME
创建索引即可缓解
create index apps.cgl_acct_asn_tmp__1 on apps.cgl_fsg_acct_assign_temp1 (AXIS_SET_ID,AXIS_SEQ);
另一个案例：
财务库的另一个sql，正常情况下是100秒左右，但并发20个时，基本无法跑完，修改优化后，只需要3秒，且并发不会互相等待、抢占资源的问题。
增加hint为/*+ full(AEH)*/
SELECT /*+ full(AEH)*/
H.PERIOD_NAME,
G.SEGMENT1 COMPANY,
GS.USER_JE_SOURCE_NAME,
GC.USER_JE_CATEGORY_NAME,
TO_NUMBER(H.EXTERNAL_REFERENCE) EXTERNAL_REFERENCE,
AID.BASE_AMOUNT ENTERED_DR,
NULL ENTERED_CR,
AID.BASE_AMOUNT ACCOUNTED_DR,
L.ACCOUNTED_CR,
H.CURRENCY_CODE,
L.DESCRIPTION,
G.SEGMENT1,
G.SEGMENT2,
G.SEGMENT3,
G.SEGMENT4,
G.SEGMENT5,
G.SEGMENT6,
G.SEGMENT7,
G.SEGMENT8,
G.SEGMENT9,
G.SEGMENT10,
FV.DESCRIPTION S2DESC,
B.NAME BATCHES_NAME,
H.NAME HEADERS_NAME,
H.DESCRIPTION HEADERS_DESCRIPTION,
AID.DESCRIPTION LINE_DESCRIPTION
FROM apps.GL_JE_BATCHES B,
apps.GL_JE_HEADERS H,
apps.GL_JE_LINES L,
apps.GL_CODE_COMBINATIONS G,
apps.GL_JE_SOURCES GS,
apps.GL_JE_CATEGORIES GC,
apps.FND_FLEX_VALUES_VL FV,
apps.GL_IMPORT_REFERENCES GIR,
apps.AP_INVOICE_DISTRIBUTIONS_ALL AID,
XLA.XLA_AE_HEADERS AEH,
XLA.XLA_AE_LINES AEL,
XLA.XLA_DISTRIBUTION_LINKS XD,
XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE B.JE_BATCH_ID = H.JE_BATCH_ID
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = G.CODE_COMBINATION_ID
AND XD.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND XD.ROUNDING_CLASS_CODE = 'ITEM EXPENSE'
AND AEL.AE_HEADER_ID = XD.AE_HEADER_ID
AND AEL.AE_LINE_NUM = XD.AE_LINE_NUM
AND AEL.ACCOUNTING_CLASS_CODE = XD.ROUNDING_CLASS_CODE
AND H.JE_SOURCE = 'Payables'
AND H.JE_CATEGORY = 'Purchase Invoices'
AND GIR.JE_BATCH_ID = B.JE_BATCH_ID
AND GIR.JE_HEADER_ID = H.JE_HEADER_ID
AND GIR.JE_LINE_NUM = L.JE_LINE_NUM
AND XTE.APPLICATION_ID = AEH.APPLICATION_ID
AND XTE.ENTITY_ID = AEH.ENTITY_ID
AND AEH.AE_HEADER_ID = AEL.AE_HEADER_ID
AND AEH.APPLICATION_ID = AEL.APPLICATION_ID
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND XTE.APPLICATION_ID = 200
AND XTE.SOURCE_ID_INT_1 = AID.INVOICE_ID
AND GIR.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
AND GIR.GL_SL_LINK_TABLE = AEL.GL_SL_LINK_TABLE
AND AEL.ACCOUNTING_CLASS_CODE = 'ITEM EXPENSE'
AND GIR.JE_HEADER_ID = H.JE_HEADER_ID
AND GIR.JE_LINE_NUM = L.JE_LINE_NUM
AND H.JE_SOURCE = GS.JE_SOURCE_NAME
AND H.JE_CATEGORY = GC.JE_CATEGORY_NAME
AND H.ACTUAL_FLAG = 'A' --:B27
AND G.TEMPLATE_ID IS NULL
AND (H.CURRENCY_CODE = 'CNY' OR 'CNY' IS NULL)
AND (G.SEGMENT1 >= NVL('3010000', G.SEGMENT1) AND
G.SEGMENT1 <= NVL('3010000', G.SEGMENT1))
AND (G.SEGMENT2 >= NVL('6601289900', G.SEGMENT2) AND
G.SEGMENT2 <= NVL('6601289900', G.SEGMENT2))
AND (G.SEGMENT3 >= NVL('0', G.SEGMENT3) AND
G.SEGMENT3 <= NVL('z', G.SEGMENT3))
AND (G.SEGMENT4 >= NVL('0', G.SEGMENT4) AND
G.SEGMENT4 <= NVL('z', G.SEGMENT4))
AND (G.SEGMENT5 >= NVL('0', G.SEGMENT5) AND
G.SEGMENT5 <= NVL('z', G.SEGMENT5))
AND (G.SEGMENT6 >= NVL('0', G.SEGMENT6) AND
G.SEGMENT6 <= NVL('z', G.SEGMENT6))
AND (G.SEGMENT7 >= NVL('0', G.SEGMENT7) AND
G.SEGMENT7 <= NVL('z', G.SEGMENT7))
AND (G.SEGMENT8 >= NVL('0', G.SEGMENT8) AND
G.SEGMENT8 <= NVL('z', G.SEGMENT8))
AND (G.SEGMENT9 >= NVL('0', G.SEGMENT9) AND
G.SEGMENT9 <= NVL('z', G.SEGMENT9))
AND (G.SEGMENT10 >= NVL('0', G.SEGMENT10) AND
G.SEGMENT10 <= NVL('z', G.SEGMENT10))
AND H.LEDGER_ID = 2023
AND H.PERIOD_NAME BETWEEN '2014-04' AND '2014-04'
AND FV.FLEX_VALUE_SET_ID = 1014875
AND FV.FLEX_VALUE = G.SEGMENT2
/*AND apps.GL_SECURITY_PKG.VALIDATE_SEGVAL(1, NULL, G.SEGMENT1, NULL, NULL) =
'TRUE'*/
AND (H.STATUS = 'P' OR ('Y' = 'N' AND H.STATUS <> 'P'))
UNION ALL
SELECT H.PERIOD_NAME PERIOD_NAME,
G.SEGMENT1 COMPANY,
GS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME,
GC.USER_JE_CATEGORY_NAME,
TO_NUMBER(H.EXTERNAL_REFERENCE) EXTERNAL_REFERENCE,
L.ENTERED_DR,
L.ENTERED_CR,
L.ACCOUNTED_DR,
L.ACCOUNTED_CR,
H.CURRENCY_CODE,
L.DESCRIPTION,
G.SEGMENT1 SEGMENT1,
G.SEGMENT2,
G.SEGMENT3,
G.SEGMENT4,
G.SEGMENT5,
G.SEGMENT6,
G.SEGMENT7,
G.SEGMENT8,
G.SEGMENT9,
G.SEGMENT10,
FV.DESCRIPTION S2DESC,
B.NAME BATCHES_NAME,
H.NAME HEADERS_NAME,
H.DESCRIPTION HEADERS_DESCRIPTION,
L.DESCRIPTION LINE_DESCRIPTION
FROM apps.GL_JE_BATCHES B,
apps.GL_JE_HEADERS H,
apps.GL_JE_LINES L,
apps.GL_CODE_COMBINATIONS G,
apps.GL_JE_SOURCES GS,
apps.GL_JE_CATEGORIES GC,
apps.FND_FLEX_VALUES_VL FV
WHERE B.JE_BATCH_ID = H.JE_BATCH_ID
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = G.CODE_COMBINATION_ID
AND H.JE_SOURCE <> 'Payables'
AND H.JE_CATEGORY <> 'Purchase Invoices'
AND H.JE_SOURCE = GS.JE_SOURCE_NAME
AND H.JE_CATEGORY = GC.JE_CATEGORY_NAME
AND H.ACTUAL_FLAG = 'A'
AND G.TEMPLATE_ID IS NULL
AND (H.CURRENCY_CODE = 'CNY' OR 'CNY' IS NULL)
AND (G.SEGMENT1 >= NVL('3010000', G.SEGMENT1) AND
G.SEGMENT1 <= NVL('3010000', G.SEGMENT1))
AND (G.SEGMENT2 >= NVL('6601289900', G.SEGMENT2) AND
G.SEGMENT2 <= NVL('6601289900', G.SEGMENT2))
AND (G.SEGMENT3 >= NVL('0', G.SEGMENT3) AND
G.SEGMENT3 <= NVL('z', G.SEGMENT3))
AND (G.SEGMENT4 >= NVL('0', G.SEGMENT4) AND
G.SEGMENT4 <= NVL('z', G.SEGMENT4))
AND (G.SEGMENT5 >= NVL('0', G.SEGMENT5) AND
G.SEGMENT5 <= NVL('z', G.SEGMENT5))
AND (G.SEGMENT6 >= NVL('0', G.SEGMENT6) AND
G.SEGMENT6 <= NVL('z', G.SEGMENT6))
AND (G.SEGMENT7 >= NVL('0', G.SEGMENT7) AND
G.SEGMENT7 <= NVL('z', G.SEGMENT7))
AND (G.SEGMENT8 >= NVL('0', G.SEGMENT8) AND
G.SEGMENT8 <= NVL('z', G.SEGMENT8))
AND (G.SEGMENT9 >= NVL('0', G.SEGMENT9) AND
G.SEGMENT9 <= NVL('z', G.SEGMENT9))
AND (G.SEGMENT10 >= NVL('0', G.SEGMENT10) AND
G.SEGMENT10 <= NVL('z', G.SEGMENT10))
AND H.LEDGER_ID = 2023
AND H.PERIOD_NAME BETWEEN '2014-04' AND '2014-04'
AND FV.FLEX_VALUE_SET_ID = 1014875
AND FV.FLEX_VALUE = G.SEGMENT2
/* AND apps.GL_SECURITY_PKG.VALIDATE_SEGVAL(1, NULL, G.SEGMENT1, NULL, NULL) =
'TRUE'*/
AND (H.STATUS = 'P' OR ('Y' = 'N' AND H.STATUS <> 'P'))
ORDER BY PERIOD_NAME, USER_JE_SOURCE_NAME, SEGMENT1, EXTERNAL_REFERENCE
小结：
临时表的读写同样会有热块问题，大量的全表扫描会产生latch等待，性能受到影响，上面就是一个例子。同时大量的使用索引，也会带来热块问题，即索引热块，如并发较大时，这种情况需要根据具体的数据量，业务逻辑，修改sql写法或添加hint，适当使用全表扫描也是非常好的解决办法。
