Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the adjustment and optimization of oracle memory parameters". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Query the size of historical memory:

1. Query the maximum historical value of each memory unit according to the snapshot in awr:

Set linesize 120 pagesize 300

Col snap_id for 99999999

Col BEGIN_INTERVAL_TIME for a20

Col PARAMETER_NAME for a30

Col value for 99999999999

Select p.PARAMETERNAME Magazine / 1024 / 1024 | | 'Mauremenagerica totalchar (BEGIN_INTERVAL_TIME,'yyyy-mm-dd hh34:mi:ss') BEGIN_INTERVAL_TIME from DBA_HIST_PARAMETER p DBA_HIST_SNAPSHOT 's where p.snap_id = s.snap_id and p.PARAMETER_NAME ='_ shared_pool_size 'and p.dbid= (select dbid from v$database) and value = (select max (value) from DBA_HIST_PARAMETER where PARAMETER_NAME =' _ _ shared_pool_size') and rownum1G

Large_pool memory usage to prevent the setting from being too small and affecting the normal operation of the business

Db_cache_size

> = sga_max_size 50%

Db_cache memory usage to prevent IO performance problems caused by too small settings and insufficient data cache space

Shared_pool_size

> = sga_max_size 10%

Shared_pool_size memory usage to prevent the setting from being too small, affecting sql parsing, resulting in shared_pool contention and database exceptions

SGA actual size = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE (new memory pool in 10g) + LOG_BUFFERS+11K (protection page for Redo Log Buffer) + 1MB + 16m (SGA internal memory consumption, suitable for 9i and previous versions)

3. Use v$sga_target_advice to recommend sga size

There is content in the view only when the advisor is open (the implied parameter _ smm_advice_enabled is TRUE) and the parameter STATISTICS_LEVEL value is not BASIC.

Query whether the implicit parameter _ smm_advice_enabled is TRUE:

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ

FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x. Instantly = USERENV ('Instance')

AND y.inst_id=USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE'% _ smm_advice_enabled%'

SQL > alter system set "_ smm_advice_enabled" = TRUE

SQL > alter system set statistics_level=typical

Sga advice example:

SGA_SIZE: sga siz

SGA_SIZE_FACTOR: the ratio of the sga_size in this table to the size of the sga in the current parameter

ESTD_DB_TIME sets the ratio of the estimated DB_TIME ESTD_DB_TIME_FACTOR ESTD_DB_TIME after the estimated sga_size to the DB_TIME under the current SGA size. Number of physical reads estimated by ESTD_PHYSICAL_READS

For example:

SQL > select sga_size,sga_size_factor,estd_db_time,estd_db_time_factor,estd_physical_reads from V$SGA_TARGET_ADVICE

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

--

276 1 154 1 10828

138. 5 191 1.2403 12666

207. 75 154 1 10828

552 2 154 1 10828

414 1.5 154 1 10828

483 1.75 154 1 10828

345 1.25 154 1 10828

Seven rows have been selected.

It can be found that when the sga is set to 207m or greater, the number of physical reads has been maintained at 10828, and there is no performance improvement, so the maximum setting is about 207m.

4. Use v$shared_pool_advice to recommend shared pool size

There is content in the view only when the advisor is open (the implied parameter _ smm_advice_enabled is TRUE) and the parameter STATISTICS_LEVEL value is not BASIC.

The official document states:

How to collect useful information from V$ SHARED_POOL_ADVICE?

Perform the following sql:

SHARED_POOL_SIZE_FOR_ESTIMATE estimates shared pool size

SHARED_POOL_SIZE_FACTOR estimates the ratio of shared pool size to current size

ESTD_LC_SIZE estimates the size of the shared pool for library cache

ESTD_LC_MEMORY_OBJECTS estimates the number of library cache objects

ESTD_LC_TIME_SAVED estimates the time saved, which is the difference between the cost of loading the shared pool and the time it takes to read from library cache

SQL > SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB"

Shared_pool_size_factor "Size Factor"

Estd_lc_time_saved "Time Saved in sec"

