- 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
- 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 :
Tools\"VMwareToolboxCmd.exe stat cpures
Tools\"VMwareToolboxCmd.exe stat memres
- 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
- *Parallel_threads_per_cpu* =1 --- since each VCPU is barely a
- *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
----------------------------------- ----------- ---------- ---------
direct path read 46,138 7.19 .56
db file sequential read 6,828 5.11 2.69
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_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 .
- *From*: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- *To*: "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@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