Home » RDBMS Server » Performance Tuning » Sql Tuning - (urgent)
icon14.gif  Sql Tuning - (urgent) [message #126874] Thu, 07 July 2005 01:37 Go to next message
rc_ashish
Messages: 39
Registered: June 2005
Location: mumbai
Member
Hi,

I have wrote around 15 procedures and their are executes one by one . in this procedures contents only select,calculation, insert and update statements.

Cursors are closed in all procedures.

My problem is that it takes 12 hours for execution for around 500 Employees.


Thnx in Advance

Ashish
Re: Sql Tuning - (urgent) [message #126916 is a reply to message #126874] Thu, 07 July 2005 06:38 Go to previous messageGo to next message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
A bit of tuning you need to do then.

Start by explaining the queries and look at their execution stats. Based on that, you should be able to decide what needs to be done. For example, you may need to analyse your tables, build indexes, restructure your queries, run stuff in parallel, etc.

Best regards.

Frank
Re: Sql Tuning - (urgent) [message #127777 is a reply to message #126874] Wed, 13 July 2005 05:19 Go to previous message
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
Junior Member
Hello,

With pl-sql , you should have performance improvements if you review the code searching for sentences like this:

...
cursor cursor1 is select column_a, column_b from table_a;
begin
for reg in cursor1
if reg.column_a='VALUE1' then
insert into table_b (column_a, column_b, column_c) values
(reg.column_a*10, reg.column_b, 1);
elsif reg.column_a='VALUE2' then
insert into table_b (column_a, column_b, column_c) values
(reg.column_a*20, reg.column_b, 2);
end if;
end loop;
commit;
...

This example code can be changed for this:

insert into table_b (column_a, column_b, column_c)
select column_a*10, column_b, 1 as column_c from table_a
where column_a='VALUE1';

insert into table_b (column_a, column_b, column_c)
select column_a*20, column_b, 2 as column_c from table_a
where column_a='VALUE2';

commit;

However this is a easy example, it can ilustrate what i mean, sometimes , there are parts of code that can be changed for some sentences with big performance gains.



Previous Topic: wait events
Next Topic: using statspack .......!
Goto Forum:
  


Current Time: Wed Jan 27 12:05:06 CST 2021