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.
LVL 19
Manoj PatilSr. Software EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob LearnedCommented:
Optimization is more of an artform than a science.  There are no exact answers.
0
Wasim Akram ShaikCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Manoj PatilSr. Software EngineerAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
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
Mark GeerlingsDatabase AdministratorCommented:
"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
Manoj PatilSr. Software EngineerAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.