Posts Tagged ‘parallel query’
How was my parallel query executed last night? – AWR!
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.
Dion Cho - Oracle Performance Storyteller 