Following are the Performance Engineering Interview Questions Set #3.
Q. 11 How to identify SQL statements consuming the most CPU resources in the Database?
Ans: Fetch/Extract the AWR report for the problematic period, go to the section ‘SQL Statistics’ and then click the ‘SQL Ordered by CPU Time’ link. This will take you to the ‘SQL Ordered by CPU Time’ table which is sorted by High to low CPU % SQL statement. Refer to the top rows to get the information on the highest CPU utilization SQLs.
Q. 12 How to identify the most expensive SQL statements by time in the Database?
Ans: Fetch/Extract the AWR report for the problematic period, go to the section ‘SQL Statistics’ and then click the ‘SQL Ordered by Elapsed Time’ link. This will take you to the ‘SQL Ordered by ElapsedTime’ table which is sorted by High to low elapsed time of the SQL statement. Refer to the top rows to get the information on the highest elapsed time taken by the SQLs.
Q. 13 What is Elapsed Time in the context of SQL in AWR report?
Ans. Elapsed Time represents the total time taken by a SQL statement during the snapshot interval which includes CPU time, wait time, render time and other overheads. The SQL statement with a high elapsed time requires attention for tuning.
Q. 14 Which metric represents I/O operations performed by SQL?
Ans: Buffer Gets
Q. 15 What idea can you get from ‘Buffer Gets’?
Ans: The ‘Buffer Gets’ table indicates the number of logical I/O operations performed by each SQL. A high number of ‘Buffer Gets’ gives an idea that the SQL statement is scanning a large amount of data, possibly due to missing indexes or suboptimal execution plans, leading to increased CPU usage as well.
You may be interested:
- Performance Testing Interview Question
- Apache JMeter Interview Question
- LoadRunner Interview Question
- Performance Testing Quiz