Hello Nick, There are lots of different tools to examine that from just EXPLAIN PLAN to AWS reports.
Unfortunately you haven't given enough information about the tables tab1 and tab2, the result table, number of concurrent sessions, average number of rows per session etc. But from what I can see the result table is something not very "healthy" and I believe it is a bottleneck but of course it depends on its parameters. Regards, Andrey On Friday, 8 March 2013 06:49:36 UTC, Ninja Li wrote: > > Thanks for all your advice. I really appreciate it. > > The issue I have here seems to be with procedure running in the multi-user > production web environment, as running the procedure in a single session in > SQL*Plus always returns the results fast. > > Is there a good way to track down the bottleneck for queries running in > multi-user environment? > > Thanks. > > 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.
