USING Auotomatic Shared Memory Management

USING Auotomatic Shared Memory Management has learned about amm some time ago. Today I want to learn about ASMM. When asmm is started, it is very simple to set the sga_target parameter. If asmm is set, the sga, such as buffer cache, large pool, java pool, streams pool, and shared pool, dynamic adjustments are made based on work. However, log buffer, db keep, db recycle buffer, and non-standard blocks cannot be automatically adjusted, in addition, there is a unit called granules in workers. Eg:

SQL> select name,bytes/1024/1024||'M' M from v$sgainfo;NAME                             M-------------------------------- -----------------------------------------Fixed SGA Size                   2.1118316650390625MRedo Buffers                     3.875MBuffer Cache Size                124MShared Pool Size                 148MLarge Pool Size                  4MJava Pool Size                   4MStreams Pool Size                4MShared IO Pool Size              0MGranule Size                     4MMaximum SGA Size                 445.98828125MStartup overhead in Shared Pool  64MNAME                             M-------------------------------- -----------------------------------------Free SGA Memory Available        156M12 rows selected.SQL> 

 

It can be seen that all pools are integer multiples of granule. If you set a parameter that is not an integer multiple of granule, take the adjacent value. For example, if I set the shared pool to 150 M, the actual value is 152 M. (Except for redo buffer) asmm has an sga_max_size value which is similar to the memory_max_target parameter of amm. Note: If you do not specify an sga_max_size value, a sum value of all components or a default value will be selected during database initialization. The example is as follows: first, determine the sga_target size and then disable amm.

SQL> SELECT (SELECT SUM (VALUE)/1024/1024 FROM V $ SGA)-(SELECT CURRENT_SIZE/1024/1024 FROM V $ SGA_DYNAMIC_FREE_MEMORY) AS SGA_TARGET FROM DUAL; SGA_TARGET----------289.988281SQL> SQL> SHOW PARAMETER MEMORYNAME TYPE VALUE values -------------------------------------- hi_shared_memory_address integer 0memory_max_target big integer 448Mmemory_target big integer 448Mshared_m Emory_address integer 0SQL> show parameter partition type value =----------- using pga_aggregate_target big integer 0SQL> SELECT * from v $ values; using PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME when descriestd_overalloc_count limit --------------------------- ------------- --- --------------- ---------- ------------------- ----------------------------- -------------------- 20447232. 125 ON 477455360 229211 29921280 40894464 94 6. 25 ON 477455360 229211 29921280 94 6 81788928. 5 ON 477455360 215694 0 100 0 122683392. 75 ON 477455360 215694 100 0 163577856 0 477455360 1 ON 215694 100 0 196292608 1.2 ON 477455360 215694 0 100 0 229008384 ON 1.4 0 215694 0 100 0 261724160 1.6 ON 477455360 215694 0 100 294439936 ON 1.8 477455360 0 215694 0 100 2 ON 327155712 477455360 0 215694 3 ON 100 490733568 0 477455360 0PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ------------------------------------------------------------------------- -------------- -------------------------------------------- 654311424 4 ON 477455360 215694 0 100 0 981467136 6 ON 477455360 0 215694 0 100 8 ON 1308622848 477455360 0 215694 014 rows selected. SQL> SELECT 81788928/1024/1024 m from dual; M ---------- 78SQL> SQL> alter system reset memory_max_target scope = spfile; System altered. SQL> alter system reset memory_target scope = spfile; System Ltered. SQL> alter system set sga_target = 310 M scope = spfile; System altered. SQL> alter system set pga_aggregate_target = 78 M scope = spfile; System altered. SQL> startup forceORACLE instance started. total System Global Area 405020672 bytesFixed Size 2213816 bytesVariable Size 251660360 bytesDatabase Buffers 146800640 bytesRedo Buffers 4345856 bytesDatabase mounted. database opened. SQL> show parameter sg ANAME type value =----------- define lock_sga boolean =boolean FALSEsga_max_size big integer 388Msga_target big integer 312 MSQL> show parameter memoryNAME type value =----------- specify when integer 0memory_max_target big integer 0memory_target bi G integer 0shared_memory_address integer 0SQL> show parameter pga_aggregateNAME type value values ------------- -------------------------------- pga_aggregate_target big integer 78 MSQL> good to start the experiment. First, I do not specify the sga_max_size parameter. Eg: SQL> alter system reset sga_max_size; System altered. SQL> If you do not specify SGA_MAX_SIZE, then Oracle Database selects a default value that is the sum of all components specified or defaulted at initializationSQL> alter system reset sga_max_size; System altered. SQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> startupORACLE instance started. total System Global Area 325685248 bytesFixed Size 2213056 bytesVariable Size 167775040 bytesDatabase Buffers 150994944 bytesRedo Buffers 4702208 bytesDatabase mounted. database opened. SQL> select sum (value) from v $ sga; SUM (VALUE) ---------- 325685248SQL> If you do specify SGA_MAX_SIZE, and at the time the database is initialized the value is less than the sum of the memory allocated for all components, either explicitly in the parameter file or by default, then the database ignores the setting for SGA_MAX_SIZE and chooses a correct value for this parameter. note: The STATISTICS_LEVEL initialization parameter must be set to TYPICAL (the default) or ALL for automatic shared memory management to function.

 

