03 March, 2015

Parallel Execution -- 2 PX Servers

I've posted a couple of examples (here and here) of Parallel Execution servers for Parallel Query.

How do we identify usage of Parallel Execution ?

I will update this post (and, possibly, subsequent post(s)) with a few methods.

The first one (as I've shown in my previous posts) is to look at the column PX_SERVERS_EXECUTIONS in either V$SQLSTATS or V$SQL.  This can identify the number of PX Servers used for an SQL (Query or DML).  However, there is a caveat when the SQL undergoes multiple execution -- the statistic on PX_SERVERS_EXECUTIONS may be cumulative (i.e. additive) across all the executions of the SQL.  UPDATE 13-Mar-15 : See the new post here.

Another method is to look at the V$PX_PROCESS and V$PX_SESSION views.

Let me demonstrate this second method using the same SQL query from my previous blog post.

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release Production on Tue Mar 3 23:34:37 2015

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

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

HEMANT>select distinct sid from v$mystat;


HEMANT>select count(*) from v$px_process;


HEMANT>select count(*) from v$px_session;


HEMANT>select degree from user_tables where table_name = 'LARGE_TABLE';


HEMANT>show parameter cpu 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     4
parallel_threads_per_cpu             integer     4
resource_manager_cpu_allocation      integer     4
HEMANT>set serveroutput off
HEMANT>select /*+ PARALLEL */ count(*) from Large_Table;


HEMANT>select count(*) from v$px_process;


SYS>select qcsid, req_degree, degree, count(*)
  2  from v$px_session
  3  group by qcsid, req_degree, degree
  4  /

---------- ---------- ---------- ----------
       197                                1
       197         16         16         16


The query by the SYS user is from a different session while the "select /*+ PARALLEL */ count(*) from Large_Table;" is being executed by HEMANT.  This query is on V$PX_SESSION and shows only when the Parallel Query sessions are active -- i.e. running HEMANT's  parallel count(*) query.  (If I query V$PX_SESSION after the parallel count(*) completes, I won't get the information).

The above output demonstrates
(a) that there were no PX servers before I began the parallel count(*) query and there were 16 at the end -- 16 PX servers had been started and had not yet shutdown by the time I queried V$PX_PROCESS (They will shutdown after a while  ** note below).
(b) that my parallel count(*) query (executed by SID 197 which is the QueryCo-ordinator -- represented by QCSID) DID request and use 16 PX server sessions (as evidenced in the output from the query on V$PX_SESSION).  Thus, what I claimed on the basis of PX_SERVERS_EXECUTION in my previous post is correct.

** Note : A few minutes later, I can see that the PX Servers have shutdown.

HEMANT>select count(*) from v$px_process
  2  /



Later, I will demonstrate how to join V$PX_PROCESS and V$PX_SESSION.
I will also demonstrate how you manage the number of PX Servers.


No comments: