3 Replies Latest reply: Apr 22, 2013 1:47 AM by Hemant K Chitale RSS

    PX COORDINATOR Issue Need Help for Understanding

    921311
      Hi,

      I am performing a simple query:

      select hdr.*
      from gl_je_headers hdr,
      gl_je_lines ln
      where ln.je_header_id=hdr.je_header_id

      While Explan Plan of the above query the following plan executed:

      Plan
      SELECT STATEMENT ALL_ROWSCost: 9,109 Bytes: 2,480,509,508 Cardinality: 9,882,508                                    
           11 PX COORDINATOR                               
                10 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10002 :Q1002Cost: 9,109 Bytes: 2,480,509,508 Cardinality: 9,882,508                          
                     9 HASH JOIN BUFFERED PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 9,109 Bytes: 2,480,509,508 Cardinality: 9,882,508                     
                          4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 364 Bytes: 65,230,760 Cardinality: 266,248                
                               3 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10000 :Q1000Cost: 364 Bytes: 65,230,760 Cardinality: 266,248           
                                    2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000Cost: 364 Bytes: 65,230,760 Cardinality: 266,248      
                                         1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT GL.GL_JE_HEADERS :Q1000Cost: 364 Bytes: 65,230,760 Cardinality: 266,248
                          8 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002Cost: 8,736 Bytes: 59,295,048 Cardinality: 9,882,508                
                               7 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001Cost: 8,736 Bytes: 59,295,048 Cardinality: 9,882,508           
                                    6 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1001Cost: 8,736 Bytes: 59,295,048 Cardinality: 9,882,508      
                                         5 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT GL.GL_JE_LINES :Q1001Cost: 8,736 Bytes: 59,295,048 Cardinality: 9,882,508


      My Query is that the both GL Tables using PARALLELISM OF DEGREE 8.But the performance got degraded.Is it because of the PX COORDINATOR?I am not sure
      i searched it goggle but did not get the concrete answer to my point.I have checked the v$parameter the MAX_PARALLEL_SERVERS is set 8 the CPU_COUNT is 16.


      Kindly help me regarding this.