Oracle Enterprise Manager
Oracle SQL Explain Plan July 16, 2010 4:59:14 PM SGT 

Target:

    ODEV

 

Version: Oracle 11.1.0.7.0

 

Database: ODEV

 

Schema: APPS

 

Date: Jul 16, 2010 12:00:00 AM


SQL Statement:

  
SELECT qr140.creation_date, mp.organization_code org, 
       qr140.character16 division, qr140.character17 dept, qr140.sequence3 || 
       qr140.sequence5 || qr140.sequence14 prtnumber, qr161.panumber, 
       qr162.canumber, open_prt.open_date prtopendate, 
       closed_prt.close_date prtclosedate, qr140.character4 categoryid, 
       qr140.item_id item_id, prod.segment1 part_number, 
       qr140.character47 customer, qr140.character12 endcustomer, 
       qr140.character8 geoloc, qr140.character6 tatpriority, 
       qr140.character9 keycust, qr_valid.validity, qr140.character21 prodeng, 
       customer.customer_number, qr160.orcvdqty, qr160.splitqty, 
       qr160.custrefid, qr160.datacodes, lkpcc.lookup_code || ' - ' || 
       lkpcc.meaning customercomplaints, lkpcc.lookup_code || ' - ' || 
       lkpcc.meaning failuremode, lkpcc.lookup_code || ' - ' || 
       lkpcc.description defectcode, lkpcc.lookup_code || ' - ' || 
       lkpcc.meaning causeowners, lkpcc.lookup_code || ' - ' || lkpcc.meaning
       cmcode, lkpcc.lookup_code || ' - ' || lkpcc.description rootcause, 
       qr160.cmname, qr160.disposittionqty, closed_prt.close_date - 
       open_prt.open_date prttat, qr140.character25 prtstatus, qr161.type1 type, 
       qr140.character4 return_category, qr140.character49 mfg_release, 
       qr140.character10 item_status, detail_plan_validity.prt_validity, 
       qr140.character19 cse, xxbi_fiscal_calendar_445.month_range_445(
       closed_prt.close_date) prt_close_dt_sysdiff, 
       xxbi_fiscal_calendar_445.period_445(closed_prt.close_date)
       prt_close_dt_period, xxbi_fiscal_calendar_445.month_range_fc445(
       closed_prt.close_date) prt_close_date_fc445
    FROM qa_results qr140, fnd_lookup_values_vl lkpcc, mtl_parameters mp, 
         qa_plans qp, mtl_system_items_b prod, (SELECT min(
                                                       qr165.qa_creation_date)
                                                       AS close_date, 
                                                       qr165.sequence3 || 
                                                       qr165.sequence5 || 
                                                       qr165.sequence14 AS
                                                       prt_no
              FROM qa_results qr165
              WHERE qr165.plan_id IN (165, 203, 402)
                AND qr165.character1 = 'CLOSED'
                AND qr165.status = 2
              GROUP BY qr165.sequence3 || qr165.sequence5 || qr165.sequence14)
         closed_prt, (SELECT qr198.character5 validity, qr198.sequence3 || 
                              qr198.sequence5 || qr198.sequence14 AS prt_no, 
                              qr198.occurrence occurrence
              FROM qa_results qr198
              WHERE qr198.plan_id IN (160, 198, 399)
                AND qr198.status = 2) qr_valid, (SELECT min(
                                                        qr165.qa_creation_date)
                                                        AS open_date, 
                                                        qr165.sequence3 || 
                                                        qr165.sequence5 || 
                                                        qr165.sequence14 AS
                                                        prt_no
              FROM qa_results qr165
              WHERE qr165.plan_id IN (165, 203, 402)
                AND qr165.character1 = 'PRT CREATION QUEUE'
                AND qr165.status = 2
              GROUP BY qr165.sequence3 || qr165.sequence5 || qr165.sequence14)
         open_prt, (SELECT decode(ra.attribute1, 'US', ra.attribute6, 'GB', 
                            ra.attribute6, ra.customer_name) AS char47, 
                            min(ra.customer_number) AS customer_number
              FROM apps.ra_customers ra
              WHERE ra.status = 'A'
                AND nvl(ra.customer_prospect_code, 'CUSTOMER') = 'CUSTOMER'
              GROUP BY decode(ra.attribute1, 'US', ra.attribute6, 'GB', 
                       ra.attribute6, ra.customer_name)) customer, (SELECT qr199.sequence3
                                                                           || 
                                                                           qr199.sequence5
                                                                           || 
                                                                           qr199.sequence14
                                                                           AS
                                                                           qr161_sequence, 
                                                                           qr199.sequence9
                                                                           || 
                                                                           qr199.sequence11
                                                                           || 
                                                                           qr199.sequence15
                                                                           AS
                                                                           panumber, 
                                                                           qr199.character1 type1
              FROM qa_results qr199
              WHERE qr199.plan_id IN (161, 199, 400)
                AND qr199.status = 2) qr161, (SELECT qr200.sequence3 || 
                                                     qr200.sequence5 || 
                                                     qr200.sequence14 AS
                                                     qr162_sequence, 
                                                     qr200.sequence8 || 
                                                     qr200.sequence10 || 
                                                     qr200.sequence7 AS
                                                     canumber
              FROM qa_results qr200
              WHERE qr200.plan_id IN (162, 200, 401)
                AND qr200.status = 2) qr162, (SELECT upper(trim(
                                                     qr198.character4)) AS
                                                     character4, upper(
                                                     trim(qr198.character6)) AS
                                                     character6, upper(
                                                     trim(qr198.character8)) AS
                                                     character8, upper(
                                                     trim(qr198.character11))
                                                     AS character11, 
                                                     upper(trim(
                                                     qr198.character10)) AS
                                                     character10, upper(
                                                     trim(qr198.character9)) AS
                                                     character9, 
                                                     qr198.sequence3 || 
                                                     qr198.sequence5 || 
                                                     qr198.sequence14 AS
                                                     qr160_sequence, 
                                                     qr198.character1 orcvdqty, 
                                                     qr198.character2 splitqty, 
                                                     qr198.character14 custrefid, 
                                                     qr198.character16 datacodes, 
                                                     qr198.character10 cmname, 
                                                     qr198.character12 disposittionqty, 
                                                     qr198.occurrence occurrence
              FROM qa_results qr198
              WHERE qr198.plan_id IN (160, 198, 399)
                AND qr198.status = 2) qr160, (SELECT decode(max(qr.character5), 
                                                     'Yes', 'Yes', 'No', 'No', 
                                                     'Untested', 'No')
                                                     prt_validity, 
                                                     qr.sequence3 || 
                                                     qr.sequence5 || 
                                                     qr.sequence14 AS
                                                     prt_number
              FROM qa_results qr
              WHERE qr.plan_id IN (160, 198, 399)
                AND qr.status = 2
              GROUP BY qr.sequence3 || qr.sequence5 || qr.sequence14)
         detail_plan_validity
    WHERE qr140.plan_id IN (140, 196, 398)
      AND qr140.status = 2
      AND qr160.qr160_sequence = qr140.sequence3 || qr140.sequence5 || 
          qr140.sequence14
      AND qr140.sequence3 || qr140.sequence5 || qr140.sequence14 = 
          closed_prt.prt_no (+)
      AND qr140.sequence3 || qr140.sequence5 || qr140.sequence14 = 
          open_prt.prt_no
      AND qr140.sequence3 || qr140.sequence5 || qr140.sequence14 = 
          qr_valid.prt_no
      AND qr140.sequence3 || qr140.sequence5 || qr140.sequence14 = 
          detail_plan_validity.prt_number (+)
      AND qr_valid.occurrence = qr160.occurrence
      AND qr140.character47 = customer.char47
      AND lkpcc.lookup_type (+) = 'XXAT_CUST_COMP'
      AND lkpcc.end_date_active (+) IS NULL
      AND lkpcc.lookup_type (+) = 'XXAT_OQA_DEF_CODE'
      AND lkpcc.end_date_active (+) IS NULL
      AND lkpcc.lookup_type (+) = 'XXAT_PRT_DEFECT_ROOT_CAUSE'
      AND lkpcc.end_date_active (+) IS NULL
      AND lkpcc.lookup_type (+) = 'XXAT_PRT_CAUSE_OWNER'
      AND lkpcc.end_date_active (+) IS NULL
      AND lkpcc.lookup_type (+) = 'XXAT_OQA_CM'
      AND lkpcc.end_date_active (+) IS NULL
      AND lkpcc.lookup_type (+) = 'XXAT_PRT_DEFECT_ROOT_CAUSE'
      AND lkpcc.end_date_active (+) IS NULL
      AND qr160.character4 = upper(trim(lkpcc.meaning (+)))
      AND qr160.character6 = upper(trim(lkpcc.meaning (+)))
      AND qr160.character8 = upper(trim(lkpcc.description (+)))
      AND qr160.character11 = upper(trim(lkpcc.meaning (+)))
      AND qr160.character10 = upper(trim(lkpcc.meaning (+)))
      AND qr160.character9 = upper(trim(lkpcc.description (+)))
      AND qr140.organization_id = qp.organization_id
      AND qr140.plan_id = qp.plan_id
      AND qp.organization_id = mp.organization_id
      AND qr161.qr161_sequence (+) = qr140.sequence3 || qr140.sequence5 || 
          qr140.sequence14
      AND qr162.qr162_sequence (+) = qr140.sequence3 || qr140.sequence5 || 
          qr140.sequence14
      AND qr140.item_id = prod.inventory_item_id
      AND qr140.organization_id = prod.organization_id
      AND mp.organization_code IN ('F02', 'F04', 'F01')
    ORDER BY qr140.organization_id, qr140.character4, qr140.character16, 
             qr140.character17, qr140.item_id

