This discussion is archived
1 2 3 4 Previous Next 45 Replies Latest reply: Nov 24, 2009 8:25 AM by 724046 Go to original post RSS
  • 15. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    OK Sounds good. The links you sent me before were actually a great read and provided some good insight with some good cases. Your suggestion of turning off parallelism seems like a good starting point. Then after I will try what Oracle support is mentioning and see the results and compare them to when I turned off the parallelism.

    I understand that CPU_COUNT is a parameter that Oracle sets thus I will not even bother to change it, however is it safe to adjust the parallel parameters to 0?
  • 16. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    Gunners,

    Yes, you can set the parallel parameters to zero. I normally do this just to ensure that parallel query does not get in the way of normal processing. Other people may have a different opinion on the best way of doing this, as I don't use parallel query that often. But I have done this in the past to effectively disable parallel query.

    John
  • 17. Re: Help with Parallelization on our SPARC server...we are drowning!
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    CPU time was 126,325 seconds while waiting on disk I/O was only 720 (505 + 215). And your "PX Deq Credit" was far more than either of these, at a massive 1,876,663.
    Have you considered the fact that Oracle "sees" 32 CPUs so the 126,325seconds time is for multiple Oracle sessions (look at the sessions count) running on 32 "CPUs" ?
    Also, why do you jump to the conclusion that the high time on "PX Deq Credit" indicates a problem ? A problem with CPU or I/O ?



    Hemant K Chitale
  • 18. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    Hemant K Chitale wrote:
    Have you considered the fact that Oracle "sees" 32 CPUs so the 126,325seconds time is for multiple Oracle sessions (look at the sessions count) running on 32 "CPUs" ?
    Also, why do you jump to the conclusion that the high time on "PX Deq Credit" indicates a problem ? A problem with CPU or I/O ?
    Hemant,

    I was not trying to say that the high "PX Deq Credit" time indicates a problem in itself. Only that it was something worth investigating further to get a better understanding of what it meant and why it was happening. I have no idea what this particular event means or how it should be interpreted - there are several other Oracle "wait events" that are not necessarily bad. I suppose "CPU time" is an example of one, as it really indicates busy time rather than waiting time.

    And, Yes, it is possible that the 126,325 seconds could be cumulative across 32 or more parallel query slaves theoretically executing on 32 or 64 virtual SPARC CPU cores. As I tried to point out, when you run multiple threads on a single shared CPU execution core only one of them can be really executing at any point in time and making real progress. But Solaris and Oracle will count or measure that each thread had the full time period executing on a (virtual) CPU.

    So in 1 second there can only be 1 second of CPU time on a single CPU core. But with 8 Oracle sessions each as a separate Solaris thread scheduled at the same time, they will each record 1 second of CPU time, for a total of 8 seconds of CPU time. Clearly these 8 Oracle sessions did not get 1 second each of CPU execution time, and would have each got much less - 0.125 seconds actually executing on a shared CPU core on average.

    The other way of saying this is that when only one thread is running it gets 100% of a 1.2 GHz CPU core. When there are 2 threads running they each get 50%, or effectively a 600 MHz CPU core each (on average). With 4 threads running per core the average effective CPU speed per thread drops to 300 MHz. And at 8 threads (which I believe the T2 does) the average CPU speed per thread drops to 150 MHz. So your CPU work achieved per thread drops as more threads try and run on the same CPU core at the same time - sharing reduces the average CPU speed experienced by each thread as it runs. CPU efficiency may increase - less wasted, stalled CPU cycles - but the work achieved per thread per unit of time decreases as sharing increases.

    So Oracle will record a high CPU usage time - 8 seconds in my example - but the real throughput in terms of work achieved will be much less - only 1 second of CPU work in my example. Hence the T2 is much slower than running on another system with non-shared multiple CPU cores.

    My general view is that if you are using a T2 based system at more than 25% CPU utilisation then you are sharing the CPU cores, and the reported CPU time per thread is its "allocated" CPU time but not its actual "executing" CPU time. The nature of the CPU sharing will reduce the percentage of elapsed time that each thread gets as more threads per CPU core are run, and the CPU utilisation goes up.

    I hope this makes sense one way or another. As I have said, I am not a Parallel Query expert, so cannot really comment on its configuration etc. My suggestion for not using Parallel Query was simply to see if the execution time and the reported profile changed, given that the "PX" events could no longer occur. I was not trying to say that such events were wrong in any way.

    John
  • 19. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Something interesting came to my attention last night.

    CPU_COUNT is set to 32. I started to look at the specs of the server closely and at first glance this value seemed normal. 8 core, 4 threads...but this morning our SysAdmin emailed me another set of specs and he is telling me that we have a 8 core 8 thread signle cpu machine thus 64 threads.

    So essentially I would think CPU_COUNT should have been set to 64 right? Why would Oracle default it to 32 then? All documentation tells me NOT TO ALTER THIS PARAMETER..but does this new information warrant a change?
  • 20. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    From a hardware perspective your SysAdmin is right - the T2 processor has 8 execution cores each supporting 8 threads. So the T2 exposes itself as 64 CPUs to any operating system that boots on it.

    From an operating system perspective there are several things that can change how many "CPUs" it sees or which it exposes up to any application software. This is yet another complication when using T2 boxes, because Sun positions them for virtualization and consolidation of multiple legacy systems onto them. This virtualization can take place at multiple places in the hierarchy.

    On the T2 systems I believe Sun has a very low level hypervisor that sits underneath any operating system. This hypervisor can break up the single 64 virtual CPU into virtual machines with different numbers of these virtual CPUs. Each of these virtual machines can then boot its own operating system. This means each virtual machine is separate, and unaware of the other virtual machines on the same physical hardware.

    Or you could use one of various operating system level virtualization products, VMware being the classic example of this. While this also presents multiple virtual machines, it does it in a slightly different way to the low level hypervisor just mentioned.

    Another option with Solaris 10 is Zones, which are self contained environments within a single Solaris 10 boot image. Each zone is unaware of the other zones, and so looks much like a virtual machine from an applications point of view - it cannot see the other zones and is unaware of them. The difference is that all zones run under the same, shared Solaris 10 operating system. Zones use various tricks to 'hide' processes in different zones from each other, even though they all run on the same Solaris 10 environment. This sharing has advantages (low overheads) and disadvantages (there is only one Solaris 10 running, and if it crashes then everything crashes in all the zones too).

    All of these ways of virtualizing your underlying hardware allow the number of CPUs to be configured. Thus you may be running in any one of these types, with only 32 CPUs exposed. Which is where Oracle gets its CPU_COUNT from.

    On Solaris 'psrinfo' in /usr/sbin lists processors. Thus 'psrinfo | wc -l' will tell you the number of CPUs in the system, as far as Solaris is concerned. I think 'zoneadm list' will tell you if you are in a zone in Solaris or not. There is one 'global' root zone, and then all other zones are named.

    A critical issue with partitioning up the T2 for virtualization is how you allocate virtual CPUs to virtual machines. Generally you want to allocate virtual CPUs that map to the same shared CPU execution core to the same virtual machine. If you don't, then you end up with two virtual machines using virtual CPUs that map to the same shared, physical CPU execution core. This can result in one very busy virtual machine stealing CPU cycles on that CPU core from another virtual machine that is less busy. Because the virtual machines are unaware of each other, the 'slower' one does not know that the 'busier' one is actually stealing cycles off it.

    That is why generally you do want to partition the virtual CPUs by their shared physical CPU core. And be aware that Solaris might number the virtual CPUs in a round robin fashion i.e. 1 to 8 are on different CPU cores, then 9 to 16 are on the same CPU cores again, etc. If you allocate CPUs 1 to 32 to a zone in Solaris, you are in fact using 4 threads on all 8 CPU cores. You might have wanted to allocate CPUs 1-4,9-12,17-20, etc. to the zone, so that they all used the same 4 CPU cores. And then allocate the other virtual CPUs to the other zone(s).

    Either way do not change the CPU_COUNT parameter manually. Oracle sets this with the value that Solaris gives it. You should not need to change this value. It does affect some things internal to Oracle, but there would be little advantage to you from trying to override its value. It does not affect things like the number of Oracle session you can have, or the number of parallel query servers, which are the main things you are interested in from an application point of view.

    John
  • 21. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Makes sense John.

    Side note: I really appreciate the way you clearly answer me with complete details! Makes it easier on me :)

    Back your reply. I think we are running zones. Here is the output of the commands:
    whms3204:/export/home/oracle 
    () VADD> psrinfo | wc -l
          64
    whms3204:/export/home/oracle 
    () VADD> zoneadm list
    whms3204
    
    whms3204:/export/home/oracle 
    () VADD> zoneadm list -v
      ID NAME             STATUS     PATH                           BRAND    IP    
       1 whms3204         running    /                              native   shared
    
    whms3204:/export/home/oracle 
    () VADD> prtdiag | grep III | wc 
    prtdiag can only be run in the global zone 
           0       0       0 
    
    whms3204:/export/home/oracle 
    () VADD> prtdiag -v | grep III 
    prtdiag can only be run in the global zone 
    
    whms3204:/export/home/oracle:  prtdiag | grep UltraSPARC-T2 | wc
          64     320    3200
    So I will not change the parameter CPU_COUNT.

    I finally got a reply back from Metalink..Here is there solution to our issue:

    +Since there are around 2000 queries that are getting serialized due to lack of parallel slaves. If you don't want to increase all the way upto 640 you can try using 300 to start and slowly increase it based on the need of parallel slaves.  Setting DOP to 1 means disabling px at the table level.  Use GV$SYSSTAT view to compare the statistics for parallel operations not downgraded and parallel operations downgraded to serial. For example:

    SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
    +

    So I ran the query that they originally wanted me to run and them the results (These are foll all 3 DB's on this one server):
    SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
    SQL> @pxrun.sql;
     
       INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
    ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
             1        318 Parallel operations not downgraded                                       32         83 2410168744
             1        319 Parallel operations downgraded to serial                                 32         94 4286011915
             1        320 Parallel operations downgraded 75 to 99 pct                              32          0  322858058
             1        321 Parallel operations downgraded 50 to 75 pct                              32          0 3281923117
             1        322 Parallel operations downgraded 25 to 50 pct                              32         54 3330456527
             1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
     
    6 rows selected
    
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
     
       INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
    ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
             1        318 Parallel operations not downgraded                                       32        144 2410168744
             1        319 Parallel operations downgraded to serial                                 32        228 4286011915
             1        320 Parallel operations downgraded 75 to 99 pct                              32          0  322858058
             1        321 Parallel operations downgraded 50 to 75 pct                              32          0 3281923117
             1        322 Parallel operations downgraded 25 to 50 pct                              32        150 3330456527
             1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
     
    6 rows selected
    
    Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
     
       INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
    ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
             1        318 Parallel operations not downgraded                                       32       1951 2410168744
             1        319 Parallel operations downgraded to serial                                 32    1280924 4286011915
             1        320 Parallel operations downgraded 75 to 99 pct                              32         76  322858058
             1        321 Parallel operations downgraded 50 to 75 pct                              32         34 3281923117
             1        322 Parallel operations downgraded 25 to 50 pct                              32       1393 3330456527
             1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
     
    6 rows selected
    Results are pretty much what I expected. Too many operations downgraded to serial. So there solution or test run is to increase PARALLEL_MAX_SERVER to 300 or 640 and set PARALLEL_MIN_SERVER to 0. I will run this test as well as turing off parallel completely.
  • 22. Re: Help with Parallelization on our SPARC server...we are drowning!
    137669 Explorer
    Currently Being Moderated
    Hi,

    As far as I understood your DB is not used for an OLTP system but for a DWH.

    From my point of view one of the main reason for you performance problem is the optimizer_index_cost_adj being set to 10. I would recommend you to set it to the default value of 100.

    In addition I would recommend you to set optimizer_dynamic_sampling to 4 so that the CBO can generate better execution plans and to increase you pga_aggregate_target to at least 2G.

    If your system has 8 cores I don’t think that it makes sense to set parallel_max_servers to a value as large as 64.

    Also a parallelism greater then 16 (or even 8) will probably not increase your performance.

    The best solution would probably be to move you DB to a server with a simple AMD or INTEL quadcore.

    Regards
    Maurice

    Edited by: Maurice Muller on Nov 19, 2009 1:45 AM
    As far as I remember 1 thread is limited to 300Mhz
  • 23. Re: Help with Parallelization on our SPARC server...we are drowning!
    614235 Explorer
    Currently Being Moderated
    Part of my point has been that because the T2 has such a poor CPU execution core (only 1.2 GHz) and that the effective CPU speed per thread drops as more threads run at the same time and share the same CPU execution core you should really be monitoring the CPU utilisation from Solaris, outside of Oracle. There may not be a problem visible in Oracle at all, because it is being throttled by the poor T2 performance. CPU Utilisation is a simple thing to measure while doing your tests using utilities like 'sar' and 'vmstat', and even 'mpstat' if you want per CPU statistics.

    As I said before, with 8 threads per CPU execution core in the T2, if utilisation was above 25% then I would be guessing that you might be CPU bound. At this point it means that on average you always have 2 threads running per CPU execution core (100% / 8 = 12.5%), and so there must be some level of contention to get onto each CPU core.

    If your CPU utilisation is 50% or higher then I would say that you are CPU bound on the T2, having 4 threads per CPU core running concurrently on average. As I said before, each CPU execution core can only ever run 1 thread at a time, so on average at any point in time 3 threads are doing absolutely nothing and waiting to be executed on a CPU core. Solaris cannot detect that some threads are not actually being run, and records it all as 'busy' time. Hence you end up with higher CPU utilisation values but no net increase in throughput in terms of work done per unit of elapsed time.

    If your CPU utilisation is 25% or less, then you are probably not CPU bound, and the performance problem lies somewhere else.

    Don't forget that you are running in a Solaris Zone, and can only see CPU utilisation within that zone. There may be activity happening in other Zones. So make sure you measure and collect CPU utilisation from the Global Zone, so that you see the utilisation over ALL of the zones, and therefore for the whole system.

    John
  • 24. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Just to verify...when disabling parellel, are these parameters to set to 0?
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    _log_parallelism                     integer     8
    _log_parallelism_max                 integer     12
    parallel_max_servers                 integer     64
    parallel_min_percent                 integer     0
    parallel_min_servers                 integer     16
    parallel_server_instances            integer     1
    parallel_threads_per_cpu             integer     2
    Are there others or are these the ones to set to 0?
  • 25. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Maurice Muller wrote:

    If your system has 8 cores I don’t think that it makes sense to set parallel_max_servers to a value as large as 64.

    Also a parallelism greater then 16 (or even 8) will probably not increase your performance.
    So then why is Oracle support telling me to increase the value to either 300 or 640...they want me to test with these two values??
  • 26. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Also, should parallel_threads_per_cpu be set 2...not 4?

    Just curious.
  • 27. Re: Help with Parallelization on our SPARC server...we are drowning!
    724046 Newbie
    Currently Being Moderated
    Was running my first test and I all I did was change the optimizer_cost_adj_index to 100 and ran the users script which has like 30 reporting queries. I also removed a line at the top of his script 'ALTER SESSION FORCE PARALLEL QUERY;'

    While the job was running I thought I would check and see what is going on.
    SQL> SELECT * FROM GV$SYSSTAT WHERE name LIKE 'Parallel operation%';
     
       INST_ID STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
    ---------- ---------- ---------------------------------------------------------------- ---------- ---------- ----------
             1        318 Parallel operations not downgraded                                       32          0 2410168744
             1        319 Parallel operations downgraded to serial                                 32          0 4286011915
             1        320 Parallel operations downgraded 75 to 99 pct                              32          0  322858058
             1        321 Parallel operations downgraded 50 to 75 pct                              32          0 3281923117
             1        322 Parallel operations downgraded 25 to 50 pct                              32          0 3330456527
             1        323 Parallel operations downgraded 1 to 25 pct                               32          0 3721245209
     
    6 rows selected
    
    SQL> SELECT * FROM V$PX_PROCESS;
     
    SERVER_NAME STATUS           PID SPID                SID    SERIAL#
    ----------- --------- ---------- ------------ ---------- ----------
    P004        AVAILABLE         22 8055                    
    P007        AVAILABLE         25 8061                    
    P012        AVAILABLE         30 8071                    
    P008        AVAILABLE         26 8063                    
    P010        AVAILABLE         28 8067                    
    P013        AVAILABLE         31 8073                    
    P002        AVAILABLE         20 8051                    
    P006        AVAILABLE         24 8059                    
    P014        AVAILABLE         32 8075                    
    P011        AVAILABLE         29 8069                    
    P000        AVAILABLE         18 8047                    
    P009        AVAILABLE         27 8065                    
    P005        AVAILABLE         23 8057                    
    P015        AVAILABLE         33 8077                    
    P003        AVAILABLE         21 8053                    
    P001        AVAILABLE         19 8049                    
     
    16 rows selected
    Now, none of my queries are being downgraded and now it only shows 16 px processes all which are sitting available. Last time I ran this mostly everything was downgraded and I showed 64 px processes which were all in use.

    hmmmm....
  • 28. Re: Help with Parallelization on our SPARC server...we are drowning!
    137669 Explorer
    Currently Being Moderated
    Gunners wrote:
    Maurice Muller wrote:

    If your system has 8 cores I don’t think that it makes sense to set parallel_max_servers to a value as large as 64.

    Also a parallelism greater then 16 (or even 8) will probably not increase your performance.
    So then why is Oracle support telling me to increase the value to either 300 or 640...they want me to test with these two values??
    What should it help to set parallel_max_servers to a value like 300 or 640 when you have only something like 32 "virtual" cores.

    I recommand you to take on specific long running SQL an to find out how much faster it is if you execute it in parallel 16 or parallel 4. Also try out if that specific query would not run faster when optimizer_index_cost_adj is set to 100 and optimizer_dynamic_sampling to 4.

    good luck
    Maurice

    Edited by: Maurice Muller on Nov 20, 2009 9:28 AM
  • 29. Re: Help with Parallelization on our SPARC server...we are drowning!
    137669 Explorer
    Currently Being Moderated
    Hi John,

    As far as I understood each core of the T2 is subdivided into 4 virtual CPUs. So even if only one process is running on one core this process will only get 1/4 of the available resource of this core which would be 300 Mhz. On a T2 (in opposite to a sparc64 VI/VII) one single thread will never use the whole resources (1.2 Ghz) of one core.

    https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=781763.1
    While Sun may report that all the virtual CPU on a T2-based computer as being 1200MHz (1.2GHz) -- for example, when looking at "/usr/sbin/psrinfo -v" output, the work within each physical core is actually divided across 4 (Chip-level MultiThreading - or CMT) threads. So any one active Unix process will only use 25% of the CPU core's cycles (or effectively feel like it is on a single CPU that is only running at 300MHz).
    So a single process may report it is nearly 100% CPU bound (as measured via getrusage() calls) from its perspective, but it can only keep one virtual CPU busy at most. So at the system level, the computer may only appear to be _(100/N)% busy_ (where N is # virtual CPUs) due to the activity of that single process.
    Regards
    Maurice

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points