Search

OakieTags

more tags

Who's online

There are currently 1 user and 28 guests online.

Online users

Recent comments

Affiliations

Home

In-Memory Parallel Query

Submitted by Ichiro Obata on Sun, 2010-08-29 21:21

TPC-Hベンチマークの続き

hammeroraのスクリプトを編集して、In-Memory Parallel Queryのテストを行う：
set sql(1) "alter session set parallel_degree_policy=auto"

まずは１セッションで：

そのときのCPU使用率は：

In-Memory Parallel Queryでは、Parallel度を明示的に指定できない。
だから、「何Parallel」で動いているのかを知りたい：


SQL> alter system flush shared_pool;
set lines 250
set pages 200
col module format a20
col STIME format a8
col ETIME format a8
col PAST format a13
col PX format '99'
col CLWAIT format '99'
col APWAIT format '99'
col SORTS format '999'
col EXECS format '999'
select /*+  no_monitoring */
        to_char(min(c.SQL_EXEC_START),'HH24:MI:SS') STIME
        ,to_char(max(c.SAMPLE_TIME),'HH24:MI:SS') ETIME
        ,substr(to_char(max(c.SAMPLE_TIME)-min(c.SQL_EXEC_START),'HH24:MI:SS'),11) PAST
        ,max(a.EXECUTIONS) EXECS
        ,max(a.FETCHES) FETCHES
        ,max(a.PX_SERVERS_EXECUTIONS) PX
        ,max(a.SORTS) SORTS
        ,max(a.ROWS_PROCESSED) ROWS_P
        ,round(max(a.USER_IO_WAIT_TIME)) IOWAIT
--        ,round(max(a.CLUSTER_WAIT_TIME)) CLWAIT
--        ,round(max(a.APPLICATION_WAIT_TIME)) APWAIT
        ,round(max(a.CONCURRENCY_WAIT_TIME)) CCWAIT
        ,round(max(a.ELAPSED_TIME)) ELAPSED
        ,round(max(a.CPU_TIME)) CPU_TIME
        ,max(a.BUFFER_GETS) BUF_GETS
        ,max(a.OPTIMIZER_COST) COST
        ,a.sql_id
--        ,a.PLAN_HASH_VALUE
        ,a.MODULE
        from v$sql a, v$active_session_history C
        where a.sql_id = c.sql_id
 and ELAPSED_TIME > 0
        and a.PARSING_SCHEMA_NAME <> 'SYS'
        group by a.PLAN_HASH_VALUE, a.sql_id, a.MODULE
 order by ELAPSED
/
STIME    ETIME    PAST          EXECS    FETCHES  PX SORTS     ROWS_P     IOWAIT     CCWAIT    ELAPSED   CPU_TIME   BUF_GETS       COST SQL_ID        MODULE
-------- -------- ------------- ----- ---------- --- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------- --------------------
00:47:24 04:50:15  04:02:51.629     1          1   4     4          2          0          0     141260     140400       2974        291 5huqb0rhzf29q wish85t.exe
04:54:49 04:54:50  00:00:01.404     1         75   4     2        744          0          0     411164     436804      28853       2903 arz3wfaba8da7 wish85t.exe
04:54:46 04:54:47  00:00:01.394     1          1   4     2          1          0          0    1038073    1029606      65835       6747 bk9sn1kbzs7yn wish85t.exe
04:54:43 04:54:44  00:00:01.384     1          1   4     2          2          0          0    1044030    1045207      74255       7614 akt0yxaa05ggd wish85t.exe
04:54:36 04:54:37  00:00:01.364     1       1152   4     2      11514          0          0    1209427    1201208      56191       9318 6c31ta3vgcwzh wish85t.exe
04:54:39 04:54:40  00:00:01.374     1          1   4     2          2          0          4    1473278    1513208      88811       9053 cruv5v9furgq5 wish85t.exe
04:54:38 04:54:39  00:00:01.364     1          1   4     4          4          0          0    1770111    1887612      41766       4239 5bgxrmr55wvb6 wish85t.exe
04:54:37 04:54:38  00:00:01.364     1          1   4     2          5          0          0    2055977    2090414      84879       8656 ampnk72s02dtp wish85t.exe
04:54:42 04:54:43  00:00:01.384     1       3798   4     2      37980          0          7    2152165    2293216      84405      12609 gwpu7myf17bz7 wish85t.exe
00:46:21 04:54:45  04:08:24.384     1          4   4     2         36          0          0    3714914    3759624      22537       7343 3ndjyxw801gnu wish85t.exe
04:54:40 04:54:42  00:00:02.374     1         18   4     4        175          0          0    4058806    4009226     100793      11871 3w4gvgbnb2tc3 wish85t.exe
04:54:33 04:54:36  00:00:03.364     1          1   3     2          4          0          0    4708541    4711230      62300       6534 3f14rpqjvdpz4 wish85t.exe
04:54:46 04:54:49  00:00:03.394     1          1   4     2          9          0          0    4984365    5070032     146284      19339 2xjra7wm4cryf wish85t.exe
04:54:49 04:54:52  00:00:03.405     1         78   4     2        780          0          0    5720865    5740836     143681      14808 bwynwaxkzqkxj wish85t.exe

ひとつを除いてはDOP(PX)=4で動いている。DOP(PX)=3で動いているSQLはlineitemのみNO_JOINのSQLだ。

bk9sn1kbzs7yn select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#14' and p_container = 'JUMBO CAN' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey)
arz3wfaba8da7 select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like 'plum%') and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year)) and s_nationkey = n_nationkey and n_name = 'PERU' order by s_name
2xjra7wm4cryf select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 315) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate
5bgxrmr55wvb6 select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'SAUDI ARABIA' and n2.n_name = 'MOZAMBIQUE') or (n1.n_name = 'MOZAMBIQUE' and n2.n_name = 'SAUDI ARABIA')) and l_shipdate between date '1995-01-01' and date '1996-12-31') shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year
6c31ta3vgcwzh select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'MACHINERY' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-14' and l_shipdate > date '1995-03-14' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate
akt0yxaa05ggd select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('REG AIR', 'AIR') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1997-01-01' and l_receiptdate < date '1997-01-01' + interval '1' year group by l_shipmode order by l_shipmode
3ndjyxw801gnu select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) as c_count from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%unusual%requests%' group by c_custkey) c_orders group by c_count order by custdist desc, c_count desc
3f14rpqjvdpz4 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '68' day (3) group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus
5huqb0rhzf29q select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue0) order by _suppkey
ampnk72s02dtp select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date '1996-01-01' and o_orderdate < date '1996-01-01' + interval '1' year group by n_name order by revenue desc
bwynwaxkzqkxj select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate) and s_nationkey = n_nationkey and n_name = 'ARGENTINA' group by s_name order by numwait desc, s_name
3w4gvgbnb2tc3 select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%olive%') profit group by nation, o_year order by nation, o_year desc
gwpu7myf17bz7 select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc
cruv5v9furgq5 select o_year, sum(case when nation = 'UNITED KINGDOM' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as
volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'EUROPE' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'SMALL ANODIZED TIN') all_nations group by o_year order by o_year

今回の結果
前回までのTPC-Hの結果と比較すると、やはりIn-Memory PQは一番速い。
でも、驚くほど速いというわけではない。
今回は、Partitioning＋Compressの環境で行ったからだろうか？

最後に、
hammeroraを使う場合は、OPTIMIZER_DYNAMIC_SAMPLING=0の環境にしないと、SQL統計情報の有無に関わらず発行されるRecursive Callがカウントされてしまうので注意です。