Home » RDBMS Server » Performance Tuning » reload of statements in library cache (oracle 9i, linux rhel3)
reload of statements in library cache [message #408248] Mon, 15 June 2009 07:56 Go to next message
suryakantnirala
Messages: 19
Registered: May 2009
Location: Mumbai
Junior Member
hi!
today in our database statements are reloaded so frequently.before today it was working properly.
so what can i do.
please help me.
Re: reload of statements in library cache [message #408255 is a reply to message #408248] Mon, 15 June 2009 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Find what has changed.

Regards
Michel
Re: reload of statements in library cache [message #408318 is a reply to message #408248] Mon, 15 June 2009 14:16 Go to previous messageGo to next message
amitzil
Messages: 9
Registered: June 2009
Junior Member
Hi,
What Oracle version are you running?
This happens usually when executing commands related to the optimizer like gathering statistics.

Liron Amitzi
Senior DBA consultant
Re: reload of statements in library cache [message #408394 is a reply to message #408248] Tue, 16 June 2009 02:04 Go to previous messageGo to next message
suryakantnirala
Messages: 19
Registered: May 2009
Location: Mumbai
Junior Member
yes i calculate statistics for all schema. then what i can do to increase the library cache hit ratio and buffer cache hit ratio
Re: reload of statements in library cache [message #408396 is a reply to message #408394] Tue, 16 June 2009 02:09 Go to previous messageGo to next message
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 #408399 is a reply to message #408248] Tue, 16 June 2009 02:16 Go to previous messageGo to next message
suryakantnirala
Messages: 19
Registered: May 2009
Location: Mumbai
Junior Member
i have oracle 9i (9.2)
but buffer cache hit ratio is also going down.
so it's take so much time to insert data in the table.
please suggest me what actions can i take. revert me as soon as possible.
Re: reload of statements in library cache [message #408403 is a reply to message #408399] Tue, 16 June 2009 02:25 Go to previous messageGo to next message
amitzil
Messages: 9
Registered: June 2009
Junior Member
Hi,
There is a chance that the new statistics result in bad performance.
Do you usually gather statistics or this was your first time?
Have you checked it on a test database?
Re: reload of statements in library cache [message #408435 is a reply to message #408248] Tue, 16 June 2009 03:52 Go to previous messageGo to next message
suryakantnirala
Messages: 19
Registered: May 2009
Location: Mumbai
Junior Member
it is my first time & i did not try it on test database.
i used dbms_stats package to gather statistics.
so what can i do now
Re: reload of statements in library cache [message #408482 is a reply to message #408435] Tue, 16 June 2009 06:58 Go to previous message
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
Previous Topic: How to tune latch: cache buffers chains
Next Topic: ORA-1652: unable to extend temp
Goto Forum:
  


Current Time: Sun Dec 04 04:52:10 CST 2016

Total time taken to generate the page: 0.11221 seconds