Configuration and superiority of ORACLE service

Configure and prioritize Oracle Oracle Services
1. It is important to realize distributed fast access and full storage in Oracle.

To avoid page adjustments and exchange at any cost, you must put the system global Area (SGA) on the store each time.

Put the SGA in the store and set the parameters in the Init.ora PRE_PAGE_SGA

Pre_page_sga=yes

2. The competition in the rollback section lowers the system's performance.

SELECT gets,waits from V$rollstat;

Gets represents the number of times an interview is returned to a volume section

Waits represents the number of the progression in the process waiting to be returned to the volume segment

If the ratio of gets to waits is greater than 2%, it means there is a problem.

The solution is to create more back-volume segments.

There are two types of lock-and-save wars that produce serious consequences: immediately latch (IMMEDIATE) and wait (waiting) to latch.

Calculation formula waiting to be locked:

Wait contention= (misses/(gets+misses)) *100

Calculation formula for immediately locked:

IMMEDIATE connection= (immediate_misses/(immediate_gets+immediate_misses)) *100

If any of the values >0, there is an existential fight

If the event is restored, the log_small_entry_max_size in the small init.ora can be reduced. If a recovery is made, the parameters of the log_simulatanous_copies in the Init.ora can be increased.

The optimal parameters of any rollback segment cannot be set too small, if the optimal<minextents will degrade the operating performance of the system.

SELECT Class,count from V$waitstat WHERE UPPER (The ' 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 of each of the first checks to the second inquiry is best <0.01

3. Improve the efficiency of the zone.

SELECT reloads,pins from V$librarycache;

Reloads the number of times the SQL command is loaded and decomposed

Pins the number of times to look up a phrase in a data library for a SQL high speed buffer.

If the ratio of reloads to pins is greater than 1%, it should increase the number of shared_pool_size in Init.ora.

SELECT getmisses,gets from V$rowcache;

If the ratio of reloads to pins is greater than 10%, it should increase the number of Init.ora

The reference value of the shared_pool_size.

4. The maximum number of open targets at any time of the user

Modifies the parameters of the open_cursors in the Init.ora.

And the fast-slow being is the way in which the cursor is kept in conversation.

Increase the session_cached_cursors=100 in the Init.ora

5. SELECT sum (GETS), sum (getmisses) from V$rowcache;

The getmisses ratio must be close to 0, in order to enhance the performance, you can increase the Init.ora parameters shared_poll_size and db_block_buffers values.

6. SELECT Name,value from V$sysstat WHERE UPPER (NAME) in

(' Sorts (MEMORY) ', ' Sorts (DISK) ');

If the order on the magnetic disc is higher, the value of the sort_area_size in the Init.ora can be increased.

7. LGWR log files If the switch is too fast to affect the activity of the data library, the performance of the next.

In the Init.ora checkpoint_process=true

log_checkpoint_interval=10000

log_checkpoint_timeout=1800

8. Multi-thread Service (MTS) configuration

1> Mts_service

This is the service name used by the adjustment program to sql*net the listener.

It's best to take the same name as SID.

2> mts_dispatchers

Configure the number of adjustment processes.

3> mts_max_dispatchers

Configure the maximum number of adjustments for the process.

4> mts_sercers

Activate the first number of shared services.

5> mts_max_servers

Set the maximum number of shared services.

6> mts_listenter_address

This is the address of the service name used by the adjustment program to sql*net the listener.

An example of an MTS configuration:

In the 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

The Listener.ora should include the following types of content:

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_level_listener=admin

Sid_list_listener=

(sid_list=

(sid_desc=

(Sid_name=mydb)

)

)

After adding the MTS parameters, restart the data library.

Check with the PS command under Sql*plus for the existence of a shared service and an adjustment program.

Øps-ef| GREP Foxmold;

The process with Ora_snnn_sid and ora_dnnn_sid names is the sharing of the service and the tuning process.

SELECT username,program,server from V$session WHERE audsid=userenv (' SESSIONID ');

The value that is returned in the server column should be shared, and if it is dedicated, it is said that MTS is not correctly configured.

Use the LSNRCTL Services command to check whether the adjustment program has logged a service to the listener.

7. Move the data library file

First method: (ALTER DATABASE)

Close the data library-move the data file-load and rename-activate

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 the data library-move the data file-load and rename-activate

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, clean up the tablespace fragments.

SELECT Tablespace_name, percent_blocks_coalesced

From dba_free_space_coalesced

To see if the value of the tablespace is not 100. The following command:

ALTER tablespace tablespace_name Coalesce

Note: This command is not necessary 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> this command to generate Mv_index. SQL file

4> @c:\mv_index. Sql

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

Maintaining REDO LOG FILE

1> SHUTDOWN

2> EXIT

3> cp/disk1/*. rdo/disk4/*. RDO

4> CHMOD 660/disk4/*. RDO

5> STARTUP MOUNT Foxmold

RECOVER SYNTAX

A. RECOVER a CLOSED DATABASE

1> 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 that:alter database must be performed after recover.