张沛

0 积分	1 好友	29 主题

发消息

[RAC性能调优] sql语句优化问题

1^#

发表于 2013-1-5 11:54:30 | 查看: 2767| 回复: 1

本帖最后由张沛于 2013-1-5 12:24 编辑

SELECT 222 APPLICATION_ID,
   CT.set_of_books_id SET_OF_BOOKS_ID,
   CT.org_id ORG_ID,
   CTT.type TRX_CLASS,
   L1.meaning TRX_CLASS_NAME,
   CT.cust_trx_type_id TRX_TYPE_N,
   CTT.name TRX_TYPE_NAME,
   CT.trx_number TRX_NUMBER_DISPLAYED,
   CT.trx_number TRX_NUMBER_C,
   CT.trx_date TRX_DATE,
   CTLGD.comments COMMENTS,
   CT.doc_sequence_id DOC_SEQUENCE_ID,
   FD.name DOC_SEQUENCE_NAME,
   CT.doc_sequence_value DOC_SEQUENCE_VALUE,
   'CT' TRX_HDR_TABLE,
   CT.customer_trx_id TRX_HDR_ID,
   CTLGD.account_class ACCT_LINE_TYPE,
   L3.meaning ACCT_LINE_TYPE_NAME,
   CTLGD.code_combination_id CODE_COMBINATION_ID,
   ct.invoice_currency_code CURRENCY_CODE,
   to_number(decode(CTLGD.account_class,
                     'REC',
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           null,
                           nvl(CTLGD.amount, 0)),
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           -nvl(CTLGD.amount, 0),
                           null))) ENTERED_DR,
   to_number(decode(CTLGD.account_class,
                     'REC',
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           -nvl(CTLGD.amount, 0),
                           null),
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           null,
                           nvl(CTLGD.amount, 0)))) ENTERED_CR,
   to_number(decode(CTLGD.account_class,
                     'REC',
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           null,
                           nvl(CTLGD.acctd_amount, 0)),
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           -nvl(CTLGD.acctd_amount, 0),
                           null))) ACCOUNTED_DR,
   to_number(decode(CTLGD.account_class,
                     'REC',
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           -nvl(CTLGD.acctd_amount, 0),
                           null),
                     decode(sign(nvl(CTLGD.amount, 0)),
                           -1,
                           null,
                           nvl(CTLGD.acctd_amount, 0)))) ACCOUNTED_CR,
   ct.exchange_date CURRENCY_CONVERSION_DATE,
   ct.exchange_rate_type CURRENCY_CONVERSION_TYPE,
   glct.user_conversion_type CURRENCY_USER_CONVERSION_TYPE,
   ct.exchange_rate CURRENCY_CONVERSION_RATE,
   'C' THIRD_PARTY_TYPE,
   ct.bill_to_customer_id THIRD_PARTY_ID,
   cust_acct.account_number THIRD_PARTY_NUMBER,
   substrb(party.party_name, 1, 50) THIRD_PARTY_NAME,
   ct.bill_to_site_use_id THIRD_PARTY_SUB_ID,
   SU.location THIRD_PARTY_SUB_NAME,
   CTLGD.gl_date ACCOUNTING_DATE,
   L4.meaning GL_TRANSFER_STATUS_NAME,
   decode(CTLGD.posting_control_id, -3, 'N', 'Y') GL_TRANSFER_STATUS,
   decode(CTLGD.account_class, 'REC', 'CT', 'CTLGD') SOURCE_TABLE,
   decode(CTLGD.account_class,
            'REC',
            ct.customer_trx_id,
            CTLGD.cust_trx_line_gl_dist_id) SOURCE_ID,
   RR.name ACCOUNTING_RULE_NAME,
   CTLGD.cust_trx_line_gl_dist_id AEL_ID,
   decode(CTL.line_number,
            null,
            L1.meaning || ' ' || CT.trx_number,
            decode(CTL2.line_number,
                  null,
                  decode(CTL.line_number,
                        null,
                        null,
                        L5.meaning || ' ' || to_number(CTL.line_number)),
                  L5.meaning || ' ' || to_number(CTL2.line_number) || ', ' ||
                  L6.meaning || ' ' || CTL.line_number)) AE_LINE_REFERENCE,
   decode(CTL.line_number,
            null,
            rpad(L1.meaning, 80) || ' ' || rpad(CT.trx_number, 20),
            decode(CTL2.line_number,
                  null,
                  decode(CTL.line_number,
                        null,
                        null,
                        L5.meaning || ' ' ||
                        lpad(to_number(CTL.line_number), 15, '0')),
                  L5.meaning || ' ' ||
                  lpad(to_number(CTL2.line_number), 15, '0') || ', ' ||
                  L6.meaning || ' ' ||
                  lpad(to_number(CTL.line_number), 15, '0'))) AE_LINE_REFERENCE_INTERNAL,
   'CTLGD' AEL_TABLE,
   CTLGD.last_update_date LAST_UPDATE_DATE,
   CTLGD.last_updated_by LAST_UPDATED_BY,
   CTLGD.creation_date CREATION_DATE,
   CTLGD.created_by CREATED_BY,
   CTLGD.last_update_login LAST_UPDATE_LOGIN,
   CTLGD.request_id REQUEST_ID,
   CTLGD.program_application_id PROGRAM_APPLICATION_ID,
   CTLGD.program_id PROGRAM_ID,
   CTLGD.program_update_date PROGRAM_UPDATE_DATE, /* The following columns are specific to AR Transaction */
   BS.name TRX_SOURCE_NAME,
   CT.batch_source_id TRX_SOURCE_ID,
   CTL.tax_exempt_number TAX_EXEMPT_NUMBER,
   CTL.inventory_item_id INVENTORY_ITEM_ID,
   decode(CTL2.line_number, null, to_number(null), CTL.line_number) TRX_DETAIL_LINE_NUMBER,
   decode(CTL2.line_number, null, CTL.line_number, CTL2.line_number) TRX_LINE_NUMBER,
   CTL.line_type TRX_LINE_TYPE,
   L2.meaning TRX_LINE_TYPE_NAME,
   CTL.quantity_invoiced TRX_QUANTITY,
   CTL.sales_order SALES_ORDER_NUMBER,
   S.name SALESREP_NAME,
   AVT.tax_code TAX_CODE,
   CTL.vat_tax_id TAX_CODE_ID,
   CTL.tax_rate TAX_RATE,
   CTL.unit_selling_price UNIT_SELLING_PRICE,
   MUOM.unit_of_measure TRX_UOM,
   to_date(null) APPLICATION_DATE,
   null APPLIED_TO_TRX_HDR_TABLE,
   to_number(null) APPLIED_TO_TRX_HDR_ID,
   null APPLIED_TO_TRX_HDR_NUMBER_C,
   null APPLIED_TO_TRX_HDR_NUMBER_DISP,
   null APPLIED_TO_TRX_HDR_CURRENCY,
   to_date(null) APPLIED_TO_TRX_HDR_DATE,
   null APPLIED_TO_TRX_LINE_TYPE_NAME,
   null APPLIED_TO_TRX_LINE_TYPE,
   to_number(null) APPLIED_TO_TRX_LINE_NUMBER,
   CT.Attribute11 pzbh,
   CTL.Description,
   CT.CREATED_FROM,
   CTLGD.AMOUNT,
   CT.CT_REFERENCE  --参考

  FROM ra_customer_trx_all       CT,
   ra_customer_trx_lines_all CTL,
   ra_cust_trx_line_gl_dist_all CTLGD,
   ra_customer_trx_lines_all CTL2,
   ar_vat_tax_all             AVT,
   mtl_units_of_measure       MUOM,
   ra_rules                   RR,
   ra_salesreps_all          S,
   ra_cust_trx_types_all       CTT,
   gl_daily_conversion_types GLCT,
   hz_cust_site_uses_all       SU,
   hz_cust_accounts          CUST_ACCT,
   hz_parties                PARTY,
   ra_batch_sources_all       BS,
   fnd_document_sequences    FD,
   ar_lookups                L6,
   ar_lookups                L2,
   ar_lookups                L5,
   ar_lookups                L4,
   ar_lookups                L1,
   ar_lookups                L3