FROM v$shared_pool_advice

Size of Shared Pool in MB Size Factor Time Saved in sec

24. 5 525

32. 6667 525

40. 8333 525

48 1 525

56 1.1667 525

64 1.3333 525

72 1.5 525

80 1.6667 525

88 1.8333 526

96 2 526

10 rows selected.

+ from the output above, you can see that the current shared pool is set to 48m (Size factor is 1).

+ as shown in the above example, the same effect can be achieved even if the size of the shared pool is reduced to 50%.

+ increase the shared pool almost twice to about 1 second in the parsing period.

-so you can resize the shared pool if you have enough memory, otherwise you can leave it as it is.

Be careful

+ latch contention and fragmentation caused by changing the size of the shared pool cannot be considered by the consultant.

Other:

In the 10G or 11G version of Oracle, how to set the shared pool size reasonably has a great impact on the performance of Oracle database.

The rules for setting Shared Pool size are as follows:

1. The reasonable value set by shared pool is found, and the statement is as follows:

Select 'Shared Pool' component

Shared_pool_size_for_estimate estd_sp_size

Estd_lc_time_saved_factor parse_time_factor

Case

When current_parse_time_elapsed_s + adjustment_s

< 0 THEN 0 ELSE current_parse_time_elapsed_s + adjustment_s END response_time FROM (select shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_time_saved_factor, a.estd_lc_time_saved, e.VALUE / 100current_parse_time_elapsed_s, c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s from v$shared_pool_advice a, (select * from v$sysstat where NAME = 'parse time elapsed') e, (select estd_lc_time_saved from v$shared_pool_advice where shared_pool_size_factor = 1) c); 2.设置上个SQL语句中查到的PARSE_TIME_FACTOR首次等于1的记录所在行的ESTD_SP_SIZE的值为shared pool。 3.设置语句如下： alter system set shared_pool_size=XXX scope=spfile;或者alter system set shared_pool_size=XXX scope=both; 5.使用v$db_cache_advice 建议db cache 大小 只有当建议器打开（隐含参数_smm_advice_enabled为TRUE），并且参数STATISTICS_LEVEL值不是BASIC时，视图中才会有内容。 列含义解释： SIZE_FOR_ESTIMATE： 预估的数据缓冲区的大小，单位M SIZE_FACTOR ： SIZE_FOR_ESTIMATE与当前数据缓冲区大小的比值，等于1时表明是当前值。 ESTD_PHYSICAL_READ_FACTOR： 当缓冲区设置为SIZE_FOR_ESTIMATE时，预估的物理读与当前的物理读的比值，如果当前的物理读为0，该值为空 ESTD_PHYSICAL_READS： 当缓冲区设置为SIZE_FOR_ESTIMATE时，预估的物理读。 下面查询default池的建议信息： SQL>

Select size_for_estimate,size_factor,estd_physical_read_factor,estd_physical_reads

2 from V$db_Cache_Advice

3 where name = 'DEFAULT'

SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

--

16 0.1 1.9942 37301 10 of Current Size

32 0.2 1.5334 28682

48 0.3 1.3367 25003

64 0.4 1.1974 22397

80 0.5 1.1248 21040

96 0.6 1.0871 20334

112 0.7 1.0443 19533

128 0.8 1.0102 18895

144 0.9 1.0029 18759

160 1 1 18705 Current Size

176 1.1 1 18705

192 1.2 1 18705

208 1.3 1 18705

224 1.4 1 18705

240 1.5 1 18705

256 1.6 1 18705

272 1.7 1 18705

288 1.8 1 18705

304 1.9 1 18705

3202 1 18705 200% of Current Size

As can be seen from the above results, when the default pool size is 160m, the estimated physical read starts to be fixed at 18705, so set the

It's more appropriate.

An important indicator to measure the appropriateness of the data buffer setting is the buffer hit rate.

SQL > select 1-sum (decode (name,'physical reads',value,0)) /

