RE: SMP/MPP and PQO

"In
Search of Clusters" by Gregory F. Pfister, 2nd edition, Prentice-Hall (Saddle River, NJ) 1998 isbn 0-13-899709-8 and it is a wonderful book.
I wish
there were other technical books like this.  The book is readable, fun, and excruciatingly technical all at the same time.

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Mohan, Ross   [mailto:MohanR_at_STARS-SMI.com]Sent: Tuesday, June 12, 2001 5:19   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   SMP/MPP and PQO
  I
  think it was just updated, too.
  <FONT color=#0000ff face=Arial
  size=2> 
  <FONT color=#0000ff face=Arial
  size=2>Great book.....   

<FONT face=Tahoma

    size=2>-----Original Message-----From: Henry Poras     [mailto:Henry.Poras_at_ctp.com]Sent: Tuesday, June 12, 2001 3:04     PMTo: Multiple recipients of list ORACLE-LSubject: RE:     SMP/MPP and PQO
<SPAN

    class=537200618-12062001>Lisa,
    If
    you are interested in more detail on the differences between SMP, NUMA, and     clusters, there is a great book "In search of clusters". It's one of the     best technical books I've read. I can't remember the author's name, but     maybe another list member can help. I picked it up because someone else on     the list recomended it a while ago.
<SPAN

    class=537200618-12062001> 
<SPAN

    class=537200618-12062001>Henrh     

      <FONT face=Tahoma 
      size=2>-----Original Message-----From: Riyaj_Shamsudeen_at_i2.com 
      [mailto:Riyaj_Shamsudeen_at_i2.com]Sent: Tuesday, June 12, 2001 
      12:48 PMTo: Multiple recipients of list 
      ORACLE-LSubject: Re: SMP/MPP and 
      PQOHi Lisa 
              SMP - In 
      SMP many CPUs share the same memory. Typical example would be E450, 4 
      processors, 4GB memory. Only L2 cache in the CPUs need to be co-ordinated 
      when a memory buffer is changed or intended to change. <FONT 
      face=sans-serif size=2>        MPP - In MPP each CPU 
      has its own memory and its own disks that they control (typically). An 
      example would be nCUBE or IBM RS6000, where each processor runs its own OS 
      and messaging is used heavily to coordinate the access to the 
      resources.       
        NUMA - Nun -uniform memory access - Group of CPUs share the same 
      memory and the messaging is used between the group of the CPUs to 
      coordinate the access to the resource. An example would be Sequent NUMA-Q, 
      where there are multiple quads and each quad has 4 cpus and 2GB memory 
      (typically) and IQ-link coordinates the access to the resources between 
      the quads. HP has ccNUMA for this. <FONT face=sans-serif 
      size=2>        Now, I don't think, E10k domain would 
      qualify for a MPP, since there is no coordination or access restrictions 
      between the domains ( at least that I know of). Each domain can manipulate 
      its own disk / memory /devices without any messaging between the domains, 
      in a non-clustered environment. <FONT face=sans-serif 
      size=2>        <FONT face=sans-serif 
      size=2>        PQ architecture splits the FTS or long 
      running operations on non-partitioned tables, in to multiple chunks based 
      upon the rowid ranges. It is not a simple split though. First, work is 
      divided in to degree of parallelism. For example, if the QC process 
      determines that it has to scan 100,000 blocks and parallelism is 
      determined as 4 then 25000 would be the split. But not all 25000 block is 
      handed over to the slave processes. QC uses 9/13 rule. QC hands over 9/13 
      of the work (i.e. 25000 * 9/13=17307) blocks equally to the slave 
      processes. When the slave processes finishes up the 9/13 work, then they 
      get 3/13 of the work (25000 *3/13=5769) blocks and 1/13 of the work when 
      the second set of 3/13s are exhausted. This is done to evenly distribute 
      the load. For example, there could data skew such that one process could 
      finish up its 9/13 work much faster than other processes and hence those 
      processes can take up the rema! in! ! ing work. <FONT 
      face=sans-serif size=2>        The suitability of PQO 
      parallelism should be defined by IO sub system in conjunction with CPU 
      power. If you have many spindles in  2 cpu server my take on it would 
      be to use parallelism of 2 to4 depending upon the cpu clock speed, disk 
      speed, memory etc..     
          ThanksRiyaj 
      "Re-yas" ShamsudeenCertified Oracle DBAi2 technologies   
      www.i2.com 
      
        
        
          
          "Koivu, Lisa" 
            <lisa.koivu_at_efairfield.com> <FONT 
            face=sans-serif size=1>Sent by: root_at_fatcity.com 
            06/12/01 09:17 AM <FONT 
            face=sans-serif size=1>Please respond to ORACLE-L 
                  
                    
            To:        Multiple recipients of list ORACLE-L 
            <ORACLE-L_at_fatcity.com> <FONT face=sans-serif 
            size=1>        cc:       
                    
              Subject:        SMP/MPP and 
        PQO<FONT face="Book Antiqua" 
      size=2>Hello everyone, 
      I was reading up on the differences 
      between SMP, MPP and how they may affect PQO (Parallel Query).<FONT 
      face="Times New Roman" size=3> 
      My understanding is that MPP is a host 
      with defined domains (like an e10k with virtual machines on it).  SMP 
      is a standalone host with no domains and multiple processors.  I am 
      not considering clustering here. 
      It seemed to me the only requirement 
      that you really need to run PQO is to have available resources to power 
      it.  For example, a little 2-cpu box that is pinned a majority of the 
      time is only going to suffer if PQO is turned on.  However, if we had 
      a 16-cpu box with abundant resources, turning on PQO would help fts and 
      large index scans in a dw-type environment.  (At least this is what I 
      saw in the past).   
      Also, I was taught that PQO should not 
      be used when a table/index is not partitioned.  However, upon reading 
      the doco, it states that the slaves split up work by blocks (or was it 
      extents?).  Seems to me this could cause more problems than it's 
      worth (i/o contention?) and partitioning, if done carefully, would be the 
      smarter way to go.  Would the slaves really be smart enough to divy 
      up work intelligently on a non-partitioned object?  My initial 
      thought is NO.   
      In addition, on metalink they even 
      went so far as to state it is OK to use PQO on a 2-processor NT machine. 
       Seems to me the statement that 'PQO provides no benefit on a SMP 
      machine' is not warranted, unless Oracle Support was just pacifying the 
      customer who wanted to see PQO work. 
      Maybe my idea of SMP is too simple. 
       If I am off my rocker can someone please set me 
      straight? 
      Thanks<FONT 
      face="Times New Roman" size=3> 
      Lisa Koivu<FONT 
      face="Times New Roman" size=3> <FONT face="Book Antiqua" 
      size=2>Oracle Database Administrator<FONT 
      face="Times New Roman" size=3> <FONT face=Tahoma 
      size=2>954-935-4117 
      The information in the electronic mail message 
      is Cendant confidential and may be legally privileged, it is intended 
      solely for the addressee(s) access to this internet electronic mail 
      message by anyone else is unauthorized. If you are not the intended 
      recipient, any disclosure, copying, distribution or any action taken or 
      omitted to be taken in reliance on it is prohibited and may be 
      unlawful. 
      The sender believes that this E-mail and any 
      attachments were free of any virus, worm, Trojan horse, and/or malicious 
      code when sent. This message and its attachments could have been infected 
      during transmission. By reading the message and opening any attachments, 
      the recipient accepts full responsibility for taking protective and 
      remedial action about viruses and other defects. Cendant Corporation or 
      Affiliates are not liable for any loss or damage arising in any way from 
      this message or its attachments.