Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted July 14, 2016

Returning The Actual DOP When Using Oracle’s Auto Degree Of Parallelism

By David Fitzjarrell

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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM