Home » RDBMS Server » Performance Tuning » performance Tunning
performance Tunning [message #403071] |
Wed, 13 May 2009 11:00 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Hi,
I wrote one package which contains four cursors the first cursor which return more than 1.5 lak
records for each record of the first cursor i define another cursor, like that i define three
cursors for each iteration i have to process to arrive the balance of amount .
My problem is while executing this package it will take more than 2 hours.
Can anyone guide me how can i tune this package i am new one to tunning.
I cant able to provide the package it is large one.
|
|
|
Re: performance Tunning [message #403074 is a reply to message #403071] |
Wed, 13 May 2009 11:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
No Oracle version and no useful information to do any analysis.
We can only give most generic hints, with which you are not going be impressed.
To start with, write better SQL.
Since you know the first cursor will fetch X records, use bulk collect with varray.
If you can do with single sql/cursor, do it.
Gather statistics on tables and indexes.
[Updated on: Wed, 13 May 2009 11:18] Report message to a moderator
|
|
|
|
|
|
Re: performance Tunning [message #403135 is a reply to message #403084] |
Wed, 13 May 2009 22:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
BlackSwan wrote on Thu, 14 May 2009 02:24 |
You do NOT tune PL/SQL.
You tune SQL.
|
I disagree. Much of tuning involes the selection of a better algorithm to achieve the same functionality. In SQL, that algorithm is determined at PARSE time, because that's how SQL works. In PL/SQL, the algorithm is determined at build time - it is hard-coded.
There is scope to have poor algorithms in both SQL and in PL/SQL. In SQL, we encourage the optimiser to make better choices. In PL/SQL, we have to re-code.
The algorithm you are using is bad PL/SQL. See this article for more on that. You should heed @JR's advice and merge it all into a single cursor.
Ross Leishman
|
|
|
|
Re: performance Tunning [message #403138 is a reply to message #403137] |
Wed, 13 May 2009 22:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I don't think we're talking about the same thing.
I'm talking about the arrangement of SQL statements within the PL/SQL.
If you have a big cursor (say 1M rows) and you nest SQL statements WITHIN the cursor loop, then you are doomed. My reasoning is that regardless of how you tune the nested statement, you still have to execute it 1M times.
Tuning involves restructuring the PL/SQL to remove any nesting. This will almost certainly affect the structure of the SQL, which may then warrant a round of pure SQL tuning.
Ross Leishman
|
|
|
|
Re: performance Tunning [message #403381 is a reply to message #403071] |
Fri, 15 May 2009 04:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
For the issue i told here i have created bitmap indexes for the below query and index details are given below.
Anyone help me to tune this query.
This is the second cursor i am using in that procedure.
SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM cms_limit_security_map
WHERE sci_las_lmt_id = recs(i).lmt_id
AND update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id and
a.cms_collateral_id=b.cms_collateral_id and
a.sci_las_llp_id=b.sci_las_llp_id and
--a.charge_id = b.charge_id and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id
the count will be :
lmtcnt sci_las_sec_id
----- ------------
11 23204574
17 23161738
The lmtcnt 11 and 17 is the count which all sci_las_lmt_id limits
tagged with that security_id means 11 number of recs(i).lmt_id and corresponding amount will be available for that particular sci_las_sec_id (23204574) and this security id have some amount of value.
sci_las_sec_id
----
(S1)(23204574) --- 200000 amount
sci_las_lmt_id
-----
(L1) (20105519) --- 50000 amount
the amount of L1 should be minus with the amount of S1 and like the same for remaining 10 Limits.
Its is not one to one mapping it is many to many mapping.
The next limit amount from that 11 should assign to the security
by after executing the query which is given above.
That limit may mapped with some security and this limit amount should be allocated to minimum lmtcnt security only.
Main process is i have temp table structure is given below.
The above query which i posted is to count the security which is
associated for various sci_las_lmt_ids
for that i have created two more indexes which is given below.
create bitmap index cms_lmt_sec_bmap_indx on cms_limit_security_map (sci_las_sec_id,sci_las_lmt_id,update_status_ind)
create bitmap index cms_lmt_sec_bmap_indx1 on cms_limit_security_map (update_status_ind,sci_las_lmt_id)
I have taken the statistics before and after creating the index.but it shows same one. i am using toad to explain the plan
Can anyone correct where i get lacking and correct me if i am wrong.
if the given things are not understandable i will produce my package also.
[Updated on: Fri, 15 May 2009 05:11] Report message to a moderator
|
|
|
|
Re: performance Tunning [message #403517 is a reply to message #403135] |
Sat, 16 May 2009 04:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
rleishman wrote on Thu, 14 May 2009 13:20 | The algorithm you are using is bad PL/SQL. See this article for more on that. You should heed @JR's advice and merge it all into a single cursor.
Ross Leishman
|
Ross Leishman
|
|
|
Re: performance Tunning [message #403831 is a reply to message #403517] |
Tue, 19 May 2009 01:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ORAGENASHOK
Messages: 240 Registered: June 2006 Location: Chennai
|
Senior Member |
|
|
Hi rleishman,
Much more thanks rleishman its really valuable document but i have modified according to that document but still my package struc over four hours and i am not able see any result.
I have provided my package here so please post to resolve this
issue.i sorry to say this i have to resolve this usrgently.
I have created GTT (Global Temporary Table) here as given below.
CREATE GLOBAL TEMPORARY TABLE CMS_SECURITY_MOD
(
CMS_COLLATERAL_ID NUMBER(19),
SCI_SECURITY_DTL_ID NUMBER(10),
BAL_CMV NUMBER(30,2)
)
ON COMMIT PRESERVE ROWS
CREATE OR REPLACE package body pkg_sec_cal_gtt
IS
procedure pro_sec_cal_gtt
is
cursor cur_security_details is
select cms_collateral_id,sci_security_dtl_id,cmv
from cms_security
where status = 'ACTIVE' and
decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
cursor cur_limit_details
is
select lmt_id,
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) REQ_ABS_VALUE,
null, cms_act_sec_coverage,
(NVL (cms_activated_limit, 0) * NVL (cms_req_sec_coverage, 0) / 100) bal_security_value,
sysdate created_date,nvl(cms_activated_limit,0) cms_activated_limit
from sci_lsp_appr_lmts where
upper(lmt_type_value) <> 'CONTROL'
and upper (cms_limit_status) = 'ACTIVE'
and update_status_ind !='D'
order by lmt_id;
cursor cur_sec_det(p_lmt_id sci_lsp_appr_lmts.lmt_id%type) is
SELECT COUNT (a.sci_las_sec_id) lmtcnt, a.sci_las_sec_id
FROM cms_limit_security_map a,
(SELECT *
FROM cms_limit_security_map
WHERE sci_las_lmt_id = p_lmt_id
AND update_status_ind != 'D') b
WHERE a.sci_las_sec_id =b.sci_las_sec_id and
a.cms_collateral_id=b.cms_collateral_id and
a.sci_las_llp_id=b.sci_las_llp_id and
b.update_status_ind != 'D'
GROUP BY a.sci_las_sec_id
ORDER BY lmtcnt, a.sci_las_sec_id;
TYPE reclist IS TABLE OF cur_security_details%ROWTYPE;
recs reclist;
TYPE lmtdetails IS TABLE OF cur_limit_details%ROWTYPE;
lmtdet lmtdetails;
TYPE secdetails IS TABLE OF cur_sec_det%ROWTYPE;
secdet secdetails;
coll_cnt1 number ;
coll_cnt2 number ;
v_cms_collateral_id number(19) ;
v_cmv number(30,2);
v_status varchar2(20);
v_req_sec_cov_ind varchar2(2) ;
v_sci_security_dtl_id number(10) ;
v_cmv_currency char(3) ;
spec_lmt_cnt number;
temp_bal_security_value number(30,2) ;
Begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE CMS_SECURITY_MOD';
EXECUTE IMMEDIATE 'TRUNCATE TABLE SEC_SECURITY_EVALUATION_RESULT';
COMMIT;
OPEN cur_security_details;
LOOP
FETCH cur_security_details BULK COLLECT INTO recs LIMIT 5000;
FORALL i IN 1..recs.COUNT
INSERT INTO CMS_SECURITY_MOD VALUES recs(i);
EXIT WHEN cur_security_details%NOTFOUND;
END LOOP;
CLOSE cur_security_details;
OPEN cur_limit_details;
FETCH cur_limit_details BULK COLLECT INTO lmtdet;
FOR j IN 1..lmtdet.COUNT
Loop
OPEN cur_sec_det(lmtdet(j).lmt_id);
FETCH cur_sec_det BULK COLLECT INTO secdet ;
coll_cnt2 := secdet.COUNT;
FOR k IN 1..secdet.COUNT
loop
if ((coll_cnt2 = 1) and (secdet(k).lmtcnt = 1)) then
select count(*) into spec_lmt_cnt
from cms_security
where sci_security_dtl_id = secdet(k).sci_las_sec_id and
status = 'ACTIVE' and
decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
if ( spec_lmt_cnt > 0 ) then
select cms_collateral_id,cmv,status,req_sec_cov_ind,
sci_security_dtl_id,cmv_currency
into v_cms_collateral_id,v_cmv,v_status,v_req_sec_cov_ind,
v_sci_security_dtl_id,v_cmv_currency
from cms_security
where sci_security_dtl_id = secdet(k).sci_las_sec_id and
status = 'ACTIVE' and
decode(req_sec_cov_ind,NULL,'Y',req_sec_cov_ind) = 'Y';
temp_bal_security_value := 0 ;
update SEC_SECURITY_EVALUATION_RESULT
set allocated_cmv = v_cmv,
act_sec_coverage = nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
bal_security_value = temp_bal_security_value
where lmt_id = lmtdet(j).lmt_id;
if sql%notfound then
insert into SEC_SECURITY_EVALUATION_RESULT
values (lmtdet(j).lmt_id,lmtdet(j).req_abs_value,v_cmv,
nvl(v_cmv,0) / decode(lmtdet(j).cms_activated_limit,0,Null,lmtdet(j).cms_activated_limit) * 100,
temp_bal_security_value,sysdate);
End if;
-- else
end if ;
end if ;
End loop;
close cur_sec_det;
End loop;
commit;
close cur_limit_details;
End pro_sec_cal_gtt;
End pkg_sec_cal_gtt;
/
|
|
|
Re: performance Tunning [message #403847 is a reply to message #403831] |
Tue, 19 May 2009 03:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
You are still opening cursors inside a loop:
FOR j IN 1..lmtdet.COUNT
Loop
OPEN cur_sec_det(lmtdet(j).lmt_id);
FETCH cur_sec_det BULK COLLECT INTO secdet ;
You are still executing SQL inside a loop:
FOR k IN 1..secdet.COUNT
loop
if ((coll_cnt2 = 1) and (secdet(k).lmtcnt = 1)) then
select count(*) into spec_lmt_cnt
from cms_security
...
Read the article again. It will tell you that you need to merge the nested SQL back into the main query, or - if you cannot design it that way due to two tables with mismatched cardinality - to process concurrent cursors.
Ross Leishman
|
|
|
Goto Forum:
Current Time: Thu Aug 22 08:27:29 CDT 2024
|