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

Install oracle software-create database-create tables in the database-store information in tables-these table files need to be stored on the physical hard disk.

If a user (this user can be a remote user, a local user or an application) connects to the oracle database, the user cannot directly connect to the data files stored on the hard disk, so run an instance in oracle. The instance is not saved on the hard disk, the instance is a part of memory, and the library and table files are stored on the hard disk. Users cannot directly access the data in the library, but can only access the instance. The instance is a special memory block, and only the instance can communicate directly with the database. In our experimental environment, the instance and the database are on the same machine. We call it a single instance environment. For users, all operations are done in the instance. Oracle divides the instance into a memory block, which we call SGA. In fact, when the oracle instance starts, it will allocate a SGA system global zone and start some oracle background processes (daemons).

Introduction of SGA core components:

Memory in oracle = SGA+PGA

An instance can have only one SGA, and all server processes and background processes share a SGA.

PGA:

Let's first take a look at how a select statement is executed?

If a hr user writes a select * from scott.emp

Step 1: compile first, which includes syntax checking and semantic checking.

Step 2: if there is no problem with the compilation, you will enter the shared pool (shared pool), while shared pool is made up of library cache and data dictionary cache (data dictionary cache). The Sql statement goes into the librarycache the first time, that is, the correct compiled sql statement is stored in the librarycache, which is just the sql statement. Sql statements that are not cached in library cache need to be hard parsed (that is, they need to consume the device's Imax 0 resource to be read from the hard disk). Assuming that you have this sql statement in library cache, you don't need to do hard parsing, and then you look up data dictionary cache. Permission and object data and attributes are cached in data dictionary cache. All data dictionary cache checks whether hr users have access to the scott.emp table, and if they have permission to continue, if they do not, return a result directly to hr users. If a statement has never been executed, the statement is first cached in library cache in share pool, and the next time it is executed, the sql statement is fetched directly from share pool. Storage of sql statements also needs to take up space, and the space of share pool is also limited. In order to prevent the shortage of share pool space, the first-in-first-out rule, that is, the first-in-first-out sql statement will cover the first-executed sql statement to release space. Of course, the larger the share pool space, the better.

We use the enterprise manager to view the information about share pool

You can view it in sql plus in the following ways, where "0" indicates automatic management

You can view shared pool size from v$sgainfo

SQL > select name,bytes/1024/1024 | | 'MB' from v$sgainfo

Shared Pool Size

256MB

Large Pool Size

16MB

Java Pool Size

16MB

Step 3: databuffer cache now that hr users can access the scott.emp table, the role of databuffer cache is to transfer data blocks from disk or storage to memory and cache them in buffer cache.

For example:

Users access the content of scott.emp for the first time

SQL > set timing on

SQL > select * from scott.emp; executes the sql statement for the first time

14 rows have been selected.

Time spent: 00: 00: 00.05

SQL > / the time taken to execute the previous command again is 00: 00: 00.00

14 rows have been selected.

Time spent: 00: 00: 00.00

Summary: buffer cache caches the data itself, library cache caches the sql statement itself.

You can view the size of the buffer cache through show parameter db_cache_size

Step 4: redo log buffer when the user performs operations such as insert,update,delete,create,alter, the data changes. Before the changed data is written to the data buffer (buffer cache), the redo log buffer is written, and the data before the change is also put into the redo log cache. This is done to ensure that when the data is recovered, oracle knows which transactions need to be committed and which transactions need to be withdrawn.

If you have any questions about this article, please add Wechat below to discuss it.

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.