How to estimate space needed for archive logs

We are back in LinkedIn and this time in Oracle senior DBA group discussion. The question is:

How to calculate necessary space on File System for put in archive log mode an database oracle 11g, currently is not in archive log mode, thanks

Space needed for your archive log location(s) can be estimated by following script, which gets average redo log file size (which should be same for all files BTW) and basically multiplies that by number of log file switches per day:

SELECT log_hist.*,
ROUND(log_hist.num_of_log_swithes * log_file.avg_log_size / 1024 / 1024) avg_mb
FROM
(SELECT TO_CHAR(first_time,'DD.MM.YYYY') DAY,
COUNT(1) num_of_log_swithes
FROM v$log_history
GROUP BY TO_CHAR(first_time,'DD.MM.YYYY')
ORDER BY DAY DESC
) log_hist,
(SELECT AVG(bytes) avg_log_size FROM v$log) log_file;

Archive log files usually go into same location where your “hot” backups go, like FRA. Space needed there depends on your backup strategy and retention policy. If you are deleting archive log files after each successful full or incremental backup, space needed should be count of days between backups plus some good reserve. If you are backing archive log files from archive log destination to some other device, than you should estimate needed space based on that.

ORA-12008: error in materialized view refresh path

This was just one of those days, when strange things happen. Consider this:

You have a materialized view with left outer join:

CREATE MATERIALIZED VIEW table_mw
      REFRESH FAST ON COMMIT
      WITH ROWID
      AS
SELECT
      v.ROWID v_rid,
      t.ROWID t_rid,
      s.ROWID s_rid,
      v.*,
      s.dfr,
      s.dfr
FROM
      table_v v,
      table_t t,
      table_s s
WHERE v.ref_id = t.id (+)
      AND v.ref_id = s.id (+)
/

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID
/

Now, let’s keep aside that not only you have to comply with documented rules for view to be fast refreshable (http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1006674), there is also one undocumented restriction: ANSI-joins are not supported.

With that solved, we did a simple UPDATE of one row in table TABLE_V. Everything looked fine, until we wanted to commit. Commit returned following error stack:

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist

Cause:  Table SNAP$_<mview_name> reads rows from the view MVIEW$_<mview_name>, which is a view on the master table (the master may be at a remote site). Any error in this path will cause this error at refresh time. For fast refreshes, the table <master_owner>.MLOG$_<master> is also referenced.

Action: Examine the other messages on the stack to find the problem. See if the objects SNAP$_<mview_name>, MVIEW$_<mview_name>, <mowner>.<master>@<dblink>, <mowner>.MLOG$_<master>@<dblink> still exist.

I checked alert log and trace files to find nothing of use there. After some experimentation I found out that for some reason, Oracle cannot cope with timestamp-based materialized view logs. Implementation of commit SCN-based materialized view logs solved the problem.

So in the end materialized view logs looked like this:

CREATE MATERIALIZED VIEW LOG ON table_v WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_t WITH ROWID, COMMIT SCN
/

CREATE MATERIALIZED VIEW LOG ON table_s WITH ROWID, COMMIT SCN
/

Seems like one of those problems you solve, but don’t know why they came in first place.

PS: There is also one important think you should not forget or you'll get same generic error on COMMIT of one of the source tables. The thing is that if you materialized view is in different schema, don't forget to also GRANT SELECT on materialized view logs (MLOG$, RUPD$).

How to get IOPS from AWR and Real-time

Recently an interesting question came up in Oracle Database Performance Tuning forum on LinkedIn.

The question was:

How to calculate the IOPS from AWR Report. ?

With follow up question after my first SQL:

Is there any way to calculate IOPS in real time?
What views we should use to calculate IOPS real time.
And can we use gv$ASM_IOSTAT or gv$sysmetric.

I did little Google check and linked nice blog post by G.Makino about how to get needed information from AWR Report in EM: https://gmakino.wordpress.com/2012/11/08/how-to-identify-iops-in-awr-reports .

So I figured it would be nice to share some SQL in LinkedIn forum and here.

Get IOPS from AWR:

SELECT metric_name,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MIN(minval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MIN(minval),1)) END) min,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(MAX(maxval / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(MAX(maxval),1)) END) max,
  (CASE WHEN metric_name LIKE '%Bytes%' THEN TO_CHAR(ROUND(AVG(average / 1024),1)) || ' KB' ELSE TO_CHAR(ROUND(AVG(average),1)) END) avg
