Performance Engineering Interview Questions #4

Following are the Performance Engineering Interview Questions Set #4.

Q. 16 What is the significance of the ‘Execution Plan’?

Ans: The execution plan provides details about the data fetching operation. It describes how the data is fetched from the database by the SQL statement. It explains the operations like table scans, index scans, joins etc. along with their order. A suboptimal execution plan may result in excessive I/O, CPU usage and longer execution times.

Q. 17 Consider; in the AWR one of the SQL statements has a high execution count with low elapsed time. What do you think about this scenario?

Ans: Considering this case, it shows that the performance of the individual SQL is good, but the frequency of execution is high and that could impact the overall performance of the Database. We have to think about batching operations, reducing the execution frequency of the SQL or caching. We can also think to optimize the application logic to extra SQL calls which is overhead to the Database.

Q. 18 What is the purpose of bind variables?

Ans. Bind variables promote cursor sharing, reducing hard parses and improving the performance of the Database. The AWR report’s ‘Instance Efficiency Percentages’ section shows the ‘Parse CPU to Parse Elapsed %’ and ‘Soft Parse %’ metrics, indicating parsing efficiency.

Q. 19 What are ‘Physical Reads’ in the context of DB?

Ans: Physical reads indicate the I/O operations during the SQL execution.

Q. 20 What does a high number of ‘Physical Reads’ indicate?

Ans: A high value of ‘Physical Reads’ indicates that the SQL statement is causing disk I/O operations which are slower than memory operations. This could be due to full table scans, lack of indexing or insufficient buffering.