Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Posts Tagged ‘parallel query

How was my parallel query executed last night? – AWR!

leave a comment »

In the previous post, I discussed on how to use v$pq_tqstat view to investigate parallel execution.

Now the question is “How was my parallel query executed last night? Well, was it executed in parallel anyway?”.

The answer is AWR – The king of historical data.

Oracle stores the sql plan and statistics for top SQLs as discussed at this post. Even with the limitation of the number of top SQLs(30 per criteria), parallel queries are more likely to be captured than non-parallel ones.

See following test case.

1. Create objects

UKJA@ukja102> create table t1(c1 int, c2 char(100));
Table created.
UKJA@ukja102>
UKJA@ukja102> insert into t1
  2  select level, 'x'
  3  from dual connect by level <= 10000;
10000 rows created.

2. Create start snapshot and execute parallel query.

UKJA@ukja102>
UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> select /*+ parallel(t1 10) */ count(*)
  2  from t1
  3  ;
  COUNT(*)
----------
     10000
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_cursor(null, null, 'typical'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  6aubqkg2xfv20, child number 0
-------------------------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       | 11126 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
   - dynamic sampling used for this statement
27 rows selected.
UKJA@ukja102>
UKJA@ukja102> select
  2    dfo_number, tq_id, server_type,    process,
  3    num_rows, bytes, avg_latency, waits, timeouts
  4  from v$pq_tqstat
  5  order by 1, 2, 3, 4, 5
  6  ;
DFO_NUMBER      TQ_ID SERVER_TYPE          PROCESS                NUM_ROWS      BYTES AVG_LATENCY      W
---------- ---------- -------------------- -------------------- ---------- ---------- ----------- ------
         1          0 Consumer             QC                           10        320           0
         1          0 Producer             P000                          1         32           0
         1          0 Producer             P001                          1         32           0
         1          0 Producer             P002                          1         32           0
         1          0 Producer             P003                          1         32           0
         1          0 Producer             P004                          1         32           0
         1          0 Producer             P005                          1         32           0
         1          0 Producer             P006                          1         32           0
         1          0 Producer             P007                          1         32           0
         1          0 Producer             P008                          1         32           0
         1          0 Producer             P009                          1         32           0
11 rows selected.

3. Create second snapshot and have an investigation on the parallel query through AWR – dba_hist_sql_plan and dba_hist_sqlstat.

UKJA@ukja102> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select * from table(dbms_xplan.display_awr('&sql_id'));
Enter value for sql_id: 6aubqkg2xfv20
old   1: select * from table(dbms_xplan.display_awr('&sql_id'))
new   1: select * from table(dbms_xplan.display_awr('6aubqkg2xfv20'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 6aubqkg2xfv20
--------------------
select /*+ parallel(t1 10) */ count(*) from t1
Plan hash value: 3110199320
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |     5 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 10183 |     5   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement
22 rows selected.
UKJA@ukja102>
UKJA@ukja102>
UKJA@ukja102> select sql_id, px_servers_execs_total, px_servers_execs_delta
  2  from dba_hist_sqlstat    
  3  where sql_id = '&sql_id'
  4         and snap_id = (select max(snap_id) from dba_hist_snapshot)
  5  ;
Enter value for sql_id: 6aubqkg2xfv20
old   3: where sql_id = '&sql_id'
new   3: where sql_id = '6aubqkg2xfv20'
SQL_ID        PX_SERVERS_EXECS_TOTAL PX_SERVERS_EXECS_DELTA
------------- ---------------------- ----------------------
6aubqkg2xfv20                     10                     10

With AWR, we can conclude that the query got executed in parallel with DOP of 10.

Yes, AWR is always fantastic.

The other way would be investigate the dba_hist_active_sess_history view, but it’s not guaranteed to contain all the corresponding sessions. But captured successfully, it would be the most useful historical data.

Written by Dion Cho

February 20, 2009 at 2:37 pm

Follow

Get every new post delivered to your Inbox.

Join 61 other followers