Search

Top 60 Oracle Blogs

Recent comments

Cache

PostgreSQL Shared Buffers vs free RAM

PostgreSQL, like all other database engines, modifies the table and index blocks in shared buffers. People think that the main goal of buffered reads is to act as a cache to avoid reading from disk. But that’s not the main reason as this is not mandatory. For example PostgreSQL expects that the filesystem cache is used. The primary goal of shared buffers is simply to share them because multiple sessions may want to read a write the same blocks and concurrent access is managed at block level in memory. Without shared buffers, you would need to lock a whole table. Most of the database engines use the shared buffers for caching. Allocating more memory can keep the frequently used blocks in memory rather than accessing disk. And because they manage the cache with methods suited to the database (performance and reliability) they don’t need another level of cache and recommend direct I/O to the database files. But not with Postgres.

Direct path and buffered reads again: compressed tables.

In the previous post on the decision between buffered and direct path reads I showed the decision is depended on the version. Up to and including version 11.2.0.2 the size of a segment needs to be five times small table threshold in order to be considered for direct path reads, and starting from 11.2.0.3 the database starts considering direct path reads starting from small table threshold. The lower limit just discussed is small table threshold or five times small table threshold with lower versions, upper limit is called “very large object threshold” (VLOT) and is five times the size of the buffercache, which is the threshold after which a table scan always is going via direct path.

Compiling Oracle Forms against functions using the 11g Function Result Cache…

I came across a rather annoying little bug yesterday…

One of the guys was trying to compile an Oracle Form on the app server and got this message.

*** ASSERT at file pdw1.c, line 4061
PSDGON missing. Can't get object number
Source Location = XNSPC0P99_2013_06_12_17_44_38__AB[71, 7]

Uncle Google pointed me in the direction of this MOS Note [ID 1276725.1].

It turns out it is a problem with the 11.1.0.7 client, which happens to be the version that ships with Forms and Reports Services. The Forms compilation fails when the form references a database function that uses the Function Result Cache in 11g.