Shulou(Shulou.com)06/01 Report--

The day before yesterday, I need to change the memory parameters of Oracle. The size of sga and pga was already configured when oracle was installed, but there are a lot of problems with it. Linux uses 16 gigabytes of memory, and sga uses 12 gigabytes of memory, so the linux system is slow. Change the number of connections opened: SQL > show parameter open_cursors;-- display the parameter value SQL > alter system set open_cursors = 1000;-modify the parameter value to 1000SQL > commit;-submit the modification successfully. Oracle adjusts SGA and PGA size blog categories: OracleSQL > show parameter sga; SQL > alter system set sga_max_size=4096M scope=spfile;System altered.SQL > alter system set sga_target=4096M scope=spfile;System altered. If it is a RAC environment, you need to add a sid='*': alter system set sga_target=20G scope=spfile sid='*'; restart to make the changes take effect: SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 4294967296 bytesFixed Size 2026296 bytesVariable Size 805307592 bytesDatabase Buffers 3472883712 bytesRedo Buffers 14749696 bytesDatabase mounted.Database opened.SQL > show parameter sga NAME TYPE VALUE-lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size Big integer 4Gsga_target big integer 4G SQL > show parameter pga No need to restart DB, just modify it online. SQL > alter system set workarea_size_policy=auto scope=both; System altered. SQL > alter system set pga_aggregate_target=512m scope=both; System altered. SQL > show parameter workarea NAME TYPE VALUE-workarea_size_policy string AUTO-this is set to AUTO SQL > show parameter pga NAME TYPE VALUE-pga_aggregate_target Big integer 536870912 SELECT * FROM V$PGASTAT Aggregate PGA auto target 477379584 bytes-the PGA size currently available for auto allocation, which should be 1118 smaller than PGA_AGGREGATE_TARGET-the number of allocations that occur after the instance is started, if this value is greater than 0 It is necessary to consider increasing the value of pga 1 to view SGA size show parameter sga_max_size db_cache_size size affected by SGA, so that it can be larger than SGA 2 to view show parameter shared_pool_size size. Generally speaking, shared_pool_size+db_cache_size=SGA_MAX_SIZE*70% is calculated to modify db_cache_size sql > alter system set db_cache_size= size M scope=spfile sid=' database SID'. Sql > shutdown immediate sql > startup sql > show parameter db_cache_size can see the modified effect [oracle@oracle_db] $sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0-Production on Wed Aug 5 21:56:47 2015Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL > SQL > show parameter sga NAME TYPE VALUE-- lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 12Gsga_target big integer 12GSQL > alter system set sga_max_size=10240M scope=spfile System altered.SQL > alter system set sga_target=10240M scope=spfile;System altered.SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startupORACLE instance started.Total System Global Area 1.0737E+10 bytesFixed Size 2101808 bytesVariable Size 1560284624 bytesDatabase Buffers 9160359936 bytesRedo Buffers 14671872 bytesDatabase mounted.Database opened.SQL > show parameter sga NAME TYPE VALUE-- lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 10Gsga_target big integer 10GSQL > show parameter pga SQL > alter system set workarea_size_policy=auto scope=both; System altered.SQL > alter system set pga_aggregate_target=2048m scope=both System altered.SQL > show parameter workarea NAME TYPE VALUE-- workarea_size_policy String AUTOSQL > show parameter workarea NAME TYPE VALUE---workarea_size_policy string AUTOSQL > show parameter pga SQL > show parameter sga NAME TYPE VALUE-- lock_sga boolean FALSEpre_page_sga boolean FALSEsga_max_size big integer 10Gsga_target big integer 10GSQL > SQL > the final result is changed to sga 10G + pga 2G 4G of system memory 32 gigabytes of swap partition memory. The effect of the change is not very obvious and needs follow-up observation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.