Re: sql query slow... help me!

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 Thu Jun 14 2007 - 11:29:29 CDT