Oracle slow with OR statement inside OW only

[ccornagg]

Dear all,

I'm currently working with Xe SP23 Oracle 9, and I hope someone of you is able to explain me why I'm experiencing an extremely slowness for the following where statement over F0911:

WHERE ( ( GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'SA' OR GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'AA' ) )

In other words:
* I cannot understand why it takes 38 seconds (jdedebug.log) to extract 0 records from OW (standard R70470 UBE) while it takes 0 seconds using SQL*Plus

* I cannot understand why it takes 0 seconds to extract 0 records from OW if I'm going to select one ledger type at a time ('AA' or 'SA' ledger type in my example) thus without using 'OR' statement.

I'll really appreciate any hints from you.

Carlo

 

[Alex_Pastuhov]

Carlo,

Check out this post:

http://www.jdelist.com/ubb/showflat.php?Cat=&Board="OW"&Number="96909"

 

[gigi]

Carlo,
questa è la clausola where costruita da OneWorld?

WHERE ( ( GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'SA'
OR
GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'AA' ) )

Se non ho capito male dal tuo post tu vuoi estrarre i record della company 00400 compresi tra due date e con LT=AA oppure LT=SA? Corretto?
Se fosse così ed inolte la clausola di where fosse quella che hai riportato(se queste supposizioni non sono corrette quello che ti dico qui non ha molto senso), la query sql di OneWorld non effettuerebbe la ricerca che vuoi tu. Dovrebbe essere così la query generata da OW (con due parentesi prima e dopo la OR)

WHERE ( ( GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'SA' )
OR
(GLCO = '00400' AND GLAID = '13509576' AND GLDGJ > 105001 AND GLDGJ <= 105036 AND GLLT = 'AA' ) )

Dal punto di vista SQL se non viene "parentesizzato" correttamente l'OR ha precedenza sulla clausola AND per cui la query viene interpretata in modo diverso.
Spero di averti aiutato
Gigi

 

[Zoltan_Gyimesi]

Hi Carlo,

As I see, the "Write Work File F0911 Transaction Records" or the "Accumulate Debits and Credits for BFW" section produce this WHERE clause in the Initialize Section event of it.

It has several problems on our version:
1.) SetSelectionAppendFlag call is missing
2.) Filtering for Company is redundant, when AccountID is filtered
3.) The <None> relation in the second GL Date selection is absolutelly wrong
4.) Maybe the sequence of the selection statement is not the best (DGJ >, DGJ <= precede the LT =)
5.) etc.

I suppose, we can modify this part of ER the way to be an index on F0911 (LD AID,LT,DGJ,CRCD) which pretty fits to the modified WHERE clause.

Maybe, the ER can be different on your version, so please, try to identify the section, which produce this WHERE clause and print the Initialize Section event rules into a text file and post it here, as an attachment.

I am almost sure, we can do something with it.

Read you later,

Zoltán

 

[ccornagg]

Zoltan,

thank you very much for your answer, here you can find the piece of code we are talking about:

---------------------------------------------------
Section : Write Work File F0911 Transaction Records
Event Rule : INIT_SECTION

Set User Selection(BC Company (F0911), <Equal To>, RV GlblCo, <And>)
Set User Selection(BC Account ID (F0911), <Equal To>, VA rpt_AccountId, <And>)
// Ledger type 1 . . . .
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Greater Than>, RV BalanceFwrdDate, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Less Than or Equal To>, VA rpt_jdThruDate, <And>)
Set User Selection(BC Ledger Type (F0911), <Equal To>, PO szLedgertype1P02, <And>)
If DS szLedgerType2 [T70470,LT] != <Blank>
// Or Ledger type 2 . . . . .
Set User Selection(BC Company (F0911), <Equal To>, RV GlblCo, <Or>)
Set User Selection(BC Account ID (F0911), <Equal To>, VA rpt_AccountId, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian , <Greater Than>, RV BalanceFwrdDate, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Less Than or Equal To>, VA rpt_jdThruDate, <And>)
Set User Selection(BC Ledger Type (F0911), <Equal To>, PO szLedgerType2, <And>)
End If
--------------------------------------------

I can understand that this code can be improved in order to try using existing index or so on ... but this DO NOT explain me why the same SQL needs 38 seconds within OW and 0 second with SQL*Plus.

Alex Pastuhov suggested me to read a different thread (too technical for me!) that sounds like: the REAL statement executed by RDBMS can be different according to the calling program ... and this makes me worried and worried ;-)

