| Procedure Execution Taking Too Long [message #565251] |
Sat, 01 September 2012 10:04  |
 |
elmousa68
Messages: 20
Registered: September 2012
|
Junior Member |
|
|
We have a form with a master block and 6 detail blocks. The master block contains a button that populates 4 of the details blocks from a large table (table contains about 8 million records) (let's call it Big_Table). What the button essentially does is this:
delete from Detail_Table1 where Detail_Table_Key=Master_Block_Key;
insert into Detail_Table1 (field1,field2,..,fieldn) as select field1, field2,...,fieldn from Big_Table where cond1 and cond2 and cond3;
go_block('detail_block1');
execute_query;
The above 3 statements are repeated for the remaining 3 blocks.
This form is used by about 15 users who have the same database user. These users have been given different "users" to use as login users through a custom-built login screen.
The problem we are facing is that sometimes the button in question populates the detail records instantly and at other times takes a long time (5 to 10 minutes, maybe longer) to execute.
This is causing an unacceptable delay for the data entry operation.
Big_Table is used by other applications using the same database user without problems.
Can anyone point out a possible cause for this problem?
Thank you in advance.
Note: We are using Forms 6i with Database 11g.
|
|
|
|
| Re: Procedure Execution Taking Too Long [message #565252 is a reply to message #565251] |
Sat, 01 September 2012 11:16  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
If you want to know what's taking the time then trace the sessions.
However I really don't see the point of having a procedure that populates separate tables from the main table.
Why not just have each block query the main table directly? It'll be more efficient.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
