SQL statement parsing Type-hard parsing and soft Parsing

I. Role of Sharedpool: cache SQL statements and execution plans of SQL statements SQL statement execution three steps: parsing (parse) ------ execute (exec) ------ get data (fetc

I. Role of Shared pool: cache SQL statements and execution plans of SQL statements SQL statement execution three steps: parsing (parse) ------ execute (exec) ------ get data (fetc

I. Shared pool function: cache the execution plan of SQL statements and SQL statements
SQL statement execution Step 3: parsing (parse) ------ execute (exec) ------ get data (fetch)

Composition of the shared pool:
Three regions: free, librarycache, and row cache (dictionary cache)
Library cache: cache the execution plans of SQL statements and SQL statements
Dictionary cache: information of the Oracle database (the number of tables in the database, the number of users, the number of columns in the table, the column name, the column data type, and the size of each table) is stored in dictionary.

Data Dictionary example: If you want to know whether table T1 is in the database
1. create table t1 as select * from dba_objects;
2. desc dba_tables; --------------- data dictionary information table
3. select table_name, owner from dba_tables where table_name like 'T1% ';
All data dictionary information can be found in the official document books à reference à dba _ tables

1. Check the librarycache size.
Select * from v $ sgastat a where a. NAME = 'library cache ';
2. free Space size
Select * from v $ sgastat a where a. pool = 'shared pool 'anda. NAME = 'free memory ';
3. row cache space size
Select * from v $ sgastat a where a. NAME = 'row cache ';

Ii. analysis: (hard parse, soft parse)
Hard parsing steps: 1. server process determines whether SQL statement syntax is incorrect
2. Check whether the objects involved in the SQL statement exist (whether the table exists, and so on)
3. Whether the user who executes the SQL statement has the corresponding permissions on the object (System and object permissions)
4. generate an execution plan -------- an SQL statement has multiple execution plans. Select the optimal execution plan from the N execution plan as the SQL Execution Plan -------- consume the most resources.
Soft analysis: without the fourth step above, it is just a regular judgment.

When Will hard parsing occur:
Server process finds the SQL statement in librarycache. If this SQL statement does not exist in library cache, it indicates that this SQL statement and its execution plan are not parsed by ------- in library cache.
Soft resolution --------
Whether hard parse or soft parse, the parsing process uses a lot of database information (permission information, object information, object statistics information ------ dictionary information); that is, the parsing of SQL statements (soft and hard) data Dictionary information is frequently accessed ---------- so the row cache is put together in the shared pool and library cache.

Details of soft and hardware Parsing
Select name, value from v $ sysstat where name like 'parse %'