Sql Statement Tuning

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.

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 ,

---

I think you do not need subquery.
Try this:

Code:

---

select max(quote_id)
from  SAM_quote q , SAM_quote_detail qd
where q.OID = qd.quote_oid
and    i.oid = qd.INS_PRODUCT_OID

---

Tamil

The /*+first_rows */ hint will tend to favour indexed paths rather than hash or merge join. If it really is you wish to get the first rows as quickly as possible, then it should help. However, if you want total throughput on the query, then you might be better removing it.

The second problem is all your inline selects. These will be executed row-by-row, hence all the nested loops in the plan. Although the joins are indexed, having four futher index lookups for each row of the main query will slow things down, making the overall throughput of the query quite bad.

I would try to re-write it with fewer inline selects. I think the most efficient way to do this would be to make use of an OLAP function such as ROW_NUMBER().

Code:

---

SELECT
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,
LATEST_QUOTE_PROD.QUOTE_iD,
LATEST_QUOTE_PROD.OID,
LATEST_SAM_PROD.OPPORTUNITY_ID,
LATEST_SAM_PROD.OID
FROM INV_PRODUCT i,
CON_DETAIL cd,
CON c,
PRODUCT p,
LOC l,
COV_TYPE ct,
(select quote_id, oid, ins_product_id
from
(select
q.quote_id,
q.oid,
q.ins_product_id,
rownumber() over (partition by qd.ins_product_oid order by q.creation_date desc) as quote_prod_rank
from
sam_quote q,
sam_quote_detail qd
where q.OID = qd.quote_oid) x
where x.quote_prod_rank = 1) latest_quote_prod,
(select opportunity_id, oid, INSTALLED_PRODUCT_OID
from
(select
o.opportunity_id, o.oid, od.INSTALLED_PRODUCT_OID,
rownumber() over (partition by od.INSTALLED_PRODUCT_OID order by od.created_time desc) as sam_prod_rank
from SAM_opportunity o , SAM_opportunity_detail Od
where O.OID = Od.opportunity_oid) x
where sam_prod_rank = 1) latest_sam_prod
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 '%'
and latest_quote_prod.ins_product_oid (+) = i.oid
and latest_sam_prod.INSTALLED_PRODUCT_OID (+) = i.oid

---

Are you using any view as i see
"TABLE ACCESS FULL SAM_COUNTRY"

if so and SAM_Country has large amount of data you need to concentrate tuning this view.

-Malay