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

1 、

# lsdev-Cc memory

To view the configured physical memory device, the following is an example of its output:

L2cache0 Available L2 Cache

Mem0 Available Memory

# lsattr-El mem0

The output looks like this:

Goodsize 7936 Amount of usable physical memory in Mbytes False

Size 7936 Total amount of physical memory in Mbytes False

This example shows that the physical memory of the machine is 5888MB. If the output of the previous lsdev has the device name mem1, use the same command to see its corresponding size and so on.

2 、

# bootinfo-r

8126464

This example shows that the physical memory of the machine is 8126464kB.

3 、

# prtconf

The output looks similar to the following:

-

-

Memory Size: 7936 MB

-

-

This command will type out the detailed configuration information of the current host, and the Memory Size attribute in the output indicates the current physical memory size of the host.

4 、

# svmon-G

The output looks similar to the following:

Size inuse free pin virtual

Memory 2031616 1474504 557112 210251 440972

Pg space 131072 1555

Work pers clnt

Pin 210251 0 0

In use 440972 0 1033532

PageSize PoolSize inuse pgsp pin virtual

S 4 KB-1426984 1555 187531 393452

M 64 KB-2970 0 1420 2970

Where: size represents the real size of physical memory in 4k. So the current physical memory size is 4k*2031616=8126464kB

The above command is tested in the Aix5.3 version through [@ more@]

Transferred from: http://javafun.javaeye.com/blog/139228

-

SQL > select 1-(physical.value-direct.value-lobs.value) / logical.value) 2 "Buffer Cache Hit Ratio"

3 from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical

4 where physical.name = 'physical reads'

5 and direct.name='physical reads direct'

6 and lobs.name='physical reads direct (lob)'

7 and logical.name='session logical reads'

Buffer Cache Hit Ratio

--

. 971388642

Where session logical reads is the total number of reads. Physical reads is read from data files. Physical reads direct is read from buffer (excluding LOBS). Physical reads direct (LOBS) is read from buffer (including LOBS)

Turn to:

one。 We can improve performance by configuring Shared Pool (to ensure that users find cached statements in memory); another important way is to enable users to find the data they request in memory! This needs to be done through Database Buffer Cache (the cache area of the database buffer).

Buffer Cache is a component of SGA that caches copies of segment blocks that users have recently accessed in the database. These copies are synchronized with their corresponding blocks on disk! If it is not synchronized, it is called a dirty buffer! Write from Cache to disk, which we call write disk. The mechanism for managing dirty buffers is called Dirty List (dirty list) or Write List (write list). This list tracks statements that have been insert,update,delete but have not yet been written. The final disk writing work is completed by the ORACLE background process Database Writer (DBW0).

Buffer Cache is also managed by a LRU algorithm. * FTS (during full table scan) the table buffer is placed directly on the least used end of the LRU.

two。 Measure the performance of Database Buffer Cache

Select 1-(physical.value-direct.value-lobs.value) / logical.value)

"Buffer Cache Hit Ratio"

From v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical

Where physical.name = 'physical reads'

And direct.name='physical reads direct'

And lobs.name='physical reads direct (lob)'

And logical.name='session logical reads'; the above statement should be more than 90% true to indicate that the adjustment is sufficient. The hit rate is high!

three。 Improve the performance of Database Buffer Cache

1. The easiest way is to increase its size. 45% of SGA is more appropriate! Several parameters

(1). The DB_BLOCK_SIZE master database block size defaults to 8K and cannot be changed after the database is established.

(2) .DB_CACHE_SIZE (default buffer pool) defaults to 48m we mainly modify this parameter to improve performance

Alter system set db_cache_advice=on; uses a statistical consultant recommended by oracle, half an hour after normal operation of the database

Recommended size for select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from v$db_cache_advice where block_size='8192' and advice_status='ON';