FROM dba_hist_sysmetric_summary
WHERE metric_name
  IN ('Physical Read Total IO Requests Per Sec',
  'Physical Write Total IO Requests Per Sec',
  'Physical Read Total Bytes Per Sec',
  'Physical Write Total Bytes Per Sec')
GROUP BY metric_name
ORDER BY metric_name;

METRIC_NAME                                                      MIN                                         MAX                                         AVG ---------------------------------------------------------------- ------------------------------------------- ------------------------------------------- ------------------------------------------- Physical Read Total Bytes Per Sec                                0 KB                                        83453.8 KB                                  415.9 KB Physical Read Total IO Requests Per Sec                          0                                           881.9                                       13.3 Physical Write Total Bytes Per Sec                               0 KB                                        8014.9 KB                                   55 KB Physical Write Total IO Requests Per Sec                         0                                           901.1                                       3.8

Get real time IOPS (It’s good to point out, that there is no such view as gv$ASM_IOSTAT). There is view gv$asm_disk_stat, but its stats are cumulative. For real time sample we have to use gv$sysmetric (v$sysmetric):

SELECT inst_id,
  intsize_csec / 100 "Interval (Secs)",
  metric_name "Metric",
  (
  CASE
    WHEN metric_name LIKE '%Bytes%'
    THEN TO_CHAR(ROUND(AVG(value / 1024 ),1))
      || 'KB'
    ELSE TO_CHAR(ROUND(AVG(value),1))
  END) "Value"
FROM gv$sysmetric
WHERE metric_name IN ('Physical Read Total IO Requests Per Sec', 'Physical Write Total IO Requests Per Sec', 'Physical Read Total Bytes Per Sec', 'Physical Write Total Bytes Per Sec')
GROUP BY inst_id,
  intsize_csec,
  metric_name
ORDER BY inst_id,
  intsize_csec,
  metric_name;

   INST_ID Interval (Secs) Metric                                                           Value ---------- --------------- ---------------------------------------------------------------- ------------------------------------------          1           60.77 Physical Read Total Bytes Per Sec                                354.6KB          1           60.77 Physical Read Total IO Requests Per Sec                          16.4          1           60.77 Physical Write Total Bytes Per Sec                               27.8KB          1           60.77 Physical Write Total IO Requests Per Sec                         2.7          2            59.6 Physical Read Total Bytes Per Sec                                2460KB          2            59.6 Physical Read Total IO Requests Per Sec                          32.8          2            59.6 Physical Write Total Bytes Per Sec                               67.6KB          2            59.6 Physical Write Total IO Requests Per Sec                         3.4

You should be aware of fact that samples 60 seconds +/- on each node of RAC. So they are not exactly the same, but difference is not of a big deal.

Think simple and spare yourself a facepalm

On one of my training sessions, I was presented with a simple query which looked like this:

SELECT company,
            COUNT(*)
FROM invoices
WHERE can_access( company ) = 1
GROUP BY company;

Execution plan with statistics was a follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      2 |        |       |   264 (100)|          |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   1 |  VIEW                   |                   |      2 |   1733 | 36393 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |       |       |          | |   2 |   HASH GROUP BY         |                   |      2 |   1733 | 15597 |   264  (24)| 00:00:04 |       |       |   2587 |00:00:02.84 |    2402 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      2 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:03.00 |    2402 |       |       |          | |*  4 |     INDEX FAST FULL SCAN| INVOICES          |     82 |   2900 | 26100 |   263  (24)| 00:00:04 |     1 |    41 |    586K|00:00:02.74 |    2402 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$2 / from$_subquery$_001@SEL$1    2 - SEL$2    4 - SEL$2 / INVOICES@SEL$2 Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("CAN_ACESS"(TO_NUMBER("COMPANY"))=1)

Function can_access was containing a single SQL and some simple PL/SQL code. Purpose of this function was to filter records based on privileges of current user.

First, I told them, that I don’t like the usage of PL/SQL function at all in this kind of situation. It’s a CPU burner on SQL and PL/SQL context switches in first place, and second, it’s hiding some important information from Oracle optimizer (selectivity for example).

Response was that its legacy stuff and that they have to deal with it somehow … ouch. The problem was, that they knew that function could be ran on grouped result set (limiting calls by great deal), but they was unable to force Oracle to do so.

So I tried usual shenanigans with parentheses, no_merge, no_query_transformation and great deal of begging. But it was to no use. Execution plan looked always the same. In the end I used the following trick to do the job:

SELECT * FROM
            (SELECT /*+ no_merge */
                        company,
                        COUNT(*)
            FROM invoices
            GROUP BY company)
