Hi, I am having performance issues with a .NET web application that calls the stored procedure on Oracle 11g v2 database. The basic web page setting is as follows; The user enters various criteria, including date range on the web page, which are passed as parameters to backend stored procedure. The procedure will insert the result set returned, up to a thousand record for each query ( in most cases, well below 100 ), into a table results_table, with the user session id: delete result_table where session_id = p_session_id; insert into result_table select session_id, col1, col2. .... from tab1, tab2 where tab1.id = tab2.id and date_start = '01-JAN-2011' and date_end = '01-FEB-2013' and ..........; Another procedure will be called to display the data: open cursor for select ...... from result_table where session_id = p_sessionid; Here is the issue and observations: Hundreds of users will log on to use the web application during peak business hours ( 10am - 6pm ) and delete and insert into the result_table. The query can take 5 or more minutes during business hours but will return in a second or two every time in off peak hours. When I run the procedure manually in SQL*Plus with all the parameters and a test session id, it completes in a second or two each time and inserts the records into the result_table on production even during peak hours and when web page can't return the same set of the records. I also found is that if I narrow the date range from two years to only 3-4 months, the query will return from the web page very fast each time. So the data volumn seems to play a role here. The two tabls has 6 million and 40 million records respectively. There looks to be performance contention here and and I would be grateful for any ideas. I searched the web and found that the setting of freelist can cause contention for multiple inserts to a table. The tablespace for the Oracle database is auto managed (ASSM). Thanks in advance for your advice. Nick
-- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
