Tuning SQL

[lee]

Hi,
There is a script which takes 60 seconds to run. I am told that it should take less time so I need to tune it. Can somebody please help me? It's a question of my LIFE!! I am new and have not understood it properly.

Thanks.


/* 06/19/00 SR Initial Coding */
/* This script regenerates the ACCT_STATIC_DETAILS table */
/* 08/27/00 SR Changed where condition accessing party_relationship. */
/* Gary recommended that the CUSTBASENO records MUST have a */
/* party relationship entry if lec.pty_id is not null */
/* 08/28/00 SR Teamtrack - DBA0236472 to modify logic to obtain bank name */
/* This one uses party(p3) and bank identifier(bi) */
/* 10/22/00 SR Teamtrack - DBA0240259 modified logic for bank name */
/* 11/01/00 SR M Elgart's Request to add the account.fund_bank_nam column */
/* before searching in party (p3) table for bank name */

declare
start_time INTEGER;
end_time INTEGER;
total_time INTEGER;
chk_pk INTEGER;
current_dt DATE;
dups_at_start INTEGER;
dups_at_end INTEGER;
cursor c1 is
SELECT /*+ FIRST_ROWS ORDERED index(AP2 ACCOUNT_ACCOUNT_PARTY_FK)
index(ap3 ACCOUNT_ACCOUNT_PARTY_FK) */
a1.acct_id as acct_id,
a1.acct_sta_typ as acct_sta_typ,
a1.pty_id as pty_id,
a1.pty_sta_typ as pty_sta_typ,
a1.acct_no as acct_no,

-- Teamtrack - DBA0236472
-- Commented out code below for bank name as requested in Teamtrack DBA0236472
-- decode(a1.fund_bank_nam,null,(decode(a1.br_cod,'152',null,'OBBR',null,'CITIBANK')),
-- a1.fund_bank_nam) as bnk_nm,

-- Teamtrack - DBA0240259
-- nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' ')) as bnk_nm,

-- decode(a1.br_cod,
-- 'OBBR',decode(bi.bank_cod,'FW',p3.pty_nam,' '),
-- '152' ,decode(bi.bank_cod,'IS',p3.pty_nam,' '),
-- nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' '))) as bnk_nm,

nvl(a1.fund_bank_nam,nvl(p3.pty_nam,decode(a1.bank_rte_id,null,'CITIBANK',' '))) as bnk_nm,

