Hi Tom,

Glad you have bandwidth again.

This is not a forms related question, but about tuning SQL statements.
Its rather complex, and if it will take much of your time then just ignore. Appreciate your time anyway.

In (2) below, the problem SQL is repeated 52 times - the '1' in 'where row_num = 1' increases from 1 to 52.
With execution time (elapsed) of 0.23 per execution, 52 repetitions will be 12 secs, and each time the form is opened the SQL is run 2 x 52 times, thus 2 x 12 sec = 24 sec which the user find unacceptable.
Tuning the forms aside, I am sure the SQL can be improved as it is referring to multiple views. Thus I am here seeking your help to tune the SQL statement.

I've provided tkprof output of the SQL statement, and view definitions of the related SQL(s).
I know this is not very straightforward, and I'm not sure if these are enough to help you understand.
If there is anything missing, let me know and I will provide.


1) I am trying to improve the performance of a customised form in Oracle Business Suite.

2) I did a trace and in the tkprof, I identified this as the problematic SQL:
=============================================================================

********************************************************************************

select distinct pov.po_number
from
poc.moto_gsr_hub_po_number_v pov ,apps.hr_locations hl where pov.item_number
= :1 and pov.vendor_id= :2 and pov.vendor_site_code = :3 and
hl.ship_to_location_id = pov.ship_id and hl.attribute6 = (select hub from
(select rownum row_num, a.* from poc.moto_gsr_hubs_v a ) where row_num = 1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.22 0.20 0 130 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.23 0.23 0 130 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE
0 NESTED LOOPS
0 HASH JOIN
1 TABLE ACCESS FULL HR_LOCATIONS_ALL
1 VIEW
20 COUNT
20 VIEW
20 SORT ORDER BY
20 SORT GROUP BY
88 TABLE ACCESS FULL MOTO_GSR_BUSINESS_MAPPING
0 VIEW
0 SORT UNIQUE
0 UNION-ALL
0 SORT GROUP BY
0 TABLE ACCESS BY INDEX ROWID PO_LINE_LOCATIONS_ALL
1 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
0 INDEX SKIP SCAN MTL_SYSTEM_ITEMS_B_N1 (object id 37999)
0 TABLE ACCESS BY INDEX ROWID PO_LINES_ALL
0 INDEX RANGE SCAN PO_LINES_N1 (object id 45070)
0 TABLE ACCESS BY INDEX ROWID PO_HEADERS_ALL
0 INDEX UNIQUE SCAN PO_HEADERS_U1 (object id 45052)
0 TABLE ACCESS BY INDEX ROWID PO_VENDOR_SITES_ALL
0 INDEX UNIQUE SCAN PO_VENDOR_SITES_U1 (object id 2004534)
0 INDEX RANGE SCAN PO_LINE_LOCATIONS_N2 (object id 45131)
0 TABLE ACCESS BY INDEX ROWID MOTO_GSR_PO_DETAILS
0 INDEX RANGE SCAN MOTO_GSR_PR_DETAILS_IDX (object id 3613210)
0 INDEX UNIQUE SCAN HR_LOCATIONS_ALL_TL_PK (object id 44683)

********************************************************************************

3) The reason for the row_num to repeat from 1 to 52 is to want to compare hl.attribute6 to all the records in moto_gsr_hubs_v.
Currently there are 20 records in moto_gsr_hubs_v. At this moment, the 'where row_num = 21' onwards is meaningless.

4) The actual sql in the form is:
=================================

select distinct pov.po_number
from poc.moto_gsr_hub_po_number_v pov,
apps.hr_locations hl
where pov.item_number = :moto_gsr_item_v.item_number
and pov.vendor_id= :moto_gsr_details.vendor_id
and pov.vendor_site_code = :moto_gsr_details.vendor_site_code
and hl.ship_to_location_id = pov.ship_id
and hl.ATTRIBUTE6 = (select hub
from (select rownum row_num,
a.*
from poc.moto_gsr_hubs_v a )
where row_num = 51 );


5) View definition of moto_gsr_hub_po_number_v is:
==================================================

