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

This article will explain in detail the sample analysis of the basic knowledge and viewing commands of the SGA area in oracle. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

Instance: the image in memory after the database is started, and the background process that manages the memory area

Composition: (SGA+background process)

View the instance name of the database:

Sql > show parameter instance name

View the database name:

Show parameter db_name

The file composition of the database: parameter file (spfile,pfile..), control file (controlfile), log file (redofile..), password file (v$pwfile_users), data file, trace file (.trace), warning file (alert), archive file (achivlog)

Relationship between instance and database:

An instance corresponds to a database: a single point of database (usually)

Multiple instances correspond to a numerical control library: OPS and RAC (multiple SGA)

Previous parallel server: Oracle Paraller Server

Application Cluster: Real Application Cluster

SGA (system global area)

SGA is a set of shared memory structures that contain data and control information for an oracle instance.

Is shared memory allocated for the oracle system

Is the main component of the instance, which most affects the performance of the database.

Automatically manage all kinds of buffers dynamically (ASMM, keep,recycle,redo_log_buffer does not participate in dynamic allocation)

Cache type of SGA:

1. Db_buffer_cache (data caching)

(1) default_pool (access to this buffer by an ordinary process will move the cache to the MRU side of the LRU list, but it will be placed on the LRU side when executing FTS (full table scan) and small table queries)

(2) keep pool (data first-in, first-out, used to release the heat table, VIP pool)

(3) recycle pool (used to cool a large table, does not need much space, and attracts cold data. Equivalent to a garbage pool)

(4) db_nk_cache_size (different memory block sizes to accommodate different block size tablespaces)

2. Shared_pool (the most critical memory fragment in SGA, too small reduces performance and consumes a lot of cpu resources)

(1) library_cache (sql statement cache)

(2) data dictionary cache (data dictionary cache)

(3) other (over 600 parts)

3. Large_pool (often used as a backup pool)

4. Java_pool (java development support)

5. Redo_log_buffer (record any changes to the database, and the redolog written to disk is the basis for instance recovery)

Some of the important query commands for SGA:

Set up automatic tracking statistics:

Set autot on stat

Set autot off

Show sga

Select * from v$sga

Show parameter sga; (check the sga max size,sga target size size, whether the SGAs is locked to physical memory (lock sga) at runtime, and whether physical memory is used at startup (pre page sga)

Show parametetr pre_page_sga

Show parameter lock_sga

Alter system set pre_page_sga=true scope=spfile

Alter system set sga_max_size=512M scope=spfile (restart takes effect)

Show parameter sga_max_size

Show parameter sga_target

Alter system set sga_target_size=400m scope=both (memory-a-s pfile)

Granule_size: the granularity size of sga memory. The memory size allocated manually will be automatically adjusted to an integer multiple of the granularity.

If the SGA is less than 1G

If SGA is greater than 1G granule-size=8m (for windows) granule-size=16m (fro other)

SGA_TARGET (Automatic shared memory management)

View the lower limit of each cache:

Show parameter db_cache_size

Show parameter shared_pool_size

Show parameter java_pool_size

Show parameter large_pool_size

View the actual size:

Select name,current_size from v$buffer_pool

Select pool,sum (bytes) from v$sgastat group by pool; (View java,shared,large pool size)

If you need to change the allocation lower limit of a buffer, you need to close ASMM first. After modification, you need to modify all cache lower limits back to 0 so that ASMM can manage automatically.

Query the current allocation size of each pool:

Select component,current_size from v$sga_dynamic_components

Specifies that the data is still placed on the MRU side when the object is scanned.

Create table aaa () cache

Altert table aaa cache

Modify the keep,recycle pool size and specify the object to use

Alter system set db_keep_cache_size=10m scope=both

Alter system set db_recycle_cache_size=10m scope=both

Create table aaa () storage (buffer_pool keep/recycle)

Alter table bbb storage (buffer_pool keep/recycle)

Create a non-standard memory block size (memory block is not 8k) to create a non-standard size block tablespace

Show parameter db%cache_size

Alter system set db_16k_cache_size=10m

Create tablespace I datafile'/ u01Compact i.dat' size 50m blocksize 16k

View library cache size

Select sum (sharable_mem) from v$db_object_cache

Clear the cache:

Alter system flush shared_pool

Set timing:

Set timing on

View parsed sql statements saved in library cache

Select * from v$sqltext where sql_text like'% dba_object%'

Select sql_text from v$sqlarea where sql_text like'% dba_object%'

Data dictionary:

Select * from dict; View data Dictionary

See which users are in the database:

Select username from dba_users

Check what data files are in the database

Select file_name from dba_data_files

Select name from v$datafile

View the size of the data dictionary cache

Select sum (sharable) from v$sqlarea

Dynamic table:

View all the dynamic tables in the system:

Select * from v$fixed_table

Some of the additional memory information in the dictionary cache and other parts of the Fixed SGA:shared pool will be accessed by the background process of the instance, and will be fixed in the SGA after the instance is started. It is a fixed component in the SGA.

This is the end of this article on "sample Analysis of basic knowledge and View commands of SGA area in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.