Chinaunix首页 | 论坛 | 博客
  • 博客访问: 55297
  • 博文数量: 9
  • 博客积分: 30
  • 博客等级: 民兵
  • 技术积分: 120
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-17 22:24
文章分类

全部博文(9)

文章存档

2022年(5)

2014年(4)

我的朋友

分类: Oracle

2014-06-24 15:48:44

1.3 热表热块

寿险财务,集团财务均有因为该问题导致性能下降,系统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,适当使用全表扫描也是非常好的解决办法。

 

阅读(2081) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~