Sunday, November 4, 2012

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.

Tuesday, February 14, 2012

Why is BTET transaction processing overhead in Teradata?

- BTET makes all the queries running between BT and ET as single transaction . If any of query fails then the entire set of queries will not be committed.

- BTET also has an overhead with locking , since it holds locks on tables till the ET is occured or all the queries have executed successfully

- DDL statements cannot be used  everywhere in BTET processing , but these statements can be given towards the end of BTET transaction.

- Using large number of BTET caused transient Journal to grow and has tendancy for System Restarts