nvl(ap1.acct_fam_nam,ap2.acct_fam_nam) as acct_nm,
a1.br_cod as br_cod,
p1.pty_nam as brn_nm,
p2.pty_id as clnt_id,
p2.pty_nam as clnt_ent_nm,
ap1.acct_fam_nam as ap1_acct_fam_nam,
ap2.acct_fam_nam as ap2_acct_fam_nam,
nvl(leg.lgl_nam,lec.lgl_nam) as lgl_nam,
nvl(leg.cust_ref_id,lec.cust_ref_id) as cust_ref_id,
lec.cust_ref_id as cust_bas_no,
leg.cust_ref_id as gfcid,
b1.br_tax_id as br_tax_id,
a1.iso_ccy_cod as acct_ccy,
a1.entprise_des as entprise_des,
a1.lst_stmt_dt as last_stmt_dt,
a1.acct_typ as acct_typ,
a1.dr_acct_ind as dr_ind_prn,
p1.pty_id as brn_id,
leg.pty_id as gfcid_id,
lec.pty_id as cust_bas_no_id,
ap1.pty_id as ap_pty_id,
ap1.acct_id as ap_acct_id,
ap1.pty_sta_typ as ap_pty_sta_typ,
ap1.pty_role_typ as ap_pty_role_typ,
ap1.acct_sta_typ as ap_acct_sta_typ,
ap1.acct_pty_sta_typ as ap_acct_pty_sta_typ
from account_party ap1,
account a1,
account_party ap2,
account_party ap3,
branch b1,
party p2,
party p1,
legal_entity leg,
legal_entity lec,
bank_identifier bi,
party p3
where
a1.acct_sta_typ = 'PROCESSED' and
a1.pty_id = b1.pty_id and
a1.pty_sta_typ = b1.pty_sta_typ and
a1.br_cod = b1.br_cod and
b1.pty_id = p1.pty_id and
b1.pty_sta_typ = p1.pty_sta_typ and
p1.pty_sta_typ = 'PROCESSED' and
p1.pty_typ = 'BRCH' and
a1.acct_id = ap1.acct_id and
a1.acct_sta_typ = ap1.acct_sta_typ and
ap1.acct_pty_sta_typ = 'PROCESSED' and
ap1.pty_sta_typ = 'PROCESSED' and
ap1.acct_role_typ = 'ACCESS' and
ap1.pty_role_typ = 'CLNT' and
ap1.pty_id = p2.pty_id and
ap1.pty_sta_typ = p2.pty_sta_typ and
a1.acct_id = ap2.acct_id(+) and
a1.acct_sta_typ = ap2.acct_sta_typ(+) and
ap2.acct_role_typ(+) = 'OWNER' and
ap2.pty_role_typ(+) = 'LGLBU' and
ap2.pty_sta_typ(+) = 'PROCESSED' and
ap2.pty_id = leg.pty_id(+) and
ap2.pty_sta_typ = leg.pty_sta_typ(+) and
leg.cust_ref_typ(+) = 'GFCID' and
a1.acct_id = ap3.acct_id(+) and
a1.acct_sta_typ = ap3.acct_sta_typ(+) and
ap3.acct_role_typ(+) = 'GROUP' and
ap3.pty_role_typ(+) = 'LGLBU' and
ap3.pty_sta_typ(+) = 'PROCESSED' and
ap3.pty_id = lec.pty_id(+) and
ap3.pty_sta_typ = lec.pty_sta_typ(+) and
lec.cust_ref_typ(+) = 'CUSTBASENO' and
a1.bank_rte_id = bi.bank_rte_id(+) and
bi.pty_id = p3.pty_id(+) and
((bi.bank_cod = decode(a1.br_cod,'OBBR','FW','152','IS',bi.bank_cod)) or
(bi.bank_cod is null)) and
(lec.pty_id is null or
exists ( select 1 from party_relationship pr
where lec.pty_id = pr.pty_id and
pr.rel_pty_id = b1.pty_id));


Begin
start_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Creating acct_static_details For ' ||to_char(sysdate,'DD-MON-YYYY
HH24:MI:SS')||'.');

select sysdate into current_dt from dual;

select count(*) into dups_at_start from acct_static_details_exceptions;

delete from acct_static_details;

for c1rec in c1 loop
SELECT count(*) into chk_pk
FROM acct_static_details asd
WHERE c1rec.clnt_id = asd.clnt_id
AND c1rec.acct_id = asd.acct_id
AND c1rec.pty_id = asd.pty_id;

if chk_pk = 0 then
INSERT INTO acct_static_details
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP ) ;
else
INSERT INTO acct_static_details_exceptions
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP
,current_dt) ;
end if;

end loop;
commit;

select count(*) into dups_at_end from acct_static_details_exceptions;

if dups_at_end > dups_at_start then
dbms_output.put_line('Duplicate Rows Found.'||user);
end if;

end_time := DBMS_UTILITY.GET_TIME;

total_time := (end_time - start_time)/100;

dbms_output.put_line('Acct_static_details created successfully at ' ||to_char(sysdate,'DD-MON-YYYY
HH24:MI:SS')||'.');
dbms_output.put_line ('Script took '|| total_time ||' seconds');
Exception
When NO_data_found then
dbms_output.put_line('No Rows Found.'||user);
rollback;
When dup_val_on_index then
dbms_output.put_line('Duplicate Rows Found.'||user);
rollback;
When Others then
dbms_output.put_line('Error : '||user||sqlerrm);
rollback;

End;
/

[pwoneill]

quite a lot to sort through in one pass :)

first, run explain plan on the large query which is used to create the cursor and see what's going on, maybe it's not the optimized. also just time that query alone to see how much of the 60 seconds that takes.

you might get minor gains from using count(1) or count(ROWNUM) instead of count(*) in the selects that use it. they accomplish the same thing but count(*) is slower.

do that and give more feedback.

one thing which would save you a query for each record is to put a block around the inserts, and catch the primary key violation exception, instead of checking ahead of time. eg, something like this

<font face="courier">
for rec in cursor loop
begin
insert into good_table (columns) values (values);
exception
when exception_for_primary_key_violation
insert into exception_table (columns) values (values);
end;
end loop
</font>

just a basic example. i'm not sure of the exception off the top of my head, you might just use OTHERS. it might not save a ton, but it will keep you from using one count query for every record processed.

[pwoneill]

oh also. this might require a lot more work, but look into use Pro*C for something like this, it might also provide performance gains over PL/SQL.

[lee]

Thanks a lot for the qnswer. I was wondering as to where do I write the Exception clause. After an insert and before else?

Please let me know.

Also, how do I get Pro*C?

thanks.

[pwoneill]

you would write the exception similar to what I gave in the example. with your code it would look something like this

Pro*C compilers come with the Oracle installation I believe, unless they weren't installed by whoever did it. Check around and see if they're available, you'll also need to learn how to write it. If you're not familiar with C to begin, let alone the SQL aspects with it might be a lot more development time than it's worth.

However it would be a nice learning experience :)

