I have a very intense update process which has about 8 SQL query - every one of them is having the HINT "NO_PARALLEL" as well as the stmt "Alter session disable dml;" before we execute the query.
Even the Index is also hinted as no parallel.
Every time this process run, i see number of ora_p??? process comes up and it never went goes down.
No when ever this happens, I am seeing lot of slowness in my transaction times. Please help and advice....
1. Oracle version number to 3 or 4 decimal places.
2. Is your Solaris 10 configured in an Oracle supported configuration or are you using containers?
3. How many CPUs on the server?
4. Post the explain plan generated using DBMS_XPLAN using the PRE and /PRE tags to retain the formatting.
5. What metrics have led you to believe that any of these hints are helping rather than hurting performance?
Oracle does not support the use of Containers. You have an environment that is unsupported. I'd suggest making a change to that your first priority.
But I am still at a loss as to why all of this effort is going into stopping parallelization on a machine with multiple CPUs. Is there some metric that demonstrates that this is an issue?
If so what is it?