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 Go to next message
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 Go to previous message
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 (+)
/

Previous Topic: "slave shutdown wait" - event
Next Topic: Can bigger blocksize for indexes help increasing performance and lower I/O
Goto Forum:
  


Current Time: Thu Mar 28 08:29:53 CDT 2024