RE: Slow Inserts

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 22 Mar 2004 11:01:39 +0000
Message-Id: <s05ec7b5.075@bristol21.bristol.ac>

In addition to the comments that others have made about doing the whole thing in one insert, possibly with nologging, I'd wonder about the index hint in the sql that you give and whether it is really necessary.

Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805

> -----Original Message-----
> From: mkline1_at_comcast.net
> Sent: 19 March 2004 18:35
> To: mkline1_at_comcast.net; oracle-l_at_freelists.org
> Subject: 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
> -----------------------------------------------------------------
>
>

---

This email contains information intended for the addressee only. It may be confidential and may be the subject of legal and/or
professional privilege. Any dissemination, distribution, copyright or use of this
communication without prior permission of the sender is strictly prohibited.

---

---

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
-----------------------------------------------------------------

Received on Mon Mar 22 2004 - 05:29:04 CST