7 Replies Latest reply: Nov 29, 2012 12:22 PM by JustinCave RSS

    why isn't parallel query working in parallel?

    user12159859
      Hi,

      I'm hoping someone can help me.

      I've been testing (several times and different ways) to see if parallel server will work and help on my queries.

      My version is 11.2 on Solaris 10.

      I have a table with over 100,000 records and set autotrace and timing on for testing purposes.

      I've confirmed PARALLEL_MIN_SERVERS=30, and PARALLEL_MAX_SERVERS=160, and PARRALLEL_SERVERS_PER_CPU=2.
      (my server has 4 CPUs)

      I've tested running full table scan (select * from table) and time took approx. 13:30 minutes.

      Then, I altered table and set parallel 4.
      Ran the test again and it ran slower (approx. 16 minutes).

      Then, I ran select /*+ parallel (manual) */ and it was still approx. 14 minutes.

      Next, I ran select /*+ parallel 4 */ and it took approx. 14:30 minutes.

      Next, I reset the degree of parallel on the table back to 1.
      Then, ran select /*+ parallel 4 */ and it took slightly less than 13:30 minutes.

      So, it seems it is not using parallel query at all. Why not?
      SQL> sho parameter cpu
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ---------
      cpu_count                            integer                          4
      parallel_threads_per_cpu             integer                          2
      resource_manager_cpu_allocation      integer                          4
      
      SQL> sho parameter parallel
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ---------
      fast_start_parallel_rollback         string                           LOW
      parallel_adaptive_multi_user         boolean                          TRUE
      parallel_automatic_tuning            boolean                          FALSE
      parallel_degree_limit                string                           CPU
      parallel_degree_policy               string                           MANUAL
      parallel_execution_message_size      integer                          16384
      parallel_force_local                 boolean                          FALSE
      parallel_instance_group              string
      parallel_io_cap_enabled              boolean                          FALSE
      parallel_max_servers                 integer                          160
      parallel_min_percent                 integer                          0
      parallel_min_servers                 integer                          30
      parallel_min_time_threshold          string                           AUTO
      parallel_server                      boolean                          FALSE
      parallel_server_instances            integer                          1
      parallel_servers_target              integer                          64
      parallel_threads_per_cpu             integer                          2
      recovery_parallelism                 integer                          0
      
      SQL> sho parameter servers
      
      NAME                                 TYPE                             VALUE
      ------------------------------------ -------------------------------- ---------
      max_shared_servers                   integer
      parallel_max_servers                 integer                          160
      parallel_min_servers                 integer                          30
      parallel_servers_target              integer                          64
      shared_servers                       integer                          1
        • 1. Re: why isn't parallel query working in parallel?
          JustinCave
          Are you trying to infer whether Oracle is executing the query in parallel based on the execution time? Or are you determining whether Oracle is actually using parallel query based on the query plan? It is entirely possible that using parallel query would cause performance to decline, to stay the same, or to improve. So trying to infer something from the execution times is not a good idea. You'd need to see the actual query plans to determine whether Oracle was using parallel query.

          It seems odd that it would take 15 minutes to scan a table that has only 100,000 rows. If each row is 1k in size, 100,000 rows means that the table is only 100 MB. If each row is 10k in size, 100,000 rows is only 1 GB. It should take much, much less time for a single thread to read 1 GB of data. Are you including the time required to fetch the data across the network? If so, and if that is a substantial fraction of the elapsed time, parallel query isn't going to do anything useful because the bottleneck is how quickly you can push that data across the network and display it in your GUI.

          Justin
          • 2. Re: why isn't parallel query working in parallel?
            user12159859
            Yes, you are correct that I am inferring the use of parallel query by the time lapsed.

            As for my method, I am using SQL*Plus on the database server and using SQL*Plus timing (set timing on).
            The table is 226 Mb (per dba_segments).
            There are just over 100,000 rows, and I have gathered fresh statistics.

            How do I see if it is actually running in parallel or not? (or if it is beneficial to do so)

            Here is the explain plan output... (but when I actually run it, I have autotrace turned on and get the actual timing).

            SQL> explain plan for
              2  select /*+ parallel 4 */ * from TEST;
            
            Explained.
            
            Elapsed: 00:00:00.11
            SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
            
            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------------------------------------------------------------------
            Plan hash value: 3547700631
            
            --------------------------------------------------------------------------------------------------------------
            | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
            --------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT     |          |   104K|    69M|  1007   (1)| 00:00:03 |        |      |            |
            |   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
            |   2 |   PX SEND QC (RANDOM)| :TQ10000 |   104K|    69M|  1007   (1)| 00:00:03 |  Q1,00 | P->S | QC (RAND)  |
            |   3 |    PX BLOCK ITERATOR |          |   104K|    69M|  1007   (1)| 00:00:03 |  Q1,00 | PCWC |            |
            |   4 |     TABLE ACCESS FULL| TEST     |   104K|    69M|  1007   (1)| 00:00:03 |  Q1,00 | PCWP |            |
            --------------------------------------------------------------------------------------------------------------
            
            Note
            -----
               - automatic DOP: skipped because of IO calibrate statistics are missing
            
            15 rows selected.
            
            Elapsed: 00:00:00.02
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2137789089
            
            ---------------------------------------------------------------------------------------------
            | Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
            |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
            ---------------------------------------------------------------------------------------------
            
            
            Statistics
            ----------------------------------------------------------
                     37  recursive calls
                     20  db block gets
                    210  consistent gets
                      0  physical reads
                      0  redo size
                   1765  bytes sent via SQL*Net to client
                    524  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      1  sorts (memory)
                      0  sorts (disk)
                     15  rows processed
            • 3. Re: why isn't parallel query working in parallel?
              JustinCave
              That query plan indicates that parallel query is being used.

              Whatever is causing the query to perform so poorly is, apparently, not something that running in parallel helps. If the problem is an I/O bottleneck, that would make sense-- creating more load on the I/O subsystem with multiple slaves would generally lead to worse runtimes.

              Justin
              • 4. Re: why isn't parallel query working in parallel?
                user12159859
                Thank you very much Justin. I respect your opinion very highly.

                One question: how could you tell from the output I provided that it did run in parallel mode?
                • 5. Re: why isn't parallel query working in parallel?
                  JustinCave
                  The PX COORDINATOR step is something you'll only see when the plan uses parallel query as are the PX SEND QC (RANDOM) and PX BLOCK ITERATOR steps. You'll also only see data in the PQ Distrib column if parallel query is being used. And the IN-OUT column's reference to P->S operations indicates a transition from parallel to serial operations (i.e. multiple parallel slaves being aggregated by a single serial master).

                  Justin
                  • 6. Re: why isn't parallel query working in parallel?
                    user12159859
                    Thanks again Justin.

                    One thing I just checked and noticed is on this server, it is a VMware configuration, running Solaris 10.
                    I wonder if the VMWare might be contributing to a poor performing I/O subsystem.

                    $ prtdiag
                    System Configuration: VMware, Inc. VMware Virtual Platform
                    BIOS Configuration: Phoenix Technologies LTD 6.00 10/13/2009
                    • 7. Re: why isn't parallel query working in parallel?
                      JustinCave
                      It's certainly possible that the presence of a VM layer decreases I/O performance. But so far, we have no particular evidence that the I/O subsystem is the problem (my earlier comment was intended to point out one possible reason that parallel query would result in decreased performance not to state that the problem was clearly one of I/O) so it's a bit early to be speculating about what might be causing a problem that we're not sure exists.

                      What we do know is that if it takes 14 minutes to scan a 226 MB table that something very bad is going on with your system because that's an absurdly low throughput for a toy database running on an old laptop with a single drive.

                      Justin