Details
-
Improvement
-
Status: Resolved
-
Major
-
Resolution: Duplicate
-
1.14.0, 1.15.0
-
None
-
Drill 1.14
Description
This issue was discovered while debugging a performance issue of a query that is run on an Oracle(via storage plugin) and Hive tables.
Listing the query as below that is taking nearly 4 hrs.
Oracle table: e35_eos.finapp.sales
Hive table: hive.temp.sales
SELECT Sum(source_cnt) source_cnt,
Sum(target_cnt) target_cnt,
sale_id,
prod_id,
cust_id,
time_id,
channel_id,
promo_id,
quantity_sold,
amount_sold,
createddate,
modifieddate
FROM (SELECT 1 source_cnt,
0 target_cnt,
sale_id,
prod_id,
cust_id,
time_id,
Trim(channel_id) CHANNEL_ID,
promo_id,
quantity_sold,
amount_sold,
createddate,
modifieddate
FROM e35_eos.finapp.sales
UNION ALL
SELECT 0 source_cnt,
1 target_cnt,
sale_id,
prod_id,
cust_id,
time_id,
Trim(channel_id) CHANNEL_ID,
promo_id,
quantity_sold,
amount_sold,
createddate,
modifieddate
FROM hive.temp.sales
WHERE top_rank = 1
AND header__change_oper <> 'D')
GROUP BY sale_id,
prod_id,
cust_id,
time_id,
channel_id,
promo_id,
quantity_sold,
amount_sold,
createddate,
modifieddate
HAVING Sum(source_cnt) <> Sum(target_cnt)
LIMIT 1000
The Physical Plan shows the step for Operator(02-08). But the "operator profile", is missing the step.
02-08 Jdbc(sql=[SELECT 1 "source_cnt", 0 "target_cnt", "SALE_ID", "PROD_ID", "CUST_ID", "TIME_ID", TRIM(BOTH ' ' FROM "CHANNEL_ID") "CHANNEL_ID", "PROMO_ID", "QUANTITY_SOLD", "AMOUNT_SOLD", "CREATEDDATE", "MODIFIEDDATE" FROM "FINAPP"."SALES"]) : rowType = RecordType(INTEGER source_cnt, INTEGER target_cnt, DECIMAL(0, 0) SALE_ID, DECIMAL(6, 0) PROD_ID, DECIMAL(0, 0) CUST_ID, TIMESTAMP(0) TIME_ID, VARCHAR(65535) CHANNEL_ID, DECIMAL(6, 0) PROMO_ID, DECIMAL(3, 0) QUANTITY_SOLD, DECIMAL(10, 2) AMOUNT_SOLD, TIMESTAMP(0) CREATEDDATE, TIMESTAMP(0) MODIFIEDDATE): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io, 0.0 network, 0.0 memory}, id = 4648
Below is the attached profile for your reference:
