Hi All, can someone help me to tune this peice of code. It is taking 45 minutes to complete the execution as of now.
We have tried taking off the loop and creating 5 different create statements but did gain just 2 minutes
Each of the table have and average of 10 million records.
DECLARE
CURSOR cur_drp_set1_tbl
IS
SELECT DISTINCT TBL_NAME,ETL_SYN_TBL_NAME FROM WC_SYNO_TARGET_TBL_SWAP_TMP WHERE COMMITTED = 0
AND ETL_SYN_TBL_NAME IN ('ACCTG_LINE','COST_ACCTG_LINE','OTHER',
'ACCT_BUDGET','ACCTG_LINE') order by TBL_NAME;
CURSOR cur_crt_set1_tbl
IS
SELECT DISTINCT A.TBL_REFRSH , B.TBL_RECRT
FROM (SELECT DISTINCT ETL_SYN_TBL_NAME , TBL_NAME AS TBL_REFRSH FROM WC_SYNO_TARGET_TBL_SWAP_TMP WHERE COMMITTED = 1
AND ETL_SYN_TBL_NAME IN ('ACCTG_LINE','COST_ACCTG','OTHER',
'ACCT_BUDGET','ACCTG_LINE')) A,
(SELECT DISTINCT ETL_SYN_TBL_NAME , TBL_NAME AS TBL_RECRT FROM WC_SYNO_TARGET_TBL_SWAP_TMP WHERE COMMITTED = 0
AND ETL_SYN_TBL_NAME IN ('ACCTG_LINE','COST_ACCTG_LINE','OTHER',
'ACCT_BUDGET','ACCTG_LINE')) B
WHERE A.ETL_SYN_TBL_NAME = B.ETL_SYN_TBL_NAME ORDER BY A.TBL_REFRSH;
BEGIN
FOR i IN cur_drp_set1_tbl
LOOP
EXECUTE IMMEDIATE 'DROP TABLE '||i.TBL_NAME;
END LOOP;
FOR j IN cur_crt_set1_tbl
LOOP
EXECUTE IMMEDIATE 'CREATE TABLE '||J.TBL_RECRT||' AS SELECT * FROM '||J.TBL_REFRSH;
END LOOP;
END;
Eeek! Dynamically dropping and creating tables like this is a recipe for SQL injection!
Based on your IN-lists, it looks like there are only around five tables to rebuild. It's MUCH safer to list them out one-by-one in code.
That said, dropping and re-creating tables is generally a bad idea in Oracle Database.
If you need to repopulate data, truncating then reinsert is safer:
truncate table ...;
insert into ...
select ... from ...;
truncate table ...;
insert into ...;
select ... from ...;
Once you've made your code injection-proof, the starting point for tuning is the same as any tuning exercise: trace your current process to see where the time is going!
Do this as follows:
exec DBMS_monitor.session_trace_enable ( null, null, true, true );
***your code here***
exec DBMS_monitor.session_trace_disable;
Then dig out the trace file and use TKPROF or similar to analyze it. Read more about how to do this at:
https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof If you need help to understand this, get the timing and execution plans for the SQL from the formatted trace file and post them here.