Solved

Oracle Database and ASP.NET Application Performance Optimization

Posted on 2014-11-10
7
381 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 34

Accepted Solution

by:
Mark Geerlings earned 334 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 19

Author Comment

by:Manoj Patil
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 334 total points
Comment Utility
"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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

763 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

10 Experts available now in Live!

Get 1:1 Help Now