WHERE (SELECT can_access( company ) FROM DUAL) = 1;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                   |      1 |        |       |  2758 (100)|          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |*  1 |  FILTER                  |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.17 |    1201 |       |       |          | |   2 |   VIEW                   |                   |      1 |   2536 | 55792 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   3 |    HASH GROUP BY         |                   |      1 |   2536 | 22824 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.15 |    1201 |    17M|  3187K|     1/0/0| |   4 |     PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   5 |      INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | |   6 |   FAST DUAL              |                   |   2537 |      1 |       |     2   (0)| 00:00:01 |       |       |   2537 |00:00:00.01 |       0 |       |       |          | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$2 / from$_subquery$_001@SEL$1    3 - SEL$2    5 - SEL$2 / INVOICES@SEL$2    6 - SEL$3 / DUAL@SEL$3 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(=1)

Half processed buffers, nice time, great! After that I left home. Next day I’m telling my success story to a colleague of mine and he is like

“... Umm ... why don’t you use HAVING?”

 So final solution should have looked like this:

SELECT company,
            COUNT(*)
FROM invoices
GROUP BY company
HAVING can_access(company) = 1;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation               | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT        |                   |      1 |        |       |   221 (100)|          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |*  1 |  FILTER                 |                   |      1 |        |       |            |          |       |       |   2537 |00:00:00.15 |    1201 |       |       |          | |   2 |   HASH GROUP BY         |                   |      1 |     26 |   234 |   221   (9)| 00:00:04 |       |       |   2537 |00:00:00.14 |    1201 |    17M|  3187K|     1/0/0| |   3 |    PARTITION RANGE ALL  |                   |      1 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.21 |    1201 |       |       |          | |   4 |     INDEX FAST FULL SCAN| INVOICES          |     41 |    290K|  2549K|   205   (2)| 00:00:03 |     1 |    41 |    293K|00:00:00.10 |    1201 |       |       |          | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    4 - SEL$1 / INVOICES@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("CAN_ACCESS"(TO_NUMBER("COMPANY"))=1)

So remember folks … don’t try to solve stuff, when you are tired after a long day and think simple. You will spare yourself some facepalm.

ORA-23313: object group is not mastered at

DISCLAIMER:

Solution presented below is a last resort solution. Official Oracle stand is that you should NEVER ever make any change in data dictionary. Always double check and make notes and backups. Use on your own risk.

One of our customers made a copy of production database (11.2.0.4) to test environment. Administrator also made all necessary changes to environment and database. When I wanted to drop our master replication group, I’ve got following error:

begin DBMS_REPCAT.DROP_MASTER_REPGROUP('MYREPGROUP'); commit; end; Error report: ORA-23313: object group "PUBLIC"."MYREPGROUP" is not mastered at MYDB.MYNEWDOMAIN.COM ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2870 ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2662 ORA-06512: at "SYS.DBMS_REPCAT", line 635 ORA-06512: at line 2 23313. 00000 -  "object group \"%s\".\"%s\" is not mastered at %s"

I queried dba_db_links and everything looked fine. So I checked database domain and global name:

SELECT * FROM GLOBAL_NAME; GLOBAL_NAME           ---------------------- MYDB.MYNEWDOMAIN.COM show parameter domain; NAME        TYPE        VALUE           ----------- ----------- ----------------- db_domain   string      mynewdomain.com

Changes he made also (unfortunately) included change of database domain and global name.

Usual solution would be to revert changes by:

alter system set db_domain='mydomain.com' scope=spfile;
alter database rename global_name to MYDB.mydomain.com;

Then bounce database, drop master replication group, change database domain and global name back and bounce database.

Unfortunately I was not able to implement this solution since database bounce was not possible because of running tests.

In the end I had to do following change in data dictionary to be able to drop master replication group:

alter table system.REPCAT$_REPSCHEMA disable constraint REPCAT$_REPSCHEMA_DEST;
update system.REPCAT$_REPSCHEMA set dblink='MYDB.MYNEWDOMAIN.COM' where sname='MYREPGROUP';
update SYSTEM.DEF$_DESTINATION set dblink='MYDB.MYNEWDOMAIN.COM' where dblink='MYDB.MYDOMAIN.COM';
alter table system.REPCAT$_REPSCHEMA enable constraint REPCAT$_REPSCHEMA_DEST;

I encourage you to study impacted tables very carefully and make backup copy before making any change. Triple check any change you are about to make. Don’t forget about remote sites which you should try to handle before dropping master site. Principle is the same, but usually change in database link is sufficient.

Presented change was done with knowledge that we WILL drop whole replication group and recreate it from scratch. We were not concerned with any delayed transactions.

