Few tricky questions on Explain plan

Few tricky questions on Explain plan

 
These are some tricky questions asked about explain to see if an candidate has really worked on query tuning and how much he would know about optimizer & explain 

1.         What does ” Pseudo Table” Locks mean?

It is a false lock which is applied on the table to prevent  two users from getting conflicting locks with all-AMP requests.

PE will determine an particular AMP to manage all AMP LOCK requests for given table and Put Pseudo lock on the table.

 To put in Simple terms , its like an University with 10 gates and at a given time you can enter the university through one gate ( or main gate) for security check.

  2.        What is residual condition?

Residual condition means a filter is applied on particular table to limit the number of rows fetched into Spool.

Say for example

Sel * from EMP.Employee where emp_sal > 10000;

 Here , residual condition will act upon to fetch only employees with salary greater than 10000

 3.         Spool “ Last use”

The particular spool file used in the step will be used for last time and the spool space will be released since it is no longer used in further steps of the query execution.