You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Selvaganesan Govindarajan (JIRA)" <ji...@apache.org> on 2016/10/05 14:17:20 UTC
[jira] [Commented] (TRAFODION-2241) TPC-H 100GB query10 fails to
complete in less than 90 minutes
[ https://issues.apache.org/jira/browse/TRAFODION-2241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15548837#comment-15548837 ]
Selvaganesan Govindarajan commented on TRAFODION-2241:
------------------------------------------------------
Can you please send the the plan of the query. To get the plan, you can do the following
log query1.log ;
showddl <table_name>
prepare s1 from <query_1> ;
explain options 'f' s1 ;
explain s1 ;
log ;
> TPC-H 100GB query10 fails to complete in less than 90 minutes
> -------------------------------------------------------------
>
> Key: TRAFODION-2241
> URL: https://issues.apache.org/jira/browse/TRAFODION-2241
> Project: Apache Trafodion
> Issue Type: Bug
> Affects Versions: 2.0-incubating
> Environment: 9 node CDH 5.4.10 cluster
> Dell R420
> - 2 x E5-2430v2 2.5GHz (6C 12T)
> - 64GB RAM
> - 4x1TB SATA
> Reporter: Aaron Molitor
> Assignee: Selvaganesan Govindarajan
>
> h5. Attempting to run the following:
> {noformat}
> SQL>@10.sql
> SQL>SELECT
> +> C_CUSTKEY,
> +> C_NAME,
> +> SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE,
> +> C_ACCTBAL,
> +> N_NAME,
> +> C_ADDRESS,
> +> C_PHONE,
> +> C_COMMENT
> +>FROM
> +> TPCH.CUSTOMER,
> +> TPCH.ORDERS,
> +> TPCH.LINEITEM,
> +> TPCH.NATION
> +>WHERE
> +> C_CUSTKEY = O_CUSTKEY
> +> AND L_ORDERKEY = O_ORDERKEY
> +> AND O_ORDERDATE >= TO_DATE('1993-10-01')
> +> AND O_ORDERDATE < TO_DATE('1994-01-01')
> +> 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
> +>LIMIT 20
> +>;
> {noformat}
> h5. query statistics are (at the time of writing this):
> {noformat}
> SQL>GET STATISTICS FOR QID MXID11004009150212341331081582351000000000206U3333300_884_SQL_CUR_6;
> Qid MXID11004009150212341331081582351000000000206U3333300_884_SQL_CUR_6
> Compile Start Time 2016/09/23 03:23:15.974896
> Compile End Time 2016/09/23 03:23:17.013202
> Compile Elapsed Time 0:00:01.038306
> Execute Start Time 2016/09/23 03:23:17.014161
> Execute End Time -1
> Execute Elapsed Time 1:29:13.390991
> State OPEN
> Rows Affected -1
> SQL Error Code 0
> Stats Error Code 0
> Query Type SQL_SELECT_NON_UNIQUE
> Sub Query Type SQL_STMT_NA
> Estimated Accessed Rows 0
> Estimated Used Rows 0
> Parent Qid NONE
> Parent Query System NONE
> Child Qid NONE
> Number of SQL Processes 69
> Number of Cpus 9
> Transaction Id -1
> Source String SELECT
> C_CUSTKEY,
> C_NAME,
> SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE,
> C_ACCTBAL,
> N_NAME,
> C_ADDRESS,
> C_PHONE,
> C_COMMENT
> FROM
> TPCH.CUSTOMER,
> TPCH.ORDERS,
> TPCH.LINEITEM,
> TPCH.NATION
> WHERE
> C_CUSTKEY = O_CUSTKEY
> AND L_ORDER
> SQL Source Length 536
> Rows Returned 0
> First Row Returned Time -1
> Last Error before AQR 0
> Number of AQR retries 0
> Delay before AQR 0
> No. of times reclaimed 0
> Cancel Time -1
> Last Suspend Time -1
> Stats Collection Type OPERATOR_STATS
> SQL Process Busy Time 3,766,170,959
> UDR Process Busy Time 0
> SQL Space Allocated 62,778 KB
> SQL Space Used 60,654 KB
> SQL Heap Allocated 910,579 KB
> SQL Heap Used 909,016 KB
> EID Space Allocated 0 KB
> EID Space Used 0 KB
> EID Heap Allocated 0 KB
> EID Heap Used 0 KB
> Processes Created 0
> Process Create Time 0
> Request Message Count 168,700
> Request Message Bytes 28,540,176
> Reply Message Count 167,984
> Reply Message Bytes 5,115,694,240
> Scr. Overflow Mode MMAP
> Scr File Count 0
> Scr. Buffer Blk Size 0
> Scr. Buffer Blks Read 0
> Scr. Buffer Blks Written 0
> Scr. Read Count 0
> Scr. Write Count 0
>
> Table Name
> Records Accessed Records Used HBase/Hive HBase/Hive HBase/Hive IO HBase/Hive IO
> Estimated/Actual Estimated/Actual IOs IO MBytes Sum Time Max Time
> TRAFODION.TPCH.NATION
> 0 25
> 25 25 16 0 106,049 15,340
> TRAFODION.TPCH.ORDERS
> 0 5,798,005
> 150,000,000 5,730,378 15,012 9,727 2,655,323,003 378,267,484
> TRAFODION.TPCH.LINEITEM
> 0 148,139,232
> 289,486,416 71,437,458 28,959 32,853 6,995,561,837 1,917,714,455
> TRAFODION.TPCH.CUSTOMER
> 0 15,000,000
> 80,000 31,963 16 7 3,611,993 529,225
> --- SQL operation complete.
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)