Oracle database Query principle

When it comes to query theory, you first need to know the architecture of Oracle. The database service consists of two parts: an instance and a database file. The examples section includes the SGA (System Global area) and the PGA (Progam Global Area) and the background process composition. Examples include: data files, control files, log files, parameter files, and so on.

When the user writes the SQL statement to start the query, the first entry to the PGA (PGA function is to save the connection information and permission information for preprocessing), the SQL statement at the PGA will generate a unique hash value (similar to the identity card). Then enter into the SGA's shared pool, in the shared pool if there is the same hash value, then the SQL statement parsing directly. If you do not have the same hash value, then check whether the SQL syntax is correct and whether the semantics are correct or not, and then generate a hash value to be saved in the shared pool before the SQL statement is parsed. When SQL is parsed, the optimizer analyzes such things as whether to go to an index or not, and finally chooses the most efficient route to save the execution plan. The next step is to go to the data buffer to fetch the data. If the buffer has the appropriate data at this point, then the data is taken and the query ends. If the data buffer has no data, it is troublesome to get the data files on the disk to read the data. The disk IO query time is greatly increased. If you find it, return the query results, and empty if you don't find it.

Oracle database design is so complex to optimize query efficiency, try to make the database less work! Makes us more efficient when we call the database.

Oracle database Query principle