RE: Slow Inserts

I got James to give me more of the code that causes the problem.

He said this was the part that takes the 7.5 hours. He said the rest runs reasonably good.

I hope this gives us enough to work on.

Michael:

The SQL listed below is the part that takes so long.

/* -------------------------------------------------------------------------*/
/*  Create the T_LDGR_FACT_RPT table                                        */ 
/* -------------------------------------------------------------------------*/

Declare

    v_acct_subset_cd t_rpt_acct_subset.acct_subset_cd%TYPE;

/* -------------------------------------------------------------------------*/
/* Cursor to select all current month rows from the t_ldgr_fact table       */ 
/* -------------------------------------------------------------------------*/

    Cursor c_rpt_acct_subset IS
        Select Distinct acct_subset_cd
          From T_RPT_ACCT_SUBSET;
 

Begin

/* -------------------------------------------------------------------------*/
/* Open cursor                                                              */ 
/* -------------------------------------------------------------------------*/

    Open c_rpt_acct_subset;
     
/* -------------------------------------------------------------------------*/
/* Start of loop                                                            */ 
/* -------------------------------------------------------------------------*/

  LOOP          Fetch c_rpt_acct_subset INTO v_acct_subset_cd;

    EXIT WHEN c_rpt_acct_subset%NOTFOUND;        

    Insert
    Into t_ldgr_fact_rpt

       (prod_id_cd,
	proces_dt_txt,
	acct_subset_cd,
	dept_id_cd,
	biz_unt_nm,
	scenr_id,
	mtd_amt,
	ytd_amt,
	qtd_amt,
	mix_annl_mtd_amt,
	mix_annl_ytd_amt,
	mix_annl_qtd_amt,
	actl_annl_mtd_amt,
	actl_annl_ytd_amt,
	actl_annl_qtd_amt
	)
    Select /*+ index (a I_LDGR_FACT_DENORM ) */ 
	   a.prod_id_cd,
           a.proces_dt_txt,
           v_acct_subset_cd,
           a.dept_id_cd,
           a.biz_unt_nm,
           a.Scenr_id,
	   Sum(a.mtd_amt),
	   Sum(a.ytd_amt),
	   Sum(a.qtd_amt),
           Sum(a.mix_annl_mtd_amt),
	   Sum(a.mix_annl_ytd_amt),
	   Sum(a.mix_annl_qtd_amt),
           Sum(a.actl_annl_mtd_amt),
	   Sum(a.actl_annl_ytd_amt),
	   Sum(a.actl_annl_qtd_amt)
      From v_ldgr_fact_denorm_mapv2rpt a, t_rpt_acct_subset_temp b
        Where a.acct_subset_cd = b.acct_nbr
          And b.acct_subset_cd = v_acct_subset_cd
    Group By
           a.prod_id_cd,
           a.proces_dt_txt,
           a.dept_id_cd,
           a.biz_unt_nm,
           a.scenr_id;

/* -------------------------------------------------------------------------*/
/* End of the loop                                                          */ 
/* -------------------------------------------------------------------------*/

  End Loop;  

  Commit;

/* -------------------------------------------------------------------------*/
/* Close the cursor                                                         */ 
/* -------------------------------------------------------------------------*/

    Close c_rpt_acct_subset;

END;

--
Michael Kline, Principle Consultant
Business To Business Solutions
13308 Thornridge Ct
Midlothian, VA  23112
804-744-1545


> The information transmitted is intended only for the person or entity to

> which it is addressed and may contain confidential and/or privileged

> material.  If the reader of this message is not the intended recipient,

> you are hereby notified that your access is unauthorized, and any review,

> dissemination, distribution or copying of this message including any

> attachments is strictly prohibited.   If you are not the intended

> recipient, please contact the sender and delete the material from any

> computer.


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------