Oracle server configuration and optimization
1. It is important to achieve distributed fast access and enrich memory in Oracle.
Do you have to avoid page scheduling and exchange each time you have to put the system's global zone (SGA).
Place the SGA in memory, set parameters pre_page_sga in Init.ora
PRE_PAGE_SGA = YES
2. The actuality of the back section will reduce the performance of the system.
SELECT GETS, WAITS from V $ ROLLSTAT;
Gets represents the number of times the rewritable segment is accessed
Waits represents the number of times the process waits for the reciprocity
If Gets and Waits are more than 2%, there is a problem
The solution is to create more back segments.
There are two types of latches to create serious consequences: immediately latch (immediate) and wait (wait) latch.
Waiting for the latching calculation formula:
Wait contention = (Misses / (Gets Misses)) * 100
Stand-latched calculation formula:
IMMEDIATE CONNECTION = (IMMEDIATE_MISSES / (IMMEDIAT_GETS IMMEDIATE_MISSES) * 100
If any of the value> 0, there is a competition for the latitude
If a resumed assignment latch occurs, you can reduce the parameter value of the log_small_entry_max_size in init.ora. If recovery replication occurs, you can increase the parameter value of log_simulatanous_copies in init.ora.
The setting of the Optimal parameter of any return segment cannot be too small if Optimal SELECT CLASS, Count From V $ Waitstat WHERE UPER (Class) in ('System Undo Header),' System Undo Block ', 'Undo header', 'undo block'); SELECT SUM (Value) from V $ sysstat where upper (name) In ('db block gets'); "consistent gets'); The ratio in each class and the second query in the first query is the best <0.01 3. Improve the efficiency of the buffer. SELECT RELOADS, PINS from V $ librarycache; Reloads deposits the number of SQL commands being loaded and decomposed Pins deposit into the database for the number of statements to the SQL high-speed buffer If the ratio of RELOADS and PINs is greater than 1%, the parameter value of Shared_Pool_Size in init.ra should be added. Select getMisses, Gets from v $ rowcache; If the ratio of RELOADS and PINs is greater than 10%, it should be added to Init.ra. Shared_pool_size parameter value. 4. The maximum number of open cursors have a cursor at any time? Modify the parameter value of the Open_Cursors in Init.ora. Related to the cache is a cache method in the session. Add session_cached_cursors = 100 in init.ra 5. SELECT SUM (Gets), SUM (GetMisses) from V $ Rowcache; The GetMisses ratio must be close to 0. To optimize performance, you can increase the value of parameter shared_poll_size and db_block_buffers in init.ra. 6. SELECT NAME, VALUE FROM V $ SYSSTAT WHERE UPER (NAME) IN ('Sorts (Memory'); 'sorts (disk); If the degree of ordering is used in the disk, you can increase the value of the sort_area_size in init.ora. 7. LGWR log files If the switch will affect the activity of the database, it will cause performance. Checkpoint_process = truelog_checkpoint_interval = 10000 in influence LOG_CHECKPOINT_TIMEOUT = 1800 8. Configuration of Multithreaded Server (MTS) 1> MTS_SERVICE This is the service name of the scheduler for the service name to the SQL * NET listener registration. It is best to take the same name as the SID. 2> MTS_Dispatcher Configure the number of scheduled processes. 3> MTS_MAX_DISPATCHERS Configure the maximum number of dispatching processes. 4> MTS_SERCERS Start the initial number of the shared server. 5> MTS_MAX_SERVERS Set the maximum number of shared servers. 6> MTS_Listenter_Address This is the address of the service name of the scheduler to register the service to the SQL * NET listener. An example of a MTS: In InIt.ra MTS_Listener_Address = '(address = (protocol = tcp) (key = mydb))' MTS_DISPATCHERS = 'TCP, 10' MTS_DISPATCHERS = 'IPX, 2' MTS_MAX_DISPATCHERS = 10 MTS_MAX_SERVERS = 10 MTS_SERVERS = 2 Need to include the following contents in Listener.ora: Listener = (Address_list = (Address = (Protocol = ipx) (Key = MYDB) ) (Address = (Protocol = TCP) (Host = Ourhost) (Port = 1521 ) STARTUP_WAIT_TIME_LISTENER = 0 Connect_timeout_listener = 10 TRACE_LIVEL_LISTENER = Admin SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = MYDB) ) ) Restart the database after adding the MTS parameter. Check if the shared server and the scheduler exist with a PS command under SQL * Plus. Ø ps -ef | grep foxmold; Processes with Ora_Snn_SID and Ora_Dnnn_sid names are shared servers and scheduling processes, respectively. Select Username, Program, Server from v $ session where audsid = uchesserenv ('sessionid'); The value returned in the Server column should be Shared, if it is Dedicated, the MTS is not configured correctly. Use the lsnrctl service command to check whether the scheduler registers the service to the listener. 7. Mobile database file The first method: (Alter Database) Close Database - Move Data File - Load and Rename - Start 1> SVRMGRL 2> Connect INTERNAL 3> Shutdown 4> EXIT 5> mv /u/product/oradata/foxmold/User01.dbf / db3 / oradata 6> SVRMGRL 7> Connect INTERNAL 8> Startup Mount Foxmold 9> ALTER DATABASE RENAME FILE '/U/product/oradata/foxmold/User01.dbf' to '/Db3/oradata/User01.dbf'10> ALTER DATABASE OPEN Second method: (Alter TableSpace) Close Database - Move Data File - Load and Rename - Start 1> SVRMGRL 2> Connect INTERNAL 3> Shutdown 4> EXIT 5> mv /u/product/oradata/foxmold/User01.dbf / db3 / oradata 6> SVRMGRL 7> Connect INTERNAL 8> ALTER TABLESPACE DATA RENAME DATAFILE '/U/product/oradata/foxmold/User01.dbf' to '/Db3/oradata/User01.dbf' 9> ALTER DATABASE OPEN 8. Statistical analysis Spool c: /analyze.sql SELECT 'Analyze Table' || Trim (Object_name) || 'Compute Statistics;' From user_Objects Where object_type = 'table' Then run the Analyze.sql file Finally, the TableSpace debris is cleaned. SELECT TABLESPACE_NAME, Percent_Blocks_coalesced From dba_free_space_coalesced Going to see that the value corresponding to the tableSpace is not 100, run as follows: Alter TableSpace TableSpace_name coalesce Note: There is no need to make this command if the value of TableSpace is 100. 9. Move Table Index to another TABLESPACE 1> Spool c: /mv_index.sql 2> SELECT 'ALTER INDEX' || Trim (Object_name) || 'Rebuild TableSpace FoxMold1;' from user_objects where object_type = 'index' 3> Generate this command to MV_INDEX.SQL file 4> @c: /mv_index.sql 10. Maintaining the controlfile 1> SVRMGRL 2> Connect INTERNAL 3> Shutdown 4> EXIT 5> cp /u/product/oradata/foxmold/control01.ctl /Db4/oradata/control01.ctl 6> Chmod 660 /db4/oradata/control01.ctl 7> Modify Init.ora Control_Files = ... 8> Startup Mount Foxmold 11. Maintain Redo Log File 1> Shutdown 2> EXIT 3> cp /disk1/*.rdo /disk4/*.rdo 4> CHMOD 660 / Disk4/*.rdo 5> Startup Mount Foxmold 12. Recover Syntax A. Recover A Closed Database1> Recover Database 2> Recover datafile '/disk1/df2.dbf' 3> ALTER DATABASE RECOVER DATABASE B. Recover An Open Database 1> Recover TableSpace User_Data 2> Recover DataFile 2 3> ALTER DATABASE Recover DataFile 2 note: Alter Database Be bound to Recover Post.
