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
  • 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.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

607 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