IMPORTANT AND DEFINITE INTERVIEW QUESTION
Q. How does the query execution occur?
1. SQL*plus checks the syntax on client side.
2. If syntax is correct the query is stamped as a valid SQL statement and
encrypted into OCI (Oracle Call Interface) packets and sent via LAN using TCP
to the server.
3. Once the packets reach the server the server process will rebuild the
query and again perform a syntax check on server side.
4. Then if syntax is correct SP will continue execution of the query.
5. The SP will go to the library cache. The L.C. will keep the recently
executed SQL statements along with their execution plan.
6. In the library cache the server process will search from the MRU (Most
Recently Used) end to the LRU (Least Recently Used) end for a match for the
SQL statement. It does this by using a hash algorithm that returns a hash
value. If the hash value of the query we have written matches with that of the
query in L.C. Then SP need not generate an execution plan (soft parsing) but
if no match is found then SP has to proceed with the generation of execution
plan (hard parsing).
7. Parsing is the process undertaken by Oracle to generate an execution plan.
8. The first step in parsing involves performing a semantic check. This is
nothing but check for the existence of the obj and its structure in the
database.
9. This check is done by SP in the data dictionary cache. Here SP will ask
for the definition of the object, if already available within the DDC , SP
will process the check. If not available then SP will retrieve the required
information from the system tablespace.
10. After this SP will approach the optimizer, who will read the SQL statement
and generate the execution plan of the query.
11. After generation of the e-plan's the SP will pick the best e-plan and go
to the L.C.
12. SP will keep the e-plan in the L.C. Along with the original SQL text.
13. At this point in time the parsing ends and the execution of the SQL
statement will begin.
14. SP will then go to the database cache and checks whether the data required
by the query is already available or not in the cache.
15. If available that data can be returned to the client else it brings the
data from the database files.
16. If sorting and filtering is required by the query then the PGA is utilized
along with the temporary tablespace for performing sort run.
17. After sort run the data is returned to the client and SQL*plus client will
show the data to the users.
Q. How does the query execution occur?
1. SQL*plus checks the syntax on client side.
2. If syntax is correct the query is stamped as a valid SQL statement and
encrypted into OCI (Oracle Call Interface) packets and sent via LAN using TCP
to the server.
3. Once the packets reach the server the server process will rebuild the
query and again perform a syntax check on server side.
4. Then if syntax is correct SP will continue execution of the query.
5. The SP will go to the library cache. The L.C. will keep the recently
executed SQL statements along with their execution plan.
6. In the library cache the server process will search from the MRU (Most
Recently Used) end to the LRU (Least Recently Used) end for a match for the
SQL statement. It does this by using a hash algorithm that returns a hash
value. If the hash value of the query we have written matches with that of the
query in L.C. Then SP need not generate an execution plan (soft parsing) but
if no match is found then SP has to proceed with the generation of execution
plan (hard parsing).
7. Parsing is the process undertaken by Oracle to generate an execution plan.
8. The first step in parsing involves performing a semantic check. This is
nothing but check for the existence of the obj and its structure in the
database.
9. This check is done by SP in the data dictionary cache. Here SP will ask
for the definition of the object, if already available within the DDC , SP
will process the check. If not available then SP will retrieve the required
information from the system tablespace.
10. After this SP will approach the optimizer, who will read the SQL statement
and generate the execution plan of the query.
11. After generation of the e-plan's the SP will pick the best e-plan and go
to the L.C.
12. SP will keep the e-plan in the L.C. Along with the original SQL text.
13. At this point in time the parsing ends and the execution of the SQL
statement will begin.
14. SP will then go to the database cache and checks whether the data required
by the query is already available or not in the cache.
15. If available that data can be returned to the client else it brings the
data from the database files.
16. If sorting and filtering is required by the query then the PGA is utilized
along with the temporary tablespace for performing sort run.
17. After sort run the data is returned to the client and SQL*plus client will
show the data to the users.
No comments:
Post a Comment