Solved

Oracle Database and ASP.NET Application Performance Optimization

Posted on 2014-11-10
7
397 Views
Last Modified: 2014-11-23
Hello Experts,

I have an application in asp.net with Oracle database 11g. In Database every day approx. 40000 records has been added.
Site has been published on IIS server 7.0. On Day start site works normal. Database showing poor performance. But after actively using site for 3 to 4 hours it slows down. Daily active users are approx 15. Provide solution to increase performance of database or application. Also provide tips to avoid common mistakes which harms application performance. No extra settings has been configured in Web.config file, IIS Server and Oracle Database.
0
Comment
Question by:Manoj Patil
7 Comments
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 334 total points
ID: 40435696
Wow!  This is a very broad question.  There may be dozens or hundreds of things you could do to improve performance.  But there are many things we don't know about your system.  Can you give us some more information including:

1. Does the database run on a dedicated server?  Or, do you have the database and IIS both running on the same server?

2. Is/are your server(s) physical or virtual?

3. How much RAM do/does your server(s) have/has?

4. What is your SGA_MAX size (in your spfile or init*.ora file)?

5. What is your BUFFER_CACHE size (in your spfile or init*.ora file)?

6. What kind of application is this?  Is it mainly transaction-processing, or data warehouse?

7.  Were the queries and update statements in the application written by people who have a good understanding of: how Oracle systems work best; your data volumes; the indexes on your tables, etc.?

8. What kind of disk system does your database server have (local disks, NAS, SAN, a combination, etc.)?

9.  Do you use RAID?  If so, what level(s)?

10. Does your application use bind variables?  Or, does it construct each separate SQL statement/query as a unique statement that Oracle has to parse before it can execute it?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 40435832
Optimization is more of an artform than a science.  There are no exact answers.
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 166 total points
ID: 40446791
I agree with the expert markgeer, in addition to the points mentioned , I would like to add these things

Seeing the info you had posted

>> Daily active users are approx 15. Provide solution to increase performance of database or application.

This number is too less to make a database slow or crash, Definitely there seems a problem in the application code itself in the first place.

>>Also provide tips to avoid common mistakes which harms application performance. No extra settings has been configured in Web.config file, IIS Server and Oracle Database.

----------> Are the Connection calls to database getting closed, I am sure if its IIS server you must be using connection pooling(confirm and check this)

---------->Are your queries causing locks, check for Locks in the database(check for v$lock where block=1(simple locks, not an effective way to check locks, just giving you a hint to proceed further) as DBA for dead locks etc., situations) , Simultaneous transactions may cause locking of db objects which will delay processing to a great extent
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 19

Author Comment

by:Manoj Patil
ID: 40449738
Hi markgeer,
Please check below configuration which I am having.

1. Database running on a dedicated server.

2. Server is physical

3. 8 GB RAM

4. What is your SGA_MAX size (in your spfile or init*.ora file)?
     Ans. Please refer attachment

5. What is your BUFFER_CACHE size (in your spfile or init*.ora file)?
     Ans. Please refer attachment

initORA.txt

6. Its a Data Warehousing application.

7.  Were the queries and update statements in the application written by people who have a good understanding of: how Oracle systems work best; your data volumes; the indexes on your tables, etc.?
      Ans.       If this will be issue application will be on hang from first use. Its hanging after 4 to 5 hours.

8. What kind of disk system does your database server have (local disks, NAS, SAN, a combination, etc.)?
      Ans. Dont Know

9.  Do you use RAID?  If so, what level(s)?
      Ans. Dont Know

10. Does your application use bind variables?  Or, does it construct each separate SQL statement/query as a unique statement that Oracle has to parse before it can execute it?
      Ans.
Provide us basic steps which we can perform on oracle database 11g to improve performance such as indexing or global temp. tables. There is no continuous round trips to database from application. Actually queries taking much time on tables where row counts more than 1 million.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40449752
8 gig of total RAM for a database server, especially a Data Warehouse is pretty low.

You posted an init.ora file.  By default Oracle uses an spfile.  Make sure you posted the correct parameters for the database that is running.

1.7 Gig of Max SGA is WAY TOO SMALL for a database of any decent size.  Give Oracle as much of the RAM as you can as long as you don't swap memory to disk.

If you are licensed for the Performance and/or Diagnostics pack for Oracle Enterprise Manager then it can be a great help in locating the bottlenecks.

Since you don't know anything about your disks, I would check with your System Administrators but my guess is the server is just undersized and misconfigured for your database.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 334 total points
ID: 40449892
"Provide us basic steps which we can perform on oracle database 11g to improve performance".

1. Tune or configure your server O/S for Oracle.  (Turn off all programs/services that Oracle doesn't need.  Set the environment variables, memory settings, swap file sizes, etc., to Oracle-recommended values.)

2. Tune your spfile or init*.ora file for your server and disk hardware.  (Set memory_target=at least 50% of physical RAM, and set other values appropriate to your number of users and type of application.)

After those tasks are done, then you can consider other changes in the database: including adding indexes.  But, you can't just add global temporary tables without changing application code to use them.

You didn't answer my questions 7-10.  If you don't know the answers to those questions, you will have a very difficult time getting good performance from Oracle.
0
 
LVL 19

Author Closing Comment

by:Manoj Patil
ID: 40461536
Thanks guys,
Now the application is running in much better condition.
Since this question is having much more depth. So till now I am satisfied with the given solutions.
Will again open the new question regarding this if needed.
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.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

820 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