<font face="courier">
for c1rec in c1 loop

-- altered code under here, notice no select or if statement
-- make sure to keep the original file before making changes
-- this is off the top of my head so try to understand what it's doing,
-- then figure out if it's correct, or make changes if you need
BEGIN

INSERT INTO acct_static_details
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP ) ;
EXCEPTION

WHEN OTHERS THEN

INSERT INTO acct_static_details_exceptions
VALUES
(c1rec.ACCT_ID
,c1rec.ACCT_STA_TYP
,c1rec.PTY_ID
,c1rec.PTY_STA_TYP
,c1rec.ACCT_NO
,c1rec.BNK_NM
,c1rec.ACCT_NM
,c1rec.BR_COD
,c1rec.BRN_NM
,c1rec.CLNT_ID
,c1rec.CLNT_ENT_NM
,c1rec.AP1_ACCT_FAM_NAM
,c1rec.AP2_ACCT_FAM_NAM
,c1rec.LGL_NAM
,c1rec.CUST_REF_ID
,c1rec.CUST_BAS_NO
,c1rec.GFCID
,c1rec.BR_TAX_ID
,c1rec.ACCT_CCY
,c1rec.ENTPRISE_DES
,c1rec.LAST_STMT_DT
,c1rec.ACCT_TYP
,c1rec.DR_IND_PRN
,c1rec.BRN_ID
,c1rec.GFCID_ID
,c1rec.CUST_BAS_NO_ID
,c1rec.AP_PTY_ID
,c1rec.AP_ACCT_ID
,c1rec.AP_PTY_STA_TYP
,c1rec.AP_PTY_ROLE_TYP
,c1rec.AP_ACCT_STA_TYP
,c1rec.AP_ACCT_PTY_STA_TYP
,current_dt) ;
END;

end loop;
commit;
</font>

[lee]

Thanks and what about the Exception clause which is already in the script? Do I remove it or keep it as it is?

Thanks.

[pwoneill]

you can keep it as it's for more general exceptions.

the block i added will only catch exceptions for the insert, which is what you want to do.

the exception block that i added might need to be a bit more specific for a the program, you probablly want to specifically catch the primary key violation exception, and let any other exceptions drop down to the OTHERS clause of the one that's already there.

[lee]

Thanks a lot. Just another question. Do I have to do anything to the Hints defined?

Thanks.

[pwoneill]

That's where explain plan, and tuning your query come into play. Odds are your biggest gains will come from tuning the query, but there's not much I can say without knowing more about it.

Time the actual query alone in sqlplus or a dummy procedure, find out how much of the 60 seconds it uses.

If the Query takes 50 seconds, and the rest of it takes 10 seconds, even if changing the loop to use exceptions instead of the select/if statement doubles performance, you're still only saving 5 seconds. On the other hand if you make the query go twice as fast you save 25 seconds. You need to break down and time individual stages of the procedure to see which sections take the largest ammounts of time and concentrate on those first.

After you find out how long the query is, run explain plan to see how it's working. Read about using explain plan in the oracle documentation. Make sure it's using indexes (or even has indexes to work with), etc. I'm not an expert SQL tuner but that's a good place to start. If you want, post the results here and I, or someone more qualified will be glad to help.

Make sure to do some work and learn/understand what you're doing. As the saying goes, give a man a fish and he'll eat for a day. Teach a man to fish and he'll eat forever. Or something :)