Home » RDBMS Server » Performance Tuning » Tuning the sql by using explain plan
| Tuning the sql by using explain plan [message #138590] |
Thu, 22 September 2005 10:13  |
balasmg
Messages: 31
Registered: June 2005
Location: bangalore
|
Member |
|
|
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.
|
|
|
|
| Re: Tuning the sql by using explain plan [message #138602 is a reply to message #138590] |
Thu, 22 September 2005 11:05  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Did you try something like the following? (Warning, untested code.)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
, qmax.quote_id latest_quote_id
, qmax.oid latest_quote_oid
, ip.opportunity_id latest_opportunity_id
, ip.oid latest_opportunity_oid
FROM inv_product i
, con_detail cd
, con c
, product p
, loc l
, cov_type ct
, (SELECT a.ins_product_oid
, a.quote_id
, a.oid
FROM (SELECT qd.ins_product_oid
, q.quote_id
, q.oid
, q.creation_date
, MAX(q.creation_date)
OVER (PARTITION BY qd.ins_product_oid) max_creation_date
, ROW_NUMBER()
OVER (PARTITION BY qd.ins_product_oid
ORDER BY q.creation_date DESC) rnk
FROM sam_quote q
, sam_quote_detail qd
WHERE q.oid = q.quote_oid) a
WHERE a.creation_date = a.max_creation_date
AND a.rnk = 1) qmax
, (SELECT b.installed_product_id
, b.opportunity_id
, b.oid
FROM (SELECT od.installed_product_id
, o.opportunity_id
, o.oid
, o.created_time
, MAX(o.created_time)
OVER (PARTITION BY od.installed_product_id) max_created_time
, ROW_NUMBER()
OVER (PARTITION BY od.installed_product_id
ORDER BY o.created_time DESC) rnk
FROM sam_opportunity o
, sam_opportunity_detail od
WHERE od.opportunity_oid = o.oid) b
WHERE b.created_time = b.max_created_time
AND b.rnk = 1) ip
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 IS NOT NULL
AND i.oid = ip.installed_product_oid (+)
AND i.oid = qmax.ins_product_oid (+)
/
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 17:16:58 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
