Oracle Performance Tuning Mock Interview

Master 30 Oracle Performance Tuning interview questions covering SQL optimization, indexing strategies, and execution plans.

Question 25 of 30

Is it a problem when you see that disk sorts are high when you run the v$sysstat command, and how would you correct this?

Why the Interviewer Asks This Question
How to Answer
Example Answer
Community Answers

William Swansen

William Swansen has worked in the employment assistance realm since 2007. He is an author, job search strategist, and career advisor who helps individuals worldwide and in various professions to find their ideal careers.

The interviewer wants to make sure that you can discern whether there is an issue or not when you're managing the Oracle database, and specific events occur. This question is structured to first describe a scenario and then ask you whether what you're seeing indicates whether there is a problem. If there is, the interviewer wants you to explain how to correct it. By asking the question in this manner, the interviewer can both confirm your knowledge of Oracle database procedures and your ability to fix problems when they occur.

William Swansen

As an experienced Oracle database administrator, you should be capable of recognizing commands, knowing what their output indicates, and addressing any issues which may occur. You are likely to be asked many questions similar to this during the interview. The first part of your answer should describe whether the output from a command indicates an issue are not. If there is no issue, you are done. If there is an issue, you should proceed to describe how to resolve it to the interviewer.

William Swansen

"If after running the vsysstat command you see that the disk sorts are high, it indicates that there is an issue with the database. To resolve this, you would tune the sort area parameters in the initialization files. It is best to start with the primary sort parameter SORT_AREA_SIZE."

Interview Coach

Jaymie

A real coach, not AI. I read every answer myself and write back with personalized feedback.

Typically responds within 24 hours.

0 - Character Count

Previous Question Next Question

30Oracle Performance Tuning Interview Questions & Answers

General

1.In your opinion, what is performance tuning?

General

2.What are some of the different types of tuning you can perform in an Oracle environment?

General

3.What are some of the elements you can tune to optimize an Oracle database?

General

4.In the context of an Oracle database, what does an optimizer do?

Operational

5.Can you discuss the different types of optimizers you use?

Technical

6.Describe the init parameter used to make use of an Oracle database optimizer?

General

7.In your opinion, which optimizer is the best one for most Oracle database tuning?

Operational

8.What are some of the prerequisites needed to use an Oracle database optimizer?

Technical

9.What is the most direct way to collect statistics on a database table?

Technical

10.What is the difference between using compute and estimate?

Technical

11.Which optimizer mode does the data dictionary follow?

Technical

12.What command can you use if you want to see the statistics of an Oracle table?

Operational

13.What is the easiest way to collect statistics of a single user in Oracle Apps?

Operational

14.What commands would you use if you didn't know what SQL is currently being used by a session?

Operational

15.Can you describe the purpose of an Oracle SQL execution plan?

Technical

16.What would you do if you needed to get the index of a table and identify which column the index is on?

Operational

17.What method would you use if you want to see the trace files in an Oracle database?

Technical

18.What types of wait events can be tuned to improve the performance of an Oracle database?

Technical

19.If the database tablespace has a table with 40 extents, does it need to be tuned? What other issues can impact this?

Technical

20.How can you assure the appropriate separation of the segments of the tablespaces when installing an Oracle database?

Technical

21.What should you check first if you see multiple fragments in the SYSTEM tablespace?

Technical

22.How would you know that the SHARED_POOL_SIZE parameter needs to be increased?

Operational

23.What guidelines do you typically follow when sizing the database block size and multi-block read for an application that does a lot of full-table scans?

Technical

24.Can you explain the use of TKPROF and the initialization parameter which needs to be used to get full TKPROF output?

Technical

25.Is it a problem when you see that disk sorts are high when you run the v$sysstat command, and how would you correct this?

Technical

26.When should copy latches be increased, and what parameters are used to do this?

Technical

27.Where do you find the list of all the initialization parameters for your instance, and how do you know if they are the default settings?

Technical

28.Can you describe the hit ratio in the context of database buffers? Also, do you use the instantaneous or cumulative hit ratio for tuning?

Technical

29.Please discuss row chaining, how it happens, and how it can be reduced or corrected.

Operational

Oracle Performance Tuning Mock Interview

Is it a problem when you see that disk sorts are high when you run the v$sysstat command, and how would you correct this?

30Oracle Performance Tuning Interview Questions & Answers

1.In your opinion, what is performance tuning?

2.What are some of the different types of tuning you can perform in an Oracle environment?

3.What are some of the elements you can tune to optimize an Oracle database?

4.In the context of an Oracle database, what does an optimizer do?

5.Can you discuss the different types of optimizers you use?

6.Describe the init parameter used to make use of an Oracle database optimizer?

7.In your opinion, which optimizer is the best one for most Oracle database tuning?

8.What are some of the prerequisites needed to use an Oracle database optimizer?

9.What is the most direct way to collect statistics on a database table?

10.What is the difference between using compute and estimate?

11.Which optimizer mode does the data dictionary follow?

12.What command can you use if you want to see the statistics of an Oracle table?

13.What is the easiest way to collect statistics of a single user in Oracle Apps?

14.What commands would you use if you didn't know what SQL is currently being used by a session?

15.Can you describe the purpose of an Oracle SQL execution plan?

16.What would you do if you needed to get the index of a table and identify which column the index is on?

17.What method would you use if you want to see the trace files in an Oracle database?

18.What types of wait events can be tuned to improve the performance of an Oracle database?

19.If the database tablespace has a table with 40 extents, does it need to be tuned? What other issues can impact this?

20.How can you assure the appropriate separation of the segments of the tablespaces when installing an Oracle database?

21.What should you check first if you see multiple fragments in the SYSTEM tablespace?

22.How would you know that the SHARED_POOL_SIZE parameter needs to be increased?

23.What guidelines do you typically follow when sizing the database block size and multi-block read for an application that does a lot of full-table scans?

24.Can you explain the use of TKPROF and the initialization parameter which needs to be used to get full TKPROF output?

25.Is it a problem when you see that disk sorts are high when you run the v$sysstat command, and how would you correct this?

26.When should copy latches be increased, and what parameters are used to do this?

27.Where do you find the list of all the initialization parameters for your instance, and how do you know if they are the default settings?

28.Can you describe the hit ratio in the context of database buffers? Also, do you use the instantaneous or cumulative hit ratio for tuning?

29.Please discuss row chaining, how it happens, and how it can be reduced or corrected.

30.The ESTAT events report indicates that you are getting busy buffer waits. What method can you use to find out what is causing this?