WHERE L6.lookup_code = 'DETAIL_LINE'
AND L6.lookup_type = 'VIEW_ACCOUNTING'
AND L5.lookup_code = 'LINE'
AND L5.lookup_type = 'VIEW_ACCOUNTING'
AND L3.lookup_type = decode(CTLGD.collected_tax_ccid,
                           null,
                           'AUTOGL_TYPE',
                           'DISTRIBUTION_SOURCE_TYPE')
AND L3.lookup_code = decode(CTLGD.collected_tax_ccid,
                           null,
                           nvl(CTLGD.account_class, 'REV'),
                           'DEFERRED_TAX')
AND S.salesrep_id(+) = CTLGD.cust_trx_line_salesrep_id
AND /* Outer join workaround(ORA-1417): Since we cannot outer join to another table for the org_id, the following is decode is used */
   nvl(CT.org_id, -99) =
   decode(S.salesrep_id, null, nvl(CT.org_id, -99), nvl(S.org_id, -99))
AND AVT.vat_tax_id(+) = CTL.vat_tax_id
AND nvl(AVT.org_id(+), -99) = nvl(CTL.org_id, -99)
AND MUOM.uom_code(+) = CTL.uom_code
AND RR.rule_id(+) = CTL.accounting_rule_id
AND L4.lookup_code = decode(CTLGD.posting_control_id, -3, 'N', 'Y')
AND L4.lookup_type = 'YES/NO'
AND L2.lookup_code(+) = CTL.line_type
AND L2.lookup_type(+) = 'STD_LINE_TYPE'
AND CT.doc_sequence_id = FD.doc_sequence_id(+)
AND L1.lookup_code = CTT.type
AND L1.lookup_type = 'INV/CM'
AND CT.cust_trx_type_id = CTT.cust_trx_type_id
AND nvl(CT.org_id, -99) = nvl(CTT.org_id, -99)
AND CT.exchange_rate_type = GLCT.conversion_type(+)
AND CT.bill_to_site_use_id = SU.site_use_id
AND CT.bill_to_customer_id = CUST_ACCT.cust_account_id
AND CUST_ACCT.party_id = party.party_id
AND CT.batch_source_id = BS.batch_source_id
AND nvl(CT.org_id, -99) = nvl(BS.org_id, -99)
AND CTL.link_to_cust_trx_line_id = CTL2.customer_trx_line_id(+)
AND nvl(CTL.org_id, -99) = nvl(CTL2.org_id(+), -99)
AND CTLGD.customer_trx_line_id = CTL.customer_trx_line_id(+)
AND nvl(CTLGD.org_id, -99) = nvl(CTL.org_id(+), -99)
AND CTLGD.account_set_flag = 'N'
AND CT.customer_trx_id = CTLGD.customer_trx_id
AND nvl(CT.org_id, -99) = nvl(CTLGD.org_id, -99)
这是语句，执行计划用附件方式添加
问题是，执行计划中红体字部分怎么优化？28,37两步？