1. It is important to implement distributed and fast access and enrich the memory in Oracle.
To avoid page scheduling and switching at any cost, the system global zone (SGA) must be put into the memory every time.
Put the SGA in the memory and set the pre_page_sga parameter in init. ora.
Pre_page_sga = Yes
2. Competition for the rollback segment will reduce the system performance.
Select gets, Waits from V $ rollstat;
Gets indicates the number of times the rollback segment is accessed.
Waits indicates the number of times that the process is waiting for the rollback segment.
If the ratio of gets to Waits is greater than 2%, there is a competition problem.
The solution is to create more rollback segments.
There are two types of lock contention that will have serious consequences: instant lock (immediate) and wait (wait) Lock.
Calculation Formula for waiting for lock ﹕
Wait contention = (misses/(gets + misses) * 100
Calculation Formula for lock now ﹕
Immediate connection = (immediate_misses/(immediate_gets + immediate_misses) * 100
If any value is greater than 0, there will be competition for the lock.
If the competition for resuming the allocation of lock storage occurs, you can reduce the parameter value of log_small_entry_max_size in init. ora. If the replication recovery competition occurs, you can increase the value of log_simulatanous_copies in init. ora.
China Network Management Alliance www_bitscn_com
The optimal parameter setting of any rollback segment cannot be too small, if optimal
Select class, count from V $ waitstat where upper (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 between each category in the first query and the second query is the best <0.01
3. Improve the buffer efficiency.
Select reloads, pins from V $ librarycache;
Number of times reloads stores SQL commands loaded and decomposed
Number of times pins saved to the database to find a statement in the SQL High-Speed Buffer
If the ratio of reloads to pins is greater than 1%, the value of shared_pool_size in init. ora should be increased.
Select getmisses, gets from V $ rowcache;
If the ratio of reloads to pins is greater than 10%, add
The value of shared_pool_size.
4. the maximum number of open cursors that a user has at any point in time
Modify the value of open_cursors in init. ora. China Network Management Alliance www_bitscn_com
The cache is related to the way the cursor is cached in the session.
Add session_cached_cursors = 100 to init. ora.
5. Select sum (gets), sum (getmisses) from V $ rowcache;
The getmisses ratio must be close to 0. to optimize the performance, you can increase the values of the shared_poll_size and db_block_buffers parameters in init. ora.
6. Select name, value from V $ sysstat where upper (name) in
('Sorts (memory) ', 'sorts (Disk )');
If the sorting volume on the disk is large, you can increase the sort_area_size value in init. ora.
7. If the log file of lgwr is switched too fast, the activity of the database will be affected, resulting in performance.
Checkpoint_process = true in init. ora
Log_checkpoint_interval = 10000
Log_checkpoint_timeout = 1800
8. Multithreading
Server (MTS) Configuration
1> mts_service
This is scheduling
Program The service name used to register the service with the SQL * Net Listener.
It is best to use the same name as Sid.
2> mts_dispatchers
Configure the number of scheduling processes.
Network Management Network bitscn_com
3> mts_max_dispatchers
Configure the maximum number of scheduling processes.
4> mts_sercers
The initial number of started shared servers.
5> mts_max_servers
Set the maximum number of shared servers.
6> mts_listenter_address
This is the address that the scheduler uses to register the service name with the SQL * Net Listener.
An example of MTS Configuration:
In init. ora
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
Similar content should be included in listener. ora:
Listener =
(Address_list =
(Address =
(Protocol = IPX)
(Key = mydb)
)
(Address =
(Protocol = TCP)
(Host = ourhost)
(Port = 1521
)
Startup_wait_time_listener = 0
54ne.com
Connect_timeout_listener = 10
Trace_level_listener = Admin
Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = mydb)
)
)
After adding MTS parameters, restart the database.
Run the ps command in SQL * Plus to check whether the Shared Server and scheduler exist.
? PS-Ef | grep foxmold;
Processes with the ora_snnn_sid and ora_dnnn_sid names are the sharing server and scheduling process.
Select username, program, server from V $ session where audsid = userenv ('sessionid ');
The value returned in the server column should be shared. If it is dedicated, it indicates that MTS is not correctly configured.
Run the LSNRCTL Services Command to check whether the scheduler registers the service with the listener.
