Andrey, Thanks for your replay. I agree with your suggestion that result_table might be the bottleneck, as multiple inserts and deletes are going against the table during peak hours. We are looking into setting INITRANS to a higher number for result_table to reduce contention. I will keep you posted on any progress. Nick
On Tuesday, March 5, 2013 6:04:44 PM UTC-5, Ninja Li wrote: > 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.
