Identified the Tuning Portion On Report Generation Performance

[ckwan]

Hello everyone,
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.

Thanks.

[JChiappa]

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.

Regards,

Chiappa

[pando]

I have read from Tom´s Website that



o When you query from the PARENT to the CHILD. Consider the EMP, DEPT
example again. It is very common to query the EMP table in the context of a
deptno. If you frequently query:

select * from dept, emp
where emp.deptno = dept.deptno and dept.deptno = :X;

to generate a report or something, you'll find not having the index in place
will slow down the queries.



May be this is your case?

[tamilselvan]

The other reason could be inadequate temporary space or memory.

Run utlbstat and utlestat to collect statistics and analysis again.