Hi Nick, I work with web too, some procedures I work with take some time, What I did to avoid that is I send the execution command from the web to oracle inside dbms_schadular let the user go on with his job and let the database take it's time with job.
Sent from my iPhone On Mar 6, 2013, at 20:36, ddf <[email protected]> wrote: > > > On Tuesday, March 5, 2013 4:04:44 PM UTC-7, 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 should probably consider partitioning your source tables on date_start > (as a possibility) to reduce the data you're searching through. Of course > partitioning is an extra-cost option so you should consult your DBA to see if > that option is installed. > > > David Fitzjarrell >> > -- > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > 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 [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
