ORACLE-System parameter tuning

First, memory adjustment

Oracle 11g, Oracle unified the SGA with the PGA, the sum of the Memory_target parameter settings, that is, Max (SGA+PGA) <= memory_target (of course, you can create a new instance of the time to determine whether to use this scheme, is not in 10g).

Alter system set sga_max_size=1500m Scope=spfile;

If the error is set to Sga_max_size>=memory_target (not equal, the PGA requires at least approximately more than 10 m of memory), the startup Nomount cannot be performed, but modifying the SGA command requires at least the startup Nomount is executed below, so you must modify the parameter in a different way, and an exception occurs when you use startup Nomount:

ora-00844: Parameter not taking memory_target into Accountora-008511073741824  More 872415232.

This time, you need to modify it in the following ways:

sql> Create pfile='/home/oracle/init0321.ora' from spfile; File created. SQL>! VI /home/oracle/init0321.ora--then modify the following parameters, such as:

*.memory_target=1073741824

*.sga_max_size=805306368

*.sga_target=805306368

sql> startup Nomount pfile= '/home/oracle/init0321.ora '; -- then use Pfile to start

Sql> create SPFile from pfile= '/home/oracle/init0321.ora '; --Create SPFile

--reboot below (by default, SPFile is used)

sql> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Sql> startup;

Note:

Pfile and SPFile default location:

SPFile ${oracle_install_path}/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

If the parameter does not specify a pfile location, the following file is used by default

Pfile ${oracle_install_path}/product/11.2.0/dbhome_1/dbs/initorcl.ora

Second, sharing pool allocation and adjustment

1. Query the cache hit ratio, and if the miss rate is close to 1%, the system needs to adjust the boost performance.

sql>Selectsum" number of requests ",sum" number of misses " from V$librarycache;

2. Data dictionary cache, no hit ratio should be less than 10%

sql>Selectsum" request access number ",sum" number of misses " from V$rowcache

If performance bottlenecks occur, consider raising the share_pool_size.

3. Data buffers

Select inch ('db block gets','consistent gets','physical Reads')

Hit Ratio = n(physical reads/(db block gets+ consistent))

If the result is small ( for example, less than 80%), you need to adjust the initialization parameter db_block_buffers (max. 65535)

Third, sorting zone adjustment and sharing connection

Sort_area_size sets the size of the memory sort, the size of each sort is determined by sort_area_retained_size, and the disk staging segment is used to sort (the temp table space), and the disk ordering takes a long time.

Methods: Long transactions and large transactions use a private connection, for short transactions and small transactions using shared connection , in 11g, the system can be automatically managed PGA,SGA allocation ratio, set the available memory.

Cause: In a shared connection, if the number of user requests is greater than the number of shared connections, the long transaction waits for the growth time of the user, performance degrades, and the long transaction system is slower in OLTP, the online transaction for general applications.

ORACLE-System parameter tuning