Database cache and Pin for Oracle Performance Optimization

1. Use the view to test the database cache size:

If your enterprise opens a new business and develops a new application for this purpose, a new database is required as the background. You are responsible for regulating this new database. So how do you determine the size of the Shared Pool? The following test helps you determine the size of the Shared Pool. Note that the size of the Shared Pool is determined only by the estimated size. The size of the Shared Pool is suitable for your database. It takes a period of time for the database to run before it can be known by observing historical data. It seems like you did not know how much salt you put when cooking for the first time. The following method only tells you how much salt you put, and how much salt you put is delicious, this requires several labs. You put a key for the first time, and if it goes down, you can put a key and a half for the second time. The same is true for database adjustment. This is why we tried our best to explain the importance of historical data. Without historical data and data for reference, you don't know how much salt is suitable for this time. The following describes how to test the size of a shared pool.

First, set your shared pool to a very large value. If it is 10 Gb, set sga_target to a very large value, restart your data, and then start all applications in the database. After the application runs for a period of time, you can use the following statement to calculate the approximate size of the database cache:

Select mem1 + mem2 from (select sum (sharable_mem) mem1 from v $ db_object_cache) a, (select value * 250 mem2 from v $ sysstat where name = 'opened cursors current') B;

This statement contains two subqueries. The first subquery uses the v $ db_object_cache view to obtain the total memory occupied by all objects in the current database cache. In addition, Oracle allocates an additional 250 bytes of memory for each cursor to store some administrative information. The second subquery is to calculate the number of currently opened cursors and multiply it by 250. The result is the total memory allocated for the cursor by Oracle. The result of adding two subqueries is the size of the library cache required by your application.

I know the approximate size of the database cache. The general method is to use it with 2 left and use the result as the size of the entire shared pool.

If your applications are scattered and it is difficult to observe the memory usage of the Shared Pool and run them all in a centralized manner, there is also a simpler and wider method for evaluating the size of memory components such as the shared pool and Buffer cache. For the OLTP system, if no other software is run on the host except the database, we should reserve about 20% or 30% of the memory for the OS, about 50% to the SGA, and the rest to the PGA. In SGA, 40% can be allocated to the Buffer cache, and the share pool also accounts for 40%. The rest are allocated to other memory components in SGA. In an OLAP system, apart from leaving around 20% or 30% of memory for the OS, SGA and PGA can be as large as they are, or even exceed SGA. In SGA, the shared pool should be as small as possible, and most of the memory can be allocated to the Buffer cache.

No matter how you allocate it, we just estimate the size of memory components such as the shared pool in advance. The size of each memory component is appropriate. After the database is running for a period of time, you need to make specific adjustments based on historical data.

1 2 Next Page