Search

Top 60 Oracle Blogs

Recent comments

automatic DOP

nocompressでIn-Memory Parallel Query の中でCOMPRESS->NOCOMPRESSにしたらパラレル度が自動的に4->6になった。

NOCOMPRESSだとパラレル度は上がるのか?

NOCOMPRESSのlineitemをSelectする:

SQL> alter system flush buffer_cache;

システムが変更されました。

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:02.03

実行計画
----------------------------------------------------------
Plan hash value: 2461824725

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6956 (1)| 00:01:24 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
22 recursive calls
4 db block gets
106301 consistent gets
102004 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed

DOP=3が自動的に設定され、2.03秒かかった。
automatic DOP: Computed Degree of Parallelism is 3

次にCOMPRESSされたlineitemをSelectしてみる:


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem_comp
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:01.51

実行計画
----------------------------------------------------------
Plan hash value: 3881738590

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6787 (2)| 00:01:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM_COMP | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
16 recursive calls
4 db block gets
67041 consistent gets
66958 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed

DOP=2が自動的に設定され、1.51秒で終わった。

まとめてみる

NOCOMPRESS   automatic DOP=3
COMPRESS    automatic DOP=2

NOCOMPRESSの方がDOPが大きく設定される。 --->アンコンプレスの処理負荷を考慮しているのだろうか?

実行時間は
NOCOMPRESS  2.01秒
COMPRESS   1.51秒

COMPRESSの方が速い。それはphysical read量に比例する
 102004  physical reads
  66958  physical reads

だから、Parallel QueryはCOMPRESSが有利
でも、In-Memory Parallel Queryだと

上記のテスト後にもう一度二つのSQLを実行してみる:


SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:00.33

実行計画
----------------------------------------------------------
Plan hash value: 2461824725

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6956 (1)| 00:01:24 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6956 (1)| 00:01:24 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM | 634K| 16M| 6941 (1)| 00:01:24 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 3


統計
----------------------------------------------------------
34 recursive calls
4 db block gets
106301 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty
2 , sum(l_extendedprice) as sum_base_price
3 , sum(l_extendedprice * (1 - l_discount)) as sum_disc_price
4 , sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge
5 , avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price
6 , avg(l_discount) as avg_disc
7 , count(*) as count_order
8 from lineitem_comp
9 where l_shipdate <= date '1992-12-01' - interval '68' day (3)
10 group by l_returnflag, l_linestatus
11 order by l_returnflag, l_linestatus
12 /

経過: 00:00:00.62

実行計画
----------------------------------------------------------
Plan hash value: 3881738590

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 6787 (2)| 00:01:22 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,01 | PCWP | |
| 5 | PX SEND RANGE | :TQ10000 | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | P->P | RANGE |
| 6 | HASH GROUP BY | | 5 | 135 | 6787 (2)| 00:01:22 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| LINEITEM_COMP | 634K| 16M| 6765 (1)| 00:01:22 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

8 - filter("L_SHIPDATE"<=TO_DATE(' 1992-09-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2


統計
----------------------------------------------------------
16 recursive calls
4 db block gets
67041 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed

一回目のSQLでBuffer_Cache上にデータがあるのでphysical reads=0となる。

NOCOMPRESS   automatic DOP=3
COMPRESS    automatic DOP=2

実行時間は
NOCOMPRESS  0.33秒
COMPRESS   0.62秒

consistent gets量が多いにもかかわらずNOCOMPRESSの方が2倍速い。
 106301   consistent gets
 67041   consistent gets

でも、その差はたった0.3秒。しかし、その僅かな差を積み上げると:

という結果になった