Home » RDBMS Server » Performance Tuning » Sql query is slow (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production)
 Sql query is slow [message #588550] |
Wed, 26 June 2013 08:04  |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Hi All,
I have below sql query which is taking more than 3mins to execute in Dev.
SELECT hd.cono,
hd.custno,
hd.externalcono,
cr.stocknosu,
cr.tourno
FROM cohead hd,a_coshipto cr,a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND EXISTS (SELECT 'x'
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.cono = cr.cono
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
The Explain plan for above query is :
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 11634 |
| 1 | NESTED LOOPS SEMI | | 1 | 85 | 11634 |
| 2 | HASH JOIN | | 2703 | 176K| 3493 |
| 3 | TABLE ACCESS FULL | A_COFLX | 49602 | 1065K| 1090 |
| 4 | HASH JOIN | | 43144 | 1895K| 2197 |
| 5 | TABLE ACCESS FULL | A_COSHIPTO | 58677 | 916K| 651 |
| 6 | TABLE ACCESS FULL | COHEAD | 42334 | 1198K| 1377 |
| 7 | TABLE ACCESS BY INDEX ROWID| COROW | 10 | 180 | 3 |
| 8 | INDEX RANGE SCAN | COROW_PK | 2 | | 2 |
---------------------------------------------------------------------------
There is an Index on CONO in COHEAD,A_COSHIPTO and A_COFLX tables but it is going for full table scans as specified in above plan.
I tried using some hints to use the Index on A_COFLX which went very costly.
COHEAD table consists 1 row for every Customer Order(CO)
COROW table consists multiple records for every customer order
A_COSHIPTO table consists multiple records which is less than or equal to COROW
A_COFLX table consists multiple records which indicates some flags pertaining to CO.(Arnd 5).
I've attached the TKprof of the same.
Please let me know some ways to improve the above query.
|
|
|
|
|
|
|
|
|
|
| Re: Sql query is slow [message #588624 is a reply to message #588605] |
Thu, 27 June 2013 03:14  |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Dear All,
Here is the DDL of Table and Indexes of all tables in the sql.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OBJECT_OWNER)
--------------------------------------------------------------------------------
CREATE TABLE "EHDA"."A_COFLX"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"COSHIPTOSEQNO" NUMBER(6,0) NOT NULL ENABLE,
"ROWPOS" NUMBER(6,0) NOT NULL ENABLE,
"ROWSUBPOS" NUMBER(2,0) NOT NULL ENABLE,
"ROWSEQ" NUMBER(2,0) NOT NULL ENABLE,
"COREFQUAL" VARCHAR2(30) NOT NULL ENABLE,
"COREFSEQNO" NUMBER(3,0) NOT NULL ENABLE,
"CHARVALUE" VARCHAR2(80),
"DATEVALUE" DATE,
"NUMVALUE" NUMBER(22,7),
"UPDDATE" DATE,
"USERID" VARCHAR2(40),
"LOGINID" VARCHAR2(40)
)
CREATE TABLE "EHDA"."A_COSHIPTO"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"COSHIPTOSEQNO" NUMBER(6,0) NOT NULL ENABLE,
"CUSTNO" VARCHAR2(7),
"CUSTNOLVLID" NUMBER(2,0),
"SHIPNAME1" VARCHAR2(30) NOT NULL ENABLE,
"SHIPNAME2" VARCHAR2(30),
"SHIPSTREET1" VARCHAR2(30),
"SHIPSTREET2" VARCHAR2(30),
"SHIPSTREET3" VARCHAR2(30),
"USSTATECD" VARCHAR2(2),
"SHIPZIPCODE" VARCHAR2(20),
"USZIPCODE" VARCHAR2(20),
"SHIPTEL" VARCHAR2(20),
"COUNTRYCD" VARCHAR2(2) NOT NULL ENABLE,
"BUYCENTRE" NUMBER(6,0),
"REGION" VARCHAR2(6),
"DISTRICT" VARCHAR2(6),
"CUREF" VARCHAR2(30),
"TERMSDEL" NUMBER(3,0) NOT NULL ENABLE,
"TRSPMODE" VARCHAR2(2) NOT NULL ENABLE,
"COMARK" VARCHAR2(30),
"SHIPCOMM" VARCHAR2(50),
"SHIPTONO" NUMBER(6,0),
"ROUTINGCD" VARCHAR2(20),
"INVCOLLMARK" VARCHAR2(5),
"SHIPCITY" VARCHAR2(30),
"FREIGHTCHA" VARCHAR2(1),
"COFREIGHT" NUMBER(19,2),
"PACKCHA" VARCHAR2(1),
"COPACK" NUMBER(19,2),
"FREIGHTLATER" VARCHAR2(1),
"TAXCERTNO" VARCHAR2(25),
"REASTAXCD" VARCHAR2(2),
"OUTSIDECITYLMT" VARCHAR2(1),
"LBLIND" VARCHAR2(1),
"STOCKNOSU" NUMBER(3,0),
"TOURNO" NUMBER(10,0),
"WEEKDAYNO" NUMBER(1,0),
"STOPTIMEDATE" DATE,
"COAMT" NUMBER(19,2),
"COWEIGHT" NUMBER(10,3),
"COVOLUME" NUMBER(10,3),
"COQTY" NUMBER(12,3),
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"INSTNO" NUMBER(8,0),
"FUNCNAME" VARCHAR2(10),
"SOTYPECD" NUMBER(3,0),
"SOSYMPTOMCD" VARCHAR2(12),
"SOSERIOUSCD" NUMBER(1,0),
"PARTNO" VARCHAR2(20),
"PLTRSPSYSTEM" VARCHAR2(3),
"TAXFREEZONE" VARCHAR2(1),
"PLACELOAD" VARCHAR2(15),
"PLTRSPCARRIER" VARCHAR2(20),
"TERMSDELEXT" VARCHAR2(30),
"CZBOOKTIME" DATE,
"CZBOOKREF" VARCHAR2(40)
)
CREATE TABLE "EHDA"."COHEAD"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"CUSTNO" VARCHAR2(7) NOT NULL ENABLE,
"CUSTNOINT" VARCHAR2(7),
"CUSTCAT" VARCHAR2(3),
"COOBJ" NUMBER(3,0),
"SALESMAN" VARCHAR2(5),
"OURREF" VARCHAR2(5) NOT NULL ENABLE,
"COWDATE" DATE,
"PRELDELAH" VARCHAR2(1),
"PRELREMNO" NUMBER(2,0) NOT NULL ENABLE,
"ROWPOSHIGH" NUMBER(6,0),
"STOCKNO" NUMBER(3,0),
"OTYPE" VARCHAR2(2) NOT NULL ENABLE,
"ACCOUNTCD" VARCHAR2(3),
"COSTATUS" NUMBER(3,0) NOT NULL ENABLE,
"INVSEQNO" NUMBER(6,0),
"SHIPSEQNO" NUMBER(3,0),
"INVCOLLMARK" VARCHAR2(5),
"INVNOLAST" NUMBER(9,0),
"INVDATELAST" DATE,
"DUEDATELAST" DATE,
"CODATE" DATE NOT NULL ENABLE,
"YOURDATE" DATE,
"CREDATE" DATE NOT NULL ENABLE,
"SHIPNAME1" VARCHAR2(30),
"SHIPNAME2" VARCHAR2(30),
"SHIPSTREET1" VARCHAR2(30),
"SHIPTONO" NUMBER(6,0),
"SHIPSTREET2" VARCHAR2(30),
"SHIPSTREET3" VARCHAR2(30),
"SHIPCITY" VARCHAR2(30),
"SHIPZIPCODE" VARCHAR2(20),
"USZIPCODE" VARCHAR2(20),
"USSTATECD" VARCHAR2(2),
"SHIPTEL" VARCHAR2(20),
"COUNTRYCD" VARCHAR2(2),
"CUREF" VARCHAR2(30),
"SHIPCOMM" VARCHAR2(50),
"COMPLDEL" VARCHAR2(1),
"TERMSDEL" NUMBER(3,0),
"COMARK" VARCHAR2(30),
"TERMSDEL1" VARCHAR2(3),
"ZONE" NUMBER(3,0),
"TOURNO" NUMBER(10,0),
"PACKCHA" VARCHAR2(1),
"COPACK" NUMBER(19,2),
"TRSPMODE" VARCHAR2(2),
"EXPCHA" VARCHAR2(1),
"COCHARGE" NUMBER(19,2),
"FREIGHTCHA" VARCHAR2(1),
"COFREIGHT" NUMBER(19,2),
"INSCHA" VARCHAR2(1),
"FORWARDER" VARCHAR2(7),
"DISCGRP" NUMBER(1,0),
"CODISC" NUMBER(5,2) NOT NULL ENABLE,
"INVENCL" VARCHAR2(1),
"DELSTOP" VARCHAR2(1),
"CREDSTOP" VARCHAR2(2),
"TERMSPAY" VARCHAR2(2),
"INVCHA" VARCHAR2(1),
"INVCHARGE" NUMBER(19,2),
"CUVATCD" VARCHAR2(2),
"VAT" VARCHAR2(6),
"VATAMTLAST" NUMBER(19,2),
"COAMTQUAL" NUMBER(10,0) NOT NULL ENABLE,
"COAMT" NUMBER(19,2) NOT NULL ENABLE,
"COWEIGHT" NUMBER(10,3) NOT NULL ENABLE,
"COVOLUME" NUMBER(10,3) NOT NULL ENABLE,
"EXPRESS" VARCHAR2(1),
"FREIGHTLATER" VARCHAR2(1),
"TEXTCD" VARCHAR2(1),
"TEXTNO" NUMBER(8,0),
"DELAYBILL" VARCHAR2(1),
"GLSUBNO" NUMBER(8,0),
"GLSUBTYPE" NUMBER(1,0),
"COCONFCD" VARCHAR2(1) NOT NULL ENABLE,
"RETSHIPNO" VARCHAR2(10),
"INVNOREF" NUMBER(9,0),
"SALESREF" VARCHAR2(5),
"RETRATE" NUMBER(3,1),
"RETPRICECD" VARCHAR2(1),
"RETPRICECOMPL" VARCHAR2(1),
"RETAUTHAMOUNT" NUMBER(19,2),
"INVSHIPDATE" DATE,
"COOBJMISS" VARCHAR2(1),
"REGION" VARCHAR2(6),
"CASHCD" VARCHAR2(1),
"UPDDATE" DATE,
"ACCOUNT" NUMBER(6,0),
"CENTRE" NUMBER(6,0),
"PRICELIST" VARCHAR2(7),
"YOURCONO" VARCHAR2(35),
"EXPORTMARK" VARCHAR2(1),
"PRODLINE" VARCHAR2(3),
"CURRENCY" VARCHAR2(3),
"TERMSPRICE" VARCHAR2(1),
"SOPLANDATE" DATE,
"FIXPRICEYN" VARCHAR2(1),
"PROJNO" NUMBER(8,0),
"PROJSUBNO" NUMBER(3,0),
"SAGREEMENTNO" NUMBER(8,0),
"INSTNO" NUMBER(8,0),
"GUARANTEE" VARCHAR2(1),
"WARRYN" VARCHAR2(1),
"BUYCENTRE" NUMBER(6,0),
"DISTRICT" VARCHAR2(6),
"SERVOFFICE" VARCHAR2(3),
"ORGUNIT" VARCHAR2(3),
"DEPTID" VARCHAR2(3),
"EMPGROUP" VARCHAR2(3),
"CUSTNOBILL" VARCHAR2(7),
"SCONO" NUMBER(8,0),
"SOPRINTDATE" DATE,
"SOVISITDATE" DATE,
"SOPLANSTARTDATE" DATE,
"QTYPLANTIME" NUMBER(9,1),
"TEXTCDINT" VARCHAR2(1),
"INTTEXTNO" NUMBER(8,0),
"ROUTINE" VARCHAR2(20),
"CODISCTYPE" VARCHAR2(1),
"CORETDATE" DATE,
"COMPONENTNO" NUMBER(8,0),
"ROUTINGCD" VARCHAR2(20),
"OINUPD" VARCHAR2(1),
"CUSTNOLVLID" NUMBER(2,0),
"UNITERMSPAY" VARCHAR2(1),
"COCREAMT" NUMBER(19,2),
"CUSTDEPT" VARCHAR2(30),
"TRANSFERCD" VARCHAR2(2),
"CODDATE" DATE,
"REMITADDRID" VARCHAR2(7),
"COSALESPGMID" VARCHAR2(8),
"COSOURCE" VARCHAR2(3),
"COYOURCONO" VARCHAR2(20),
"PRCDATE" DATE,
"METHOFREC" VARCHAR2(3),
"COBORULE" VARCHAR2(3),
"COCANCELDATE" DATE,
"UNITSELL" VARCHAR2(3),
"CUAPPDATE" DATE,
"ACTDELDATE" DATE,
"PROMID" VARCHAR2(8),
"DEALID" VARCHAR2(8),
"NDA" VARCHAR2(1),
"COPICKHLDCD" VARCHAR2(1),
"SOSERIOUSCD" NUMBER(1,0),
"SOERRORCD" NUMBER(3,0),
"SOACTIONCD" NUMBER(3,0),
"BCUSTNO" VARCHAR2(7),
"CCUSTNO" VARCHAR2(7),
"CASHDISCAMT" NUMBER(19,2),
"CASHDISCDATE" DATE,
"BANKREF" VARCHAR2(20),
"SUBNO" NUMBER(8,0),
"SUBTYPE" NUMBER(1,0),
"SOTYPECD" NUMBER(3,0),
"SOSYMPTOMCD" VARCHAR2(12),
"COMBINATIONID" NUMBER(9,0),
"STRUCTID" VARCHAR2(10),
"FUNCNAME" VARCHAR2(10),
"PARTNO" VARCHAR2(20),
"SOMAINTPRCLIST" VARCHAR2(7),
"SOUNITID" VARCHAR2(20),
"SOSCHEDULENO" NUMBER(3,0),
"SOSCHELINENO" NUMBER(3,0),
"SOBILLCHK" VARCHAR2(1),
"SOETADATE" DATE,
"SOSTOPDATE" DATE,
"SOCLOSEDATE" DATE,
"SODEFERCD" VARCHAR2(1),
"SODEFERREASCD" VARCHAR2(3),
"SODEFERSTDATE" DATE,
"SODEFERENDDATE" DATE,
"SOASSISTREASCD" VARCHAR2(3),
"SOREJECTREASCD" VARCHAR2(3),
"SOREJECT" NUMBER(3,0),
"SOENGFAIL" NUMBER(3,0),
"SOCUSTREPFLG" VARCHAR2(1),
"SOENGREPFLG" VARCHAR2(1),
"SERIALNUMBER" VARCHAR2(21),
"SOSTOCKSOURCECD" VARCHAR2(10),
"SOSUBCONTRACTNO" NUMBER(8,0),
"BLANKET" VARCHAR2(1),
"RETAPPROVAL" VARCHAR2(1),
"RETCAUSE" VARCHAR2(2),
"ARREFNO" VARCHAR2(14),
"EDIECNO" VARCHAR2(10),
"COQTY" NUMBER(12,3),
"REQDELDATE" DATE,
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"COCLASS" VARCHAR2(2),
"COPRMTID" NUMBER(10,0),
"COOPACCR" VARCHAR2(1),
"SOEVENTCD" VARCHAR2(3),
"SOASSISTENG" VARCHAR2(5),
"REQSHIPDATE" DATE,
"RPDESTSYSTEM" VARCHAR2(3),
"LUMPSUMMODE" VARCHAR2(1),
"LUMPSUMDISC" NUMBER(19,2),
"KILLDATE" DATE,
"WARRPRICELIST" VARCHAR2(7),
"SORESPENG" VARCHAR2(5),
"ORDSIZEALLOWMODE" VARCHAR2(1),
"ORDSIZEALLOWBRACK" VARCHAR2(2),
"COSALESPGMIDLS" VARCHAR2(8),
"COINVSTATUS" NUMBER(3,0),
"COINVTRGTYPE" NUMBER(1,0),
"REQINVDATE" DATE,
"SOPRIMEENG" VARCHAR2(1),
"COPLSYSREFNO" VARCHAR2(35),
"RDTYPE" VARCHAR2(1),
"XREFCONO" NUMBER(8,0),
"ORIGINVNO" NUMBER(9,0),
"WWSHOPID" VARCHAR2(3),
"WWLOGIN" VARCHAR2(31),
"EMAILLONG" VARCHAR2(254),
"EXTERNALCONO" VARCHAR2(35),
"ORDMAPTYPE" VARCHAR2(1),
"SHIPDATE" DATE,
"PORTALSTATUS" NUMBER(2,0),
"COCONFCDSTATUS" VARCHAR2(1)
)
CREATE TABLE "EHDA"."COROW"
( "CONO" NUMBER(8,0) NOT NULL ENABLE,
"ROWPOS" NUMBER(6,0) NOT NULL ENABLE,
"ROWSUBPOS" NUMBER(2,0) NOT NULL ENABLE,
"ROWSEQ" NUMBER(2,0) NOT NULL ENABLE,
"CONOSUB" NUMBER(6,0) NOT NULL ENABLE,
"OTYPE" VARCHAR2(2),
"SHIPDATE" DATE,
"CODATE" DATE,
"CREDATE" DATE NOT NULL ENABLE,
"TEXTCD" VARCHAR2(1),
"TEXTNO" NUMBER(8,0),
"STOCKNOPU" NUMBER(3,0) NOT NULL ENABLE,
"STOCKNOSU" NUMBER(3,0) NOT NULL ENABLE,
"TOURNO" NUMBER(10,0),
"WEEKDAYNO" NUMBER(1,0),
"PICKST" VARCHAR2(2),
"STOCKLOC" VARCHAR2(7),
"PARTNO" VARCHAR2(20) NOT NULL ENABLE,
"PARTNOCOM" VARCHAR2(20),
"PARTDESCR1" VARCHAR2(30),
"PARTDESCR2" VARCHAR2(30),
"CLASS5" VARCHAR2(4),
"CLASS6" VARCHAR2(4),
"UNITSELL" VARCHAR2(3),
"UNIT" VARCHAR2(3),
"UNITRELSS" NUMBER(16,12),
"QTYUNIT" NUMBER(12,3) NOT NULL ENABLE,
"QTYCO" NUMBER(12,3) NOT NULL ENABLE,
"LOTTYPE" VARCHAR2(1),
"QTYRES" NUMBER(12,3) NOT NULL ENABLE,
"QTYBACK" NUMBER(12,3),
"BACKCD" VARCHAR2(2),
"BOMESS" VARCHAR2(1),
"PICKLISTNO" NUMBER(8,0) NOT NULL ENABLE,
"QTYPICK" NUMBER(12,3) NOT NULL ENABLE,
"COMPLDEL" VARCHAR2(1),
"QTYSHIP" NUMBER(12,3) NOT NULL ENABLE,
"COWDATE" DATE NOT NULL ENABLE,
"COADATE" DATE,
"CUSTROWPOS" VARCHAR2(16),
"PRSALES" NUMBER(22,7) NOT NULL ENABLE,
"PRUNIT" NUMBER(3,0),
"PRLCCMAH" NUMBER(22,7),
"POROWAMT" NUMBER(19,2),
"CODISC" NUMBER(5,2) NOT NULL ENABLE,
"COROWDISC1" NUMBER(5,2) NOT NULL ENABLE,
"COROWDISC2" NUMBER(5,2),
"COROWDISC3" NUMBER(5,2),
"PRMANUAL" NUMBER(22,7),
"COROWAMT" NUMBER(19,2),
"PRICERIND" VARCHAR2(1),
"ROWWEIGHT" NUMBER(10,3),
"ROWVOL" NUMBER(10,3),
"VENDNO" VARCHAR2(7),
"PONO" NUMBER(8,0),
"PONOVEND" VARCHAR2(20),
"PRVEND" NUMBER(22,7),
"VENDDISC1" NUMBER(5,2) NOT NULL ENABLE,
"VENDDISC2" NUMBER(5,2),
"ROWSTATUS" NUMBER(3,0) NOT NULL ENABLE,
"DELNOTENO" VARCHAR2(20),
"INVSEQNO" NUMBER(6,0),
"SHIPSEQNO" NUMBER(3,0),
"INVNO" NUMBER(9,0),
"VENDOR_REF" VARCHAR2(20),
"ACCOUNT" NUMBER(6,0),
"CENTRE" NUMBER(6,0),
"QTYDEL" NUMBER(12,3) NOT NULL ENABLE,
"FINALDELCD" VARCHAR2(1),
"POWDATE" DATE,
"CONFIRMED" VARCHAR2(1),
"QTYPORES" NUMBER(12,3) NOT NULL ENABLE,
"QTYCORES" NUMBER(12,3),
"STOCKNOSHIP" NUMBER(3,0),
"POADATE" DATE,
"ASSEMBLECD" VARCHAR2(1),
"STOCKNOASS" NUMBER(3,0),
"AUTHDATE" DATE,
"RESDATE" DATE,
"RETARRIVE" VARCHAR2(1),
"RETCAUSE" VARCHAR2(2),
"RETMEASURE" VARCHAR2(2),
"RETAMOUNT" NUMBER(19,2),
"RETRATE" NUMBER(3,1),
"RETCONO" NUMBER(8,0),
"LENDCONO" NUMBER(8,0),
"QTYDET" NUMBER(12,3),
"SHIPNO" NUMBER(10,0),
"INVDATE" DATE,
"DELDATE" DATE,
"LIMITDEF" NUMBER(1,0),
"DETTYPE" VARCHAR2(1),
"AUTH" VARCHAR2(5),
"MTRLAV" NUMBER(19,2),
"PRAUTH" VARCHAR2(5),
"RETROWPOS" NUMBER(6,0),
"UPDDATE" DATE,
"OINUPD" VARCHAR2(1),
"PRICECD" VARCHAR2(3),
"SHIPMENTNO" NUMBER(6,0),
"EXPRESAUTH" VARCHAR2(5),
"INSTNO" NUMBER(8,0),
"FUNCNAME" VARCHAR2(10),
"LOTNO" VARCHAR2(40),
"SUBLOT" VARCHAR2(40),
"LOTCD" VARCHAR2(1),
"EXPIREDATE" DATE,
"SALESMAN" VARCHAR2(5),
"GUARANTEE" VARCHAR2(1),
"SODEBTYPE" VARCHAR2(1),
"PROJACTNO" NUMBER(4,0),
"PROJSHIPNO" NUMBER(3,0),
"PROJVENDNO" VARCHAR2(7),
"MARKINGTEXT" VARCHAR2(10),
"MARKINGNO" NUMBER(3,0),
"AUTHFINAL" VARCHAR2(1),
"PRODLINE" VARCHAR2(3),
"CORETDATE" DATE,
"INTINVNO" NUMBER(9,0),
"VAT" VARCHAR2(6),
"VATAMT" NUMBER(19,2),
"AUTOCODISC" VARCHAR2(1),
"COMPLROWCD" VARCHAR2(1),
"MARKINGADDR" VARCHAR2(30),
"COSHIPTOSEQNO" NUMBER(6,0),
"PRICELIST" VARCHAR2(7),
"TERMSPAY" VARCHAR2(2),
"CREHLDCD" NUMBER(1,0),
"PRICELVLID" NUMBER(2,0),
"USTAXCODE" VARCHAR2(10),
"IMRSRVTYPE" VARCHAR2(1),
"COPRMTID" NUMBER(10,0),
"PRIODATE" DATE,
"COADATE1" DATE,
"COBODATE" DATE,
"GRSPRICE" NUMBER(22,7),
"PRFRORDNO" NUMBER(22,7),
"RETTOTLNAMT" NUMBER(19,2),
"ORIGINVNO" NUMBER(9,0),
"ORIGCONO" NUMBER(8,0),
"COSALESPGMID" VARCHAR2(8),
"DISCTYPERDISC1" NUMBER(3,0),
"DISCTYPERDISC2" NUMBER(3,0),
"DISCTYPERDISC3" NUMBER(3,0),
"DISCTYPEPRICE" NUMBER(3,0),
"DISCTYPEODISC" NUMBER(3,0),
"EXPRETDATE" DATE,
"RCVRETDATE" DATE,
"EXPRETQTY" NUMBER(12,3),
"RCVRETQTY" NUMBER(12,3),
"COADJCAUSE" VARCHAR2(2),
"COAPPHLDCD" NUMBER(1,0),
"COGMHLDCD" NUMBER(1,0),
"CORETHLDCD" NUMBER(1,0),
"COMANPRHLDCD" NUMBER(1,0),
"COSALESPGMIDDISC1" VARCHAR2(8),
"COSALESPGMIDDISC2" VARCHAR2(8),
"COSALESPGMIDDISC3" VARCHAR2(8),
"COSALESPGMIDCODISC" VARCHAR2(8),
"PRPRICELIST" NUMBER(22,7),
"DISCTYPEPLPRICE" NUMBER(3,0),
"COSALESPGMIDPLPR" VARCHAR2(8),
"COOPACCR" VARCHAR2(1),
"PRELDELAH" VARCHAR2(1),
"PRCDATE" DATE,
"REQDELDATE" DATE,
"COCANCELDATE" DATE,
"CUAPPDATE" DATE,
"ACTDELDATE" DATE,
"COBORULE" VARCHAR2(3),
"VARIND" VARCHAR2(1),
"SUBTIND" VARCHAR2(1),
"REPLIND" VARCHAR2(1),
"ORDEREDPARTNO" VARCHAR2(20),
"CORELDATE" DATE,
"PROMID" VARCHAR2(8),
"DEALID" VARCHAR2(8),
"NDA" VARCHAR2(1),
"IMFCSTLVLTYPE" VARCHAR2(2),
"IMFCSTLVLID" VARCHAR2(10),
"COTRANSHLDCD" NUMBER(1,0),
"SELLPACK" VARCHAR2(5),
"COPICKHLDCD" VARCHAR2(1),
"COTEXTID" VARCHAR2(8),
"TAXFLG" VARCHAR2(1),
"SUBNO" NUMBER(8,0),
"SUBTYPE" NUMBER(1,0),
"SOACTIVITYCD" VARCHAR2(3),
"COMBINATIONID" NUMBER(9,0),
"STRUCTID" VARCHAR2(10),
"SCONO" NUMBER(8,0),
"SOTOOLKIT" VARCHAR2(20),
"SOSCHELINENO" NUMBER(3,0),
"SOINVSTARTDATE" DATE,
"SOINVENDDATE" DATE,
"SOREQSTATUS" NUMBER(2,0),
"SOVISITDATE" DATE,
"SOETADATE" DATE,
"PARTPACK" VARCHAR2(15),
"STOCKNOPICKCOMP" NUMBER(3,0),
"STOPTIMEDATE" DATE,
"SRVLVLUPD" NUMBER(1,0),
"SORETURNCD" NUMBER(3,0),
"PRINVNO" NUMBER(9,0),
"PRCONO" NUMBER(8,0),
"ARREFNO" VARCHAR2(14),
"PROMCLOF" VARCHAR2(1),
"IMSCRCLVLTYPE" VARCHAR2(2),
"IMSCRCLVLID" VARCHAR2(10),
"COANNATTEST" VARCHAR2(1),
"COBRACKETTYPE" VARCHAR2(2),
"COBRACKETTYPEMODE" VARCHAR2(1),
"ACKDELIVDATE" DATE,
"SEQNO" NUMBER(3,0),
"BUUNIT" VARCHAR2(3),
"CHILEV" NUMBER(2,0),
"CHISEL" VARCHAR2(20),
"CUSTRESQTYUSED" NUMBER(12,3),
"CHANGEQTYCD" VARCHAR2(3),
"CHANGETEXTFLG" VARCHAR2(1),
"CHANGETEXTNO" VARCHAR2(8),
"SOSWAPPARTNO" VARCHAR2(20),
"COPRLCW" NUMBER(19,2),
"RETAPPROVAL" VARCHAR2(1),
"REQSHIPDATE" DATE,
"ACKSHIPDATE" DATE,
"ACKSHIPDATEORIGIN" DATE,
"WARRPERIOD" NUMBER(4,1),
"SOACTUALTIME" NUMBER(13,3),
"UNITPRICE" VARCHAR2(3),
"QTYCOUNITPRICE" NUMBER(12,3),
"QTYPICKUNITPRICE" NUMBER(12,3),
"QTYSHIPUNITPRICE" NUMBER(12,3),
"PRICEUNITPRICE" NUMBER(22,7),
"ROWINVSTATUS" NUMBER(3,0),
"REQINVDATE" DATE,
"COINVHLDFLG" NUMBER(1,0),
"ORIGROWSEQ" NUMBER(2,0),
"ORIGROWPOS" NUMBER(6,0),
"SCACCODE" VARCHAR2(12),
"FORWARDER" VARCHAR2(7),
"BILLOFLADING" VARCHAR2(30),
"EDPICKTICKSTATUS" VARCHAR2(1),
"RELTOOWSEQNO" NUMBER(6,0),
"EXPPRICE" NUMBER(17,2),
"EXPUOM" VARCHAR2(3),
"WTCODE" VARCHAR2(2),
"TAXLOC" VARCHAR2(1),
"INTRASTATPROCCD" VARCHAR2(6),
"WTVATPERCENT" NUMBER(4,2),
"STATSHIPFLG" NUMBER(1,0),
"COODATE" DATE,
"RSRVDQUALSUPPLY" VARCHAR2(1),
"EARLYRELWHSE" NUMBER(2,0),
"RETTRANSITCD" NUMBER(1,0),
"COACCEPTACKDATECNT" NUMBER(5,0),
"CZTMPLANSTART" DATE,
"CZTMPLANEND" DATE,
"CZTRANSPORTORDERNO" NUMBER(8,0),
"CZFIRSTLEG" NUMBER(10,0)
)
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "EHDA"."A_COFLX_PK" ON "EHDA"."A_COFLX" ("CONO", "COSHIPTOSEQNO", "ROWPOS", "ROWSUBPOS", "ROWSEQ", "COREFQUAL", "COREFSEQNO")
CREATE UNIQUE INDEX "EHDA"."A_COSHIPTO_PK" ON "EHDA"."A_COSHIPTO" ("CONO", "COSHIPTOSEQNO")
CREATE INDEX "EHDA"."A_COSHIPTO_X1" ON "EHDA"."A_COSHIPTO" ("CUSTNO")
CREATE UNIQUE INDEX "EHDA"."COHEAD_PK" ON "EHDA"."COHEAD" ("CONO")
CREATE INDEX "EHDA"."COHEAD_X1" ON "EHDA"."COHEAD" ("COSTATUS")
CREATE INDEX "EHDA"."COHEAD_X2" ON "EHDA"."COHEAD" ("CUSTNO", "OTYPE")
CREATE INDEX "EHDA"."COHEAD_X3" ON "EHDA"."COHEAD" ("COCONFCD")
CREATE INDEX "EHDA"."COHEAD_X4" ON "EHDA"."COHEAD" ("OTYPE", "CONO", "COSTATUS")
CREATE INDEX "EHDA"."COHEAD_X5" ON "EHDA"."COHEAD" ("CUSTNOBILL", "ORGUNIT", "COCREAMT")
CREATE INDEX "EHDA"."COHEAD_X6" ON "EHDA"."COHEAD" ("YOURCONO")
CREATE INDEX "EHDA"."COHEAD_X7" ON "EHDA"."COHEAD" ("COINVSTATUS")
CREATE INDEX "EHDA"."COROW_CZ1" ON "EHDA"."COROW" ("STOCKNOSU", "TOURNO")
CREATE INDEX "EHDA"."COROW_X2" ON "EHDA"."COROW" ("PARTNO", "STOCKNOPU", "ROWSTATUS", "OTYPE")
CREATE INDEX "EHDA"."COROW_X9" ON "EHDA"."COROW" ("ORIGCONO")
CREATE INDEX "EHDA"."COROW_X1" ON "EHDA"."COROW" ("ROWSTATUS", "ACKSHIPDATE", "STOCKNOPU")
CREATE UNIQUE INDEX "EHDA"."COROW_PK" ON "EHDA"."COROW" ("CONO", "ROWPOS", "ROWSUBPOS", "ROWSEQ")
CREATE INDEX "EHDA"."COROW_X3" ON "EHDA"."COROW" ("PICKLISTNO", "STOCKNOPU")
CREATE INDEX "EHDA"."COROW_X4" ON "EHDA"."COROW" ("PONO")
CREATE INDEX "EHDA"."COROW_X5" ON "EHDA"."COROW" ("RETCONO")
CREATE INDEX "EHDA"."COROW_X6" ON "EHDA"."COROW" ("OTYPE", "ROWSTATUS")
CREATE INDEX "EHDA"."COROW_X7" ON "EHDA"."COROW" ("STOCKNOPICKCOMP", "TOURNO","WEEKDAYNO", "ACKSHIPDATE")
CREATE INDEX "EHDA"."COROW_X8" ON "EHDA"."COROW" ("ROWINVSTATUS")
[b]ALL_IND_COLUMNS[/b]
TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
A_COFLX A_COFLX_PK CONO 1
A_COFLX A_COFLX_PK COSHIPTOSEQNO 2
A_COFLX A_COFLX_PK ROWPOS 3
A_COFLX A_COFLX_PK ROWSUBPOS 4
A_COFLX A_COFLX_PK ROWSEQ 5
A_COFLX A_COFLX_PK COREFQUAL 6
A_COFLX A_COFLX_PK COREFSEQNO 7
A_COSHIPTO A_COSHIPTO_PK CONO 1
A_COSHIPTO A_COSHIPTO_PK COSHIPTOSEQNO 2
A_COSHIPTO A_COSHIPTO_X1 CUSTNO 1
COHEAD COHEAD_PK CONO 1
COHEAD COHEAD_X1 COSTATUS 1
COHEAD COHEAD_X2 CUSTNO 1
COHEAD COHEAD_X2 OTYPE 2
COHEAD COHEAD_X3 COCONFCD 1
COHEAD COHEAD_X4 OTYPE 1
COHEAD COHEAD_X4 CONO 2
COHEAD COHEAD_X4 COSTATUS 3
COHEAD COHEAD_X5 CUSTNOBILL 1
COHEAD COHEAD_X5 ORGUNIT 2
COHEAD COHEAD_X5 COCREAMT 3
COHEAD COHEAD_X6 YOURCONO 1
COHEAD COHEAD_X7 COINVSTATUS 1
COROW COROW_CZ1 STOCKNOSU 1
COROW COROW_CZ1 TOURNO 2
COROW COROW_PK CONO 1
COROW COROW_PK ROWPOS 2
COROW COROW_PK ROWSUBPOS 3
COROW COROW_PK ROWSEQ 4
COROW COROW_X1 ROWSTATUS 1
COROW COROW_X1 ACKSHIPDATE 2
COROW COROW_X1 STOCKNOPU 3
COROW COROW_X2 PARTNO 1
COROW COROW_X2 STOCKNOPU 2
COROW COROW_X2 ROWSTATUS 3
COROW COROW_X2 OTYPE 4
COROW COROW_X3 PICKLISTNO 1
COROW COROW_X3 STOCKNOPU 2
COROW COROW_X4 PONO 1
COROW COROW_X5 RETCONO 1
COROW COROW_X6 OTYPE 1
COROW COROW_X6 ROWSTATUS 2
COROW COROW_X7 STOCKNOPICKCOMP 1
COROW COROW_X7 TOURNO 2
COROW COROW_X7 WEEKDAYNO 3
COROW COROW_X7 ACKSHIPDATE 4
COROW COROW_X8 ROWINVSTATUS 1
COROW COROW_X9 ORIGCONO 1
Also here is the count of records in each table.
select count(*) from cohead; 84007
select count(*) from a_coshipto; 87514
select count(*) from a_coflx 492846;
select count(*) from cohead where otype = 61; 42695
select count(*) from a_coflx where corefqual = 'PRINT_FLAG'; 81149
select count(*) from a_coflx where charvalue IS NULL; 327917
select count(*) from a_coflx where charvalue IS NULL and corefqual = 'PRINT_FLAG'; 48843
Thanks for reverting back..
|
|
|
|
| Re: Sql query is slow [message #588629 is a reply to message #588624] |
Thu, 27 June 2013 03:53 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi
Please additionally post the results for the following queries:
select * from
(select cono, count(*)
from cohead
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coshipto
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coflx
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select cono, count(*)
from a_coflx
where charvalue IS NULL and corefqual = 'PRINT_FLAG'
group by rollup(cono)
order by 2 desc)
where rownum<20;
select * from
(select coshiptoseqno, count(*)
from a_coflx
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
select * from
(select coshiptoseqno, count(*)
from a_coshipto
group by rollup(coshiptoseqno)
order by 2 desc)
where rownum<20;
2. Please also look if the results of number of records for each table is correct in comparison with what you see in DBA_TABLES => NUM_ROWS column for each table.
3.Also check for histograms in DBA_TAB_COLUMNS (column HISTOGRAM) for the columns in the where clause of this table.
Post the results here.
Regards,
Andrey
[Updated on: Thu, 27 June 2013 03:53] Report message to a moderator |
|
|
|
|
|
| Re: Sql query is slow [message #588680 is a reply to message #588646] |
Thu, 27 June 2013 06:49 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi prashanth7582,
This is strange... I would expect it will use the indexes each table has on the column named CONO, as it appears to be highly selective.
1. What happens if you try to force the query to be executed with multiple indexes, like this?
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) */
hd.cono,
hd.custno,
hd.externalcono,
cr.stocknosu,
cr.tourno
FROM cohead hd,a_coshipto cr,a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND EXISTS (SELECT 'x'
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.cono = cr.cono
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
2. What if we re-write the query, some combination like the example:
SELECT hd.cono, hd.custno, hd.externalcono, cr.stocknosu, cr.tourno
FROM cohead hd, a_coshipto cr, a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND cr.cono in (SELECT cr2.cono
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
Regards,
Andrey
|
|
|
|
| Re: Sql query is slow [message #588686 is a reply to message #588680] |
Thu, 27 June 2013 07:09 |
prashanth7582
Messages: 34
Registered: October 2005
Location: Bangalore
|
Member |
|
|
Hi Andrey,
1. I had tried having Hints to inform the optimiser to use indixes but the plan is bad compared to what it is used currently.I think that is the reason why Optimiser isnt using that.
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) */
HD.CONO,
HD.CUSTNO,
HD.EXTERNALCONO,
CR.STOCKNOSU,
CR.TOURNO
FROM cohead HD,a_coshipto CR,a_coflx CF
WHERE HD.CONO = CR.CONO
AND HD.OTYPE = 61
AND EXISTS (SELECT 'x'
FROM corow CR2
WHERE CR2.ROWSTATUS BETWEEN 790 AND 989
AND CR2.CONO = CR.CONO
AND CR2.TOURNO = CR.TOURNO
AND CR.COSHIPTOSEQNO = CR2.COSHIPTOSEQNO)
AND HD.CONO = CF.CONO
AND CR.COSHIPTOSEQNO = CF.COSHIPTOSEQNO
AND CF.COREFQUAL = 'PRINT_FLAG'
AND CF.CHARVALUE IS NULL;
Explain Plan :
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 61191 (4)|
|* 1 | TABLE ACCESS BY INDEX ROWID | A_COFLX | 1 | 22 | | 3 (0)|
| 2 | NESTED LOOPS | | 1 | 85 | | 61191 (4)|
| 3 | NESTED LOOPS | | 1 | 63 | | 61188 (4)|
|* 4 | HASH JOIN SEMI | | 1 | 34 | 1608K| 61186 (4)|
|* 5 | TABLE ACCESS BY INDEX ROWID| A_COSHIPTO | 58677 | 916K| | 28604 (1)|
| 6 | INDEX FULL SCAN | A_COSHIPTO_PK | 87222 | | | 1554 (1)|
|* 7 | TABLE ACCESS FULL | COROW | 901K| 15M| | 31138 (6)|
|* 8 | TABLE ACCESS BY INDEX ROWID | COHEAD | 1 | 29 | | 2 (0)|
|* 9 | INDEX UNIQUE SCAN | COHEAD_PK | 1 | | | 1 (0)|
|* 10 | INDEX RANGE SCAN | A_COFLX_PK | 1 | | | 2 (0)|
----------------------------------------------------------------------------------------------
2. Changing EXISTS to IN clause.
[
SELECT hd.cono, hd.custno, hd.externalcono, cr.stocknosu, cr.tourno
FROM cohead hd, a_coshipto cr, a_coflx cf
WHERE hd.cono = cr.cono
AND hd.otype = 61
AND cr.cono in (SELECT cr2.cono
FROM corow cr2
WHERE cr2.rowstatus BETWEEN 790 AND 989
AND cr2.tourno = cr.tourno
AND cr.coshiptoseqno = cr2.coshiptoseqno)
AND hd.cono = cf.cono
AND cr.coshiptoseqno = cf.coshiptoseqno
AND cf.corefqual = 'PRINT_FLAG'
AND cf.charvalue IS NULL;
Explain Plan :
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | | 11634 (3)|
| 1 | NESTED LOOPS SEMI | | 1 | 85 | | 11634 (3)|
|* 2 | HASH JOIN | | 2703 | 176K| 1648K| 3493 (8)|
|* 3 | TABLE ACCESS FULL | A_COFLX | 49602 | 1065K| | 1090 (8)|
|* 4 | HASH JOIN | | 43144 | 1895K| 1608K| 2197 (8)|
|* 5 | TABLE ACCESS FULL | A_COSHIPTO | 58677 | 916K| | 651 (7)|
|* 6 | TABLE ACCESS FULL | COHEAD | 42334 | 1198K| | 1377 (9)|
|* 7 | TABLE ACCESS BY INDEX ROWID| COROW | 10 | 180 | | 3 (0)|
|* 8 | INDEX RANGE SCAN | COROW_PK | 2 | | | 2 (0)|
----------------------------------------------------------------------------------------
Seems to remain same.
If I remove the exists clause,query is faster and gets executed in 13secs but that is very much reqd..So confused how to improve the above query.
CM: fixed the code tags
[Updated on: Thu, 27 June 2013 07:16] by Moderator Report message to a moderator |
|
|
|
| Re: Sql query is slow [message #588694 is a reply to message #588686] |
Thu, 27 June 2013 07:49 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
Hi prashanth7582 ,
How much time does it take to execute the query(from SQL*Plus issue "set timing on":
SELECT /*+ index(hd COHEAD_PK) index(cr A_COSHIPTO_PK) index (cf A_COFLX_PK) index (CR2 COROW_PK) */
HD.CONO,
HD.CUSTNO,
HD.EXTERNALCONO,
CR.STOCKNOSU,
CR.TOURNO
FROM cohead HD,a_coshipto CR,a_coflx CF
WHERE HD.CONO = CR.CONO
AND HD.OTYPE = 61
AND EXISTS (SELECT 'x'
FROM corow CR2
WHERE CR2.ROWSTATUS BETWEEN 790 AND 989
AND CR2.CONO = CR.CONO
AND CR2.TOURNO = CR.TOURNO
AND CR.COSHIPTOSEQNO = CR2.COSHIPTOSEQNO)
AND HD.CONO = CF.CONO
AND CR.COSHIPTOSEQNO = CF.COSHIPTOSEQNO
AND CF.COREFQUAL = 'PRINT_FLAG'
AND CF.CHARVALUE IS NULL;
2. If you want to see real execution plan and actual cardinality costs e.t.c you need to run a query with /*+ gather_plan_statistics */
Hint and then use dbms_xplan.display_cursor in order to query the plan table, while it is still in the shared pool.
Regards,
Andrey
[Updated on: Thu, 27 June 2013 07:50] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat May 03 08:58:56 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
