Oracle accuracy and Optimized Configuration

Optimizing the configuration of Oracle is very important. An appropriate Optimization Configuration can improve the system performance several times. However, an improper configuration will cause your system problems constantly. By default, Oracle is installed without any optimized configuration. Such configuration can be combined on your own machine, but never put it on your server.
This guide first introduces the Oracle memory structure, then describes how to view and modify memory parameters, and finally introduces other optimization parameters. Bytes
You can use the Oracle console or command line to optimize the configuration of Oracle. There are several advantages to configuring through command lines,
First, you do not need to enter the user name and password, which is useful when you forget the password (only on the machine where Oracle is located );
Second, sometimes the database goes wrong and cannot be connected to Oracle through the Oracle console, but it may be connected through the command line;
Third, we can look more professional, right? When we enter some commands in the command line, the system returns us a dozen texts, just like a hacker in a movie.
 
2.1 log on using the command line
Open the start-run menu and Enter cmd to open a dos window.
 
The command line is as follows:
 
Sqlplus <User Name> [/<password>] [@ <service name>] as sysdba
Generally, we use the sys or system user to log in and modify it. we enter the following command:
Sqlplus sys/huhao @ sa as sysdba
 
 
 
2.2 simpler logon commands
If you forget your password or want to simplify it, enter the following command:
 
Sqlplus/as sysdba
 
This method skips the user password and service name, which is useful when you forget the password, but must be on the server where Oracle is located (think about it, if it can be like this everywhere, so where is the security of Oracle ?).
If the Oracle server and client are installed on the server where Oracle is located, or multiple Oracle servers are installed, before using this simple command, you must first set the value of oracle_sid (that is, the service name). Otherwise, the following error occurs:
This error is understandable. There are multiple oracle_sid on the server. You didn't specify which one is, And sqlplus certainly didn't know which one to connect.
The syntax of this command is:
 
Set oracle_sid = service name
 
Run the following command:
 
Set oracle_sid = sa
Sqlplus/as sysdba
2.3 alter system
The alter system command is used to modify some Oracle parameters. We need to use the alter Oracle memory configuration.
Alter system has a scope option, which has three optional values: memory, spfile, and both.
Memory: Only parameters of the current instance are changed. If Oracle is restarted, the values before modification are restored.
Spfile: only modify the spfile parameter. After Oracle is restarted, the modified value is used. Spfile refers to the configuration file used during Oracle startup. Some parameters are stored in this file. Oracle reads this file at startup and performs initialization settings accordingly.
Both: Change the instance and spfile parameters.
3. Oracle memory configuration
To give you a better understanding of Oracle's memory structure system, Let's first look at the overall structure of Oracle databases.

3.1 overall structure of Oracle

Each Oracle database is composed of an Oracle Instance and a database (data file, control file, and redo log file). The so-called Instance is the medium for user interaction with the database, you can operate a database by connecting to an instance. The instance is composed of a unified memory structure (SGA, PGA, UGA) and a batch of memory resident processes. The instance is identified by ORACLE_SID in the operating system and the INSTANCE_NAME parameter in Oracle. The values of the two instances are the same. When the database is started, the system first allocates the system global zone (SGA) in the server memory, forming the Oracle memory structure, and then starts several operating system processes with resident memory, it constitutes the process structure of Oracle. The memory area and background process are collectively called an Oracle instance.

The instance structure is as follows:

 

3.2 SGA
System global zone-System global area (SGA) is a group of shared memory structures that store data and control file information of oracle database instances. If multiple users connect to the database at the same time, they will share the region, so SGA is also called Shared Global Area. When the database instance is started, the SGA memory is automatically allocated. When the database instance is closed, the SGA memory is recycled.
SGA is the region with the largest memory usage and an important factor affecting database performance.
SGA mainly includes the following parts:
1) Shared Pool
2) Data Buffer
3) large pool
4) Java pool
5) log Buffer
The above memory is the sum of the SGA memory. It is important to share the pool and data buffer, which will be explained later.
3.2.1 view SGA
SGA can dynamically adjust the size, that is, you do not need to shutdown the database to adjust the size. You can set the sga_max_size parameter in the initialization parameter. When the sum of each part of the SGA is greater than the set sga_max_size parameter, the sga_max_size parameter will be ignored, it is used to add the size of each part. When the size of sga_max_size is greater than the size of each part, the sga_max_size parameter is used.
SGA is the region with the largest memory usage and an important factor affecting database performance.
Run the following command to view the SGA:
Show parameter sga;
The result is as follows:

 
The value of sga_max_size is the size of SGA. It can be seen that the size of sga_max_size is 256 M.
3.2.2 modify SGA
To modify the SGA size, run the following command:
Alter system set sga_max_size = 164 M scope = spfile;
Here I change it to 164 M, and you can change it to the desired size. Because sga_max_size is a static parameter and cannot be modified at runtime, the scope can only be set to spfile. The new memory size will take effect after Oracle restarts.
The result is as follows:

 
3.2.3 Shared Pool
The shared pool is the memory area for parsing, compiling, and executing SQL, PL, and SQL programs.
It consists of three parts:
1) The Library Cache contains the analysis code and execution plan of SQL, PL/SQL statements;
2) Data Dictionary Buffer (Data Dictionary Cache) Table, column definition, and permission;
3) User MTS session information in the Usr Global Area.
The size of each of these three parts cannot be defined separately, and must be indirectly defined through the shared pool.
You may ask why you want to cache SQL statements? What if I don't cache it? To solve this problem, we must first understand the execution process of an SQL statement in Oracle.

