Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql query slow... help me!

Re: sql query slow... help me!

From: Kevin <procsharper_at_gmail.com>
Date: Fri, 15 Jun 2007 14:59:52 -0000
Message-ID: <1181919592.420834.181000@p77g2000hsh.googlegroups.com>


Thank you Charles :)

On Jun 14, 9:29 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 14, 11:24 am, Kevin <procshar..._at_gmail.com> wrote:
>
> > Hi, I'm a software developer and I'm making a new software with
> > Oracle. I made a some sql querys and it's very very slow so I get
> > complain from the users. Is there any way that get it faster result ?
> > please help me.
>
> > here's the sql query
>
> > SELECT '', '0' SELECT_YN, V01.ALLOC_NO, V01.CUSTREQUEST_NO,
> > V01.DIV_NO, V01.CONSOL_NO, DECODE(V01.TR_DT, NULL, '',
>
> > TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.TR_DT, '', '')) TR_DATE,
> > V01.STATUS, T13.CODE_NAME STATUS_NAME, BL_NO,
> > V01.CNTR_NO, V01.CARRIER_TRANS_NO,
> > V01.TRUCKER_CD, T17.CARRIER_NAME, V01.SUB_CARRIER, T18.CARRIER_NAME,
> > V01.CAR_CODE,
>
> (Snip)
>
> Without an explain plan (DBMS Xplan), without knowing the data model,
> and without knowing the Oracle version, it will be hard to make
> suggestions. However, here are a few:
> * Are you retrieving any columns that are unnecessary? If so, remove
> those columns.
>
> * It appears that you are attempting to use the V01 inline view to
> drive into the other tables and views (T11, T12, T13, V04, etc.) using
> an outer join. I have seen cases where Oracle will actually try a
> merge Cartesian join in such cases to drive from the tables into the
> inline view. Consider adding a /* ORDERED */ hint after the first
> SELECT so that the V01 inline view, which is listed first in the FROM
> clause, becomes the driving source.
>
> * Buried deep in the V01 inline view is the following:
> AND T06.ORDERED_CARRIER IN
> (SELECT
> CARRIER_NO
> FROM
> TMS_CARRIERRELATION_TB
> START WITH CARRIER_NO = : i_Carrier_No
> CONNECT BY PRIOR CARRIER_NO = HIGH_CARRIER)
> - try to modify that to a more efficient form.
>
> * Buried deep in the V01 inline view is the following:
> T01.TR_TYPE LIKE : i_Trans_Type | | '%'
> AND NVL(T01.DIRECT_YN, 'N') = 'N'
> AND (T01.BIZ_KIND_CD LIKE : i_BizKind || '%' OR (T01.BIZ_KIND_CD IS
> NULL AND : i_BizKind IS NULL))
> AND T01.ALLOC_NO LIKE NVL(: i_AllocNo, '') | | '%'
> - it is unlikely that any indexes are being used to help improve
> performance with code that looks like the above.
>
> * You frequently call TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC, which
> likely results in a context switch. Context switches degrade
> performance.
>
> Good luck with resolving the performance issue.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Received on Fri Jun 15 2007 - 09:59:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US