Currently I am facing some performance on the (Oracle Reports) performance running on Oracle Database 8.0.5 standard edition. The users normally will 'feel' the performance degrade when more than 2 users running same or different reports. Sometimes the user need to wait for few hours to generate a report. I check the hitratio is 98%.
I had checked all SQL statement - it looks fine. The tables has about few hunderd thousand records. I cannot use the table partition as the Oracle Standard Edition not provide it. Is anyone have any idea to solve this problem.
Maybe the report is trying to make some DML (Insert/Update/Delete) in some locked table(s) , and it is wating for the locks ?
Try (or ask for your DBA) to trace the session of this report in the database, and check the wait views in the database, to find EXACTLY what your report is waiting for.