UBE / SQL Performance - Oracle

[DBohner-(db)]

Ugh,

I'm trying to find the pixie-dust to resolve all the problems with Mother of Earth... I feel like I am flailing...

9.0, Oracle 11g, Unix
I have this UBE that hangs at the end of the Initialize Section for an hour, then quickly races through the fifty-one records it pulls back...

I am schooled, I know that it is all Database Time - that dang Select Statement is Taking an Hour!

I re-submit the UBE with Logging turned on, after an hour I capture the SQL Statement and I switch gears, opening my SQL tool.

The first time I run the statement, exactly as copied from the log - it takes 45 minutes (fifteen minutes faster than the UBE). Each subsequent submission of the same SQL statement take only one of my life's breaths (I am short winded, can't you tell).

I run the UBE again - and it, again, takes an hour.

What the heck am I missing? I know there is an opportunity for a new index - but, shouldn't the blasted Oracle 'Optimizer' have figured that the EXACT same query keeps hitting the system? Why can I run it in under seconds through a query tool (Aqua) - while RUNUBE (as Oracle claims it) take an HOUR - EVERY TIME.

Where's the Magic Performance Button?

Suggestions (other than an iSeries, please)?

(db)

 

[craig_welton]

Dan,
Rather than bang your head around the question why it doesn't learn to get faster, I would concentrate on getting it faster the first time. That fact that the query takes 45 minutes outside of JDE is the issue. There must be index options.

 

[Scott Beebe]

What table is this?

You have ones of 2 choices. Optimize your select statement so it utilizes as many of the fields in one of the existing indexes. Or create a custom index that better matches the way you need to select the data.

 

[DBohner-(db)]

List,

OK - The I know the issue is 'partly' the Index that needs to be created. But, I need some input understanding the Database Issue (how to resolve).

The UBE (RunBatch, according to Enterprise Manager) ALWAYS takes an hour to do the Data Selection. I run the report with logging and I capture the Select Statement.

Then, I Alt-Tab to my SQL Tool (Aqua Data Studio) and I run the query. The first time I run the query - it runs for forty-five (45) Minutes. On each successive execution of the exact same query - the execution completes in under ten seconds.

That said - why does the report, with the EXACT same Data Selection, ALWAYS Data Select for an Hour? My assumption is that there is a Data Base 'Switch' somewhere that isn't configured correctly and the Submitted Jobs are creating their own data access paths.

Being that there are a bunch of Oracle 11g Systems out there - I can't imagine that UBEs appear to create their own Data Access Path for Every Run.

I have asked the DBA(s) to run the statement through SQL Advisor and Suggest New Indexes....

Thoughts?

(db)

 

[WhippingBoy]

[ QUOTE ]
I have asked the DBA(s) to run the statement through SQL Advisor and Suggest New Indexes....

Thoughts?

[/ QUOTE ]

Yeah, go wash your hands.

Let the DBA take responsibility now. If an index is missing you can add it, but beyond that the database is their baby. Learn what the solution is, but you don't have to be personally responsible at this point.

That's my take anyway. I'll add; after 20 years consulting, I've never seen a query take 45 minutes to read a 50 record table. Maybe one of their squirrels died?

 

[Remo Williams]

Squirrels? I don't get it.

 

[DBohner-(db)]

JDE Support confirms the issue with the Select always taking an hour to run for UBE(s), while the query completes in seconds from a query tool.

The query is actually grabbing 51 records from an F0911 that is only 6,399,378 in size (relatively small).

We are seeing similar behavior across the system - I'm not washing my hands yet (performance is on my plate, ugh)... I'm still trying to organize the Database Side that something is amiss - I just don't have the training to suggest which bit they need to hammer back into the wrong peg.

(db)

 

[WhippingBoy]

My apologies. I said 'squirrel' when, to be technically accurate, I should have said 'hamster'.

Instead of writing a long technical dissertation, perhaps this simplified diagram will suffice:

Database Performance Engine Model and Diagram

Of course, the more rodentia powering the database, the better your performance.

According to the diagram, performance is nearly nonexistent early in the day, and can be sporadic thereafter.

 

[Remo Williams]

Okay, I took a look at the diagram.. But I just can't seem to come up with a decent reply right now

 

[Jaise James]

Is this a custom UBE or Standard? If it's a custom ube, you can change the logic to use primary keys and hopefully it will run faster. I am not sure about Oracel DB, but on SQL you can always update statistics. DO run the query with a query plan to see why is it taking that long at DB level

 

[DBohner-(db)]

Nick,

R51425B - Transaction Analysis (Standard Report, no modification).

AS for the Statistics part, I've asked (not everyone listens to Developers, though).

Unlike other clients, I don't have the opportunity to monitor the Enterprise Manager. I'm at the mercy of the orangutans (and hamsters)

(db)

 

[Larry_Jones]

Daniel,

took a peek at this UBE, the BSVW, and the table. The sort sequence exactly matches one of the table (F0911) indices which appears in the database as index F0911_25. Could be a database (stats) issue, or not.

As far as why the report "always takes an hour" vs the query tool only taking 45 minutes the first time, the below may shed some light on this
----------------------------------------
7.6 Using the Client Query Result Cache
To improve the response time of repetitive queries, Oracle Call Interface (OCI) applications can utilize client memory to take advantage of the OCI result cache. A result cache stores the results of queries shared across all sessions. When these queries are executed repeatedly, the results are retrieved directly from the cache memory, resulting in faster query response time. The query results stored in the cache become invalid when data in the database objects being accessed by the query is modified. The client-side result cache is a separate feature from the server-side result cache. Unlike the server result cache, the OCI result cache does not cache results in the server SGA

 

[DBohner-(db)]