CREATE OR REPLACE VIEW moto_gsr_hub_po_number_v
AS
SELECT poh.segment1 po_number,
pol.line_num line_number,
msi.inventory_item_id inventory_item_id,
msi.segment1 item_number,
poh.vendor_id vendor_id,
pvsa.vendor_site_code vendor_site_code,
(SUM (NVL (plla.quantity, 0)) - SUM (NVL (plla.quantity_received, 0))) balance_qty,
msi.organization_id org_id,
plla.ship_to_location_id ship_id,
pol.from_header_id,
pol.from_line_id,
pol.line_type_id,
NVL (pol.unit_price, 0) unit_price,
NVL (poh.currency_code, 'USD') currency_code
FROM apps.po_headers_all poh,
apps.po_vendor_sites_all pvsa,
apps.po_lines_all pol,
apps.mtl_system_items msi,
apps.po_line_locations_all plla
WHERE plla.ship_to_organization_id = msi.organization_id
AND poh.vendor_site_id = pvsa.vendor_site_id
AND poh.po_header_id = pol.po_header_id
AND poh.po_header_id = plla.po_header_id
AND pol.item_id = msi.inventory_item_id
AND pol.po_line_id = plla.po_line_id
AND poh.type_lookup_code IN ('STANDARD', 'BLANKET')
AND NVL (poh.authorization_status, 'INCOMPLETE') = 'APPROVED'
AND NVL (poh.closed_code, 'x') NOT LIKE '%CLOSED%'
AND NVL (pol.closed_code, 'x') NOT LIKE '%CLOSED%'
AND NVL (plla.closed_code, 'OPEN') = 'OPEN'
GROUP BY poh.segment1,
pol.line_num,
msi.inventory_item_id,
msi.segment1,
poh.vendor_id,
pvsa.vendor_site_code,
msi.organization_id,
plla.ship_to_location_id,
pol.from_header_id,
pol.line_type_id,
pol.from_line_id,
unit_price,
currency_code
UNION
SELECT po_number, line_number, inventory_item_id, item_number, vendor_id,
vendor_site_code, balance_qty, organization_id org_id,
ship_to_loc_id ship_id, 0 from_header_id, 0 from_line_id,
0 line_type_id, unit_price, currency_code
FROM poc.moto_gsr_po_details;

And the AUTOTRACE output of the first part of the UNION is:


Execution Plan - Original
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5024 Card=1 Bytes=136)
1 0 SORT (GROUP BY) (Cost=5024 Card=1 Bytes=136)
2 1 NESTED LOOPS (Cost=4977 Card=1 Bytes=136)
3 2 NESTED LOOPS (Cost=4975 Card=1 Bytes=114)
4 3 HASH JOIN (Cost=4973 Card=1 Bytes=104)
5 4 TABLE ACCESS (FULL) OF 'PO_LINES_ALL' (Cost=1889 Card=14872 Bytes=505648)
6 4 HASH JOIN (Cost=3054 Card=32183 Bytes=2252810)
7 6 TABLE ACCESS (FULL) OF 'PO_HEADERS_ALL' (Cost=1218 Card=4758 Bytes=204594)
8 6 TABLE ACCESS (FULL) OF 'PO_LINE_LOCATIONS_ALL' (Cost=1802 Card=251795 Bytes=6798465)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'PO_VENDOR_SITES_ALL' (Cost=2 Card=1 Bytes=10)
10 9 INDEX (UNIQUE SCAN) OF 'PO_VENDOR_SITES_U1' (UNIQUE) (Cost=1 Card=1)
11 2 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_B' (Cost=2 Card=1 Bytes=22)
12 11 INDEX (UNIQUE SCAN) OF 'MTL_SYSTEM_ITEMS_B_U1' (UNIQUE) (Cost=1 Card=1)

6) View definition of moto_gsr_hubs_v (used in the problem sql) is:
===================================================================

SELECT hub, MIN (seq_number) seq_num
FROM poc.moto_gsr_business_mapping
WHERE group_name= 'Standard'
AND hub != 'NONKIT'
AND enabled = 'Y'
GROUP BY hub
ORDER BY 2;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104 Card=34 Bytes=748)
1 0 SORT (ORDER BY) (Cost=104 Card=34 Bytes=748)
2 1 SORT (GROUP BY) (Cost=104 Card=34 Bytes=748)
3 2 TABLE ACCESS (FULL) OF 'MOTO_GSR_BUSINESS_MAPPING' (Cost=11 Card=51 Bytes=1122)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
62 consistent gets
61 physical reads
0 redo size
501 bytes sent via SQL*Net to client
288 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
20 rows processed


Really appreciate if you can provide some pointers.

Thank you.

Rgds,
TC

(select hub
                from (select rownum row_num,
                          a.*
                      from poc.moto_gsr_hubs_v a )
                where row_num = 51 ); 

select distinct row_num, pov.po_number
from poc.moto_gsr_hub_po_number_v pov,
    apps.hr_locations hl,
      (select rownum row_num, a.hub
         from poc.moto_gsr_hubs_v a 
        where rownum <= 51 ) X <b>-- MEANINGLESS GARBAGE, but that is what you 
                               -- already have!!!!</b>
where pov.item_number = :moto_gsr_item_v.item_number
  and pov.vendor_id= :moto_gsr_details.vendor_id
  and pov.vendor_site_code = :moto_gsr_details.vendor_site_code
  and hl.ship_to_location_id = pov.ship_id
  and hl.ATTRIBUTE6 = X.HUB