Performance Issue for a query

[gys67]

Hi :

I have an issue of performence in one my oracle pl/sql procedure.
This procedure gets data from oracle view and compares it with existing table.
Depapnding on the comparision , it writes results to an un indexed table.
The volume of data is very high and procedure takes hours to run In the procedure, I get data from view in to cursor, then there are certain if and case statements and
finally there is an insert statement.
Voulme of data from view is about 30000 rows and comparing againsts 500000 rows.
Please advise , what are the steps I have to check to ensure that I can minimize run time.

Thanks

 

[sem]

First of all you should look at execution plans of your statements and check whether you have no locking issues. Then, try to rewrite your loop in "pure SQL". In most cases it's possible, and though it may look a bit ugly, performance would be improved significantly.

Regards, Dima

 

[gys67]

Thanks for your response.

I was wondering that Index could have any effect on select statement.

In other words I do not know what is the impact of index on select statement.

Thanks
G

 

[sem]

Good index may help, but the meaning of goodness is case specific. Can you provide more details (table structures, indexes available, current execution plans etc.)? In fact the numbers mentioned above are quite small for Oracle, though not too small to have no chance to puzzle it. Can you check whether the time is spent by looping through cursor or during inner processing? Try to run empty loop on that cursor.

Regards, Dima