Home » SQL & PL/SQL » SQL & PL/SQL » Tuning a Plsql procedure
Tuning a Plsql procedure [message #187231] Fri, 11 August 2006 07:24 Go to next message
vamsi_krishna_777
Messages: 7
Registered: August 2006
Location: Mumbai
Junior Member

Hi
I have Procedure that have 3 curosrs which are nested one below with in each other

If Outer most cursor pick's one row and another in side it pick around 400000 records and another cursor nested in previous cursor which pick ten row's.Here the logic for first row one stored procedure is called.For second row another so no ......up to ten rows.These ten stored procedure's(functions) are basically validating some field's as address comparision ,dob etc.So my porblem is it is taking 14 minutes to execute this procedure.As per my observation because of those ten rule's it is taking more time ,since if comment some of rule's it is taking less time as 6 minutes .Form indexes point of view and all other aspects i think it is ok
So i found an approach to run all the rules simultaneously using dbms_job .But i face a problem that i can't uderstand how to send the input and out put parameter's to calling procedure.And these parameter's must be passed by variable from called procedure to calling procedure


Please suggest me what is the syntax of dbms_job with input and output parameters with variable's that must be passed as formal parameter's



Regarding DBMS_JOB.SUBMIT I am getting following error

Declare
x number:=1;
b number;
jobnumber number;
BEGIN
DBMS_JOB.SUBMIT(JOB => jobnumber,
WHAT => 'vamsi_proc1('||x||');',
NEXT_DATE => (sysdate+1/(86400)),
INTERVAL => null);
--dbms_output.put_line('value of b is '||b);
COMMIT;
END;
/
Declare
*
ERROR at line 1:
ORA-06550: line 1, column 107:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ")" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 5


create or replace procedure vamsi_proc1(v in out number) as
x number(4);
begin
--a:=1;
insert into emp_vamsi(select 677,ENAME,SAL,DEPTNO,MANAGER from emp where empno=v);
commit;
--a:=2;
--x:=a;
end;
/

And also please suggest me weather using job's is a good idea ,If not than suggest me what ever will the other approach.
Re: Tuning a Plsql procedure [message #187254 is a reply to message #187231] Fri, 11 August 2006 09:02 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you post the procedure itself?
Re: Tuning a Plsql procedure [message #187280 is a reply to message #187231] Fri, 11 August 2006 11:19 Go to previous messageGo to next message
vamsi_krishna_777
Messages: 7
Registered: August 2006
Location: Mumbai
Junior Member

Hi

I cannot mail whole procedure,since officilly it is not allowed but i will send the structure and logic how it is written.

it's structure will look some thing look like this

declare
empno_fetch number;

CURSOR deu_process IS
SELECT empno
FROM emp
WHERE flg_process = 'N'
AND ROWNUM <= 5000
ORDER BY empno;

CURSOR dup_all IS --c1 fetch around 400000 records
(select empno
from emp
where empno>l_empno
order by empno
)
where rownum<=empno_fetch;

CURSOR rules IS --c2
SELECT rule_id, rule_score, name_Of_func
FROM rule;

begin
l_count := 1;
OPEN dedupe_rule_attr;
LOOP
BEGIN
Here one loop is used for fetching 5000 records at time and
Here deu_process cursor is opened ,assume it fetches one record
Here another cursor dup_all is used for fetching the records and assume it fetched 400000 then

Here another cursor is opened name where it fetches 10 rows
Here logic is if first row then
call's stored function which has following functionalty
it compares fields such as address of outer most cursor i.e deu_process with all the address of the other rows i.e 400000 rows and return a number
if second row then
call's another stored function which has
compares other fields such as first name,last name
if third then other field
so on ....
...
...
...
...
up to 10
and then finally ends

so if i comment all the rules loop then it takes only one min to execute .If it validaes all of them if takes 14 minutes to execute .so there is problem in that 10 procedures to execute


Please suggest me what is the syntax of dbms_job with input and output parameters with variable's that must be passed as formal parameter's



Regarding DBMS_JOB.SUBMIT I am getting following error

Declare
x number:=1;
b number;
jobnumber number;
BEGIN
DBMS_JOB.SUBMIT(JOB => jobnumber,
WHAT => 'vamsi_proc1('||x||');',
NEXT_DATE => (sysdate+1/(86400)),
INTERVAL => null);
--dbms_output.put_line('value of b is '||b);
COMMIT;
END;
/
Declare
*
ERROR at line 1:
ORA-06550: line 1, column 107:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ")" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 5


create or replace procedure vamsi_proc1(v in out number) as
x number(4);
begin
--a:=1;
insert into emp_vamsi(select 677,ENAME,SAL,DEPTNO,MANAGER from emp where empno=v);
commit;
--a:=2;
--x:=a;
end;
/

using dbms_job.submit proc that call's functions which ret out and in parameters to calling procedure environment .I can't get this with out using data base table's.
If you have any method for this Plese suggest

And also please suggest me weather using job's is a good idea ,If not than suggest me what ever will the other approach.

Thank's for Replying
Regard's
vamsi krishna
Re: Tuning a Plsql procedure [message #187930 is a reply to message #187231] Wed, 16 August 2006 06:58 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Run a TKPROF over trace file and look for the most resource consuming statements.

Re: Tuning a Plsql procedure [message #187952 is a reply to message #187930] Wed, 16 August 2006 08:25 Go to previous messageGo to next message
vamsi_krishna_777
Messages: 7
Registered: August 2006
Location: Mumbai
Junior Member

Hi
I had done the tkprof and saw the timing .I could trace the one of the sql is executing several times unnessarly .so i modified it and then saw so it gave 2 minutes decrease in time.But still it running it is running 11 minutes .It is only becase large number of iterations.So can u recomend any kind of multitasking or multithreadig except dbms_job.since i have already tried with it .It is creating unnecessary locks.

Regards
vamsi krishna
Re: Tuning a Plsql procedure [message #187971 is a reply to message #187952] Wed, 16 August 2006 09:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can't you use joins instead of looping through all these cursors?
Previous Topic: When I use weak REF CURSOR to use BULK COLLECTION
Next Topic: Running sqlplus.exe as normal windows domain user
Goto Forum:
  


Current Time: Wed Dec 07 12:56:10 CST 2016

Total time taken to generate the page: 0.17984 seconds