RE: 11.2.0.3 SQL Profile Confusion
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <christopherdtaylor1994@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Nov 2013 14:28:13 -0500
Offhand I suspect it saw a REALLY low cost for the parallel plan over the
serial plan. Unless things have changed recently (which they likely have) the
optimizer has a very optimistic view of parallel plans. Generally it takes the
cost of doing an operation it could do in parallel (a full table scan for
example) and divides it by the degree of parallelism. With a degree of 32
that is going to look very good to the optimizer. It appears you have about 16
CPUs, eh?
Cost and run performance unfortunately are not necessarily related. And it’s
unfortunate because that is exactly how the optimizer believe the world is. A
lower cost “should” run better than a higher cost, but….
The tuning advisor, in my experience, can help with some low hanging fruit but
it’s far from ready to put us SQL Optimization Specialist out of a job.
Ric Van Dyke
Education Director
Hotsos Enterprises, Ltd
Hotsos Symposiun March 2-6 2014, Be There.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Chris Taylor
Sent: Friday, November 01, 2013 2:53 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 11.2.0.3 SQL Profile Confusion
I understand (I think) the following points:
Metalink Documents say that:
"In 11.2.0.2 Automatic Degree of Parallelism can only be used if I/O
statistics are gathered." DocID: 1269321.1
"The hardware characteristics include I/O calibration statistics so these
statistics must be gathered otherwise Oracle Database does not use the
automatic degree parallelism feature."
• Parallel query profile recommendation: Starting with Oracle Database 11g
Release 2 SQL Tuning
Advisor may recommend accepting a profile that uses the Automatic Degree of
Parallelism
(Auto DOP) feature. A parallel query profile is only recommended when the
original plan is
serial and when parallel execution can significantly reduce the elapsed time
for a long-running
query. When it recommends a profile that uses Auto DOP, SQL Tuning Advisor
gives details
about the performance overhead of using parallel execution for the SQL
statement in the
report. For parallel execution recommendations, SQL Tuning Advisor may provide
two SQL
profile recommendations, one using serial execution and one using parallel.
http://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf
We're using EM Cloud Control 12.1.0.2.0 and I ran a problematic SQL statement
through the SQL Tuning Advisor.
It came up with 1 recommendation:
"Consider accepting the recommended SQL profile to use parallel execution for
this statement."
Benefit 96.52%
Ok I thought, let's try it and implemented the profile (being new to this
Parallel Query Profile opportunity)
However I'm confused why it would even recommend a parallel query profile based
on the above information?
Also, when I tested it out, elapsed time jumped from 1.8 Seconds to 26.845
seconds per execution and used 32 parallel processors! (I definitely
understand this - way too many parallel processes and the overhead involved
increased the total execution time).
Anyone want to help me understand why it came up with a Parallel SQL Profile
recommendation in the first place?
Here's all our parallel parameters in the 11.2.0.3 database:
NAME VALUE
parallel_server FALSE
parallel_server_instances 1
parallel_min_percent 0
parallel_min_servers 0
parallel_max_servers 285
parallel_instance_group
parallel_execution_message_size 2152
parallel_degree_policy MANUAL
parallel_adaptive_multi_user TRUE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE
parallel_io_cap_enabled FALSE
parallel_min_time_threshold AUTO
parallel_degree_limit CPU
parallel_force_local FALSE
parallel_servers_target 256
Thanks,
Chris
Other related posts:
