Tuning SQL

Hi,
The following is a Pl/SQL script which I need to tune. Also, I am told that in the 'for' loop, the 'select count(*).....' statement is not required. How do I tune it? Please let me know.

My job is at stake.

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

Man, you gotta be kidding. Without the tables and indexes and data on hand, nobody's going to be able to truly optimize something this complex. The only reason I'm looking at this at all is that I happen to be between fires and love doing this kinda stuff.

Basically, all I can do is give you some pointers.

First of all, a statement this large really needs some better formatting. Always put the new table fields on the right or the left (I prefer the right) to make it easier to read.

Okay, first real hint: ALWAYS JOIN EVERYTHING THAT'S JOIN-ABLE BETWEEN EVERY TABLE. I cannot stress this enough.

For example, you have:

AND b1.pty_id = a1.pty_id
as well as
AND p1.pty_id = b1.pty_id,

You also NEED to add:
AND p1.pty_id = a1.pty_id

Otherwise, the optimizer will never know that going from table a1 directly to table p1 is an option. The first rule of optimization is to always give the optimizer every possible chance to get it right. So cross-join every single field that is cross-joinable. This means joining to constant values as well (although Oracle can generally figure that one out on its own, IT CAN'T HURT!). Having too many joins will almost never hurt, but NOT having those legal joins often will hurt.

Moving on, if any of those joins are done simply to retrieve a name from a code table, or to only translate a single field, do them as sub-selects in the SELECT clause. While it is generally a better idea to pile everything into the WHERE clause, the rules change when outer-joins (as well as some other criteria like GROUP BYs, etc.) are involved. The optimizer is put at a disadvantage. If you know that all you are doing in the STATE table is translating the State_Cd to a State_Name, then do that in the SELECT clause so it will definitely be done last. I don't know if any of these fields fall under that heading, but branch sure looks like it does. I would pull that out and put it in the SELECT clause.

Since you can't do the 'join everything' trick where outer joins are concerned, you *really* need to know what your driving table is going to be. In this case, it is obviously a1. Therefore, wherever possible, join the outer-joines tables to *that* table. You have several cases where you are outer-joining to b1 where you could have used a1. Change these! Then, like I said above, pull b1 out to the SELECT clause. It appears to only be used to get some tax id.

Databases are set-oriented beasts. If there is *any* way of combining this into a single INSERT...SELECT statement by, say, using DECODES... DO IT. Unfortunately, you lose your ability to commit between rows.

The
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;
inside the loop should have
AND ROWNUM = 1
appended to it. You don't care about the count, just the existence of a record.

I don't know if there is any way to re-write that last part of the WHERE clause with the DECODES and ORs and NULL tests, but do it if you can - those will kill you.

Finally, if none of this works, go back to the business rules at hand. This just strikes me as too cumbersome to be the best approach. Without knowing more I can't help much on this, but it just *seems* wrong. There almost *has* to be an easier way.

Hope this helps

- Chris

Is there a way to remove the statement 'select count(*).....' because the rows are not ordered by clnt_id, acct_id and pty_id and the goal is to get them in order. How do I do this?


Thanks.

Sorry, but I missed that one completely.

The select count(*) exists to decide which of the two inserts to do.

Okay, okay , so I finally compared the two statements in the 'if'. Ther are the same. With the minor exception that one appears to insert the current datetime while the other does not.

Another point - *comment your code*. So you do a count against the table on every single record just to decide whether or not to stick a date into one field? You have to tell us if that is necessary. I have no idea why you would be doing that, so how would I know what to tell you?

Okay, now the rest seems to make a little more sense. You want the results from the cursor SELECT to come back in order of the 3 fields used in the SELECT COUNT(*) statement. For some reason, you want the first record of each set of records to have this datetime filled in. So...

F1 F2 F3 DateField
1 1 1 SYSDATE
1 1 1 NULL
1 1 2 SYSDATE
1 1 2 NULL
1 1 2 NULL
...

Is this correct?

What can we do for this? First of all, you will need to sort your cursor query in order of these fields that you care about. This will then do what you want. Is this the best way? Probably not, but I'd be more interested in whether or not it is truly necessary to do what you are trying to do. If your current SQL actually returns the records in the order you want, you are just plain lucky. If you want records in order, always add the ORDER BY.

Wait, after some more thought - even given that date field value, why do you care about the order? There has to be another field that you care about other than those 3. Just ordering by those 3 fields would be no different than a random order. Basically, there is no more point to me guessing at all this. If you want any more help with this, you need to pony up some more requirements.

- Chris

Well, I am not sure if you got it, but let me explain.

In the for loop, the 'select count(*)......' statement gives me the total records where the c1rec.clnt_id = asd.clnt_id and c1rec.acct_id = asd.acct_id and c1rec.pty_id = asd.pty_id .

But, I want the script to have the 3 columns sorted. If the columns contain:
clnt_id acct_id pty_id
1 2 3
4 5 6
1 2 3

then it will go to the first record and get 1 2 3 and put it in the table 'acct_static_details'. The next time it will insert 4 5 6 in the same table. The third time, since the record already exists in the table, it will put the record in another table ' acct_static_details_exceptions'. But, if the records are sorted, then the script will take less time to execute. Is it possible to sort these records? I hope that I am clear.

Thanks.

Gotcha. I looked at every column in the inserts and didn't notice that the table names were different.

Regardless, sorting will not make a difference, unless you care which of those (1 2 3) records gets inserted into which table. An insert statement is completely atomic.

INSERT INTO T1...
INSERT INTO T2...
INSERT INTO T1...

is, for all intensive purposes, as fast as:

INSERT INTO T1...
INSERT INTO T1...
INSERT INTO T2...

The minor differences you *might* see because of the cacheing of the index blocks or whatever is going to be completely overshadowed by both the speed of the driving query and the fact that you are doing separate inserts. You need to optimize the query in the cursor. If possible, you should not be doing inserts inside a cursor. It is much faster to do them as a bulk operation. However, in this case, it would probably be rather difficult to do that, so.....

The other handyd-dandy trick you can do with Oracle is the BULK COLLECT functionality. Here is a link for ya:

http://oradoc.photo.net/ora81/DOC/se...olls.htm#23723

You do a BULK COLLECT around the cursor SELECT to bulk-load a TABLE array. You then use FORALL around the INSERTS to do a bulk insert. There are, as always, some restrictions here. You won't be able to hit the table to look for rows inside the loop, because I don't think the rows are inserted until the end. BUT...

If you order the rows, and then use some local vars to tell when the ID changes, you can handle that functionality without hitting the table. Whenever the key changes, insert into the details table. If the key is the same, insert into the exceptions table. Using the FORALL, Oracle should hold off until the end and do a bulk-insert for you. This should shave some serious time off.

Again, start by using the hints I gave you in the first reply to try to optimize the driving query. This will be the biggest bang for the buck. Then you can worry about doing the BULK stuff.

Hope this helps,

- Chris

Thanks a lot but what about the hint I have in the script. Is that okay or do I have to make a change?
Please let me know.

Thanks.

Good input , Chris.

Certainly you deserve credit.

I am still confused!! Please help.

Thanks.

Lee, you are in a tuff position.
Chris has made some AWESOME points (half of which I don't even comprehend)! The best point he made was that the script just doesn't "LOOK RIGHT". I agree with that.

You might be better off using the current script as a tool/model and re-writing the whole thing. That way you can eliminate and add the stuff Chris was talking about. PLUS, YOU will understand the whole of it better and might think up some good tuning on your own while you are re-writing it.

Hope that helps a little. Might be too late anyways.
- Magnus