7 Replies Latest reply: Dec 4, 2007 12:54 PM by ben23 RSS

    Too Many oracle ora_p0?? processes in Solaris 10

    611668
      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. Re: Too Many oracle ora_p0?? processes in Solaris 10
          damorgan
          Post the following:

          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?
          • 2. Re: Too Many oracle ora_p0?? processes in Solaris 10
            611668
            1. Oracle 10g 10.2.0
            SQL*Plus: Release 10.2.0.2.0

            2. Yes, solaris 10g has been configured for this.

            3. We have 4 quad procs

            4. Plan
            <PRE>
            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop| TQ |IN-OUT| PQ Distrib |
            ------------------------------------------------------------------------------------------------------------------------------------------------------

            | 0 | DELETE STATEMENT | | 131K| 11M| 52285 (1)| 00:15:42 | | | | | |
            | 1 | DELETE | TABLE1 | | | | | | | | | |
            | 2 | PX COORDINATOR | | | | | | | | | | |
            | 3 | PX SEND QC (RANDOM) | :TQ10000 | 131K| 11M| 52285 (1)| 00:15:42 | | | Q1,00 | P->S | QC (RAND) |
            | 4 | PX PARTITION RANGE ALL | | 131K| 11M| 52285 (1)| 00:15:42 | 1 | 9| Q1,00 | PCWC | |
            | 5 | INLIST ITERATOR | | | | | | | | Q1,00 | PCWC | |
            |* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 | 131K| 11M| 52285 (1)| 00:15:42 | 1 | 9| Q1,00 | PCWP | |
            |* 7 | INDEX RANGE SCAN | IDX_ID_1 | 218K| | 196 (2)| 00:00:04 | 1 | 9| Q1,00 | PCWP | |

            </PRE>

            Predicate Information (identified by operation id):
            ---------------------------------------------------

            6 - filter("COL1"='ABC')
            7 - access("ID_1"='01234567891092' OR "ID_1" = "0000001233445566')

            22 rows selected.

            Message was edited by:
            user608665
            • 3. Re: Too Many oracle ora_p0?? processes in Solaris 10
              611668
              Now after running many explain plan and different queries:
              here is what happening:

              1. The queries are still in parallel even though NO_PARALLEL is being used in all the cases as well as the Alter session disable parallel dml;

              2. I also tried to use the PQ_DISTRIBUTE HINT with E NONE NONE - still the parallelism is present.

              And there is no entry in the OL$HINTS ...

              Please advice....
              • 4. Re: Too Many oracle ora_p0?? processes in Solaris 10
                damorgan
                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?
                • 5. Re: Too Many oracle ora_p0?? processes in Solaris 10
                  ben23
                  That means you have an error in specifying the hint, or missed hinting a table in the query.

                  The hint must refer to the table ALIAS in brackets, rather than the table NAME:
                  • 6. Re: Too Many oracle ora_p0?? processes in Solaris 10
                    611668
                    Yes, that's correct. I did not missed them, I did check the hints syntax and its correct.

                    After lot of study and reading lots of articles

                    I found when you do the alter session disable parallel query and followed by the alter session parallel dml and ddl; it does work.

                    I am now running my queries with these options and I will post my results in few mins.
                    • 7. Re: Too Many oracle ora_p0?? processes in Solaris 10
                      ben23
                      The hints themselves would work without the need for ALTER session. Are there any views involved?

                      ALTER table noparallel would be the other option, but I take it you want to leave it on for other queries?