Select obj.owner,obj.object_name,obj.object_type,count (distinct bh.BLOCK#) "NUM. Buffers"

From dba_objects obj,v$bh bh

Where obj.object_id=bh.OBJD

And owner! = 'SYS'

Group by obj.owner,obj.object_name,obj.object_type; the above statement can see which objects are being cached in Buffer Cache and how many Buffer Cache buffers are being used.

(3)。 Use multiple buffer pools

Alter system set db_cache_size=300M

Alter system set db_keep_cache_size=150M; holding area

Alter system set db_recycle_cache_size=50M; Recycle area assigns tables to hold area

Alter table col_cust storage (buffer_pool keep); use statement

Select owner,segment_type,segment_name,buffer_pool

From dba_segments

Where buffer_pool! = 'DEFAULT'; can view the distribution

Select name,block_size,current_size

From vault bufferpole; you can check the size of each Buffer Pool

Select name "Buffer Pool", 1-(physical_reads / (db_block_gets + consistent_gets)) "Buffer Pool Hit Ratio"

From v$buffer_pool_statistics order by name; can check the hit rate of each Buffer Pool. The bigger the KEEP, the better, and the smaller the Recycle, the better.

(4)。 Cache tables in memory although tables can be placed in the hold area, because Keep Pool is also controlled by LRU, when FTS (full table scan), the table is still placed on the least used side of LRU. So it's possible to be removed. So we can use

Alter table col_cust cache; 's method of turning a table into a cache table

Select owner,table_name from dba_tables where ltrim (cache) ='Y' can view the information of the cache area table

Transferred from: http://blog.csdn.net/luoyanqing119/archive/2008/10/30/3183099.aspx

-

Oracle hit rate query, SGA tuning

-

-- data cache hit rate

-- calculation formula: 1-(physical reads / (db block gets + consistent gets))

-- the hit rate should be greater than 0.90.

Select name,value

From v$sysstat

Where name in ('physical reads','db block gets','consistent gets')

/

-- shared area database cache hit rate

-- calculation formula: SUM (pins-reloads) / SUM (pins)

-- the hit rate should be greater than 0.99

Select sum (pins-reloads) / sum (pins)

From v$librarycache

/

-- shared area dictionary cache hit rate

-- calculation formula: SUM (gets-getmisses-usage-fixed) / SUM (gets)

-- the hit rate should be greater than 0.85

Select sum (gets-getmisses-usage-fixed) / sum (gets)

From v$rowcache

/

-- detecting contention for rollback segments

-- SUM (waits) value should be less than 1% of SUM (gets) value

Select sum (gets), sum (waits), sum (waits) / sum (gets)

From v$rollstat

/

-- detect the number of contraction of the rollback section

Select name,shrinks

From v$rollstat, v$rollname

Where v$rollstat.usn = v$rollname.usn

/

-- on SGA tuning

(*): OS uses memory + SGA + session* (sort_area_size + hash_area_size + 2m) < total physical RAM is better

Log_buffer: 128K-1m is usually not a big problem and should not be too big

Large_pool_size: if MTS is not set, it is usually used in RMAN and OPQ, but it should be about 10m-50m.

Java_pool_size: if you don't use java, 30m is usually enough

Data buffer, after making the previous settings, any memory that can be provided to oracle should be given data buffer = (db_block_size * db_block_buffers)

Shared_pool_size cannot be set too large, usually it should be controlled at 200M--300M

To be more specific, you can refer to the following settings on the basis of meeting the principles above (*)

If 512m RAM

Recommended shared_pool_size = 50m, data buffer = 200m

If 1G RAM

Shared_pool_size = 100m, data buffer = 500m

If 2G

Shared_pool_size = 150m, data buffer = 1.2g

No matter how much physical memory is, it has nothing to do with the parameters.

Assume 64 bit ORACLE

4G memory

Shared_pool_size = 200m, data buffer = 2.5G

8GB of memory

Shared_pool_size = 300m, data buffer = 5G

12GB of memory

Shared_pool_size = 300M-800M, data buffer = 8G

Transferred from: http://oracle.chinaitlab.com/optimize/38281.html

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.