Skip to Main Content
  • Questions
  • How to tune a create table that runs in loop

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ravi.

Asked: February 03, 2020 - 4:29 pm UTC

Answered by: Chris Saxon - Last updated: February 04, 2020 - 2:24 pm UTC

Category: PL/SQL - Version: Oracle 12C

Viewed 100+ times

You Asked

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;

and we said...

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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.