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

View the size of the buffer cache:

SQL > SELECT component,current_size,min_size FROM v$sga_dynamic_components

COMPONENT CURRENT_SIZE MIN_SIZE

-

Shared pool 5570035712 5301600256

Large pool 67108864 67108864

Java pool 67108864 67108864

Streams pool 67108864 67108864

DEFAULT buffer cache 1.1409E+10 1.1140E+10

KEEP buffer cache 0 0

RECYCLE buffer cache 0 0

DEFAULT 2K buffer cache 0 0

DEFAULT 4K buffer cache 0 0

DEFAULT 8K buffer cache 0 0

DEFAULT 16K buffer cache 0 0

DEFAULT 32K buffer cache 0 0

Shared IO Pool 0 0

ASM Buffer Cache 0 0

14 rows selected.

SQL > show parameter sga

NAME TYPE VALUE

-

Lock_sga boolean FALSE

Pre_page_sga boolean FALSE

Sga_max_size big integer 16512M

Sga_target big integer 16512M

SQL > show parameter db_cache_size

NAME TYPE VALUE

-

Db_cache_size big integer 0

The default value of db_cache_size is 0, which means that the size is not set and is automatically adjusted by SGA.

Here you can see that buffer cache is automatically assigned by the system itself, probably 10880MB.

For example:

Now change it.

Alter system set db_cache_size=200M scope=both

SQL > alter system set db_cache_size=200M scope=both

Alter system set db_cache_size=200M scope=both

*

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-00384: Insufficient memory to grow cache

The results show that there is not enough memory to increase.

That is, the sga_target is already this big, and it's all over.

It's all distributed to each pool.

At this point, increase the value of DEFAULT buffer cache.

To increase the size of this buffercache pool,

First increase the size of the sga

First of all, let's talk about the train of thought in this place.

First increase the sga_max_size size

And then add sga_target.

Finally, add db_cache_size.

The statements are

Alter system set sga_max_size=500M scope=spfile

Alter system set sga_target=400M

Alter system set db_cache_size=200M

There are three available values for the scope option

Scope=spfile

It means that the parameter values are stored in the spfile file, that is, the contents of the initialization parameters file of oracle are changed and will not take effect until the database is restarted.

Scope=memory

Means to change the parameter value of an instance that is running in memory, only in memory, with immediate effect

If you close the instance, this value is lost and restored to the originally saved value, because the change is not written to the initialization parameter file.

Scope=both

This means that the parameter values are stored in both spfile and memory, which is effective immediately, because it is still valid after the database is restarted after being stored in spfile.

Both is the default value, spfile must be used to modify static parameters, and dynamic parameters can be used in all three ways.

Location of the spfile file

SQL > show parameter spfile

NAME TYPE VALUE

-

Spfile string / u01/app/oracle/product/10.2.0

/ db_1/dbs/spfilejiagulun.ora

In order to protect the security of the database, some parameters are protected to specify it as a static state.

Additional parameters must be added when the value is modified and the database must be restarted to take effect.

But most of the parameters are dynamic and can be modified at any time.

Sga_max_size is a static parameter, sga_target and db_cache_size are dynamic parameters

The current values of these three parameters

Sga_max_size big integer 272M

Sga_target big integer 272M

Db_cache_size big integer 0

Because sga_max_size and sga_target have the same current values

So you have to modify the sga_max_size and restart the database

Before you can continue to modify the value of sga_target to increase it

The value of db_cache_size can be increased only when sga has enough space.

My revision process

As soon as sga_target increases the value, it automatically allocates all remaining space to db_cache_size.

Thought it was because db_cache_size used the default value of 0

However, the effect remains the same after manually assigning values to db_cache_size.

When changing the value of db_cache_size

There is a limit to the increase.

Sga needs to analyze whether there is enough space to meet this increased value based on all the current state values.

There is generally no limit when reducing.

Because even if this value is set low, sga still uses automatic space allocation to allocate space to db_cache_size.

In general, the true value will not be reduced and will remain in a reasonable state.

Set the reasonable value of buffer cache:

SELECT size_for_estimate "Cache Size (MB)", size_factor,buffers_for_estimate "Buffers"

Estd_physical_read_factor est_read_factor

Estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t

FROM v$db_cache_advice

WHERE NAME='DEFAULT' AND block_size= (SELECT VALUE FROM v$parameter WHERE NAME='db_block_size')

Cache Size (MB) SIZE_FACTOR Buffers EST_READ_FACTOR ESTD_PHY_RED EST_PHY_RED_T

--

1088. 1 128520 12.0303 5839773844 10813098

2176. 2 257040 4.7929 2326564251 3913685

3264. 3 385560 2.4136 1171610058 1645529

4352. 4 514080 1.5546 754629867 826643

5440. 5 642600 1.2682 615616449 553642

6528. 6 771120 1.1589 562544805 449417

7616. 7 899640 1.0981 533021196 391437

8704. 8 1028160 1.0541 511676762 349520

9792. 9 1156680 1.0238 496952824 320604

10880 1 1285200 1 485421183 297958

11968 1.1 1413720. 9812 476284469 280015

13056 1.2 1542240. 9644 468161067 264061

14144 1.3 1670760. 9495 460909360 249820

15232 1.4 1799280. 9358 454276548 236794

16320 1.5 1927800. 9253 449182455 226790

17408 1.6 2056320. 9128 443069856 214786

18496 1.7 2184840. 9015 437583045 204011

19584 1.8 2313360. 8913 432648923 194321

20672 1.9 2441880. 8659 420332933 170134

21760 2 2570400. 8412 408330158 146563

20 rows selected.

SQL > show parameter db_block_size

NAME TYPE VALUE

-

Db_block_size integer 8192

The size of a block is 8192

1088MB divided by 8K is the value of 128520 for the buffers column in the first row.

EST_READ_FACTOR is the estimated physical reading factor.

Number of physical reads estimated by ESTD_PHY_RED

Estimated physical read time by EST_PHY_RED_T

We can take the physical reading time as the standard.

Because the most prominent role of buffercache is to cache block.

The goal is to reduce physical io

This reduces the time spent reading physical io.

ESTD_PHY_RED and EST_PHY_RED_T are basically synchronized.

The more times it takes, the longer it takes.

This is the number we are looking for in actual production.

It corresponds to the values of the last three columns of this row, when the values of these three columns do not change much after this row.

Just find the Cache Size (MB) value setting for this line.

Related articles:

Http://blog.csdn.net/xinzhan0/article/details/52663078

Http://blog.itpub.net/12679300/viewspace-1185623/

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.