Hi All,
Before I started to learning Oracle in depth. I was always wondering what happen when we issue a simple SQL statement. I did an investigation and read several articles about it. However, there is not much more resource about this subject. The below link is one of the only source about this subject.
https://docs.oracle.com/database/121/CNCPT/sqllangu.htm#CNCPT216
I also read some threads in Tom Kyte's web site. I also analyzed Jonathan Lewis's ORACLE CORE book. However, this subject is superficial for this book. As far as I see ORACLE CORE contains very interesting and advance topics. I am so pleasured in order to analze it. It is absolutely a fantastic book for us.
Let me recap from what I learnt and please correct me if I am wrong. Let's say that we issue the following SQL query in SQL*PLUS.
select * from employees e join departments d
on e.department_id = d.department_id;
Parsing happen:
*Syntax Check
*Semantic Check
*Privileges Check
*Shared Pool Check: Every SQL query has a hash value (SQL Id, V$SQL) and this value stores in the SGA (Shared Pool). If another SQL queryhas been issued before out query, optimization and row source generation parts don't have to work for this SQL query. Because execution plan etc. has already been prepared for the query. This is called Soft Parse. If the hash value cannot be found in the Shared Pool. So, optimization and row source generation parts should work. This is called Hard Parse.
*Optimization & Row Source generation: An execution plan and Row source tree is prepared for the query. (Data Dictionary is also used in this part)
Execution:
In the execution part PGA(Program Global Area) comes into play. If we use dedicated server UGA (User Global Area) is stored in the PGA. If we use shared server UGA is stored in the SGA.
There are some parts of PGA.
* A cursor is assigned to the our query in order to control and manage the data. (I am just guessing)
The important part is SQL Work Area: The main part ofg PGA. Sorting, Hash and Merge operations perform in this part. In other words, there is a join opration in the above query. If this join contains HASH operation, this operation performs in this area. If the size of this area is less, then Oracle give more space to the PGA or the operation is going to perform in the Disk (Temp space).
Session Area and Private SQL Area is another parts of PGA. Also these areas are composed of UGA (Package, session and bind variables stores in UGA). Normally, (If we don't use Parallel Processing) the data blocks read from SGA (from Buffer Cache) into PGA (Private SQL Area). If the data blocks are not find in the SGA then they will be retrieved from DISK into SGA.
DISK => SGA => PGA=> Client
In our scenario, SQL*PLUS is the client. After the CURSOR (Tom Kyte says that it is like a FILE HANDLE or POINTER) managing the row sources, then the result set is fetch to the client.
So far, please correct me if I said something wrong.
OPEN CURSOR
PARSE
LOOP
EXECUTE
FETCH
EXIT WHEN NO ROWS FOUND
END LOOP
CLOSE CURSOR
My questions are about EXECUTE and FETCH sections.
q1) How does CONSISTENT GETS + DB BLOCK GETS and FETCH interacts with eachother?
q2) How a result set is sent to the client BEFORE the whole operation finished in the SQL WORK AREA?
or let's say that our query gets 1 million rows. and our PGA size is not available for this operation. As far as I know, Oracle do this process cut the data blocks into small pieces, So how come Oracle sent data to the client before all operations finished (HASH, SORT etc.) I am just trying to figure out this?
In addition, It has been said that some part of data is sent before the other part of them is going to be processed. Somebody please please explain me this?
q3) optimizer_mode = ALL_ROWS, FIRST_ROWS How these settings effect the PGA?
q4) Is there any article which explains to this part (FETCH + EXECUTION) in details?
Thanks in advanced.