Definition of SQL statement execution（parse/execute/fetch)

1) parse - pretty well defined, that is prepareStatement - we do a soft or hard parse, compile the statement, figure out how to execute it.  2) execute - we OPEN the statement. For an update, for a delete, for an insert - that would be it, when you OPEN the statement, we execute it. All of the work happens here.  for select it is more complex. Most selects will do ZERO work during the execute. All we are doing is opening the cursor - the cursor is a pointer to the space in the shared pool where the plan is, your bind variable values, the SCN that represents the "as of" time for your query - in short  the cursor at this point is your context, your virtual machine state, think of the SQL plan as if it were bytecode (it is) executed as a program (it is) in a virtual machine (it is). The cursor is your instruction pointer (where are you in the execution of this statement), your state (like registers), etc. Normally, a select does nothing here - it just "gets ready to rock and roll, the program is ready to go, but not yet really started".  However, there are exceptions to everything - turn on trace and do a select * from scott.emp FOR UPDATE. That is a select, but it is also an update. You would see work done during the execute as well as the fetch phase. The work done during the execute was that of going out and touching every row and locking it. The work done during the fetch phase was that of going out and retrieving the data back to the client.  3) fetch - this is where we see almost all of the work for SELECTS (and nothing really for the other DMLS as you do not fetch from an update).  There are two ways a SELECT might be processed. What I call a "quick return query" and a "slow return query"

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1855700000346846274