Regarding Performance issue while increasing the no of Concurrent users

Database
Enthusiast

Regarding Performance issue while increasing the no of Concurrent users

Hi All,

We are facing severe performance issues while increasing the concurrent users accessing the Reports.
(Queries that run in few seconds takes minutes to run if we increase the users from 1 to 5)

1. The reporting queries access the Tables through views using Access locks
2. Queries do use Join Indexes
3. I'm not seeing CPU limit or Spool limit being hit
(We have currently enabled query logging (With SQL option) but dont capture other details like
Explain plan or Step level info)
But we have been seeing this Perf issue even before enabling this log)
4. No other application is running on the Dev Server
(System resources are fully dedicated to us)
5. While trying this Trial run no other sessions were running (Like ETL load etc)

Is there any other parameter that we need to look into?
(Any tuning or admin stuff that we need to look into to improve the performance)

Regards,
Annal T
3 REPLIES
Enthusiast

Re: Regarding Performance issue while increasing the no of Concurrent users

Its a 1 Node 10 Amp system

But as said earlier no other application is running on the Server and System resources
are completely available to our application.

Regards,
Annal T
Enthusiast

Re: Regarding Performance issue while increasing the no of Concurrent users

Annal,

1)Try increasing spool limits for users and DBCache limits and Datablock size for tables.
2) Monitor the number of sessions being used by each query as those may be too high or too less.

It may help.

Amit.
Enthusiast

Re: Regarding Performance issue while increasing the no of Concurrent users

Hi ,

Do you have Dynamic workload manager or TASM enabled.
if it is then i think there might be some filter condition for concurrent sessions....which would put those queries in the delayed queue.

well when u run those sessions concurrently check from TD manager --> Monitor --> delayed
and see its the same?

regards
TD_DBA