|
|
|
|
|
|
|
|
| Re: reload of statements in library cache [message #408396 is a reply to message #408394] |
Tue, 16 June 2009 02:09   |
amitzil
Messages: 9
Registered: June 2009
|
Junior Member |
|
|
What Oracle version are you running?
If you gathered statistics, that explain the invalidation you have. It will take some time until users will execute SQLs in the system and it will come stable again.
The reason is that Oracle caches SQL queries in the library cache. If you gather statistics all the SQLs get invalidated (default in 9i, not the default in 10g, but can be set). In this case, ALL SQLs will not use the cache but will be reparsed again. That causes the library cache to be busy. But it should come back to normal after a while (I can't say when, depends on your system).
If this is not the case, I guess there was a change (database parameters, application, etc.) and you should identify it.
Liron Amitzi
Senior DBA consultant
|
|
|
|
|
|
|
|
|
|
| Re: reload of statements in library cache [message #408482 is a reply to message #408435] |
Tue, 16 June 2009 06:58 |
amitzil
Messages: 9
Registered: June 2009
|
Junior Member |
|
|
It's a problem...
I don't want to guess a solution and it depends on your application needs.
Usually, you shouldn't gather statistics for the first time on a production system since it changes the entire query optimization and can cause problems (as you have).
You can delete the statistics using dbms_stats and you will use RBO (rule based optimizer) again (assuming that you really didn't have any statistics before).
The real solution is to do a tuning session on the database and set the optimizer parameters (for example, setting optimizer_index_cost_adj to 5 or 10 if this is an OLTP system).
Liron
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