1) To start asmm to start asmm, you need to set the sga_target parameter, and set statistics_leve to typical or all, and set the following parameter to 0. You also need to set memory_target and memory_max_target parameters. Large_pool_size, java_pool_sizeshared_pool_sizestreams_pool_sizedb_cache_size, and then set the following parameter manually. Parameter 2) monitoring asmm can be monitored using the v $ sga_target_advice view.

SQL> select * from v$sga_target_advice;  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS---------- --------------- ------------ ------------------- -------------------       156              .5           40              1.0256               10995       234             .75           39                   1                9770       312               1           39                   1                9770       390            1.25           39                   1                9770       468             1.5           39                   1                9770       546            1.75           39                   1                9770       624               2           39                   1                97707 rows selected.SQL> 

 

We can see the recommended value from oracle. In addition, the view corresponds to the following in awr: capture awreg first:

[oracle@oracle-one ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 6 00:09:51 2013Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> exec dbms_workload_repository.create_snapshot;PL/SQL procedure successfully completed.SQL> @/opt/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sqlCurrent Instance~~~~~~~~~~~~~~~~   DB Id    DB Name      Inst Num Instance----------- ------------ -------- ------------2735005832 RHYS                1 RHYSSpecify the Report Type~~~~~~~~~~~~~~~~~~~~~~~Would you like an HTML report, or a plain text report?Enter 'html' for an HTML report, or 'text' for plain textDefaults to 'html'Enter value for report_type: htmlType Specified:  htmlInstances in this Workload Repository schema~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id     Inst Num DB Name      Instance     Host------------ -------- ------------ ------------ ------------* 2735005832        1 RHYS         RHYS         oracle-oneUsing 2735005832 for database IdUsing          1 for instance numberSpecify the number of days of snapshots to choose from~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Entering the number of days (n) will result in the most recent(n) days of snapshots being listed.  Pressing <return> withoutspecifying a number lists all completed snapshots.Enter value for num_days: 1Listing the last day's Completed Snapshots                                                        SnapInstance     DB Name        Snap Id    Snap Started    Level------------ ------------ --------- ------------------ -----RHYS         RHYS                 9 06 Aug 2013 00:07      1                                 10 06 Aug 2013 00:08      1                                 11 06 Aug 2013 00:10      1Specify the Begin and End Snapshot Ids~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enter value for begin_snap: 9Begin Snapshot Id specified: 9Enter value for end_snap: 11Specify the Report Name~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrrpt_1_9_11.html.  To use this name,press <return> to continue, otherwise enter an alternative.Enter value for report_name:Using the report name awrrpt_1_9_11.html

 

Let's take a look at this point: the view v $ sga_target_advice. In addition, these are all view-based in awr. It seems much more convenient. . I don't think it's interesting to get it. ######################################## ### (^ ω ^) .............................. &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&