Solved

DB response is slow

Posted on 2011-09-12
9
216 Views
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.

1.jpg
2.jpg
0
Comment
Question by:Netsol-NOS
  • 4
  • 3
  • 2
9 Comments
 
LVL 47

Expert Comment

by:schwertner
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.
0
 
LVL 6

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.
0
 

Author Comment

by:Netsol-NOS
ID: 36527713
We have taken the snapshot for your kind consideration as you suggested, kindly and let us know if there is any possibility.
SGA.jpg
SPP-and-SPS.jpg
0
 
LVL 47

Accepted Solution

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

At least 800MB and even more.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 36527732
If you do not use Java code decrease the Java Pool size.
0
 
LVL 47

Expert Comment

by:schwertner
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).
0
 
LVL 6

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,

0
 
LVL 6

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).
0
 

Author Closing Comment

by:Netsol-NOS
ID: 36558215
Thank you for the quick reply and action.
incriment in Buffer cache upto 800 MB resolved the problem.
Regards,
Hassan
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now