Hi Nick, If all these results are used only during the session (I see you're anyway deleting the whole table in the beginning) - you can consider using global temporary table instead of permanent one.
Yavor On Tuesday, March 12, 2013 5:24:41 AM UTC+2, Ninja Li wrote: > > 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.
