DB response is slow

Posted on 2011-09-12
Medium Priority
Last Modified: 2012-06-27
“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.

Question by:Netsol-NOS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 48

Expert Comment

ID: 36527571
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.

Expert Comment

by:Javier Morales
ID: 36527672
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.

Author Comment

ID: 36527713
We have taken the snapshot for your kind consideration as you suggested, kindly and let us know if there is any possibility.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 48

Accepted Solution

schwertner earned 1500 total points
ID: 36527728
With Buffer cache 208 Mb your DB is dead.

At least 800MB and even more.
LVL 48

Expert Comment

ID: 36527732
If you do not use Java code decrease the Java Pool size.
LVL 48

Expert Comment

ID: 36527740
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).

Expert Comment

by:Javier Morales
ID: 36527761
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,


Expert Comment

by:Javier Morales
ID: 36527772
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).

Author Closing Comment

ID: 36558215
Thank you for the quick reply and action.
incriment in Buffer cache upto 800 MB resolved the problem.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question