(sum (decode (name,'db block gets',value,0)) +

Sum (decode (name,'consistent gets',value,0) "Buffer cache hit ratio"

From v$sysstat

Buffer cache hit ratio

--

0.981732159175874

When the value is less than 95%, DBA needs to pay attention to whether the data buffer is insufficient. The hit rate of the data buffer is affected by many factors.

Does not mean that the low buffer hit rate does not mean that the database does not have enough data buffers for OLAP databases (often need to read a lot of data)

It should be analyzed according to its business characteristics. Consider increasing the data buffer and setting multiple buffers (keep,recycle buffers). )

The method of setting the minimum value by 6.Shared pool and db cache

During a typical, busy period on the database, run the following query:

SET PAGESIZE 100

COL COMPONENT FORMAT A25

COL FINAL_SIZE FORMAT A15

Select component, AVG (FINAL_SIZE) "AVG FINAL", MEDIAN (FINAL_SIZE) "MEDIAN FINAL", MAX (FINAL_SIZE) "MAX FINAL"

From v$sga_resize_ops

Group by component

Example:

COMPONENT AVG FINAL MEDIAN FINAL MAX FINAL

--

DEFAULT buffer cache 167772160 167772160 167772160

Shared pool 104857600 104857600 104857600

For "DEFAULT buffer cache", determine the larger value in "AVG FINAL" or "MEDIAN FINAL" and take this value as the minimum Buffer Cache.

For "Shared Pool", determine the larger value in "AVG FINAL" or "MEDIAN FINAL" and take this value as the minimum Shared Pool.

Add the smallest Buffer Cache and the smallest Shared Pool and compare it to the current SGA_TAGET or SGA_MAX_SIZE.

If the sum is greater than SGA_TARGET or SGA_MAX_SIZE, you need to increase the size of SGA_TARGET and SGA_MAX_SIZE accordingly. After determining the size of SGA_TARGET and SGA_MAX_SIZE, you can implement them, such as:

SQL > alter system set sga_max_size=nnn scope=SPFILE

SQL > ALTER SYSTEM SET SGA_TARGET=nnn SCOPE=BOTH

Sets the value of the parameter DB_CACHE_SIZE to the minimum buffer cache

SQL > ALTER SYSTEM SET DB_CACHE_SIZE=n SCOPE=SPFILE

Set the value of the parameter SHARED_POOL_SIZE to the minimum shared pool

SQL > ALTER SYSTEM SET SHARED_POOL_SIZE=m SCOPE=SPFILE

Re-start the database.

3. PGA SIZE:

The PGA consists of two sets of areas:

Fixed PGA and variable PGA (or PGA heap, PGA Heap [heap-Heap is a managed memory area]). Fixed PGA is similar to fixed SGA in that it is fixed in size and contains a large number of atomic variables, small data structures, and pointers to variable PGA.

Variable PGA is a memory heap. Its memory segment can be found through the view X$KSMPP (the other view X$KSMSP can find the memory segment information of the variable SGA, which has the same structure). The PGA heap contains memory for holding the X$ table (dependencies and parameter settings, including DB_FILES, CONTROL_FILES).

Generally speaking, the variable region of PGA is mainly divided into the following three parts:

O Private SQL zone

O Vernier and SQL area

O session memory

1. Typical PGA settings:

In an OLTP (online transaction processing) system, the typical PGA memory setting should be a small portion of the total memory (for example, 20%), with the remaining 80% allocated to SGA.

OLTP:PGA_AGGREGATE_TARGET= (total_mem * 80%) * 20%

In a DSS (dataset) system, a typical PGA memory allocates up to 70% of memory because some large queries are run.

DSS:PGA_AGGREGATE_TARGET= (total_mem * 80%) * 50%

two。 How to set up PGA according to the actual situation?

You can run the following sql during a normal stress test

Select (select sum (pga_used_mem) / 1024lap 1024 from v$process) / (select count (*) from v$process) from dual

Get the approximate amount of memory consumed by a process, and then estimate the total number of connections in the system, such as 500connections, so sessions=1.1*process+5=500, then processes=450, multiplied by the memory consumed by a process, can roughly estimate how much PGA needs to be set up.

EG = 1.1x 450 = 495m estimated a little larger 550m

It is best to set the value of PGA larger than the calculated value. After setting the value of PGA, you can set the total memory to PGA/0.16 according to the nature of the system. If the system is OLTP, you can also estimate the size of SGA.

After PGA_AGGREGATE_TARGET is set, the size of each process's PGA memory is also limited:

O during serial operations, the available PGA memory for each process is MIN (PGA_AGGREGATE_TARGET * 5%, _ pga_max_size/2), and the default value of the implicit parameter _ pga_max_size is 200m. It is also not recommended to modify it.

O when operating in parallel, the PGA memory available for parallel statements is PGA_AGGREGATE_TARGET * 30% / DOP (Degree Of Parallelism parallelism).

3. Use V$PGA_TARGET_ADVICE to recommend pga size

This view shows the estimated predictions of the PGA optimization advisor, which shows the PGA performance statistics that V$PGASTAT may display at various PGA_AGGREGATE_ target values. Select the PGA_AGGREGATE_ target value used to predict the value that is about the current PGA_AGGREGATE_TARGET. The estimated statistical value is simulated according to the load after the instance is started.

Columns commonly used in v$pga_target_advice dynamic performance.

Pga_target_for_estimate: the value of the predicted pga_aggregate_target parameter.

Pga_target_factor: the ratio of the predicted pga value to the current pga value.

Estd_pga_cache_hit_percentage: the estimated cache hit rate when pga_aggregate_target is set to the value of a pga_target_for_estimate. The value of this column is equal to: bytes_processed / (bytes_processed + estd_extra_bytes_rw).

Estd_overalloc_count: if the pga_aggregate_target value is set to the pga_target_for_estimate value. The number of times that is estimated to exceed the allocation (over-allocations). If the value is not 0, pga_target_for_estimate is not large enough, so pga_target_for_estimate should not be set to the value of pga_aggregate_target, otherwise oracle will not trust the value. In excess of allocation, the amount of memory actually allocated to PGA exceeds the value set by pga_aggregate_target.

SQL > SELECT pga_target_for_estimate / 1024 / 1024 "PGA (MB)"

Pga_target_factor

Estd_pga_cache_hit_percentage

Estd_overalloc_count

FROM v$pga_target_advice

PGA (MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT

-

10. 5 34 13

15. 75 34 13

20 1 100 13

24 1.2 100 13

28 1.4 100 13

32 1.6 100 3

36 1.8 100 0

40 2 100 0

60 3 100 0

80 4 100 0

120 6 100 0

160 8 100 0

12 rows selected.

From the above data, the following conclusions can be drawn:

1. The first column represents the specific values of different PGA

two。 The second column PGA_TARGET_FACTOR is "1" to indicate the current pga_aggregate_target setting size (other values are multiples based on this data). Here is 20m, which can be confirmed by the pga_aggregate_target parameter.

SQL > show parameter pga_aggregate_target

NAME TYPE VALUE

-

Pga_aggregate_target big integer 20M

3. The third column represents the percentage of Cache hit rates estimated by PGA

At present, if the PGA is 20m, the system can achieve 100% hit rate.

4. If the fourth column is "0", the overload of PGA can be eliminated.

As can be seen from the above data, when the PGA is 36m, the overload of PGA can be eliminated.

5. Based on the above conclusion, we can finally set the size of the PGA to 36m.

SQL > alter system set pga_aggregate_target=36m

4. Considerations for Oracle memory adjustment:

1. Pay attention to daily operation

Whether the commonly adjusted parameters take effect immediately:

SQL > select name, issys_modifiable

2 from V$PARAMETER

3 where name in ('sga_max_size'

4 'sga_target'

5 'pga_aggregate_target'

6 'db_cache_size'

7 'shared_pool_size'

8 'large_pool_size'

9 'java_pool_size'

10 'stream_pool_size'

11 'log_buffer')

NAME ISSYS_MOD

--

Sga_max_size FALSE

Shared_pool_size IMMEDIATE

Large_pool_size IMMEDIATE

Java_pool_size IMMEDIATE

Sga_target IMMEDIATE

Db_cache_size IMMEDIATE

Log_buffer FALSE

Pga_aggregate_target IMMEDIATE

If issys_modifiable=immediate, it means that this parameter can be modified immediately in system and takes effect immediately.

Alter system set xxx=xxx scope=both

If issys_modifiable=deferred or false, it means that this parameter cannot be directly modified in memory. You need to add scope=spfile to it before it takes effect after restart.

Alter system set xxx=xxx scope=spfile

Alter system set sga_max_size=xxm scope=spfile;-reduces the size of SGA, static parameters, and takes effect after restart

Alter system set sga_target=xxm scope=both;-dynamic parameter; oracle recommendation: modify this parameter at startup, do not set this parameter dynamically.

Alter system set db_cache_size=xxM scope=spfile

Alter system set shared_pool_size=xxM scope=both

Alter system set large_pool_size=xxM scope=both

Alter system set pga_aggregate_target=xxM sope=both;-reduce the size of pga

Modify parameters dynamically through SPFILE:

(1) create a pfile

SQL > create pfile from spfile

(2) modify the content of pfile

The main content after modification is

Sga_target=1700000000 (about 1.7g)

Lock_sga=true

Pga_aggregate_tagert=250000000 (about 250m)

Workarea_size_policy=auto

Pre_page_sga=true

Sga_max_size=1720000000 (about 1.72g)

(3) start the database according to pfile

SQL > startup pfile='d:/oracle/product/10.1.0/db_1/database/INITorcl.ORA'

If it cannot be started, it may be due to some parameters, then modify the INIToracl.ORA configuration file until it starts normally.

(4) create spfile

SQL > create spfile from pfile

The appeal order will overwrite the spfile file "SPFILEORCL.ORA" under / oracle/product/10.1.0/db_1/database/

Of course, you can also explicitly specify pfile.

SQL > create spfile from'/ oracle/product/10.1.0/db_1/database/INITorcl.ORA'

(5) start the database with spfile and adjust the performance

SQL > shutdown immediate

SQL > startup

2. Other expansion

The size of the SGA_MAX_SIZE is not arbitrary and must meet certain conditions.

Sga_max_size=100M, which must satisfy the minimum sum of all components of SGA; at least the size of the sum of db_cache_size,log_buffer,shared_pool_size,large_pool_size,java_pool_size)

5. Daily memory related troubleshooting sql:

Find the statement that takes up a lot of shared pool memory:

Set linesize 150 pagesize 600

SELECT substr (sql_text,1,40) "Stmt", count (*)

Sum (sharable_mem) "Mem"

Sum (users_opening) "Open"

Sum (executions) "Exec"

FROM v$sql

GROUP BY substr (sql_text,1,40)

HAVING sum (sharable_mem) > & MEMSIZE order by 2 desc

Here, the value of MEMSIZE is 10% of the size of shared pool, in byte. This statement can find out the occupation of shared p

The SQL where ool has a lot of memory, these SQL can be similar literal statements or different versions of the same statement.

Query specific sessions that use more than 100m PGA:

Set line 200 pages 9999

Col MACHINE for a20

Col PROGRAM for a40

Col username for a15

Col process for 99999999

Col sql_id for 9999999999999999

Select s.sid, s.serial#, s.username, s.machine, s.program,s.process, s.sql_id, p.pga_alloc_mem/1048576 size_m, p.spid

From v$session s, v$process p where s.paddr=p.addr

And p.pga_alloc_mem > 104857600 order by 8 desc

The session that consumes the most pga memory

Select * from (

Select s.username, s.sql_id, s.prev_sql_id, p.spid, p.pga_alloc_mem/1024/1024/1024

From v$session s, v$process p

Where p.addr=s.paddr

Order by p.pga_alloc_Mem desc

) where rownum

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.