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

This article mainly introduces how to query pga/sga and component values in the database, which is very detailed and has a certain reference value. Interested friends must read it!

Estimate the impact of the size of the query buffer_cache setting on the number and time of physical reads:

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')

Query when the sga component size

SQL > select component,current_size,max_size,min_size from v$sga_dynamic_components

COMPONENT CURRENT_SIZE MAX_SIZE MIN_SIZE

-

Shared pool 4294967296 0 4294967296

Large pool 268435456 0 268435456

Java pool 134217728 0 134217728

Streams pool 436207616 0 436207616

DEFAULT buffer cache 1.6744E+10 0 1.6744E+10

KEEP buffer cache 0 0 0

RECYCLE buffer cache 0 0 0

DEFAULT 2K buffer cache 0 0 0

DEFAULT 4K buffer cache 0 0 0

DEFAULT 8K buffer cache 0 0 0

DEFAULT 16K buffer cache 0 0 0

COMPONENT CURRENT_SIZE MAX_SIZE MIN_SIZE

-

DEFAULT 32K buffer cache 0 0 0

ASM Buffer Cache 0 0 0

13 rows selected.

SQL >

SQL > select pool,sum (bytes/1024/1024) size_m from v$sgastat group by pool

POOL SIZE_M

--

15983.9976

Shared pool 4126.17428

Streams pool 416.03157

Large pool 256

Java pool 128

Oracle provides several initialization parameters to set the memory size of each part of the SGA

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 244M

Controls the size of the shared pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter java_pool_size

NAME TYPE VALUE

-

Java_pool_size big integer 52M

Controls the size of the JAVA pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter large_pool_size

NAME TYPE VALUE

-

Large_pool_size big integer 60M

Control the size of the large pool, which can be adjusted dynamically during the operation of the instance.

SQL > show parameter streams_pool_size

NAME TYPE VALUE

-

Streams_pool_size big integer 0

Control the size of the flow pool, which can be adjusted dynamically during the operation of the instance

SQL > show parameter db_cache_size

NAME TYPE VALUE

-

Db_cache_size big integer 160M

Controls the size of the data buffer, which can be adjusted dynamically while the instance is running

SQL > show parameter log_buffer

NAME TYPE VALUE

-

Log_buffer integer 5603328

Controls the size of the log buffer, which cannot be modified during operation.

SQL > show parameter sga_target

NAME TYPE VALUE

-

Sga_target big integer 0

Related to the automatic management of SGA. If the value is zero, DBA is required to manage SGA manually.

SQL > show parameter sga_max_size

NAME TYPE VALUE

-

Sga_max_size big integer 524M

The maximum memory that can be used by sga. The value of sga_target cannot exceed sga_max_size. When allocating memory for components in SGA, the total memory of SGA cannot exceed the value set by sga_max_size, otherwise an error will be reported. Sga_max_size cannot be dynamically adjusted while the instance is running. You must first modify the initialization parameter file, and then restart the instance.

The sga_max_size parameter is valid only when the instance is running. If the sum of parameter values composed of various parts of SGA is found to be greater than sga_max_size when the instance starts, the instance will ignore the currently set sga_max_ size value and modify sga_max_size to the maximum value of the current SGA.

SQL > show parameter statistics_level

NAME TYPE VALUE

-

Statistics_level string TYPICAL

This value is related to the automatic management of SGA memory. When the value is typical or all, oracle will use the relevant statistics that will be more collected as a reference to determine how to allocate each part of memory in SGA.

All parts of memory in oracle SGA are allocated and reclaimed at a granularity. The size of granularity cannot be set by DBA, depending on the size of SGA and the type of system platform. On most platforms, the SGA1G granularity is 16MB. Under the win32 platform, SGA > 1G, the granularity is 8MB. When setting the value of each part of oracle SGA, it needs to be an integral multiple of the granularity of oracle, otherwise oracle will adjust automatically.

SQL > select component,granule_size from v$sga_dynamic_components

COMPONENT GRANULE_SIZE

-

Shared pool 4194304

Large pool 4194304

Java pool 4194304

Streams pool 4194304

DEFAULT buffer cache 4194304

KEEP buffer cache 4194304

RECYCLE buffer cache 4194304

DEFAULT 2K buffer cache 4194304

DEFAULT 4K buffer cache 4194304

DEFAULT 8K buffer cache 4194304

DEFAULT 16K buffer cache 4194304

DEFAULT 32K buffer cache 4194304

Shared IO Pool 4194304

ASM Buffer Cache 4194304

14 rows selected

The particle size of each part of oracle SGA

SQL > select name,bytes from v$sgainfo

NAME BYTES

Fixed SGA Size 1375820 stores status information for databases and instances

Redo Buffers 5795840

Buffer Cache Size 167772160

Shared Pool Size 255852544

Data dictionary cache 62914560

Java Pool Size 54525952

Streams Pool Size 0

Shared IO Pool Size 0

Granule Size 4194304

Maximum SGA Size 548237312

Startup overhead in Shared Pool 58720256

Free SGA Memory Available 0

View the memory allocation of each part of the SGA

SQL > select * from v$sga

NAME VALUE

--

Fixed Size 1375820

Variable Size 373293492 includes shared pool, large pool, java pool and stream pool.

Database Buffers 167772160 data buffer (Buffer Cache Size)

Redo Buffers 5795840

View the memory allocation of each part of the SGA.

The memory used by each part of the SGA can only be used for its own use and cannot be shared with each other. If the Java Pool allocates 1 gigabyte of memory, but the current database does not use the flow feature, the space allocated to the stream pool cannot be used by other SGA

Components are used. Therefore, when using alter system set to set the memory composed of various parts of the SGA, you need to be careful not to lead to the waste of system memory and performance loss.

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 244M

SQL > alter system set shared_pool_size=230m

System altered

SQL > show parameter shared_pool_size

NAME TYPE VALUE

-

Shared_pool_size big integer 232M

Oracle automatically adjusts to granularity (in this case, 4MB) according to granularity.

SQL > show parameter pga

NAME TYPE VALUE

-

Pga_aggregate_target big integer 4G

SQL > select * from v$pgastat

NAME VALUE UNIT

-

Aggregate PGA target parameter 4294967296 bytes

Aggregate PGA auto target 2837357568 bytes

Global memory bound 429496320 bytes

Total PGA inuse 1607690240 bytes

Total PGA allocated 2188926976 bytes

Maximum PGA allocated 1.0088E+10 bytes

Total freeable PGA memory 176619520 bytes

Process count 336

Max processes count 1115

PGA memory freed back to OS 7.4384E+13 bytes

Total PGA used for auto workareas 464832512 bytes

NAME VALUE UNIT

-

Maximum PGA used for auto workareas 5814672384 bytes

Total PGA used for manual workareas 0 bytes

Maximum PGA used for manual workareas 1062912 bytes

Over allocation count 82922

Bytes processed 3.2595E+14 bytes

Extra bytes read/written 4.6775E+12 bytes

Cache hit percentage 98.58 percent

Recompute count (total) 15634412

19 rows selected.

SQL >

The above is all the contents of the article "how to query pga/sga and component values in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.