Search My Oracle Blog

Custom Search

24 March, 2015

Parallel Execution -- 3 Limiting PX Servers

In my previous posts, I have demonstrated how Oracle "auto"computes the DoP when using the PARALLEL Hint by itself, even when PARALLEL_DEGREE_POLICY is set to MANUAL.  This "auto"computed value is CPU_COUNT x PARALLEL_THREADS_PER_CPU.

How do we limit the DoP ?

1.  PARALLEL_MAX_SERVERS is an instance-wide limit, not usable at the session level.

2.  Resource Manager configuration can be used to limit the number of PX Servers used

3.  PARALLEL_DEGREE_LIMIT, unfortunately, is not usable when PARALLEL_DEGREE_POLICY is MANUAL

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Mar 24 22:57:18 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     4
resource_manager_cpu_allocation      integer     4
SYS>show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL
SYS>show parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     64
SYS>  
SYS>select * from dba_rsrc_io_calibrate;

no rows selected

SYS>
SYS>connect hemant/hemant
Connected.
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
         4

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         1                    16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

As expected, the query uses 16 PX Servers (and not the table-level definition of 4).  Can we use PARALLEL_DEGREE_LIMIT ?

HEMANT>alter session set parallel_degree_limit=4;

Session altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         2                    32 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

No, it actually still used 16 PX servers f or the second execution.

What about PARALLEL_MAX_SERVERS ?

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=4;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         3                    36 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Yes, PARALLEL_MAX_SERVERS restricted the next run of the query to 4 PX Servers.  However, this parameter limits the total concurrent usage of PX Servers at the instance level.  It cannot be applied or derived to the session level.

.
.

.

18 March, 2015

Parallel Execution -- 2c PX Servers

Adding to my two previous posts here and here about identifying usage of Parallel Execution -- exactly how many PX servers were used for a query, here is a third method.  (the first two are V$PX_PROCESS/V$PX_SESSION and V$SQLSTATS.PX_SERVERS_EXECUTIONS).  This method uses V$PQ_SESSTAT.

However, the limitation of V$PQ_SESSTAT is that it can only be queried from the same session as that which ran the Parallel Query.  The other two methods can be used by a separate "monitoring" session.

HEMANT>show parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     8
HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=64; 

System altered.

SYS>show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     4
resource_manager_cpu_allocation      integer     4
SYS>show parameter parallel_degree_policy

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy               string      MANUAL
SYS>



As has been identified earlier, the PARALLEL Hint will use 16 PX Servers (limited by PARALLEL_MAX_SERVERS [see this post] because of the values of CPU_COUNT and PARALLEL_THREADS_PER_CPU (where, in this case, PARALLEL_DEGREE_POLICY is yet MANUAL).

SYS>alter system flush shared_pool;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             1
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             1
Server Threads                         16             0
Allocation Height                      16             0
Allocation Width                        1             0
Local Msgs Sent                       464           464
Distr Msgs Sent                         0             0
Local Msgs Recv'd                     464           464
Distr Msgs Recv'd                       0             0

11 rows selected.

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized                    1             2
DML Parallelized                        0             0
DDL Parallelized                        0             0
DFO Trees                               1             2
Server Threads                         16             0
Allocation Height                      16             0
Allocation Width                        1             0
Local Msgs Sent                       464           928
Distr Msgs Sent                         0             0
Local Msgs Recv'd                     464           928
Distr Msgs Recv'd                       0             0

11 rows selected.

HEMANT>

As we can see, the SESSIONS_TOTAL count of Server Threads does not get updated (although the count of Queries Parallelized is updated).  This behaviour remains in 12c.  (However, there are two additional statistics available in 12c).
.
.
.

13 March, 2015

Parallel Execution -- 2b PX Servers

Continuing my previous post, here I demonstrate  using V$SQLSTATS.PX_SERVERS_EXECUTIONS and a couple of issues around it.

I have restarted the database.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Fri Mar 13 22:49:20 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HEMANT>show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     4
resource_manager_cpu_allocation      integer     4
HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';

DEGREE
----------------------------------------
         1

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         1                    16 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         2                    32 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

[Note : To understand why the executions took 16 PX Servers inspite of the degree on table being 1, see this post]
So we see that PX_SERVERS_EXECUTIONS shows cumulative statistics.  Let's try a slight twist.

HEMANT>connect / as sysdba
Connected.
SYS>alter system set parallel_max_servers=8;

System altered.

SYS>connect hemant/hemant
Connected.
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         3                    40 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

Because I set PARALLEL_MAX_SERVERS to 8, my query on Large_Table could take only 8 PX Servers at the next execution.  V$SQLSTATS.PX_SERVERS_EXECUTIONS now shows a cumulative count of 40 for 3 executions. There is no way to determine how many PX Servers were used in each of the 3 executions, because the history of executions is not maintained.
(In my controlled experiment, we know, by deduction, that the 3rd execution took 8 PX Servers simply because we know already that the first 2 executions took a cumulative count of 32 PX Servers -- by deducting 32 from 40 to get 8 for the 3rd execution)

What happens if the SQL is invalidated ?

HEMANT>alter table large_table parallel 4;

Table altered.

HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;

  COUNT(*)
----------
   4802944

HEMANT>select executions, px_servers_executions, sql_fulltext
  2  from v$sqlstats
  3  where sql_id = '8b0ybuspqu0mm';

EXECUTIONS PX_SERVERS_EXECUTIONS SQL_FULLTEXT
---------- --------------------- --------------------------------------------------------------------------------
         1                     8 select /*+ PARALLEL */ count(*) from Large_Table

HEMANT>

The ALTER TABLE, being a DDL, had invalidated the query on Large_Table.  So, V$SQLSTATS also got reset.  Therefore, EXECUTIONS reset to 1 and PX_SERVES_EXECUTIONS got reset to 8.

.
.

.

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com