1 2 3 Previous Next 31 Replies Latest reply: Dec 14, 2012 9:14 AM by Penky RSS

    Please help with parallel query

    Penky
      Hi all,

      I am "playing around" with parallel query and trying to see whether it could improve some longer running queries, but I can not get the database to use a parallel execution plan, no matter what I do! I hope someone can point me into the right direction!

      ORACLE Version is 11.2.0.2
      Server OS Win 2008 R2
      CPU count = 32
      64GB RAM
      AMM activated, memory_target=50560M
      SQL> show parameter parallel

      NAME TYPE VALUE
      ------------------------------------ ----------- --------------
      fast_start_parallel_rollback string LOW
      parallel_adaptive_multi_user boolean TRUE
      parallel_automatic_tuning boolean FALSE
      parallel_degree_limit string CPU
      parallel_degree_policy string AUTO
      parallel_execution_message_size integer 16384
      parallel_force_local boolean FALSE
      parallel_instance_group string
      parallel_io_cap_enabled boolean FALSE
      parallel_max_servers integer 985
      parallel_min_percent integer 0

      NAME TYPE VALUE
      ------------------------------------ ----------- --------------
      parallel_min_servers integer 16
      parallel_min_time_threshold string 5
      parallel_server boolean FALSE
      parallel_server_instances integer 1
      parallel_servers_target integer 512
      parallel_threads_per_cpu integer 2
      recovery_parallelism integer 0
      I also ran the IO calibration which resultet in
      Max I/O per Second 21569
      Max MB per Second 989
      I collected system statistics, time frame 1 hour. the results are:
      select pname, pval1 from sys.aux_stats$;
      STATUS     
      DSTART     
      DSTOP     
      FLAGS     0
      CPUSPEEDNW     915
      IOSEEKTIM     10
      IOTFRSPEED     4096
      SREADTIM     0,589
      MREADTIM     0,841
      CPUSPEED     1355
      MBRC     11
      MAXTHR     679936
      SLAVETHR
      I altered all my tables and indexes using "ALTER TABLE xxx PARALLEL" so when I query the dba_tables, the DEGREE is DEFAULT for all objects invoked in my queries.

      from what I have learned so far, I set all the neccessary parameters.
      From my understanding, every query which would estimate longer than 5 seconds, should be considered to run in parallel (parallel_min_time_threshold=5). But not a single query is doing so unless forced manually with a /*+ PARALLEL */ hint !!! It drives me crazy. Manually choosing a degree of e.g. 16 speeds up some queries from 15 minutes to 1 minute, but why does ORACLE not do it by itself?
      Since it is a Siebel application we are talking of, there is no possibility to add hints to the SQL.

      example:

      this query took 29 seconds to complete, but was run in SERIAL
      SQL_ID atzj0dmhshb23, child number 0
      -------------------------------------
      SELECT T7.CONFLICT_ID, T7.LAST_UPD, T7.CREATED,
      T7.LAST_UPD_BY, T7.CREATED_BY, T7.MODIFICATION_NUM,
      T7.ROW_ID, T9.MAIN_PH_NUM, T9.NAME, T9.REGION,
      T9.X_SUB_REGION, T20.ATTRIB_44, T20.ATTRIB_26,
      T20.ATTRIB_45, T20.ATTRIB_27, T20.ATTRIB_03,
      T33.SUPPRESS_MAIL_FLG, T33.EMAIL_ADDR, T33.MID_NAME,
      T33.PR_DEPT_OU_ID, T33.LAST_NAME, T33.SEX_MF,
      T33.PR_PER_ADDR_ID, T33.PR_POSTN_ID, T30.PR_ADDR_ID,
      T33.HOME_PH_NUM, T33.OWNER_PER_ID, T33.WORK_PH_NUM,
      T33.FAX_PH_NUM, T33.FST_NAME, T20.ATTRIB_07,
      T3.INTEGRATION_ID, T33.PR_PER_PAY_PRFL_ID, T33.PRIV_FLG,
      T33.PR_MKT_SEG_ID, T33.PR_REP_SYS_FLG,
      T33.PR_REP_MANL_FLG, T33.PR_REP_DNRM_FLG, T33.PR_OPTY_ID,
      T33.PR_GRP_OU_ID, T33.EMP_FLG, T8.OWN_INST_ID,
      T8.INTEGRATION_ID, T33.PERSON_UID, T7.NAM

      Plan hash value: 35208051

      ---------------------------------------------------------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      ---------------------------------------------------------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | | | 34 (100)| |
      | 1 | NESTED LOOPS OUTER | | 10 | 42440 | 34 (0)| 00:00:01 |
      | 2 | NESTED LOOPS OUTER | | 10 | 42300 | 33 (0)| 00:00:01 |
      | 3 | NESTED LOOPS OUTER | | 10 | 42160 | 32 (0)| 00:00:01 |
      | 4 | NESTED LOOPS OUTER | | 10 | 42020 | 31 (0)| 00:00:01 |
      | 5 | NESTED LOOPS | | 10 | 41880 | 30 (0)| 00:00:01 |
      | 6 | NESTED LOOPS OUTER | | 11 | 45947 | 29 (0)| 00:00:01 |
      | 7 | NESTED LOOPS | | 11 | 45716 | 28 (0)| 00:00:01 |
      | 8 | NESTED LOOPS OUTER | | 11 | 45364 | 27 (0)| 00:00:01 |
      | 9 | NESTED LOOPS OUTER | | 11 | 45243 | 26 (0)| 00:00:01 |
      | 10 | NESTED LOOPS OUTER | | 11 | 45122 | 25 (0)| 00:00:01 |
      | 11 | NESTED LOOPS OUTER | | 11 | 43648 | 24 (0)| 00:00:01 |
      | 12 | NESTED LOOPS OUTER | | 11 | 37070 | 23 (0)| 00:00:01 |
      | 13 | NESTED LOOPS OUTER | | 11 | 34661 | 22 (0)| 00:00:01 |
      | 14 | NESTED LOOPS OUTER | | 11 | 34430 | 21 (0)| 00:00:01 |
      | 15 | NESTED LOOPS OUTER | | 11 | 33891 | 20 (0)| 00:00:01 |
      | 16 | NESTED LOOPS OUTER | | 11 | 33253 | 19 (0)| 00:00:01 |
      | 17 | NESTED LOOPS OUTER | | 11 | 32362 | 18 (0)| 00:00:01 |
      | 18 | NESTED LOOPS OUTER | | 11 | 31999 | 17 (0)| 00:00:01 |
      | 19 | NESTED LOOPS OUTER | | 11 | 29337 | 16 (0)| 00:00:01 |
      | 20 | NESTED LOOPS OUTER | | 11 | 28556 | 15 (0)| 00:00:01 |
      | 21 | NESTED LOOPS OUTER | | 11 | 28061 | 14 (0)| 00:00:01 |
      | 22 | NESTED LOOPS OUTER | | 11 | 26400 | 13 (0)| 00:00:01 |
      | 23 | NESTED LOOPS OUTER | | 11 | 26169 | 12 (0)| 00:00:01 |
      | 24 | NESTED LOOPS OUTER | | 11 | 25465 | 10 (0)| 00:00:01 |
      | 25 | NESTED LOOPS OUTER | | 11 | 21131 | 9 (0)| 00:00:01 |
      | 26 | NESTED LOOPS OUTER | | 11 | 18326 | 8 (0)| 00:00:01 |
      | 27 | NESTED LOOPS | | 11 | 13651 | 7 (0)| 00:00:01 |
      | 28 | NESTED LOOPS OUTER | | 11 | 12452 | 6 (0)| 00:00:01 |
      | 29 | NESTED LOOPS OUTER | | 11 | 10978 | 5 (0)| 00:00:01 |
      | 30 | NESTED LOOPS | | 11 | 9504 | 4 (0)| 00:00:01 |
      | 31 | NESTED LOOPS OUTER | | 4 | 360 | 3 (0)| 00:00:01 |
      | 32 | NESTED LOOPS | | 4 | 228 | 2 (0)| 00:00:01 |
      |* 33 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
      | 34 | TABLE ACCESS BY INDEX ROWID| S_CONTACT_BU | 4 | 184 | 1 (0)| 00:00:01 |
      |* 35 | INDEX RANGE SCAN | S_CONTACT_BU_M1 | 4 | | 1 (0)| 00:00:01 |
      | 36 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 33 | 1 (0)| 00:00:01 |
      |* 37 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      |* 38 | TABLE ACCESS BY INDEX ROWID | S_CONTACT | 3 | 2322 | 1 (0)| 00:00:01 |
      |* 39 | INDEX UNIQUE SCAN | S_CONTACT_P1 | 1 | | 1 (0)| 00:00:01 |
      | 40 | TABLE ACCESS BY INDEX ROWID | S_MED_SPEC | 1 | 134 | 1 (0)| 00:00:01 |
      |* 41 | INDEX UNIQUE SCAN | S_MED_SPEC_P1 | 1 | | 1 (0)| 00:00:01 |
      | 42 | TABLE ACCESS BY INDEX ROWID | S_PRI_LST | 1 | 134 | 1 (0)| 00:00:01 |
      |* 43 | INDEX UNIQUE SCAN | S_PRI_LST_P1 | 1 | | 1 (0)| 00:00:01 |
      |* 44 | TABLE ACCESS BY INDEX ROWID | S_PARTY | 1 | 109 | 1 (0)| 00:00:01 |
      |* 45 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | | 1 (0)| 00:00:01 |
      | 46 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_SS | 1 | 425 | 1 (0)| 00:00:01 |
      |* 47 | INDEX RANGE SCAN | S_CONTACT_SS_U1 | 1 | | 1 (0)| 00:00:01 |
      | 48 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_LOYX | 1 | 255 | 1 (0)| 00:00:01 |
      |* 49 | INDEX RANGE SCAN | S_CONTACT_LOYX_U1 | 1 | | 1 (0)| 00:00:01 |
      |* 50 | INDEX RANGE SCAN | S_DQ_CON_KEY_U1 | 1 | 394 | 1 (0)| 00:00:01 |
      |* 51 | TABLE ACCESS FULL | S_CASE | 1 | 64 | 0 (0)| |
      | 52 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 21 | 1 (0)| 00:00:01 |
      |* 53 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
      | 54 | TABLE ACCESS BY INDEX ROWID | S_EMP_PER | 1 | 151 | 1 (0)| 00:00:01 |
      |* 55 | INDEX UNIQUE SCAN | S_EMP_PER_U1 | 1 | | 1 (0)| 00:00:01 |
      | 56 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 45 | 1 (0)| 00:00:01 |
      |* 57 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 4 | | 1 (0)| 00:00:01 |
      | 58 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_FNX | 1 | 71 | 1 (0)| 00:00:01 |
      |* 59 | INDEX RANGE SCAN | S_ORG_EXT_FNX_U1 | 1 | | 1 (0)| 00:00:01 |
      | 60 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_X | 1 | 242 | 1 (0)| 00:00:01 |
      |* 61 | INDEX RANGE SCAN | S_ORG_EXT_X_U1 | 1 | | 1 (0)| 00:00:01 |
      | 62 | TABLE ACCESS BY INDEX ROWID | S_CON_ADDR | 1 | 33 | 1 (0)| 00:00:01 |
      |* 63 | INDEX RANGE SCAN | S_CON_ADDR_M51 | 1 | | 1 (0)| 00:00:01 |
      | 64 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 81 | 1 (0)| 00:00:01 |
      |* 65 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
      | 66 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 58 | 1 (0)| 00:00:01 |
      |* 67 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | 1 (0)| 00:00:01 |
      | 68 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 49 | 1 (0)| 00:00:01 |
      |* 69 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 70 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 21 | 1 (0)| 00:00:01 |
      |* 71 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
      | 72 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 219 | 1 (0)| 00:00:01 |
      |* 73 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 74 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 598 | 1 (0)| 00:00:01 |
      |* 75 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | 1 (0)| 00:00:01 |
      | 76 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_X | 1 | 134 | 1 (0)| 00:00:01 |
      |* 77 | INDEX RANGE SCAN | S_CONTACT_X_U1 | 1 | | 1 (0)| 00:00:01 |
      |* 78 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
      |* 79 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
      | 80 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 32 | 1 (0)| 00:00:01 |
      |* 81 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 1 | | 1 (0)| 00:00:01 |
      | 82 | TABLE ACCESS BY INDEX ROWID | S_POSTN | 1 | 21 | 1 (0)| 00:00:01 |
      |* 83 | INDEX UNIQUE SCAN | S_POSTN_U2 | 1 | | 1 (0)| 00:00:01 |
      |* 84 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | 1 (0)| 00:00:01 |
      | 85 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 14 | 1 (0)| 00:00:01 |
      |* 86 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      | 87 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 14 | 1 (0)| 00:00:01 |
      |* 88 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      | 89 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 14 | 1 (0)| 00:00:01 |
      |* 90 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      | 91 | TABLE ACCESS BY INDEX ROWID | S_USER | 1 | 14 | 1 (0)| 00:00:01 |
      |* 92 | INDEX UNIQUE SCAN | S_USER_U2 | 1 | | 1 (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      33 - access("T15"."ROW_ID"=:2)
      35 - access("T1"."BU_ID"=:2)
      37 - access("T2"."PAR_ROW_ID"=:2)
      38 - filter((NLS_UPPER("LAST_NAME",'nls_sort=''GENERIC_BASELETTER''') LIKE
      NLS_UPPER(:3,'nls_sort=''GENERIC_BASELETTER''') AND "T33"."PRIV_FLG"='N'))
      39 - access("T33"."ROW_ID"="T1"."CONTACT_ID")
      41 - access("T33"."MED_SPEC_ID"="T5"."ROW_ID")
      43 - access("T33"."CURR_PRI_LST_ID"="T18"."ROW_ID")
      44 - filter("T7"."PARTY_TYPE_CD"<>'Suspect')
      45 - access("T7"."ROW_ID"="T33"."PAR_ROW_ID")
      47 - access("T7"."ROW_ID"="T8"."PAR_ROW_ID")
      49 - access("T7"."ROW_ID"="T12"."PAR_ROW_ID")
      50 - access("T7"."ROW_ID"="T19"."CONTACT_ID")
      51 - filter("T7"."ROW_ID"="T25"."PR_SUBJECT_ID")
      53 - access("T33"."PR_POSTN_ID"="T21"."PAR_ROW_ID")
      55 - access("T7"."ROW_ID"="T23"."PAR_ROW_ID")
      57 - access("T30"."POSTN_ID"=:1 AND "T7"."ROW_ID"="T30"."CON_ID")
      59 - access("T33"."PR_DEPT_OU_ID"="T22"."PAR_ROW_ID")
      61 - access("T33"."PR_DEPT_OU_ID"="T14"."PAR_ROW_ID")
      63 - access("T33"."PR_OU_ADDR_ID"="T11"."ADDR_PER_ID" AND "T33"."PR_DEPT_OU_ID"="T11"."ACCNT_ID")
      65 - access("T33"."PR_PER_ADDR_ID"="T32"."ROW_ID")
      67 - access("T33"."PR_OU_ADDR_ID"="T17"."ROW_ID")
      69 - access("T33"."PR_DEPT_OU_ID"="T3"."PAR_ROW_ID")
      71 - access("T3"."PR_POSTN_ID"="T31"."PAR_ROW_ID")
      73 - access("T33"."PR_DEPT_OU_ID"="T9"."PAR_ROW_ID")
      75 - access("T33"."PR_DEPT_OU_ID"="T13"."PAR_ROW_ID")
      77 - access("T7"."ROW_ID"="T20"."PAR_ROW_ID")
      78 - access("T33"."PR_DEPT_OU_ID"="T4"."ROW_ID")
      79 - access("T33"."PR_SYNC_USER_ID"="T16"."ROW_ID")
      81 - access("T33"."PR_POSTN_ID"="T29"."POSTN_ID" AND "T33"."ROW_ID"="T29"."CON_ID")
      83 - access("T29"."POSTN_ID"="T6"."PAR_ROW_ID")
      84 - access("T29"."POSTN_ID"="T27"."ROW_ID")
      86 - access("T6"."PR_EMP_ID"="T26"."PAR_ROW_ID")
      88 - access("T21"."PR_EMP_ID"="T28"."PAR_ROW_ID")
      90 - access("T31"."PR_EMP_ID"="T24"."PAR_ROW_ID")
      92 - access("T33"."PR_SYNC_USER_ID"="T10"."PAR_ROW_ID")

      Note
      -----
      - dynamic sampling used for this statement (level=5)
      - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
      - SQL profile SYS_SQLPROF_013b617a8f0b005f used for this statement
      Seems like ORACLE estimates all my queries with "1 second" which is below the parallel threshold (5 seconds) and therefore runs in serial? Or am I totally wrong?


      (continued)

      Edited by: Penky on Dec 5, 2012 9:37 AM
        • 1. Re: Please help with parallel query
          Penky
          The funny part:

          when I try to get the plan using EXPLAIN PLAN FOR ....
          it will produce a parallel plan.
          explain plan for <very_long_SQL_statement>
          select * from table(dbms_xplan.display());
          Plan hash value: 4174012900

          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
          | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
          | 0 | SELECT STATEMENT | | 19199 | 77M| | 2760 (2)| 00:00:06 | | | |
          | 1 | PX COORDINATOR | | | | | | | | | |
          | 2 | PX SEND QC (ORDER) | :TQ10015 | 19199 | 77M| | 2760 (2)| 00:00:06 | Q1,15 | P->S | QC (ORDER) |
          | 3 | SORT ORDER BY | | 19199 | 77M| 150M| 2760 (2)| 00:00:06 | Q1,15 | PCWP | |
          | 4 | PX RECEIVE | | 19199 | 77M| | 2757 (2)| 00:00:06 | Q1,15 | PCWP | |
          | 5 | PX SEND RANGE | :TQ10014 | 19199 | 77M| | 2757 (2)| 00:00:06 | Q1,14 | P->P | RANGE |
          |* 6 | HASH JOIN RIGHT OUTER | | 19199 | 77M| | 2757 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 7 | PX RECEIVE | | 1 | 64 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 8 | PX SEND BROADCAST | :TQ10003 | 1 | 64 | | 2 (0)| 00:00:01 | Q1,03 | P->P | BROADCAST |
          | 9 | PX BLOCK ITERATOR | | 1 | 64 | | 2 (0)| 00:00:01 | Q1,03 | PCWC | |
          | 10 | TABLE ACCESS FULL | S_CASE | 1 | 64 | | 2 (0)| 00:00:01 | Q1,03 | PCWP | |
          |* 11 | HASH JOIN RIGHT OUTER | | 19199 | 76M| | 2755 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 12 | PX RECEIVE | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 13 | PX SEND BROADCAST | :TQ10004 | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,04 | P->P | BROADCAST |
          | 14 | PX BLOCK ITERATOR | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,04 | PCWC | |
          | 15 | TABLE ACCESS FULL | S_USER | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,04 | PCWP | |
          | 16 | NESTED LOOPS OUTER | | 19199 | 76M| | 2752 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 17 | NESTED LOOPS OUTER | | 19199 | 73M| | 2680 (2)| 00:00:06 | Q1,14 | PCWP | |
          |* 18 | HASH JOIN RIGHT OUTER | | 19199 | 73M| | 2560 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 19 | PX RECEIVE | | 2034 | 299K| | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 20 | PX SEND BROADCAST | :TQ10005 | 2034 | 299K| | 2 (0)| 00:00:01 | Q1,05 | P->P | BROADCAST |
          | 21 | PX BLOCK ITERATOR | | 2034 | 299K| | 2 (0)| 00:00:01 | Q1,05 | PCWC | |
          | 22 | TABLE ACCESS FULL | S_EMP_PER | 2034 | 299K| | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
          |* 23 | HASH JOIN RIGHT OUTER | | 19199 | 70M| | 2557 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 24 | PX RECEIVE | | 1 | 425 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 25 | PX SEND BROADCAST | :TQ10006 | 1 | 425 | | 2 (0)| 00:00:01 | Q1,06 | P->P | BROADCAST |
          | 26 | PX BLOCK ITERATOR | | 1 | 425 | | 2 (0)| 00:00:01 | Q1,06 | PCWC | |
          | 27 | TABLE ACCESS FULL | S_CONTACT_SS | 1 | 425 | | 2 (0)| 00:00:01 | Q1,06 | PCWP | |
          |* 28 | HASH JOIN RIGHT OUTER | | 19199 | 62M| | 2555 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 29 | PX RECEIVE | | 1 | 255 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 30 | PX SEND BROADCAST | :TQ10007 | 1 | 255 | | 2 (0)| 00:00:01 | Q1,07 | P->P | BROADCAST |
          | 31 | PX BLOCK ITERATOR | | 1 | 255 | | 2 (0)| 00:00:01 | Q1,07 | PCWC | |
          | 32 | TABLE ACCESS FULL | S_CONTACT_LOYX | 1 | 255 | | 2 (0)| 00:00:01 | Q1,07 | PCWP | |
          |* 33 | HASH JOIN RIGHT OUTER | | 19199 | 57M| | 2552 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 34 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
          | 35 | PX RECEIVE | | 1 | 394 | | 1 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 36 | PX SEND BROADCAST | :TQ10000 | 1 | 394 | | 1 (0)| 00:00:01 | | S->P | BROADCAST |
          | 37 | INDEX FULL SCAN | S_DQ_CON_KEY_U1 | 1 | 394 | | 1 (0)| 00:00:01 | | | |
          |* 38 | HASH JOIN RIGHT OUTER | | 19199 | 50M| | 2550 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 39 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
          | 40 | PX RECEIVE | | 1 | 33 | | 1 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 41 | PX SEND BROADCAST | :TQ10001 | 1 | 33 | | 1 (0)| 00:00:01 | | S->P | BROADCAST |
          | 42 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 33 | | 1 (0)| 00:00:01 | | | |
          |* 43 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | | 1 (0)| 00:00:01 | | | |
          |* 44 | HASH JOIN RIGHT OUTER | | 19199 | 49M| | 2548 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 45 | PX RECEIVE | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 46 | PX SEND BROADCAST | :TQ10008 | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,08 | P->P | BROADCAST |
          | 47 | PX BLOCK ITERATOR | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,08 | PCWC | |
          | 48 | TABLE ACCESS FULL | S_USER | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,08 | PCWP | |
          |* 49 | HASH JOIN RIGHT OUTER | | 19199 | 49M| | 2545 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 50 | PX RECEIVE | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 51 | PX SEND BROADCAST | :TQ10009 | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,09 | P->P | BROADCAST |
          | 52 | PX BLOCK ITERATOR | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,09 | PCWC | |
          | 53 | TABLE ACCESS FULL | S_USER | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,09 | PCWP | |
          | 54 | NESTED LOOPS | | 19199 | 49M| | 2542 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 55 | NESTED LOOPS OUTER | | 20247 | 51M| | 2517 (2)| 00:00:06 | Q1,14 | PCWP | |
          | 56 | NESTED LOOPS | | 20247 | 51M| | 2492 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 57 | NESTED LOOPS OUTER | | 20247 | 49M| | 2441 (2)| 00:00:05 | Q1,14 | PCWP | |
          |* 58 | HASH JOIN RIGHT OUTER | | 20247 | 48M| | 2391 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 59 | PX RECEIVE | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 60 | PX SEND BROADCAST | :TQ10010 | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,10 | P->P | BROADCAST |
          | 61 | PX BLOCK ITERATOR | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,10 | PCWC | |
          | 62 | TABLE ACCESS FULL | S_POSTN | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,10 | PCWP | |
          | 63 | NESTED LOOPS OUTER | | 20247 | 48M| | 2389 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 64 | NESTED LOOPS OUTER | | 20247 | 47M| | 2364 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 65 | NESTED LOOPS OUTER | | 20247 | 43M| | 2291 (2)| 00:00:05 | Q1,14 | PCWP | |
          |* 66 | HASH JOIN RIGHT OUTER | | 20247 | 42M| | 2242 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 67 | PX RECEIVE | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 68 | PX SEND BROADCAST | :TQ10011 | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,11 | P->P | BROADCAST |
          | 69 | PX BLOCK ITERATOR | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,11 | PCWC | |
          | 70 | TABLE ACCESS FULL | S_POSTN | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,11 | PCWP | |
          | 71 | NESTED LOOPS OUTER | | 20247 | 41M| | 2239 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 72 | NESTED LOOPS OUTER | | 20247 | 37M| | 2214 (2)| 00:00:05 | Q1,14 | PCWP | |
          |* 73 | HASH JOIN RIGHT OUTER | | 20247 | 26M| | 2165 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 74 | PX RECEIVE | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 75 | PX SEND BROADCAST | :TQ10012 | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,12 | P->P | BROADCAST |
          | 76 | PX BLOCK ITERATOR | | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,12 | PCWC | |
          | 77 | TABLE ACCESS FULL | S_USER | 1570 | 21980 | | 2 (0)| 00:00:01 | Q1,12 | PCWP | |
          | 78 | NESTED LOOPS OUTER | | 20247 | 25M| | 2162 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 79 | NESTED LOOPS | | 20247 | 24M| | 2113 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 80 | NESTED LOOPS OUTER | | 19642 | 23M| | 2040 (2)| 00:00:05 | Q1,14 | PCWP | |
          | 81 | NESTED LOOPS OUTER | | 19642 | 22M| | 1968 (2)| 00:00:04 | Q1,14 | PCWP | |
          | 82 | NESTED LOOPS OUTER | | 19642 | 19M| | 1968 (2)| 00:00:04 | Q1,14 | PCWP | |
          |* 83 | HASH JOIN RIGHT OUTER | | 19642 | 17M| | 1968 (2)| 00:00:04 | Q1,14 | PCWP | |
          | 84 | PX RECEIVE | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 85 | PX SEND BROADCAST | :TQ10013 | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,13 | P->P | BROADCAST |
          | 86 | PX BLOCK ITERATOR | | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,13 | PCWC | |
          | 87 | TABLE ACCESS FULL | S_POSTN | 1004 | 21084 | | 2 (0)| 00:00:01 | Q1,13 | PCWP | |
          | 88 | NESTED LOOPS OUTER | | 19642 | 17M| | 1965 (2)| 00:00:04 | Q1,14 | PCWP | |
          | 89 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
          | 90 | PX RECEIVE | | | | | | | Q1,14 | PCWP | |
          | 91 | PX SEND ROUND-ROBIN | :TQ10002 | | | | | | | S->P | RND-ROBIN |
          | 92 | NESTED LOOPS | | 19642 | 15M| | 1918 (2)| 00:00:04 | | | |
          | 93 | NESTED LOOPS | | 68351 | 3804K| | 532 (1)| 00:00:02 | | | |
          |* 94 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | | 1 (0)| 00:00:01 | | | |
          | 95 | EX ROWID TABLE ACCESS BY IND | S_CONTACT_BU | 68351 | 3070K| | 66 (2)| 00:00:01 | | | |
          |* 96 | INDEX RANGE SCAN | S_CONTACT_BU_M52 | 68351 | | | 0 (0)| 00:00:01 | | | |
          |* 97 | X ROWID TABLE ACCESS BY INDE | S_CONTACT | 1 | 774 | | 0 (0)| 00:00:01 | | | |
          |* 98 | INDEX UNIQUE SCAN | S_CONTACT_P1 | 1 | | | 0 (0)| 00:00:01 | | | |
          | 99 | WID TABLE ACCESS BY INDEX RO | S_ADDR_PER | 1 | 81 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*100 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 101 | D TABLE ACCESS BY INDEX ROWI | S_PRI_LST | 1 | 134 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*102 | INDEX UNIQUE SCAN | S_PRI_LST_P1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 103 | TABLE ACCESS BY INDEX ROWID | S_MED_SPEC | 1 | 134 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*104 | INDEX UNIQUE SCAN | S_MED_SPEC_P1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 105 | TABLE ACCESS BY INDEX ROWID | S_CON_ADDR | 1 | 33 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*106 | INDEX RANGE SCAN | S_CON_ADDR_M51 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 107 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 32 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*108 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 109 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_FNX | 1 | 71 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*110 | INDEX RANGE SCAN | S_ORG_EXT_FNX_U1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 111 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 598 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*112 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 113 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 219 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*114 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 115 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 49 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*116 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 117 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT_X | 1 | 242 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*118 | INDEX RANGE SCAN | S_ORG_EXT_X_U1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*119 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 120 | TABLE ACCESS BY INDEX ROWID | S_ADDR_PER | 1 | 58 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*121 | INDEX UNIQUE SCAN | S_ADDR_PER_P1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*122 | TABLE ACCESS BY INDEX ROWID | S_PARTY | 1 | 109 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*123 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*124 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*125 | INDEX UNIQUE SCAN | S_PARTY_P1 | 1 | 11 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 126 | TABLE ACCESS BY INDEX ROWID | S_POSTN_CON | 1 | 45 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*127 | INDEX RANGE SCAN | S_POSTN_CON_M3 | 4 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          | 128 | TABLE ACCESS BY INDEX ROWID | S_CONTACT_X | 1 | 134 | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          |*129 | INDEX RANGE SCAN | S_CONTACT_X_U1 | 1 | | | 0 (0)| 00:00:01 | Q1,14 | PCWP | |
          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------

          Predicate Information (identified by operation id):
          ---------------------------------------------------

          6 - access("T7"."ROW_ID"="T25"."PR_SUBJECT_ID"(+))
          11 - access("T31"."PR_EMP_ID"="T24"."PAR_ROW_ID"(+))
          18 - access("T7"."ROW_ID"="T23"."PAR_ROW_ID"(+))
          23 - access("T7"."ROW_ID"="T8"."PAR_ROW_ID"(+))
          28 - access("T7"."ROW_ID"="T12"."PAR_ROW_ID"(+))
          33 - access("T7"."ROW_ID"="T19"."CONTACT_ID"(+))
          38 - access("T1"."BU_ID"="T2"."PAR_ROW_ID"(+))
          43 - access("T2"."PAR_ROW_ID"(+)=:2)
          44 - access("T6"."PR_EMP_ID"="T26"."PAR_ROW_ID"(+))
          49 - access("T33"."PR_SYNC_USER_ID"="T10"."PAR_ROW_ID"(+))
          58 - access("T3"."PR_POSTN_ID"="T31"."PAR_ROW_ID"(+))
          66 - access("T29"."POSTN_ID"="T6"."PAR_ROW_ID"(+))
          73 - access("T21"."PR_EMP_ID"="T28"."PAR_ROW_ID"(+))
          83 - access("T33"."PR_POSTN_ID"="T21"."PAR_ROW_ID"(+))
          94 - access("T15"."ROW_ID"=:2)
          96 - access("T1"."BU_ID"=:2)
          97 - filter(NLS_UPPER("LAST_NAME",'nls_sort=''GENERIC_BASELETTER''') LIKE NLS_UPPER(:3,'nls_sort=''GENERIC_BASELETTER''') AND "T33"."PRIV_FLG"='N')
          98 - access("T33"."ROW_ID"="T1"."CONTACT_ID")
          100 - access("T33"."PR_PER_ADDR_ID"="T32"."ROW_ID"(+))
          102 - access("T33"."CURR_PRI_LST_ID"="T18"."ROW_ID"(+))
          104 - access("T33"."MED_SPEC_ID"="T5"."ROW_ID"(+))
          106 - access("T33"."PR_OU_ADDR_ID"="T11"."ADDR_PER_ID"(+) AND "T33"."PR_DEPT_OU_ID"="T11"."ACCNT_ID"(+))
          108 - access("T33"."PR_POSTN_ID"="T29"."POSTN_ID" AND "T33"."ROW_ID"="T29"."CON_ID")
          110 - access("T33"."PR_DEPT_OU_ID"="T22"."PAR_ROW_ID"(+))
          112 - access("T33"."PR_DEPT_OU_ID"="T13"."PAR_ROW_ID"(+))
          114 - access("T33"."PR_DEPT_OU_ID"="T9"."PAR_ROW_ID"(+))
          116 - access("T33"."PR_DEPT_OU_ID"="T3"."PAR_ROW_ID"(+))
          118 - access("T33"."PR_DEPT_OU_ID"="T14"."PAR_ROW_ID"(+))
          119 - access("T33"."PR_DEPT_OU_ID"="T4"."ROW_ID"(+))
          121 - access("T33"."PR_OU_ADDR_ID"="T17"."ROW_ID"(+))
          122 - filter("T7"."PARTY_TYPE_CD"<>'Suspect')
          123 - access("T7"."ROW_ID"="T33"."PAR_ROW_ID")
          124 - access("T33"."PR_SYNC_USER_ID"="T16"."ROW_ID"(+))
          125 - access("T29"."POSTN_ID"="T27"."ROW_ID")
          127 - access("T30"."POSTN_ID"(+)=:1 AND "T7"."ROW_ID"="T30"."CON_ID"(+))
          129 - access("T7"."ROW_ID"="T20"."PAR_ROW_ID"(+))

          Note
          -----
          - dynamic sampling used for this statement (level=5)
          - automatic DOP: Computed Degree of Parallelism is 9
          - SQL profile "SYS_SQLPROF_013b617a8f0b005f" used for this statement
          conclusion:
          - queries are all run in serial, although EXPLAIN PLAN FOR would (sometimes) produce a parallel plan.
          - execution time estimations seem to be wrong and often pass below parallel threshold, although execution in "real life" would tike much longer than estimated

          Your comments are highly appreciated!

          Penky

          Edited by: Penky on Dec 5, 2012 9:40 AM
          • 2. Re: Please help with parallel query
            Marcus Rangel
            It seems to me that the optimizer does not see the parallelism as a good choice in this particular query. What happens if you do "select * from some_big_table" ? Does it choose parallel execution ?
            • 3. Re: Please help with parallel query
              Penky
              OK, let's take siebel.s_accnt_postn:
              SQL> select count(1) from siebel.s_accnt_postn;

              COUNT(1)
              --
              7928302
              SQL> explain plan for select * from siebel.s_accnt_postn;

              Explained.

              SQL> select * from table (dbms_xplan.display());

              PLAN_TABLE_OUTPUT
              -------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------

              Plan hash value: 4072445022

              -----------------------------------------------------------------------------------
              | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
              -----------------------------------------------------------------------------------
              | 0 | SELECT STATEMENT | | 7915K| 1343M| 34792 (32)| 00:00:01 |
              | 1 | TABLE ACCESS FULL| S_ACCNT_POSTN | 7915K| 1343M| 34792 (32)| 00:00:01 |
              -----------------------------------------------------------------------------------

              Note
              -----

              PLAN_TABLE_OUTPUT
              -------------------------------------------------------------------------------------
              -------------------------------------------------------------------------------------

              - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
              I really doubt the FTS will take place in 1 second .... !?
              • 4. Re: Please help with parallel query
                Penky
                What could make the optimizer assume that it would take 1 second to do a Full Table Scan on a table with 1,3GB of data?
                Of course it will take much longer than that and parallel query would definately help in this case.
                • 5. Re: Please help with parallel query
                  Dom Brooks
                  SQL profile SYS_SQLPROF_013b617a8f0b005f used for this statement
                  You've got a SQL profile in place for this statement.
                  This will be adjusting all the cardinality estimates and may be one factor as to why the estimates come in below the parallel threshold.
                  • 6. Re: Please help with parallel query
                    Penky
                    OK I have another example, this time with no SQL profile. It's a very simple query which will produce a Full Table Scan. Query would benefit very much from parallel query, but ORACLE just doesn't do it.


                    the query:
                    select * from siebel.s_evt_act where todo_cd is null and owner_login = 'XXX';
                    EXPLAIN PLAN:
                    select * from table(dbms_xplan.display());
                    Plan hash value: 3995919838
                     
                    -------------------------------------------------------------------------------
                    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                    -------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |           |    14 |  8638 |   114K (27)| 00:00:01 |
                    |*  1 |  TABLE ACCESS FULL| S_EVT_ACT |    14 |  8638 |   114K (27)| 00:00:01 |
                    -------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                     
                       1 - filter("TODO_CD" IS NULL AND "OWNER_LOGIN"='XXX')
                     
                    Note
                    -----
                       - automatic DOP: Computed Degree of Parallelism is 1
                    this query actually takes *70 seconds* to complete.


                    Since our IO is quite fast, I know it will run very fast in parallel. So when forcing parallel query by doing:
                    select /*+PARALLEL(32)*/ * from siebel.s_evt_act where todo_cd is null and owner_login = 'XXX';
                    it will look like this
                    Plan hash value: 1182001768
                     
                    ---------------------------------------------------------------------------------------------------------------
                    | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
                    ---------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT     |           |    14 |  8638 | 14096   (7)| 00:00:01 |        |      |            |
                    |   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |            |
                    |   2 |   PX SEND QC (RANDOM)| :TQ10000  |    14 |  8638 | 14096   (7)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
                    |   3 |    PX BLOCK ITERATOR |           |    14 |  8638 | 14096   (7)| 00:00:01 |  Q1,00 | PCWC |            |
                    |*  4 |     TABLE ACCESS FULL| S_EVT_ACT |    14 |  8638 | 14096   (7)| 00:00:01 |  Q1,00 | PCWP |            |
                    ---------------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                     
                       4 - filter("TODO_CD" IS NULL AND "OWNER_LOGIN"='XXX')
                     
                    Note
                    -----
                       - Degree of Parallelism is 32 because of hint
                    this query completes in *<10 seconds*.


                    I would really be interested to know why the query does not run in parallel automatically?

                    Edited by: Penky on Dec 11, 2012 5:08 PM
                    • 7. Re: Please help with parallel query
                      Iordan Iotzov
                      Well, you've got:
                      Max MB per Second 989
                      Therefore, scanning 1.3G should be around (slightly more than) one second.

                      How much of time was spent waiting on IO ?

                      Iordan Iotzov
                      http://iiotzov.wordpress.com/

                      Edited by: Iordan Iotzov on Dec 11, 2012 8:51 AM
                      • 8. Re: Please help with parallel query
                        Penky
                        Hi Iordan!

                        Thanks for your answer!
                        The 989 MB/s can only be achieved with several concurrent processes accessing the file system at once. One single process will never reach that speed. Therefore, the query in serial takes 70 seconds, and in parallel it takes around 4 or 5 seconds. The asumption of Optimizer that a serial access will take 1 second is wrong.

                        Looking at Enterprise Manager:

                        wait activity of serial query:
                        70% db file sequential read
                        30% db file scattered read
                        average throughput was 13,71 MB/s


                        wait activity of parallel query (32):
                        77% db file sequential read
                        23% db file scattered read
                        average throughput was 170 MB/s

                        It's strange.
                        • 9. Re: Please help with parallel query
                          Iordan Iotzov
                          In 112G2, one might expect the IO wait for this type of operation to be mostly “direct path read”. 70% “db file sequential read” in a full table is rather unusual.

                          How much is spent on CPU? A there any other wait events that consume significant time?
                          How is the tablespace organized?
                          How is the table organized – partitions, segments, extents, etc.


                          Iordan Iotzov
                          http://iiotzov.wordpress.com/
                          • 10. Re: Please help with parallel query
                            6363
                            Penky wrote:
                            OK I have another example, this time with no SQL profile. It's a very simple query which will produce a Full Table Scan. Query would benefit very much from parallel query, but ORACLE just doesn't do it.
                            Since the optimizer expects only 14 rows to be returned it is making the right choice in not executing as parallel in this example.
                            the query:
                            select * from siebel.s_evt_act where todo_cd is null and owner_login = 'XXX';
                            EXPLAIN PLAN:
                            select * from table(dbms_xplan.display());
                            Plan hash value: 3995919838
                            
                            -------------------------------------------------------------------------------
                            | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                            -------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT  |           |    14 |  8638 |   114K (27)| 00:00:01 |
                            |*  1 |  TABLE ACCESS FULL| S_EVT_ACT |    14 |  8638 |   114K (27)| 00:00:01 |
                            -------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                            1 - filter("TODO_CD" IS NULL AND "OWNER_LOGIN"='XXX')
                            
                            Note
                            -----
                            - automatic DOP: Computed Degree of Parallelism is 1
                            • 11. Re: Please help with parallel query
                              Penky
                              Iordan Iotzov wrote:
                              How much is spent on CPU? A there any other wait events that consume significant time?
                              How is the tablespace organized?
                              How is the table organized – partitions, segments, extents, etc.
                              Hi and thanks for the response,

                              I am looking at the statistics for this SQL in Enterprise Manager (SQL Monitoring).
                              This time the parallel version (32) took 12 seconds.
                              Wait activity is again nearly the same (80%/20%). Database time spent was 6 minutes. Divided by 32, it results in ~12 seconds execution time.
                              Database time statistics:
                              77% user I/O
                              17% CPU
                              5,9% Other

                              Don't know exactly what else you need to know. The tablespace consists of 6 files, each 10GB. The indexes are stored in another tablespace, with 7 files, each 10GB.
                              All the datafiles are stored on "one drive" which is a SSD RAID 10 in the background. Could it be the reason? Will ORACLE not run queries in parallel, if the datafiles are not striped over different drives?

                              3360 wrote:
                              Since the optimizer expects only 14 rows to be returned it is making the right choice in not executing as parallel in this example.
                              actually the result is 0 rows. But not matter how many rows it returns, scanning a table with 5385269 rows in total, not using an index, should always benefit from being run in parallel, shouldn't it?
                              • 12. Re: Please help with parallel query
                                Iordan Iotzov
                                The Oracle makes too many single reads and too few bulk reads for this type of operation (full table scan). It might be that CBO (almost) correctly estimated the time to scan a 1.3B table, but this particular table is significantly more “fragmented”, i.e. spread across large number of small chunks, than Oracle expected.
                                These queries can help confirm or reject this theory:
                                select tablespace_name , extent_management, allocation_type ,  segment_space_management 
                                from dba_tablespaces
                                where tablespace_name = '<...>'
                                
                                
                                select * from dba_segments
                                where segment_name = '<..>'
                                
                                select * from dba_extents
                                where segment_name = '<..>'
                                Iordan Iotzov
                                http://iiotzov.wordpress.com/
                                • 13. Re: Please help with parallel query
                                  Penky
                                  SQL> select tablespace_name , extent_management, allocation_type ,  segment_space_management
                                    2  from dba_tablespaces
                                    3  where tablespace_name in ('SBL_DATA','SBL_INDEX');
                                   
                                  TABLESPACE_NAME                EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT
                                  ------------------------------ ----------------- --------------- ------------------------
                                  SBL_DATA                       LOCAL             SYSTEM          AUTO
                                  SBL_INDEX                      LOCAL             SYSTEM          AUTO
                                  
                                  
                                  select * from dba_segments
                                    2  where owner = 'SIEBEL' and segment_name = 'S_EVT_ACT';
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       SEGMENT_SUBTYPE TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS   MAX_SIZE RETENTION MINRETENTION PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ --------------- ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ---------- --------- ------------ ------------ ---------- --------------- ------------ ----------- ----------- ----------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              ASSM            SBL_DATA                                14       171809 5367660544     655232         88      781189120     1048576           1  2147483645 2147483645                                                                          14 DEFAULT     DEFAULT     DEFAULT
                                  
                                  
                                  
                                  SQL> select * from dba_extents
                                    2  where owner = 'SIEBEL' and segment_name = 'S_EVT_ACT';
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                0         14     171776   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                1          9      90752   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                2         11      70144   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                3         12      51200   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                4         13      37760   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                5         14     179968   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                6          9      98944   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                7         11      78336   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                8         12      59392   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                                9         13      45952   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               10         14     188160   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               11          9     107136    8388608       1024            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               12         11      86528    8388608       1024           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               13         12      67584    8388608       1024           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               14         13      54144    8388608       1024           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               15         14     196352    8388608       1024           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               16          9     108160    1048576        128            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               17         11     366080   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               18         12     340608   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               19         13     329472   67108864       8192           13
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               20         14     563328   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               21          9     441856   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               22         11     398848   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               23         12     373376   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               24         13     362240   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               25         14     604288   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               26          9     474624   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               27         11     439808   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               28         12     406144   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               29         13     378624   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               30         14     620672   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               31          9     491008   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               32         11     456192   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               33         12     414336   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               34         13     386816   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               35         14     628864   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               36          9     499200   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               37         11     464384   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               38         12     422528   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               39         13     395008   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               40         14     637056   67108864       8192           14
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               41          9     507392    5242880        640            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               42         11     472576   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               43         12     430720   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               44         13     403200   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               45         14     645248   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               46          9     508032   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               47         11     480768   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               48         12     438912   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               49         13     411392   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               50         14     653440   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               51          9     516224   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               52         11        128   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               53         12       8320   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               54         13     101504   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               55         14       8320   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               56          9       8320   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               57         11       8320   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               58         12     120320   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               59         13     109696   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               60         14      16512   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               61          9     207488   67108864       8192            9
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               62         11     157568   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               63         12     128512   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               64         13     117888   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               65         14      24704   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               66          9     215680   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               67         11     165760   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               68         12     136704   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               69         13     429184   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               70         14     345856   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               71          9      16768   42991616       5248            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               72         11     182144   67108864       8192           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               73         12     463744   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               74         13     437376   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               75         14     354048   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               76          9     240256    7340032        896            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               77         11      16768   34603008       4224           11
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               78         12     471936   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               79         13     445568   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               80         14     362240   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               81          9     690048   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               82         11     776576   67108864       8192           11
                                   
                                  OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
                                  ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               83         12     896512   67108864       8192           12
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               84         13     612224   67108864       8192           13
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               85         14     881024   67108864       8192           14
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               86          9     948608   67108864       8192            9
                                  SIEBEL                         S_EVT_ACT                                                                                                       TABLE              SBL_DATA                               87         11    1101952   67108864       8192           11
                                   
                                  88 rows selected
                                   
                                  
                                  
                                   
                                  anything unusual here?

                                  Edited by: Penky on Dec 14, 2012 8:39 AM
                                  • 14. Re: Please help with parallel query
                                    905562
                                    Your time estimates in the serial queries are all under parallel_min_time_threshold (yours is 5). To check I'm right (I think I am :) ). Issue this
                                    alter session set parallel_min_time_threshold = 0;
                                    And check your plans etc again.


                                    Oracle, as you mentioned won't use PX if it is under this - question is, why is it getting that so wrong in your instance?

                                    Edited by: mrk on Dec 14, 2012 7:57 AM
                                    1 2 3 Previous Next