x

Differences between DB_BUFFER_CACHE and LIBRARY_CACHE ?

What are the main differences between the predefined functions DB_BUFFER_CACHE and LIBRARY_CACHE ? Thank you in advance.

more ▼

asked Feb 04 '10 at 11:55 AM in Default

OracleApprender gravatar image

OracleApprender
771 68 73 75

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The library_cache is the region of memory where Oracle code (statements, procedures, functions) are stored for execution. If a statement, procedure or function is requested, the cache is checked to see if it's already available in memory. If so, it's executed immediately. If not, it's loaded to the cache and executed. This region is shared.

The db_buffer_cache is the region of memory where database data pages are stored. By data I mean that indexes, etc, are included. Configuration seems to be relatively complicated.

This chapter of Oracle's docs:

Memory Configuration and Use

could get you started on better understanding. That page refers to additional resources.

more ▼

answered Feb 04 '10 at 07:14 PM

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

11g introduces a 3rd memory area - the SQL Result Set Cache http://www.toadworld.com/EXPERTS/GuyHarrisonsImprovingOraclePerformance/insideSpotlight/OptimizingtheOracle11gResultCache/tabid/606/Default.aspx

"The Oracle 11g Result Set Cache stores entire result sets in shared memory . If a SQL query is executed and its result set is in the cache then almost the entire overhead of the SQL execution is avoided: this includes parse time, logical reads, physical reads and any cache contention overhead (latches for instance) that might normally be incurred."
Feb 04 '10 at 11:33 PM HillbillyToad
Great link. Thanks for the info.
Feb 05 '10 at 01:13 AM KillerDBA
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x375
x16

asked: Feb 04 '10 at 11:55 AM

Seen: 1296 times

Last Updated: Feb 04 '10 at 11:55 AM