Optimizer Mode Used:

  ALL_ROWS

Total Cost:

  140,257

Execution Steps:

Step #Step Name
60SELECT STATEMENT
59SORT [ORDER BY]
58HASH JOIN [OUTER]
52HASH JOIN [OUTER]
47HASH JOIN [OUTER]
42NESTED LOOPS [OUTER]
39NESTED LOOPS
35NESTED LOOPS
31HASH JOIN [OUTER]
26NESTED LOOPS
24NESTED LOOPS
22HASH JOIN
16NESTED LOOPS
14NESTED LOOPS
12NESTED LOOPS
9MERGE JOIN [CARTESIAN]
4APPS. VIEW
3HASH [GROUP BY]
2QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
1APPS.XXAT_QA_RESLTS_CHAR1 INDEX [RANGE SCAN]
8BUFFER [SORT]
7INLIST ITERATOR
6QA.QA_PLANS TABLE ACCESS [BY INDEX ROWID]
5QA.QA_PLANS_U1 INDEX [UNIQUE SCAN]
11INV.MTL_PARAMETERS TABLE ACCESS [BY INDEX ROWID]
10INV.MTL_PARAMETERS_U1 INDEX [UNIQUE SCAN]
13QA.QA_RESULTS_U1 INDEX [RANGE SCAN]
15QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
21APPS. VIEW
20HASH [GROUP BY]
19HASH JOIN
17AR.HZ_CUST_ACCOUNTS TABLE ACCESS [FULL]
18AR.HZ_PARTIES TABLE ACCESS [FULL]
23INV.MTL_SYSTEM_ITEMS_B_U1 INDEX [UNIQUE SCAN]
25INV.MTL_SYSTEM_ITEMS_B TABLE ACCESS [BY INDEX ROWID]
30APPS. VIEW
29HASH [GROUP BY]
28QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
27APPS.XXAT_QA_RESLTS_CHAR1 INDEX [RANGE SCAN]
34INLIST ITERATOR
33QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
32QA.QA_RESULTS_U1 INDEX [RANGE SCAN]
38INLIST ITERATOR
37QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
36QA.QA_RESULTS_U2 INDEX [UNIQUE SCAN]
41APPLSYS.FND_LOOKUP_VALUES TABLE ACCESS [BY INDEX ROWID]
40APPLSYS.FND_LOOKUP_VALUES_U1 INDEX [RANGE SCAN]
46APPS. VIEW
45INLIST ITERATOR
44QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
43QA.QA_RESULTS_U1 INDEX [RANGE SCAN]
51APPS. VIEW
50INLIST ITERATOR
49QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
48QA.QA_RESULTS_U1 INDEX [RANGE SCAN]
57APPS. VIEW
56HASH [GROUP BY]
55INLIST ITERATOR
54QA.QA_RESULTS TABLE ACCESS [BY INDEX ROWID]
53QA.QA_RESULTS_U1 INDEX [RANGE SCAN]

