Re: More info on sql query running slow

Rick_Cale_at_teamhealth.com wrote:
>
> Steve,
>
> That query took about 52 seconds to run.
>
> Thanks
> Rick
>
>
> "Stephane
> Faroult" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> <sfaroult_at_oriol cc:
> ecorp.com> Subject: RE: More info on sql query running slow
> Sent by:
> root_at_fatcity.co
> m
>
>
> 03/20/2002
> 10:53 AM
> Please respond
> to ORACLE-L
>
>
>
> Rick,
>
> Curious to know how this would perform :
>
> SELECT COUNT(a.phy_contract_id)
> FROM accrued_and_paid a,
> (select phy_contract_id
> from phy_contracts
> where company_id = 16
> and contract_type = 'IC') b
> WHERE a.hold_payment_flag = 'Y'
> AND b.phy_contract_id = a.phy_contract_id;
>
> >----- Original Message -----
> >From: Rick_Cale_at_teamhealth.com
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >Sent: Wed, 20 Mar 2002 05:58:25
> >
> >Hi All,
> >Oracle 8.1.6
> >
> >I have 2 tables which have been analyzed. The query
> >takes about 30+ seconds
> >to run. If I run it many times it is faster as
> >data gets loaded into
> >buffer
> >cache. I want to optimize when that is not the
> >case. All the fields in the
> >query have a separate index created EXCEPT for
> >ACCRUED_AND_PAID.hold_payment_flag.
> >
> >Phy_Contracts has 10,466 rows.
> >Accrued_and_Paid has 820,919 rows.
> >
> >Here is output from explain plan
> >
> >SELECT STATEMENT Cost = 1382
> > SORT AGGREGATE
> > NESTED LOOPS
> > TABLE ACCESS BY INDEX ROWID PHY_CONTRACTS
> > INDEX RANGE SCAN PCON_CMPY_FK
> > TABLE ACCESS BY INDEX ROWID ACCRUED_AND_PAID
> > INDEX RANGE SCAN ADPD_PCON_FK
> >
> >Any ideas what I can do to speed this query.
> >
> >SELECT COUNT(a.phy_contract_id)
> >FROM accrued_and_paid a, phy_contracts b
> >WHERE a.hold_payment_flag = 'Y'
> >AND b.phy_contract_id = a.phy_contract_id
> >AND b.company_id = 16
> >AND b.contract_type = 'IC';
> >
> >List of single column index on PHY_CONTRACTS
> >INDEX_NAME COLUMN_NAME
> >------------------------------
> >---------------------
> >PCON_CONTRACT_TYPE_IDX CONTRACT_TYPE
> >PCON_CMPY_FK COMPANY_ID
> >PCON_PK PHY_CONTRACT_ID
> >
> >List of single column index on ACCRUED_AND_PAID
> >INDEX_NAME COLUMN_NAME
> >------------------------------ ------------------
> >ADPD_PCON_FK PHY_CONTRACT_ID
> >
> >
> >Thanks
> >Rick
> >

Yek. You should check the plan, there must be a full scan somewhere. I was hoping that the in-line view would be run first (assuming indices are efficient) and the the result used to pull the results from the other table.
Two more things to try :

1. /*+ ORDERED */ and the in-line view first in the FROM clause
2. Not using an in-line view but a a.phy_contract_id in (select ..). In theory it shouldn't make any difference but the CBO has its mysterious ways sometimes.

-- 
Regards,

Stephane Faroult
Oriole Ltd
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).