【博客文章2022】IO校准和自动SQL并行执行
Author： Bo Tang
1. 如何指定SQL执行的并行度：
优先顺序：Hint > Session > Object（DDL并行度）。 而Hint又有两种：手工并行度Hint和自动并行度Hint（parallel_degree_policy必需设为limited，而且对象的DDL并行度必需是default）。
Oracle 11g R2版本之前，所有的并行度Hint都是手工并行度Hint。设置手工并行度Hint需要对SQL语句和工作负载有非常清楚的认识，这对DBA来说是很繁重的工作。由于DBA不会设置手工并行度Hint，或者当心设置不好手工并行度Hint对数据库带来负面的影响，所以很多本来应该并行的SQL语句都是以串型方式执行。新的自动并行度Hint的出现解决了这个难题。
2. 使用手工并行度Hint执行： 使用手工并行度Hint执行一条SQL语句，希望它能够并行执行。手工并行度Hint的语法是/*+ parallel(行源的名字 手工指定的并行度) */（也可以写成/*+ parallel(行源的名字, 手工指定的并行度) */）。只要数据库实例的parallel_max_servers的值还够分配（建议至少2倍于手工指定的并行度），那么理论上你在Hint中写几个并行度，最后SQL语句就会以该并行度来执行。但是并行度实际上并不是越大越好，何况它还要消耗资源，影响其他SQL语句的执行。并行查询会占用更多的内存排序区。 并行执行的内存占用量=串行执行的内存占用量*并行数*2。 例如开1G的排序区，20个并行进程，那么在极限情况下内存占用应为1G*20*2=40G。内存小于此量会占用pagefile而使得性能急剧下降。 首先，在系统中查看parallel_max_servers的值：
[oracle@station76 ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@station76 ~]$ mkdir dir1
事先下载好上面的ebs_workload.dmp
[oracle@station76 ~]$ mv ebs_workload.dmp dir1
[oracle@station76 ~]$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 1 19:29:29 2022
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create directory dir1 as '/home/oracle/dir1';
Directory created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@station76 ~]$ impdp system/oracle_4U directory=dir1 dumpfile=ebs_workload.dmp
Import: Release 12.1.0.2.0 - Production on Sat Apr 1 19:36:25 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dir1 dumpfile=ebs_workload.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "APPS"."FACT_PD_OUT_ITM_293" 175.0 MB 1501663 rows
. . imported "APPS"."ADM_PG_FEATUREVALUE" 14.26 MB 172392 rows
. . imported "APPS"."LU_ELEMENTGROUP_REL" 3.477 MB 84468 rows
. . imported "APPS"."LU_OUTLET_293" 3.398 MB 22289 rows
. . imported "APPS"."LU_ITEM_293" 1.274 MB 5355 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL" 767.1 KB 7957 rows
. . imported "APPS"."LU_ELEMENTRANGE_REL_Q2" 767.1 KB 7957 rows
. . imported "APPS"."ADM_CC_FEATUREVALUE" 248.8 KB 3047 rows
. . imported "APPS"."STS_JFV_TAB" 48.75 KB 7 rows
. . imported "APPS"."LU_PG_FEATUREVALUE_15_Q2" 122.9 KB 2505 rows
. . imported "APPS"."STS_JFV_TAB_CPLANS" 85.02 KB 132 rows
. . imported "APPS"."STS_JFV_TAB_CBINDS" 9.476 KB 0 rows
. . imported "APPS"."LU_PERIOD_293" 8.031 KB 31 rows
. . imported "APPS"."PLAN_TABLE" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 1 19:37:57 2022 elapsed 0 00:01:27
|
其次，查看表FACT_PD_OUT_ITM_293的DDL并行度定义，并改变它为DEFAULT：
SQL> select table_name, degree from dba_tables where owner='APPS' and table_name='FACT_PD_OUT_ITM_293';
--------------------------------------------------------------------------------------------------------------------------------
FACT_PD_OUT_ITM_293 1
SQL> alter table APPS.fact_pd_out_itm_293 parallel;
Table altered.
SQL> select table_name, degree from dba_tables where owner='APPS' and table_name='FACT_PD_OUT_ITM_293';
--------------------------------------------------------------------------------------------------------------------------------
FACT_PD_OUT_ITM_293 DEFAULT |
再查看并行度参数，并打开自动并行策略，使系统能接受自动并行度Hint的语法/*+ parallel(语句级别并行度) */：
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_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
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 160 NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent integer 0
parallel_min_servers integer 16
parallel_min_time_threshold string AUTO
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_servers_target integer 64
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0
SQL> alter system set parallel_degree_policy=limited;System Altered.
|
带老的Hint执行SQL语句：
SQL> conn apps/apps
Connected.
SQL> set linesize 10000
SQL> set autot traceonly
SQL> SELECT /*+ parallel (t1 8) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
'r' || t5.elementrange_id price_eur_id,
'B' || t2.productgroup_id productgroup_id,
'G' || t6.elementgroup_id period_id,
SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
FROM
lu_item_293 t2,
lu_pg_featurevalue_15 t3,
lu_elementrange_rel t4,
fact_pd_out_itm_293 t1,
lu_elementgroup_rel t6,
lu_elementrange_rel t5
WHERE
/* Attribute Joins */
((t1.item_id = t2.item_id
/* Customizing Begin */
AND t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
AND (t1.period_id = t6.value_id)
)
/* Attribute Filters */
AND ((t2.productgroup_id = 15520)
AND (t1.productgroup_id = 15520) /* Push Down Filters */
AND (t2.pg_featurevalue_01_id IN (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95))
AND (t2.pg_featurevalue_08_id IN (716,717))
AND (t2.pg_featurevalue_02_id IN (4165,4166))
AND (t2.pg_featurevalue_13_id = 5424)
AND (t4.elementrange_id IN (3091,3092))
AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
AND (t6.elementgroup_id = 14659)
AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
)
/* Fact Filters */
AND (t1.project_type_id = '1'
)
GROUP BY
t2.pg_featurevalue_13_id,
t2.pg_featurevalue_02_id,
t4.elementrange_id,
t2.pg_featurevalue_08_id,
t2.pg_featurevalue_01_id,
t5.elementrange_id,
t2.productgroup_id,
t6.elementgroup_id
/
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1148629017
----------------------------------------------------------------------------------------------------------------------------------------------------------
|
Id | Operation |
Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
| TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 350 | 23100 | | 1085 (1)| 00:00:01 | | | |
|
1 | PX COORDINATOR |
| | | | | | |
| |
| 2 | PX SEND QC (RANDOM) |
:TQ10006 | 350 | 23100 | | 1085 (1)| 00:00:01
| Q1,06 | P->S | QC (RAND) |
| 3 | HASH GROUP
BY | | 350 | 23100
| | 1085 (1)| 00:00:01 | Q1,06 | PCWP | |
| 4
| PX RECEIVE |
| 350 | 23100 | | 1085 (1)| 00:00:01 | Q1,06 | PCWP
| |
| 5 | PX SEND HASH |
:TQ10005 | 350 | 23100 | | 1085 (1)| 00:00:01
| Q1,05 | P->P | HASH |
| 6 | HASH GROUP
BY | | 350 | 23100
| | 1085 (1)| 00:00:01 | Q1,05 | PCWP | |
| 7
| MERGE JOIN |
| 706 | 46596 | | 1084 (1)| 00:00:01 | Q1,05 | PCWP
| |
| 8 | SORT JOIN
| | 1066 | 54366 | | 1061 (1)|
00:00:01 | Q1,05 | PCWP | |
| 9 | NESTED
LOOPS | | 1066 | 54366
| | 1060 (1)| 00:00:01 | Q1,05 | PCWP | |
| 10
| NESTED LOOPS | |
1066 | 54366 | | 1060 (1)| 00:00:01 | Q1,05 | PCWP
| |
| 11 | VIEW |
VW_GBC_14 | 1066 | 37310 | | 764 (1)| 00:00:01
| Q1,05 | PCWP | |
| 12 | HASH GROUP
BY | | 1066 | 98072 | |
764 (1)| 00:00:01 | Q1,05 | PCWP | |
| 13
| PX RECEIVE | |
1066 | 98072 | | 764 (1)| 00:00:01 | Q1,05 | PCWP
| |
| 14 | PX SEND HASH |
:TQ10003 | 1066 | 98072 | | 764 (1)| 00:00:01
| Q1,03 | P->P | HASH |
| 15 | HASH GROUP
BY | | 1066 | 98072 | |
764 (1)| 00:00:01 | Q1,03 | PCWP | |
| 16
| MERGE JOIN | |
46370 | 4166K| | 763 (1)| 00:00:01 | Q1,03 | PCWP
| |
| 17 | SORT JOIN
| | 6575 | 494K| 1160K| 739 (1)|
00:00:01 | Q1,03 | PCWP | |
|* 18 | HASH
JOIN | | 6575 | 494K|
| 738 (1)| 00:00:01 | Q1,03 | PCWP | |
| 19
| PX RECEIVE |
| 4 | 44 | | 93 (2)| 00:00:01 | Q1,03 | PCWP
| |
| 20 | PX SEND BROADCAST |
:TQ10000 | 4 | 44 | | 93 (2)| 00:00:01
| Q1,00 | S->P | BROADCAST |
| 21 | PX
SELECTOR | | | |
| | | Q1,00 | SCWC | |
|* 22
| TABLE ACCESS FULL | LU_ELEMENTGROUP_REL
| 4 | 44 | | 93 (2)| 00:00:01 | Q1,00 | SCWP
| |
|* 23 | HASH JOIN
| | 6575 | 423K| | 646 (1)|
00:00:01 | Q1,03 | PCWP | |
| 24 |
JOIN FILTER CREATE | :BF0000 | 586 | 18752 |
| 35 (0)| 00:00:01 | Q1,03 | PCWP | |
| 25
| PX RECEIVE |
| 586 | 18752 | | 35 (0)| 00:00:01 | Q1,03 | PCWP
| |
| 26 | PX SEND BROADCAST |
:TQ10001 | 586 | 18752 | | 35 (0)| 00:00:01
| Q1,01 | S->P | BROADCAST |
| 27 | PX
SELECTOR | | | |
| | | Q1,01 | SCWC | |
|* 28
| TABLE ACCESS FULL| LU_ITEM_293
| 586 | 18752 | | 35 (0)| 00:00:01 | Q1,01 | SCWP
| |
| 29 | JOIN FILTER USE |
:BF0000 | 60086 | 1995K| | 610 (1)| 00:00:01
| Q1,03 | PCWP | |
| 30 | PX BLOCK
ITERATOR | | 60086 | 1995K| | 610
(1)| 00:00:01 | Q1,03 | PCWC | |
|* 31
| TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 |
60086 | 1995K| | 610 (1)| 00:00:01 | Q1,03 | PCWP
| |
|* 32 | FILTER
| | | | | |
| Q1,03 | PCWP | |
|* 33 | SORT
JOIN | | 7 | 105 |
| 23 (5)| 00:00:01 | Q1,03 | PCWP | |
| 34
| BUFFER SORT |
| | | | | | Q1,03 | PCWC
| |
| 35 | PX RECEIVE
| | 7 | 105 | | 22 (0)|
00:00:01 | Q1,03 | PCWP | |
| 36 |
PX SEND BROADCAST | :TQ10002 | 7 | 105 |
| 22 (0)| 00:00:01 | Q1,02 | S->P | BROADCAST |
| 37
| PX SELECTOR |
| | | | | | Q1,02 | SCWC
| |
|* 38 | TABLE ACCESS FULL |
LU_ELEMENTRANGE_REL | 7 | 105 | | 22 (0)| 00:00:01
| Q1,02 | SCWP | |
|* 39 | INDEX RANGE
SCAN | ADM_PG_FEATUREVALUE_IDX2 | 1 | |
| 1 (0)| 00:00:01 | Q1,05 | PCWP | |
|* 40
| TABLE ACCESS BY INDEX ROWID | ADM_PG_FEATUREVALUE
| 1 | 16 | | 2 (0)| 00:00:01 | Q1,05 | PCWP
| |
|* 41 | FILTER
| | | | | |
| Q1,05 | PCWP | |
|* 42 | SORT
JOIN | | 2 | 30
| | 23 (5)| 00:00:01 | Q1,05 | PCWP | |
| 43
| BUFFER SORT |
| | | | | | Q1,05 | PCWC
| |
| 44 | PX RECEIVE
| | 2 | 30 | | 22 (0)|
00:00:01 | Q1,05 | PCWP | |
| 45 | PX SEND
BROADCAST | :TQ10004 | 2 | 30 |
| 22 (0)| 00:00:01 | Q1,04 | S->P | BROADCAST |
| 46
| PX SELECTOR |
| | | | | | Q1,04 | SCWC
| |
|* 47 | TABLE ACCESS FULL |
LU_ELEMENTRANGE_REL | 2 | 30 | | 22 (0)| 00:00:01
| Q1,04 | SCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 18 - access("T1"."PERIOD_ID"="T6"."VALUE_ID")
22 - filter("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR "T6"."VALUE_ID"=20030799999030 OR
"T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
23 - access("T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID" AND "T1"."ITEM_ID"="T2"."ITEM_ID")
28 - filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR "T2"."PG_FEATUREVALUE_01_ID"=76 OR
"T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92 OR
"T2"."PG_FEATUREVALUE_01_ID"=95 OR "T2"."PG_FEATUREVALUE_01_ID"=103 OR
"T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR
"T2"."PG_FEATUREVALUE_01_ID"=134 OR "T2"."PG_FEATUREVALUE_01_ID"=148 OR
"T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR
"T2"."PG_FEATUREVALUE_01_ID"=216 OR "T2"."PG_FEATUREVALUE_01_ID"=236 OR
"T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477
OR
"T2"."PG_FEATUREVALUE_01_ID"=3891 OR
"T2"."PG_FEATUREVALUE_01_ID"=24958 OR
"T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520
AND ("T2"."PG_FEATUREVALUE_02_ID"=4165 OR
"T2"."PG_FEATUREVALUE_02_ID"=4166) AND ("T2"."PG_FEATUREVALUE_08_ID"=716
OR
"T2"."PG_FEATUREVALUE_08_ID"=717))
31 - filter(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR "T1"."PERIOD_ID"=20030799999060 OR
"T1"."PERIOD_ID"=20030799999120) AND "T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1' AND
SYS_OP_BLOOM_FILTER(:BF0000,"T1"."PRODUCTGROUP_ID","T1"."ITEM_ID"))
32 - filter("T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND")
33 - access("T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND")
filter("T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND")
38 - filter("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694
OR "T5"."ELEMENTRANGE_ID"=8695 OR "T5"."ELEMENTRANGE_ID"=8696 OR
"T5"."ELEMENTRANGE_ID"=8697 OR "T5"."ELEMENTRANGE_ID"=8698 OR "T5"."ELEMENTRANGE_ID"=8699)
39 - access("ITEM_1"="FEATUREVALUE_ID")
40 - filter("TABLE_NR"=15 AND "DELETED"=0)
41 - filter("FEATUREVALUE_NUM"<="T4"."UBOUND")
42 - access(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
filter(INTERNAL_FUNCTION("FEATUREVALUE_NUM")>=INTERNAL_FUNCTION("T4"."LBOUND"))
47 - filter("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092) Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 6 Sql Plan Directives used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
27073 consistent gets
25776 physical reads
204 redo size
1438 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
8 rows processed
SQL> set autot off
SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized 0 0 0
DML Parallelized 0 0 0
DDL Parallelized 0 0 0
DFO Trees 0 0 0
Server Threads 0 0 0
Allocation Height 0 0 0
Allocation Width 0 0 0
Local Msgs Sent 0 968 0
Distr Msgs Sent 0 0 0
Local Msgs Recv'd 0 968 0
Distr Msgs Recv'd 0 0 0 STATISTIC LAST_QUERY SESSION_TOTAL CON_ID
------------------------------ ---------- ------------- ----------
DOP 0 0 0
Slave Sets 0 0 0 13 rows selected. |
上面没有采取并行度来执行，这是因为parallel_degree_policy不是MANUAL。从上面的执行计划分析看出该SQL语句的单块读总成本为：1085。该值越低越好。
3. 在执行过IO校准的情况下，同时在POLICY为LIMITED下使用自动并行执行：
在生产环境中，强烈建议在数据库非繁忙时段进行IO校准。这是因为IO校准不仅时间很长而且对数据产生的模拟IO负载还相当巨大，同时正常的工作负载还会干扰模拟工作负载的校准结果。 下面来查看如何在数据库系统中做IO校准，首先查看一下CALIBRATE_IO存储过程的形式参数定义：
上面的 NUM_PHYSICAL_DISKS代表数据库系统所在的服务器的硬盘的个数；MAX_LATENCY代表硬盘的最大延迟（单位为1/1000秒）。一定要根据真实硬盘的情况来作IO校准，在IO校准之前查看一下filesystem_io这个参数：
SQL> SELECT /*+ parallel (8) */
'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
'r' || t4.elementrange_id pg_featurevalue_15_id,
'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
'r' || t5.elementrange_id price_eur_id,
'B' || t2.productgroup_id productgroup_id,
'G' || t6.elementgroup_id period_id,
SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
FROM
lu_item_293 t2,
lu_pg_featurevalue_15 t3,
lu_elementrange_rel t4,
fact_pd_out_itm_293 t1,
lu_elementgroup_rel t6,
lu_elementrange_rel t5
WHERE
/* Attribute Joins */
((t1.item_id = t2.item_id
/* Customizing Begin */
AND t1.productgroup_id = t2.productgroup_id)
/* Customizing End */
AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
AND (t1.period_id = t6.value_id)
)
/* Attribute Filters */
AND ((t2.productgroup_id = 15520)
AND (t1.productgroup_id = 15520) /* Push Down Filters */
AND (t2.pg_featurevalue_01_id IN (103,104,107,110,113,134,148,167,171,186,192,216,2259,236,241,2477,24958,27445,297,3891,71,76,89,92,95))
AND (t2.pg_featurevalue_08_id IN (716,717))
AND (t2.pg_featurevalue_02_id IN (4165,4166))
AND (t2.pg_featurevalue_13_id = 5424)
AND (t4.elementrange_id IN (3091,3092))
AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
AND (t6.elementgroup_id = 14659)
AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
)
/* Fact Filters */
AND (t1.project_type_id = '1'
)
GROUP BY
t2.pg_featurevalue_13_id,
t2.pg_featurevalue_02_id,
t4.elementrange_id,
t2.pg_featurevalue_08_id,
t2.pg_featurevalue_01_id,
t5.elementrange_id,
t2.productgroup_id,
t6.elementgroup_id
/
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4062235825
----------------------------------------------------------------------------------------------------------------------------------------
|
Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 983 | 689 (1)| 00:00:01 | | | |
|
1 | PX COORDINATOR | |
| | | | | | |
| 2
| PX SEND QC (RANDOM) | :TQ10003 | 7 |
983 | 689 (1)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 3
| HASH GROUP BY | | 7 |
983 | 689 (1)| 00:00:01 | Q1,03 | PCWP | |
| 4 |
PX RECEIVE | | 7 | 983
| 689 (1)| 00:00:01 | Q1,03 | PCWP | |
| 5 | PX
SEND HASH | :TQ10002 | 7 | 983 |
689 (1)| 00:00:01 | Q1,02 | P->P | HASH |
| 6 |
HASH GROUP BY | | 7 | 983 |
689 (1)| 00:00:01 | Q1,02 | PCWP | |
|* 7 | HASH
JOIN | | 30692 | 3686K| 688
(1)| 00:00:01 | Q1,02 | PCWP | |
|* 8 | TABLE
ACCESS FULL | LU_ELEMENTGROUP_REL | 4 | 44 | 13
(0)| 00:00:01 | Q1,02 | PCWP | |
|* 9 | HASH
JOIN | | 30692 | 3356K| 675
(1)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX
RECEIVE | | 2741 | 208K| 65
(5)| 00:00:01 | Q1,02 | PCWP | |
| 11 | PX SEND
BROADCAST | :TQ10001 | 2741 | 208K| 65 (5)|
00:00:01 | Q1,01 | P->P | BROADCAST |
|* 12 | HASH
JOIN | | 2741 | 208K| 65 (5)|
00:00:01 | Q1,01 | PCWP | |
|* 13 | TABLE
ACCESS FULL | LU_ITEM_293 | 586 | 18752 | 5 (0)|
00:00:01 | Q1,01 | PCWP | |
| 14 | MERGE
JOIN | | 13211 | 593K| 60 (5)|
00:00:01 | Q1,01 | PCWP | |
| 15 | SORT
JOIN | | 14 | 420 | 8 (13)|
00:00:01 | Q1,01 | PCWP | |
| 16 | MERGE
JOIN CARTESIAN | | 14 | 420 | 7 (0)|
00:00:01 | Q1,01 | PCWP | |
| 17 | PX
BLOCK ITERATOR | | 7 | 105 | 3 (0)|
00:00:01 | Q1,01 | PCWC | |
|* 18 | TABLE
ACCESS FULL | LU_ELEMENTRANGE_REL | 7 | 105 | 3 (0)|
00:00:01 | Q1,01 | PCWP | |
| 19 | BUFFER
SORT | | 2 | 30 | 4 (0)|
00:00:01 | Q1,01 | PCWP | |
|* 20 | TABLE
ACCESS FULL | LU_ELEMENTRANGE_REL | 2 | 30 | 3 (0)|
00:00:01 | Q1,01 | PCWP | |
|* 21 |
FILTER | | |
| | | Q1,01 | PCWP | |
|* 22
| SORT JOIN | | 2823 |
45168 | 52 (4)| 00:00:01 | Q1,01 | PCWP | |
| 23
| BUFFER SORT | |
| | | | Q1,01 | PCWC | |
| 24
| PX RECEIVE | | 2823 |
45168 | 51 (2)| 00:00:01 | Q1,01 | PCWP | |
| 25
| PX SEND BROADCAST | :TQ10000 | 2823 |
45168 | 51 (2)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 26
| PX BLOCK ITERATOR | | 2823 |
45168 | 51 (2)| 00:00:01 | Q1,00 | PCWC | |
|* 27
| TABLE ACCESS FULL| ADM_PG_FEATUREVALUE | 2823 |
45168 | 51 (2)| 00:00:01 | Q1,00 | PCWP | |
| 28
| PX BLOCK ITERATOR | | 60086 |
1995K| 610 (1)| 00:00:01 | Q1,02 | PCWC | |
|* 29
| TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 | 60086 |
1995K| 610 (1)| 00:00:01 | Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 7 - access("T1"."PERIOD_ID"="T6"."VALUE_ID")
8 - filter("T6"."ELEMENTGROUP_ID"=14659 AND ("T6"."VALUE_ID"=20030699999060 OR "T6"."VALUE_ID"=20030799999030 OR
"T6"."VALUE_ID"=20030799999060 OR "T6"."VALUE_ID"=20030799999120))
9 - access("T1"."ITEM_ID"="T2"."ITEM_ID" AND "T1"."PRODUCTGROUP_ID"="T2"."PRODUCTGROUP_ID")
filter("T1"."PD_PRICE_UNITS_EUR">="T5"."LBOUND" AND "T1"."PD_PRICE_UNITS_EUR"<="T5"."UBOUND")
12 - access("T2"."PG_FEATUREVALUE_15_ID"="FEATUREVALUE_ID")
13 - filter("T2"."PG_FEATUREVALUE_13_ID"=5424 AND ("T2"."PG_FEATUREVALUE_01_ID"=71 OR "T2"."PG_FEATUREVALUE_01_ID"=76 OR
"T2"."PG_FEATUREVALUE_01_ID"=89 OR "T2"."PG_FEATUREVALUE_01_ID"=92 OR "T2"."PG_FEATUREVALUE_01_ID"=95 OR
"T2"."PG_FEATUREVALUE_01_ID"=103 OR "T2"."PG_FEATUREVALUE_01_ID"=104 OR "T2"."PG_FEATUREVALUE_01_ID"=107 OR
"T2"."PG_FEATUREVALUE_01_ID"=110 OR "T2"."PG_FEATUREVALUE_01_ID"=113 OR "T2"."PG_FEATUREVALUE_01_ID"=134 OR
"T2"."PG_FEATUREVALUE_01_ID"=148 OR "T2"."PG_FEATUREVALUE_01_ID"=167 OR "T2"."PG_FEATUREVALUE_01_ID"=171 OR
"T2"."PG_FEATUREVALUE_01_ID"=186 OR "T2"."PG_FEATUREVALUE_01_ID"=192 OR "T2"."PG_FEATUREVALUE_01_ID"=216 OR
"T2"."PG_FEATUREVALUE_01_ID"=236 OR "T2"."PG_FEATUREVALUE_01_ID"=241 OR "T2"."PG_FEATUREVALUE_01_ID"=297 OR
"T2"."PG_FEATUREVALUE_01_ID"=2259 OR "T2"."PG_FEATUREVALUE_01_ID"=2477 OR "T2"."PG_FEATUREVALUE_01_ID"=3891 OR
"T2"."PG_FEATUREVALUE_01_ID"=24958 OR "T2"."PG_FEATUREVALUE_01_ID"=27445) AND "T2"."PRODUCTGROUP_ID"=15520 AND
("T2"."PG_FEATUREVALUE_02_ID"=4165 OR "T2"."PG_FEATUREVALUE_02_ID"=4166) AND ("T2"."PG_FEATUREVALUE_08_ID"=716 OR
"T2"."PG_FEATUREVALUE_08_ID"=717))
18 - filter("T5"."ELEMENTRANGE_ID"=8693 OR "T5"."ELEMENTRANGE_ID"=8694 OR "T5"."ELEMENTRANGE_ID"=8695 OR
"T5"."ELEMENTRANGE_ID"=8696 OR "T5"."ELEMENTRANGE_ID"=8697 OR
"T5"."ELEMENTRANGE_ID"=8698 OR "T5"."ELEMENTRANGE_ID"=8699)
20 - filter("T4"."ELEMENTRANGE_ID"=3091 OR "T4"."ELEMENTRANGE_ID"=3092)
21 - filter("FEATUREVALUE_NUM"<="T4"."UBOUND")
22 - access("FEATUREVALUE_NUM">="T4"."LBOUND")
filter("FEATUREVALUE_NUM">="T4"."LBOUND")
27 - filter("TABLE_NR"=15 AND "DELETED"=0)
29 - filter(("T1"."PERIOD_ID"=20030699999060 OR "T1"."PERIOD_ID"=20030799999030 OR "T1"."PERIOD_ID"=20030799999060 OR
"T1"."PERIOD_ID"=20030799999120) AND "T1"."PRODUCTGROUP_ID"=15520 AND "T1"."PROJECT_TYPE_ID"='1') Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 8 because of hint
- 6 Sql Plan Directives used for this statement
Statistics
----------------------------------------------------------
359 recursive calls
0 db block gets
34841 consistent gets
27877 physical reads
1164 redo size
1428 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
8 rows processed
|
4. 在执行过IO校准的情况下，同时在POLICY为AUTO下自动并行执行：
SQL> alter system set parallel_degree_policy=auto;
System Altered. SQL> show parameter parallel_min_time_threshold NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ parallel_min_time_threshold string AUTO
|
为了尽量接近生产环境，制造一个很大的表：
SQL> conn sh/oracle_4U Connected. SQL> create table sales2 as select * from sales; Table created. SQL> insert into sales2 select * from sales2; 918843 rows created. SQL> commit; Commit complete. SQL> insert into sales2 select * from sales2;
多次执行上面操作
SQL> commit; Commit complete.
|
下面测试执行：
SQL> set linesize 10000
SQL> set autot on
SQL> select /*+ parallel */ count(*) from sales2; COUNT(*)
----------
6431901
Execution Plan
----------------------------------------------------------
Plan hash value: 3046936024
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 682 (1)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 918K| 682 (1)| 00:00:01 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| SALES2 | 918K| 682 (1)| 00:00:01 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------- Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized for buffer cache
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
31670 consistent gets
0 physical reads
0 redo size
545 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
|
