I have one query as below where we are performing an insertion in one empty table. The insertion statement is as below.The below insertion query is currently taking more than 20 hrs for its insertion. The tables which are used in the join i.e.JEDI_CDW_DB.CALL_HIST has UPI defined on columns used in join query and also table JEDI_AGR_DB.CDR_COST_HIST_PRE has UPI defined on columns used in join. But still there is issue with query performance.
Thanks in advance.
Please find below the DDLs of the source and target tables.
1) Source Tables(JEDI_CDW_DB.CALL_HIST and JEDI_AGR_DB.CDR_COST_HIST_PRE)
2) Target table - JEDI_AGR_DB.CALL_DAY_IMAGE_1
DDL of JEDI_CDW_DB.CALL_HIST:
Is the select exeucting fast ? if true, then is the insert taking time ? This is a Insert sel query with no PI defined for the select part, so the first column will be the PI. If that is skewed, the insert will run longer.
No the select operation is also not executing fast. Both select and insert are taking time.
Can you suggest any ways through which I can improve the query performance. As you can see in my earlier post, the explain plan shows a high number of hrs.
Few of the basic things that you need to see are :
1. Run : Diagnostic helpstats on for session
2. Run the explain of the query, See if optimizer suggests you some stats. Plese collect the stats with high confidence. This should help if the stats are missing.
3. I see that a lot of duplication and redistribution is happening in different steps. also, the rows involved are very high here. billions in some cases. So, please check if the joining columns are correct. You must be doing Pi-No Pi join, so a lot of duplication/redistribution is happening
If all of these dont help, then you need to check the logic of the queey, if you joining correct tables on correct columns.
I ran the command Diagnostic helpstats on for session on that query last week and found some collect stats as mentioned below required. So, accordingly I ran the collect stats below as suggested :
COLLECT STATISTICS jedi_cdw_db.call_hist COLUMN (CALL_START_DT ,CALL_START_TM ,SWITCH_ID ,CALL_RECORD_SEQ_NO);
Your table is defined as a SET table, has a UPI defined on it. Maybe it is doing a full row dupe check for each insert? Can you try creating a copy of this table as a MULTISET table and do the insert?
First you should check the actual runtime/resource usage for each step using QryLogStepsV.
The UPI on
call_hist cannot be used for the join, what are the demographics for
accs_meth_id, is it actually needed in the PI?
The stats can probably be collected USING SAMPLE 2 PERCENT instead of full stats, but I doubt it will improve performance.