Solved

Oracle Database and ASP.NET Application Performance Optimization

Posted on 2014-11-10
7
393 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 76

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

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…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

778 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