3.2.3.1 SQL Execution Process
To execute an SQL statement in Oracle, follow these steps:
1) Create a Cursor to Create a Cursor;
2) Parse the Statement parsing Statement;
3) Bind Any Variables to Bind Variables;
4) Run the Statement;
5) Close the Cursor to Close the Cursor;
To query an SQL statement, perform the following steps:
1) Describe Results of a Query describes the Query result set;
2) Define Output of a Query defines the Output data of the Query;
3) Fetch Rows of a Query obtains the queried Rows.

3.2.3.2 SQL parsing process
From the above steps, we can see that each SQL statement needs to be parsed (Parse), and a parsing process needs to complete the following work:
1) Check the syntax to verify whether it is a legal statement and whether there is a syntax error;
2) Perform a semantic check to find the data dictionary to verify that the table and column definitions are correct;
3) (For the CBO optimization mode, refer to the Oracle optimizer chapter) to collect statistics on reference objects;
4) obtain the syntax analysis lock on the required object so that the definition of these objects is not changed during the syntax analysis of the statement;
5) check whether the user's permissions are sufficient;
6) select the best execution plan for this statement from many possible execution paths;
7) load it into the shared SQL zone;
(8) Build the statement version (P-CODE ).
Parsing is an expensive operation because it consumes a lot of resources and is time-consuming. Because of this, Oracle creates the concept of a shared pool, the shared pool automatically caches parsed SQL statements. If the same SQL statement is encountered in the future, no Parsing is required. This greatly improves the SQL Execution speed.

3.2.3.3 principle of cache SQL
When ORACLE executes an SQL statement, it first obtains a hash value from the string of the SQL statement through a hash algorithm, and then checks whether the hash value exists in the shared pool, if yes, use the cached execution plan to execute this statement (that is, cache hit, the hit rate of the shared pool will be mentioned later). If no (that is, the cache is missing) resolution is required.
Since Oracle uses the hash value of SQL characters to determine whether it is the same SQL statement, if your SQL has a slight transformation, it is another SQL statement in Oracle's view, it will be re-parsed.
For example:
Select id, name from members where id = 1403.
Select id, name From members where id = 1403.
Select name, id from members where id = 1403.
These three sqls are three different sqls in Oracle's view.

3.2.3.4 bind a variable
In most cases, there are some frequently changing values in SQL statements. For example:
Select id, name from members where id = 1207.
Select id, name from members where id = 1208.
Select id, name from members where id = 1209.
As mentioned above, such SQL statements are actually three different ones, because their characters are obviously different. So how can we make them the same SQL? You can bind variables.
The following is an SQL statement containing Bound variables:
Select id, name from members where id =: member_id
In this way, no matter how the member_id changes, Oracle considers this SQL statement to be the same, which can save the parsing cost.
So how can I bind variables in java Development? Note: do not consider the following code as binding variables:
Statement stmt = conn. createStatement ();
String member_id = member. id;
String SQL = "select id, name from members where id =" + member_id;
Stmt.exe cuteQuery (SQL );
In the above example, when the value of member. id is 1207, the SQL statement we pass to stmt is actually:
Select id, name from members where id = 1207.
When member. id is 1208, it is:
Select id, name from members where id = 1208.
In Oracle's view, they are still different SQL statements.
In fact, it is very easy to bind variables in java. You only need to use the PreparedStatement object. As follows:
String SQL = "select id, name from members where id =? ";
PreparedStatement pstmt = conn. createStatement (SQL );
Pstmt. setString (1, member. id); // pass the member. id to the first question mark.
In this way, PreparedStatement will automatically convert this SQL statement to an SQL statement similar to the following when it is passed to Oracle:
Select id, name from members where id =: member_id
In this way, the bound variable is implemented. It only needs to be parsed once. No matter how the member. id changes, no more Parsing is required.
The Hibernate we use internally uses PreparedStatement for processing. Therefore, we do not need to do anything to use the Bind Variable. Hibernate has already helped us.

