Re: 12c Parallelism in windows potentially behind library cache lock/ PX Deq: Parse Reply/ PX Deq: Execution Msg

• Hi Jonathan ,
• Most of our clients' Infrastructure is completely transparent to us when it comes to how their Esx Farm is built or their Vcenter configuration (allocation of VCPUs, data stores..etc).
• last two years they aggressively swapped they physical servers to an all virtuallized environment. We wound up forced to move and certify our application to 12c (from 10g) on VMs while a data consolidation was also in progress.
• The physical host is an intel x86 platform *: * Intel® Xeon® Processor E5-2660 v3 25M Cache, 2.60 GHz

-

   CPU and memory aren't reserved neither :

       C:\Users\>"C:\Program Files\VMware\VMware    Tools\"VMwareToolboxCmd.exe stat cpures

           0 MHz

        C:\Users\>"C:\Program Files\VMware\VMware    Tools\"VMwareToolboxCmd.exe stat memres

         0 Mb

• All we know from the IT guy is that the VCPUs are thread based (not core based) so that made me set thread_per_cpu =1.
• The client has no license for Diag pack unfortunatly. I use Oracle-ASH from Kyle Hailey and statspack plus sqlt .
  -
  
• To stop the Parallelization of the query I changed the bellow paramaters :
• *Parallel_threads_per_cpu* =1 --- since each VCPU is barely a physical thread
• *Optimizer_adaptive_features*=false; --- no effect
• *Paralle_max_servers*=1 --- the query isn't run in parallel now
• *Optimizer_features_enable*='10.2.0.5' --- works sometimes to stabilize the chosn plans (to avoid using adaptive plans , symanic stats, Cardinality feedback etc..)
• Since the change the previous query has improved (no more PX) . There is no concurrency related waits, the majority of the DB time being CPU (with few User I/O waits)
• Waits Time Avg Wait Wait Type \1000 Hours Ms Pct
  • ----------- ---------- ---------

    ---

    CPU 98.51 77.25 direct path read 46,138 7.19 .56 5.64 db file sequential read 6,828 5.11 2.69 4.00
    -
    

What this doesn't tell is that some child cursors of other queries in the database are now hanging and crashing (not all version) . I traced a report (BIND variables only) that was hanging on a user session for 45min. I saw branches of the execution plan being parallelized though max_parallel_server set to 1.
where does this come from and why is the optimzer is still executing some quries in parallel ?

   A question is what other parameter should I change to deactivate parallelism permanently ?

    I tried changing parallel_degree_limit from CPU to 2 but still I see parallelization here and there.

      alter system set parallel_degree_limit=2 ;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      2
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     1
parallel_min_percent                 integer     0
parallel_min_servers                 integer     1
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_servers_target              integer     1
parallel_threads_per_cpu             integer     1

-

-

thank you and sorry for the long reply . Koss

-
-

• *From*: Jonathan Lewis <jonathan_at_xxxxxxxxxxxxxxxxxx>
• *To*: "Oracle-L_at_xxxxxxxxxxxxx" <Oracle-L_at_xxxxxxxxxxxxx>
• *Date*: Sat, 8 Jun 2019 15:17:36 +0000

I would be very cautious about trusting any timing information for a query running with DOP 32 (and 64 PX processes) on a virtual machine with 16 virtual CPUs. What fraction of a real CPU is a virtual CPU, and are your "real CPUs" based on a core count or thread count ? You may have far less CPU available than you think - and in a virtual environment you could be subject to all sorts of odd time-losses that Oracle doesn't know about when a process is pre-empted, or queued.

I can't work out exactly how long your query is taking from the stats you supplied, but some of them seem to indicate reasonable behaviour - when you optimize the query (and you don't say how many branches you have to the UNION ALL) one process could demand a lot of CPU for optimising leaving 64 processes waiting for the optimisation to complete.

Are you licensed for the performance and diagnostic packs ? Have you checked who the waiters are waiting for, and how much CPU that blocker takes to optimise the query, and how much "lost time" that single session records. (This may mean taking one trace file and comparing the e= and c= values and tim= timestamps at points where the session is apparently not waiting.

Regards
Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l