Solved

Oracle Database and ASP.NET Application Performance Optimization

Posted on 2014-11-10
7
405 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
[X]
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
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
Independent Software Vendors: 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!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
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.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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