【博客文章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语句：
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
/
|
SQL> conn apps/apps
Connected.
SQL> set linesize 10000
SQL> set autot traceonly
SQL> SELECT /*+ parallel (t1 8) */
2 'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
3 'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
4 'r' || t4.elementrange_id pg_featurevalue_15_id,
5 'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
6 'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
7 'r' || t5.elementrange_id price_eur_id,
8 'B' || t2.productgroup_id productgroup_id,
9 'G' || t6.elementgroup_id period_id,
10 SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
11 FROM
12 lu_item_293 t2,
13 lu_pg_featurevalue_15 t3,
14 lu_elementrange_rel t4,
15 fact_pd_out_itm_293 t1,
16 lu_elementgroup_rel t6,
17 lu_elementrange_rel t5
18 WHERE
19 /* Attribute Joins */
20 ((t1.item_id = t2.item_id
21 /* Customizing Begin */
22 AND t1.productgroup_id = t2.productgroup_id)
23 /* Customizing End */
24 AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
25 AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
26 AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
27 AND (t1.period_id = t6.value_id)
28 )
29 /* Attribute Filters */
30 AND ((t2.productgroup_id = 15520)
31 AND (t1.productgroup_id = 15520) /* Push Down Filters */
32 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))
33 AND (t2.pg_featurevalue_08_id IN (716,717))
34 AND (t2.pg_featurevalue_02_id IN (4165,4166))
35 AND (t2.pg_featurevalue_13_id = 5424)
36 AND (t4.elementrange_id IN (3091,3092))
37 AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
38 AND (t6.elementgroup_id = 14659)
39 AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
40 )
41 /* Fact Filters */
42 AND (t1.project_type_id = '1'
43 )
44 GROUP BY
45 t2.pg_featurevalue_13_id,
46 t2.pg_featurevalue_02_id,
47 t4.elementrange_id,
48 t2.pg_featurevalue_08_id,
49 t2.pg_featurevalue_01_id,
50 t5.elementrange_id,
51 t2.productgroup_id,
52 t6.elementgroup_id
53 / 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.
|
3. 在执行过IO校准的情况下，在POLICY为LIMITED下使用自动并行执行：
在生产环境中，强烈建议在数据库非繁忙时段进行IO校准。这是因为IO校准不仅时间很长而且对数据产生的模拟IO负载还相当巨大，同时正常的工作负载还会干扰模拟工作负载的校准结果。 下面来查看如何在数据库系统中做IO校准，首先查看一下CALIBRATE_IO存储过程的形式参数定义：
上面的 NUM_PHYSICAL_DISKS代表数据库系统所在的服务器的硬盘的个数；MAX_LATENCY代表硬盘的最大延迟（单位为1/1000秒）。一定要根据真实硬盘的情况来作IO校准，在IO校准之前查看一下filesystem_io这个参数：
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
/
|
SQL> SELECT /*+ parallel (8) */
2 'B' || t2.pg_featurevalue_13_id pg_featurevalue_13_id,
3 'B' || t2.pg_featurevalue_02_id pg_featurevalue_02_id,
4 'r' || t4.elementrange_id pg_featurevalue_15_id,
5 'B' || t2.pg_featurevalue_08_id pg_featurevalue_08_id,
6 'B' || t2.pg_featurevalue_01_id pg_featurevalue_01_id,
7 'r' || t5.elementrange_id price_eur_id,
8 'B' || t2.productgroup_id productgroup_id,
9 'G' || t6.elementgroup_id period_id,
10 SUM(t1.pd_sales_units*t1.pd_projection_factor*t1.pd_price_units_eur) salesvalueeur
11 FROM
12 lu_item_293 t2,
13 lu_pg_featurevalue_15 t3,
14 lu_elementrange_rel t4,
15 fact_pd_out_itm_293 t1,
16 lu_elementgroup_rel t6,
17 lu_elementrange_rel t5
18 WHERE
19 /* Attribute Joins */
20 ((t1.item_id = t2.item_id
21 /* Customizing Begin */
22 AND t1.productgroup_id = t2.productgroup_id)
23 /* Customizing End */
24 AND (t2.pg_featurevalue_15_id = t3.pg_featurevalue_15_id)
25 AND (t3.pg_featurevalue_15_num BETWEEN t4.lbound AND t4.ubound)
26 AND (t1.pd_price_units_eur BETWEEN t5.lbound AND t5.ubound)
27 AND (t1.period_id = t6.value_id)
28 )
29 /* Attribute Filters */
30 AND ((t2.productgroup_id = 15520)
31 AND (t1.productgroup_id = 15520) /* Push Down Filters */
32 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))
33 AND (t2.pg_featurevalue_08_id IN (716,717))
34 AND (t2.pg_featurevalue_02_id IN (4165,4166))
35 AND (t2.pg_featurevalue_13_id = 5424)
36 AND (t4.elementrange_id IN (3091,3092))
37 AND (t5.elementrange_id IN (8693,8694,8695,8696,8697,8698,8699))
38 AND (t6.elementgroup_id = 14659)
39 AND (t1.period_id IN (20030699999060,20030799999030,20030799999060,20030799999120)) /* Resolved ElementGroup Filters */
40 )
41 /* Fact Filters */
42 AND (t1.project_type_id = '1'
43 )
44 GROUP BY
45 t2.pg_featurevalue_13_id,
46 t2.pg_featurevalue_02_id,
47 t4.elementrange_id,
48 t2.pg_featurevalue_08_id,
49 t2.pg_featurevalue_01_id,
50 t5.elementrange_id,
51 t2.productgroup_id,
52 t6.elementgroup_id
53 / 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
|
上面采取了8个并行度来执行。从上面的执行计划分析看出该SQL语句的单块读总成本为：689。该值越低越好。
4. 在执行过IO校准的情况下，在POLICY为AUTO下t自动并行执行：
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
|
|
