Home » RDBMS Server » Performance Tuning » takes more time to open lov (forms 6i , oracle -9i)
takes more time to open lov [message #287438] Wed, 12 December 2007 04:25 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear all

i have below query which takes more time to open.
when i bring
and nvl(b.vc_quality_flg,'~ ') = 'Y'
and nvl(b.vc_main_store_flg,'~') = 'Y'
to upward then it take liitle less time.

currently it take 11 sec.
please go through my query and tell me how will i improve my performance.


SELECT distinct c.VC_ITEM_CODE,e.VC_ITEM_DESC
FROM PURCHASE.HD_PUR_ORDER a,HD_RECEIPT b ,DT_RECEIPT c,CHALLAN_ORDER d ,MAKESS.MST_ITEM e
WHERE a.vc_comp_code = '01'--:global.vc_comp_code 
and D.VC_COMP_CODE = A.VC_COMP_CODE 
AND D.VC_COMP_CODE = B.VC_COMP_CODE 
AND B.VC_COMP_CODE = C.VC_cOMP_CODE 
AND C.VC_COMP_CODE = E.VC_COMP_CODE 
and nvl(b.vc_quality_flg,'~ ') = 'Y' 
and nvl(b.vc_main_store_flg,'~') = 'Y'
and A.VC_ORDER_NO = D.VC_ORDER_NO 
AND A.dt_ORDER_date = D.dt_ORDER_date 
AND A.NU_SUPPLIER_CODE = D.NU_SUPPLIER_CODE 
AND D.NU_SUPPLIER_CODE = B.NU_PARTY_CODE 
and D.VC_CHALLAN_NO = B.VC_CHALLAN_NO 
AND B.VC_RECEIPT_NO = C.VC_RECEIPT_NO 
AND B.dt_RECEIPT_date = C.dt_RECEIPT_date  
AND C.VC_ITEM_CODE = E.VC_ITEM_CODE 
AND A.VC_ORDER_NO = C.VC_GATE_ORDER_NO 
AND A.dt_ORDER_date = C.dt_ORDER_date  
AND c.dt_receipt_date between :dt_from and :dt_to
order by 1



waiting for ur response..


Regards.
Re: takes more time to open lov [message #287484 is a reply to message #287438] Wed, 12 December 2007 06:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Can you post here execution plan, autotrace output for your query.

Re: takes more time to open lov [message #287610 is a reply to message #287484] Wed, 12 December 2007 22:38 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
sir,

It's very easy to show ex-plan & autotrace from toad.
but unable to copy those things from it.

can u please tell me how could i get ex-plan from sql*plus.
so that i can provide u the ex-plan & autotrace of it.


Regards
Re: takes more time to open lov [message #287612 is a reply to message #287438] Wed, 12 December 2007 22:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page
 In iSQL*Plus, you can also use the Preferences screen to set
 system variables.

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NUM[WIDTH] {10|n}
   ARRAY[SIZE] {15|n}                       PAGES[IZE] {14|n}
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      PAU[SE] {OFF|ON|text}
   AUTOP[RINT] {OFF|ON}                     RECSEP {WR[APPED]|EA[CH]|OFF}
   AUTORECOVERY {OFF|ON}                    RECSEPCHAR {_|c}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         SERVEROUT[PUT] {ON|OFF}
     [EXP[LAIN]] [STAT[ISTICS]]               [SIZE {n | UNLIMITED}] [FOR[MAT]
   BLO[CKTERMINATOR] {.|c|ON|OFF}             {WRA[PPED] |
   CMDS[EP] {;|c|OFF|ON}                       WOR[D_WRAPPED] |
   COLSEP {_|text}                             TRU[NCATED]}]
   CON[CAT] {.|c|ON|OFF}                   *SHIFT[INOUT] {VIS[IBLE] |
   COPYC[OMMIT] {0|n}                         INV[ISIBLE]}
   COPYTYPECHECK {ON|OFF}                  *SHOW[MODE] {OFF|ON}
   DEF[INE] {&|c|ON|OFF}                   *SQLBL[ANKLINES] {OFF|ON}
   DESCRIBE [DEPTH {1|n|ALL}]               SQLC[ASE] {MIX[ED] |
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]     LO[WER] | UP[PER]}
   ECHO {OFF|ON}                           *SQLCO[NTINUE] {> | text}
  *EDITF[ILE] file_name[.ext]              *SQLN[UMBER] {ON|OFF}
   EMB[EDDED] {OFF|ON}                      SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
   ESC[APE] {\|c|OFF|ON}                   *SQLPRE[FIX] {#|c}
   FEED[BACK] {6|n|ON|OFF}                 *SQLP[ROMPT] {SQL>|text}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  SQLT[ERMINATOR] {;|c|ON|OFF}
  *FLU[SH] {ON|OFF}                        *SUF[FIX] {SQL|text}
   HEA[DING] {ON|OFF}                      *TAB {ON|OFF}
   HEADS[EP] {||c|ON|OFF}                  *TERM[OUT] {ON|OFF}
   INSTANCE [instance_path|LOCAL]          *TI[ME] {OFF|ON}
   LIN[ESIZE] {80|n} ({150|n} iSQL*Plus)    TIMI[NG] {OFF|ON}
   LOBOF[FSET] {1|n}                       *TRIM[OUT] {ON|OFF}
   LOGSOURCE [pathname]                    *TRIMS[POOL] {OFF|ON}
   LONG {80|n}                              UND[ERLINE] {-|c|ON|OFF}
   LONGC[HUNKSIZE] {80|n}                   VER[IFY] {ON|OFF}
   MARK[UP] HTML [OFF|ON]                   WRA[P] {ON|OFF}
     [HEAD text] [BODY text] [TABLE text]   XQUERY {BASEURI text|
     [ENTMAP {ON|OFF}]                        ORDERING{UNORDERED|
     [SPOOL {OFF|ON}]                                  ORDERED|DEFAULT}|
     [PRE[FORMAT] {OFF|ON}]                   NODE{BYVALUE|BYREFERENCE|
   NEWP[AGE] {1|n|NONE}                            DEFAULT}|
   NULL text                                  CONTEXT text}
   NUMF[ORMAT] format


 An asterisk (*) indicates the SET option is not supported in iSQL*Plus.


Please note the line including AUTOTRACE above

When all else fails, you could simply RTFM or use GOOGLE.
Re: takes more time to open lov [message #287615 is a reply to message #287612] Wed, 12 December 2007 23:17 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

http://asktom.oracle.com/tkyte/article1/autotrace.html
Previous Topic: DBLink performance issues
Next Topic: Optimize large SQL query with multiple joins
Goto Forum:
  


Current Time: Wed Nov 13 04:59:34 CST 2024