Avatar of Manoj Patil
Manoj Patil
Flag for India asked on

Oracle Database and ASP.NET Application Performance Optimization

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.
Oracle DatabaseASP.NET.NET Programming

Avatar of undefined
Last Comment
Manoj Patil

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mark Geerlings

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bob Learned

Optimization is more of an artform than a science.  There are no exact answers.
SOLUTION
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Manoj Patil

ASKER
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.
slightwv (䄆 Netminder)

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Manoj Patil

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