Oracle architecture
Architecture of Oracle
?
Part 1: Oracle8i architecture
Chapter 1. Summary
You can learn about the following in this chapter.
1. Understand the composition of Oracle instance
2, understand the composition of the Oracle database
3, understand the composition of Oracle memory structure
4. Understand the role and division of the background process
5. Understand the physical files of the database and the corresponding logical structure
6, understand the overall architecture of Oracle
Chapter II. Understand the Oracle Example
2.1 Oracle Server
Oracle is a portable database - it can be used on each platform in each platform, that is, the so-called cross-platform feature. Also slightly different on different operating systems, such as on UNIX / Linux, Oracle is implemented, each major function is a process; on Windows, it is a single process, but in this process There is a plurality of threads. However, from the overall architecture, Oracle is the same on different platforms, such as memory structures, background processes, and data storage.
A running Oracle database can be seen as an Oracle Server, which consists of database (Database) and instances (instance), in general, one Oracle Server contains an instance and a corresponding database, but In special cases, such as the OPS, 9i RAC of 8i, one database in a server can correspond to multiple instances.
A series of physical files (data files, control files, online logs, etc.) or corresponding logical structures (table space, segments, etc.) are called database, simply, is a series of physical files with disk. Composition. Oracle Memory Structure and Background Process are instances of the database, one instance can only be installed (mount) and open (Open) on a database, responsible for the corresponding operation of the database and interact with the user.
The relationship between examples and databases is shown below:
Figure 1 Oracle Server
2.2 Oracle Memory Structure (Memory Structure)
2.2.1
Memory structure composition
Oracle memory structure can primarily share the memory area and non-shared memory area, and shared memory regions mainly include the system global zone SGA (System Global Area), and the non-shared memory area is mainly composed of PROGRAM GLOBAL AREA, which can be used as follows. Graphical representation.
Figure 2 Oracle Memoery Structrue
2.2.2
Global Sharing Area System Global Area (SGA)
System Global Area is a huge shared memory area, which is seen as a large buffer pool in the Oracle database, where data can be shared by the various processes of Oracle. The size can be viewed by the following statement:
SQL> SELECT * FROM V $ SGA;
Name Value
-------------------- ---------
Fixed Size 39816
Variable size 259812784
Database buffers 1.049e 09
Redo buffers 327680
More detailed information can be referred to V $ SGASTAT, V $ buffer_pool
It mainly includes the following parts:
2.2.2
.1 shared pool
The shared pool is the most critical memory fragment in the SGA, especially in performance and scalability. A too small shared pool will kill performance, so that the system stops, too large sharing pools will also have the same effect, which will consume a lot of CPU to manage this sharing pool. Incorrect use of shared pools will only bring disasters. The shared pool is mainly divided into the following two parts:
1, SQL statement cache) When a user submits a SQL statement, Oracle analyzes this SQL, which is similar to compiling, which will cost relatively more time. After analyzing this SQL, Oracle will save his analysis results in the Library Cache of Shared Pool. When the database is executed for the second time, Oracle automatically skips this analysis process, thereby reducing the time of the system. This is why the first time running SQL is slower than the second run SQL.
Below example, the time of PARSE
SQL> Startup
SQL> SELECT Count (*) from userTable;
Count (*)
------------
243
Elapsed: 00: 00: 00.08
This is the time used in the case where Share_Pool and Data Buffer have no data buffer.
SQL> ALTER System Flush Shared_pool;
SYSTEM altered.
Clear Share_Pool, keep Data Buffer
SQL> SELECT Count (*) from userTable;
Count (*)
-----------------
243
Elapsed: 00: 00: 00:00.02
SQL> SELECT Count (*) from userTable;
Count (*)
----------------
243
Elapsed: 00: 00: 00:00.00
From two SQL time differences, it can be seen from the SQL PARSE time for 00: 00:00.02
For SQL statements saved in the shared pool, you can query from V $ SQLText, V $ SQLAREA, for programmers, try to improve the reuse rate of statements, reduce the analysis time of statements. A design-designed application can destroy the Share Pool of the entire database, and improve the reuse rate of the SQL statement must first develop good habits, try to use bind variables.
2, data dictionary buffer (Data Dictionary Cache)
Obviously, the data dictionary buffer is a buffer pool prepared by Oracle Specially prepared for Oracle, nothing can be said.
2.2.2
.2 block buffer cache ---- Data block buffer
These buffers are some data blocks that are used in all data files. Let them operate in memory. There are no system files, completed data files, temporary data files, and rollback segment files in this level. That is, the data blocks of any file may be buffered. Any modification of the database is completed in this buffer and writes the modified data into the disk by the DBWR process.
The blocks of this buffer are basically managed in two different lists. One is the "dirty" table (dirty list), written in the writing process of the database block, and the other is a list of inquirable blocks (LRU LIST), general In case, it is to manage the Least Recently Used, LRU algorithm.
The block buffer cache can be divided into the following three parts (Default Pool, Recy Pool). If it is not an initialization parameter (init.ra), Oracle will default to default pool.
Due to the limitations of operating system addressing capabilities, do not pass special settings, on 32-bit systems, block buffer caches can be achieved.
1.7G
On the 64-bit system, the block buffer cache can be achieved.
10g
.
2.2.2
. 3 redo log buffer
The buffer of the redo log file is recorded in the order in order, and then writes it into the disk by the LGWR process. These modifications may be a DML statement, such as (INSERT, UPDATE, DELETE), or DDL statement, such as (Create, Alter, Drop, etc.). The presence of the redo log buffer is because the memory to memory is more than the speed of memory to the hard disk, so the recovery of the database can accelerate the operation speed of the database, but the consistency and recovery of the database, data is The retention time in the redo log buffer is not very long. Therefore, the re-log buffer is generally small, greater than
3M
The subsequent log buffer has not much practical significance.
2.2.2
.4 Java program buffer (Java pool) ------ Java pool
After Java's program area, Oracle 8i, Oracle added support for Java in the kernel. The program buffer is reserved for Java programs. Provide syntax analysis area for Java. If no Java program is not necessary to change the default size of the buffer. His size is set in the Java_Pool_Size of the init.ora file, default
10m
.
2.2.2
.5 big pool (Large pool)
The name of the big pool is not because it is large, but because it is used to allocate large block memory, the process is more than the memory than the shared pool, starting at 8.0.
The following object uses a big pool:
1, MTS - Assign UGA in the Large Pool of SGA
2, PARALLEL EXECUTETION OF STATEMENTS - Allows allocation of the message buffer to be used to coordinate parallel query servers
3, Backup (Backup) - Used for RMAN Disk I / O Cache
2.2.3
Program Sharing Area Program Global Area (PGA)
Program Global Area (PGA) is used to save memory segments related to user processes, and PGA is always allocated locally by a process or thread, and other processes cannot be accessed. -------- So non-sharing J
User Global Area (UGA) is actually a state of the session, which is the memory that the session must always get. For a dedicated server process, the UGA is allocated in the PGA. For multi-threaded processes, UGA is assigned in Large Pool.
PGA / UGA typically stores information such as users' variables, permissions, stacks, sort spaces. The largest of the PGA / UGA is also Sort information, determined by the initialization parameter sort_area_size, since Sort Information is assigned in the UGA, so it can better utilize memory in the shared server.
2.3 Background Process
The background process is Oracle's program that manages the reading and writing, recovery, and monitoring of the database. Server Process is primarily connected and communicated by him and User Process, and is exchanged by him and User Process. On the UNIX machine, the Oracle background process relative to the operating system process, that is, an Oracle background process will start an operating system process; on the Windows machine, the Oracle background process is relative to the operating system thread, open the task manager, we only Can see an Oracle.exe process, but through another tool, you can see the threads included in the process here. The relationship between the background process and other structures is shown in the figure:
Figure 3 Oracle Backgroup Process
On the UNIX can be viewed by the following method:
PS-EF | GREP ORA_
# ps -ef | grep ora_ | GREP XCLUAT
Oracle 29431 1 0 Sep 02? 2:02 ORA_DBWR_SID
Oracle 29444 1 0 Sep 02? 0:03 ORA_CKPT_SID
Oracle 29448 1 0 Sep 02? 2:42 Ora_SMON_SID
Oracle 29442 1 0 Sep 02? 3:25 ORA_LGWR_SID
Oracle 29427 1 0 Sep 02? 0:01 Ora_PMON_SID
Oracle systems have 5 basic processes they are
DBWR (Data File Write Process)
LGWR (log file writing process)
SMON (system monitoring process)
PMON (User Process Monitoring Process)
CKPT (checkpoint process, synchronous data file, log file, control file)
2.3.1
Data writing process DBWR
Write the data buffer of the modified data buffer to the corresponding data file
Maintenance of air buffers in the system
Here, it is pointed out that several easy-to-error concepts:
· When a update is submitted, DBWR writes the data to the disk and returns to the user to complete.
· DBWR will trigger CKPT background process
· DBWR does not trigger the LGWR process
The above concept is wrong.
DBWR is a very underlying work process, and he batchs the data of the buffer to disk. There is little relationship with any recent user's process, nor is it controlled by their control. As for DBWR, it will not trigger LGWR and CKPT processes, we will discuss it in the following sections. //, that is, the DBWR process will not write data to disk after a update submission. He will decide when he will decide when to write the data of the buffer to disk J.
The main conditions for DBWR work are as follows
· DBWR timeout
· There is no more space buffer in the system to store data
· CKPT process triggers DBWR, etc.
2.3.2
Log write process LGWR
Write the data written to redo the log buffer, LGWR is a process that must communicate with the front desk user process. When the data is modified, the system generates a redo log and records in the redo log buffer. This redo log can be similar to one of the following structures:
SCN = 000000001000
Data block ID
Object ID = 0801
Data line = 02
Modified data = 0011
When submitted, the LGWR must write the data of the modified data to the log data file, and then notify the recent process to submit success, and notify the user by the front desk process. From this point, it can be seen that LGWR undertakes the task of maintaining system data integrity.
The main conditions for LGWR work are as follows
· User submit
· There is 1/3 redo log buffer unwritten disk
· More than
1M
Redo the log buffer is not written to disk
·time out
· DBWR's SCN number of data is larger than the SCN number of LGWR record, DBWR triggers LGWR write
2.3.3
System monitoring SMON
Mainly included
· Clear temporary space
· Complete system instance recovery when system startup
· Competency and idle space
· Restore transactions from unavailable files
· Instance recovery of failed nodes in OPS
· Clear Obj $ table
· Reduced back
· Remove the rollback segment
2.3.4
Process monitoring PMON
Mainly used to remove the failure user process, release the resources used by the user process. If PMON will roll back the uncommitted work, release the lock, and release the SGA resource assigned to the failed process.
2.3.5
Checkpoint process CKPT
Synchronize data files, log files, and control files, due to the working principle of DBWR / LGWR, cause data files, log files, and control files, which requires a CKPT process to synchronize. CKPT updates the header information of the data file / control file.
The main conditions for CKPT work are as follows
· When logging in log
· When the database is immediate, transaction, normal option shutdown database
· Determine based on the value of the settings of the initial file log_checkpoint_interval, log_checkpoint_timeout, Fast_Start_IO_TARGET to determine
· User trigger
The startup of the following process requires manual configuration
2.3.6
Archive process Arch
When the database is running in an archive, Oracle will start the ARCH process. When the log file is written, the log file is switched, the old redo log file is copied by the ARCH process to one / more specific directories / Remote machine. These replicated red log files are called archive log files.
2.3.7
Distributed recovery RECO
Responsible for solving the fault in distribution. Oracle can connect remote multiple databases, and some things are in a state of being afforded due to network issues. The RECO process tries to establish communication with the remote server. When the fault is eliminated, the Reco process automatically solves all the unresolved sessions.
2.3.8
Server process
Classification of service processes
· Dedicated Server Process
A service process corresponds to a user process
· Shared service process (Multitreaded Server Process)
A service process corresponds to multiple user processes and turns to the user process.
2.3.9
User Process User Process
At the client, it is responsible for passing the user's SQL statement to the service process and collects query data from the server segment.
2.4 A concept of throughout the database --- System change number SCN (System Change Number)
The system changes, a serial number maintained by the system. Automatic increase when the system needs to be updated, and he is an important sign for consistency and sequential recovery in the system.
Run the following statement to get the system SCN number
SQL> SELECT MAX (KTuxescnw * Power (2, 32) KTUXESCNB) SCN from x $ ktuxe;
?? SCN
------------
? 3
1014
SCN has the following features:
a. Query statement does not increase SCN, even if it occurs at the same time, the corresponding SCN within the database is also different. This ensures the order of data recovery.
b. Maintain data consistency, when a query is executed, he will get a current SCN number from the system. While he looks for data, he checks each data line and his corresponding SCN number, only Those who are not bigger than his SCN can be taken from the buffer corresponding to the user data file, and those who are greater than his SCN number should be removed from the buffer of the backliver data file.
Case Analysis:
A query returns the following 5 lines
ID Name
-------------------------------------
1
Shanghai
2
Beijing
3 gugangzhou
4 shenzhen
5 hanzhou
User A starts from 12:00, and ends at 12:05 to execute an UPDATE statement at 12:01 User B, updated the ID is 2 records beijing should become tianjing. And submit, this time user A That query does not appear tianjing records. The SCN of the query is N and the user B is updated so that the SCN of the system becomes n 1 When the user A queries the record of ID = 2, it is found that his SCN is more than the SCN at the beginning of the query, he will Find the record of SCN = N in the rollback segment data buffer and returned it.
Chapter 3. Oracle Database (Database)
3.1 Physical structure - a collection of physical operating system files.
3.1.1
Control file
Parameter file init.ora records the location of the control file, the control file is a very small binary file, up to 64MB, the control file includes the following main information
· Database name, checkpoint information, timestamp created by the database
· All data files, online log files, archive log file information
· Backup information, etc.
With this information, Oracle knows that files are data files, which are currently the log files, which are basic conditions for system start and run, so he is the root of Oracle. If there is no control file system, it is impossible to start. Control files are very important, usually using multiple mirror copies to protect control files, or use RAID to protect control files. The loss of the control file will make the recovery of the database become complicated. Control file information can be obtained from V $ ControlFile
3.1.2
Data file (data files)
Details of the data file are recorded in the control file
You can view data files by way of following
SQL> SELECT NAME FROM V $ datafile;
Name
---------------------------------------------
/u05/dbf/Prod/system_01.dbf
/u06/dbf/Prod/temp_01.dbf
/u04/dbf/prod/Users_01.dbf
/u09/dbf/prod/rbs_01.dbf
/u06/dbf/prod/applsys_indx_01.dbf
/u05Dbf/prod/applsys_data_01.dbf
The data file is the most important physical file in Oracle, which directly records user data. According to the use, you can divide the data file into the following categories:
· System data file
· Rollback data file
· Temporary data file
· User data file
All of the above various documents belong to tablespaces of different properties, and in the following logical structures, the role of this type will be further explained.
3.1.3
Redo file (redo files)
Anything that users perform in the database will be recorded in redo log files. Before you understand the redo log, you must understand the two concepts of redo logs, redo log groups and redo log group members (Member), at least two log group files in a database, and then write another A group, that is, the turn is written. At least one log group is at least one log group, multiple log members in a log group are mirror relationships, which is conducive to the protection of log files, because the corridation of log files, especially the damage of the current online log, the influence on the database It is huge.
The switching process of the online log group is called switching, requiring special attention that log switches will cause temporary "hanging" in a database that is not optimized. There are two cases that hang roughly:
· In the case of archiving, the logs that need to be archived have no way to archive, and the online log needs to be reused.
· Checkpoint incident has not been completed (log switching causes checkpoints), and online logs need to be reused
Solving the common means of this problem is:
I. Increase the log group
II. Increase log file member size
A online log composition and operation of two members of each log group are roughly shown in the figure:
Figure 4 redo log
You can view the log group via V $ log, and V $ logfile can view the specific member file.
3.1.4
Archive Diles
Oracle can run in two modes, archive mode and non-archive mode. If you don't use archiving mode, of course, you will not archive logs, however, your system will not be a practical system, especially if you cannot use the system, because you may lose data. However, in archiving mode, in order to save all the modifications of the user, after the redo log file is switched and the overwriting system is saved into a set of continuous file series, the file series is the archive log file.
Some people may say that archive log files occupy my large number of hard disk space, in fact, think about it, are you willing to waste a little disk space to protect your data, or willing to lose your data? Obviously see, we need to guarantee our data security. In fact, archiving is not always occupying your disk space, you can back up her back to tape, or remove all log files before the last full backup. You can view the information of the archive log file via V $ Archived_Log and V $ log_history.
3.1.5
Initialization parameter file (parameter file)
InIntSid.ora or init.ora file, because the version is different, its location may also be different. In 8i, it is usually located in $ oracle_home / admin /
/ Pfile
under. In 9i's parameter file is a plain text file, you can open modifications with a text editor, starting from 9i, a parameter file called SPFile, saved in binary.
Initialization file records a launch parameter of many database, such as memory, control file, number of processes, etc. When loading (load upon Nomount), the initialization file records a lot of important parameters, the performance of the database is very much, if not It, the database will not be able to start. Before 9i, the modification of the parameter file must restart the database to make the parameters, starting from 9i, you can use the command to modify the contents of the spfile file.
The parameters in the parameter file are not always constant, and the version is different. Most parameters, such as db_block_size's life is very long, and many other parameters have been discarded as the version changes. In addition to the parameters of the document record, Oracle also supports many internal parameters, of course, these parameters are not recommended.
You can query the current parameter settings via the V $ Parameter view.
3.1.6
Other files
i. Password file
The authentication of the SYSDBA authority user used for Oracle, mainly refers to the internal user before 9i, and the user has been canceled from 9i. The password of the password file can be modified through an ORAPWD command.
Ii. log file
· Alarm log file (Alert.log or Alrt
. la
)
Record the database start, close, and some important error information. Database administrators should regularly check this file and make even if the problem occurs. You can find his path Select Value from V $ Parameter where name = 'background_dump_dest', or get its path through the parameter file.
· Background trace file
The path is consistent with the alarm file path, and the information written when the system background process is wrong.
· User tracking file
The information written when the user process is wrong, it is generally impossible to read, can be transformed into the format that can be read through the Oracle TKPROF tool. The user tracks the path of the file, you can find his path Select Value from V $ Parameter where name = 'user_dump_dest', or get its path through the parameter file.
User tracking files can be generated by setting user tracking or dump commands, generally in debugging, optimization, system analysis.
Chapter 4. Oracle Logic Structure (Logical Structure)
Oracle logic structure consists of a series of interrelated logical objects. Figure:
Figure 5 logical structure
4.1 Table Space (Tablespace)
Table space is the basic logical structure in the database, a collection of data files. A table space can contain multiple data files, but a data file can only belong to a table space. Before 8i, the type of table space has only one, called a Dictionary Management Table space (DMT), that is, the assignment of space in the data dictionary. After 8i, in order to reduce the overhead on the dictionary, the local management table space (LMT) is introduced. In this type of table space, the bitmap stored in each data file is used to manage space allocation, no Require the use of data dictionary. Local management has a high speed, no fragmented, etc., and it is recommended to manage local management.
You can query the tablespace via V $ TABLESPACE, and DBA_TABLESPACE can query detailed tablespace information.
4.2 (segment)
The segment is the space occupied by the object in the database, although the segment and database objects are one or one, but the segment is from the perspective of the database store. A segment can only belong to a table space, of course, a table space can have multiple sections.
Table space and data files are a pair of relationships on physical storage, tablespaces and sections are a pair of relationships on logical storage, and segment is not directly related to data file. A segment can belong to multiple data files, and the segment can specify which data file is expanded to.
The section can be divided into the following four
· Data segment (Data Segment)
· Index Segment
· Rollback Segment
· Temporary Segment
Detailed segment information can be queried by DBA / ALL / User_SEGMENTS.
4.3 Intervals -------- or called "expansion segments" better
There are a variety of interpretations about Extent, and some translation is translated as an panel, and I usually translate as a range. There can be multiple intervals in a segment, and the interval is a larger storage space for data one-time, until the interval is full, the database will continue to apply for a new reserved storage space, namely a new interval, Alternatively, the maximum section (Max Extent) or no disk space available is available.
In Oracle8i or later, theoretically, a segment can be infinite intervals, but multiple intervals have a performance impact on Oracle, and Oracle recommends distributing data from as little as possible to reduce Oracle's management and magnetic head, but In some special cases, it is necessary to distribute one segment on multiple data files or multiple devices, and the appropriate plus multiple intervals is also very beneficial.
Detailed interval information can be queried by DBA / ALL / USER_EXTENTS.
4.4 Oracle Data Block (Block)
Oracle's most basic storage unit, specified when establishing a database, although it is visible in the initialization file, but cannot be modified. In order to ensure the speed of access, it is an integer multiple of the OS data block. Oracle's operation is based on the block, one interval can contain multiple blocks, and if the interval size is not an integer multiple of the block size, Oracle actually expands to the integer multiple of the block.
The internal structure of the block is more complicated. Take the block of the table as an example. From a simple structure, the internal division can be divided into the following sections: public head, table catalog, line Directory, available space, etc.
The following is a general structural diagram of a table block:
Figure Six Table Block
Block header contains information about block types (table blocks, index blocks, etc.), information about blocking activity and excessive transaction information, and address of the disk on the disk. Table Directory, if given, the information containing the tables stored in this block (the data of multiple tables may be saved in the same block). The row directory is included in the description line discovered in the block. The above 3 parts is a block overhead, and the remainder is available storage space, which can be used to obtain the available space size with the following query. SELECT KVISVAL, KVISTAG, KVISDSC from Sys.X $ KVIS;
The general 8K (8192) block is available in 8168
PCTFree and PCTUSED are the two access parameters of the table, in fact, act on the blocks in the table, PCTFree and PCTUSED represent two percentages, the defaults are 10 and 40, respectively. PCTFREE indicates that the percentage of available space is used for future updates, avoiding row migration. If the row data reserves the space reserved for PCTFree, the block will be revoked from Free List and no longer receive data. PCTUSED indicates that when the travel free space is lowered (such as deleting data) to the percentage specified by the parameter, the block re-enters the free list and starts receiving new data. PCTFREE and PCTUSED configuration have a certain relationship with the optimization of the system, so it should be cautious, and PCTFree Pctused should not be greater than or equal to 100, otherwise the block will cause the block to move downward, seriously affect performance.
4.5 Basic Table Space Introduction
4.5.1
System Table Space (SYSTEM)
The table space contained data file is called a system data file.
The table space stores system tables and data dictionaries, which generally does not place data from users, but user scripts, such as processes, functions, and package are saved in the data dictionary.
The data dictionary is some system tables or views, where the system is stored, and he includes database version, data file information, table and index segment information, system's operating status, etc., etc., etc., etc., etc., and user script information. The database administrator can understand the running status of Oracle by querying the data dictionary.
View data data dictionary SQL
Select * from Dict
View SQL of the internal system table
SELECT * FROM V $ fixed_view_definition
DBA must have a deep understanding of the data dictionary in the system table of the system, they must prepare some basic SQL statements, can immediately understand the status of the system and the status of the database, these basic SQL includes
· The remaining space of the system
· System SGA
· Status system waiting
· User's permissions
· Current user lock
· The use of buffers, etc.
On the road to DBA, we don't recommend you to excessively depends on excellent database management tools such as OEM / Quest, because they are not conducive to your understanding of the data dictionary, the SQL statement can complete almost all database management work.
A large number of reading is a significant feature of the table space.
4.5.2
Temporary table space (Temporary)
This table space contains data files called temporary data files
Mainly stores temporary data such as user sorting, because there is no way to open up a time period on a permanent table space, so there must be a temporary table space, mainly for sorting operations that cannot be performed on memory. We must specify a temporary table space for the user.
The space occupied by the temporal segment will be released when the next system is started.
4.5.3
Rollback segment table space (rollback)
If the database is modified to the data, then the rollback segment must be used, and the rollback segment is used to hold the data (Undo) before the modification. Rollback segments are usually placed on a separate table space (rollback table space), avoid watch space fragmentation, which contains data files that are rollback data files. 4.5.3
.1 Returning to the role of the segment in the system
When the database is updated inserted, the new data is updated to the original data file, and the old data is placed in the rollback segment. If the data needs to roll back, then you can roll back Segments are copied to the data file again. To complete the rollback of the data. When the system is restored, the rollback segment can be used to roll back to the Commital's data, and the system has been resolved.
Rollback segments are generally written, a small number of reads, so it is recommended to put the rollback segment into a separate device (such as a separate disk or RAID) to reduce the IO contention of the disk.
4.5.3
.2 returning segment works
· One rollometer space can be divided into multiple returns.
• A rollover segment can save data from multiple sessions.
· Rollback segment is a circular data model
It is assumed that the rollback segment consists of four intervals, and their usage is the interval 1à interval 2à interval 3à interval 4à interval 1. That is, the interval can be recycled. When the interval 4 is in the interval 1, the session in the interval 1 has not ended, and the interval 4 cannot be reused after it is used, and the system must allocate the interval 5. Continue to serve other session services. This is why the reasons why returning to the gap will continue to "rise", and the expansion of the rollover segment is affecting performance, and try to avoid it.
This is an example of a returning segment from 4 intervals to 5 intervals:
Figure 7 rollback segment
We analyze the completion of an UPDATE statement
1. Users submit a UPDATE statement
2, Server Process Check the memory buffer.
If there is no buffer of the data block, read from disk
i. If there is no more space, DBWR is started to write a dirty buffer that will not be written to the disk.
II. If there is a valid space, read
3, update data in the buffer
i. Apply for a returning segment entrance to write the old data
II. Plock and update data
III. Record in Redo Log Buffer at the same time
4. Users submit a commit statement
i. SCN increase
II. Write Redo Log Buffer to Redo Log File
Iii. Return to user commit completion
4.5.4
User Table Space (USER)
The data files are included as user data files
It is generally established by the user to access the tablespace of user data, generally two types of common user-type data, data, and indexes. In general, if the condition is permitted, it can be considered on different disks.
Chapter 5. Frequently Asked Questions
1. What is the relationship between examples and SID?
Often someone asking the SID? SID in the Oracle system is a frequent variable, such as environment variables Oracle_sid, initialization file INITSID.ORA, what is SID? In fact, the SID is an identifier of the Oracle instance, and the different SIDs correspond to different memory buffers (SGAs) and different background processes. In this way we can have multiple SID database instances on a physical server.
2, what is the relationship between Oracle Database and an instance?
The database is composed of an instance of a physical file and an access data file. When an instance of an access data file is one, the database is called a single-node database. This is the most database form we see. Of course, there is a multi-node database, which is one or more instances to access a database (or jointly accessing a set of data files), better providing stability and parallel processing capabilities. This is called OPS (Oracle Parallel Server) in Oracle9i in Oracle9i, called RAC (Real Application Cluster). In this database. Two / multiple instances are on different servers, all Oracle data files on a shared disk array, instances on multiple servers can work simultaneously, they communicate through an internal network. If a server can't serve the service, the other will take over its work, especially in key business, great potential. 3. Is there any data that may be not submitted in the data file in the running database?
This is likely to exist because the data of the user data file is written by DBWR, and DBWR is a very bottom background process, not responsible for interacting with the user. The user's interaction is done by LGWR.
4, in the problem 3, if there is no data written, the machine suddenly power off, will the data integrity will be damaged?
No, because the integrity of the database is LGWR to ensure that Oracle ensures that any modifications of DBWR write data files have been recorded in redo log files. When the system starts again, you can know that those data are not submitted by reading a log file. This time Oracle will automatically roll back these data. Therefore, the damage of the online log, especially the current online log, the impact on the database is huge, which may result in incomplete databases.
5. Does the data file lose data?
It can be said that if you have backed up and archiving, it will not. Because all records of data modifications are recorded in redo logs, it will not be lost, and you will resume all data as long as you recover the previous backups Restore and current online log files.
6, is there a log damage to log damage?
The above is said that the logistics of the online log is extremely large, so we may not only lose data, but it may also cause different steps in the database. Records of all commits in redo logs will be lost, which is why Oracle is the reason for mirroring on line weight log files. Any data loss is not allowed.
7. Can I specify if I don't write a rollback segment?
It is not possible, the writeback segment is Oracle to ensure consistent read and transaction consistency. The rollback segment is a high-write section, it is recommended to put it on a separate device.
For DDL statements, such as DROP, TRUNCATE can do not write a segment (without UNDO information), so the deletion of the entire table, if the amount of data is relatively large, it is recommended to use Truncate Table. That is, it is impossible to recover by Rollback after a table! ! J
Do not write online logs, but you can write very little online logs in some specific operations, such as creating a table via Create Table Tablename as SELECT in NOLOGGING, or in an append method INSERT data to the table, Or direct loading and other operations.
Chapter 6. Summary
Here, we understand the relationship between examples and databases, a database can have multiple instances, but an instance cannot correspond to multiple databases. In general, we are all single-node database, ie, one instance only corresponds to A database.
We understand the composition of the Oracle instance, including memory and background processes, further explaining the composition of SGA and SGA, and analyzes the benefits of statement reuse. In the background process, it is important to elaborate DBWR and LGWR, where DBWR is a background process from Oracle control, and LGWR is responsible for interacting with the user. In the Oracle database, we have an important explanation of the physical and logical structure of the database, in In physical structures, you need to pay attention to the following files: Control files, online logs, data files and parameter files. In logical structures, you need to know the relationship between each logical structure, from large to small order: Table space à paragraph à zone à block.
?
?
?
?
?
?
Part II: Oracle9i New Features
First, create a database
9I The tool to create a database is renamed DBCA, or you can easily create a database easier through scripts, such as:
CREATE DATABASE $ {ORACLE_SID}
User sys identified by sys
User system identified by system
Logfile Group 1 ('$ {oracle_base} / oradata / $ {oracle_sid} /redo01.log') SIZE
10m
,
Group 2 ('$ {oracle_base} / oradata / $ {oracle_sid} /redo02.log') size
10m
,
Group 3 ('$ {oracle_base} / oradata / $ {oracle_sid} /redo03.log') SIZE
10m
MaxLogfiles 5
MaxLogmembers 5
MaxLoghistory 1
MaxDatafiles 254
MaxInstances 1
ArchiveLog
Character set zhs16gbk
National Character Set Al16UTF16
DataFile '$ {Oracle_Base} / oradata / $ {oracle_sid} /system01.dbf' size
300M
Default Temporary TableSpace TBSTEMP TEMPFILE
?? '$ {oracle_base} / oraData / $ {oracle_sid} /temp01.dbf' size
500M
undo TableSpace TBSundo DataFile
'$ {Oracle_base} / oradata / $ {oracle_sid} /undo01.dbf' size
500M
;
It features a dedicated rollback and temporary table space, not like Oracle 8i, there is no difference between the rollback and temporary table space and the ordinary table space, which simplifies the configuration, which is also conducive to performance. To pay attention to the specified file keyword of the temporary table space is TempFile instead of a universal DataFile, and the storage options for the temporary table space are determined by the Oracle system. The same rollback table space is also determined by the Oracle system, no need to intervene.
Second, binary parameter file
Oracle 9i can use binary configuration files in $ Oracle_Home / DBS, default is spfile {sid}. O, such as spfileORADB.ORA, supporting the Oracle system process to dynamically adjust parameters without restart, this is required for uninterrupted The system is advantageous. This profile can be created in the Base-of-Book phase.
Create SPFile from Pfile;
oral
Create SPFILE = 'file full name' from pfile = 'file full name'
When the database is started, the spfile {instance name}. ORA file will be found by default, if not, it will look for the pfile file, which is a text file with 8i-compatible init {sid} .ora. Of course, the database can also be initiated by the specified manner. Startup Pfile = 'file full name' or startup spfile = 'file full name'
The biggest benefit using SPFile may be to support dynamic parameters modification, such as
Alter system set parameter_name = new_value scope = memory | SPFILE | BOTH
In this way, the parameters can be modified or to the parameter file by command mode. For 24 * 7 systems, this is a small benefit.
Third, SGA dynamic memory allocation
Oracle9i can be dynamically allocated SGA, but there is also a restriction, but allocate SGA_MAX_SIZE size, within this size, the SGA size can be dynamically allocated, and different SGA memory sizes can be allocated according to different requirements.
Such as
SQL> SHOW Parameter SGA_MAX_SIZE
?
Name ???????????????????? Type ??????? Value
----------------------------------- --- -----------
SGA_MAX_SIZE ????????????????????????? Big Integer 1605044320 ?? // unit is byte
?
SQL> Show parameter db_cache_size
?
Name ???????????????????? Type ??????? Value
----------------------------------- --- --------
DB_Cache_Size ??????????????? big integer 671088640
?
SQL> ALTER SESTEM SET DB_CACHE_SIZE =
500M
Scope = Both;
SYSTEM altered.
Note: DB_CACHE_SIZE is also a newly added parameters of 9i, instead of DB_BLOCK_BUFFERS in previous versions, and can directly set the size of the data buffer through this parameter.
Fourth, PGA automatic management
Prior to 9i, the PGA mainly includes user information, session information, stack information, and sort_area_size, haveh_area_size, bitmap_merge_size, create_bitmap_Area_size, etc. We must set the size of the above pool and monitor it to achieve the best performance.
However, after 9i, we can set the following two parameters to obtain the automatic management of PGA:
a. Workarea_size_policy b. PGA_AGGREGATE_TARGET
If Workarea_SIZE_POLICY = True and PGA_AGGREGATE_TARGET = The specific memory size, Oracle will automatically manage the PGA size, and ignore the settings of the above parameters. Automatically managed PGAs will use their own management methods to get shared use of PGA.
If automatic management is used, there will be the following view to monitor
1. ?????? v $ SQL_WORKAREA
2. ?????? v $ SQL_WORKAREA_ACTIVE
3. ?????? V $ PROCESS CONTAINS New Column (PGA_USED_MEM, PGA_ALLOC_MEM AND PGA_MAX_MEM) 4. ?????? v $ PGASTAT
Oracle 9.2 has added the following view 1. V $ SQL_WORKAREA_HISTOGRAM 2. V $ PGA_TARGET_ADVICE 3. V $ PGA_TARGET_ADVICE_HISTOGRAM
V. Localization of Table Space Management
After 9i, especially 920, if the system table space uses local management, all table space can only use local management mode if the specified dictionary management will display an error. Its specific syntax can be described as follows:
Create TableSpace TBSData DataFile '...' [EXTENT Management Local] [Autoallocate];
For the creation of each expansion block size, the new option is set: Uniform Expansion Block Size (Uniform [Size XXX [K | M]]), can override the Autoallocate option, if you don't add specific Size XXX [K | M], default
1M
This is not necessary to consider the details of the DEFAULT Storage parameters such as Initial, Next, PctinCrease, MaxExtents in Oracle 8i. In fact, these settings of Oracle 8i have nothing to do.
If you do not specify the type of Extent Management, if you use the Default Storage option at the same time, you have the following judgment to be compatible with Oracle8i:
If you use Minimum Extent = initial = next and pctincrease = 0, if it is, Oracle uses UNIFORM options, size = initial; if not, Oracle ignores the specified option, use Autoallocate.
If you do not specify Minimum Extent, Oracle Check whether INITIAL = next and pctincrease = 0, if it is Oracle uses UNIFORM options, size = initial; if not Oracle ignores the specified option, use Autoallocate.
To avoid confusion with Oracle 8i habitual practice, it is recommended to use only Oracle 9i more than a simpler method.
For tablespaces that store a small amount of static data, such as configuration information, etc., can be simply written as:
CREATE TABLESPACE TBSDATA DATAFILE '...';
For tablespaces that must be cared for its expansion block, such as large quantities of records or indexes, simply written as:
Create TableSpace TBSData DataFile '...' Uniform Size
10m
;
Another problem is that the temporary table space of 9i will all adopt local management mode, so the temporary table space will be wrong when Oracle8i is upgraded, because Oracle does not provide downward compatibility.
Creating a syntax for creating a temporary table space is changed in 9i as follows:
Create Temporary TableSpace Tempfile 'file full name'
6. Automatic returning segment management
Before 9i, the return segment is all manual management and monitoring. DBA needs a certain amount of time to manage and monitor the performance of the rollback segment, create a bad or managed back to the segment, will cause a lot of performance bottlenecks. . From Oracle9i, in order to better manage the rollback segments, Oracle, the automatic return band management is used by default. Automatic returning segment management can maximize 8i-famous "snapshot too old" errors, Oracle9i sets automatic return segment management by the following four initial test parameters:
undo_management ??????????????????? String ????? AUTO
Undo_retention ?????????????? integer ???? 10800
Undo_suppress_errors ?????????????????????? false
Undo_tableSpace ??????????????????? String ????? Undotbs1
Undo_management indicates that the return segment management is automated, Oracle recommends automatic mode, if not for the database, do not modify this parameter.
Undo_Retrion indicates the time that the rollback information remains in the rollback segment, the unit is second, the default 3 hours.
Undo_suppress_erroRORS indicates that some error messages do not display, such as the operation of the system returning segment will not display errors, although this operation is not successful.
Undo_tablespace indicates the use of auto-rollback tablespace, and DBA needs to monitor the size of the table.
Another advantage of the automatic returning segment is that you can use Flashback to view the previous data or derived the previous data to prevent some people from a certain extent.
Seven, automatic segment space allocation
Oracle9i introduces the automatic distribution of segment space (ASSM), ASSM's TableSpace is implemented by adding the Segment Space Management Auto clause to the TABLESPACE defined syntax (only table space management). Replacing traditional unidirection list Freelist by using bitmap FreeList, ASSM's TableSpace will automate FreElist, and cancel the ability to specify PCTUSED, FREELISTS, and FREELIST Groups to store parameters.
Note that after the table or index is assigned to this tableSpace, the value of the PCTUSED used for independent objects will be ignored, and Oracle9i uses the bitmap array to automatically manage the TableSpace in the table and the indeed of the Freelist. This next extension clause is outdated for tables and indexes created inside the LMT TableSpace, because they manage them by local TableSpace. However, the initial parameter is still needed, because Oracle cannot know the size of the initial table load in advance. For ASSM, the minimum value of Initial is three blocks.
There are also some controversies for an universal method for Oracle. In large databases, individual object settings bring huge differences in performance and storage.
In the previous version, when there is no multiple freeelist, each Oracle table and the index have a data block in the table of the table, which uses the remaining blocks used by the object and inserts the declaration for any SQL insertion. The new data row created provides data blocks. When the data block within the data buffer cannot be used by another DML transaction lock, the buffer is busy waiting. When you need to insert multiple tasks into the same table, these tasks are forced to wait, while Oracle will assign the remaining block at the same time, one.
After having ASSM, Oracle claims to significantly increase the performance of DML concurrency operation, because (the same) bitmap can be used simultaneously, this eliminates serialization of the remaining space. According to the test results of Oracle, use the bitmap Freelist to eliminate all segmented headers (for resources), can you get a super fast concurrent insertion operation?
references:
http://www.happyit.net
Http://otn.racle.com/documentation/content.html
http://metalink.oracle.com/
EXPERT One-on-one Oracle? [US] THOMAS KYTE Tsinghua University Press
Oracle 8i Web Development Guide [Beauty] Dan Hotka, ET Al. Tsinghua University Press
Oracle 8i DBA Architecture & Administration And Backup & Recovery Guide
???????????????????????????????] dong Stuns Biju Thomas, Electronic Industry Press
Oracle Database Administrator Technical Guide [Beauty] SUMIT SARIN Working House
Oracle 9i Unix Management Manual [US] Donald K.burleson Machinery Industry Press
?
2004
-8-6
Joint Query Union operations can merge multiple queries to merge the results in a result set. UNION operations are displayed: [Table] Query 1Union [all] Query 2 Union ... Example: Return to all suppliers and customers of Brazil and the names and cities SELECT CompanyName, CityFROMSuppliersWHERE Country = 'Brazil'UNIONSELECT CompanyName, CityFROMCustomersWHERE Country =' Brazil 'NOTE: the case of default, UNION clause does not return duplicate records to display all records to be added to the operational requirements UNION ALL option with the query. The same number of fields. However, the field data type does not have to be the same. In each query parameter, you can use the Group By clause or the Having clause to group. To display the returned data in the specified order, you can at the end of the last query Use the Oreer By clause.
If two tables, such as Supply and Custom tables, their column names must be available in the same UNION function, otherwise it will not be used. If the column name is the same, but the data type is different, the Union function can also be executed, but repeated lists J
?
?
When the internal connection, the return of the query result collection is only the line that meets the query condition (WHERE search criteria or Having condition) and the connection condition. When using an external connection, it returns to the query result collection not only contains rows that meet the connection conditions, but also the left table (when connected to the left), the right table (when connected to the right) or two edges (full All data lines in an external connection.
?
For example, two tables Supply and Custom
SQL> SELECT * from Supply;
?
Company
CITY
-------- --------
IBM ????? manhaton
HP ?????? newyork
MS ?????? newyork
?
SQL> SELECT * from Custom;
?
Company
CITY
-------- --------
IBM ????? manhaton
MS ?????? manhaton
Sun ????? Kalifnio
?
SQL> SELECT * from Supply Union Select * from Custom;
?
Company
CITY
-------- --------
HP ?????? newyorkibm ????? manhaton
MS ?????? manhaton
MS ?????? newyork
Sun ????? Kalifnio
?
SQL> SELECT * from Supply S Left Join Custom C on S.comPanyName = C.CompanyName
?
Company
CITY
????
Company
CITY
---------------------- --------
IBM ????? manhaton ibm ????? manhaton
MS ?????? newyork? Ms ?????? manhaton
HP ?????? newyork
SQL> SELECT * from Supply S, Custom C Where S.comPanyName = C.CompanyName ( );
?
Company
CITY
????
Company
CITY
---------------------- --------
HP ?????? newyork
IBM ????? manhaton IBM ????? manhaton
MS ?????? newyork? Ms ?????? manhaton
?
SQL> SELECT * from Supply s Full Outer Join Custom C on S.city = C.CITY;
?
Company
CITY
????
Company
CITY
---------------------- --------
IBM ????? manhaton IBM ????? manhaton
IBM ????? manhaton ms ?????? manhaton
MS ?????? newyork
HP ?????? newyork
????????????????? sun ????? kaalifnio
?
SQL> SELECT * from Supply Cross Join Custom;
?
Company
CITY
????
Company
CITY
---------------------- --------
IBM ????? manhaton ibm ????? manhaton
HP ?????? newyork? IBM ????? manhaton
MS ?????? newyork? IBM ????? manhaton
IBM ????? manhaton ms ?????? manhaton
HP ?????? newyork? Ms ?????? manhaton
MS ?????? newyork? Ms ?????? manhaton
IBM ????? manhaton sun ????? kaalifnio
HP? ????? newyork? Sun ????? kalifnio
MS ?????? newyork? Sun ????? Kalifnio
?
9 lines have been selected.
?
SQL> SELECT * from Supply S inner Join Custom C on S.comPanyName = C.CompanyName
?
Company
CITY
????
Company
CITY
---------------------- --------
IBM ????? manhaton IBM ????? manhaton
MS ?????? newyork? Ms ?????? manhaton
?