Locks and Locking

If you missed Oracle Locks and Locking Virtual Session, you can still check out the presentation:



I'll probably stick another free virtual session to start of June. I would like to hear from you what you would like to see. Looking forward to your suggestions in comments bellow ...

Oracle is ignoring my DOP

Some time ago I came over interesting problem with parallel execution in Oracle Database 11g Release 2 (11.2.0.4 PSU 5) which I think is worth sharing.

One of the programmers came to me claiming, that Oracle is totally ignoring his parallel degree which he set with parallel hint. Query looked in principle like this (please keep in mind that query is specifically tailored for problem to manifest):

SELECT
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

All tables and ranged partitioned by quarter of year on DATE column order_date. All indexes are local with no compression.

So I ran it and checked parallel query overview:
Username     QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP ------------ -------- ---------- ---- ------- ------------- ----------  - p000      (Slave)  1          364  435                 4          4  - p001      (Slave)  1          433  435                 4          4  - p002      (Slave)  1          189  435                 4          4  - p003      (Slave)  1          249  435                 4          4  - p008      (Slave)  2          129  435                 4          4  - p009      (Slave)  2          188  435                 4          4  - p010      (Slave)  2          248  435                 4          4  - p011      (Slave)  2          304  435                 4          4 MYDBA        QC                  435  435  

As you can see, he was requesting DOP 2, but our parallel query overview claims he requested DOP 4. Even if that was true, how is it that we see 8 parallel slaves?

You can see something called Slave Set in our query witch has value 1 and 2. This means that Oracle has created two slave sets for processing of our query. This is because Oracle identified two operations which can be done at same time. Basically slave set 1 is producing data for slaves in set 2 which are performing our aggregation operation. Each set is respecting requested DOP, but together, they go double the DOP originally requested.

So this is why DOP 8 and not 4. Now let's try to find out why DOP 4 when we wanted 2. Let's check execution plan first:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                                       | Name              | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   | Max-Tmp | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                                |                   |        |       |       | 87984 (100)|          |       |       |        |      |            |       |       |          |         | |   1 |  PX COORDINATOR                                 |                   |        |       |       |            |          |       |       |        |      |            |       |       |          |         | |   2 |   PX SEND QC (RANDOM)                           | :TQ10006          |  57770 |  1692K|       | 87984   (1)| 00:20:32 |       |       |  Q1,06 | P->S | QC (RAND)  |       |       |          |         | |   3 |    HASH GROUP BY                                |                   |  57770 |  1692K|  2288K| 87984   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |  5747K|  2487K|          |         | |   4 |     PX RECEIVE                                  |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,06 | PCWP |            |       |       |          |         | |   5 |      PX SEND HASH                               | :TQ10005          |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | P->P | HASH       |       |       |          |         | |*  6 |       HASH JOIN BUFFERED                        |                   |  57770 |  1692K|       | 87982   (1)| 00:20:32 |       |       |  Q1,05 | PCWP |            |    32M|  7375K|    12/0/0|    6144 | |   7 |        PX RECEIVE                               |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |   8 |         PX SEND HASH                            | :TQ10003          |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | P->P | HASH       |       |       |          |         | |   9 |          VIEW                                   |                   |    132K|  1939K|       | 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  10 |           HASH UNIQUE                           |                   |    132K|  4525K|  9104K| 45982   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |    32M|  6786K|     8/0/0|         | |  11 |            PX RECEIVE                           |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,03 | PCWP |            |       |       |          |         | |  12 |             PX SEND HASH                        | :TQ10001          |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | P->P | HASH       |       |       |          |         | |* 13 |              FILTER                             |                   |        |       |       |            |          |       |       |  Q1,01 | PCWC |            |       |       |          |         | |  14 |               NESTED LOOPS                      |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  15 |                NESTED LOOPS                     |                   |    192K|  6590K|       | 45976   (1)| 00:10:44 |       |       |  Q1,01 | PCWP |            |       |       |          |         | |  16 |                 PX BLOCK ITERATOR               |                   |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWC |            |       |       |          |         | |* 17 |                  TABLE ACCESS FULL              | ITEM_LINE         |  29124 |   398K|       |   930   (1)| 00:00:14 |   KEY |   KEY |  Q1,01 | PCWP |            |       |       |          |         | |  18 |                 PARTITION RANGE AND             |                   |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |* 19 |                  INDEX RANGE SCAN               | ITEM_ORDER_IX2    |      1 |       |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |       |       |          |         | |  20 |                TABLE ACCESS BY LOCAL INDEX ROWID| ITEM_ORDER        |      7 |   147 |       |     3   (0)| 00:00:01 |     1 |     1 |  Q1,01 | PCWP |            |       |       |          |         | |  21 |        PX RECEIVE                               |                   |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,05 | PCWP |            |       |       |          |         | |  22 |         PX SEND HASH                            | :TQ10004          |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | P->P | HASH       |       |       |          |         | |  23 |          VIEW                                   | index$_join$_005  |    778K|    11M|       | 41998   (2)| 00:09:48 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |* 24 |           HASH JOIN                             |                   |        |       |       |            |          |       |       |  Q1,04 | PCWP |            |    14M|  3248K|    12/0/0|         | |  25 |            PX RECEIVE                           |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  26 |             PX SEND HASH                        | :TQ10002          |    778K|    11M|       |  6406   (2)| 00:01:30 |       |       |  Q1,02 | P->P | HASH       |       |       |          |         | |  27 |              PX BLOCK ITERATOR                  |                   |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWC |            |       |       |          |         | |* 28 |               INDEX FAST FULL SCAN              | ITEM_ORDR_DET_IX2 |    778K|    11M|       |  6406   (2)| 00:01:30 |   KEY |   KEY |  Q1,02 | PCWP |            |       |       |          |         | |  29 |            BUFFER SORT                          |                   |        |       |       |            |          |       |       |  Q1,04 | PCWC |            |    12M|  1246K|     8/0/0|         | |  30 |             PX RECEIVE                          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |  Q1,04 | PCWP |            |       |       |          |         | |  31 |              PX SEND HASH                       | :TQ10000          |    778K|    11M|       | 29363   (1)| 00:06:52 |       |       |        | S->P | HASH       |       |       |          |         | |  32 |               PARTITION RANGE ITERATOR          |                   |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | |* 33 |                INDEX FAST FULL SCAN             | ITEM_ORDR_DET_IX1 |    778K|    11M|       | 29363   (1)| 00:06:52 |   KEY |   KEY |        |      |            |       |       |          |         | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Well, nothing special there for a first sight, but DOP has to run up from somewhere. Let's try to limit it in our session by:

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

