parsing puzzle

From: Joe A-C <jwc7744_at_yahoo.com>
Date: Thu, 8 Apr 2010 18:40:16 -0700 (PDT)
Message-ID: <866348.96016.qm_at_web30004.mail.mud.yahoo.com>

We have an application that is suddenly doing a lot of parsing. The history behind this is that about 2 years ago the database was upgraded from 8i to 10g at which point performance went down hill. The diagnosis at the time was high amounts of parsing due to the fact that bind variables weren't being used. After increasing the shared pool didn't help, I eventually brought the problem under control by reducing the shared pool. The thinking was that since there was less memory to search before parsing (which inevitably happened) the time to run would be less. While the application was still slow it wasn't as painfully slow as it had been. The app was in this state until the vendor sent an upgrade which used more bind variables at which point I successfully went back to using auto memory management, thereby increasing the shared pool.

Things were relatively calm until a few weeks ago when the parsing issue started again. The app owner says that there have been no changes to the app and that there is no different processing taking place. The only thing that changed is an upgrade to the network. The app owner is saying that the network is slower but I don't see how this could be related to high parse rates.

I tried the original fix which was to reduce the shared pool to no avail. In fact it seemed to make things worse.

Another possibility is that the app was doing a lot of parsing before and now the network is the real slow down. I do see an increase in the number of network waits but the total time of network waits is a fraction of the time for concurrent waits. Here's an example where the number of network waits is > 5 million but the total wait time is 360 secs while the total wait time for concurrency is 89k secs:

Wait Class Waits %Timeouts Total Wait Time(s) Avg wait(ms) Waits/tx

Concurrency 	855,412  	1.32 	88,902  	104 	21.71
User I/O 	1,893,052 	0.01 	11,349  	6 	48.05
Other 	23,755 	1.67 	821 	35 	0.60
Commit 	40,550 	0.11 	671 	17 	1.03
Configuration 	757 	89.04 	669 	883 	  0.02
Network 	5,602,717 	0.00 	360 	0 	142.22
System I/O 	59,770 	0.00 	295 	5 	1.52
Application 	549 	0.00 	6 	10 	0.01

Can anyone think of how the network could affect the instance in this way?

Any ideas would be appreciated.

Thanks.
Joe       

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 08 2010 - 20:40:16 CDT