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: <fitzjarrell_at_cox.net>
Date: Thu, 14 Jun 2007 09:31:31 -0700
Message-ID: <1181838691.601241.222670@i13g2000prf.googlegroups.com>


On Jun 14, 10: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,
> T20.CAR_NO, V01.PLATE_NO, V01.GPS_MOBILE_NUM
> GPS_MOBILE_NUM, V01.DRIVER, T21.USER_NAME, V01.MOBILE_NUM CELLULAR,
> V01.DPTR_AREA_CD, T11.AREA_NAME, V01.COLL_FR,
> V01.COLL_FR_NM, V01.COLL_FR_DESC, DECODE(V01.LOAD_BOOKING_DT, NULL,
> '',
>
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_BOOKING_DT,
> V01.LOAD_BOOKING_TM, '')) LOAD_BOOKING_DATETIME,
> DECODE(V01.LOAD_ETA_DATE, NULL, '',
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ETA_DATE,
> V01.LOAD_ETA_TIME, ''))
> LOAD_ETA_DATETIME, DECODE(V01.LOAD_ATA_DATE,
> NULL, '', TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ATA_DATE,
> V01.LOAD_ATA_TIME, '')) LOAD_ATA_DATETIME,
> DECODE(V01.LOAD_ATD_DATE, NULL, '',
>
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.LOAD_ATD_DATE,
> V01.LOAD_ATD_TIME, '')) LOAD_ATD_DATETIME,
> V01.ATD_STOP_REASON, V01.ARR_AREA_CD,
> T12.AREA_NAME, V01.DELV_TO, V01.DELV_TO_NM, V01.DELV_TO_DESC,
> DECODE(V01.UNLOAD_BOOKING_DT, NULL, '',
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_BOOKING_DT,
> V01.UNLOAD_BOOKING_TM, ''))
> UNLOAD_BOOKING_DATETIME, DECODE(V01.UNLOAD_ETA_DATE, NULL, '',
>
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ETA_DATE,
> V01.UNLOAD_ETA_TIME, '')) UNLOAD_ETA_DATETIME,
> DECODE(V01.UNLOAD_ATA_DATE, NULL, '',
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ATA_DATE,
> V01.UNLOAD_ATA_TIME,
> '')) UNLOAD_ATA_DATETIME,
> DECODE(V01.UNLOAD_ATU_DATE, NULL, '',
>
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.UNLOAD_ATU_DATE,
> V01.UNLOAD_ATU_TIME, '')) UNLOAD_ATD_DATETIME,
> V01.ATU_STOP_REASON, PU_ORD_NO, PU_ORD_SEQ,
> MBL_NO, PO_NO, V01.ISSUE_NO, V01.SEAL_NO, V01.CUST_CD, T15.CUST_NAME,
> REAL_CUST_NAME, PACK_QTY, UNALLOC_PACK_QTY,
> UNALLOC_PACK_QTY/* , ALLOC_PACK_QTY*/ , CARGO_WT,
> UNALLOC_CARGO_WT,
> UNALLOC_CARGO_WT/* ,
> ALLOC_CARGO_WT*/ , CARGO_CBM, UNALLOC_CARGO_CBM,
> UNALLOC_CARGO_CBM/* ,
> ALLOC_CARGO_CBM*/ , CARGO_VOL_WT, UNALLOC_CARGO_VOL_WT,
> UNALLOC_CARGO_VOL_WT/* ,
> ALLOC_CARGO_VOL_WT*/ , LINE_CD, T16.CUST_NAME LINE_NM, V01.VSL_NM,
> V01.VOYAGE, V01.POL_CD,
> V01.POD_CD, V04.ALLOC_FAMT + V04.ALLOC_VAT_FAMT,
> V03.TRANS_FAMT + V03.TRANS_VAT_FAMT, V01.TRANS_TON, V01.REMARK,
> V01.DESCRIPTION, V01.TR_TYPE, V01.PACK_UNIT,
> V01.ACCOUNT_GB, T23.CODE_NAME/*żżżżż*/ , V01.CAR_TON, T24.TON_DESC/
> *żż*/ ,
> T24.CAPACITY_CODE, T20.CAR_TYPE, T25.TYPE_NAME,
> V01.COSTCONFIRM_YN, V01.TRANS_NO, V01.FC_NO,
> V01.FC_REQUESTED_CARRIER,
> V01.FC_CAR_SEQ, TRANS_CNT, ORDER_RANK,
> V01.MOBILE_NUM2, V01.BOOK_TYPE, DECODE(V01.TRUCKER_ALLOC_DT, NULL,
> '',
>
> TMS_COMMONFUNC_Pk.GET_VIEWDATETIMESTR_FC(V01.TRUCKER_ALLOC_DT,
> V01.TRUCKER_ALLOC_TM, '')) TRUCKER_ALLOC_DATETIME,
> V01.ENG_NM, V01.TEL1, V01.EMAIL
> FROM (SELECT MIN(T01.ALLOC_NO) ALLOC_NO, MIN(T01.DIV_NO)
> DIV_NO, MIN(T01.TR_TYPE) TR_TYPE, MIN(T01.DPTR_PLAN_DT)
> LOAD_BOOKING_DT,
> MIN(T01.DPTR_PLAN_TM)
> LOAD_BOOKING_TM, MIN(T01.ARR_PLAN_DT) UNLOAD_BOOKING_DT,
> MIN(T01.ARR_PLAN_TM)
> UNLOAD_BOOKING_TM,
> MIN(T01.DPTR_AREA_CD) DPTR_AREA_CD, MIN(T01.ARR_AREA_CD) ARR_AREA_CD,
> MAX(T01.PACK_QTY)
> PACK_QTY,
> MAX(T01.UNALLOC_PACK_QTY) UNALLOC_PACK_QTY, SUM(T01.ALLOC_PACK_QTY)
> ALLOC_PACK_QTY, SUM(T04.QTY)
> LOAD_QTY,
> MAX(T01.CARGO_WT) CARGO_WT, MAX(T01.UNALLOC_CARGO_WT)
> UNALLOC_CARGO_WT, SUM(T01.ALLOC_CARGO_WT)
> ALLOC_CARGO_WT,
> MAX(T01.CARGO_CBM) CARGO_CBM, MAX(T01.UNALLOC_CARGO_CBM)
> UNALLOC_CARGO_CBM,
> SUM(T01.ALLOC_CARGO_CBM)
> ALLOC_CARGO_CBM, MAX(T01.CARGO_VOL_WT) CARGO_VOL_WT,
> MAX(T01.UNALLOC_CARGO_VOL_WT)
> UNALLOC_CARGO_VOL_WT,
> SUM(T01.ALLOC_CARGO_VOL_WT) ALLOC_CARGO_VOL_WT, MIN(T01.PACK_UNIT)
> PACK_UNIT,
> MIN(T01.PU_REF_NO)
> PU_REF_NO, MIN(T01.STATUS) STATUS, MIN(T05.CAR_CAPACITY) CAR_TON,
> MIN(T01.CAR_QTY) CAR_QTY,
> MIN(T01.CUST_CD)
> CUST_CD, MIN(T01.TRUCKER_CD) OR_TRUCKER_CD, MIN(T06.ORDERED_CARRIER)
> TRUCKER_CD,
>
> MIN(T01.TRUCKER_CANCEL_YN) TRUCKER_CANCEL_YN,
> MIN(T01.TRUCKER_CANCEL_RSN) TRUCKER_CANCEL_RSN,
> MIN(T01.CARGO_TYPE)
> CARGO_TYPE, MIN(T01.DECL_NO) DECL_NO, MIN(T01.BL_NO) BL_NO,
> MIN(T01.ARR_DT) ARR_DT,
> MIN(T01.OB_DT) OB_DT,
> MIN(T01.TR_DT) TR_DT, MIN(T01.DPTR_BND_AREA) DPTR_BND_AREA,
> MIN(T01.DPTR_DESC) DPTR_DESC,
> MIN(T01.ARR_BND_AREA)
> ARR_BND_AREA, MIN(T01.ARR_DESC) ARR_DESC, MIN(T01.COLL_FR) COLL_FR,
> MIN(T01.COLL_FR_NM)
> COLL_FR_NM,
> MIN(T01.COLL_FR_DESC) COLL_FR_DESC, MIN(T01.DELV_TO) DELV_TO,
> MIN(T01.DELV_TO_NM) DELV_TO_NM,
> MIN(T01.DELV_TO_DESC)
> DELV_TO_DESC, MIN(T01.PU_ORD_NO) PU_ORD_NO, MIN(T01.PU_ORD_SEQ)
> PU_ORD_SEQ,
> MIN(T01.AIR_TR_YN)
> AIR_TR_YN, MIN(T01.LINE_ORD_NO) LINE_ORD_NO, MIN(T01.BOOKING_NO)
> BOOKING_NO, MIN(T01.CY_CD)
> CY_CD, MIN(T01.LINE_CD)
> LINE_CD, MIN(T01.FILE_NO) FILE_NO, MIN(T01.PO_NO) PO_NO,
> MIN(T01.DPTR_PORT_CD) DPTR_PORT_CD,
> MIN(T01.CUST_PIC_NM)
> CUST_PIC_NM, MIN(T01.CUST_PIC_TEL) CUST_PIC_TEL, MIN(T01.FT20_QTY)
> FT20_QTY, MIN(T01.FT40_QTY)
> FT40_QTY,
> MIN(T01.HBL_DEST) HBL_DEST, MIN(T01.POL_CD) POL_CD, MIN(T01.POD_CD)
> POD_CD, MIN(T01.MBL_NO) MBL_NO,
> MAX(T01.VSL_NM) VSL_NM,
> MIN(T01.VOYAGE) VOYAGE, MAX(T01.READ_YN) READ_YN, MAX(T01.ARR_CHG_YN)
> ARR_CHG_YN,
> MIN(T01.TOT_CHRG_WT)
> TOT_CHRG_WT, MIN(T01.ALLOC_PIC_NM) ALLOC_PIC_NM,
> MIN(T01.ALLOC_PIC_TEL) ALLOC_PIC_TEL,
>
> MIN(T01.TRUCKER_ALLOC_DT) TRUCKER_ALLOC_DT, MIN(T01.TRUCKER_ALLOC_TM)
> TRUCKER_ALLOC_TM, MIN(T01.REAL_CUST_NAME)
> REAL_CUST_NAME,
> MIN(T01.REMARK) REMARK, MIN(T06.CNTR_TYPE) CNTR_TYPE,
> MIN(T06.CNTR_SIZE) CNTR_SIZE,
> MIN(T06.LOAD_ETA_DATE)
> LOAD_ETA_DATE, MIN(T06.LOAD_ETA_TIME) LOAD_ETA_TIME,
> MIN(T03.REQUEST_NO) REQUEST_NO,
> MIN(T03.SEQ) SEQ,
> MIN(T03.UNLOAD_ETA_DATE) UNLOAD_ETA_DATE, MIN(T03.UNLOAD_ETA_TIME)
> UNLOAD_ETA_TIME,
> MIN(T05.TRANS_NO)
> TRANS_NO, MIN(T05.ACCOUNT_GB) ACCOUNT_GB, MIN(T05.TRANS_TON)
> TRANS_TON, MIN(T05.CARRIER_NO)
> SUB_CARRIER,
> MIN(T05.CARCODE) CAR_CODE, MIN(T05.USER_NO) DRIVER,
> MIN(T05.MOBILE_NUM) MOBILE_NUM,
> MIN(T05.MOBILE_NUM2)
> MOBILE_NUM2, MIN(T05.PLATE_NO) PLATE_NO/* ,
> MIN(T05.MOBILE_NUM2) MOBILE_NUM2*/ ,
> MIN(T05.GPS_MOBILE_NUM)
> GPS_MOBILE_NUM, MIN(T05.SEAL_NO) SEAL_NO, MIN(T05.ISSUE_NO) ISSUE_NO,
> MIN(T05.DESCRIPTION)
> DESCRIPTION,
> MIN(T05.COSTCONFIRM_YN) COSTCONFIRM_YN, MIN(T04.LOAD_PLACE)
> LOAD_PLACE, MIN(T04.LOAD_DATE)
> LOAD_ATA_DATE,
> MIN(T04.LOAD_TIME) LOAD_ATA_TIME, MIN(T04.LOAD_ATD_DATE)
> LOAD_ATD_DATE, MIN(T04.LOAD_ATD_TIME)
> LOAD_ATD_TIME,
> MIN(T04.ATD_STOP_REASON) ATD_STOP_REASON, MIN(T04.UNLOAD_PLACE)
> UNLOAD_PLACE,
> MIN(T04.UNLOAD_DATE)
> UNLOAD_ATA_DATE, MIN(T04.UNLOAD_TIME) UNLOAD_ATA_TIME,
> MIN(T04.UNLOAD_ATU_DATE)
> UNLOAD_ATU_DATE,
> MIN(T04.UNLOAD_ATU_TIME) UNLOAD_ATU_TIME, MIN(T04.ATU_STOP_REASON)
> ATU_STOP_REASON,
> MIN(T07.FC_NO) FC_NO,
> MIN(T07.REQUESTED_CARRIER) FC_REQUESTED_CARRIER, MIN(T07.CAR_SEQ)
> FC_CAR_SEQ,
>
> MAX(T05.CARRIER_TRANS_NO) CARRIER_TRANS_NO, RANK() OVER (PARTITION BY
> MIN(T05.TRANS_NO)
> ORDER BY T01.ALLOC_NO) ORDER_RANK,
> COUNT(NVL(T05.TRANS_NO, '-1')) TRANS_CNT, MIN(T08.CNTR_NO) CNTR_NO,
> MIN(T06.CUSTREQUEST_NO) CUSTREQUEST_NO,
> MIN(T01.BOOK_TYPE) BOOK_TYPE, NVL(MAX(T01.DIRECT_YN), 'N') DIRECT_YN,
> MIN(T06.CONSOL_NO) CONSOL_NO, MIN(T01.ENG_NM)
> ENG_NM, MIN(T01.TEL1) TEL1, MIN(T01.EMAIL) EMAIL
> FROM TMS_PAN_ORDER_TB T01, TMS_ORDERREQUEST_TB T02,
> TMS_REQUESTGOODS_TB T03, TMS_TRANSGOODS_TB T04, TMS_TRANS_TB T05,
> TMS_REQUEST_TB T06, TMS_FCREQUESTARRANGE_TB T07,
> TMS_ORDER_CNTRDESC_TB T08
> WHERE 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, '') | | '%' AND
> T01.ALLOC_NO = T02.ALLOC_NO(+) AND
> T01.DIV_NO = T02.DIV_NO(+) /*AND T01.TRUCKER_CD
> IN*/ 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)
> /*AND (T01.DPTR_PLAN_DT >= i_Search_SDate AND
> T01.DPTR_PLAN_DT <= i_Search_EDate)*/ AND T06.LOAD_DATE BETWEEN :
> i_Search_SDate AND
> : i_Search_EDate AND (T01.DPTR_AREA_CD LIKE :
> i_Dept_Area | | '%' OR
> (: i_Dept_Area IS NULL AND T01.DPTR_AREA_CD IS
> NULL)) AND (T01.ARR_AREA_CD LIKE : i_Arr_Area | | '%' OR
> (: i_Arr_Area IS NULL AND T01.ARR_AREA_CD IS
> NULL)) AND (T01.BL_NO LIKE : i_HBLNo | | '%' OR
> (: i_HBLNo IS NULL AND T01.BL_NO IS NULL)) AND
> (T06.CNTR_NO LIKE : i_CNTRNo | | '%' OR
> (: i_CNTRNo IS NULL AND T06.CNTR_NO IS NULL))
> /* AND (T01.STATUS IN
> (SELECT CAR_CODE FROM TABLE (:L_CARS)) OR T01.STATUS IS NULL)*/ AND
> (T01.ENG_NM LIKE : i_BookingUser | | '%' OR
> (: i_BookingUser IS NULL AND T01.ENG_NM IS
> NULL)) AND T02.REQUEST_NO = T03.REQUEST_NO(+) AND T02.SEQ = T03.SEQ(+)
> AND
> T03.REQUEST_NO = T04.REQUEST_NO(+) AND T03.SEQ =
> T04.SEQ(+) AND T04.TRANS_NO = T05.TRANS_NO(+) AND
> t03.request_no = t06.request_no /*AND
> T04.REQUEST_NO = T06.REQUEST_NO(+)*/ AND T05.TRANS_NO =
> T07.TRANS_NO(+) AND
> T01.ALLOC_NO = T08.ALLOC_NO(+) AND T01.DIV_NO =
> T08.DIV_NO(+)
> GROUP BY T01.ALLOC_NO, T01.DIV_NO) V01, TMS_PAN_AREA_TB T11,
> TMS_PAN_AREA_TB T12, TMS_PAN_ETCODE_TB T13, TMS_CUST_TB T15,
> TMS_CUST_TB T16, TMS_CARRIER_TB T17, TMS_CARRIER_TB T18, TMS_CAR_TB
> T20, TMS_USER_TB T21, TMS_PAN_ETCODE_TB T23,
> TMS_CARCAPACITY_TB T24, TMS_CARTYPE_TB T25, TMS_CARRIER_TB T26,
> (SELECT ALLOC_NO, DIV_NO, SUM(FAMT) TRANS_FAMT, SUM(VAT_FAMT)
> TRANS_VAT_FAMT
> FROM TMS_TRANSORDERCOST_TB
> GROUP BY ALLOC_NO, DIV_NO) V03,
> (SELECT ALLOC_NO, DIV_NO, SUM(FAMT) ALLOC_FAMT, SUM(VAT_FAMT)
> ALLOC_VAT_FAMT
> FROM TMS_ORDERCOST_TB
> GROUP BY ALLOC_NO, DIV_NO) V04
> WHERE V01.DPTR_AREA_CD = T11.AREA_CODE(+) AND V01.ARR_AREA_CD =
> T12.AREA_CODE(+) AND T13.CODE_CLASS(+) = '213' AND
> V01.STATUS = T13.CODE_VALUE(+) AND V01.CUST_CD =
> T15.CUST_NO(+) AND V01.LINE_CD = T16.CUST_NO(+) AND
> V01.TRUCKER_CD = T17.CARRIER_NO(+) AND
> V01.SUB_CARRIER = T18.CARRIER_NO(+) AND V01.ALLOC_NO = V04.ALLOC_NO(+)
> AND
> V01.DIV_NO = V04.DIV_NO(+) AND V01.ALLOC_NO =
> V03.ALLOC_NO(+) AND V01.DIV_NO = V03.DIV_NO(+) AND V01.CAR_CODE =
> T20.CAR_CODE(+) AND
> V01.DRIVER = T21.USER_NO(+) AND
> T23.CODE_CLASS(+) = '209' AND V01.ACCOUNT_GB = T23.CODE_VALUE(+) AND
> V01.CAR_TON = T24.CAR_CAPACITY(+) AND
> T20.CAR_TYPE = T25.CAR_TYPE(+) AND V01.OR_TRUCKER_CD =
> T26.CARRIER_NO(+)
> ORDER BY T13.SORT_ORDER, V01.TRUCKER_ALLOC_DT, V01.TRUCKER_ALLOC_TM,
> V01.ALLOC_NO
This is a mess; how could you expect anyone to make any sense from what you've posted? You have not supplied:

Oracle version ( to four numbers)
Table DDL
Index DDL (if you have indexes)
Explain plan output

Your question of 'can I make it go faster' cannot be answered here. You should have a DBA, go ask him/her about this. If not then at least generate an execution plan:

SQL> @?/rdbms/admin/utlxplan

Table created.

SQL> explain plan set statement_id = 'mess' for <include your jumble of a query here>;

Explained.

If you're using 9.x.x.x or later (which I presume you are) you can then execute:

SQL> select * from table(dbms_xplan.display);

and see what Oracle is doing with your query. Spool that output to a file and post that plan here, along with the other items requested. When we have sufficient information you can possibly receive an answer.

David Fitzjarrell Received on Thu Jun 14 2007 - 11:31:31 CDT

Original text of this message

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