And run our query:
Username   QC/Slave Slave Set  SID   QC SID  Requested DOP Actual DOP ---------- -------- ---------- ----- ------- ------------- ----------  - p000    (Slave)  1          360   435                 3          3  - p001    (Slave)  1          436   435                 3          3  - p002    (Slave)  1          189   435                 3          3  - p003    (Slave)  2          249   435                 3          3  - p004    (Slave)  2          307   435                 3          3  - p005    (Slave)  2          365   435                 3          3 MYDBA      QC                  435   435                            

As we can see our DOP is 3 now. This must be an application of rule where if there is no DOP set, Oracle will use default DOP. We are limiting DOP of tables by our hint and this should be inherited to all tables, which are part of parallel query. From execution plan, we can see that Oracle is also performing parallel execution on some indexes with index fast full scan. Normally if Oracle sets to run parallel on indexes also, he'll use DOP which is set for the query. But it seems that he ignored our DOP from tables and overridden it with default value from parallel scan of indexes, since we did not specifically set it. Let's test our query with following hints then:

SELECT /*+ parallel_index(detail, item_ordr_det_ix1, 2) parallel_index(detail, item_ordr_det_ix2, 2) */
            sel.item_id,
            COUNT(*)
FROM
            (SELECT
                       /*+ full(line) parallel(line, 2) */
                       DISTINCT item.item_id,
                       item.order_date
            FROM order_line line,
                      order_item item
            WHERE line.line_id = item.line_id
                       AND line.order_date = item.order_date
                       AND line.order_date BETWEEN '01012014' AND '31122014'
            ) sel,
            order_item_detail detail
WHERE detail.order_date BETWEEN '01012014' AND '31122014'
           AND detail.order_date = sel.order_date
           AND detail.item_id = sel.item_id
GROUP BY sel.item_id;

Username  QC/Slave Slave Set  SID  QC SID  Requested DOP Actual DOP --------- -------- ---------- ---- ------- ------------- ----------  - p000   (Slave)  1          360  435                 2          2  - p001   (Slave)  1          436  435                 2          2  - p002   (Slave)  2          189  435                 2          2  - p003   (Slave)  2          249  435                 2          2 MYDBA     QC                  435  435                            

Now that's a nice BUG. Oracle has really overridden our DOP with default DOP from index parallel scans in index join.

This behavior is very hard to reproduce (I was not able to do so with my own data) and I have seen it only twice in very particular setup, so you might never run into it. But it's good to be prepared.