1 2 Previous Next 16 Replies Latest reply: Feb 24, 2013 2:39 PM by Hussein Sawwan-Oracle RSS

    Performance issue with the following view pa_draft_inv_line_details_v

    user2549696
      Hi Gurus,
      We have a select statement in which we are using this view pa_draft_inv_line_details_v.This view is causing Performane Issue while exectuing select statement.Can you please help me on this.

      Thanks,
      RS.
        • 1. Re: Performance issue with the following view pa_draft_inv_line_details_v
          Srini Chavali-Oracle
          Pl post details of OS, database and EBS versions. Pl see these threads on how to post a tuning request

          When your query takes too long ...

          HOW TO: Post a SQL statement tuning request - template posting

          Are statistics current ?

          ARXTWMAI Taking Very Long Time To Save. Autoinvoice Also Performs Poorly [ID 556535.1]

          HTH
          Srini
          • 2. Re: Performance issue with the following view pa_draft_inv_line_details_v
            Hussein Sawwan-Oracle
            user2549696 wrote:
            Hi Gurus,
            We have a select statement in which we are using this view pa_draft_inv_line_details_v.This view is causing Performane Issue while exectuing select statement.Can you please help me on this.

            Thanks,
            RS.
            Do you have the statistics collected up to date?

            Was this working before? If yes, any changes been done recently?

            Do you run a select statement on the view with no conditions? How many records do you get when you query this view?

            https://forums.oracle.com/forums/search.jspa?threadID=&q=Query+AND+Performance&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

            Thanks,
            Hussein
            • 3. Re: Performance issue with the following view pa_draft_inv_line_details_v
              user2549696
              Hi Hussien,
              Do you have the statistics collected up to date?
              Can you please tell me how to have the statistics collected up to date?

              Was this working before? If yes, any changes been done recently?
              It was working fine before.No changes done recently.

              Do you run a select statement on the view with no conditions? How many records do you get when you query this view?
              Depending on the Operating Unit Setup the record number changes.

              Below is the query which is taking nearly 8HRS to execute.

              SELECT a.project_id, b.NAME project_name, b.segment1 project_number,

              b.carrying_out_organization_id project_org_id,

              b.organization_name project_org,

              SUBSTR (b.project_type, 1, 2) project_type,

              c.incurred_by_person_id person_id,

              (TRIM (per.first_name) || ' ' || TRIM (per.last_name)) emp_name,

              c.expenditure_type, a.draft_invoice_num, a.line_num,

              NVL (c.unit_of_measure, 'NA') unit_of_measure,

              DECODE (c.unit_of_measure, 'HOURS', SUM (c.quantity), 1) qty,

              DECODE (c.unit_of_measure,

              'HOURS', c.bill_rate,

              SUM (c.quantity)

              ) rate,

              TO_CHAR (MIN (c.expenditure_item_date), 'DD-MON-YYYY') from_date,

              TO_CHAR (MAX (c.expenditure_item_date), 'DD-MON-YYYY') TO_DATE

              FROM pa_draft_invoice_lines_v a,

              pa_projects_v b,

              pa_draft_inv_line_details_v c,

              (SELECT *

              FROM per_all_people_f

              WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effective_start_date)

              AND TRUNC (NVL (effective_end_date,

              SYSDATE + 10

              )

              )) per

              WHERE a.project_id = b.project_id

              -- AND b.segment1 = :b3

              AND a.project_id = c.project_id(+)

              AND a.draft_invoice_num = c.draft_invoice_num(+)

              AND a.line_num = c.draft_invoice_item_line_num(+)

              AND c.incurred_by_person_id = per.person_id(+)

              -- AND TO_CHAR (a.draft_invoice_num) = :b2

              -- AND TO_CHAR (a.line_num) = :b1

              GROUP BY a.project_id,

              b.NAME,

              b.segment1,

              b.carrying_out_organization_id,

              b.organization_name,

              b.project_type,

              c.incurred_by_person_id,

              (TRIM (per.first_name) || ' ' || TRIM (per.last_name)

              ),

              c.expenditure_type,

              a.draft_invoice_num,

              a.line_num,

              c.unit_of_measure,

              c.bill_rate

              Can you please help me on Tuning this Query.

              Thanks,
              RS
              • 4. Re: Performance issue with the following view pa_draft_inv_line_details_v
                Hussein Sawwan-Oracle
                Can you please tell me how to have the statistics collected up to date?
                How to Identify when a Table/Index was Last Analyzed by Gather Schema Statistics [ID 1434191.1]
                It was working fine before.No changes done recently.
                Did you try to bounce the database/application and see if this helps?
                Do you run a select statement on the view with no conditions? How many records do you get when you query this view?
                Depending on the Operating Unit Setup the record number changes.

                Below is the query which is taking nearly 8HRS to execute.
                Please provide the explain plan from the docs referenced above and answer my other questions.

                Thanks,
                Hussein
                • 5. Re: Performance issue with the following view pa_draft_inv_line_details_v
                  user2549696
                  Hussien,
                  Sorry for Late Reply.
                  Here are the details:
                  Gather Schema Statistics
                  INDEX_NAME     TABLE_NAME
                  AP_SUPPLIERS_N1     AP_SUPPLIERS
                  AP_SUPPLIERS_N2     AP_SUPPLIERS
                  AP_SUPPLIERS_N3     AP_SUPPLIERS
                  AP_SUPPLIERS_N4     AP_SUPPLIERS
                  AP_SUPPLIERS_N5     AP_SUPPLIERS
                  AP_SUPPLIERS_N6     AP_SUPPLIERS
                  AP_SUPPLIERS_N7     AP_SUPPLIERS
                  AP_SUPPLIERS_N8     AP_SUPPLIERS
                  AP_SUPPLIERS_U1     AP_SUPPLIERS
                  AP_SUPPLIERS_U2     AP_SUPPLIERS
                  FND_LOOKUP_VALUES_U1     FND_LOOKUP_VALUES
                  FND_LOOKUP_VALUES_U2     FND_LOOKUP_VALUES
                  GL_DAILY_CONVERSION_TYPES_U1     GL_DAILY_CONVERSION_TYPES
                  GL_DAILY_CONVERSION_TYPES_U2     GL_DAILY_CONVERSION_TYPES
                  HR_ORGANIZATION_UNITS_FK1     HR_ALL_ORGANIZATION_UNITS
                  HR_ORGANIZATION_UNITS_FK2     HR_ALL_ORGANIZATION_UNITS
                  HR_ORGANIZATION_UNITS_FK3     HR_ALL_ORGANIZATION_UNITS
                  HR_ORGANIZATION_UNITS_FK4     HR_ALL_ORGANIZATION_UNITS
                  HR_ORGANIZATION_UNITS_PK     HR_ALL_ORGANIZATION_UNITS
                  HR_ORGANIZATION_UNITS_UK2     HR_ALL_ORGANIZATION_UNITS
                  HR_ALL_ORGANIZATION_UNTS_TL_N2     HR_ALL_ORGANIZATION_UNITS_TL
                  HR_ALL_ORGANIZATION_UNTS_TL_PK     HR_ALL_ORGANIZATION_UNITS_TL
                  PA_COST_DISTRIBUTION_LINES_N10     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N12     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N13     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N14     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N15     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N16     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N17     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N19     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N2     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N20     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N3     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N4     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N5     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N6     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N7     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N8     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_N9     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_COST_DISTRIBUTION_LINES_U1     PA_COST_DISTRIBUTION_LINES_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_N1     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_N2     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_N3     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_N4     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_N5     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_EVENT_REV_DIST_LINE_U1     PA_CUST_EVENT_RDL_ALL
                  PA_CUST_REV_DIST_LINES_N1     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N2     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N3     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N4     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N5     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N6     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N7     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_N9     PA_CUST_REV_DIST_LINES_ALL
                  PA_CUST_REV_DIST_LINES_U1     PA_CUST_REV_DIST_LINES_ALL
                  PA_DRAFT_INVOICE_ITEMS_N1     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_N2     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_N3     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_N4     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_N5     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_N6     PA_DRAFT_INVOICE_ITEMS
                  PA_DRAFT_INVOICE_ITEMS_U1     PA_DRAFT_INVOICE_ITEMS
                  PA_EVENTS_N1     PA_EVENTS
                  PA_EVENTS_N2     PA_EVENTS
                  PA_EVENTS_N3     PA_EVENTS
                  PA_EVENTS_N4     PA_EVENTS
                  PA_EVENTS_N5     PA_EVENTS
                  PA_EVENTS_N6     PA_EVENTS
                  PA_EVENTS_U1     PA_EVENTS
                  PA_EVENTS_U2     PA_EVENTS
                  PA_EVENTS_U3     PA_EVENTS
                  PA_EVENTS_U4     PA_EVENTS
                  PA_EXPENDITURES_ALL_N11     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N1     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N2     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N3     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N4     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N5     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N6     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N7     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N8     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_N9     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_U1     PA_EXPENDITURES_ALL
                  PA_EXPENDITURES_ITEMS_N18     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N1     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N10     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N11     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N12     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N13     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N14     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N15     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N16     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N17     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N18     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N19     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N2     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N20     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N21     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N22     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N23     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N24     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N25     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N26     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N27     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N28     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N29     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N3     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N30     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N31     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N32     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N33     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N35     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N4     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N5     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N6     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N7     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N8     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_N9     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_ITEMS_U1     PA_EXPENDITURE_ITEMS_ALL
                  PA_EXPENDITURE_TYPES_N1     PA_EXPENDITURE_TYPES
                  PA_EXPENDITURE_TYPES_N2     PA_EXPENDITURE_TYPES
                  PA_EXPENDITURE_TYPES_N3     PA_EXPENDITURE_TYPES
                  PA_EXPENDITURE_TYPES_U1     PA_EXPENDITURE_TYPES
                  PA_EXPENDITURE_TYPES_U2     PA_EXPENDITURE_TYPES
                  PA_PROJECT_STATUSES_U2     PA_PROJECT_STATUSES
                  PA_PROJECT_STATUSES_U3     PA_PROJECT_STATUSES
                  PA_PROJECT_TYPES_N1     PA_PROJECT_TYPES_ALL
                  PA_PROJECT_TYPES_U1     PA_PROJECT_TYPES_ALL
                  PA_PROJECT_TYPES_U2     PA_PROJECT_TYPES_ALL
                  PA_TASKS_N1     PA_TASKS
                  PA_TASKS_N10     PA_TASKS
                  PA_TASKS_N11     PA_TASKS
                  PA_TASKS_N12     PA_TASKS
                  PA_TASKS_N13     PA_TASKS
                  PA_TASKS_N14     PA_TASKS
                  PA_TASKS_N2     PA_TASKS
                  PA_TASKS_N3     PA_TASKS
                  PA_TASKS_N4     PA_TASKS
                  PA_TASKS_N5     PA_TASKS
                  PA_TASKS_N6     PA_TASKS
                  PA_TASKS_N7     PA_TASKS
                  PA_TASKS_N8     PA_TASKS
                  PA_TASKS_N9     PA_TASKS
                  PA_TASKS_U1     PA_TASKS
                  PA_TASKS_U2     PA_TASKS
                  PER_PEOPLE_F_FK1     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_FK2     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N1     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N2     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N50     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N51     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N52     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N53     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N54     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N55     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N56     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N57     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N58     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N59     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_N60     PER_ALL_PEOPLE_F
                  PER_PEOPLE_F_PK     PER_ALL_PEOPLE_F
                  xxx_PERALL_EFFDATE     PER_ALL_PEOPLE_F
                  • 6. Re: Performance issue with the following view pa_draft_inv_line_details_v
                    user2549696
                    Execution Plan:
                    Query Plan
                    SELECT STATEMENT Cost = 1013
                    HASH GROUP BY
                    FILTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS
                    HASH JOIN OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS
                    NESTED LOOPS
                    HASH JOIN
                    MERGE JOIN CARTESIAN
                    NESTED LOOPS
                    NESTED LOOPS
                    NESTED LOOPS
                    INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                    INDEX UNIQUE SCAN HR.HR_ORGANIZATION_UNITS_PK
                    INDEX UNIQUE SCAN PA.PA_IMPLEMENTATIONS_U1
                    INDEX RANGE SCAN HR.HR_ORGANIZATION_INFORMATIO_FK2
                    BUFFER SORT
                    TABLE ACCESS BY INDEX ROWID HR.HR_ORGANIZATION_INFORMATION
                    INDEX RANGE SCAN HR.HR_ORGANIZATION_INFORMATIO_FK2
                    TABLE ACCESS FULL PA.PA_PROJECTS_ALL
                    INDEX UNIQUE SCAN PA.PA_PROJECT_TYPES_U1
                    TABLE ACCESS BY INDEX ROWID HR.HR_ALL_ORGANIZATION_UNITS
                    INDEX UNIQUE SCAN HR.HR_ORGANIZATION_UNITS_PK
                    TABLE ACCESS BY INDEX ROWID HR.HR_ALL_ORGANIZATION_UNITS_TL
                    INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                    INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                    TABLE ACCESS BY INDEX ROWID PA.PA_PROJECT_STATUSES
                    INDEX UNIQUE SCAN PA.PA_PROJECT_STATUSES_U2
                    TABLE ACCESS BY INDEX ROWID PA.PA_DRAFT_INVOICE_ITEMS
                    INDEX RANGE SCAN PA.PA_DRAFT_INVOICE_ITEMS_N3
                    TABLE ACCESS BY INDEX ROWID APPLSYS.FND_LOOKUP_VALUES
                    INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                    INDEX RANGE SCAN PA.PA_EVENTS_U1
                    TABLE ACCESS BY INDEX ROWID APPLSYS.FND_LOOKUP_VALUES
                    INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                    INDEX UNIQUE SCAN AR.HZ_CUST_ACCT_SITES_U1
                    VIEW PA_DRAFT_INV_LINE_DETAILS_V
                    UNION-ALL
                    NESTED LOOPS
                    NESTED LOOPS
                    MERGE JOIN CARTESIAN
                    NESTED LOOPS
                    NESTED LOOPS OUTER
                    FILTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS OUTER
                    NESTED LOOPS
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    NESTED LOOPS OUTER
                    HASH JOIN
                    HASH JOIN
                    NESTED LOOPS
                    NESTED LOOPS
                    HASH JOIN
                    NESTED LOOPS
                    TABLE ACCESS BY INDEX ROWID PA.PA_PROJECT_TYPES_ALL
                    INDEX SKIP SCAN PA.PA_PROJECT_TYPES_U1
                    INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                    TABLE ACCESS FULL PA.PA_PROJECTS_ALL
                    INDEX RANGE SCAN PA.PA_EXPENDITURE_ITEMS_N28
                    TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURE_ITEMS_ALL
                    INDEX FULL SCAN PA.PA_SYSTEM_LINKAGES_U1
                    TABLE ACCESS FULL PA.PA_EXPENDITURE_TYPES
                    INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                    TABLE ACCESS BY INDEX ROWID PA.PA_COST_DISTRIBUTION_LINES_ALL
                    INDEX UNIQUE SCAN PA.PA_COST_DISTRIBUTION_LINES_U1
                    INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    TABLE ACCESS BY INDEX ROWID PA.PA_TASKS
                    INDEX UNIQUE SCAN PA.PA_TASKS_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    TABLE ACCESS BY INDEX ROWID PA.PA_CUST_REV_DIST_LINES_ALL
                    INDEX RANGE SCAN PA.PA_CUST_REV_DIST_LINES_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                    • 7. Re: Performance issue with the following view pa_draft_inv_line_details_v
                      user2549696
                      INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                      INDEX RANGE SCAN PA.PA_EXPENDITURE_COMMENTS_U1
                      INDEX UNIQUE SCAN PA.PA_TRANSACTION_SOURCES_U1
                      INDEX UNIQUE SCAN HR.PER_JOBS_PK
                      TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURES_ALL
                      INDEX UNIQUE SCAN PA.PA_EXPENDITURES_U1
                      TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F
                      INDEX RANGE SCAN HR.PER_PEOPLE_F_PK
                      VIEW PUSHED PREDICATE PO_VENDORS
                      NESTED LOOPS
                      TABLE ACCESS BY INDEX ROWID AP.AP_SUPPLIERS
                      INDEX UNIQUE SCAN AP.AP_SUPPLIERS_U1
                      INDEX UNIQUE SCAN AR.HZ_PARTIES_U1
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      BUFFER SORT
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      NESTED LOOPS
                      NESTED LOOPS
                      MERGE JOIN CARTESIAN
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      FILTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      HASH JOIN
                      HASH JOIN
                      NESTED LOOPS
                      NESTED LOOPS
                      HASH JOIN
                      NESTED LOOPS
                      TABLE ACCESS BY INDEX ROWID PA.PA_PROJECT_TYPES_ALL
                      INDEX SKIP SCAN PA.PA_PROJECT_TYPES_U1
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      TABLE ACCESS FULL PA.PA_PROJECTS_ALL
                      INDEX RANGE SCAN PA.PA_EXPENDITURE_ITEMS_N28
                      TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURE_ITEMS_ALL
                      INDEX FULL SCAN PA.PA_SYSTEM_LINKAGES_U1
                      TABLE ACCESS FULL PA.PA_EXPENDITURE_TYPES
                      INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                      TABLE ACCESS BY INDEX ROWID PA.PA_COST_DISTRIBUTION_LINES_ALL
                      INDEX UNIQUE SCAN PA.PA_COST_DISTRIBUTION_LINES_U1
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                      TABLE ACCESS BY INDEX ROWID PA.PA_TASKS
                      INDEX UNIQUE SCAN PA.PA_TASKS_U1
                      INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                      INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                      INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                      INDEX RANGE SCAN PA.PA_EXPENDITURE_COMMENTS_U1
                      INDEX UNIQUE SCAN PA.PA_TRANSACTION_SOURCES_U1
                      INDEX UNIQUE SCAN HR.PER_JOBS_PK
                      TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURES_ALL
                      INDEX UNIQUE SCAN PA.PA_EXPENDITURES_U1
                      TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F
                      INDEX RANGE SCAN HR.PER_PEOPLE_F_PK
                      VIEW PUSHED PREDICATE PO_VENDORS
                      NESTED LOOPS
                      TABLE ACCESS BY INDEX ROWID AP.AP_SUPPLIERS
                      INDEX UNIQUE SCAN AP.AP_SUPPLIERS_U1
                      INDEX UNIQUE SCAN AR.HZ_PARTIES_U1
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      BUFFER SORT
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                      INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                      NESTED LOOPS
                      MERGE JOIN CARTESIAN
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS OUTER
                      NESTED LOOPS
                      NESTED LOOPS
                      NESTED LOOPS OUTER
                      • 8. Re: Performance issue with the following view pa_draft_inv_line_details_v
                        user2549696
                        NESTED LOOPS
                        NESTED LOOPS OUTER
                        TABLE ACCESS FULL PA.PA_DRAFT_INVOICE_DETAILS_ALL
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURE_ITEMS_ALL
                        INDEX RANGE SCAN PA.PA_EXPENDITURE_ITEMS_N35
                        VIEW PUSHED PREDICATE PO_VENDORS
                        NESTED LOOPS
                        TABLE ACCESS BY INDEX ROWID AP.AP_SUPPLIERS
                        INDEX UNIQUE SCAN AP.AP_SUPPLIERS_U1
                        INDEX UNIQUE SCAN AR.HZ_PARTIES_U1
                        INDEX RANGE SCAN PA.PA_SYSTEM_LINKAGES_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURE_TYPES
                        INDEX UNIQUE SCAN PA.PA_EXPENDITURE_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN PA.PA_TRANSACTION_SOURCES_U1
                        INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                        INDEX UNIQUE SCAN HR.PER_JOBS_PK
                        TABLE ACCESS BY INDEX ROWID PA.PA_TASKS
                        INDEX UNIQUE SCAN PA.PA_TASKS_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_PROJECTS_ALL
                        INDEX RANGE SCAN PA.PA_PROJECTS_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_PROJECT_TYPES_ALL
                        INDEX RANGE SCAN PA.PA_PROJECT_TYPES_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURES_ALL
                        INDEX UNIQUE SCAN PA.PA_EXPENDITURES_U1
                        TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F
                        INDEX RANGE SCAN HR.PER_PEOPLE_F_PK
                        INDEX RANGE SCAN PA.PA_EXPENDITURE_COMMENTS_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_COST_DISTRIBUTION_LINES_ALL
                        INDEX UNIQUE SCAN PA.PA_COST_DISTRIBUTION_LINES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                        BUFFER SORT
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        NESTED LOOPS
                        NESTED LOOPS
                        MERGE JOIN CARTESIAN
                        NESTED LOOPS
                        NESTED LOOPS OUTER
                        FILTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS
                        NESTED LOOPS OUTER
                        NESTED LOOPS
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        NESTED LOOPS OUTER
                        HASH JOIN
                        HASH JOIN
                        NESTED LOOPS
                        NESTED LOOPS
                        HASH JOIN
                        NESTED LOOPS
                        TABLE ACCESS BY INDEX ROWID PA.PA_PROJECT_TYPES_ALL
                        INDEX SKIP SCAN PA.PA_PROJECT_TYPES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        TABLE ACCESS FULL PA.PA_PROJECTS_ALL
                        INDEX RANGE SCAN PA.PA_EXPENDITURE_ITEMS_N28
                        TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURE_ITEMS_ALL
                        INDEX FULL SCAN PA.PA_SYSTEM_LINKAGES_U1
                        TABLE ACCESS FULL PA.PA_EXPENDITURE_TYPES
                        INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                        TABLE ACCESS BY INDEX ROWID PA.PA_COST_DISTRIBUTION_LINES_ALL
                        INDEX UNIQUE SCAN PA.PA_COST_DISTRIBUTION_LINES_U1
                        INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_TASKS
                        INDEX UNIQUE SCAN PA.PA_TASKS_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        TABLE ACCESS BY INDEX ROWID PA.PA_CUST_REV_DIST_LINES_ALL
                        INDEX RANGE SCAN PA.PA_CUST_REV_DIST_LINES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                        INDEX RANGE SCAN PA.PA_EXPENDITURE_COMMENTS_U1
                        INDEX UNIQUE SCAN PA.PA_TRANSACTION_SOURCES_U1
                        INDEX UNIQUE SCAN HR.PER_JOBS_PK
                        TABLE ACCESS BY INDEX ROWID PA.PA_EXPENDITURES_ALL
                        INDEX UNIQUE SCAN PA.PA_EXPENDITURES_U1
                        TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F
                        INDEX RANGE SCAN HR.PER_PEOPLE_F_PK
                        VIEW PUSHED PREDICATE PO_VENDORS
                        • 9. Re: Performance issue with the following view pa_draft_inv_line_details_v
                          user2549696
                          NESTED LOOPS
                          TABLE ACCESS BY INDEX ROWID AP.AP_SUPPLIERS
                          INDEX UNIQUE SCAN AP.AP_SUPPLIERS_U1
                          INDEX UNIQUE SCAN AR.HZ_PARTIES_U1
                          INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                          BUFFER SORT
                          INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                          INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                          INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                          MERGE JOIN CARTESIAN
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS
                          NESTED LOOPS
                          NESTED LOOPS
                          TABLE ACCESS BY INDEX ROWID PA.PA_CUST_EVENT_RDL_ALL
                          INDEX FULL SCAN PA.PA_CUST_EVENT_REV_DIST_LINE_N4
                          TABLE ACCESS BY INDEX ROWID PA.PA_EVENTS
                          INDEX RANGE SCAN PA.PA_EVENTS_U1
                          INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                          INDEX RANGE SCAN PA.PA_PROJECTS_U1
                          INDEX UNIQUE SCAN PA.PA_TASKS_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          BUFFER SORT
                          INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                          MERGE JOIN CARTESIAN
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS OUTER
                          NESTED LOOPS ANTI
                          NESTED LOOPS OUTER
                          NESTED LOOPS
                          NESTED LOOPS
                          NESTED LOOPS
                          VIEW PA.index$_join$_064
                          HASH JOIN
                          INDEX FAST FULL SCAN PA.PA_DRAFT_INVOICE_ITEMS_N5
                          INDEX FAST FULL SCAN PA.PA_DRAFT_INVOICE_ITEMS_N2
                          TABLE ACCESS BY INDEX ROWID PA.PA_EVENTS
                          INDEX RANGE SCAN PA.PA_EVENTS_U1
                          INDEX UNIQUE SCAN HR.HR_ALL_ORGANIZATION_UNTS_TL_PK
                          INDEX RANGE SCAN PA.PA_PROJECTS_U1
                          INDEX UNIQUE SCAN PA.PA_TASKS_U1
                          TABLE ACCESS BY INDEX ROWID PA.PA_CUST_EVENT_RDL_ALL
                          INDEX RANGE SCAN PA.PA_CUST_EVENT_REV_DIST_LINE_N2
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          INDEX UNIQUE SCAN GL.GL_DAILY_CONVERSION_TYPES_U1
                          BUFFER SORT
                          INDEX RANGE SCAN APPLSYS.FND_LOOKUP_VALUES_U1
                          INDEX UNIQUE SCAN PA.PA_DRAFT_INVOICES_U1
                          INDEX RANGE SCAN PA.PA_PROJECTS_U1
                          TABLE ACCESS BY INDEX ROWID HR.PER_ALL_PEOPLE_F
                          INDEX RANGE SCAN HR.PER_PEOPLE_F_PK
                          FILTER
                          FAST DUAL
                          Note: PLAN_TABLE is older version: Using simple plan output.
                          • 10. Re: Performance issue with the following view pa_draft_inv_line_details_v
                            user2549696
                            Hussein,
                            As whole data is not allowing one post so i posted it part by part.Please check it and Suggest me the Proper solution ASAP.

                            Thanks,
                            RS.
                            • 11. Re: Performance issue with the following view pa_draft_inv_line_details_v
                              Hussein Sawwan-Oracle
                              You only need to check the value of last_analyzed column to verify if the statistics are collected up to date.

                              Please provide the explain plan from the docs referenced above and answer my other questions.

                              Thanks,
                              Hussein
                              • 12. Re: Performance issue with the following view pa_draft_inv_line_details_v
                                user2549696
                                Hi Hussein,
                                Which was submitted in above posts is the gather statistics and Execution Plan for the Query which i mentioned previously.

                                Please check it once and Provide me solution ASAP.

                                Thanks,
                                RS..
                                • 13. Re: Performance issue with the following view pa_draft_inv_line_details_v
                                  Hussein Sawwan-Oracle
                                  user2549696 wrote:
                                  Hi Hussein,
                                  Which was submitted in above posts is the gather statistics and Execution Plan for the Query which i mentioned previously.

                                  Please check it once and Provide me solution ASAP.

                                  Thanks,
                                  RS..
                                  The above does not help.

                                  Please provide the explain plan from the links/docs referenced above.

                                  Thanks,
                                  Hussein
                                  • 14. Re: Performance issue with the following view pa_draft_inv_line_details_v
                                    user2549696
                                    Hussien,
                                    If you dont mind please provide me the reference docs once again which gives exact explain plan.

                                    Thanks,
                                    RS..
                                    1 2 Previous Next