Oracle-L: Re: More info on sql query running slow

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 21 Mar 2002 11:38:24 -0800
Message-ID: <F001.0043022D.20020321113824@fatcity.com>

I agree with /*+ORDERED */ hint suggestion (in combination with inline view).
I've seen cases, when inline view itself didn't do the job, but along with /*+ORDERED */ hint proved to be a huge performance benefit.

So, try:

SELECT /*+ORDERED */ 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;

or, may be changing order of "a" and "b".

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, March 20, 2002 2:50 PM

> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: ineyman_at_perceptron.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).

Received on Thu Mar 21 2002 - 13:38:24 CST