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
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