Bo's Oracle Station

查看: 1703|回复: 2

课程第36次(2017-07-28星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-7-28 20:56:25 | 显示全部楼层 |阅读模式
第三阶段New Feature/OCM Exam Preparation31-36

Screenshot.png

  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. SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 28 20:48:34 2017

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> show parameter parallel_

  7. NAME                                     TYPE         VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. fast_start_parallel_rollback             string         LOW
  10. parallel_adaptive_multi_user             boolean         TRUE
  11. parallel_automatic_tuning             boolean         FALSE
  12. parallel_degree_limit                     string         CPU
  13. parallel_degree_policy                     string         MANUAL
  14. parallel_execution_message_size      integer         16384
  15. parallel_force_local                     boolean         FALSE
  16. parallel_instance_group              string
  17. parallel_io_cap_enabled              boolean         FALSE
  18. parallel_max_servers                     integer         80
  19. parallel_min_percent                     integer         0

  20. NAME                                     TYPE         VALUE
  21. ------------------------------------ ----------- ------------------------------
  22. parallel_min_servers                     integer         0
  23. parallel_min_time_threshold             string         AUTO
  24. parallel_server                      boolean         FALSE
  25. parallel_server_instances             integer         1
  26. parallel_servers_target              integer         32
  27. parallel_threads_per_cpu             integer         2
  28. recovery_parallelism                     integer         0
  29. SQL> conn sh/oracle_4U
  30. Connected.
  31. explain plan for
  32. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  33. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  34. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
  35. AND c.cust_state_province = 'FL'
  36. AND ch.channel_desc = 'Direct Sales'
  37. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
  38.   8  t.calendar_quarter_desc;

  39. Explained.

  40. SQL> set linesize 2000      
  41. SQL> @?/rdbms/admin/utlxplp

  42. PLAN_TABLE_OUTPUT
  43. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  44. Plan hash value: 1865285285

  45. ------------------------------------------------------------------------------------------------------------
  46. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |
  47. ------------------------------------------------------------------------------------------------------------
  48. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
  49. |   1 |  HASH GROUP BY                       |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
  50. |*  2 |   HASH JOIN                       |           |  2337 |   173K|   919   (2)| 00:00:12 |           |           |
  51. |   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
  52. |*  4 |     TABLE ACCESS FULL               | TIMES           |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
  53. |*  5 |    HASH JOIN                       |           | 12456 |   729K|   900   (2)| 00:00:11 |           |           |

  54. PLAN_TABLE_OUTPUT
  55. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  56. |   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |           |           |
  57. |*  7 |      TABLE ACCESS FULL               | CHANNELS  |         1 |        13 |         3   (0)| 00:00:01 |           |           |
  58. |   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
  59. |*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
  60. |  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
  61. |  11 |      TABLE ACCESS FULL               | SALES           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
  62. ------------------------------------------------------------------------------------------------------------

  63. Predicate Information (identified by operation id):
  64. ---------------------------------------------------


  65. PLAN_TABLE_OUTPUT
  66. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  67.    2 - access("S"."TIME_ID"="T"."TIME_ID")
  68.    4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
  69.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
  70.    5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  71.    7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  72.    9 - filter("C"."CUST_STATE_PROVINCE"='FL')

  73. 28 rows selected.

  74. SQL>
复制代码
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> show parameter parallel_degree

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. parallel_degree_limit                     string         CPU
  7. parallel_degree_policy                     string         MANUAL
  8. SQL> alter system set parallel_degree_policy=limited;

  9. System altered.

  10. SQL> conn sh/oracle_4U
  11. Connected.
  12.     explain plan for
  13.     SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  14.     FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  15.     WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
  16.     AND c.cust_state_province = 'FL'
  17.     AND ch.channel_desc = 'Direct Sales'
  18.     AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
  19.   8      t.calendar_quarter_desc;

  20. Explained.

  21. SQL> @?/rdbms/admin/utlxplp

  22. PLAN_TABLE_OUTPUT
  23. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  24. Plan hash value: 1865285285

  25. ------------------------------------------------------------------------------------------------------------
  26. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |
  27. ------------------------------------------------------------------------------------------------------------
  28. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
  29. |   1 |  HASH GROUP BY                       |           |   607 | 46132 |   920   (2)| 00:00:12 |           |           |
  30. |*  2 |   HASH JOIN                       |           |  2337 |   173K|   919   (2)| 00:00:12 |           |           |
  31. |   3 |    PART JOIN FILTER CREATE     | :BF0000   |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
  32. |*  4 |     TABLE ACCESS FULL               | TIMES           |   274 |  4384 |        18   (0)| 00:00:01 |           |           |
  33. |*  5 |    HASH JOIN                       |           | 12456 |   729K|   900   (2)| 00:00:11 |           |           |

  34. PLAN_TABLE_OUTPUT
  35. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  36. |   6 |     MERGE JOIN CARTESIAN       |           |   383 | 14937 |   408   (1)| 00:00:05 |           |           |
  37. |*  7 |      TABLE ACCESS FULL               | CHANNELS  |         1 |        13 |         3   (0)| 00:00:01 |           |           |
  38. |   8 |      BUFFER SORT               |           |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
  39. |*  9 |       TABLE ACCESS FULL        | CUSTOMERS |   383 |  9958 |   405   (1)| 00:00:05 |           |           |
  40. |  10 |     PARTITION RANGE JOIN-FILTER|           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
  41. |  11 |      TABLE ACCESS FULL               | SALES           |   918K|        18M|   489   (2)| 00:00:06 |:BF0000|:BF0000|
  42. ------------------------------------------------------------------------------------------------------------

  43. Predicate Information (identified by operation id):
  44. ---------------------------------------------------


  45. PLAN_TABLE_OUTPUT
  46. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  47.    2 - access("S"."TIME_ID"="T"."TIME_ID")
  48.    4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
  49.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
  50.    5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  51.    7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  52.    9 - filter("C"."CUST_STATE_PROVINCE"='FL')

  53. 28 rows selected.

  54. SQL>
复制代码
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> show parameter parallel_degree

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. parallel_degree_limit                     string         CPU
  7. parallel_degree_policy                     string         LIMITED
  8. SQL> alter system set parallel_degree_policy=auto;

  9. System altered.

  10. SQL> conn sh/oracle_4U
  11. Connected.
  12. explain plan for
  13. SELECT c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
  14. FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
  15. WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id
  16. AND c.cust_state_province = 'FL'
  17. AND ch.channel_desc = 'Direct Sales'
  18. AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12') GROUP BY c.cust_city,
  19.   8  t.calendar_quarter_desc;

  20. Explained.

  21. SQL> @?/rdbms/admin/utlxplp

  22. PLAN_TABLE_OUTPUT
  23. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  24. Plan hash value: 3013537432

  25. -----------------------------------------------------------------------------------------------------------------------------------------
  26. | Id  | Operation                       | Name           | Rows  | Bytes | Cost (%CPU)| Time           | Pstart| Pstop |        TQ  |IN-OUT| PQ Distrib |
  27. -----------------------------------------------------------------------------------------------------------------------------------------
  28. |   0 | SELECT STATEMENT               |           |   607 | 46132 |   512   (2)| 00:00:07 |           |           |            |           |                |
  29. |   1 |  PX COORDINATOR                |           |           |           |                |           |           |           |            |           |                |
  30. |   2 |   PX SEND QC (RANDOM)               | :TQ10004  |   607 | 46132 |   512   (2)| 00:00:07 |           |           |  Q1,04 | P->S | QC (RAND)|
  31. |   3 |    HASH GROUP BY               |           |   607 | 46132 |   512   (2)| 00:00:07 |           |           |  Q1,04 | PCWP |                |
  32. |   4 |     PX RECEIVE                       |           |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,04 | PCWP |                |
  33. |   5 |      PX SEND HASH               | :TQ10003  |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,03 | P->P | HASH        |

  34. PLAN_TABLE_OUTPUT
  35. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  36. |*  6 |       HASH JOIN                |           |  2337 |   173K|   511   (2)| 00:00:07 |           |           |  Q1,03 | PCWP |                |
  37. |   7 |        PX RECEIVE               |           |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
  38. |   8 |         PX SEND BROADCAST      | :TQ10000  |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | P->P | BROADCAST|
  39. |   9 |          PX BLOCK ITERATOR     |           |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | PCWC |                |
  40. |* 10 |           TABLE ACCESS FULL    | CHANNELS  |         1 |        13 |         2   (0)| 00:00:01 |           |           |  Q1,00 | PCWP |                |
  41. |* 11 |        HASH JOIN               |           |  9347 |   575K|   509   (2)| 00:00:07 |           |           |  Q1,03 | PCWP |                |
  42. |  12 |         PART JOIN FILTER CREATE| :BF0001   |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
  43. |  13 |          PX RECEIVE               |           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,03 | PCWP |                |
  44. |  14 |           PX SEND BROADCAST    | :TQ10001  |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | P->P | BROADCAST|
  45. |  15 |            PX BLOCK ITERATOR   |           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | PCWC |                |
  46. |* 16 |             TABLE ACCESS FULL  | TIMES           |   274 |  4384 |        10   (0)| 00:00:01 |           |           |  Q1,01 | PCWP |                |

  47. PLAN_TABLE_OUTPUT
  48. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  49. |* 17 |         HASH JOIN               |           | 49822 |  2286K|   498   (2)| 00:00:06 |           |           |  Q1,03 | PCWP |                |
  50. |  18 |          PX RECEIVE               |           |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,03 | PCWP |                |
  51. |  19 |           PX SEND BROADCAST    | :TQ10002  |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | P->P | BROADCAST|
  52. |  20 |            PX BLOCK ITERATOR   |           |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | PCWC |                |
  53. |* 21 |             TABLE ACCESS FULL  | CUSTOMERS |   383 |  9958 |   225   (1)| 00:00:03 |           |           |  Q1,02 | PCWP |                |
  54. |  22 |          PX BLOCK ITERATOR     |           |   918K|        18M|   271   (2)| 00:00:04 |:BF0001|:BF0001|  Q1,03 | PCWC |                |
  55. |* 23 |           TABLE ACCESS FULL    | SALES           |   918K|        18M|   271   (2)| 00:00:04 |:BF0001|:BF0001|  Q1,03 | PCWP |                |
  56. -----------------------------------------------------------------------------------------------------------------------------------------

  57. Predicate Information (identified by operation id):
  58. ---------------------------------------------------

  59. PLAN_TABLE_OUTPUT
  60. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  61.    6 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  62.   10 - filter("CH"."CHANNEL_DESC"='Direct Sales')
  63.   11 - access("S"."TIME_ID"="T"."TIME_ID")
  64.   16 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
  65.               "T"."CALENDAR_QUARTER_DESC"='2000-02')
  66.   17 - access("S"."CUST_ID"="C"."CUST_ID")
  67.   21 - filter("C"."CUST_STATE_PROVINCE"='FL')
  68.   23 - filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"S"."CUST_ID"),SYS_OP_BLOOM_FILTER(:BF0000,"S"."CHANNEL_ID")))

  69. Note

  70. PLAN_TABLE_OUTPUT
  71. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  72. -----
  73.    <font color="Red">- automatic DOP: Computed Degree of Parallelism is 2</font>

  74. 46 rows selected.

  75. SQL>
复制代码



回复

使用道具 举报

2

主题

15

帖子

118

积分

版主

Rank: 7Rank: 7Rank: 7

积分
118
发表于 2017-8-7 21:38:22 | 显示全部楼层
唐老师,PARALLEL_DEGREE_POLICY设置为AUTO时 ,是不是涉及到的有并行度的对象都会被缓存到database  buffer cache 里? 所以此时如果有index的时候。整个index也会被缓存至database  buffer cache 是吗?   那如果真的如上所述,设置为PARALLEL_DEGREE_POLICY设置为AUTO 不就是意味着这个参数其实不是一个很可控的参数?
回复 支持 反对

使用道具 举报

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
 楼主| 发表于 2017-8-8 15:58:45 | 显示全部楼层
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.

回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 23:42 , Processed in 0.041873 second(s), 27 queries .

快速回复 返回顶部 返回列表