How to estimate the PGA, SGA size, configuration, database server memory
ORACLE to the proposal: OLTP systems PGA = (Total Memory) * 80% * 20%. DSS systems PGA = (Total Memory) * 80% * 50%.
ORACLE recommends a database server, sub-80% of the memory to the database, 20% of the memory to the operating system, how it to a database server with memory?
SQL> select * from v $ pgastat;
NAME VALUE UNIT
————————————————– ————– ———- ————
aggregate PGA target parameter 104857600 bytes
—– This value is equal to the value of parameter PGA_AGGREGATE_TARGET, if this value is 0, disable the automatic PGA management.
aggregate PGA auto target 75220992 bytes
—– Said how much memory to the PGA also provides automatic operation mode, this value is usually close to pga_aggregate_target-total pga inuse.
global memory bound 20971520 bytes
—– Workspace implementation of the maximum, if the value is less than 1M, immediately increase the size of PGA
total PGA inuse 30167040 bytes
—– Total size of the current allocation of PGA, this value may be greater than the PGA, PGA settings if too small. This value is close to the select sum (pga_used_mem) from v $ process.
total PGA allocated 52124672 bytes
—– Take the total size of work area
maximum PGA allocated 67066880 bytes
total freeable PGA memory 0 bytes —- no idle PGA
process count 23 —- 23 present a process
max processes count 25
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 8891392 bytes
maximum PGA used for auto workareas 22263808 bytes
total PGA used for manual workareas 0 bytes — 0 automated management
maximum PGA used for manual workareas 0 bytes — 0 automated management
over allocation count 0
If the PGA set too small, leading to sometimes more than PGA_AGGREGATE_TARGET PGA value here is 0, indicating no expansion of PGA values greater than the TARGET, if this value occurred, then the increase in PGA size.
bytes processed 124434432 bytes
extra bytes read / written 0 bytes
cache hit percentage 100 percent — 100% hit rate, if the small increase in PGA
recompute count (total) 6651
19 rows selected
SQL> select max (pga_used_mem) / 1024/1024 M from v $ process; —- the current maximum memory consumption of a process
SQL> select min (pga_used_mem) / 1024/1024 M from v $ process where pga_used_mem> 0; — process consumes less memory
SQL> select max (pga_used_mem) / 1024/1024 M from v $ process; —- process has the maximum memory consumption
SQL> select sum (pga_used_mem) / 1024/1024 from v $ process; —- total current consumption of the PGA process
SUM (PGA_USED_MEM) / 1024/1024
How to set the PGA do? We can stress test stage, the simulation about the operation of the system, then run
select (select sum (pga_used_mem) / 1024/1024 from v $ process) / (select count (*) from v $ process) from dual; be a process taking up about how much memory, and then estimate how much total system connection, For example, a total of 500 connections,
So Sessions = 1.1 * process +5 = 500, then the processes = 450, multiplied by a process consumes memory, you can roughly estimate how much PGA need to set up.
Best to set the value of PGA than the calculated value of larger, PGA values configured, you can depending on the system of nature, if the system is OLTOP, then the total memory can be set to PGA/0.16, eventually able to estimate the SGA the size of the proposal, with some memory or more, cheaper anyway.
The following excerpt eygle on a process to allocate the maximum memory (serial operation) of the rules:
10gR1 before the operation for the serial (non-parallel) of a process to allocate the maximum memory is min (5% pga_aggregate_target, 100m)
10gR2 after the operation for the serial (non-parallel) of a process to allocate the maximum memory the following rules:
If pga_aggregate_target <= 500m, then the largest memory for the 20% * pga_aggregate_target.
If the 500m <pga_aggregate_target <= 1000m, then the maximum memory 100m.
If the 1000m <pga_aggregate_target <= 2.5G, then the maximum amount of memory to 10% * pga_aggregate_target.
If pga_aggregate_target> 2.5G, then the maximum amount of memory to 2.5G.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x $ ksppi x, SYS.x $ ksppcv y
3 WHERE x.inst_id = USERENV (‘Instance’)
4 AND y.inst_id = USERENV (‘Instance’)
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE ‘% & par%’
NAME VALUE DESCRIB
————————————————– —————————— ——————– ————————————————– ———- —————————————- —————————————-
_smm_max_size 20480 maximum work area size in auto mode (serial)
SQL> show parameter pga
NAME TYPE VALUE
———————————— ———– — —————————
pga_aggregate_target big integer 100M
Here I have a process to allocate the maximum memory 20M, because my PGA = 100M, consistent with the above rules.
Implicit argument that a process to allocate _smm_max_size largest memory.
Buy piner the “oracle high availability environment,” a book, just to take advantage of this time to learn about.
To summarize what you see published here, starting today, the first chapter on the content of SGA and PGA.
Will continue to be summarized later in this publication and share.
SGA and PGA is structured as follows chart:
Sqlp> show sga
Or select * from v $ sga;
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 176161448 bytes
Database Buffers 109051904 bytes
Redo Buffers 2945024 bytes
Fixed Size: includes database and examples of control information, status information, dictionary information, startup was fixed in the SGA, the will not change.
Variable Size: include shard pool, large pool, java pool, stream pool, cursor area and other structures
Database Buffers: a database where the data block buffer, is the largest local SGA decided to database performance
Redo Buffers: REDO buffers provide a place, too much is not needed in the OLAP
V $ sgastat recorded some statistical information SGA
V $ sga_dynamic_components save SGA can be adjusted manually adjust the records of some of the region
Shard_pool_size determine its size, 10g automatic management of the future
Shard_pool in the data dictionary and the control region of the user can not directly control, and the users only sql buffer (library cache).
The process will often visit with DBMS_SHARED_POOL.KEEP stored procedure or package the package pin in the shared pool.
Manually remove the contents of the shared pool: alter system flush shard_pool;
Shared pool related to several common view:
V $ sqlarea records all sql statistics, including the implementation of the number of physical time, logical time, time-consuming
V $ sqltext_with_newline full show sql statement to mark through hash_value statement, piece sorting
V $ sql_plan save the sql in the implementation plan, through the tool to view
V $ shared_pool_advice forecast on the shared pool can be adjusted reference SGA
In the OLTP system requirements data buffer hit ratio above 95%
select sum (pins) “execution”, sum (pinhits) “hits”,
((Sum (pinhits) / sum (pins)) * 100) “pinhitration”,
sum (reloads) “misses”, ((sum (pins) / (sum (pins)
+ Sum (reloads))) * 100) “relhitratio”
from V $ librarycache
|PINS||NUMBER||Number of times a PIN was requested for objects of this namespace|
|PINHITS||NUMBER||Number of times all of the metadata pieces of the library object were found in memory|
|RELOADS||NUMBER||Any PIN of an object that is not the first PIN performed since the object handle was created, and which requires loading the object from disk|
Oracle to obtain from the data buffer in the database called cache hit, to get the feet from the disk cache miss
Data buffer in the data block through the dirty list (dirty list) and the LRU list (LRU list) to manage.
Data buffer can be broken down into: default pool, keep pool, recycle pool corresponding parameters db_cache_size, db_keep_cache_size, db_recycle_size buffer size, respectively,
From the start oracle 9i support different block sizes of table space, the corresponding block sizes for different tablespaces of different block size specified data buffer, different data block size corresponding db_nk_cache_size buffer can be specified, where n can be 2,4,6,16 or 32
V $ db_cache_advice forecast data buffer, you can do to adjust the reference data buffer
V $ bh, x $ bh records the data block buffer in the data buffer in the case, by this view, the system can find the hot block. By the following statement in the top 10 hot spots to find systems where hot objects fast:
Select / * + rule * / owner, object_name from dba_objects
Where data_object_id in
(Select obj from
(Select obj from x $ bh order by tch desc)
Where rownum <11);
Related to the process used to preserve the user’s memory segment.
Started from 9i PGA automatic management, pga_aggregate_target parameter specifies the session to use the maximum total PGA memory limit. Workarea_size_policy parameters for the switch automatic PGA memory management functions, auto / manual
In OLTP environments, automatic PGA management if set to some value, such as 2G or so will be able to meet the system requirements.
Automatic memory management:
Starting from 9i, sga_max_size parameter SGA memory size, not the dynamic changes
From the 10g start sga_target specified parameters, all the SGA components such as: shared pool, data buffer, large pool do not have to manually specified, Oracle will automatically manage. This feature is the automatic shared memory management ASMM. If you set sga_target = 0, will automatically turn off the automatic shared memory management functions. Sga_target size can not exceed sga_max_size size.
Manual management of SGA:
Alter system set sga_target = 2000m;
Alter system set db_cache_size = 1000m;
Alter system set shared_pool = 200m;
Alter system set sga_target = 0 ——— turn off automatic shared memory management ASMM
11G after the sga + pga memory can automatically manage the AMM, the relevant parameters memory_max_target memory_target. Set up after these two parameters do not care about the SGA and PGA
11g manual memory management:
Alter system set memory_target = 3000m;
Alter system set sga_target = 2000m;
Alter system set pga_aggregate_target = 1000m;
Alter system set memory_target = 0 ;——— turn off the automatic memory management AMM
SGA + PGA is best not to exceed 70% of the total memory
June 19, 2010 - Posted by ardhian | Database Administration, Oracle | 1m, aggregate, automated management, configuration database, database server, dss, dss systems, global memory, implementation, max processes, operating system, operation mode, Oracle, PGA, proposal, select sum, server memory, sql, target parameter
No comments yet.
Just another guy who wish to be an expert one day. Used to live in Bandung “Paris van Java“. Now running his own Linux, Computer Network, Oracle Database, and SAP private consultant in Surabaya City “City of Heroes“. Try to share his Basis knowledge to the real world.
If this blog information useful for you, please consider to donation to help me maintain and update this blog regularly.
My paypal account : firstname.lastname@example.org
Want to contact me directly : Ardhian
- Brief Description of SAP Executable
- DB2 Optimized for SAP – Set it and forget it
- Blink 182′s song lyric on SAP standard report
- Update to the SAP on Oracle -> Windows/SQL Server OS/DB Migration FAQ
- Downloading .sca files (SAP_JTECHS.sca, SAP_BUILDT.sca, SAP-JEE.sca)
- SAP idle session time out / Logging off inactive users
- Downloading pre-configured Smart Forms
- How to get List of transactions executed by a User in a specific period of time
- Do we need to really log-out and log-in again to make authorization changes effective?
- Differences between the transactions SE09 and SE10
Leonardo on Blink 182′s song lyric o… Juan Gerardo Palacio… on Blink 182′s song lyric o… Google on Checking SAP R/3 tablespa… Ray Ban Rb 4174 Pola… on Checking SAP R/3 tablespa… Attorney on Activate and Configure SAP ITS…
- Activate and Configure SAP ITS Webgui on SAP ECC 6.0
- How to estimate the PGA, SGA size, configuration, database server memory
- Installation and Configuration of SAPGUI for Java on Linux
- Howto mirror an AIX rootvg
- Dowload Here !!
- How to find indexes which are candidate for rebuild?
- SAP Router Installation
- Update Database Statistics using BRTOOLS and BRCONNECT
- New SAPGUI 7.30 for Windows has been released !!
- Client Copy from PRD server to QA server through Export - Import
- 484,068 hits
- Heaven: youtu.be/3TVyNm05Law via @YouTube 2 days ago
- "Just Give Me A Reason" - Pink ft Nate Ruess (Sam Tsui & Kylee Cover): youtu.be/eCi3lo1JaQI via @YouTube 1 week ago
- How to mirror the rootvg in AIX wp.me/pRUR-kP 1 month ago
- Howto mirror an AIX rootvg wp.me/p6mSO-5n 1 month ago
- RT @bhinnekacom: Halo B'Nerz.. Mimin mau bagi-bagi Smartphone Asus Zenfone dari Intel.. Yang mau RT dong.. ;) 2 months ago
- June 2014
- November 2013
- October 2013
- May 2013
- September 2012
- August 2012
- July 2012
- June 2012
- February 2012
- January 2012
- November 2011
- October 2011
- June 2011
- May 2011
- February 2011
- January 2011
- November 2010
- June 2010
- May 2010
- April 2010
- December 2009
- May 2009
- April 2009
- March 2009
- February 2009
- November 2008
- October 2008
- September 2008
- July 2008
- June 2008
- December 2007
- November 2007
- September 2007
- August 2007
Access Method AIX Application Server Basis BRBACKUP BRCONNECT BRRESTORE BRSPACE BRTOOLS Client Copy Client Export Client Import Connection String control file Database datafile DB02 DB2 DB13 DB14 distro download ERP F G http://ardhian.kioslinux.com IBM ITS Linux LPD MAXDATAFILES Message Server online redo log Oracle Oracle 9i Oracle 11g ORASID PGA PlatinGUI port Print redo log redo log groups remote client copy S SAP SAP Basis SAPClients SAP ERP SAPGUI SAPGUI Distribution Service SAPGUI for Java SAPGUI for Windows sap logon SAP Netweaver SAP Printing System SAP R3 SAProuter SCC8 scc9 solman key SPAM STMS SUN Java System Number Table reorganization Tablespace TCODE TRANSACTION CODE U Unix v$log Virtualization Windows WPar