Using Oracle’s Auto Degree of Parallelism (Auto DOP) can improve performance if used judiciously, but unless you’re running autotrace at the time the query or statement runs you probably won’t know what degree Oracle decided to set. There are ways around this; let’s look at them and see what information we can glean from Oracle.
To illustrate this some preparation will need to be done; let’s create and populate a table, along with a couple of indexes:
SQL>
SQL> col c_2 format a35
SQL> set echo on
SQL>
SQL> create table test1(
2 c_1 number not null,
3 c_2 varchar2(255)
4 );
Table created.
SQL>
SQL> begin
2 for i in 1..10 loop
3 insert into test1
4 values (i, 'Test '||i);
5 end loop;
6
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> create index test1_c_1
2 on test1(c_1);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TEST1', method_opt=>'for all columns size 20', cascade=>true, estimate_percent=>null)
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 for i in 11..20 loop
3 if mod(i,2) = 0 then
4 insert into test1 values (i, 'Test '||i);
5 else
6 insert into test1 values(i, null);
7 end if;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TEST1', method_opt=>'for all columns size 20', cascade=>true, estimate_percent=>null)
PL/SQL procedure successfully completed.
SQL>
SQL> create index test1_c_2
2 on test1(c_2,0);
Index created.
SQL>
SQL> insert into test1 select * from test1;
20 rows created.
SQL> insert into test1 select * from test1;
40 rows created.
SQL> insert into test1 select * from test1;
80 rows created.
SQL> insert into test1 select * from test1;
160 rows created.
SQL> insert into test1 select * from test1;
320 rows created.
SQL> insert into test1 select * from test1;
640 rows created.
SQL> insert into test1 select * from test1;
1280 rows created.
SQL> insert into test1 select * from test1;
2560 rows created.
SQL> insert into test1 select * from test1;
5120 rows created.
SQL> insert into test1 select * from test1;
10240 rows created.
SQL> insert into test1 select * from test1;
20480 rows created.
SQL> insert into test1 select * from test1;
40960 rows created.
SQL> insert into test1 select * from test1;
81920 rows created.
SQL> insert into test1 select * from test1;
163840 rows created.
SQL> insert into test1 select * from test1;
327680 rows created.
SQL>
SQL> update test1 set c_2 = 'NULL' where c_2 is null;
163840 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter table test1 modify c_2 not null;
Table altered.
SQL>
SQL> create index test1_c1c2 on
2 test1(c_1, c_2);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'TEST1', method_opt=>'for all columns size 20', cascade=>true, estimate_percent=>null)
PL/SQL procedure successfully completed.
SQL>
[The setup is from a different example; the necessary parts were extracted to guarantee parallel execution.] Let’s now run a query, using a parallel hint:
SQL>
SQL> select b.* from
2 (select /*+ parallel(test1,4) */ distinct c_1, c_2, count(*) over (partition by c_1, c_2) my_count
3 from test1
4 ) b
5 where b.my_count > 1;
C_1 C_2 MY_COUNT
---------- ----------------------------------- ----------
19 NULL 32768
1 Test 1 32768
14 Test 14 32768
15 NULL 32768
3 Test 3 32768
4 Test 4 32768
8 Test 8 32768
11 NULL 32768
13 NULL 32768
17 NULL 32768
20 Test 20 32768
C_1 C_2 MY_COUNT
---------- ----------------------------------- ----------
2 Test 2 32768
5 Test 5 32768
6 Test 6 32768
7 Test 7 32768
9 Test 9 32768
10 Test 10 32768
12 Test 12 32768
16 Test 16 32768
18 Test 18 32768
20 rows selected.
SQL>
Let’s now look at what Oracle chose as the DOP (as this was a previous example it’s known to run in parallel). The following base query (lines 1 through 7) was supplied in the Oracle database forums, and it was modified to also query the current session history. First let’s get a sql_id for the parallel query:
SQL>
SQL> column sql_id new_value your_sql_id
SQL>
SQL> select sql_id
2 from v$sql
3 where sql_text like '%select /*+ parallel(test1,4) */%'
4 and to_date(first_load_time,'RRRR-MM-DD/HH24:MI:SS') >= trunc(sysdate)
5 and rownum = 1;
SQL_ID
-------------
g8bs1utfq845w
SQL>
Using that sql_id we run the following query:
SQL>
SQL> select sql_id,
2 sample_time,
3 sql_opname,
4 trunc(px_flags/2097152) dop,
5 program
6 from DBA_HIST_ACTIVE_SESS_HISTORY
7 where sql_id = '&&your_sql_id'
8 union
9 select sql_id,
10 sample_time,
11 sql_opname,
12 trunc(px_flags/2097152) dop,
13 program
14 from v$active_session_history
15 where sql_id = '&&your_sql_id';
SQL_ID SAMPLE_TIME SQL_OPNAME DOP PROGRAM
------------- ------------------------------ --------------- ---------- ------------------------------
g8bs1utfq845w 16-JUN-16 10.18.49.845 AM SELECT 4 ORACLE.EXE (P001)
g8bs1utfq845w 16-JUN-16 10.18.49.845 AM SELECT 4 ORACLE.EXE (P002)
g8bs1utfq845w 16-JUN-16 10.18.49.845 AM SELECT 4 ORACLE.EXE (P003)
g8bs1utfq845w 08-JUL-16 09.02.13.918 AM SELECT 4 ORACLE.EXE (P001)
g8bs1utfq845w 08-JUL-16 09.02.13.918 AM SELECT 4 ORACLE.EXE (P003)
g8bs1utfq845w 08-JUL-16 09.02.13.918 AM SELECT 4 ORACLE.EXE (P004)
g8bs1utfq845w 08-JUL-16 09.02.13.918 AM SELECT 4 ORACLE.EXE (P005)
g8bs1utfq845w 08-JUL-16 09.04.05.032 AM SELECT 4 ORACLE.EXE (P003)
g8bs1utfq845w 08-JUL-16 09.04.05.032 AM SELECT 4 ORACLE.EXE (P005)
9 rows selected.
SQL>
The above query works in versions 11.2 and 12.1, returning the DOP that was automatically set at runtime. As px_flags are listed in the Oracle documentation as ‘Reserved for further use’ you might not want to rely on this query in future releases. Andrew Sayer, in that same thread, posted a different query that does not rely on translations of undocumented column values, shown below:
SQL>
SQL> SELECT DISTINCT t.*
2 FROM v$sql_plan sp
3 ,XMLTABLE('/other_xml/info' PASSING XMLTYPE(sp.other_xml) COLUMNS type VARCHAR2(30) PATH '@type' , value VARCHAR2(50) PATH '.') t
4 WHERE sp.sql_id = '&&your_sql_id'
5 AND sp.other_xml is not null
6 AND t.type NOT LIKE 'nodeid%'
7 /
TYPE VALUE
------------------------------ --------------------------------------------------
db_version 12.1.0.2
derived_cpu_dop 0
derived_io_dop 0
dop 4
dop_reason table property
parse_schema "BING"
plan_hash 252934288
plan_hash_2 1682034359
plan_hash_full 1682034359
px_in_memory no
px_in_memory_imc no
11 rows selected.
SQL>
That same query can be modified to use the DBA_HIST_SQL_PLAN view, to retrieve results not currently in V$SQL_PLAN:
SQL>
SQL> SELECT DISTINCT t.*
2 FROM dba_hist_sql_plan sp
3 ,XMLTABLE('/other_xml/info' PASSING XMLTYPE(sp.other_xml) COLUMNS type VARCHAR2(30) PATH '@type' , value VARCHAR2(50) PATH '.') t
4 WHERE sp.sql_id = '&&your_sql_id'
5 AND sp.other_xml is not null
6 AND t.type NOT LIKE 'nodeid%'
7 /
no rows selected
SQL>
As expected the current DOP data isn’t in DBA_HIST_SQL_PLAN.
If you’re using autotrace the DOP setting will be returned in the plan output:
Execution Plan
----------------------------------------------------------
Plan hash value: 252934288
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 3100 | 135 (16)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 20 | 3100 | 135 (16)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | VIEW | | 20 | 3100 | 135 (16)| 00:00:01 | Q1,01 | PCWP | |
| 4 | SORT UNIQUE | | 20 | 200 | 135 (16)| 00:00:01 | Q1,01 | PCWP | |
| 5 | WINDOW SORT | | 20 | 200 | 135 (16)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 655K| 6400K| 116 (2)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 655K| 6400K| 116 (2)| 00:00:01 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 655K| 6400K| 116 (2)| 00:00:01 | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL| TEST1 | 655K| 6400K| 116 (2)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."MY_COUNT">1)
Note
-----
- Degree of Parallelism is 4 because of table property
Normally autotrace isn’t used, especially in production environments, so the above options should be used when you want, or need, to know just how “parallel” Oracle made a query execution.
I’ve written on parallel execution before, and I’ll say again that it isn’t the ‘be-all and end-all’ performance enhancer. Sometimes, though, it can be good to use it and when you do it can be nice to know what sort of load Oracle put on your server when executing a particular query in parallel.
