Introduction: Oracle database is a large-scale database system, generally used in commercial and government departments. It is very powerful and can handle large quantities of data. It is also used extensively in networks. However, most small and medium-sized enterprises prefer to use database systems such as mysql and SQL server. Its operation is very simple and its functions are very complete. It's just that compared to Oracle database, it is not as good at processing large amounts of data.

1. ORACLE instance

1. ORACLE instance - including memory structure and background process

System Global Area (SGA) and Background Process are called databases Example.

2. ORACLE database - a collection of physical operating system files

A collection of a series of physical files (data files, control files, online logs, parameter files, etc.)

3. System Global Area (SGA)

System Global Area is a huge shared memory area. It is regarded as a large buffer of the Oracle database. Pool, the data here can be used by various processes of ORACLE. Its size can be viewed through the following statement:

SQL select * from v$sga;

NAME VALUE

-

Fixed Size 39816

Variable Size 259812784

Database Buffers 1.049E+09

Redo Buffers 327680

For more detailed information, please refer to V$sgastat, V$buffer_pool

Mainly includes the following parts:

a. ***Shared pool (Shared pool)

***Shared pool is in SGA The most critical piece of memory, especially for performance and scalability. A shared pool that is too small will kill performance and cause the system to stop. A shared pool that is too large will have the same effect and will consume a lot of CPU to manage the shared pool. Improper use of shared pools will only lead to disaster. ***The shared pool can be divided into the following two parts:

SQL Statement Cache (Library Cache)

When a user submits a SQL statement, Oracle will Perform analysis (parse). This process is similar to compilation and will take a relatively long time. After analyzing this SQL, Oracle will save its analysis results in Sharedpool's LibraryCache. When the database executes the SQL for the second time, Oracle will automatically skip this analysis process, thus reducing the system running time. This is also why the first SQL run is a little slower than the second SQL run.

The following is an example of parse time

SQL select count(*) fromscpass;

COUNT(*)

--

p>

243

Elapsed: 00:00:00.08

This is the time taken when neither Share_pool nor Data buffer has a data buffer

System altered.

Clear Share_pool, retain Data buffer

SQL select count(*) from scpass;

COUNT(*)

--

243

Elapsed: 00:00:00.02

SQL select count( *) from scpass ;

COUNT(*)

--

243

Elapsed: 00:00:00.00

From the time difference between the two SQL statements, it can be seen that the Parse time of the SQL is approximately 00:00:00.02

For the SQL statements saved in the shared pool, you can get the It is found in Sqltext and v$Sqlarea that programmers should try to increase the reuse rate of statements and reduce the analysis time of statements. A poorly designed application can destroy the entire database Sharepool. To improve the reuse rate of SQL statements, you must first develop good habits and try to use Bind variables.

Data Dictionary Cache

Obviously, the data dictionary buffer is a buffer pool specially prepared by ORACLE for the data dictionary for internal use of ORACLE. There is nothing to say. of.

The above content is relatively basic, so for beginners who are just getting started, in the early stages of learning, carefully study the content introduced in this article, I believe it will be very helpful for everyone's introductory learning. helpful.