3.2.3.5 view a Shared Pool
Run the following command to view the memory size of the Shared Pool:
Show parameter shared_pool_size;
The results are as follows:
 

3.2.3.6 modify a Shared Pool
Run the following command to modify the memory size of the Shared Pool:
Alter system set shared_pool_size = 90 M scope = both;

 
Shared_pool_size is a dynamic parameter that can be modified at runtime. Therefore, the scope is set to both, and the new memory size takes effect immediately. The changes are also saved in the Oracle Startup File.

3.2.3.7 view shared pool hit rate
The hit rate of the Shared Pool reflects the SQL repetition rate. The higher the hit rate, the higher the SQL repetition rate, that is, the more time saved for SQL parsing, the faster data query is reflected in the system.
You can run the following command to view the hit rate:
Select sum (pinhits)/sum (pins) * 100 "hit rate" from v $ librarycache;
The result is as follows:

 
If the hit rate is lower than 95%, you need to adjust the size of the Shared Pool. We know that if the bound variable is not used in the program, even if the shared pool is large, there will be no good results, but there will be side effects. Therefore, the more important thing is to change the most commonly used SQL statements to bind variables, and you will see obvious results.

3.2.4 Data Buffer
If an operation is performed each time, Oracle must read all data blocks from the disk and write each data block to the disk after it is changed. Obviously, the efficiency is very low. The data buffer zone stores frequently accessed data for all users. When modifying data, first retrieve data from the data file and store it in the data buffer. Modify/insert data is also stored in the buffer. commit or DBWR (details are provided below) when other conditions of the process are triggered, data is written to the data file. The size of the data buffer can be dynamically adjusted, but cannot exceed the limit of sga_max_size.
3.2.4.1 view Data Buffer
Run the following command to view the memory size of the Data Buffer:
Show parameter db_cache_size;
The results are as follows:

 
3.2.4.2 modify the Data Buffer
Run the following command to modify the memory size of the Data Buffer:
Alter system set db_cache_size = 50 M scope = both;
Db_cache_size is a dynamic parameter that can be modified at runtime. Therefore, the scope is set to both, and the new memory size takes effect immediately. The changes are also saved in the Oracle Startup File.

3.2.4.3 View data buffer hit rate
Data Buffer also has a hit rate concept, which generally requires a hit rate of 90% or above. If your hit rate is too low, your database is very inefficient and you need to adjust the size of the data buffer.
You can run the following command to view the hit rate:
Select (1-(physical. value-direct. value-lobs. value)/logical. value) * 100 "hit rate"
From v $ sysstat physical,
V $ sysstat direct,
V $ sysstat lobs,
V $ sysstat logical
Where physical. name = 'physical reads'
And direct. name = 'physical reads direct'
And lobs. name = 'physical reads direct (lob )'
And logical. name = 'session logical reads ';
The result is as follows:

 

PGA (Process Global Area) is a private memory Area of server process. It contains Global variables, data structures, and some control information. In Oracle8i, PGA adjustment is very complex. You need to adjust parameters such as SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and CREATE_BITMAP_AREA_SIZE. After ORACLE9I, you only need to adjust PGA_AGGREGATE_TARGET.
Each user connects to Oracle and occupies a certain amount of memory. Of course, after connecting to Oracle, the user will perform some operations, such as general queries and sorting queries, or query using Hash connections. All of these require memory, and the memory (there are some others, so I won't talk about it here .) The sum is the size of PGA. If the PGA is set too small, Oracle will frequently exchange data with the disk, and the performance will be greatly affected.
3.3.1 view PGA
Run the following command to view the SGA:
Show parameter pga;
The result is as follows:

 
The pga_aggregate_target value is the PGA size. It can be seen that the pga_aggregate_target size is 200 MB.
3.3.2 modify PGA
Run the following command to modify the memory size of the Shared Pool:
Alter system set pga_aggregate_target = 90 M scope = both;

 
Pga_aggregate_target is a dynamic parameter that can be modified at runtime. Therefore, scope is set to both, and the new memory size takes effect immediately. The changes are also saved in the Oracle Startup File.
3.3.3 view PGA hit rate
You can run the following command to view the hit rate:
SELECT a. VALUE "Check hit rate"
From v $ PGASTAT
Where a. NAME = 'cache hit percentage ';
The result is as follows:

Author: ERDP Technical Architecture"