课程第36次（2017-07-28星期五）

botang

第三阶段New Feature/OCM Exam Preparation（31-36）

1. explain plan for
   
2. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
   
3. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
   
4. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
   
5. AND c.cust_state_province = 'FL'
   
6. AND ch.channel_desc = 'Direct Sales'
   
7. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
   
8. t.calendar_quarter_desc;

复制代码

1. [oracle@station17 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 28 20:48:34 2017
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysdba
   
8. Connected.
   
9. SQL> show parameter parallel_
   
10. 
    
11. NAME                                     TYPE         VALUE
    
12. ------------------------------------ ----------- ------------------------------
    
13. fast_start_parallel_rollback             string         LOW
    
14. parallel_adaptive_multi_user             boolean         TRUE
    
15. parallel_automatic_tuning             boolean         FALSE
    
16. parallel_degree_limit                     string         CPU
    
17. parallel_degree_policy                     string         MANUAL
    
18. parallel_execution_message_size      integer         16384
    
19. parallel_force_local                     boolean         FALSE
    
20. parallel_instance_group              string
    
21. parallel_io_cap_enabled              boolean         FALSE
    
22. parallel_max_servers                     integer         80
    
23. parallel_min_percent                     integer         0
    
24. 
    
25. NAME                                     TYPE         VALUE
    
26. ------------------------------------ ----------- ------------------------------
    
27. parallel_min_servers                     integer         0
    
28. parallel_min_time_threshold             string         AUTO
    
29. parallel_server                      boolean         FALSE
    
30. parallel_server_instances             integer         1
    
31. parallel_servers_target              integer         32
    
32. parallel_threads_per_cpu             integer         2
    
33. recovery_parallelism                     integer         0
    
34. SQL> conn sh/oracle_4U
    
35. Connected.
    
36. explain plan for
    
37. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
    
38. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
    
39. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
    
40. AND c.cust_state_province = 'FL'
    
41. AND ch.channel_desc = 'Direct Sales'
    
42. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
    
43.   8  t.calendar_quarter_desc;
    
44. 
    
45. Explained.
    
46. 
    
47. SQL> set linesize 2000      
    
48. SQL> @?/rdbms/admin/utlxplp
    
49. 
    
50. PLAN_TABLE_OUTPUT
    
51. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
52. Plan hash value: 1865285285
    
53. 
    
54. ------------------------------------------------------------------------------------------------------------
    
55. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |
    
56. ------------------------------------------------------------------------------------------------------------
    
57. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
    
58. |   1 |  HASH GROUP BY                       |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
    
59. |*  2 |   HASH JOIN                       |           |  2337 |   173K|   919   (2)| 00:00:12 |           |           |
    
60. |   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
    
61. |*  4 |     TABLE ACCESS FULL               | TIMES           |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
    
62. |*  5 |    HASH JOIN                       |           | 12456 |   729K|   900   (2)| 00:00:11 |           |           |
    
63. 
    
64. PLAN_TABLE_OUTPUT
    
65. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
66. |   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |           |           |
    
67. |*  7 |      TABLE ACCESS FULL               | CHANNELS  |         1 |        13 |         3   (0)| 00:00:01 |           |           |
    
68. |   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
    
69. |*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
    
70. |  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
    
71. |  11 |      TABLE ACCESS FULL               | SALES           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
    
72. ------------------------------------------------------------------------------------------------------------
    
73. 
    
74. Predicate Information (identified by operation id):
    
75. ---------------------------------------------------
    
76. 
    
77. 
    
78. PLAN_TABLE_OUTPUT
    
79. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
80.    2 - access("S"."TIME_ID"="T"."TIME_ID")
    
81.    4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
    
82.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
    
83.    5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
    
84.    7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
    
85.    9 - filter("C"."CUST_STATE_PROVINCE"='FL')
    
86. 
    
87. 28 rows selected.
    
88. 
    
89. SQL>
    

复制代码

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> show parameter parallel_degree
   
4. 
   
5. NAME                                     TYPE         VALUE
   
6. ------------------------------------ ----------- ------------------------------
   
7. parallel_degree_limit                     string         CPU
   
8. parallel_degree_policy                     string         MANUAL
   
9. SQL> alter system set parallel_degree_policy=limited;
   
10. 
    
11. System altered.
    
12. 
    
13. SQL> conn sh/oracle_4U
    
14. Connected.
    
15.     explain plan for
    
16.     SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
    
17.     FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
    
18.     WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
    
19.     AND c.cust_state_province = 'FL'
    
20.     AND ch.channel_desc = 'Direct Sales'
    
21.     AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
    
22.   8      t.calendar_quarter_desc;
    
23. 
    
24. Explained.
    
25. 
    
26. SQL> @?/rdbms/admin/utlxplp
    
27. 
    
28. PLAN_TABLE_OUTPUT
    
29. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
30. Plan hash value: 1865285285
    
31. 
    
32. ------------------------------------------------------------------------------------------------------------
    
33. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |
    
34. ------------------------------------------------------------------------------------------------------------
    
35. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
    
36. |   1 |  HASH GROUP BY                       |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
    
37. |*  2 |   HASH JOIN                       |           |  2337 |   173K|   919   (2)| 00:00:12 |           |           |
    
38. |   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
    
39. |*  4 |     TABLE ACCESS FULL               | TIMES           |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
    
40. |*  5 |    HASH JOIN                       |           | 12456 |   729K|   900   (2)| 00:00:11 |           |           |
    
41. 
    
42. PLAN_TABLE_OUTPUT
    
43. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
44. |   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |           |           |
    
45. |*  7 |      TABLE ACCESS FULL               | CHANNELS  |         1 |        13 |         3   (0)| 00:00:01 |           |           |
    
46. |   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
    
47. |*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
    
48. |  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
    
49. |  11 |      TABLE ACCESS FULL               | SALES           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
    
50. ------------------------------------------------------------------------------------------------------------
    
51. 
    
52. Predicate Information (identified by operation id):
    
53. ---------------------------------------------------
    
54. 
    
55. 
    
56. PLAN_TABLE_OUTPUT
    
57. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
58.    2 - access("S"."TIME_ID"="T"."TIME_ID")
    
59.    4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
    
60.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
    
61.    5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
    
62.    7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
    
63.    9 - filter("C"."CUST_STATE_PROVINCE"='FL')
    
64. 
    
65. 28 rows selected.
    
66. 
    
67. SQL>
    

复制代码

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> show parameter parallel_degree
   
4. 
   
5. NAME                                     TYPE         VALUE
   
6. ------------------------------------ ----------- ------------------------------
   
7. parallel_degree_limit                     string         CPU
   
8. parallel_degree_policy                     string         LIMITED
   
9. SQL> alter system set parallel_degree_policy=auto;
   
10. 
    
11. System altered.
    
12. 
    
13. SQL> conn sh/oracle_4U
    
14. Connected.
    
15. explain plan for
    
16. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
    
17. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
    
18. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
    
19. AND c.cust_state_province = 'FL'
    
20. AND ch.channel_desc = 'Direct Sales'
    
21. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
    
22.   8  t.calendar_quarter_desc;
    
23. 
    
24. Explained.
    
25. 
    
26. SQL> @?/rdbms/admin/utlxplp
    
27. 
    
28. PLAN_TABLE_OUTPUT
    
29. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
30. Plan hash value: 3013537432
    
31. 
    
32. -----------------------------------------------------------------------------------------------------------------------------------------
    
33. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |        TQ  |IN-OUT| PQ Distrib |
    
34. -----------------------------------------------------------------------------------------------------------------------------------------
    
35. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   512   (2)| 00:00:07 |           |           |            |           |                |
    
36. |   1 |  PX COORDINATOR                |           |           |           |                |           |           |           |            |           |                |
    
37. |   2 |   PX SEND QC (RANDOM)               | :TQ10004  |   607 | 46132 |   512   (2)| 00:00:07 |           |           |  Q1,04 | P->S | QC (RAND)|
    
38. |   3 |    HASH GROUP BY               |           |   607 | 46132 |   512   (2)| 00:00:07 |           |           |  Q1,04 | PCWP |                |
    
39. |   4 |     PX RECEIVE                       |           |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,04 | PCWP |                |
    
40. |   5 |      PX SEND HASH               | :TQ10003  |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,03 | P->P | HASH        |
    
41. 
    
42. PLAN_TABLE_OUTPUT
    
43. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
44. |*  6 |       HASH JOIN                |           |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,03 | PCWP |                |
    
45. |   7 |        PX RECEIVE               |           |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
    
46. |   8 |         PX SEND BROADCAST      | :TQ10000  |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | P->P | BROADCAST|
    
47. |   9 |          PX BLOCK ITERATOR     |           |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | PCWC |                |
    
48. |* 10 |           TABLE ACCESS FULL    | CHANNELS  |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | PCWP |                |
    
49. |* 11 |        HASH JOIN               |           |  9347 |   575K|   509   (2)| 00:00:07 |           |           |  Q1,03 | PCWP |                |
    
50. |  12 |         PART JOIN FILTER CREATE| :BF0001   |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
    
51. |  13 |          PX RECEIVE               |           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
    
52. |  14 |           PX SEND BROADCAST    | :TQ10001  |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | P->P | BROADCAST|
    
53. |  15 |            PX BLOCK ITERATOR   |           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | PCWC |                |
    
54. |* 16 |             TABLE ACCESS FULL  | TIMES           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | PCWP |                |
    
55. 
    
56. PLAN_TABLE_OUTPUT
    
57. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
58. |* 17 |         HASH JOIN               |           | 49822 |  2286K|   498   (2)| 00:00:06 |           |           |  Q1,03 | PCWP |                |
    
59. |  18 |          PX RECEIVE               |           |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,03 | PCWP |                |
    
60. |  19 |           PX SEND BROADCAST    | :TQ10002  |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | P->P | BROADCAST|
    
61. |  20 |            PX BLOCK ITERATOR   |           |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | PCWC |                |
    
62. |* 21 |             TABLE ACCESS FULL  | CUSTOMERS |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | PCWP |                |
    
63. |  22 |          PX BLOCK ITERATOR     |           |   918K|        18M|   271   (2)| 00:00:04 |:BF0001|:BF0001|  Q1,03 | PCWC |                |
    
64. |* 23 |           TABLE ACCESS FULL    | SALES           |   918K|        18M|   271   (2)| 00:00:04 |:BF0001|:BF0001|  Q1,03 | PCWP |                |
    
65. -----------------------------------------------------------------------------------------------------------------------------------------
    
66. 
    
67. Predicate Information (identified by operation id):
    
68. ---------------------------------------------------
    
69. 
    
70. PLAN_TABLE_OUTPUT
    
71. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
72. 
    
73.    6 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
    
74.   10 - filter("CH"."CHANNEL_DESC"='Direct Sales')
    
75.   11 - access("S"."TIME_ID"="T"."TIME_ID")
    
76.   16 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
    
77.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
    
78.   17 - access("S"."CUST_ID"="C"."CUST_ID")
    
79.   21 - filter("C"."CUST_STATE_PROVINCE"='FL')
    
80.   23 - filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"S"."CUST_ID"),SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID")))
    
