How To Locate Badly tuned SQL In JDE

Hi folks

We are running JDE812 with Tools 898 and an Oracle 10g database. I'm picking up a lot of bad SQL on the system and want to trace it back to the originating Business Process/Screen etc. From Oracle I can obtain the process ID on the E1 server and trace it back to the Kernel but I want to be able to track it back further from there (preferably without using log files). Any help would be appreciated

Many thanks

Doug

I am not sure Oracle have the sql profiler to trace but required efforts.
AD

Call object Kernel Debug Log will show User ID / application / business Function, so with some efforts developers should be able to trace it back to the specific line of code.
Keep in mind though, that JDE code doesn't have SQL statements in explicit way. It has NER code that can be (ususally relatively transparently ) translated into the database SQL statements

You can turn on\off debug logging for the specific Kernel(s) on the fly, so no need to retsart the whole system.

PS Another thought ... you can create 'named' proxy database users for some E1 users if you have a small group of 'suspects'
That will at least trace it back to the specific E1 user. From there you can dubug session of the specific user instead of perofrming blanket debugging of the entire enterprise server

If you are tracking the issue real time , which I presume you are since you are able to get the process and machine from v$session , check the kernel process in Server Manager , it should show you the JDE Users connected to that kernel and the thread detail should show you what applications they have open.

This might help you look in the right direction to find the source of the bad SQL.

Good Luck

It is very difficult to trace the actual bad sql.

Really, it's not so hard to identify a badly defined SQL Statement (or to get a proper index created for one that isn't badly defined).

Each database has its own tools for database analysis. iSeries has the Index Advisor, Oracle has an analyzer and I'm sure that SQL has something similar.

With the iSeries, you can actually sort the SQL statements by their greatest impact on the system (and how often they get run). Looking at the Advisor ~ you create indexes and perform other database duties. What you can't identify is 'what submitted' the statement.

Seek your local DBA for help

(db)

"What you can't identify is 'what submitted' the statement"

I think thats the whole point of the original post Dave.

Hi guys,
Its been a while! Related to this I have a follow up question!

A client of mine has found that an index with included columns (created through SQL tuning advisor) has caused a problem with data for a customer report through a JDE view (F0006->F03b11 if your interested).

It turns out that the included columns had to have some extra fields in for the data returned to be correct.

Has anyone seen this before or offer any advice about why this is happening?
Client is on XE/SQL2005

Cheers

Peter

Hi I have seen this issue where a custom index actually has broken an e1 application. I had to spend months to figure out the issue and Had involved both Oracle and MS( because we were on SQL server). Since then I have really avoided adding custom indexes to standard tables. If we really need to add anything we try to test all the applications that user that table. Most of the custom indexes that we require is for reporting purposes. Hence, For that I replicate my data to a separate database and reports point to that that database. On this replicated database, we can add any index we want

Hi Jaise,
Thanks for the reply, can you remember what the underlying problem was ?

Cheers

Peter

Peter,

if I can recall now, what happened was that due to the custom index, the result that we were getting were incorrect. Hence the application started to work incorrectly. Once we removed the index, the results were correct and its started to work correctly

Jaise,

Are you on an iSeries?

I can only fathom a couple cases where an Index would cause bad data to be returned
- Sequence is unexpected (expecting Ascending and getting Descending)
- Corrupt SQL Packages on iSeries
- Some sort of Index Corruption (rebuilding index should resolve??)

Interesting, the very least!

(db)

The only other thing I can think of is if the secondary index was also defined as unique and thus was causing key violations.

Daniel

No we are on SQL/wintel platform. It was those sequencing issues, now that I recall