Larry,

The Cache thing is very close to what I'm fighting.

When I re-run the SQL Statement on the Query Tool, I can change the data selection (change dates, Ledger Type or...) - and the query still completes in less than ten seconds.

However, if I run the Exact Same UBE with the same Data Selection - it's always an hour. I'm sure it's an access path / setting-something on the database - just can't find the magic button.

What document was your quote from? It might have some more tidbits for me to interfere with...

As always - Thank you

(db)

 

[Larry_Jones]

Daniel,

the document is the Oracle 11G Database Performance Tuning Guide - just google and you'll find it.

How often does this UBE run? Unless its running frequently (minutes) what you're seeing is that the query results have aged out of the server cache. Whereas the query tool you're running is probably using a client-side cache.

A better explanation can be found in this document "Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC Environment". Ignore that it says RAC and look at the section on Result Cache. This may be what you're running into.

It's still a DBA issue though ...

 

[ice_cube210]

Daniel,

I have seen similar issues with E1 running against an Oracle DB. We had queries that when running from the UBE would take an hour. Run it from toad would return in under a minute (few seconds for the initial fetch , and a few minutes to go all the way to the end).

Usually when we ran the query from an SQL plus session it would mimic the same behavior as the UBE though there were cases where SQL Plus also returned right way , but the UBE would still take an hour (log timing from the debug log of UBE).

Do you see the same explain plan being used if you monitor the SID on the DB side when the UBE is running , versus the session from your query tool...

Do you know what the CURSOR_SHARING parameter is set to ? Values can be EXACT , SIMILAR or FORCE (this was in 10g , not sure if they have changed in 11g)

I know you said you have limited access to the database to monitor these things but that's where I would start

Good Luck

 

[DBohner-(db)]

Hi Ice,

I did get someone to retrieve the Cursor Sharing value - it is EXACT. Per the Oracle Rep - that's what it is supposed to be for E1? Based on what I read - I would have expected it to be 'Similar' ... it's a good thing I'm not the dba?? =D

Thanks for any assists you can provide!

(db)

 

[ice_cube210]

The EXACT might explain why the UBE always takes the same amount of time when run from E1 , even if you run the same data selection.

My explanation below is in simple terms as I have understood it and I may not be using the right technical terms , because I too am not a DBA

E1 tends uses bind values in the query , so two queries will never be similar or exact even, so no match is found in the shared pool for that statement as it does not know what the bind values are so it will not reuse statement path in the shared pool that was just used for the same query because the setting is set to EXACT.

SIMILAR on the other hand should still reuse the statement plans and data access paths if all the columns in the WHERE clause are the same but the literal values were different

The Oracle Rep's statement about E1 requiring it to be EXACT might be correct , I need to look up my notes and possibly the spfile I have saved from a few years ago.

If I remember correctly EXACT worked better for some proccess and SIMILAR worked better for some processes. We finally had to do a trade off and go with what worked better for the more critical processes. I will try look up and see if I can find what we we finally went with

If you have a Dev DB you can test these settings , you can try change to SIMILAR (FORCE generally tends to have negative results) and see what results you get.

I'd also try and see if there is any difference in the explain plan when the UBE runs vs when the query is run from your query tool..

Check out doc id - 748368.1 on the support site. It's a 10g performance paper for E1. Their tests seem to show that on a overall average SIMILAR had the best results (Page 12 of the document)

 

[DBohner-(db)]

I'm just the guy that is assigned to resolve the issue, I'm not the guy with the tools to look at it or change anything that might fix it... =D

Now that I got that out of the way - thanks for the suggestion, Ice!

More Dirt to plant into the equation...

Possibly a BIND Variable Issue? When we execute the statement in a query tool - it takes seconds. When the UBE executes the statement, it takes an hour. Enterprise Manager shows the query 'as submitted' is all those :key# Bind Variables...

Thus, it appears the actual statement submitted by the UBE is 'Bound' - but what is represented in the JDEDebug.log is the representation of what was bound (or is that Binded??)

Debug Log:
SELECT ... CRPDTA.F0911
WHERE ( ( GLMCU = ' 217617' AND GLSUB IN ( '120104','120206' ) AND GLOBJ IN ( '51336','51112' ) ) ...
ORDER BY GLCO ASC,GLMCU ASC,GLSUB ASC,GLOBJ ASC,GLDGJ ASC,GLLT ASC,GLSBL ASC,GLSBLT ASC,GLPOST ASC,GLDCT ASC,GLAN8 ASC

Enterprise Manager Sees it at:
SELECT ... CRPDTA.F0911
WHERE ( ( GLMCU = :KEY1 AND GLSUB IN ( :KEY2, :KEY3 ) AND GLOBJ IN ( :KEY4 ,:KEY5 ) )...
ORDER BY GLCO ASC,GLMCU ASC,GLSUB ASC,GLOBJ ASC,GLDGJ ASC,GLLT ASC,GLSBL ASC,GLSBLT ASC,GLPOST ASC,GLDCT ASC,GLAN8 ASC

So - now to figure out what's next...

(db)

 

[gigi]

Daniel try to delete statistics over that table and its indexes.
Does it change behaviour?
gg

 

[Larry_Jones]

Daniel,

you said
"When we execute the statement in a query tool - it takes seconds."

earlier you said that the 1st time from the query tool it takes 45 minutes. Which is it?

I know you don't have control over this but one way out of this issue is to not use JDE's "reporting". 10+ years ago we had a similar issue trying to use JDE's Income statement or balance sheet (don't remember exactly) report. It ran . . . but it took 2 hours and really thrashed the system while doing so. A financial analyst rewrote the report in Crystal and it ran in 45 seconds . . . just saying.