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

1. Oracle Concepts Guide explains every part of SQL language and database in detail. People who are advised to use Oracle should read "Oracle Concepts Guide".

2. Shared pool and database cache

2.1 SGA shared pool: sharing SQL/PL/SQL code between users

What the shared pool stores: the system parameters used by SQL statements and Oracle (in an area called the data dictionary cache) Oracle stores almost everything you can think of in the shared pool.

The area of memory allocated to the shared pool is limited, so when a new statement is executed, the previously loaded statement is not

Can be kept in it for a long time. There is an algorithm called Least Recently Used, LRU, which can be used to manage objects in a shared pool.

2.2 Library cache

Parsing: the library cache is mainly an area where statements that have been parsed exist.

Soft parsing: Oracle retrieves the previously parsed information and reuses it.

Hard parsing: if the statement has not been executed before, Oracle will do all the work to generate the execution plan for the current statement and store it in the cache for future reuse.

Every time you do hard parsing, Oralce must collect all of its information before it can actually execute the statement. Query the data dictionary many times.

* what does hard parsing do?

Open the tracking data. The extended SQL trace captures every activity that occurs during execution, and you can see the statements executed and each statement that Oracle must execute.

2.3 identical statements

By querying the v$sql view, you can see the sql statements that have been executed in the library cache:

Select sql_text, sql_id, child_number, hash_value, executions from v$sql where upper (sql_text) like'% EMPLOYEESS%'

How to check that the sql statement is identical, oracle first converts the string to a hash value. This hash value is used as a keyword for the statement to be stored in the library cache. Compare the hash values to find a match.

Any difference can lead to a difference in hash values, which is why it is so important to use bound variables instead of constants in your sql statements. When using bound variables, Oracle can still share this statement even if you change the value of the bound variable.

Before fetching any information in the library cache, Oracle gets a latch, and all other paintings have to wait until the latch is released before they can get the latch done.

Mutex, a serialized component that prevents multiple threads from accessing a shared structure at the same time.

Compared with the latch, the biggest advantage of mutex is that it takes up less memory and can be released quickly.

It is important to note that syntax parsing still requires the use of library cache latches. It is critical to make your code use less latches (that is, hard parsing).

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.