 Sql Tuning - (urgent) [message #126874] |
Thu, 07 July 2005 01:37  |
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  |
Frank Naude
Messages: 4587
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
