"hints" on SQL statements - Oracle database

[Aarto]

Hi guys! This is not really my area of expertise but i am helping out in investigating performance issues.
The customer is testing E1 9.1 tools 9.2 on Oracle cloud. They have migrated from AS/400 / I-series and db2 database to Oracle Enterprise edition 12c database


Some of the batch jobs are taking dramatically longer to complete on Oracle vs. AS/400. Mostly custom jobs that handle very large number of records and do a lot of table I/O such as looking up UDC codes in F0005 for each record etc, and then write records to custom tables or write to a flatfile
The job execution times are like 2-3 times longer than on AS/400 and, in some cases, 5-6 times longer for the affected UBES
In general (with some exepctions) the UBEs (standard and custom) run 2-3 times slower


Some jobs actually run faster on Oracle cloud (not many though)
- Enterprise server is not on same physical server as the database.
- System seems to be "idling" (CPU, memory, network traffic, database metrics) so the constraint is not in processing power
- Number of database cursors is set to 1.000 or higher and is not a bottleneck
- Interactive performance (JAS server, applications) is Excellent, it is only the UBEs where we see performance issues
- P98613 database caching table is populated in same way Old vs. new environment (AS/400 vs. Oracle cloud)

To me, it seems that the difference is due to AS/400 having SQL packages so the tables/results like UDC table records being accessed heavily get "cached" in DB/2 but not on Oracle cloud. Looking at the logs, i can see that the jobs on AS/400 take same time to complete as on Oracle cloud if run for a small number of records and the time to fetch the first F0005 record is same as on oracle cloud for the first few records but much faster for each subsequent records. While the time to get results from F0005 is always the same on Oracle cloud (Just an example, other table lookups demonstrate similar behaviour)

I have been reading about "hints" that can be assigned to SQL statements on Oracle database like "RESULT_CACHE". Is there anything in that could be configured in this area in E1 so these hints would be added to the SQL statement?

Also, the logging/journalling is turned ON for the database but many of these jobs use a worktable that is always cleared when the job is started. Apparently, the logging can be turned off for selected tables but the SQL statement also must have the "hint" to not write to the re-do log. So, again, is there a way to get this "hint" to be added or anything else that could be done to turn off logging for selected tables in the business data


Any tips appreciated
Aarto

 

[Tom_Davidson]

Aarto,

The SQL package on the IBM i actually only stores the access plan, Oracle has a similar plan cache that in many cases still has the plan resident, it is very similar to the IBM i SQL Plan Cache.

There are no end of reasons why some queries are faster and some slower, many have to do with SQL optimization, others have to do with caching.

Now to your question, I know of no way to do hints in JDE, but I don't normally use an Oracle DB in JDE.

Tom

 

[Aarto]

Thanks Tom! We are reviewing/adding indexes where required but was hoping that there would be a "Magic parameter" somewhere to do with this. Some UBEs run 10x faster on Oracle and some (mainly custom ones) run 5x slower so it's really a mixed bag. Ah well, grunt work it is then

 

[Larry_Jones]

Aarto,

Is the customer live or still in a test / crp environment?

I ask because Oracle tends to undersize test environments on the cloud in my limited experience working with other (non JDE) Oracle cloud apps.

You can't add hints to the SQL - its created dynamically on the fly by JDE internals - that's a no go.

Are you SAAS or PAAS or ...? can't keep up with the acronyms

I would think Oracle should be responsible for performance and tuning issues - have you raised the issue with them?
<Editorial Opinion>
Lastly some unasked for opinion. This sort of thing is why I am dead set against the cloud for critical business applications that the company needs 100% availability every business day. On-premise we are able to ensure up-time and if there is a failure we're in control and its "all hands on deck" to get it corrected asap. With cloud based solutions you're not just at the mercy of your provider being competent and having adequate resources but also you may have multiple failure points for every piece in-between you and them (a recent snafu involving a routing table mis-configuration comes to mind). It seems that every other month the press is trumpeting about some outage or other if we haven't experienced it ourselves. But I'm a old fart who has difficulty seeing the Emperor's new suit </Editorial Opinion>

 

[craig_welton]

<Editorial Opinion>
Lastly some unasked for opinion. This sort of thing is why I am dead set against the cloud for critical business applications that the company needs 100% availability every business day. On-premise we are able to ensure up-time and if there is a failure we're in control and its "all hands on deck" to get it corrected asap. With cloud based solutions you're not just at the mercy of your provider being competent and having adequate resources but also you may have multiple failure points for every piece in-between you and them (a recent snafu involving a routing table mis-configuration comes to mind). It seems that every other month the press is trumpeting about some outage or other if we haven't experienced it ourselves. But I'm a old fart who has difficulty seeing the Emperor's new suit </Editorial Opinion>

Larry,

Amen

 

[Larry_Jones]

a few months back I was attending a conference (non-JDE) and in a round table on Cloud hosted applications.
One guy shared how his company had the unfortunate experience of having their "servers" in the same neighborhood as some Netflix servers - with the expected congestion as a result.
Moral of the story is that in the cloud your neighbors can "reduce the water pressure in your shower" if you get my comparison.

 

[ice_cube210]

Have you noticed a trend in performance of Read Only Batch Jobs vs Update Batch Jobs (R09801 , R42800 etc..).Any difference in performance when it is only one job running vs multiple ? If it is your update jobs that are taking longer then you are on the right track of looking at the logging on the tables. But you obviously can't afford to turn off logging for some of your major transnational tables. Have you looked at your storage I/O performance to see if that is where the bottle neck is ?

Have a look at this white paper and see if it helps.Though it is not exactly the same situation as the one your are facing, it talks about increasing the number of dbwriter processes to increase the throughput performance when running multiple update batch jobs, among other recommendations.

http://www.oracle.com/webfolder/tec...e Papers/BatchScalabilityTuningWhitePaper.pdf

 

[Richardto]

[FONT="&amp]In every new database environment, database SQL optimizeris responsible for getting a maximum speed of your SQL. But, it is verydifficult for database SQL optimizer to generate the best execution plan foryour SQL every time. you can try this [/FONT]
[FONT="&amp]https://tosska.com/tosska-sql-tuning-expert-tse-oracle/[/FONT]
[FONT="&amp]This software can help you to get the bestexecution plan out from all possible plans that Oracle SQL optimizer cangenerate. Any configuration change including indexing should be delayed untilyou are sure that the best query plan is already used by Oracle.[/FONT]

 

[Aarto]

Thanks for all the tips and insights.. The issues seem mostly be solvable through indexes and there's some real bad coding in places as well.. DB2 seems to be more "forgiving" than Oracle database so proper analysis is required. The affected UBEs are mostly custom written ones, standard UBEs tend to run quicker on the Oracle platform

With kind regards
Aarto