Home » SQL & PL/SQL » SQL & PL/SQL » How to modify this package for Performance tuning (merged)
How to modify this package for Performance tuning (merged) [message #238444] Fri, 18 May 2007 01:33 Go to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
CREATE OR REPLACE PACKAGE BODY
schema_name.package_name AS

PROCEDURE main_proc_name IS

PROCEDURE sub_proc_name (file_name VARCHAR2) IS

v_str VARCHAR2(32767);
v_file_name VARCHAR2(100) := 'abc'||
substr(p_file_name,1, instr( p_file_name,'.',-1)-1) ||
'.txt';
v_file_handle UTL_FILE.FILE_TYPE;
v_directory_name CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'abc_dir';
rec_cnt NUMBER:=0;

CURSOR cur_name (p_file_name VARCHAR2)
IS (
SELECT
field_1,
field_2,
field_3,
field_4
FROM
table_name a
WHERE
a.field_1 = p_file_name);


cur_name_obj cur_name%ROWTYPE;

BEGIN

v_file_handle := UTL_FILE.FOPEN( v_directory_name, v_file_name, 'w', 32767 );

OPEN cur_name(p_file_name);
LOOP
FETCH cur_name INTO cur_name_obj;

v_str := cur_name_obj.a.field_1
||','||cur_name_obj.a.field_2
||','||cur_name_obj.a.field_3
||','||cur_name_obj.a.field_4;
rec_cnt := rec_cnt + 1;
update table_name a set a.a.field_5 = 'Y' where a.file_name = p_file_name;
EXIT WHEN cur_name%NOTFOUND;

UTL_FILE.PUT_LINE ( v_file_handle, v_str );

IF rec_cnt > 80 THEN
UTL_FILE.FFLUSH ( v_file_handle );
rec_cnt := 0;
END IF;

END LOOP;

UTL_FILE.FFLUSH ( v_file_handle );

UTL_FILE.FCLOSE(v_file_handle);

CLOSE cur_name;


EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO RECORD FETCHED BY QUERY');

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error In the Procedure');
RAISE;


END sub_proc_name;

begin
for i in (select distinct a.field_1 from table_name a where a.a.field_5 is null )
loop
filename := i.field_1;
sub_proc_name(filename);
end loop;

end main_proc_name;

end package_name;
/
Re: How to modify this package for Performance tuning [message #238453 is a reply to message #238444] Fri, 18 May 2007 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- No format
- No Oracle version
- No explaination of the meaning or purpose of the package
- No description of dependent objects
- No look

Regards
Michel
Re: How to modify this package for Performance tuning [message #238462 is a reply to message #238453] Fri, 18 May 2007 03:19 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
table format:
CREATE TABLE REJ_VC_MOVE_RECORDS
(
field_2 DATE NOT NULL,
field_1 VARCHAR2(60 BYTE) NOT NULL,
field_3 VARCHAR2(11 BYTE) NOT NULL,
field_4 VARCHAR2(3 BYTE) NOT NULL,
field_3 VARCHAR2(4000 BYTE),
field_4 VARCHAR2(1 BYTE) NOT NULL
)


oracle version 10g

purpose of package...storing the table records in a flat file taht is to be mailed later by a unix script...

no dependent objects
Re: How to modify this package for Performance tuning [message #238464 is a reply to message #238462] Fri, 18 May 2007 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
purpose of package... storing the table records in a flat file

spool myfile
select * from mytable;
spool off

I was talking about formatting your post: How to format your posts

Regards
Michel

[Updated on: Fri, 18 May 2007 03:29]

Report message to a moderator

Re: How to modify this package for Performance tuning [message #238471 is a reply to message #238464] Fri, 18 May 2007 03:49 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
actually i wanted to know how to modify the oracle package for performance tuning
Re: How to modify this package for Performance tuning [message #238489 is a reply to message #238471] Fri, 18 May 2007 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually you should want to achieve your business needs with optimized performances.

Regards
Michel
Re: How to modify this package for Performance tuning [message #238492 is a reply to message #238489] Fri, 18 May 2007 06:00 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
yeah i understand that.but how to do performance tuning on this package?
Re: How to modify this package for Performance tuning [message #238507 is a reply to message #238492] Fri, 18 May 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My old eyes can't read code if it is not formatted. ./fa/1601/0/

Regards
Michel
Re: How to modify this package for Performance tuning [message #238512 is a reply to message #238507] Fri, 18 May 2007 06:52 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member

CREATE OR REPLACE PACKAGE BODY 
schema_name.package_name AS

PROCEDURE main_proc_name IS

PROCEDURE sub_proc_name (file_name VARCHAR2) IS

v_str VARCHAR2(32767);
v_file_name VARCHAR2(100) := 'abc'|| 
substr(p_file_name,1, instr( p_file_name,'.',-1)-1) ||
'.txt';
v_file_handle UTL_FILE.FILE_TYPE;
v_directory_name CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'abc_dir';
rec_cnt NUMBER:=0; 

CURSOR cur_name (p_file_name VARCHAR2) 
IS (
SELECT 
field_1,
field_2,
field_3,
field_4
FROM 
table_name a
WHERE 
a.field_1 = p_file_name);


cur_name_obj cur_name%ROWTYPE;

BEGIN

v_file_handle := UTL_FILE.FOPEN( v_directory_name, v_file_name, 'w', 32767 ); 

OPEN cur_name(p_file_name);
LOOP
FETCH cur_name INTO cur_name_obj;

v_str := cur_name_obj.a.field_1
||','||cur_name_obj.a.field_2
||','||cur_name_obj.a.field_3
||','||cur_name_obj.a.field_4;
rec_cnt := rec_cnt + 1;
update table_name a set a.a.field_5 = 'Y' where a.file_name = p_file_name;
EXIT WHEN cur_name%NOTFOUND; 

UTL_FILE.PUT_LINE ( v_file_handle, v_str );

IF rec_cnt > 80 THEN
UTL_FILE.FFLUSH ( v_file_handle );
rec_cnt := 0;
END IF;

END LOOP;

UTL_FILE.FFLUSH ( v_file_handle );

UTL_FILE.FCLOSE(v_file_handle);

CLOSE cur_name;


EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO RECORD FETCHED BY QUERY');

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error In the Procedure');
RAISE;


END sub_proc_name;

begin
for i in (select distinct a.field_1 from table_name a where a.a.field_5 is null )
loop
filename := i.field_1;
sub_proc_name(filename);
end loop;

end main_proc_name;

end package_name;

[Updated on: Fri, 18 May 2007 07:51] by Moderator

Report message to a moderator

Re: How to modify this package for Performance tuning [message #240526 is a reply to message #238512] Fri, 25 May 2007 03:05 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
Hi,
I had formatted the package in the earlier mail.
I would be grateful if anyone could kindly tell me how to modify that package for performance tuning in oracle 10g.
Pls pls pls....frnz....the need is urgent and am not able to solve it....
How to modify this apckage for performance tuning [message #240529 is a reply to message #238444] Fri, 25 May 2007 03:07 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
CREATE OR REPLACE PACKAGE BODY 
schema_name.package_name AS

PROCEDURE main_proc_name IS

PROCEDURE sub_proc_name (file_name VARCHAR2) IS

v_str VARCHAR2(32767);
v_file_name VARCHAR2(100) := 'abc'|| 
substr(p_file_name,1, instr( p_file_name,'.',-1)-1) ||
'.txt';
v_file_handle UTL_FILE.FILE_TYPE;
v_directory_name CONSTANT ALL_DIRECTORIES.DIRECTORY_NAME%TYPE := 'abc_dir';
rec_cnt NUMBER:=0; 

CURSOR cur_name (p_file_name VARCHAR2) 
IS (
SELECT 
field_1,
field_2,
field_3,
field_4
FROM 
table_name a
WHERE 
a.field_1 = p_file_name);


cur_name_obj cur_name%ROWTYPE;

BEGIN

v_file_handle := UTL_FILE.FOPEN( v_directory_name, v_file_name, 'w', 32767 ); 

OPEN cur_name(p_file_name);
LOOP
FETCH cur_name INTO cur_name_obj;

v_str := cur_name_obj.a.field_1
||','||cur_name_obj.a.field_2
||','||cur_name_obj.a.field_3
||','||cur_name_obj.a.field_4;
rec_cnt := rec_cnt + 1;
update table_name a set a.a.field_5 = 'Y' where a.file_name = p_file_name;
EXIT WHEN cur_name%NOTFOUND; 

UTL_FILE.PUT_LINE ( v_file_handle, v_str );

IF rec_cnt > 80 THEN
UTL_FILE.FFLUSH ( v_file_handle );
rec_cnt := 0;
END IF;

END LOOP;

UTL_FILE.FFLUSH ( v_file_handle );

UTL_FILE.FCLOSE(v_file_handle);

CLOSE cur_name;


EXCEPTION

WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('NO RECORD FETCHED BY QUERY');

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error In the Procedure');
RAISE;


END sub_proc_name;

begin
for i in (select distinct a.field_1 from table_name a where a.a.field_5 is null )
loop
filename := i.field_1;
sub_proc_name(filename);
end loop;

end main_proc_name;

end package_name;
Re: How to modify this apckage for performance tuning [message #240533 is a reply to message #240529] Fri, 25 May 2007 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't know about indenting and comments and meaningful names?

What's the package and procedures are intend to do?
What's your Oracle version?

Regards
Michel
Re: How to modify this apckage for performance tuning [message #240558 is a reply to message #240533] Fri, 25 May 2007 03:47 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
The package/procedures are mailny written for doing emailing job.
This package takes data from the mentioned table and puts it in a flat file(done by another unix script that calls this package) which is emailed to a system.
The tuning of the package is important as the job needs to be faster as all other jobs will be on hold until the emailing of files are done.

The version of oracle is 10g.

ma'am the necessity is urgent and am not able to find out any solution.I would be grateful if you could kindly provide me with a solution.
Re: How to modify this package for Performance tuning (merged) [message #240560 is a reply to message #238444] Fri, 25 May 2007 03:52 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
For my complete stab in the dark as to what your problem might be (assuming there is a problem and this is not just a general enquiry), you could look at the update statement:

update table_name a set a.a.field_5 = 'Y' where a.file_name = p_file_name;

Is there an index on file_name to ensure this is optimal ? Has the table been analyzed ?
Re: How to modify this package for Performance tuning (merged) [message #240785 is a reply to message #240560] Fri, 25 May 2007 15:17 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Or, to complete the question: are all statistics up to date and are index statistics included?
Re: How to modify this package for Performance tuning (merged) [message #240819 is a reply to message #240785] Sat, 26 May 2007 01:54 Go to previous messageGo to next message
priyanka.d
Messages: 20
Registered: May 2007
Junior Member
index is present on file_name.
Re: How to modify this package for Performance tuning (merged) [message #240827 is a reply to message #238444] Sat, 26 May 2007 04:20 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member

Quote:
update table_name a set a.a.field_5 = 'Y' where a.file_name = p_file_name;


How many records in table_name

As said earler, have you gathered index & table stats.

Further trace output would help.

Brayan.
Previous Topic: regarding blob data
Next Topic: Preventing commit in a "Before update" trigger.
Goto Forum:
  


Current Time: Sun Dec 04 19:05:24 CST 2016

Total time taken to generate the page: 0.04567 seconds