UBE SQL Query taking too long

HediHK666

Member
Hi everyone,

I have a problem with an UBE (specific) that is taking way too long to start processing data. Looking at the logs, it seems like the database is taking 90mn to return a result set:

Code:
Jan 25 10:05:59.870520	jdb_rqst.c594	 - 	Entering JDB_SetSelectionX (hRequest 09da58b8) 
Jan 25 10:05:59.870531	jdb_rqst.c640	 - 	Exiting JDB_SetSelectionX With Success 
Jan 25 10:05:59.870540	jdb_exef.c2545	 - 	Entering JDB_SelectKeyed (hRequest 09da58b8) 
Jan 25 10:05:59.870575	dbinitrq.c482	 - 	ORACLE DBInitReq conn=096672f8 requ=0a06cf40 exa-scan (jdee91) new [ 35] 
Jan 25 10:05:59.870883	dbsql_s.c1984	 - 	SELECT T0.GLKCO, T0.GLDCT, T0.GLDOC, T0.GLDGJ, T0.GLJELN, T0.GLEXTL, T0.GLPOST, T0.GLICU, T0.GLICUT, T0.GLCO, T0.GLANI, T0.GLAID, T0.GLMCU, T0.GLOBJ, T0.GLSUB, T0.GLSBL, T0.GLSBLT, T0.GLLT, T0.GLCRCD, T0.GLAA, T0.GLGLC, T0.GLEXA, T0.GLEXR, T0.GLR1, T0.GLR2, T0.GLRCND, T0.GLALT1, T0.GLPYID, T0.GLABR1, T0.GLABR2, T0.GLBCRC, T0.GLCRRM, T0.GLACR, T1.GMAID, T1.GMCRCD, T1.GMR020 FROM PRODDTA.F0911 T0,PRODDTA.F0901 T1 WHERE  (  ( T0.GLPOST = 'P' AND T1.GMR020 = 'B' AND T0.GLRCND = ' ' AND T0.GLCO IN  ( '00001','00002','00004','00005','00006','00007','00009','00010','00013','00016','00015','00019' )  AND T0.GLDCT NOT IN  ( 'MO','U3','U4' )  )  AND  ( T0.GLRCND <> 'R' AND T0.GLLT = 'AA' )  )  AND  ( T0.GLAID = T1.GMAID )  ORDER BY T0.GLCO ASC,T0.GLDCT ASC,T0.GLDOC ASC 
Jan 25 11:40:12.330013	jdb_exef.c2553	 - 	Exiting JDB_SelectKeyed with Success 
Jan 25 11:40:12.330243	jdb_exet.c6597	 - 	Entering JDB_InsertTable (Table F986114A) (hRequest 09ce13c8) 
Jan 25 11:40:12.330309	dbresrq.c123	 - 	ORACLE DBRsetReq conn=096672f8 requ=09f0d0e8 exa-scan.thomeurope.com (jdee91) 
Jan 25 11:40:12.339580	dbsql_i.c175	 - 	INSERT INTO SVM910.F986114A (JDEXEHOST, JDJOBNBR, JDAUDTYP, JDAUDSEQ, JDAUDINFO) VALUES ('jdeente91',446874.000000,'05',0.000000,VARCHAR>250)

Do you have any idea what might cause this? What is weird is that executing that same query diretly using Sql Developer is instant.

Running E1 9.1 on Oracle Exa Data (not sure what db version).

I hope this is in the right forum section :)

Thanks guys!
 
Hi HediHK666,

Running on SQL Developer and the actual generation of UBE is different.
SQL Developer will only produce the first 50 records (depending on the setup) while UBE will generate the full set of data.
Thus, the comparison cannot be made this way.

The issue is clearly on the primary / foreign keys that exists in F0911 and F0901.
The join that was made is on the field AID only.

In F0901, AID is a primary key.
However, in F0911, AID is NOT a primary key.

Thus, the execution will definitely take some time in UBE.

You /DBA may want to add a foreign key to F0901 for CO, AID, MCU, OBJ, SUB.
Subsequently the same to F0911 for KCO, AID, MCU, OBJ, SUB.
Join this together in the Business View for this UBE.

It will thus run faster and complete in no time at all.

Regards,
Adrian
www.drenconsulting.com
 
Last edited:
Back
Top