Oracle KEEP Pool - How Does it help?

When a session requests for the data, Oracle first tries to find the data in the Buffer Pool. If its not present, Oracle fetches the data from the disk (which is called the physical reads) and places in the buffer pool. Once the data is placed in the buffer pool, different sessions can access the data for completing the session requirement. However, entire data from the database cannot be placed in the buffer pool (unless you have abundant memory, still not advisable). So Oracle does a cleanup mechanism to keep the buffer pool usage in a controlled manner and within the set size limits.

If you are sure that the data from specific objects will be always accessed by the application, like a User table. When the first session access this data, the data is moved to the buffer pool. At a later point, for some reason, Oracle decides to evict this data from the buffer pool to give space for some other incoming data, the next access of the User table data will be slower as it incurs a physical read. In such cases, we use a feature called KEEP Pool.

KEEP Pool is also a buffer pool but specifically designated for objects that are defined to be using the KEEP Pool during the object creation itself. While defining the objects, it can be mentioned that the data should be pulled into the KEEP pool rather than the DEFAULT BUFFER POOL. This way, the data will not be evicted from the buffer even if the clean up mechanism of default buffer pool is kicked in. So the KEEP pool gives a higher probability for the data to be in buffer since all objects by default goes to the Default Buffer Pool only and not to the KEEP Pool. The syntax for attaching the object to the keep pool is shown below

ALTER TABLE MYOBJECT storage (buffer_pool keep);

For e.g. if there are only 20 objects(tables, indexes) defined to be in the KEEP pool, only the data from these 20 objects will be present in the KEEP Pool. Even if the default buffer pool evicts data by means of the LRU algorithm because of any reason, the data in the KEEP Pool will not be impacted.

In practice, the small tables in the application are normally the candidate for the KEEP Pool. Oracle generally tends to prefer Full table scans for smaller tables and hence the chances of the data getting evicted from the default pool is high. So these small tables can be attached to the KEEP Pool and hence will not be evicted.

When the AMM (Automatic Memory Management) is turned on for Oracle10g, keep in mind that the size of KEEP pool is not part of the SGA Target. (on a side note, so is for RECYCLE Pool, will cover the RECYCLE Pool in later blogs)

Trip to Mackinac Island

During the weekend, I made a quick trip to Mackinac Island. We spent around 6 hours on the Mackinac Island and a night in the St. Ignace City( City of the Mainland). Well, I'm not quite sure as to how I spent the 6 hours on the Island :-) .

The Mackinac Bridge connects the Lower Peninsula and Upper Peninsula of Michigan State. The bridge is the 12th longest suspension bridge in the world (wiki). The view of the bridge from the Road and from Water (while on the ferry) is spectacular. The ferry took us from the main land to the Mackinac Island. On the island, just like other tourist attractions, the market is impressive. Lot many chocolate fudges shop, restaurants, bike rentals, tour operators were seen.
We first visited the Butterflies museum. The museum had two rooms, one with many butterflies flying around and the other with few glass cases with insects. Then we visited the museum that describes the history of the Mackinac Bridge and the Island. There were other places, which were good but did not visit, which includes the Mackinac Fort (saw it from outside and did not feel that it was worth climbing the stairs), Arch Rock (again had to walk and then climb the stairs).
The impressive part of the journey was the Ferry from the Island and back. The speed jets gives you a view of the Mackinac Bridge and also of the beautiful Lake (one of the 5 Great lakes of Michigan).

High CPU usage of database

In my experience ,Oracle database is capable of taking good amount of load and can utilize the hardware power very effectively for scaling. However in some case , you might find that the CPU usage by the database is considerably high. In my experience , these factors can also cause a high CPU utilization

• Queries performing excessive work to achieve desired results. This could be more data blocks scanning, more nested loops etc.
• Table setups can be cause of concern if there is large amount of row chaining etc.
• Large amount of house keeping work on database like archival sweep, rman processes etc.
• Application keeps opening new physical connections to the database instead of using the connection pool. Even though application servers configure the connection pool, the connection pool might open new physical connections for all transactions due to wrong set up of connection pool.
• XA Transaction has a higher impact on the database as it needs to perform the house keeping activity like managing the transactions stages like prepare, commit etc .
• When less number of CPUs are present and more number of processes are present on the system, the system usage will go high due to context switching.
• As a matter of fact, the CPU utilization will go invalid or the system starts behaving in an awkward manner when the CPU utilization hits above 85%.

In some cases, correcting the above symptoms would fix the high CPU usage. However , in many other cases, it might be that the applications needs that much processing power to support the database operations.

Article on Cloud Computing

My first article on the web got published few weeks back. The article can be found @

http://ajax.sys-con.com/node/938581

The article concentrates on the online software distribution using the cloud.

Hope you enjoy reading it.