Last few day i explore my dremio community edition query works very slow.
Hi @ashik_88 Welcome to the Dremio community! We need to see why the query is slow. Are you able to click on the job and download the profile and attach the zip file. We can review and see why the query is running slow
513c3a74-efd7-453e-a2f6-6aa09e5feda9.zip (35.2 KB)
@balaji.ramaswamy ,Downloaded profile has been attach. Previously this view run only 1-1.5 min.
@ashik_88 The entire time is spent on the push down SQL below that ran on the source RDBMS which is Oracle in your case. Can you do 3 things?
- Run the below query in Oracle directly and see how much time it takes, run it a few times in the day during times when the Oracle server is busy and not so busy
- Do a EXPLAIN PLAN FOR on this SQL and see if the right plan is used
- Try using EXTERNAL QUERY - Querying Relational-Database Sources Directly | Dremio Documentation
SELECT "ENTERDATE", "SOL_ID", "GL_CODE", "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", "ACCOUNTNUMBER", "ACCT_NAME", "CURRENCY", CAST("BALANCE" AS NUMBER(38, 4)) "BALANCE", CAST("BALANCE_BDT" AS NUMBER(38, 6)) "BALANCE_BDT"
FROM (SELECT *
FROM ((SELECT *
FROM ((SELECT CAST(DATE '2025-12-25' AS TIMESTAMP) "ENTERDATE", "SOL_ID", "GL_CODE", "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", "ACCOUNTNUMBER", "ACCT_NAME", "CURRENCY", CAST("BALANCE" AS NUMBER(38, 4)) "BALANCE", "BALANCE_BDT"
FROM (SELECT "t0"."SOL_ID", "t0"."GL_CODE", "t0"."GL_SUB_HEAD_CODE", "t0"."GL_SUB_HEAD_DESC", "t0"."ACCOUNTNUMBER", "t0"."ACCT_NAME", "t0"."ACCT_CRNCY_CODE" "CURRENCY", "STG_EAB_FULL"."BALANCE", ("STG_EAB_FULL"."BALANCE" * "t0"."EXPR$1") "BALANCE_BDT"
FROM (SELECT "t"."ACID", "t"."SOL_ID", "t"."ACCT_NAME", "t"."GL_SUB_HEAD_CODE", "t"."ACCT_CRNCY_CODE", "t"."GL_SUB_HEAD_DESC", "t"."GL_CODE", "t"."ACCOUNTNUMBER", "FND_FX"."EXPR$1"
FROM (SELECT "DIM_ACCOUNT"."ACID", "DIM_ACCOUNT"."SOL_ID", "DIM_ACCOUNT"."ACCT_NAME", "DIM_ACCOUNT"."GL_SUB_HEAD_CODE", "DIM_ACCOUNT"."ACCT_CRNCY_CODE", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD"."GL_CODE", "DIM_ACCOUNT"."ACCOUNTNUMBER"
FROM (SELECT "ACID", "SOL_ID", "ACCT_NAME", "GL_SUB_HEAD_CODE", "ACCT_CRNCY_CODE", SUBSTR("FORACID", 4, 10) "ACCOUNTNUMBER"
FROM (SELECT "DIM_ACCOUNT"."ACID", "DIM_ACCOUNT"."SOL_ID", "DIM_ACCOUNT"."FORACID", "DIM_ACCOUNT"."ACCT_NAME", "DIM_ACCOUNT"."GL_SUB_HEAD_CODE", "DIM_ACCOUNT"."ACCT_OWNERSHIP", "DIM_ACCOUNT"."ACCT_CLS_DATE", "DIM_ACCOUNT"."ACCT_CRNCY_CODE"
FROM "DWH"."DIM_ACCOUNT") "DIM_ACCOUNT"
WHERE "ACCT_OWNERSHIP" = 'O' AND "GL_SUB_HEAD_CODE" <> '41018' AND (("SOL_ID" = '999' OR "SOL_ID" = '888')) AND (("ACCT_CLS_DATE" IS NULL OR "ACCT_CLS_DATE" > TIMESTAMP '2025-12-25 00:00:00.000'))) "DIM_ACCOUNT"
INNER JOIN (SELECT "SOL_ID", "GL_SUB_HEAD_CODE", "CRNCY_CODE", "GL_SUB_HEAD_DESC", "GL_CODE"
FROM (SELECT "DIM_GL_SUB_HEAD"."SOL_ID", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_CODE", "DIM_GL_SUB_HEAD"."CRNCY_CODE", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD"."GL_CODE"
FROM "DWH"."DIM_GL_SUB_HEAD") "DIM_GL_SUB_HEAD"
WHERE "GL_SUB_HEAD_CODE" <> '41018' AND (("SOL_ID" = '999' OR "SOL_ID" = '888'))) "DIM_GL_SUB_HEAD" ON (("DIM_ACCOUNT"."SOL_ID" = "DIM_GL_SUB_HEAD"."SOL_ID" AND "DIM_ACCOUNT"."GL_SUB_HEAD_CODE" = "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_CODE") AND "DIM_ACCOUNT"."ACCT_CRNCY_CODE" = "DIM_GL_SUB_HEAD"."CRNCY_CODE")) "t"
INNER JOIN (SELECT "FXD_CRNCY_CODE", TRUNC("VAR_CRNCY_UNITS", 4) "EXPR$1"
FROM (SELECT "FND_FX"."BUSINESS_DATE", "FND_FX"."FXD_CRNCY_CODE", "FND_FX"."VAR_CRNCY_UNITS"
FROM "DWH"."FND_FX") "FND_FX"
WHERE "BUSINESS_DATE" = TIMESTAMP '2025-12-25 00:00:00.000') "FND_FX" ON "t"."ACCT_CRNCY_CODE" = "FND_FX"."FXD_CRNCY_CODE") "t0"
INNER JOIN (SELECT "ACID", TRUNC("TRAN_DATE_BAL", 4) "BALANCE"
FROM (SELECT "STG_EAB_FULL"."TRAN_DATE_BAL", "STG_EAB_FULL"."EOD_DATE", "STG_EAB_FULL"."END_EOD_DATE", "STG_EAB_FULL"."ACID"
FROM "DWH"."STG_EAB_FULL") "STG_EAB_FULL"
WHERE "EOD_DATE" <= TIMESTAMP '2025-12-25 00:00:00.000' AND "END_EOD_DATE" >= TIMESTAMP '2025-12-25 00:00:00.000') "STG_EAB_FULL" ON "t0"."ACID" = "STG_EAB_FULL"."ACID") "t1"
GROUP BY "SOL_ID", "GL_CODE", "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", "ACCOUNTNUMBER", "ACCT_NAME", "CURRENCY", "BALANCE", "BALANCE_BDT")
UNION ALL
(SELECT CAST(DATE '2025-12-25' AS TIMESTAMP) "ENTERDATE", "SOL_ID", "GL_CODE", "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", NULL "ACCOUNTNUMBER", NULL "ACCT_NAME", "CURRENCY", SUM("$f5") "BALANCE", SUM("$f6") "BALANCE_BDT"
FROM (SELECT "t5"."SOL_ID", "t5"."GL_CODE", "t5"."GL_SUB_HEAD_CODE", "t5"."GL_SUB_HEAD_DESC", "t5"."ACCT_CRNCY_CODE" "CURRENCY", "FACT_DAILY_BALANCES"."$f5", ("FACT_DAILY_BALANCES"."$f5" * "t5"."EXPR$0") "$f6"
FROM (SELECT "t4"."ACID", "t4"."SOL_ID", "t4"."GL_SUB_HEAD_CODE", "t4"."ACCT_CRNCY_CODE", "t4"."GL_SUB_HEAD_DESC", "t4"."GL_CODE", "FND_FX0"."EXPR$0"
FROM (SELECT "DIM_ACCOUNT0"."ACID", "DIM_ACCOUNT0"."SOL_ID", "DIM_ACCOUNT0"."GL_SUB_HEAD_CODE", "DIM_ACCOUNT0"."ACCT_CRNCY_CODE", "DIM_GL_SUB_HEAD0"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD0"."GL_CODE"
FROM (SELECT "ACID", "SOL_ID", "GL_SUB_HEAD_CODE", "ACCT_CRNCY_CODE"
FROM (SELECT "DIM_ACCOUNT"."ACID", "DIM_ACCOUNT"."SOL_ID", "DIM_ACCOUNT"."GL_SUB_HEAD_CODE", "DIM_ACCOUNT"."ACCT_OWNERSHIP", "DIM_ACCOUNT"."ACCT_CLS_FLG", "DIM_ACCOUNT"."ACCT_CLS_DATE", "DIM_ACCOUNT"."ACCT_CRNCY_CODE"
FROM "DWH"."DIM_ACCOUNT") "DIM_ACCOUNT0"
WHERE "ACCT_OWNERSHIP" <> 'O' AND (("SOL_ID" = '999' OR "SOL_ID" = '888')) AND ((("ACCT_CLS_DATE" IS NULL OR "ACCT_CLS_DATE" > TIMESTAMP '2025-12-25 00:00:00.000') OR "ACCT_CLS_FLG" = 'N'))) "DIM_ACCOUNT0"
INNER JOIN (SELECT "SOL_ID", "GL_SUB_HEAD_CODE", "CRNCY_CODE", "GL_SUB_HEAD_DESC", "GL_CODE"
FROM (SELECT "DIM_GL_SUB_HEAD"."SOL_ID", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_CODE", "DIM_GL_SUB_HEAD"."CRNCY_CODE", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD"."GL_CODE"
FROM "DWH"."DIM_GL_SUB_HEAD") "DIM_GL_SUB_HEAD0"
WHERE "SOL_ID" = '999' OR "SOL_ID" = '888') "DIM_GL_SUB_HEAD0" ON (("DIM_ACCOUNT0"."SOL_ID" = "DIM_GL_SUB_HEAD0"."SOL_ID" AND "DIM_ACCOUNT0"."GL_SUB_HEAD_CODE" = "DIM_GL_SUB_HEAD0"."GL_SUB_HEAD_CODE") AND "DIM_ACCOUNT0"."ACCT_CRNCY_CODE" = "DIM_GL_SUB_HEAD0"."CRNCY_CODE")) "t4"
INNER JOIN (SELECT "FXD_CRNCY_CODE", TRUNC("VAR_CRNCY_UNITS", 4) "EXPR$0"
FROM (SELECT "FND_FX"."BUSINESS_DATE", "FND_FX"."FXD_CRNCY_CODE", "FND_FX"."VAR_CRNCY_UNITS"
FROM "DWH"."FND_FX") "FND_FX0"
WHERE "BUSINESS_DATE" = TIMESTAMP '2025-12-25 00:00:00.000') "FND_FX0" ON "t4"."ACCT_CRNCY_CODE" = "FND_FX0"."FXD_CRNCY_CODE") "t5"
INNER JOIN (SELECT "ACID", TRUNC(CASE WHEN ("SCHEME_CATEGORY" = 'TL' OR "SCHEME_CATEGORY" = 'OL') THEN ("TRAN_DATE_BAL" * -1) ELSE CAST("TRAN_DATE_BAL" AS NUMBER(38, 4)) END, 4) "$f5"
FROM (SELECT "FACT_DAILY_BALANCES"."BUSINESS_DATE", "FACT_DAILY_BALANCES"."ACID", "FACT_DAILY_BALANCES"."SCHEME_CATEGORY", "FACT_DAILY_BALANCES"."TRAN_DATE_BAL"
FROM "DWH"."FACT_DAILY_BALANCES") "FACT_DAILY_BALANCES"
WHERE "BUSINESS_DATE" = TIMESTAMP '2025-12-25 00:00:00.000' AND (((((("SCHEME_CATEGORY" = 'TL' OR "SCHEME_CATEGORY" = 'OL') OR "SCHEME_CATEGORY" = 'CL') OR "SCHEME_CATEGORY" = 'SBA') OR "SCHEME_CATEGORY" = 'TDA') OR "SCHEME_CATEGORY" = 'CAA'))) "FACT_DAILY_BALANCES" ON "t5"."ACID" = "FACT_DAILY_BALANCES"."ACID") "t6"
GROUP BY "SOL_ID", "GL_CODE", "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", "CURRENCY")) "t9")
UNION ALL
(SELECT CAST(DATE '2025-12-25' AS TIMESTAMP) "ENTERDATE", "SOL_ID", "GL_CODE", CAST('41018' AS VARCHAR(4000)) "GL_SUB_HEAD_CODE", "GL_SUB_HEAD_DESC", NULL "ACCOUNTNUMBER", NULL "ACCT_NAME", "CURRENCY", CAST("BALANCE" AS NUMBER(38, 4)) "BALANCE", "BALANCE_USD"
FROM (SELECT "t11"."SOL_ID", "t11"."GL_CODE", "t11"."GL_SUB_HEAD_DESC", "t11"."ACCT_CRNCY_CODE" "CURRENCY", "STG_EAB_FULL0"."BALANCE", ("STG_EAB_FULL0"."BALANCE" * "t11"."EXPR$0") "BALANCE_USD"
FROM (SELECT "t10"."ACID", "t10"."SOL_ID", "t10"."ACCT_CRNCY_CODE", "t10"."GL_SUB_HEAD_DESC", "t10"."GL_CODE", "FND_FX1"."EXPR$0"
FROM (SELECT "DIM_ACCOUNT1"."ACID", "DIM_ACCOUNT1"."SOL_ID", "DIM_ACCOUNT1"."ACCT_CRNCY_CODE", "DIM_GL_SUB_HEAD1"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD1"."GL_CODE"
FROM (SELECT "ACID", "SOL_ID", "GL_SUB_HEAD_CODE", "ACCT_CRNCY_CODE"
FROM (SELECT "DIM_ACCOUNT"."ACID", "DIM_ACCOUNT"."SOL_ID", "DIM_ACCOUNT"."GL_SUB_HEAD_CODE", "DIM_ACCOUNT"."ACCT_CLS_DATE", "DIM_ACCOUNT"."ACCT_CRNCY_CODE"
FROM "DWH"."DIM_ACCOUNT") "DIM_ACCOUNT1"
WHERE "GL_SUB_HEAD_CODE" = '41018' AND (("SOL_ID" = '999' OR "SOL_ID" = '888')) AND (("ACCT_CLS_DATE" IS NULL OR "ACCT_CLS_DATE" > TIMESTAMP '2025-12-25 00:00:00.000'))) "DIM_ACCOUNT1"
INNER JOIN (SELECT "SOL_ID", "GL_SUB_HEAD_CODE", "CRNCY_CODE", "GL_SUB_HEAD_DESC", "GL_CODE"
FROM (SELECT "DIM_GL_SUB_HEAD"."SOL_ID", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_CODE", "DIM_GL_SUB_HEAD"."CRNCY_CODE", "DIM_GL_SUB_HEAD"."GL_SUB_HEAD_DESC", "DIM_GL_SUB_HEAD"."GL_CODE"
FROM "DWH"."DIM_GL_SUB_HEAD") "DIM_GL_SUB_HEAD1"
WHERE "GL_SUB_HEAD_CODE" = '41018' AND (("SOL_ID" = '999' OR "SOL_ID" = '888'))) "DIM_GL_SUB_HEAD1" ON (("DIM_ACCOUNT1"."SOL_ID" = "DIM_GL_SUB_HEAD1"."SOL_ID" AND "DIM_ACCOUNT1"."GL_SUB_HEAD_CODE" = "DIM_GL_SUB_HEAD1"."GL_SUB_HEAD_CODE") AND "DIM_ACCOUNT1"."ACCT_CRNCY_CODE" = "DIM_GL_SUB_HEAD1"."CRNCY_CODE")) "t10"
INNER JOIN (SELECT "FXD_CRNCY_CODE", TRUNC("VAR_CRNCY_UNITS", 4) "EXPR$0"
FROM (SELECT "FND_FX"."BUSINESS_DATE", "FND_FX"."FXD_CRNCY_CODE", "FND_FX"."VAR_CRNCY_UNITS"
FROM "DWH"."FND_FX") "FND_FX1"
WHERE "BUSINESS_DATE" = TIMESTAMP '2025-12-25 00:00:00.000') "FND_FX1" ON "t10"."ACCT_CRNCY_CODE" = "FND_FX1"."FXD_CRNCY_CODE") "t11"
INNER JOIN (SELECT "ACID", TRUNC("TRAN_DATE_BAL", 4) "BALANCE"
FROM (SELECT "STG_EAB_FULL"."TRAN_DATE_BAL", "STG_EAB_FULL"."EOD_DATE", "STG_EAB_FULL"."END_EOD_DATE", "STG_EAB_FULL"."ACID"
FROM "DWH"."STG_EAB_FULL") "STG_EAB_FULL0"
WHERE "EOD_DATE" <= TIMESTAMP '2025-12-25 00:00:00.000' AND "END_EOD_DATE" >= TIMESTAMP '2025-12-25 00:00:00.000') "STG_EAB_FULL0" ON "t11"."ACID" = "STG_EAB_FULL0"."ACID") "t12"
GROUP BY "SOL_ID", "GL_CODE", "GL_SUB_HEAD_DESC", "CURRENCY", "BALANCE", "BALANCE_USD")) "t15") "t15"
ORDER BY "t15"."SOL_ID", "t15"."GL_CODE", "t15"."GL_SUB_HEAD_CODE", "t15"."ACCOUNTNUMBER", "t15"."CURRENCY"