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