Sql Statement Tuning

[balasmg]

explain plan for
SELECT /*+ first_rows */
i.ACTUAL_PURCHASE_PRICE,
i.COMM_ID,
cd.CONTRACT_OID LAST_CONTRACT_OID,
c.CONTRACT_ID LAST_CONTRACT_ID,
cd.EFFECTIVE_DATE,
cd.START_DATE,
i.LIST_PRICE,
i.LOCATION_OID,
i.ORDER_NUMBER,
i.QTY,
i.SERIAL_NUMBER,
i.SERIAL_NUMBER_LK,
i.SHIP_DATE,
i.SO_LINE_NUM,
i.OID,
i.VERSION,
i.PO_NUMBER,
p.PRODUCT_ID,
p.PRODUCT_ID_LK,
p.PRODUCT_NAME,
P.PRODUCT_NUMBER,
P.STATUS PRODUCT_STATUS,
cd.COVERAGE_TYPE_OID,
cd.SERV_PART_NUMBER,
c.BUYER_COMPANY_OID,
ct.COVERAGE_NAME ,
L.SELLER_COM_OID,
L.COMPANY_ID,
L.COMPANY_STATUS,
L.STATE_NAME,
L.STATE_CODE,
(
select quote_id
from SAM_quote q
where q.creation_date =
(
select max(q.creation_date)
from SAM_quote q , SAM_quote_detail qd
where q.OID = qd.quote_oid
and i.oid = qd.INS_PRODUCT_OID
)
and rownum < 2
) latest_quote_id ,
(
select oid from SAM_quote q
where q.creation_date =
(
select max(q.creation_date)
from SAM_quote q , SAM_quote_detail qd
where q.OID = qd.quote_oid
and i.oid = qd.INS_PRODUCT_OID
)
and rownum < 2
) latest_quote_oid ,
(
select opportunity_id from SAM_opportunity o
where o.created_time =
(
select max(o.created_time)
from SAM_opportunity o , SAM_opportunity_detail Od
where O.OID = Od.opportunity_oid
and i.oid = Od.INSTALLED_PRODUCT_OID
)
and rownum < 2
) latest_opportunity_id ,
(
select oid from SAM_opportunity o
where o.created_time =
(
select max(o.created_time)
from SAM_opportunity o , SAM_opportunity_detail Od
where O.OID = Od.opportunity_oid
and i.oid = Od.INSTALLED_PRODUCT_OID
)
and rownum < 2
) latest_opportunity_oid
FROM INV_PRODUCT i,
CON_DETAIL cd,
CON c,
PRODUCT p,
LOC l,
COV_TYPE ct
WHERE i.last_contract_detail_oid = cd.oid(+) AND
NVL(cd.contract_oid,-99999) = c.oid(+) AND
L.oid = i.location_oid AND
p.oid = i.product_oid AND
cd.COVERAGE_TYPE_OID = ct.oid(+)
and i.serial_number_lk like '%'


1 select operation, options, object_name
2 from plan_table
3* connect by prior id=parent_id and prior statement_id = statement_id
SQL>
SQL> /

OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ ------------------------------
SELECT STATEMENT
COUNT STOPKEY
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS OUTER
NESTED LOOPS
TABLE ACCESS FULL SAM_COUNTRY
TABLE ACCESS BY INDEX ROWID SAM_LOCATION
INDEX RANGE SCAN IDX_LOCATION_10
TABLE ACCESS BY INDEX ROWID SAM_STATE
INDEX UNIQUE SCAN PK_STATE
TABLE ACCESS BY INDEX ROWID COMPANY
INDEX UNIQUE SCAN PK_COMPANY
TABLE ACCESS BY INDEX ROWID INV_PRODUCT
INDEX RANGE SCAN IDX_INSTALLED_PRODUCT_06
TABLE ACCESS BY INDEX ROWID SAM_PRODUCT
INDEX UNIQUE SCAN PK_PRODUCT
TABLE ACCESS BY INDEX ROWID CON_DETAIL
INDEX UNIQUE SCAN PK_CONTRACT_DETAIL
TABLE ACCESS BY INDEX ROWID COV_TYPE
INDEX UNIQUE SCAN PK_COVERAGE_TYPE
TABLE ACCESS BY INDEX ROWID SAM_COMPANY
INDEX UNIQUE SCAN PK_COMPANY
TABLE ACCESS BY INDEX ROWID CON
INDEX UNIQUE SCAN PK_CONTRACT


HOW TO TUNE THIS STATEMENT.
HOW THESE QUERY CAN BE REWRITTEN
I ALSO ATTACHED SAME FOR CLEAR VISIBILTY.