1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Tune the following Query

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 30, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have below Query.

    SELECT to_char(aeh.accounting_date
    ,'YYYY-MM-DD') GL_DATE
    ,(case when gjst.user_je_source_name='Assets' and gjct.user_je_category_name in ('Addition','Transfer','Reclass','Retirement','Adjustment')
    then (select max(asset_number) from fa_transaction_history_trx_v
    where transaction_header_id=ent.transaction_number)
    when gjst.user_je_source_name='Assets' and gjct.user_je_category_name='Depreciation'
    then to_char(ent.source_id_int_1)
    else ent.transaction_number end) TRANSACTION_NUMBER
    ,to_char(xle.transaction_date
    ,'YYYY-MM-DD"T"hh:mi:ss') TRANSACTION_DATE
    ,aeh.doc_sequence_value DOCUMENT_SEQUENCE_NUMBER
    ,aeh.ae_header_id HEADER_ID
    ,aeh.description HEADER_DESCRIPTION
    ,gjct.user_je_category_name JE_CATEGORY_NAME
    ,gjst.user_je_source_name JE_SOURCE_NAME
    ,gjh.NAME GL_JE_NAME
    ,ael.description LINE_DESCRIPTION
    ,ael.currency_code ENTERED_CURRENCY
    ,ael.entered_dr ENTERED_DR
    ,ael.entered_cr ENTERED_CR
    ,ael.accounted_dr ACCOUNTED_DR
    ,ael.accounted_cr ACCOUNTED_CR
    ,NULL PARTY_TYPE
    ,glbgt.ledger_name LEDGER_NAME
    ,glbgt.ledger_currency LEDGER_CURRENCY
    ,glbgt.period_year PERIOD_YEAR
    ,glbgt.period_number PERIOD_NUMBER
    ,glbgt.balance_type_code BALANCE_TYPE_CODE
    ,glbgt.budget_name BUDGET_NAME
    ,glbgt.encumbrance_type ENCUMBRANCE_TYPE
    ,glbgt.begin_balance_dr BEGIN_BALANCE_DR
    ,glbgt.begin_balance_cr BEGIN_BALANCE_CR
    ,glbgt.period_net_dr PERIOD_NET_DR
    ,glbgt.period_net_cr PERIOD_NET_CR
    ,glbgt.accounting_code_combination ACCOUNTING_CODE_COMBINATION
    ,glbgt.code_combination_description CODE_COMBINATION_DESCRIPTION
    ,(case when gjst.user_je_source_name='Projects' then
    (select max(customer_name) from pa_draft_revenues_v where project_id=ent.source_id_int_1)
    WHEN gjst.user_je_source_name='Cost Management' then
    (SELECT vendor_name FROM rcv_transactions rt ,ap_suppliers asp,rcv_receiving_sub_ledger rrsl,xla_distribution_links xdl
    where rt.vendor_id=asp.vendor_id and rrsl.rcv_transaction_id=rt.transaction_id
    and rrsl.rcv_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.ae_header_id=aeh.ae_header_id
    and xdl.ae_line_num=ael.ae_line_num)
    else
    (select max(vendor_name) from fa_invoice_details_v
    where to_char(asset_id)=(case when gjst.user_je_source_name='Assets' and gjct.user_je_category_name in ('Addition','Transfer','Reclass','Retirement','Adjustment')
    then (select max(asset_number) from fa_transaction_history_trx_v
    where transaction_header_id=ent.transaction_number)
    when gjst.user_je_source_name='Assets' and gjct.user_je_category_name='Depreciation'
    then to_char(ent.source_id_int_1)
    else ent.transaction_number end)) end) THIRD_PARTY_NAME
    FROM xla_ae_headers aeh
    ,xla_ae_lines ael
    ,xla_lookups xlk1
    ,xla_lookups xlk2
    ,xla_events xle
    ,xla_event_classes_tl xect
    ,xla_event_types_tl xet
    ,fnd_user fdu
    ,xla_transaction_entities ent
    ,fnd_application_tl fap
    ,fun_seq_versions fsv1
    ,fun_seq_versions fsv2
    ,fnd_document_sequences fns
    ,gl_je_categories_tl gjct
    ,gl_je_sources_tl gjst
    ,gl_daily_conversion_types gdct
    ,gl_import_references gir
    ,gl_je_lines gjl
    ,gl_je_headers gjh
    ,gl_je_batches gjb
    ,xx_xla_report_balances_gt glbgt

    WHERE 1=1
    AND ael.ae_header_id = aeh.ae_header_id
    AND ael.application_id = aeh.application_id
    AND aeh.accounting_entry_status_code = 'F'
    AND aeh.gl_transfer_status_code = 'Y'
    AND xlk2.lookup_code = ael.accounting_class_code
    AND xlk1.lookup_code(+) = aeh.funds_status_code
    AND xlk2.lookup_type = 'XLA_ACCOUNTING_CLASS'
    AND xlk1.lookup_type(+) = 'XLA_FUNDS_STATUS'
    AND xle.application_id = aeh.application_id
    AND xle.event_id = aeh.event_id
    AND xet.application_id = aeh.application_id
    AND xet.event_type_code = aeh.event_type_code
    AND xet.LANGUAGE = USERENV('LANG')
    AND xect.application_id = xet.application_id
    AND xect.entity_code = xet.entity_code
    AND xect.event_class_code = xet.event_class_code
    AND xect.LANGUAGE = USERENV('LANG')
    AND ent.application_id = aeh.application_id
    AND ent.entity_id = aeh.entity_id
    AND fdu.user_id = ent.created_by
    AND fap.application_id = aeh.application_id
    AND fap.LANGUAGE = USERENV('LANG')
    AND fsv1.seq_version_id(+) = aeh.completion_acct_seq_version_id
    AND fsv2.seq_version_id(+) = aeh.close_acct_seq_version_id
    AND fns.application_id(+) = aeh.application_id
    AND fns.doc_sequence_id(+) = aeh.doc_sequence_id
    AND gdct.conversion_type(+) = ael.currency_conversion_type
    AND gjct.je_category_name = aeh.je_category_name
    AND gjct.LANGUAGE = USERENV('LANG')
    AND gjst.je_source_name = gjh.je_source
    AND gjst.LANGUAGE = USERENV('LANG')
    AND gjl.ledger_id = glbgt.ledger_id
    AND gjl.code_combination_id = glbgt.code_combination_id
    AND gjl.effective_date BETWEEN glbgt.period_start_date AND glbgt.period_end_date
    AND gjl.effective_date BETWEEN :p_GL_DATE_FROM AND :p_GL_DATE_TO
    AND gjl.period_name = glbgt.period_name
    AND gjl.je_header_id = gjh.je_header_id
    AND gjl.period_name = gjh.period_name
    AND gjl.je_header_id = gir.je_header_id
    AND gjl.je_line_num = gir.je_line_num
    AND gjh.je_header_id = gir.je_header_id
    AND gjh.status = 'P'
    AND NVL(gjh.je_from_sla_flag,'N') IN('Y','U')
    AND gjb.je_batch_id = gir.je_batch_id
    AND gjb.status = 'P'
    AND gir.gl_sl_link_id = ael.gl_sl_link_id
    AND gir.gl_sl_link_table = ael.gl_sl_link_table
    AND aeh.balance_type_code = glbgt.balance_type_code
    AND NVL(aeh.budget_version_id,-19999) = NVL(glbgt.budget_version_id,-19999)
    AND NVL(ael.encumbrance_type_id,-19999)= NVL(glbgt.encumbrance_type_id,-19999)


    I need to tune above Query to increase it's performance.

    Please help me...
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    When I add below query It took double time to execute than earlier Query.

    ,(case when gjst.user_je_source_name='Projects' then
    (select max(customer_name) from pa_draft_revenues_v where project_id=ent.source_id_int_1)
    WHEN gjst.user_je_source_name='Cost Management' then
    (SELECT vendor_name FROM rcv_transactions rt ,ap_suppliers asp,rcv_receiving_sub_ledger rrsl,xla_distribution_links xdl
    where rt.vendor_id=asp.vendor_id and rrsl.rcv_transaction_id=rt.transaction_id
    and rrsl.rcv_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.ae_header_id=aeh.ae_header_id
    and xdl.ae_line_num=ael.ae_line_num)
    else
    (select max(vendor_name) from fa_invoice_details_v
    where to_char(asset_id)=(case when gjst.user_je_source_name='Assets' and gjct.user_je_category_name in ('Addition','Transfer','Reclass','Retirement','Adjustment')
    then (select max(asset_number) from fa_transaction_history_trx_v
    where transaction_header_id=ent.transaction_number)
    when gjst.user_je_source_name='Assets' and gjct.user_je_category_name='Depreciation'
    then to_char(ent.source_id_int_1)
    else ent.transaction_number end)) end) THIRD_PARTY_NAME
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    You can provide here the plan of the query?
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Are you asking Explain Plan of SQl Query?

    I am not able to copy that Explain Plan results from Sql developer.
     
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 938 | 638 (1)| | |
    | 1 | SORT AGGREGATE | | 1 | 260 | | | |
    | 2 | NESTED LOOPS | | | | | | |
    | 3 | NESTED LOOPS | | 1 | 260 | 25 (0)| | |
    | 4 | NESTED LOOPS | | 1 | 232 | 11 (0)| | |
    | 5 | NESTED LOOPS | | 1 | 222 | 11 (0)| | |
    | 6 | NESTED LOOPS | | 1 | 214 | 11 (0)| | |
    | 7 | NESTED LOOPS | | 1 | 209 | 11 (0)| | |
    | 8 | NESTED LOOPS | | 1 | 191 | 9 (0)| | |
    | 9 | NESTED LOOPS | | 1 | 174 | 8 (0)| | |
    | 10 | NESTED LOOPS | | 1 | 146 | 7 (0)| | |
    | 11 | NESTED LOOPS | | 1 | 115 | 2 (0)| | |
    | 12 | NESTED LOOPS | | 1 | 83 | 2 (0)| | |
    | 13 | TABLE ACCESS BY INDEX ROWID | FA_TRANSACTION_HEADERS | 1 | 54 | 2 (0)| | |
    |* 14 | INDEX UNIQUE SCAN | FA_TRANSACTION_HEADERS_U1 | 1 | | 1 (0)| | |
    |* 15 | INDEX UNIQUE SCAN | FA_LOOKUPS_B_U1 | 1 | 29 | 0 (0)| | |
    |* 16 | INDEX UNIQUE SCAN | FA_LOOKUPS_TL_U1 | 1 | 32 | 0 (0)| | |
    |* 17 | TABLE ACCESS FULL | FA_DEPRN_PERIODS | 1 | 31 | 5 (0)| | |
    | 18 | TABLE ACCESS BY INDEX ROWID | FA_BOOK_CONTROLS | 1 | 28 | 1 (0)| | |
    |* 19 | INDEX UNIQUE SCAN | FA_BOOK_CONTROLS_U1 | 1 | | 0 (0)| | |
    | 20 | TABLE ACCESS BY INDEX ROWID | FA_ADDITIONS_B | 1 | 17 | 1 (0)| | |
    |* 21 | INDEX UNIQUE SCAN | FA_ADDITIONS_B_U1 | 1 | | 0 (0)| | |
    | 22 | TABLE ACCESS BY INDEX ROWID | FA_ASSET_HISTORY | 1 | 18 | 2 (0)| | |
    |* 23 | INDEX RANGE SCAN | FA_ASSET_HISTORY_N3 | 1 | | 1 (0)| | |
    |* 24 | INDEX UNIQUE SCAN | FA_CATEGORIES_B_U1 | 1 | 5 | 0 (0)| | |
    |* 25 | INDEX UNIQUE SCAN | FA_CATEGORIES_TL_U1 | 1 | 8 | 0 (0)| | |
    |* 26 | INDEX UNIQUE SCAN | FA_ADDITIONS_TL_U1 | 1 | 10 | 0 (0)| | |
    |* 27 | INDEX RANGE SCAN | FA_CALENDAR_PERIODS_U2 | 808 | | 4 (0)| | |
    |* 28 | TABLE ACCESS BY INDEX ROWID | FA_CALENDAR_PERIODS | 515 | 14420 | 14 (0)| | |
    | 29 | SORT AGGREGATE | | 1 | 195 | | | |
    | 30 | VIEW | PA_DRAFT_REVENUES_V | 1 | 195 | 5 (100)| | |
    | 31 | SORT GROUP BY | | 1 | 576 | | | |
    |* 32 | FILTER | | | | | | |
    | 33 | NESTED LOOPS | | | | | | |
    | 34 | NESTED LOOPS | | 1 | 576 | 25 (0)| | |
    | 35 | NESTED LOOPS | | 1 | 553 | 24 (0)| | |
    | 36 | NESTED LOOPS | | 1 | 524 | 23 (0)| | |
    | 37 | NESTED LOOPS | | 1 | 499 | 22 (0)| | |
    | 38 | NESTED LOOPS | | 1 | 475 | 20 (0)| | |
    | 39 | NESTED LOOPS | | 1 | 418 | 18 (0)| | |
    | 40 | NESTED LOOPS | | 1 | 233 | 11 (0)| | |
    | 41 | NESTED LOOPS | | 1 | 176 | 8 (0)| | |
    | 42 | NESTED LOOPS | | 1 | 148 | 7 (0)| | |
    | 43 | NESTED LOOPS | | 1 | 113 | 6 (0)| | |
    | 44 | NESTED LOOPS | | 1 | 107 | 5 (0)| | |
    | 45 | NESTED LOOPS | | 1 | 84 | 4 (0)| | |
    | 46 | TABLE ACCESS BY INDEX ROWID | PA_PROJECTS_ALL | 1 | 84 | 2 (0)| | |
    |* 47 | INDEX UNIQUE SCAN | PA_PROJECTS_U1 | 1 | | 1 (0)| | |
    | 48 | FAST DUAL | | 1 | | 2 (0)| | |
    | 49 | TABLE ACCESS BY INDEX ROWID | PA_PROJECT_TYPES_ALL | 85 | 1955 | 1 (0)| | |
    |* 50 | INDEX UNIQUE SCAN | PA_PROJECT_TYPES_U1 | 1 | | 0 (0)| | |
    |* 51 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 43 | 258 | 1 (0)| | |
    |* 52 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)| | |
    |* 53 | TABLE ACCESS BY INDEX ROWID | PA_PROJECT_STATUSES | 5 | 175 | 1 (0)| | |
    |* 54 | INDEX UNIQUE SCAN | PA_PROJECT_STATUSES_U2 | 1 | | 0 (0)| | |
    | 55 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 28 | 1 (0)| | |
    |* 56 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)| | |
    | 57 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 57 | 3 (0)| | |
    |* 58 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| | |
    |* 59 | TABLE ACCESS BY INDEX ROWID | PA_DRAFT_REVENUES_ALL | 3 | 555 | 7 (0)| | |
    |* 60 | INDEX RANGE SCAN | PA_DRAFT_REVENUES_N12 | 5 | | 1 (0)| | |
    | 61 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 57 | 2 (0)| | |
    |* 62 | INDEX UNIQUE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 1 (0)| | |
    | 63 | TABLE ACCESS BY INDEX ROWID | PA_DRAFT_REVENUE_ITEMS | 1 | 24 | 2 (0)| | |
    |* 64 | INDEX RANGE SCAN | PA_DRAFT_REVENUE_ITEMS_U1 | 1 | | 1 (0)| | |
    | 65 | TABLE ACCESS BY INDEX ROWID | PA_AGREEMENTS_ALL | 1 | 25 | 1 (0)| | |
    |* 66 | INDEX UNIQUE SCAN | PA_AGREEMENTS_U1 | 1 | | 0 (0)| | |
    | 67 | TABLE ACCESS BY INDEX ROWID | HZ_CUST_ACCOUNTS | 1 | 29 | 1 (0)| | |
    |* 68 | INDEX UNIQUE SCAN | HZ_CUST_ACCOUNTS_U1 | 1 | | 0 (0)| | |
    |* 69 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | | 0 (0)| | |
    | 70 | TABLE ACCESS BY INDEX ROWID | HZ_PARTIES | 1 | 23 | 1 (0)| | |
    | 71 | NESTED LOOPS | | | | | | |
    | 72 | NESTED LOOPS | | 1 | 66 | 44 (0)| | |
    | 73 | NESTED LOOPS | | 1 | 39 | 43 (0)| | |
    | 74 | NESTED LOOPS | | 1 | 28 | 42 (0)| | |
    | 75 | PARTITION LIST ALL | | 1 | 16 | 40 (0)| 1 | 19 |
    | 76 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_DISTRIBUTION_LINKS | 1 | 16 | 40 (0)| 1 | 19 |
    |* 77 | INDEX RANGE SCAN | XLA_DISTRIBUTION_LINKS_N3 | 1 | | 39 (0)| 1 | 19 |
    | 78 | TABLE ACCESS BY INDEX ROWID | RCV_RECEIVING_SUB_LEDGER | 1 | 12 | 2 (0)| | |
    |* 79 | INDEX RANGE SCAN | RCV_RECEIVING_SUB_LEDGER_N3 | 1 | | 1 (0)| | |
    | 80 | TABLE ACCESS BY INDEX ROWID | RCV_TRANSACTIONS | 1 | 11 | 1 (0)| | |
    |* 81 | INDEX UNIQUE SCAN | RCV_TRANSACTIONS_U1 | 1 | | 0 (0)| | |
    |* 82 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | | 0 (0)| | |
    | 83 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 27 | 1 (0)| | |
    | 84 | SORT AGGREGATE | | 1 | 48 | | | |
    |* 85 | FILTER | | | | | | |
    |* 86 | HASH JOIN | | 7955 | 372K| 257 (3)| | |
    | 87 | NESTED LOOPS | | 3287 | 118K| 44 (5)| | |
    | 88 | TABLE ACCESS FULL | AP_SUPPLIERS | 3287 | 102K| 43 (3)| | |
    |* 89 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 5 | 0 (0)| | |
    |* 90 | TABLE ACCESS FULL | FA_ASSET_INVOICES | 7955 | 87505 | 212 (2)| | |
    | 91 | SORT AGGREGATE | | 1 | 260 | | | |
    | 92 | NESTED LOOPS | | | | | | |
    | 93 | NESTED LOOPS | | 1 | 260 | 25 (0)| | |
    | 94 | NESTED LOOPS | | 1 | 232 | 11 (0)| | |
    | 95 | NESTED LOOPS | | 1 | 222 | 11 (0)| | |
    | 96 | NESTED LOOPS | | 1 | 214 | 11 (0)| | |
    | 97 | NESTED LOOPS | | 1 | 209 | 11 (0)| | |
    | 98 | NESTED LOOPS | | 1 | 191 | 9 (0)| | |
    | 99 | NESTED LOOPS | | 1 | 174 | 8 (0)| | |
    | 100 | NESTED LOOPS | | 1 | 146 | 7 (0)| | |
    | 101 | NESTED LOOPS | | 1 | 115 | 2 (0)| | |
    | 102 | NESTED LOOPS | | 1 | 83 | 2 (0)| | |
    | 103 | TABLE ACCESS BY INDEX ROWID | FA_TRANSACTION_HEADERS | 1 | 54 | 2 (0)| | |
    |*104 | INDEX UNIQUE SCAN | FA_TRANSACTION_HEADERS_U1 | 1 | | 1 (0)| | |
    |*105 | INDEX UNIQUE SCAN | FA_LOOKUPS_B_U1 | 1 | 29 | 0 (0)| | |
    |*106 | INDEX UNIQUE SCAN | FA_LOOKUPS_TL_U1 | 1 | 32 | 0 (0)| | |
    |*107 | TABLE ACCESS FULL | FA_DEPRN_PERIODS | 1 | 31 | 5 (0)| | |
    | 108 | TABLE ACCESS BY INDEX ROWID | FA_BOOK_CONTROLS | 1 | 28 | 1 (0)| | |
    |*109 | INDEX UNIQUE SCAN | FA_BOOK_CONTROLS_U1 | 1 | | 0 (0)| | |
    | 110 | TABLE ACCESS BY INDEX ROWID | FA_ADDITIONS_B | 1 | 17 | 1 (0)| | |
    |*111 | INDEX UNIQUE SCAN | FA_ADDITIONS_B_U1 | 1 | | 0 (0)| | |
    | 112 | TABLE ACCESS BY INDEX ROWID | FA_ASSET_HISTORY | 1 | 18 | 2 (0)| | |
    |*113 | INDEX RANGE SCAN | FA_ASSET_HISTORY_N3 | 1 | | 1 (0)| | |
    |*114 | INDEX UNIQUE SCAN | FA_CATEGORIES_B_U1 | 1 | 5 | 0 (0)| | |
    |*115 | INDEX UNIQUE SCAN | FA_CATEGORIES_TL_U1 | 1 | 8 | 0 (0)| | |
    |*116 | INDEX UNIQUE SCAN | FA_ADDITIONS_TL_U1 | 1 | 10 | 0 (0)| | |
    |*117 | INDEX RANGE SCAN | FA_CALENDAR_PERIODS_U2 | 808 | | 4 (0)| | |
    |*118 | TABLE ACCESS BY INDEX ROWID | FA_CALENDAR_PERIODS | 515 | 14420 | 14 (0)| | |
    |*119 | FILTER | | | | | | |
    | 120 | NESTED LOOPS OUTER | | 1 | 938 | 638 (1)| | |
    | 121 | NESTED LOOPS | | 1 | 902 | 637 (1)| | |
    | 122 | NESTED LOOPS | | 1 | 866 | 636 (1)| | |
    | 123 | NESTED LOOPS | | 1 | 848 | 634 (1)| | |
    | 124 | NESTED LOOPS | | 1 | 844 | 634 (1)| | |
    | 125 | NESTED LOOPS OUTER | | 1 | 813 | 632 (1)| | |
    | 126 | NESTED LOOPS | | 1 | 805 | 631 (1)| | |
    | 127 | NESTED LOOPS | | 1 | 768 | 631 (1)| | |
    | 128 | NESTED LOOPS | | 1 | 713 | 630 (1)| | |
    | 129 | NESTED LOOPS | | 1 | 706 | 630 (1)| | |
    | 130 | NESTED LOOPS | | 1 | 676 | 629 (1)| | |
    | 131 | NESTED LOOPS | | 1 | 482 | 628 (1)| | |
    | 132 | NESTED LOOPS | | 1 | 390 | 589 (1)| | |
    | 133 | NESTED LOOPS | | 1 | 382 | 588 (1)| | |
    |*134 | HASH JOIN | | 85 | 30005 | 402 (1)| | |
    |*135 | TABLE ACCESS FULL | GL_JE_SOURCES_TL | 85 | 2635 | 6 (0)| | |
    | 136 | NESTED LOOPS | | | | | | |
    | 137 | NESTED LOOPS | | 85 | 27370 | 395 (1)| | |
    |*138 | HASH JOIN | | 85 | 22185 | 310 (1)| | |
    | 139 | TABLE ACCESS BY INDEX ROWID | GL_JE_LINES | 694 | 24290 | 104 (0)| | |
    |*140 | INDEX RANGE SCAN | TESTINDX6 | 1250 | | 6 (0)| | |
    |*141 | TABLE ACCESS FULL | XX_XLA_REPORT_BALANCES_GT | 335 | 75710 | 205 (1)| | |
    |*142 | INDEX UNIQUE SCAN | GL_JE_HEADERS_U1 | 1 | | 0 (0)| | |
    |*143 | TABLE ACCESS BY INDEX ROWID | GL_JE_HEADERS | 1 | 61 | 1 (0)| | |
    | 144 | TABLE ACCESS BY INDEX ROWID | GL_IMPORT_REFERENCES | 1 | 29 | 3 (0)| | |
    |*145 | INDEX RANGE SCAN | GL_IMPORT_REFERENCES_N1 | 1 | | 2 (0)| | |
    |*146 | TABLE ACCESS BY INDEX ROWID | GL_JE_BATCHES | 1 | 8 | 1 (0)| | |
    |*147 | INDEX UNIQUE SCAN | GL_JE_BATCHES_U1 | 1 | | 0 (0)| | |
    | 148 | PARTITION LIST ALL | | 1 | 92 | 39 (0)| 1 | 19 |
    |*149 | TABLE ACCESS BY LOCAL INDEX ROWID| XLA_AE_LINES | 1 | 92 | 39 (0)| 1 | 19 |
    |*150 | INDEX RANGE SCAN | XLA_AE_LINES_N4 | 1 | | 38 (0)| 1 | 19 |
    | 151 | PARTITION LIST ITERATOR | | 1 | 194 | 1 (0)| KEY | KEY |
    |*152 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS | 1 | 194 | 1 (0)| KEY | KEY |
    |*153 | INDEX UNIQUE SCAN | XLA_AE_HEADERS_U1 | 1 | | 0 (0)| KEY | KEY |
    | 154 | TABLE ACCESS BY INDEX ROWID | GL_JE_CATEGORIES_TL | 1 | 30 | 1 (0)| | |
    |*155 | INDEX UNIQUE SCAN | GL_JE_CATEGORIES_TL_U1 | 1 | | 0 (0)| | |
    |*156 | INDEX UNIQUE SCAN | FND_APPLICATION_TL_U1 | 1 | 7 | 0 (0)| | |
    |*157 | INDEX RANGE SCAN | XLA_EVENT_TYPES_TL_U1 | 1 | 55 | 1 (0)| | |
    |*158 | INDEX UNIQUE SCAN | XLA_EVENT_CLASSES_TL_U1 | 1 | 37 | 0 (0)| | |
    |*159 | TABLE ACCESS BY INDEX ROWID | FND_DOCUMENT_SEQUENCES | 1 | 8 | 1 (0)| | |
    |*160 | INDEX UNIQUE SCAN | FND_DOCUMENT_SEQUENCES_U1 | 1 | | 0 (0)| | |
    | 161 | PARTITION LIST ITERATOR | | 1 | 31 | 2 (0)| KEY | KEY |
    |*162 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_TRANSACTION_ENTITIES | 1 | 31 | 2 (0)| KEY | KEY |
    |*163 | INDEX UNIQUE SCAN | XLA_TRANSACTION_ENTITIES_U1 | 1 | | 1 (0)| KEY | KEY |
    |*164 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | 4 | 0 (0)| | |
    | 165 | PARTITION LIST ITERATOR | | 1 | 18 | 2 (0)| KEY | KEY |
    | 166 | TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS | 1 | 18 | 2 (0)| KEY | KEY |
    |*167 | INDEX UNIQUE SCAN | XLA_EVENTS_U1 | 1 | | 1 (0)| KEY | KEY |
    |*168 | INDEX UNIQUE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 36 | 1 (0)| | |
    |*169 | INDEX UNIQUE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 36 | 1 (0)| | |
    ---------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    14 - access("TH"."TRANSACTION_HEADER_ID"=TO_NUMBER:)B1))
    15 - access("LOB"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="TH"."TRANSACTION_TYPE_CODE")
    16 - access("LOTL"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="LOTL"."LOOKUP_CODE" AND
    "LOTL"."LANGUAGE"=USERENV('LANG'))
    17 - filter("TH"."BOOK_TYPE_CODE"="DP"."BOOK_TYPE_CODE" AND "TH"."DATE_EFFECTIVE">="DP"."PERIOD_OPEN_DATE" AND
    "TH"."DATE_EFFECTIVE"<=NVL("DP"."PERIOD_CLOSE_DATE","TH"."DATE_EFFECTIVE"))
    19 - access("DP"."BOOK_TYPE_CODE"="BC"."BOOK_TYPE_CODE")
    21 - access("TH"."ASSET_ID"="ADB"."ASSET_ID")
    23 - access("AH"."ASSET_ID"="ADB"."ASSET_ID" AND "TH"."TRANSACTION_HEADER_ID">="AH"."TRANSACTION_HEADER_ID_IN")
    filter("AH"."TRANSACTION_HEADER_ID_IN"<=TO_NUMBER:)B1) AND
    "TH"."TRANSACTION_HEADER_ID"<NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1) AND
    NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1)>TO_NUMBER:)B2))
    24 - access("B"."CATEGORY_ID"="AH"."CATEGORY_ID")
    25 - access("B"."CATEGORY_ID"="T"."CATEGORY_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
    26 - access("ADB"."ASSET_ID"="ADTL"."ASSET_ID" AND "ADTL"."LANGUAGE"=USERENV('LANG'))
    27 - access("CP"."CALENDAR_TYPE"="BC"."DEPRN_CALENDAR" AND "TH"."TRANSACTION_DATE_ENTERED">="CP"."START_DATE")
    28 - filter("TH"."TRANSACTION_DATE_ENTERED"<="CP"."END_DATE")
    32 - filter(NULL IS NOT NULL)
    47 - access("PROJECT_ID"=:B1)
    filter("PA_SECURITY"."ALLOW_QUERY"("PROJECT_ID")='Y')
    50 - access("PROJECT_TYPE"="PT"."PROJECT_TYPE" AND "ORG_ID"="PT"."ORG_ID")
    51 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET
    _BUSINESS_GROUP_ID"()))
    52 - access("CARRYING_OUT_ORGANIZATION_ID"="HAO"."ORGANIZATION_ID")
    53 - filter("PS"."STATUS_TYPE"='PROJECT')
    54 - access("PROJECT_STATUS_CODE"="PS"."PROJECT_STATUS_CODE")
    56 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID" AND "HAOTL"."LANGUAGE"=USERENV('LANG'))
    filter(DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('HR_ALL_ORGANIZATION_UNITS',"HAOTL"."ORG
    ANIZATION_ID"))='TRUE')
    58 - access("LOOKUP_TYPE"='TRANSFER STATUS' AND "VIEW_APPLICATION_ID"=275 AND "SECURITY_GROUP_ID"=0 AND
    "LANGUAGE"=USERENV('LANG'))
    filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0)
    59 - filter("LOOKUP_CODE"="R"."TRANSFER_STATUS_CODE")
    60 - access("R"."PROJECT_ID"=:B1)
    62 - access("LOOKUP_TYPE"='INVOICE/REVENUE STATUS' AND "VIEW_APPLICATION_ID"=275 AND
    "LOOKUP_CODE"=DECODE("R"."GENERATION_ERROR_FLAG",'Y','GENERATION
    ERROR',DECODE(INTERNAL_FUNCTION("R"."RELEASED_DATE"),NULL,'UNRELEASED',DECODE("R"."TRANSFER_STATUS_CODE",'P','RELEASED','X','RE
    JECTED IN TRANSFER','T','TRANSFERRED','A','ACCEPTED','R','REJECTED','RECEIVED'))) AND "SECURITY_GROUP_ID"=0 AND
    "LANGUAGE"=USERENV('LANG'))
    64 - access("RI"."PROJECT_ID"=:B1 AND "RI"."DRAFT_REVENUE_NUM"="R"."DRAFT_REVENUE_NUM")
    filter("RI"."PROJECT_ID"="R"."PROJECT_ID")
    66 - access("R"."AGREEMENT_ID"="A"."AGREEMENT_ID")
    68 - access("CUST_ACCT"."CUST_ACCOUNT_ID"="A"."CUSTOMER_ID")
    69 - access("CUST_ACCT"."PARTY_ID"="PARTY"."PARTY_ID")
    77 - access("XDL"."AE_HEADER_ID"=:B1 AND "XDL"."AE_LINE_NUM"=:B2)
    79 - access("RRSL"."RCV_SUB_LEDGER_ID"="XDL"."SOURCE_DISTRIBUTION_ID_NUM_1")
    81 - access("RRSL"."RCV_TRANSACTION_ID"="RT"."TRANSACTION_ID")
    82 - access("RT"."VENDOR_ID"="ASP"."VENDOR_ID")
    85 - filter(TO_CHAR("AI"."ASSET_ID")=CASE WHEN (:)B1='Assets') AND (:)B2='Addition') OR :)B3='Transfer') OR :)B4='Reclass')
    OR :)B5='Retirement') OR :)B6='Adj ustment'))) THEN (SELECT MAX("ADB"."ASSET_NUMBER") FROM "FA"."FA_BOOK_CONTROLS"
    "BC","FA"."FA_ADDITIONS_TL" "ADTL","FA"."FA_ADDITIONS_B" "ADB","FA"."FA_ASSET_HISTORY" "AH","FA"."FA_TRANSACTION_HEADERS"
    "TH","FA"."FA_DEPRN_PERIODS" "DP","FA"."FA_CALENDAR_PERIODS" "CP","FA"."FA_CATEGORIES_B" "B","FA"."FA_CATEGORIES_TL"
    "T","FA"."FA_LOOKUPS_TL" "LOTL","FA"."FA_LOOKUPS_B" "LOB" WHERE "LOB"."LOOKUP_CODE"="TH"."TRANSACTION_TYPE_CODE" AND
    "LOB"."LOOKUP_TYPE"='FAXOLTRX' AND "LOTL"."LANGUAGE"=USERENV('LANG') AND "LOB"."LOOKUP_CODE"="LOTL"."LOOKUP_CODE" AND
    "LOTL"."LOOKUP_TYPE"='FAXOLTRX' AND "T"."LANGUAGE"=USERENV('LANG') AND "B"."CATEGORY_ID"="T"."CATEGORY_ID" AND
    "B"."CATEGORY_ID"="AH"."CATEGORY_ID" AND "TH"."TRANSACTION_DATE_ENTERED">="CP"."START_DATE" AND
    "CP"."CALENDAR_TYPE"="BC"."DEPRN_CALENDAR" AND "TH"."TRANSACTION_DATE_ENTERED"<="CP"."END_DATE" AND
    "TH"."BOOK_TYPE_CODE"="DP"."BOOK_TYPE_CODE" AND "TH"."DATE_EFFECTIVE">="DP"."PERIOD_OPEN_DATE" AND
    "TH"."DATE_EFFECTIVE"<=NVL("DP"."PERIOD_CLOSE_DATE","TH"."DATE_EFFECTIVE") AND "TH"."TRANSACTION_HEADER_ID"=TO_NUMBER:)B7) AND
    "TH"."TRANSACTION_HEADER_ID">="AH"."TRANSACTION_HEADER_ID_IN" AND "AH"."ASSET_ID"="ADB"."ASSET_ID" AND
    "AH"."TRANSACTION_HEADER_ID_IN"<=TO_NUMBER:)B8) AND "TH"."TRANSACTION_HEADER_ID"<NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRA
    NSACTION_HEADER_ID"+1) AND NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1)>TO_NUMBER:)B9) AND
    "TH"."ASSET_ID"="ADB"."ASSET_ID" AND "ADTL"."LANGUAGE"=USERENV('LANG') AND "ADB"."ASSET_ID"="ADTL"."ASSET_ID" AND
    "DP"."BOOK_TYPE_CODE"="BC"."BOOK_TYPE_CODE") WHEN (:)B10='Assets') AND :)B11='Depreciation')) THEN TO_CHAR:)B12) ELSE :B13 END
    )
    86 - access("PAV"."VENDOR_ID"="AI"."PO_VENDOR_ID")
    89 - access("PAV"."PARTY_ID"="HP"."PARTY_ID")
    90 - filter("AI"."PO_VENDOR_ID" IS NOT NULL AND "AI"."DATE_INEFFECTIVE" IS NULL)
    104 - access("TH"."TRANSACTION_HEADER_ID"=TO_NUMBER:)B1))
    105 - access("LOB"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="TH"."TRANSACTION_TYPE_CODE")
    106 - access("LOTL"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="LOTL"."LOOKUP_CODE" AND
    "LOTL"."LANGUAGE"=USERENV('LANG'))
    107 - filter("TH"."BOOK_TYPE_CODE"="DP"."BOOK_TYPE_CODE" AND "TH"."DATE_EFFECTIVE">="DP"."PERIOD_OPEN_DATE" AND
    "TH"."DATE_EFFECTIVE"<=NVL("DP"."PERIOD_CLOSE_DATE","TH"."DATE_EFFECTIVE"))
    109 - access("DP"."BOOK_TYPE_CODE"="BC"."BOOK_TYPE_CODE")
    111 - access("TH"."ASSET_ID"="ADB"."ASSET_ID")
    113 - access("AH"."ASSET_ID"="ADB"."ASSET_ID" AND "TH"."TRANSACTION_HEADER_ID">="AH"."TRANSACTION_HEADER_ID_IN")
    filter("AH"."TRANSACTION_HEADER_ID_IN"<=TO_NUMBER:)B1) AND
    "TH"."TRANSACTION_HEADER_ID"<NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1) AND
    NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1)>TO_NUMBER:)B2))
    114 - access("B"."CATEGORY_ID"="AH"."CATEGORY_ID")
    115 - access("B"."CATEGORY_ID"="T"."CATEGORY_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
    116 - access("ADB"."ASSET_ID"="ADTL"."ASSET_ID" AND "ADTL"."LANGUAGE"=USERENV('LANG'))
    117 - access("CP"."CALENDAR_TYPE"="BC"."DEPRN_CALENDAR" AND "TH"."TRANSACTION_DATE_ENTERED">="CP"."START_DATE")
    118 - filter("TH"."TRANSACTION_DATE_ENTERED"<="CP"."END_DATE")
    119 - filter(TO_DATE:)P_GL_DATE_FROM)<=TO_DATE:)P_GL_DATE_TO))
    134 - access("GJST"."JE_SOURCE_NAME"="GJH"."JE_SOURCE")
    135 - filter("GJST"."LANGUAGE"=USERENV('LANG'))
    138 - access("GJL"."LEDGER_ID"="GLBGT"."LEDGER_ID" AND "GJL"."CODE_COMBINATION_ID"="GLBGT"."CODE_COMBINATION_ID" AND
    "GJL"."PERIOD_NAME"="GLBGT"."PERIOD_NAME")
    filter("GJL"."EFFECTIVE_DATE">="GLBGT"."PERIOD_START_DATE" AND "GJL"."EFFECTIVE_DATE"<="GLBGT"."PERIOD_END_DATE")
    140 - access("GJL"."EFFECTIVE_DATE">=:p_GL_DATE_FROM AND "GJL"."EFFECTIVE_DATE"<=:p_GL_DATE_TO)
    141 - filter("GLBGT"."PERIOD_END_DATE">=:p_GL_DATE_FROM AND "GLBGT"."PERIOD_START_DATE"<=:p_GL_DATE_TO)
    142 - access("GJL"."JE_HEADER_ID"="GJH"."JE_HEADER_ID")
    143 - filter("GJH"."STATUS"='P' AND (NVL("GJH"."JE_FROM_SLA_FLAG",'N')='Y' OR NVL("GJH"."JE_FROM_SLA_FLAG",'N')='U') AND
    "GJL"."PERIOD_NAME"="GJH"."PERIOD_NAME")
    145 - access("GJH"."JE_HEADER_ID"="GIR"."JE_HEADER_ID" AND "GJL"."JE_LINE_NUM"="GIR"."JE_LINE_NUM")
    filter("GJL"."JE_HEADER_ID"="GIR"."JE_HEADER_ID")
    146 - filter("GJB"."STATUS"='P')
    147 - access("GJB"."JE_BATCH_ID"="GIR"."JE_BATCH_ID")
    149 - filter(NVL("AEL"."ENCUMBRANCE_TYPE_ID",(-19999))=NVL("GLBGT"."ENCUMBRANCE_TYPE_ID",(-19999)))
    150 - access("GIR"."GL_SL_LINK_ID"="AEL"."GL_SL_LINK_ID" AND "GIR"."GL_SL_LINK_TABLE"="AEL"."GL_SL_LINK_TABLE")
    152 - filter("AEH"."GL_TRANSFER_STATUS_CODE"='Y' AND "AEH"."ACCOUNTING_ENTRY_STATUS_CODE"='F' AND
    "AEH"."BALANCE_TYPE_CODE"="GLBGT"."BALANCE_TYPE_CODE" AND NVL("AEH"."BUDGET_VERSION_ID",(-19999))=NVL("GLBGT"."BUDGET_VERSION_I
    D",(-19999)))
    153 - access("AEL"."AE_HEADER_ID"="AEH"."AE_HEADER_ID" AND "AEL"."APPLICATION_ID"="AEH"."APPLICATION_ID")
    155 - access("GJCT"."JE_CATEGORY_NAME"="AEH"."JE_CATEGORY_NAME" AND "GJCT"."LANGUAGE"=USERENV('LANG'))
    156 - access("FAP"."APPLICATION_ID"="AEH"."APPLICATION_ID" AND "FAP"."LANGUAGE"=USERENV('LANG'))
    157 - access("XET"."APPLICATION_ID"="AEH"."APPLICATION_ID" AND "XET"."EVENT_TYPE_CODE"="AEH"."EVENT_TYPE_CODE" AND
    "XET"."LANGUAGE"=USERENV('LANG'))
    filter("XET"."LANGUAGE"=USERENV('LANG') AND "XET"."EVENT_TYPE_CODE"="AEH"."EVENT_TYPE_CODE")
    158 - access("XECT"."APPLICATION_ID"="XET"."APPLICATION_ID" AND "XECT"."ENTITY_CODE"="XET"."ENTITY_CODE" AND
    "XECT"."EVENT_CLASS_CODE"="XET"."EVENT_CLASS_CODE" AND "XECT"."LANGUAGE"=USERENV('LANG'))
    159 - filter("FNS"."APPLICATION_ID"(+)="AEH"."APPLICATION_ID")
    160 - access("FNS"."DOC_SEQUENCE_ID"(+)="AEH"."DOC_SEQUENCE_ID")
    162 - filter("SECURITY_ID_INT_1" IS NULL)
    163 - access("ENTITY_ID"="AEH"."ENTITY_ID" AND "APPLICATION_ID"="AEH"."APPLICATION_ID")
    164 - access("FDU"."USER_ID"="CREATED_BY")
    167 - access("XLE"."EVENT_ID"="AEH"."EVENT_ID" AND "XLE"."APPLICATION_ID"="AEH"."APPLICATION_ID")
    168 - access("LV"."LOOKUP_TYPE"='XLA_ACCOUNTING_CLASS' AND "LV"."VIEW_APPLICATION_ID"=602 AND
    "LV"."LOOKUP_CODE"="AEL"."ACCOUNTING_CLASS_CODE" AND "LV"."SECURITY_GROUP_ID"=0 AND "LV"."LANGUAGE"=USERENV('LANG'))
    169 - access("LV"."LOOKUP_TYPE"(+)='XLA_FUNDS_STATUS' AND "LV"."VIEW_APPLICATION_ID"(+)=602 AND
    "LV"."LOOKUP_CODE"(+)="AEH"."FUNDS_STATUS_CODE" AND "LV"."SECURITY_GROUP_ID"(+)=0 AND "LV"."LANGUAGE"(+)=USERENV('LANG'))

    Note
    -----
    - 'PLAN_TABLE' is old version
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Pay attention to steps in the plan: 84 -90
     
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    | 84 | SORT AGGREGATE | | 1 | 48 | | | |
    |* 85 | FILTER | | | | | | |
    |* 86 | HASH JOIN | | 7955 | 372K| 257 (3)| | |
    | 87 | NESTED LOOPS | | 3287 | 118K| 44 (5)| | |
    | 88 | TABLE ACCESS FULL | AP_SUPPLIERS | 3287 | 102K| 43 (3)| | |
    |* 89 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 5 | 0 (0)| | |
    |* 90 | TABLE ACCESS FULL | FA_ASSET_INVOICES | 7955 | 87505 | 212 (2)| | |

    In my query there is no fa_asset_invoices table
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    You in request use view...
    For example : fa_transaction_history_trx_v
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Yes I used fa_transaction_history_trx_v.

    What can I do now?

    is this the problem?
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    For a start:
    execute separately query

    Code (SQL):

    SELECT MAX(asset_number) FROM fa_transaction_history_trx_v
               WHERE transaction_header_id=<SOME value>

     
    with any value "transaction_header_id" and check plain sql query.


    NOTE:It is desirable to rewrite your request with "WITH" use.
     
  12. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Sorry for late replies...

    When I click on Post it is taking long time open.

    select max(asset_number) from fa_transaction_history_trx_v
    where transaction_header_id=10234

    It is giving asset_number.
    And explain Plan for this...



    ----------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    ----------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 273 | 25 (0)|
    | 1 | SORT AGGREGATE | | 1 | 273 | |
    | 2 | NESTED LOOPS | | | | |
    | 3 | NESTED LOOPS | | 1 | 273 | 25 (0)|
    | 4 | NESTED LOOPS | | 1 | 245 | 11 (0)|
    | 5 | NESTED LOOPS | | 1 | 235 | 11 (0)|
    | 6 | NESTED LOOPS | | 1 | 227 | 11 (0)|
    | 7 | NESTED LOOPS | | 1 | 222 | 11 (0)|
    | 8 | NESTED LOOPS | | 1 | 204 | 9 (0)|
    | 9 | NESTED LOOPS | | 1 | 187 | 8 (0)|
    | 10 | NESTED LOOPS | | 1 | 159 | 7 (0)|
    | 11 | NESTED LOOPS | | 1 | 128 | 2 (0)|
    | 12 | NESTED LOOPS | | 1 | 96 | 2 (0)|
    | 13 | TABLE ACCESS BY INDEX ROWID| FA_TRANSACTION_HEADERS | 1 | 67 | 2 (0)|
    |* 14 | INDEX UNIQUE SCAN | FA_TRANSACTION_HEADERS_U1 | 1 | | 1 (0)|
    |* 15 | INDEX UNIQUE SCAN | FA_LOOKUPS_B_U1 | 1 | 29 | 0 (0)|
    |* 16 | INDEX UNIQUE SCAN | FA_LOOKUPS_TL_U1 | 1 | 32 | 0 (0)|
    |* 17 | TABLE ACCESS FULL | FA_DEPRN_PERIODS | 1 | 31 | 5 (0)|
    | 18 | TABLE ACCESS BY INDEX ROWID | FA_BOOK_CONTROLS | 1 | 28 | 1 (0)|
    |* 19 | INDEX UNIQUE SCAN | FA_BOOK_CONTROLS_U1 | 1 | | 0 (0)|
    | 20 | TABLE ACCESS BY INDEX ROWID | FA_ADDITIONS_B | 1 | 17 | 1 (0)|
    |* 21 | INDEX UNIQUE SCAN | FA_ADDITIONS_B_U1 | 1 | | 0 (0)|
    | 22 | TABLE ACCESS BY INDEX ROWID | FA_ASSET_HISTORY | 1 | 18 | 2 (0)|
    |* 23 | INDEX RANGE SCAN | FA_ASSET_HISTORY_N3 | 1 | | 1 (0)|
    |* 24 | INDEX UNIQUE SCAN | FA_CATEGORIES_B_U1 | 1 | 5 | 0 (0)|
    |* 25 | INDEX UNIQUE SCAN | FA_CATEGORIES_TL_U1 | 1 | 8 | 0 (0)|
    |* 26 | INDEX UNIQUE SCAN | FA_ADDITIONS_TL_U1 | 1 | 10 | 0 (0)|
    |* 27 | INDEX RANGE SCAN | FA_CALENDAR_PERIODS_U2 | 808 | | 4 (0)|
    |* 28 | TABLE ACCESS BY INDEX ROWID | FA_CALENDAR_PERIODS | 515 | 14420 | 14 (0)|
    ----------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    14 - access("TH"."TRANSACTION_HEADER_ID"=10234)
    15 - access("LOB"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="TH"."TRANSACTION_TYPE_CODE")
    16 - access("LOTL"."LOOKUP_TYPE"='FAXOLTRX' AND "LOB"."LOOKUP_CODE"="LOTL"."LOOKUP_CODE" AND
    "LOTL"."LANGUAGE"=USERENV('LANG'))
    17 - filter("TH"."BOOK_TYPE_CODE"="DP"."BOOK_TYPE_CODE" AND
    "TH"."DATE_EFFECTIVE">="DP"."PERIOD_OPEN_DATE" AND
    "TH"."DATE_EFFECTIVE"<=NVL("DP"."PERIOD_CLOSE_DATE","TH"."DATE_EFFECTIVE"))
    19 - access("DP"."BOOK_TYPE_CODE"="BC"."BOOK_TYPE_CODE")
    21 - access("TH"."ASSET_ID"="ADB"."ASSET_ID")
    23 - access("AH"."ASSET_ID"="ADB"."ASSET_ID" AND
    "TH"."TRANSACTION_HEADER_ID">="AH"."TRANSACTION_HEADER_ID_IN")
    filter("AH"."TRANSACTION_HEADER_ID_IN"<=10234 AND
    "TH"."TRANSACTION_HEADER_ID"<NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1)
    AND NVL("AH"."TRANSACTION_HEADER_ID_OUT","TH"."TRANSACTION_HEADER_ID"+1)>10234)
    24 - access("B"."CATEGORY_ID"="AH"."CATEGORY_ID")
    25 - access("B"."CATEGORY_ID"="T"."CATEGORY_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
    26 - access("ADB"."ASSET_ID"="ADTL"."ASSET_ID" AND "ADTL"."LANGUAGE"=USERENV('LANG'))
    27 - access("CP"."CALENDAR_TYPE"="BC"."DEPRN_CALENDAR" AND
    "TH"."TRANSACTION_DATE_ENTERED">="CP"."START_DATE")
    28 - filter("TH"."TRANSACTION_DATE_ENTERED"<="CP"."END_DATE")

    Note
    -----
    - 'PLAN_TABLE' is old version
     
  13. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    there are some questions for the table FA_DEPRN_PERIODS (step 17 in plan):
    can show output this query ?
    Code (SQL):

    SELECT  t.num_rows,
            t.blocks,
            t.empty_blocks,
            t.avg_space,
            t.last_analyzed    
    FROM all_tab_statistics t
    WHERE t.TABLE_NAME = 'FA_DEPRN_PERIODS';

     
     
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Num_Rows Blocks empty_blocks avg_space

    284 13 0 0
     
  15. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1) If for the table FA_DEPRN_PERIODS : there are no indexes, then it is possible to try to create a necessary index for search
    2) also it is necessary to pay attention to plan steps :27,28
    3) can show output this query (command window):
    Code (SQL):

    ALTER SESSION SET statistics_level = ALL;
    ALTER SESSION SET timed_statistics = TRUE;
    SELECT /*+ gather_plan_statistics */
    MAX(asset_number)
    FROM fa_transaction_history_trx_v
    WHERE transaction_header_id=10234;
    SELECT * FROM TABLE (dbms_xplan.display_cursor(NULL,NULL,'ADVANCED -projection'));

     
     
  16. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Sergey,

    It has 3 indexes.

    Index Owner Index Name Uniqueness Status Index Type Temporary Partitioned Function Index Status Join Index Columns Column Expression
    FA FA_DEPRN_PERIODS_U1 UNIQUE VALID NORMAL N NO NO BOOK_TYPE_CODE, PERIOD_NAME
    FA FA_DEPRN_PERIODS_U2 UNIQUE VALID NORMAL N NO NO BOOK_TYPE_CODE, FISCAL_YEAR, PERIOD_NUM
    FA FA_DEPRN_PERIODS_U3 UNIQUE VALID NORMAL N NO NO BOOK_TYPE_CODE, PERIOD_COUNTER
     
  17. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1)
    can show index stats ...
    Code (SQL):


    SELECT
        i_s.INDEX_NAME,
        i_s.blevel,
        i_s.LEAF_BLOCKS,
        i_s.DISTINCT_KEYS,
        i_s.NUM_ROWS,
        i_s.LAST_ANALYZED,
        i.STATUS,
        i.partitioned
    FROM user_ind_statistics i_s
    JOIN user_indexes  i ON i.index_name = i_s.INDEX_NAME
    WHERE i_s.INDEX_NAME  IN ('FA_DEPRN_PERIODS_U1','FA_DEPRN_PERIODS_U2','FA_DEPRN_PERIODS_U3');
     
    2)
    What is PERIOD_COUNTER and PERIOD_NUM ?
     
  18. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It could be PART of the problem; the view is created against a table with no usable indexes thus the FULL TABLE SCAN. One option would be to add a relevant index to the FA_ASSET_INVOICES table to improve the access path for the view. There may be other areas that need attention, but this seems to be one of the major issues you face.
     
  19. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    With reference to your Post #2 on this thread:

    "suspicion" is naturally on this portion of added code.

    This is demonstrated by the results of the diagnostics of Sergey.
    However, I'll go more in David's direction and focus on this portion of query (with some formatting):

    I wonder why you're doing (on 3rd line above):
    I guess because you're joining it with either "asset_number" or "source_id_int_1" which may be non-numeric (VARCHAR2 in database I think:).

    "fa_invoice_details_v" is a view based on FA_ASSET_INVOICES and PO_VENDORS (itself a view based om AP_SUPPLIERS and HZ_PARTIES) and there are a couple of indexes on ASSET_ID (concatenated indexes - ASSET_ID being the 1st column). And ASSET_ID being of NUMBER datatype, if you remove the TO_CHAR in line 3, then your join may use the Index on FA_ASSET_INVOICES and thus avoiding a FTS. Optimizer will automatically do an implicit TO_NUMBER on the "asset_number" or "source_id_int_1". If these 2 fields do contain non-numeric values however, then your query will fail ... and we're back to square one !

    I'll let you do some tests & checks.

    All the best !
     
  20. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    This query giving 0 rows. there is no index with that name in that table.



    period_counter is a number. For example APR-12 has 24145 period_counter.
    And period_number is 1.

    because it's financial year start with apr to mar