Thanks again, ciao.

Carlo

 

[Zoltan_Gyimesi]

Hi Carlo,

...and here you can find the piece of modified code

I attached the .txt file to this post too, because the indented text can be read easier.

Please, try it and let us know the results. Thanks in advance.

Take care of the <None>, <And> and <Or> parameters in each selection line.

----------------------------------------------------------------------
// *** Selections had been modified - by Z.Gyimesi, 2005.Sept.22
Set Selection Append Flag( <No>)
Set User Selection(BC Account ID (F0911), <Equal To>, VA rpt_AccountId, <None>)
// Ledger type 1 . . . .
Set User Selection(BC Ledger Type (F0911), <Equal To>, PO szLedgertype1P02, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian , <Greater Than>, RV BalanceFwrdDate, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Less Than or Equal To>, VA rpt_jdThruDate, <And>)
If PO szLedgerType2 is not equal to <Blank>
// Or Ledger type 2 . . . . .
Set User Selection(BC Account ID (F0911), <Equal To>, VA rpt_AccountId, <Or>)
Set User Selection(BC Ledger Type (F0911), <Equal To>, PO szLedgerType2, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian , <Greater Than>, RV BalanceFwrdDate, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Less Than or Equal To>, VA rpt_jdThruDate, <And>)
End If
If PO szLedgerType3 is not equal to <Blank>
// Or Ledger Type 3 . . . . . .
Set User Selection(BC Account ID (F0911), <Equal To>, VA rpt_AccountId, <Or>)
Set User Selection(BC Ledger Type (F0911), <Equal To>, PO szLedgerType3, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian , <Greater Than>, RV BalanceFwrdDate, <And>)
Set User Selection(BC Date - For G/L (and Voucher) - Julian (F0911), <Less Than or Equal To>, VA rpt_jdThruDate, <And>)
End If
// ********************************************************************

Regards & Good Luck,

Zoltán

 

[Kiran PC]

Hi GIGI,

I would appreciate is you can translate the same in English and just to let you know that this is international forum and most of them speak English

 

[ccornagg]

Zoltan,

thank you so much for your precious help: it works !

Here you are the new WHERE statement (around 1 second in my debuglog instead of 38 seconds):

-----------------------------------------------------------------
WHERE ( ( GLAID = '13509576' AND GLLT = 'AA' AND GLDGJ > 105001 AND GLDGJ <= 105036 OR GLAID = '13509576' AND GLLT = 'SA' AND GLDGJ > 105001 AND GLDGJ <= 105036 ) ) ORDER BY GLDGJ DESC
-----------------------------------------------------------------

If I understood correctly your change is going to force OW for standard index 18 usage (F0911).

I still cannot understand why I have to do that whether SQL*Plus is "so clever" to manage the previous statement too ... using for sure a index (0 second for a 26 million records' table ...).

Thanks again, CIAO.

Carlo

 

[Alex_Pastuhov]

Carlo,

In short, when you run in in SQL*Plus, you provide the values, like this:
... GLAID = '13509576' AND GLLT = 'AA' ...

but OW submits a query like this:
... GLAID = :KEY1 AND GLLT = :KEY2 ...

with the actual values submitted afterwards.

The plan is re-evaluated for each submission and may not always be correct, i.e.: the optimiser may not have enough information or can make inaccurate assumptions.

This can be fixed - there are settings affecting the entire DB behaviour, but mostly, this would have to be done on a case-by-case basis. Tuning is usually a laborious process, taking weeks at best - because there will be dozens of such issues in any DB, but you should consider doing it anyway - there are clear advantages.

Re-arranging the WHERE clause would certainly change the execution plan - for the better or for the worse is hard to tell in advance without studying each case. Guessing is obviously chancy...

 

[Zoltan_Gyimesi]

Hi Carlo,

Thank you to let us know the result.
I am glad to read, that your query (and your UBE) got a bit faster.

Analyzing the original query and the indicies on F0911, further know the logic between CO and AID, I suppose:
- the redundant CO filtering
- the illogical sequence of Select statements (the redundant CO is the first, the range on DGJ precede the EQ on LT)
confused the optimizer, and it was not able to make the best decision.

The target of changes were to help the optimizer to make a better decision - finding the best indicies - while the result of the query does not change.

I don't know why is SQL*Plus "so clever" - I am just a developer

Who knows, what happens in the OW middleware? Maybe Alex

Sorry to say, I have no more idea, how to make this query in your UBE more faster.

Regards,

Zoltán