The Oracle9i database has a very great enhancement in internal characteristics, one of the most exciting Oracle DBA excitement can dynamically set all Oracle SGA control parameters. Unlike 8i, the initialization parameters are placed in a text file and read it when the database is started, but Oracle9i can reset all Oracle parameters through the alter database and alter system commands. Before 9i, if you want to change some changes to the Oracle database, the Oracle administrator must turn off the database and reset the parameters in the init.ora file, and then restart the database. This reset is often done in the Oracle database that operates using OLTP mode during the day, to switch to the data warehouse mode. For the modification of the parameters for stopping and restarting the Oracle database, Oracle9i has obvious strengthens this, which makes it easier to implement the goals that the database continuously available. This ability to increase and narrow different regions in Oracle SGA provides some exciting new features to Oracle database administrators. Database activity in each region of SGA can be monitored independently, and can also be assigned and retrieved according to the mode of use in the Oracle database. Let's first see some of the following Oracle9i databases and Oracle8i databases. One of Oracle9i is a separate PGA space for all dedicated connections connected to the Oracle database. In Oracle8i, for dedicated Oracle connections, we need to allocate a separate area in memory, called Program Global Area or PGA. The PGA space contains Sort_Area_size and additional RAM control structures to maintain the status of the connection task. In Oracle9i, the PGA space has been replaced by a new memory space in Oracle SGA, which is set by PGA_AGGREGATE_TARGET parameters (see Figure 1). ********* Figure 1 *********** (Difference between Oracle8i and Oracle9i on memory allocation) Since all memory is allocated in Oracle SGA, Oracle Database Management Enter the memory allocated to the Oracle server and can be assigned to 80% of the ORACE server. Oracle recommends that the rest of the server is 20% memory to the operating system's task. When the user is connected to the Oracle9i database, the memory required by the sorting work will be assigned in the Oracle9i's PGA_AGGREGATE_TARGET area. This can make Oracle9i run faster than Oracle8i, because the memory is only assigned during the required period, and it can be released immediately to other connected Oracle tasks. Dynamically modified SGA area Since Oracle administrators can now increase and reduce SGA's full area, we can quickly check what is the SGA area, so we can know how Oracle DBA monitors these areas and is more effective for Oracle databases. Redistribute memory. The area of the SGA can be divided into the following parts. Data buffers - Oracle9i have up to 7 separate data buffers to save disk delivery data blocks. These include traditional Keep Pool, Recycle Pool, and Default Pool, which is also established for each Oracle database (2k, 4K, 8K, 16K, and 32K), which is built (see Figure 2).
Figure 2 Independent Oracle9i Data Buffer We can monitor the hit rate of these seven data buffers. If the buffer hits, we can reduce the memory allocated to these data buffered, and reassign them An additional memory is required in other Oracle instances. When the data buffer hits the hit rate (DBHR), we can separate the memory by a data buffer and reassigned it to other data buffers (as shown in Figure 3). Figure 3 Reassigning a memory shared pool - Oracle9i's shared pool between Oracle9i's data buffer is an important role to analyze and execute Oracle SQL statements. Low library cache hits the memory that is allocated to library cache, when Shared Pool needs to make a lot of analysis and execution of SQL statements, Oracle9i's database administrators can use ALTER SYSTEM to add additional memory for Shared Pool. PGA Area - The memory assigned to the PGA_AGGREGATE_TARGET is used to let the Oracle connection maintenance and connection-related information (such as a cursor state), and sort the result set of SQL. Log Buffer - For Oracle Redo Log buffer, we can see in the frequency of log switch (log transformation). Oracle administrators can monitor activities in the Redo Log area, and dynamically increase memory when the Oracle database requires additional memory as the original buffer area service. Let us now take a closer look between the following memory areas. Changing the memory allocation of the PGA When one of the following conditions is true, we will need to dynamically modify the PGA_AGGREGATE_TARGET parameter. . When V $ sysstat is exceeded with the statistical value of "Estimated PGA Memory for One-Pass", we need to increase the value of the PGA_AGGREGATE_TARGET. . When V $ sysstat is more than one percent in "Workarea Executions - Multipass", the database will be benefited from more memory. You may be too high to estimate the space of the PGA memory. When V $ SysStat, "Workarea Executions - Optimal" is 100%, you can consider the value of the PGA_AGGREGATE_TARGET. We can see if Shared Pool needs more memory through a simple script. Meaning Library Cache lost rate
set lines 80; set pages 999; column mydate heading 'Yr Mo Dy Hr..' format a16column c1 heading "execs" format 9,999,999column c2 heading "Cache Misses | While Executing" format 9,999,999column c3 heading "Library Cache | Miss Ratio" Format 999.99999break on mydate skip 2; select to_char (snap_time, 'yyyy-mm-dd hh24') MyDate, SUM (New.Pins-Old.Pins) C1, SUM (New.Reloads-Old.Reloads) C2, SUM new.reloads-old.reloads) / sum (new.pins-old.pins) library_cache_miss_ratiofrom stats $ librarycache old, stats $ librarycache new, stats $ snapshot snwherenew.snap_id = sn.snap_idandold.snap_id = new.snap_id-1andold.namespace = new.NamespaceGroup Byto_CHAR (Snap_time, 'YYYY-MM-DD HH24'); Cache Missesyr. Mo Dy Hr. EXECS While Executing Library_Cache_Miss_Ratio ------------------- ----- -------------------------------------- 2001-12-11 10 10, 338 3.000732001-12-14 10 190, 707 202.001062001-12-16 10 2,803 11.00392 Viewed by the above example, between 9:00 AM to 10: AM Shared pool significantly lacks memory. We can dynamically reset the shared_pool parameters during this period to allocate additional memory from DB_CACHE_SIZE. SGA's thresholds can be seen from the table below that several obvious thresholds can be utilized when monitoring the memory of SGA. We can write some scripts and integrate some intelligence so you can reset the SGA when the demand is changed.
Ram Area Too-Small Condition Too-Large Condition Shared Pool Library Cache Misses No Misses Data Buffer Cache Hit Ratio <90% Hit Ratio> 95% PGA AGGREGATE HIGH MULTI-pass Executions 100% Optimal Executions
Table 1: Abnormal conditions in SGA Indicates that the Load Oracle9i of the SGA memory area uses some new internal views or add new columns to the existing view to help see the internal memory allocation in Oracle9i. The following new V $ view can help monitor the memory usage of Oracle9i connections. V $ process - add three new columns to monitor the use of PGA memory in Oracle9i, the name of the new column is PGA_USED_MEM, PGA_ALLOC_MEM, and PGA_MAX_MEM. V $ sysstat - Added a lot of new statistical rows, including Area Statistics for Optimal, One-Pass, and Multi-Pass. V $ PGASTAT - This new view shows all of the background processes and dedicated PGA memory uses V $ SQL_PLAN - this new view contains all currently executed SQL execution plan information. This is very attractive for performance adjustment experts that require optimal SQL statements. V $ Workarea - This new view provides details of the cumulative memory statistics for Oracle9i connections. V $ Workarea_Active - This new view provides internal memory usage information for all SQL statements that are currently executing. The purpose is to monitor memory usage in SGA through these V $ view, and then reassign the memory through the alter system command according to the processing requirements of the Oracle instance. Let's see some of these new Oracle9i features and scripts, which help us use detailed memory usage. Of course, we cannot detail all the technologies here, let us see a simple example, which decides when to reset the PGA_AGGREGATE_TARGET parameter by using the V $ Sysstat view. The following query can be obtained from all the number and percentages of Work Areas after starting from the database instance. Work_area.sqlselectname profile, CNT, Decode (Total, 0, 0, Round (CNT * 100 / Total)) PercentageFrom (SELECT NAME, VALUE CNT, (SUM (Value) OVER ()) TotalFromv $ sysstat wherename like 'Workarea EXEC% ');
The output of this query may be as follows:
Profile CNT Percentage -------------------------------------------------------------------------- -------- Workarea Executions - Optimal 5395 95Workarea Executions - OnePass 284 5Workarea Executions - Multipass 0 0
The output of this query is used to tell DBA when to dynamically adjust the PGA_AGGREGATE_TARGET parameter. In general, if the execution of Multi-Pass is greater than 0, it is necessary to increase the value of the PGA_AGGREGATE_TARGET and reduce its value when Optimal Execution is 100%. We can also use the V $ PGASTAT view to determine the memory usage of our Oracle instance. The V $ PGASTAT view provides an instance-level summary statistics for PGA use and automatic memory management. The following scripts provide statistics for overall memory usage of all Oracle9i connections. The following is a simple script used to detect PGA memory in Oracle9i.
check_pga.sqlcolumn name format a30column value format 999,999,999selectname, value fromv $ pgastat; The output of this query might look like the following: NAME VALUE --------------------- ------------------------------------------ Aggregate PGA Auto Target 736,052,224global Memory bound 21,200total expected memory 141,144total PGA inuse 22,234,736total PGA allocated 55,327,872maximum PGA allocated 23,970,624total PGA used for auto workareas 262,144maximum PGA used for auto workareas 7,333,032total PGA used for manual workareas 0maximum PGA used for manual workareas 0estimated PGA memory for optimal 141,395 Maximum PGA Memory for Optimal 500, 123, 520ESTIMATED PGA MEMORY for One-Pass 534,144maximum PGA Memory for One-Pass
We can see the following statistics in the V $ PGASTAT display above. Aggregate PGA Auto Target - This column gives all memory available for Oracle9i connections. We have already mentioned that this value is set by PGA_AGGREGATE_TARGET. Global Memory Bound - This statist represents the maximum value of Work Area, and Oracle recommends that you should increase the value of the PGA_AGGREGATE_TARGET when the statistics falls to 1M. Total PGA Allocated - This statistics showing the high water bit lines used in all PGA memory in the database. When using an increase, you should see the value of this value close to the PGA_AGGREGATE_TARGET. Total PGA Used for Auto Workareasures - This statistical monitoring memory is used or all of which run in all connections in automatic memory mode. What to remember is that not all internal processes use automatic memory characteristics. For example, Java and PL / SQL will allocate memory, but this part will not be counted in this value. Therefore, we can use the value of the overall PGA to subtract this value to get the memory used by Java and PL / SQL. Estimated PGA Memory for Optimal / One-Pass - This statistical estimation Optimal mode Performs the memory required by all connection tasks. To remember, if Oracle9i is insufficient, it will call Multi-Pass operations. This statistics are very important for monitoring memory in Oracle9i, and most Oracle DBA will increase the PGA_AGGREGATE_TARGET to this value. Now we have already understood this concept, let's take a look at what methods for automatically reconfiguring SGAs. In a UNIX environment, it is very simple to modify the memory configuration by timing task when processing the demand change. For example, many Oracle databases operate in OLTP mode in general working hours, running a large number of bulk reports on memory requirements at night. We know that in an OLTP database, we should set DB_CACHE_SIZE to a large value, and in a bulk task in a large demand memory, you need to assign additional memory to the PGA_AGGREGATE_TARGET. The following UNIX scripts can be used to reset the OLTP and DSS SGA values and do not stop instances. In this example, we assume that there is an Oracle server with 8GB memory. We also assume that 20% of memory is used for UNIX, while the remaining 6GB memory is used as Oracle and Oracle connections. These scripts are used in HP / UX or Solaris, and accept $ Oracle_SID as a parameter. DSS_CONFIG.KSH scripts will run at 6:00 P.m per night to reset Oracle's bulk task in the evening. DSS_CONFIG.KSH #! / bin / ksh # first, we must set the environment..... Oracle_sid = $ 1export oracle_sidoracle_home = `CAT / etc / oracle_Home =` CAT / etc / oracle_home = `$ ORACLE_SID: | CUT -F2 -D ':'` # Oracle_Home = `CAT / VAR / OPT / ORACLE / ORATAB | GREP ^ $ ORACLE_SID: | CUT -F2 -D ':'` Export Oracle_HomePath = $ ORACLE_HOME / BIN: $ PATHEXPORT PATH $ ORACLE_HOME / BIN / SQLPLUS -S / NOLOGIN <
connect system / manager as sysdba; ALTER SYSTEM set db_cache_size = 1500m; ALTER SYSTEM set shared_pool_size = 500m; ALTER SYSTEM set pga_aggregate_target = 400m; exit Now that we know a common way to modify Oracle configuration, we are also very easy! Seeing a technique can be developed simply to continuously monitor Oracle's processing requirements, and use the ALTER SYSTEM to modify according to existing database requirements. Conclusion Although memory management in Oracle9i still requires a lot of manual operation, most of the Oracle administrators can use tools to continuously monitor memory usage in Oracle SGA, and can automatically redistribute according to the current usage in Oracle Instance RAM. This allows Oracle administrators to flexibly reset their system based on changes in the system.
