Re: Tunning help for plsql

that's a lot of processing. the database itself hopefully is tuned ..

but as for the code. for one thing, i can't see why your first two cursors can't be combined in one, e.g.

cursor c1_ccrn is
select distinct sy.pidm, sy.term_code, sy.tckn_crn

      from sytihst_bak sy, sztstdn sz
      where sy.pidm = sz.pidm
      and term_code = sz.term_code_eff;

then you can have 1 less nested cursor. or am i missing something.

then make sure all the individual queries involved are optimized (explain/tkprof), especially the innermost update. but have your dba check it out too to make sure the db is doing ok in terms of redo, rollback, and everything else.

hhuynh99_at_yahoo.com (Helen) wrote in message news:<c4d1ed11.0203251615.62120a_at_posting.google.com>...
> Hi, I need to reset the tckn_seq_no so that it's unique per
> pidm/term/tckn_crn.
>
> This script works but it's so slow. Any advice?
> Thanks in advance
>
> -------------------------------------
> DECLARE
>
> -- about 30K recs in here
> CURSOR c1 IS
> SELECT pidm, term_code_eff
> FROM sztstdn;
>
> CURSOR ccrn(v_pidm NUMBER, v_term VARCHAR2) IS
> select distinct pidm, term_code, tckn_crn
> from sytihst_bak
> where pidm=v_pidm
> and term_code = v_term;
>
> --about 60K rec in here
> CURSOR cseq(v_pidm NUMBER, v_term VARCHAR2, v_crn VARCHAR2) IS
> select pidm, term_code, record_type, tckn_crn, tckn_seq_no,
> checkseq
> from sytihst_bak
> where pidm=v_pidm
> and term_code = v_term
> and tckn_crn = v_crn
> for update of tckn_seq_no, checkseq;
>
>
> w_seq INTEGER;
>
>
> BEGIN
>
> FOR c1_cur IN c1 LOOP
>
> FOR ccrn_cur IN ccrn(c1_cur.pidm, c1_cur.term_code_eff) LOOP
>
> FOR cseq_cur IN cseq(ccrn_cur.pidm, ccrn_cur.term_code,
> ccrn_cur.tckn_crn) LOOP
>
> w_seq := ccrn%ROWCOUNT;
>
> UPDATE sytihst_bak
> SET tckn_seq_no = w_seq,
> checkseq = 'Y'
> WHERE current of cseq;
>
>
> END LOOP;
> COMMIT;
>
> END LOOP;
>
> END LOOP;
>
> EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line(SUBSTR(SQLERRM,1,2000));
> ROLLBACK;
>
> END;
> /
Received on Tue Mar 26 2002 - 19:54:55 CET