0

I am trying to run an Oracle SQL query in parallel, but it is refusing to do so. My database parameters are:

fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      CPU
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     TRUE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     8
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     8
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

I have tried the following session statements:

alter session set parallel_degree_policy = 'AUTO';
alter session force parallel QUERY parallel 4;
alter session enable parallel query;

I have tried the following SQL hints:

/*+ parallel(auto) */
/*+ parallel(4) */
/*+ parallel */

I am checking for parallelism with the following queries:

select *
from   v$sql_plan_monitor
where  sid = 4019
and    status = 'EXECUTING';
select SQL_TEXT, PX_SERVERS_EXECUTIONS, EXECUTIONS,
(PX_SERVERS_EXECUTIONS/EXECUTIONS) AS AVG_PQ_EXEC
from V$SQL where SQL_ID = 'a6g8hcgatapky';

I am using a complex SQL statement that runs for 40 minutes. Can anyone tell me what I am doing wrong and why my query does not run in parallel.

Superdooperhero
  • 7,584
  • 19
  • 83
  • 138
  • Can you include the statement or at least the explain plan - I suspect the plan will show something still is serial. – Andrew May 11 '18 at 10:29
  • I prefer not to – Superdooperhero May 11 '18 at 10:50
  • In that plan will be a number of parallelism indicators, PX Send / Receive, PCWP / PCWC and P->P or P->S - I would suspect you have something with a P->S appearing that indicates its dropping to serial - but with no query, no explain plan, you have a question which can not be effectively answered. – Andrew May 11 '18 at 10:59
  • I hope that you use Enterprise Edition. ALTER SESSION ENABLE PARALLEL QUERY should be run to enable parallel query on session level (You did it, check in (G)V$SESSION). If you want to enforce parallel processing by using hint try /*+ parallel(table_name_or_alias, 4) */. Try also to increase PARALLEL_MAX_SERVERS parameter. Sometimes if there is not enough free parallel servers Oracle refuese to run query in parallel, at least double it. – Jacek Wróbel May 11 '18 at 11:05
  • The explain contains nothing P related (or P->S). I'm not a DBA and it does not allow me to set PARALLEL_MAX_SERVERS from my session. – Superdooperhero May 11 '18 at 11:14
  • you say you are not a DBA...is it possible the DBA has set you up in a resource consumer group where you are not able to run anything in parallel? Might want to check with your DBA – thatjeffsmith May 11 '18 at 12:34
  • According to the dba he has not – Superdooperhero May 11 '18 at 12:55
  • Is the query dml? you altered parallel query, but not parallel dml – Andrew May 11 '18 at 13:06
  • No, it's a select statement. – Superdooperhero May 11 '18 at 13:16
  • @Superdooperhero You may want to read my long [here](https://stackoverflow.com/a/21132027/409172) for some tips in diagnosing Degree of Parallelism. There are literally dozens of reasons why the DOP may be unexpected so you'll want to read through that annoyingly long list. Also, it might help to post the execution plan and the entire query. – Jon Heller May 12 '18 at 02:11
  • You can see the sql text and explain plan here: https://drive.google.com/file/d/1vWfT645GjG7MJTa-VZ4QB2HEaC5kHYkp/view?usp=sharing – Superdooperhero May 14 '18 at 07:10
  • Do you have to set a degree of parallelism on one of your tables in order for this to work? I'm using Oracle Applications so I don't want to change their defaults. – Superdooperhero May 14 '18 at 07:52
  • @Superdooperhero Can you add the "text" version of that report? That file doesn't work for me in IE, Firefox, or Chrome. – Jon Heller May 14 '18 at 18:27
  • @Superdooperhero I was able to read the file (had to re-install Flash and allow it to run). Can you re-run it with `parallel(4)` instead of `parallel(auto)`? I'm guessing that with auto parallelism, and parallel_min_time_threshold set to auto (10 seconds), maybe Oracle assumes the plan will run in less than 10 seconds and doesn't use parallelism. Many of the cardinality estimates are way off, you might also want to gather statistics on the tables before re-running. – Jon Heller May 15 '18 at 04:10
  • We have the Gather Schema Statistics concurrent program running every night for all schemas, so not sure why the stats are so wrong. parallel(4) also did not trigger running in parallel. – Superdooperhero May 15 '18 at 08:33
  • @Superdooperhero If you post the report with `/*+ parallel(4) */` it might say why it didn't run in parallel. For example, there may be a "downgrade" warning. – Jon Heller May 17 '18 at 04:51

0 Answers0