Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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.
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
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.
Suggested Courses

577 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