How to Verify Parallel Execution using 3 Different Ways

Applies to: Database Management Software Oracle std & Enterprise Edition Oracle 10g to Oracle 11g.

There are many ways to crosscheck Parallel Execution. Few are below:

1. using V$pq_sesstat view.
2. Explain Plan for table.
3. V$session View.

Let's discuss all one by one with example

1. V$pq_sesstat View: Value of "Queries Parallelized" represents parallel execution. If this is greater than 0 parallel execution is working.

Before Parallel Execution:

SQL> select * from v$pq_sesstat;

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

11 rows selected.

SQL> select /*+ parallel( emp,4) */ * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

After Parallel Execution:

SQL> 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                          4             0
Allocation Height                       4             0
Allocation Width                        1             0
Local Msgs Sent                        22            22
Distr Msgs Sent                         0             0
Local Msgs Recv'd                      22            22
Distr Msgs Recv'd                       0             0

11 rows selected.

2. Explain Plan for table: Using this method, we can also fiture out Parallel execution is happening on not.

SQL> explain plan for SELECT /*+ PARALLEL(e) PARALLEL(d) */ dname,
           MAX(sal), AVG(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY
           dname;   

Explained.

SQL> set linesize 10000;
SQL> @ $ORACLE_HOME/rdbms/admin/utlxplp.sql


 











In about query plan, Fields "TQ", "IN-OUT" and "PQ Distrib" clearly show how parallel execution is happening. While for Reference in below query where no parallel hint is 
given these fields are unavailable.


SQL> explain plan for SELECT  dname,
           MAX(sal), AVG(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY
           dname;  

Explained.

 











3. V$session View: This view can also give you detail about parallel execution. use below query to find SLQ_ID and it's parallel sessions. 

SQL> select sql_ID, program from v$session ;

SQL_ID        PROGRAM
------------- ------------------------------------------------
4qm8a3w6a1rfd oraagent.bin@database.example.com (TNS V1-V3)
4qm8a3w6a1rfd oracle@database.example.com (P001)
4qm8a3w6a1rfd oracle@database.example.com (P002)
4qm8a3w6a1rfd oracle@database.example.com (P003)
4qm8a3w6a1rfd oracle@database.example.com (P004)

Program names start with "P00"  with same sql_id show how may session are running for a query in parallel.
      

No comments:

Post a Comment