DB response is slow

“VM is working fine on desktop machine but works slowly when users connect to Oracle DB through application server although its performance is optimal with SAN “Storage Area Network”. RAM capacity same has been assigned to VM on desktop machine as on SAN. There is also no bottleneck (Physical RAM & CPU) has been seen during testing on desktop machine using Task Manager”


I am working with HP Pro 6200 MT Core i5 machine Kindly see the details below for further information,


Processor:           (Intel(R) Core (TM) i5-2400 CPU @ 3.10 GHz)

RAM:                     6.00 GB

Hard Disk:            1.5 TB (1TB + 500 GB)


OS: Windows 7 Professional SP1 x64


I installed VMware workstation on the Desktop machine and also copied VM which is created with Linux OS. There is 1.5 TB (1TB + 500GB) HDD is installed on desktop machine so the 1TB is dedicated to VM and 500 GB to desktop machine.

Kindly see the VMware version for detail and also see VM details in the second snapshot.

There are two instances of Oracle have been created on the particular VM. It creates problem when multiple users connects it through application server and it starts slow down gradually.


You support in this regard will be highly appreciated.

Who is Participating?
schwertnerConnect With a Mentor Commented:
With Buffer cache 208 Mb your DB is dead.

At least 800MB and even more.
You say " It creates problem when multiple users connects it through application server and it starts slow down gradually."

In this case try to investigate following on the Oracle server:

1. Size of the SGA and its components. Pay attention to db_buffer_cache and shared_pool_size parameters.
2. Number of the processes and sessions
SQL>show parameter process
SQL>show parameter session
3. If you have fresh statistics over the tables and indexes of the data base
4. check the number of the sessions active:
SQL>select count(*) from v$session;

If this do not give a clue then try to print AWR report on the high load period. There are postings here that will instruct you how to run AWR report. Pay attention to the recommendions there.
Javier MoralesOracle DBACommented:
Are statistics gathered ?
Without them, Oracle database is blind at optimizing sql executions.

Try running:

SQL> exec   dbms_stats.gather_database_stats

Open in new window

If the performance is still unacceptable, follow the schwertner instructions about AWR report. You may select there a frame of time to monitor what causes the highest load on the database and would drive you to the following steps you'll need to do.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Netsol-NOSAuthor Commented:
We have taken the snapshot for your kind consideration as you suggested, kindly and let us know if there is any possibility.
If you do not use Java code decrease the Java Pool size.
SESSION parameter is too low.

Increase it to 400-500,

but before this increse the processes parameter to 420  - 520.

This need shutdown and startup of the DB (I mean the processes parameter).
Javier MoralesOracle DBACommented:
I wouldn't go though the "advice" buttons, as they offer a estimation based on actual memory/processes situation.
Prior to increase SGA I would tune SQL and help the optimizer with DBMS_STATS package, gathering all the information Oracle needs to optimize queries.

Are you runing an Oracle9i database? this version is out of support since years...

please, launch DBMS_STATS.gather_database_stats (or dbms_stats.gather_SCHEMA_stats to gather statistics for a sigle schema) and start installing STATSPACK to gather reports (as AWR didn't exist in Oracle9i version).

SQL> @?/rdbms/admin/spcreate

Open in new window

(runs also in windoes with slashes ;-) ) --> it creates the STATSPACK user and tables

SQL> @?/rdbms/admin/spauto

Open in new window

--> It creates a job to perform snapshots hourly

SQL> @?/rdbms/admin/spreport

Open in new window

--> It generates a performance report based on the data gathered between two snapshots.

Hope it helps,

Javier MoralesOracle DBACommented:
How many sessions does the database has?

session and processes parameters are a LIMIT and they don't affect performance.
Well, yes, buffer cache seems quite low, but this kind of recommendations can't be told unless you see a valid metric that indicates it must be raised up (advisor button on the right may be valid).

I don't think SGA is the problem. You probably have bad sql that's dropping performance...

gather statistics and generate a statspack report before changing memory (and rebooting the database).
Netsol-NOSAuthor Commented:
Thank you for the quick reply and action.
incriment in Buffer cache upto 800 MB resolved the problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.