Heavy SQL's stalling Oracle 10g

jorgas

Member
Heavy SQL\'s stalling Oracle 10g

Hi! I have some questions regarding our installation of E1 with an Oracle 10g database running on a windows platform.

We have one system user in the system, i.e. all requests to our database from the web, or from windows clients, are made with the same oracle database user. It seems to me that when really heavy SQL's are posted, such ones that takes a fair amount of time to complete, the entire database's processing power is being used up for the duration of the request. This gives the behaviour that other users cannot really do anything within the whole system, since the database is busy with such a heavy request. And subsequent requests don't get any processing power until that first one is finished.

Now, I may be jumping to conclusion here, and I may make the wrong assumptions regarding this, but to me it seems like this is the case at least. I am though very inexperienced with Oracle databases and their design and functioning.

I guess a lot of things can be done to avoid this. Tuning SQL's so that they are more optimized, creating indexes on crucial columns so that rows are found faster etc. etc. But, even if such actions are taken the same behaviour will show if and when a heavy SQL is posted anyway. Resulting in the system being more or less unavailable during that time. Even if only for a minute or two, that behaviour is totally unacceptable for us. I should point out that this is mostly a problem with interactively posted batches, since they run during operational hours.

What we want to do in order to avoid this is to limit the processing power for each request. My colleague has more experience than me with Oracle databases and his best idea is (was) to limit processing power based on user profiles in Oracle. This won't help us though since all requests are made using the same user.

So the real question is: Can processing power in the database be limited per request instead of per user? If it is limited per user, using user profiles, will that limit be kept for all concurrent requests for that user put together, or per request? Is there any way that we can create more than one system user (database user) and have for example interactive batches always run under another system user than other interactive work?

I would appreciate any help in this matter, both explicit solutions or references to relevant documentation regarding the Oracle database or similar.

Best regards, Jörgen
 
Re: Heavy SQL\'s stalling Oracle 10g

Jorgas,

you and your colleague's direction for dealing this is an attempt to treat the symptom - not the problem.

A properly configured Oracle DBMS on a appropriately sized box shouldn't be experiencing the bottlenecks you describe. In fact the symptoms are of a grossly undersized server or mis-configured DBMS.

If you can tell us more about your configuration we can chime in with specific suggestions, can you tell us
a) # Concurrent Users
b) E1 Version and Tools release version
c) Hardware specifics (memory, processors, disk drive configuration)
d) Windows Server Version
e) Other Applications or databases installed on Oracle DB's box.
f) Oracle Block Size, DB Buffers, etc ...
g) Specifically what E1 Job(s) cause this behavior?

After thinking about it, sounds like your REDO logs are too small and/or too few. But it could be many things - like never Analyzing the DB schemas to update statistics . . .

Waiting to hear back from you,
 
Re: Heavy SQL\'s stalling Oracle 10g

Each jde user account is associated with a system user account - and by default they are all associated with the same user account, namely JDE. This system user is the account used to conect to the underlying database. So, yes, it would be possible to create additional ORACLE accounts and use them as system users for different individuals or groups. Then you can use the controls in the database to control the resource usage to some extent.

That said, as noted in Larry Jones response, the best you could hope for there is limiting the blocking rather than fixing. Tuning, probably at a lot of levels, is what's needed. If you can provide jdelist with some more specifics we may well be able to start you in the right direction but tuning is something that isn't a one action fix.
 
Re: Heavy SQL\'s stalling Oracle 10g

Hi Larry! Thanks for your reply.

I'll try to answer some of your questions:

a) # Concurrent Users
Right now only 10-20, so I guess that's not an issue right now.
b) E1 Version and Tools release version
8.12 and Tools Release 8.96 H1
c) Hardware specifics (memory, processors, disk drive configuration)
Well, on the machine where we experience problems we are running a WM-Ware server configured with four 2.21 GHz processors, 3.75 GB RAM and running towards an IBM SAN (I think Raid 5).
d) Windows Server Version
2003
e) Other Applications or databases installed on Oracle DB's box.
On this particular box we are running a JDE Enterprise Server as well as the Oracle database.
f) Oracle Block Size, DB Buffers, etc ...
Block size 8192, I am not sure what you refer to as DB Buffers?
g) Specifically what E1 Job(s) cause this behavior?
Right now a job working towards the F42119 table.

We did get a lot of performance improvement by indexing that table and a couple of other tables used by the job. It went from processing times at around 1-1.5 hours to about 2-3 minutes. Quite an improvement. I seems like a lot of columns are not indexed by default, some of them being date columns which is a bit strange since dates usually are among the most common columns in WHERE clauses.

I also started to dig into memory parameters on the db server. It turned out that our PGA was set to 72 MB, caused by our server being set to 700 concurrent users. I changed this so that the PGA is automatically managed and upped the aggregate PGA to 1 GB. That manouver alone increased the throughput from around 30-40 transactions/second to around 500 transactions/second. We will also have a restart of the server tonight and after that the SGA will have increased from 600MB to 1GB. So I think you were very correct in your assumption that we have a badly configured db... A bit dissapointing since we have had two different DBA's from Oracle here at different occations :)

Please let me know if you have even more suggestions. The redo log theory is interesting. I haven't received any warnings in the enterprise manager regarding the redo log being slow or anything.

Best regards, Jörgen
 
Re: Heavy SQL\'s stalling Oracle 10g

Virtual servers are bound to cause such problems.

You really need a dedicated server. And then look at the tuning side again, because everything will be different then.
 
Re: Heavy SQL\'s stalling Oracle 10g

Thx for the update Jorgas.

Sounds like you're well on your way towards correcting the immediate issues. However, I would listen to Alex re use of VM in production.

Regarding the Oracle consultants. I'm not surprised. There ARE good consultants working for Oracle. However, any large consulting/services organization has room for lots of mediocre consultants as well. Instead I suggest in the future that you find a small, hungry, independant consultancy who have to be good to survive.

Congrats again on resolving your issues,
 
Back
Top