11.2.0.3 SQL Profile Confusion

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Fri, 1 Nov 2013 13:52:37 -0500
Message-ID: <CAP79kiQ3MSvaT4_0et8evTkxDnnuiRXZrG7en-B_RUSjzrV38g_at_mail.gmail.com>

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

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

Received on Fri Nov 01 2013 - 19:52:37 CET