This discussion is archived
7 Replies Latest reply: Dec 4, 2007 10:54 AM by 602378 RSS

Too Many oracle ora_p0?? processes in Solaris 10

611668 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ACE Director
    Currently Being Moderated
    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
    602378 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    602378 Newbie
    Currently Being Moderated
    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?