-
We have a database that has started to experience SEVERE spikes in Latch Waits (library cache). These spikes are in the range of 100K - 1M ms waited/sec. When the spikes occur, our users are almost completely locked-up until the latches come down.
Most of the documentation on Library Cache Latches tells you to tune your SQL. Unfortunately, this is a purchased application, and we have no ability to change the sql.
A couple of notes on our database:
-The database has ~80 Gb of data
-We have ~800 users on a typical day... using the same application for the most part.
-Our system is 8.1.6.3 on AIX 4.3.3
-We have 16 Gb of Memory
-Our SGA is ~3 Gig
-We are using MTS.
Does anyone have any idea what we can do to fix this problem?
Any help would be appreciated!
Thanks!
Jodie
-
look into metalink doc 100964.1
also calculate the sga
8.0.X
To approximate size of the SGA (Shared Global Area), use the following
formula:
((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + log_buffers) + 1MB
8.1.X
To approximate size of the SGA (Shared Global Area), use the following
formula:
((db_block_buffers * block size) +
(shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB
sonofsita
http://www.ordba.net
-
The size of the SGA was not our problem. We have done a lot of work there, and our SGA appears to be the appropriate size. But thanks for the help.
In case anyone is interested, we did solve our problem. We had a query that was doing a FULL table scan of a 9.5Gig table over and over. We forced the use of the index, and our problem went away!
Thanks!
Jodie
-
We had a query that was doing a FULL table scan of a 9.5Gig table over and over. We forced the use of the index, and our problem went away!
9.5G? Wow :-)
-
Most of the documentation on Library Cache Latches tells you to tune your SQL. Unfortunately, this is a purchased application, and we have no ability to change the sql.
seems like you did end up tuning the SQL anyway :-)
- Rajeev
Rajeev Suri
-
Actually, the Vendor came back with an "update" since the query appeared to not be using the index.
I agree SQL is what needed to be tuned, but Mgmt wanted a quick fix from the DBA's.
Oh Well!
-
next time if you cannot modify the query and you need to change or force a specific execution plan use stored outlines
-
A simple or materialised view would also do the trick.
Performance... Push the envelope!
-
Thanks for all your help!!
Jodie
-
next time if you cannot modify the query and you need to change or force a specific execution plan use stored outlines
Pando,
what is "stored outlines" ?
Thanks,
- Rajeev
Rajeev Suri
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|