Step #DescriptionEst. CostEst. Rows ReturnedEst. KBytes Returned
  1   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XXAT_QA_RESLTS_CHAR1.34--
  2   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.610.03
  3   This plan step has no supplementary description information.
  4   This plan step represents the execution plan for the subquery defined by the view .710.084
  5   This plan step retrieves a single ROWID from the B*-tree index QA_PLANS_U1.13--
  6   This plan step retrieves rows from table QA_PLANS through ROWID(s) returned by an index.230.023
  7   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  8   This plan step sorts the buffer row source.930.023
  9   This plan step accepts two sets of rows and builds the set of all possible combinations of row pairs. The result set grows exponentially with the size of the row sets joined.930.275
  10   This plan step retrieves a single ROWID from the B*-tree index MTL_PARAMETERS_U1.01--
  11   This plan step retrieves rows from table MTL_PARAMETERS through ROWID(s) returned by an index.110.008
  12   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.1220.199
  13   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index QA_RESULTS_U1.172767--
  14   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.50310.189
  15   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.31210.09
  16   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.------
  17   This plan step retrieves all rows from table HZ_CUST_ACCOUNTS.494,21282.266
  18   This plan step retrieves all rows from table HZ_PARTIES.4,422680,84525,930.62
  19   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.4,4824,212242.684
  20   This plan step has no supplementary description information.
  21   This plan step represents the execution plan for the subquery defined by the view .4,4834,212386.648
  22   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.4,98610.281
  23   This plan step retrieves a single ROWID from the B*-tree index MTL_SYSTEM_ITEMS_B_U1.11--
  24   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.4,98810.304
  25   This plan step retrieves rows from table MTL_SYSTEM_ITEMS_B through ROWID(s) returned by an index.210.022
  26   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.------
  27   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index XXAT_QA_RESLTS_CHAR1.34--
  28   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.610.03
  29   This plan step has no supplementary description information.
  30   This plan step represents the execution plan for the subquery defined by the view .710.084
  31   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.4,99610.388
  32   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index QA_RESULTS_U1.512114,323--
  33   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.21,2161694.786
  34   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  35   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.26,21252.08
  36   This plan step retrieves a single ROWID from the B*-tree index QA_RESULTS_U2.41--
  37   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.510.093
  38   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  39   This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause.26,23710.509
  40   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index FND_LOOKUP_VALUES_U1.21--
  41   This plan step retrieves rows from table FND_LOOKUP_VALUES through ROWID(s) returned by an index.310.069
  42   This plan step joins two sets of rows by iterating over the outer, or driving, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause; in addition to the rows satisfying that condition, Oracle also returns all rows from the row set not containing the outer join operator (+) which failed to satisfy that condition.26,24010.578
  43   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index QA_RESULTS_U1.513114,323--
  44   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.21,21816,872477.82
  45   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  46   This plan step represents the execution plan for the subquery defined by the view .21,21816,8723,806.086
  47   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.47,45897.233
  48   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index QA_RESULTS_U1.513114,323--
  49   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.21,21716,872296.578
  50   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  51   This plan step represents the execution plan for the subquery defined by the view .21,21716,8722,537.391
  52   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.68,6761,5551,483.628
  53   This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index QA_RESULTS_U1.513114,323--
  54   This plan step retrieves rows from table QA_RESULTS through ROWID(s) returned by an index.21,21716,872378.961
  55   This plan step loops through the query's IN list predicate, executing its child step for each value found.
  56   This plan step has no supplementary description information.
  57   This plan step represents the execution plan for the subquery defined by the view .21,22016,8721,318.125
  58   This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice.89,901262,386270,841.799
  59   This plan step accepts a set of rows from its child node, and sorts them on a per-column basis using the query's ORDER BY clause.140,257262,386270,841.799
  60   This plan step designates this statement as a SELECT statement.140,257262,386270,841.799