Re: parsing puzzle
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, jwc7744@xxxxxxxxx
- Date: Fri, 9 Apr 2010 15:24:40 -0700 (PDT)
Just curious- What do the results look like for the SQL statement(s) that is
being executed by so many users concurrently and what is the I/O datafile
involved in the bottleneck underneath it?
By the looks of this, it should be pretty apparant just from V$SQL if you look
at:
select * from v$SQL
order by concurrency_wait_time, user_io_wait_time desc;
If it's "consistent", the culprit(s) should show up pretty consistently...
Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Thu, 4/8/10, Joe A-C <jwc7744@xxxxxxxxx> wrote:
From: Joe A-C <jwc7744@xxxxxxxxx>
Subject: parsing puzzle
To: oracle-l@xxxxxxxxxxxxx
Date: Thursday, April 8, 2010, 7:40 PM
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
