SQL Statement Execution Phases:
-----------------------------------------
When Application queries a request, it creates a user process and gives to server process through dispatcher.
server process places sql query in shared pool - sql area
Insert Statement:
In shared pool it goes to 3 stages Parsing, Execution and Fetching.
Parsing:
it performs syntactical, semantic checks
if success it will generate sql_id and hash value for the sql query and will be stored in ShardPool --> Library Cache.
if same hash value already exists in Library Cache then
it try to get the data from Database Buffer Cache is called Soft Parsing is also called Cache Hit.
otherwise it is called Cache Miss then it goes for Hard Parsing.
Execution:
Optimization:
In hard parsing it performs shared pool check.
Here optimizer evaluates multiple plans depending on
Optimizer statistics, Access paths, Optimizer hints
expressions, conditions and constraints.
Fetching:
Chooses lowest cost execution plan will be executed by server process and gets the data from datafile and places it in the database buffer cache.
finally the result set will be displayed in cursor area.
Update Statement:
If it is update command then copy of a result set will be stored into the undo segments for roll-back and copy of a database buffer cache will be modified and marked as dirty buffers.
-----------------------------------------
When Application queries a request, it creates a user process and gives to server process through dispatcher.
server process places sql query in shared pool - sql area
Insert Statement:
In shared pool it goes to 3 stages Parsing, Execution and Fetching.
Parsing:
it performs syntactical, semantic checks
if success it will generate sql_id and hash value for the sql query and will be stored in ShardPool --> Library Cache.
if same hash value already exists in Library Cache then
it try to get the data from Database Buffer Cache is called Soft Parsing is also called Cache Hit.
otherwise it is called Cache Miss then it goes for Hard Parsing.
Execution:
Optimization:
In hard parsing it performs shared pool check.
Here optimizer evaluates multiple plans depending on
Optimizer statistics, Access paths, Optimizer hints
expressions, conditions and constraints.
Fetching:
Chooses lowest cost execution plan will be executed by server process and gets the data from datafile and places it in the database buffer cache.
finally the result set will be displayed in cursor area.
Update Statement:
If it is update command then copy of a result set will be stored into the undo segments for roll-back and copy of a database buffer cache will be modified and marked as dirty buffers.
Server process writes all redos to redo log buffers.
When commit is issued all the redo entries to that transaction will be written to online redo logfiles by LGWR.
When checkpoint (log switch) occurs (CKPT) will signals the db writer (DBWr). db writer writes all related dirty blocks to the datafiles using logfiles and checkpoint will update the latest scn to datafile header and controlfile.
finally result will be displayed to cursor area.
Insert Statement:
If it is insert command then values are stored in database buffer cache and the pointer for the table is stored in the undo tablespace.
finally result will be displayed to cursor area.
Insert Statement:
If it is insert command then values are stored in database buffer cache and the pointer for the table is stored in the undo tablespace.
When commit is issued log writer writes redo entries to redo logfiles
when checkpoint occurs (CKPT) will signals the db writer (DBWr). db writer writes all related blocks to the datafile
and checkpoint will update the latest scn to datafile header and controlfile.
For DDL:
issue a COMMIT before executing Create statement.
Verify privileges
Verify which tablespace
Verify tablespace quotas is not exceed.
Verify same object not exist same schema.
Create a table into the data dictionary.
issue a commit or roll-back.
and checkpoint will update the latest scn to datafile header and controlfile.
For DDL:
issue a COMMIT before executing Create statement.
Verify privileges
Verify which tablespace
Verify tablespace quotas is not exceed.
Verify same object not exist same schema.
Create a table into the data dictionary.
issue a commit or roll-back.
