Skip to Main Content
  • Questions

  • Performance Issue - high response time

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ogundotun.

Asked: January 25, 2004 - 2:22 pm UTC

Last updated: January 28, 2006 - 12:09 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked

High Tom,

I have an oracle 9i release 9.2.0.1.0 (32 bits) database created on solaris 8 on which I always have maximum of 50 concurrent users.The front end for the application running against the database is ASP with VB scripts.All the tables in the database are properly indexed and initially I was maintaining the default values for the initialization parameters but then any time I have more than 20 users on the machine the response time is always high with 100% CPU utilization,so I increased the shared_pool_size,buffer_cache_size but the issue remain the same.Once the server is shutdown and restarted it will be okay for a while.
The application is a small one in which there are just 10 tables and the biggest of all has 700000 records.

The system spec is as below;

SUN FIRE V120 with IGB Processor,IGB RAM and 2(36GB) Hard disks.

Please, I how can I go about this problem.

Thanks,

-Akinwale-

and Tom said...

well, i don't know how you can say "every thing is properly indexed" and so on.

You have to identify WHAT is causing your issue (frankly, it sounds like it could be "too little hardware").

Then and only then can you apply a remedy. Changing parameters willy nilly like that is fruitless. Like shooting a gun into the woods at night hoping to hit a deer to eat for breakfast. It won't happen.

So, use the tools like sql_trace with timed statistics. trace your applications. verify they are doing the least amount of work possible (eg: if you see a query that retrieves 5 rows from a 3 table join but does 10,000 logical IO's to get it -- you have found something to work on)

Hopefully -- I really hope (but somehow, i have a gut feeling the opposite is true) you are using BIND VARIABLES. If not, that is undoubtably a major contributor to your issue (seems to be epidemic with VB programmers -- no binds, every query is a unique query). You can use statspack with a 15 minute snapshot window during your busy time to find out. If your soft parse % is not above 99%, you are missing bind variables and need to fix that immediately.


So, suggest you trace individual applications, look for low hanging fruit.

Then, maybe use statspack to get a read on how the instance as a whole is doing.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Oracle ASP-Net Performance Issue

Abdul Seerae, January 27, 2006 - 10:19 am UTC

We have a 3rd party ASP-Net based application running on oracle 9i - Windows 3000 Server with 2GB RAM. The users are complaining about high response time.
OEM shows buffer cache hit ratio is 100% and Lib. Cache hit ratio is about 99.95%. IO in database is very low and always shows 25 ~ 30% free memory. But the CPU usage is between 95 ~ 100% most of the time – which is slowing down the server.
We can't see any bottlenecks in Oracle database side. When we look at the CPU utilization, there are few sessions of "aspnet_wp.exe" which is taking up all the CPU time. We tried to convince our users that it is an application issue rather than database issue. But the vendor is blaming database and it seems to me that vendor don't want to spent time to fine tune their ASP codes / scripts.

I am wondering, is there any easy way to prove that their application is to be blamed?. Any thoughts / suggestions in this respect will be highly appreciated.



Tom Kyte
January 27, 2006 - 11:24 am UTC

Windows 3000! cool :)

well, if the cache hit ratio is 100%, I don't see what could possibly be wrong... (sarcasm, sorry...)


burning the cpu at 100% isn't slowing down the server technically - server is running at top speed, it just doesn't have sufficient resources (eg: if you are at 100% cpu, you probably want to be at 150% cpu but you cannot be).

if aspnet_wp.exe is consuming the vast majority of the cpu, not too much I can say - if oracle.exe was, I could comment.



look at stats pack

Jim, January 27, 2006 - 12:15 pm UTC

Pure guess but high CPU usage and low IO would tend to have me suspect the app isn't using bind variables. But look at statspack.

Tom Kyte
January 28, 2006 - 12:09 pm UTC

the cpu is being used on this system by:

aspnet_wp.exe
</code> http://www.liutilities.com/products/wintaskspro/processlibrary/ASPNET_WP/ <code>

not oracle.exe - so not sure if that would apply.

We are not alone...

Abdul Seerae, February 02, 2006 - 5:35 pm UTC

Hi Tom,

Thank you for your comments. (Sorry for the typo - it should be Windows 2003 and not 3000).

It seems the issue is with ‘aspnet_wp.exe’. On searching google for 'aspnet_wp.exe' I got 100s of hits, most of them complaining about 99% CPU usage, irrespective of database (on oracle, mysql etc).

Regards


Workaround to pass Objects to Stored Procedure in ODP.NET

Anil Bishnoie, April 23, 2006 - 9:08 pm UTC

Hi,
Does oracle 9.2.X ODP.NET support passing hetrogenous object ( consisting of int and string members) from C#/ASP.NET call to a Stored Procedure with heterogenous Object Type as input Parameter.Is there a workaround /code snippet to do this using some LOB concepts using c#/odp.net ?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.