81. 
    
82. Note
    
83. 
    
84. PLAN_TABLE_OUTPUT
    
85. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
86. -----
    
87.    <font color="Red">- automatic DOP: Computed Degree of Parallelism is 2</font>
    
88. 
    
89. 46 rows selected.
    
90. 
    
91. SQL>
    

复制代码

xiaoyu

唐老师，PARALLEL_DEGREE_POLICY设置为AUTO时 ，是不是涉及到的有并行度的对象都会被缓存到database  buffer cache 里？ 所以此时如果有index的时候。整个index也会被缓存至database  buffer cache 是吗？   那如果真的如上所述，设置为PARALLEL_DEGREE_POLICY设置为AUTO 不就是意味着这个参数其实不是一个很可控的参数？

botang

xiaoyu 发表于 2017-8-7 21:38
唐老师，PARALLEL_DEGREE_POLICY设置为AUTO时 ，是不是涉及到的有并行度的对象都会被缓存到database  buffe ...

PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.Values:

    MANUAL: Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
    LIMITED: Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the DEFAULT degree of parallelism will retain the MANUAL behavior.
    AUTO: Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.


而in-memory parallel execution指RAC集群，不是把索引拷贝在buffer cache中，那个叫fast full index scan。
New automatic parallelization capabilities enable the instancewide buffer cache to store and reuse the clusterwide data cache for a single parallel operation.
Minimizes or even completely eliminates the physical I/O for a parallel operation.
Parallel Data Cache enables the clusterwide usage of the buffer cache for parallel operations, scaling out the available memory for data caching with the number of nodes in a cluster.
Parallel Data Cache optimizes the physical I/O requirements, speeding up the processing of large parallel operations.