Detailed steps for oracle hard Parsing

Oracle involves the query statement of a table. If it is the first execution, that is, hard parsing, The objects involved in the steps to be executed are as follows: Tables # QueriesPurposeaccess $1 rows $ 10Constraintcolumn-specificdatacdef $3 Constra

Oracle involves the query statement of a table. If it is the first execution, that is, hard parsing, the steps to be executed involve the following objects: tables # Queries Purpose access $1 Permissions used by a dependent object against its parent ccol $10 Constraint column-specific data cdef $3 Constra

Oracle involves the query statement of a table. If it is the first execution, that is, hard parsing, the steps to be executed involve the following objects:

Tables # Queries Purpose

Access $1 Permissions used by a dependent object against its parent

Ccol $10 Constraint column-specific data

Cdef $3 Constraint-specific definition data

Col $1 Table column-specific data -- not always in memory

Dependency $1 Interobject dependencies -- not always in memory

Hist_head $12 Histogram header data

Histgrm $3 Histogram specifications

Icol $6 Index columns

Ind $, ind_stats $1 Indexes, index statistics

Obj $8 Objects -- dictionary cache

Objauth $2 Table authorizations

Seg $7 Mapping of all database segments

Syn $1 Synonyms

Tab $, tab_stats $1 Tables, table statistics

User $2 User definitions

We can see that the hard parsing process executes 59 queries, and the hard parsing resource consumption is quite serious. In the actual production environment, we should avoid hard parsing as much as possible, in addition, many of the data dictionary information involved in the query is not all in the memory. For example, in the annotated section above, the actual query finds that the information of many objects is not stored in the dictionary cache, this information may also be replaced based on the LRU principle of the shared pool.

The following is the execution result of the same statement in different environments. You can also see the differences between soft and hard parsing.

A statement is hard parsed during the first execution.

Select * from ttest where object_id = 1000;

Found through set autotrace traceonly statistics

230 consistent gets

226 physical reads

-- It indicates that there will still be consistent read in the memory while reading the physical data, because the data on the hard disk cannot be directly checked and operated, and must be moved to the memory.

Only alter system flush shared_pool;

298 consistent gets

6 physical reads

-- For hard parsing, some data dictionary information still requires physical read. The table information is not completely in the dictionary cache, and all the data involved in this query is cached in the buffer cache, the largest part of physical reads is avoided, but remember that IO operations consume cpu and IO, while hard parsing occupies cpu and latch memory, basically, one cpu is used in real time. When a large number of CPUs are used, the entire system may be unable to work.

After all caches

230 consistent gets

0 physical reads

-- Soft parsing, no physical read at all

In fact, there is also a soft parsing. The session_cached_cursors parameter value is very important and will not be elaborated here.