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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bob Learned
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.