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

"Some of the different types of tuning I perform on an Oracle database to optimize its performance include CPU and memory tuning, optimizing the IO functionality, tuning the applications, and finally, tuning the database functions. By addressing each of these areas, I ensure that the system is balanced and performing as well as possible."

Most of the interviewer's questions are meant to determine your qualifications for the job you are interviewing for. They will ask a range of questions to determine your knowledge, experience, and expertise related to the position. By asking about the different types of tuning you can perform, they will understand your knowledge and a description of the experience you have in this area.

When preparing for an interview, it is important that you research the organization, the job, and, if possible, the interviewer. Knowing as much as you can about these three areas will allow you to prepare yourself to answer the questions they are likely to ask. While you could never be 100% certain of what questions they will use during the interview, the more research you perform, the better you will be prepared.

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

"The main two elements I tune when optimizing an oracle database include hit ratios and wait for events."

As the interview progresses, the interviewer will continue to ask you questions about how you go about doing your job. These are known as operational questions. Operational questions usually ask you to describe the steps you take or the processes you use to perform in this role. Interviewers expect you to answer operational questions briefly and directly, describing how you accomplish the task you are being asked about.

While this question seems similar to what you asked earlier in the interview, it is distinctively different. Differentiating questions during the interview is a key skill you should acquire when looking for a job. You need to completely understand the question before providing the answer the interviewer is looking for.

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

"In the context of oracle database tuning, an optimizer is a mechanism that will create the execution plan of an SQL statement used to locate the data within the database which the user needs."

When asking you questions during an interview, the interviewer will use terminology common to the position to which you are applying. You are expected to be both familiar with the terminology and be able to respond to the question appropriately. Interviewers use this question format to confirm their knowledge of the profession and collect information asked for in their questions.

Questions about the technology, processes, and procedures used in a job are known as technical questions. Like operational questions, interviewers use technical questions to confirm your knowledge and experience related to the job. When responding to a technical question, you should keep your answer brief and to the point and only provide the information the interviewer is asking you about. They will ask a follow-up question if they need additional information.

In your opinion, what is performance tuning?

"In my opinion, performance tuning of Oracle databases involves making optimal use of the system using existing resources. This will improve the database's performance and make it more efficient since no additional resources were added. It is important to understand how the database would be used when optimizing its functions."

The interviewer will ask you this question early in the interview to begin the discussion, learn more about you, and collect information they can use later in the interview. By asking your opinion about this topic, they are also interested in your communication style. They want to see how clearly you express yourself whether you can discuss complex technical topics in simple, easy-to-understand language.

When formulating your answer to this question, you should be honest and state your opinion about the definition of performance tuning in your work. There is no right or wrong answer to this question, so focus on your communication style. Keep your answer brief and to the point, and don't elaborate too much on your definition of performance tuning.

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

"When tuning an Oracle database, I prefer to use the CBO or Cost-Based Optimization methodology. This is less restrictive than RBO and typically results in the most efficient Oracle DBMS. I always have the option of lawyering on the RBO if I believe it will enhance the results are obtained from the CBO."

It is not uncommon for interviewers to ask for your opinion, even if they are asking an operational or technical question. In jobs like this one, formal processes and procedures can be arbitrary and may differ depending on the individual. The interviewer will ask the question to discover which processes you prefer and whether they are similar to those currently used by the interviewer's organization.

When asked for your opinion about a technical topic or operational procedure, you should answer honestly and truthfully. You should also be prepared for a follow-up question requesting that you justify your position on the topic, either through your experience or with commonly available data and statistics. Trying to artificially align your opinion of the topic with that of the organization would be a mistake.

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

When the ESTAT events report shows that you are getting buffer busy waits, it normally indicates that there is contention in redo, rollback, or data blocks. When this occurs, I check the v$waitstat view to see what may be causing the problem. I look at the value of the 'count' column to see where the problem is and the 'class' column to see what may be causing it. UNDO indicates the issue is related to the rollback segments, and DATA tells me it is in the database buffers.

The interviewer begins this question by making a statement about an event and what it indicates. They then ask a question related to the statement. Describing an issue followed by asking a question is a common format used by interviewers. Normally it is pretty straightforward, but they can also use this format to mislead you. If the scenario is incorrect or not related to the question, they usually expect you to correct them and then provide the appropriate information.

While it is sometimes intimidating to correct the interviewer, it is okay to do this if you are sure that they have made an error, either intentionally or by mistake. If you elect to correct their statement, try to do so in a diplomatic manner to avoid insulting them in case the misstatement was made in error. Indicating that you know something is wrong and how to correct it will demonstrate your qualifications for this position.

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?

"Oracle normally reads blocks in 64k chunks. Therefore, when sizing database block sizes and multi-block reads for an application, these should have a product equal to 64 or a multiple of 64."

Most technical professions involve standards, best practices, and guidelines used to perform the job. This ensures that the actions taken by professionals in this role will be the same in response to similar situations. Interviewers are likely to question you about these throughout the interview to confirm your familiarity with them. They also want to make sure you follow the guidelines and best practices since this makes your actions more effective and efficient.

Knowing about the industry standards, guidelines, and best practices is a critical requirement of a technical professional. You should both be aware of these and be able to discuss them during the interview. Demonstrating that you follow industry standards and guidelines will enhance your chances of getting this job.

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

"The method I would use to see the trace files in an Oracle database would be to invoke the command tkprof. The complete format for this command is--tkprof allllle.trc llkld.txt."

This is another question about what command you would use in a specific scenario or situation. However, the interviewer has formatted this question as a response to a situation that you encounter in the course of your work. The interviewer may format questions differently to make the interview interesting, determine how you respond to different formats of questions, and require you to be creative when responding to their questions. No two interviewers are the same, and therefore their questions will vary.

No matter how the interviewer asks the question, it is your responsibility to discern what information they are looking for. This will help you formulate your response and provide the interviewer with the information they expect. If you don't understand the question or need clarification, it is okay to ask the interviewer a question or two. The interviewer appreciates it when the candidate asks a question. However, overdoing this may signal to the interviewer that you are not as familiar with the topics as you need to be to perform this job.

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

"The purpose of an oracle SQL execution plan is to create a road map of how Oracle DBAs execute SQL. This helps Oracle administrators structure the database, create the tables, and execute SQLs to provide the information and data needed by the users to perform their jobs."

While you are interviewing for a specific job that involves optimizing an Oracle database, you're likely to be asked questions about all the functions and components of the DBMS. Interviewers ask these types of questions to confirm that you have a complete understanding of the Oracle environment and can use all of the commands, variables, and other items within Oracle to tune and optimize the database. The interviewer may also ask you questions about related topics such as applications, storage, memory, and other components of a system that impact Oracle's performance.

It is virtually impossible to know every item, component, command, and issue impacting an Oracle database's performance. Interviewers know this and will not expect you to know everything. However, you are expected to know the most important things, the common issues you will encounter, and the commands used to execute common tasks required of an Oracle administrator. Reviewing these before the interview will help prepare you for the majority of questions you will be asked. It is perfectly acceptable to admit that you do not know an answer to an interviewer's question as long as you can describe how you would locate the information needed to perform the task they describe.

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

"There are two commands you can use if you don't know what SQL is currently being used by a session. These are v$sql and v$sql_area. v$sql stores statistics about each child version of a SQL statement stored in the Shared Pool, while v$sqlarea summarizes these statistics per sqlid."

As the interview progresses, the interviewer will continue to ask you about the commands you use to accomplish a task in this role. This could be a question that suggests that there are multiple answers since the interviewer used the plural form of commands. Interviewers will often give you hints and the content and the format of the questions they ask.

It is critically important that you carefully listen to the interviewer's entire question before beginning to formulate your answer or begin to respond. This will ensure that you understand the question and are providing the information the interviewer expects in your response. Suppose you do not understand the question or aren't clear about what the interviewer is asking. In that case, it is appropriate to ask a clarifying question or begin your answer with a clarifying statement.

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

"The easiest way to collect statistics of a single user within Oracle Apps is to use the command - fnd_stats package."

Once the interviewer begins to present cases or ask you for the commands used in this job, they are likely to continue asking these questions during this part of the interview. Interviewers like to structure interviews by asking general questions early, progress to technical and operational questions, and then move on to cases or practical questions. They may conclude the interview by asking you behavioral questions that present scenarios in which skills such as collaboration, innovation, conflict resolution, and other soft skills are explored.

You have two options when asked to resolve a case or provide a command used to accomplish a task. The first is to answer the question directly without embellishment or additional details. The other is to provide some background to the interviewer, which puts the command you are discussing into context and demonstrates your complete understanding of the topic. You can use both of these formats during the same interview, alternating between them as is appropriate.

Can you discuss the different types of optimizers you use?

"There are two main types of optimizers I use to tune in oracle database. The first is a Rule-Based Optimizer or RBO. The other is a CBO, which is a Cost-Based Optimizer. Which optimizer I use is based on the guidance provided by the database project stakeholders and the objective of the optimization. It is not unusual to use elements of both the RBO and the CBO when optimizing a database."

This question can either stand by itself be a follow-up to a question you were asked earlier during the interview. Interviewers will typically ask follow-up questions to clarify your previous answer or collect additional information about the topic. Interviewers may ask follow-up questions at any time during the interview, but typically they immediately follow the original question.

When you recognize a question as a follow-up, quickly review the previous questions and your answers to identify the topic you are being asked about and refresh yourself on how you originally responded to the interviewer's question. Knowing this will enable you to expand on your original answer or answer similarly. Interviewers will appreciate consistency between your original answer and response to this follow-up question.

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

"Before designating the optimization methodology I will use to tune the Oracle database, I first need to collect some initial information. This includes the stakeholder's objective when using the database and the statistics of a database object. This information enables me to write the init parameter needed to set the optimizer mode."

The interviewer continues to ask you different questions about a similar topic, in this case, database optimization. They will often do this if the topic is a specific issue within the organization or they're looking for a solution in this area. Organizations hire individuals who can help them solve problems or optimize their operations. An easy way to remember this is that organizations hire people who can help them make money, save money, or save time.

When you recognize that the interviewer is asking multiple questions about the same topic, it is an indication that they have issues in this area or need to address them by hiring someone in this position. Once you're aware of this, you should expand your answers about the topic to demonstrate your expertise in this area. Hopefully, your research before the interview indicated that this topic was important to the organization, so you've prepared answers to questions you are anticipating.

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

"If I were to observe multiple fragments in the SYSTEM tablespace, I would ensure that database users didn't set the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment. I could accomplish this by checking the DBA_USERS view. Once I confirmed that this was the case, I would reset the SYSTEM tablespace properly."

While the interviewer asks you this question directly, it could also have been presented as a case during the interview. If asked in the form of a question, they expect you to compose a brief, concise response. If presented as a case, they expect you to demonstrate how you would perform this task or resolve the issue. This may involve using the appropriate commands, taking action, or creating code. It will also likely involve discussion of your response to the case, including alternative ways you could have resolved it.

If you are asked a question about a specific scenario and how to resolve it, provide the interviewer with a brief, direct response to their question. If you are presented with a case, first analyze what you are being asked to do and then formulate a plan to address the interviewer's scenario. Be prepared to justify your response and discuss alternative ways you could have resolved the situation.

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

"Row chaining is an issue related to the rows in a table within an Oracle database. It occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value. The row now won't fit in the remaining block space, resulting in the row chaining to another block. By setting the storage parameters on the table to appropriate values, you reduce the chance of this occurring. Doing this after the fact will also correct the issue. It can also be corrected by exporting and importing the affected table."

This is another hybrid technical and operational question asking you to describe terminology used in this profession and then discuss how to address issues related to it. Interviewers like to ask questions like this because it indicates both your knowledge and your ability to perform on the job. It is common for interviewers to combine technical questions asking for a definition with operational questions asking how you would correct a problem. This is aligned with what actually happens on the job since you first need to recognize an issue and then know how to resolve it.

Since this is a two-part question, the best way to respond is to begin by defining the technical term you are being asked about. Once you've done this, you can then describe how to correct the issue associated with the term. Since this is how work is normally done, you should be able to easily structure the response in your mind before presenting it to the interviewer. You can use the acronym CAR to help you remember this. Cause, Affect, Resolution.

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

"In the context of database buffers, the hit ratio provides information about how many times the database could read a value from the buffers instead of how many times it had to re-read a data value from the disks. The higher the percentage of reads from the buffers, the better the database performs. Anything less than 80% buffer reads indicates that there is a problem. A hit ratio of existing parameters is a cumulative value since the database started. It is not as accurate as the instantaneous hit ratio, which compares pairs of readings based on a designated time span. I prefer the instantaneous hit ratio for tuning since it provides more valuable data by indicating what the instance is doing over the time it was generated."

This is another two-part question. The first part asks you what information the hit ratio provides in a specific context. The second is an operational question asking how you go about tuning the database based on the hit ratio information. The interviewer is testing both your knowledge of the technology and your ability to complete the operation needed to optimize the database.

When asked a two-part question, you should respond similarly. Start by answering the first part, then answer the second part of the question. As with any technical or operational question, your answer should be brief and to the point. You should also anticipate follow-up questions from the interviewer.

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?

"Oracle stores the initialization parameters in the init.ora file. Examining this file closely will indicate if the parameters were set manually or by default. The v$parameter view will display all the parameters, their value, and whether or not the current value is the default value."

The interviewer will ask you questions about the initialization parameters throughout the interview. This is because this is one of the key components of an Oracle database that enables it to be tuned and optimized. They will ask you where you can find the list of the initialization parameters to ensure that you are qualified to identify and then modify these. Asking about how you would know if the initialization parameters are the default settings provides the interviewer with a gauge of your expertise.

Knowing where to find a list of initialization parameters and being able to determine if they are the default settings is critical for this role. As an experienced Oracle database administrator, you should be able to do this without any hesitation. The key to answering this question is to make sure you respond to both parts; where to find the parameters and how to confirm they are the defaults.

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

"When you see a 'redo copy' in latch hit ratio, you know there is excessive contention for the copy latches. To fix this, you can increase copy latches. This is done by invoking the initialization parameter LOG_SIMULTANEOUS_COPIES to increase the copy latches to twice the number of CPUs available in the system."

By asking the question in this manner, the interviewer has made several assumptions. First, they assume you know what a copy latch is. They also assume that you know when these should be increased. Finally, they hope to confirm that you know the parameters used to accomplish this. While this is a multipart question, its primary purpose is to explore your knowledge and capabilities related to Oracle database tuning.

You can answer this question by first stating what you look for to confirm that the copy latches need to be increased. The next step is to describe how you would do this. Make sure to include the format of the initialization parameter you would use to address this issue. As with any technical question, you should be prepared for follow-up with the interviewer will use to explore this issue in more depth.

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?

"If after running the v$sysstat 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."

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.

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.

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

"The tkprof tool is a tuning tool used to determine the CPU execution times for SQL statements. To use the tool, you start by setting the timed statistics parameter to 'true' in the initialization file. The next step is to turn on tracing. You can do this for the entire database by utilizing the SQL trace parameter or for just the current session using the ALTER SESSION command. Either of these will generate the trace file. Once this is complete, you can run the tkprof tool against the trace file and examine the output."

The interviewer will ask you this question for two reasons. The first is to confirm that you know what tkprof does. The second is to see whether you can set the initialization parameter needed to execute this tool. Questions like these confirm your capabilities for this job and the depth of your knowledge. As the interview progresses, you can expect to be asked many more technical questions like this one, and they will become increasingly challenging.

When responding to this question, the first thing you should do is define tkprof. This will demonstrate your knowledge of the process and confirm that this is what the interviewer is asking you about. The next step is to discuss what this command does and the results you obtain by using the final part of the answer to describe the initialization parameter you would use to get the full output. Answering the question in this manner will ensure that you provided the interviewer with all the information they were expecting.

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

"An initialization parameter, or init parameter file is an ASCII text file containing parameters used to perform functions within an Oracle database. You can use these parameters and values to specify which optimizer you will use to tune the database. The format for this is optimizer mode= rule---RBO cost--CBO choose-----First CBO otherwise RBO."

As the interview progresses, the interviewer will continue to ask you both operational and technical questions about this job. This is the easiest way for them to understand your qualifications for the position, level of experience, and technical expertise. They will also use terminology relevant to this role in their questions, assuming that you know its meaning.

By asking the question and this format, the interviewer assumes that you know the meaning of an init parameter, how it is used, and which ones are used to accomplish the task they are describing. This is why it is a good idea to thoroughly review all the terminology, processes, and technology used in this job before the interview. This will keep the terms first in your mind and enable you to quickly retrieve the information you need to answer the interviewer's question. If you are not familiar with the terminology used in a question, you can ask a clarifying question or make a definitive statement before answering the interviewer's question.

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

"There are several indications that the SHARED_POOL_SIZE parameter needs to be increased., These include Poor data dictionary or library cache hit ratios and getting error ORA-04031. Another indication is steadily decreasing performance when all the other tuning parameters are the same."

Oracle database administrators who perform performance tuning operations need to be familiar with the aspects of the database which indicate issues or problems. Interviewers will ask you about these to ensure that you recognize these signals and know how to respond to them. They're likely to ask you about the issues the organization has already encountered or are likely to encounter based on the structure of their Oracle database.

When responding to the interviewer's question about what indicators you can expect related to a specific issue you are likely to encounter with the Oracle database, make sure you include all of the signals you may encounter. In some cases, these may be limited to only one indicator, while in others, there may be multiple signals indicating that a specific action needs to be taken. This is an exception to the rule of keeping your answer direct and to the point. You should provide all of the possible scenarios related to this issue.

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

"The command I typically use to view the statistics of an Oracle table is - select num_rows, blocks, empty_blocks from dba_tables where tab_name='emp'."

Interviewers are likely to ask you about different commands used to tune an Oracle database. They want to know that you understand how to do this and the commands used to accomplish individual tasks. These may be individual questions similar to this one or be presented in the form of a case in which you are asked to execute the command or create code required to accomplish the task designated by the interviewer.

It is typical for interviews for technical roles such as an Oracle administrator and tuner to include cases. These are scenarios created by the interviewer which you are asked to resolve using your knowledge of this job. You can prepare for these cases by reviewing the processes, procedures, and commands you use to execute tasks when optimizing an Oracle DBMS. Practicing these scenarios before the interview will familiarize you with them and enable you to complete the case during the interview.

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

"To maximize the performance of an Oracle database, it is important that you ensure the proper separation of the segments of a tablespace within the database. These include the INDEX, SYSTEM, ROLLBACK, REDO LOG, DATA, and TEMPORARY segments. You can use Oracle's Flexible Architecture standard or other partitioning schemes to ensure proper separation of these segments."

By asking this question, the interviewer assumes that you have a strong background, lots of experience, and the expertise needed to do this job. Only very experienced and capable Oracle administrators will be able to answer this question. Detailed questions like this are usually asked in the middle or towards the end of an interview after the interviewer has gained confidence in your capabilities.

As the technical questions become more difficult, your responses may become more detailed. This is appropriate as long as your answers align with the complexity of the question. Some difficult questions may still only require a straightforward, concise response. It is your responsibility to determine how complex should answer needs to be to provide the information the interviewer seeks.

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

"If the tablespace of a database has a table which contains multiple extents, it does not necessarily indicate that the database needs to be turned. However, if there are chained rows within the table, the performance can be impacted. Recognizing this would indicate that the database needs to be optimized using one of the two methods discussed earlier."

The interviewer is asking you a hybrid technical and operational question. They recognize that you need the technical knowledge and must be familiar with the tuning process to respond to this question. Hybrid questions like this are useful to interviewers when exploring your qualifications and determining your ability to perform this job.

Every time you respond to an interviewer's question, you should be trying to demonstrate your qualifications, experience, and expertise. Keep in mind that the purpose of an interview is to further qualify you for this role. The interviewer knows that you can do this job based on the information you provided in your resume or CV. They want to confirm this information and explore your background in more depth. Remembering this throughout the interview will help improve your performance and make it more likely you will be offered the position at the conclusion of the interview.

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

"As I stated earlier, wait events are one of the components which can be tuned to improve the performance of an Oracle database that tracks the weight events which can be tuned are CPU time, and direct path read."

You may already recognize this is a follow-up to a question you were asked earlier in the interview. Interviewers ask follow-up questions directly after the original question to clarify your answer, collect more information, or explore the topic in more detail. They may also ask follow-up questions at points later in the interview to calibrate your answers. Interviewers appreciate candidates who are consistent throughout the interview. This tells them that you are not fabricating your answers but rather are using your knowledge, experience, and expertise to respond to their questions.

If you are asked a follow-up question directly after the initial question, it indicates that the interviewer needs more information. This signals you to provide more details or elaborate on the answer. If the follow-up question occurs later in the interview, interviewers are looking to calibrate your answers to similar questions. In this case, try to remember your initial response to their question, then similarly answer this one.

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

"If I needed to get the index of a table and identify which column the index is located on, I would use two commands. These are - dba_indexes and dba_ind_columns."

As the interview progresses, the interviewer's questions will become more detailed and complex. This indicates that they are gaining confidence in your capabilities and want to explore your qualifications in greater detail. When this occurs, it is likely that the interviewer has already decided about your qualification for the role and is using these questions to differentiate you from other qualified candidates.

As the questions become more detailed and complex, your answers should follow suit. You can begin to embellish your responses a little more, include additional details in your answers, or provide background information to put your answers into context. Alternatively, it is okay to continue to respond with brief, concise answers, encouraging the interviewer to ask follow-up questions.

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

"The most direct way to collect statistics on a database table is to use an init parameter to designate one of two methods used to collect the statistics. These are compute and estimate. The format of the init parameter is 'analyze table emp compute statistics' or 'analyze table emp estimate statistics'."

When an interviewer asks you about the best way to accomplish a task, they are really asking for your opinion. Most tasks have several different options which can be used when performing them. The interviewer wants to understand which of these options you use and whether this follows industry standards, generally agreed upon methodologies, or the process currently used by their organization.

Since this question asks you about your opinion on the most direct way to collect statistics on a database table, your answer should reflect your expertise and experience performing this task. It is very likely that you follow generally accepted, agreed-upon methodologies. Your answer should reflect what the interviewer is expecting. However, this also provides you the opportunity to demonstrate your innovation by describing something recently developed or which is being adopted as a new standard for the industry. Regardless of how you answer the question, you should be prepared for a follow-up question asking you to discuss your rationale for using the method you described in your answer.

What is the difference between using compute and estimate?

"If you use compute, a full-table scan or FTS will be performed. Only 10% of the table will be read and analyzed when using estimate. The full-table scan will take longer but will be more accurate. The estimate will only take a brief amount of time but lacks accuracy. Which one you use is based on how the statistics will be used and what level of accuracy the user is expecting."

Interviewers expect your answers to questions to be brief and to the point, but they also expect you to provide complete information in response to their questions. This demonstrates your expertise and provides them with an example of your communication skills. Since you will be required to communicate with the other individuals within the organization who may not have your technical expertise, interviewers want to make sure you can discuss technical terms and simple, easy-to-understand language.

You probably already recognize this as a follow-up to a question you were asked earlier in the interview. By asking a follow-up question, the interviewer indicates that you did not provide enough information, or they may simply want to explore the topic in more detail. If you experience a great number of follow-up questions during an interview, it is an indication that you need to start elaborating on your answers, providing additional details.

Which optimizer mode does the data dictionary follow?

"Oracle data dictionary is an important component of the Oracle DBMS. The data dictionary stores the metadata, which is all the information about the object's structures, as well as users, tables, columns, and other components. RBO or Rule-Based Optimization is the optimizer mode the data dictionary follows."

Interviewers will ask you additional questions about Oracle's two optimizer modes to ensure that you fully understand what these are and how to use them. Knowing about the individual database components impacted by each optimizer mode demonstrates your qualifications for this role. Questions like this may either be standalone or follow-ups to questions the interviewer asked earlier in the interview.

When responding to this question, you may want to start by defining what the data dictionary is before stating which optimizer mode it follows. This will demonstrate to the interviewer your knowledge of the topic and act as a clarifying statement. While this breaks the rule of keeping your answers to technical questions brief and to the point, it ensures that you are answering the question appropriately and lets the interviewer know you are knowledgeable about this topic.

Oracle Performance Tuning Interview Questions

Go Back

1. What are some of the different types of tuning you can perform in an Oracle environment?
2. What are some of the elements you can tune to optimize an Oracle database?
3. In the context of an Oracle database, what does an optimizer do?
4. In your opinion, what is performance tuning?
5. In your opinion, which optimizer is the best one for most Oracle database tuning?
6. The ESTAT events report indicates that you are getting busy buffer waits. What method can you use to find out what is causing this?
7. 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?
8. What method would you use if you want to see the trace files in an Oracle database?
9. Can you describe the purpose of an Oracle SQL execution plan?
10. What commands would you use if you didn't know what SQL is currently being used by a session?
11. What is the easiest way to collect statistics of a single user in Oracle Apps?
12. Can you discuss the different types of optimizers you use?
13. What are some of the prerequisites needed to use an Oracle database optimizer?
14. What should you check first if you see multiple fragments in the SYSTEM tablespace?
15. Please discuss row chaining, how it happens, and how it can be reduced or corrected.
16. Can you describe the hit ratio in the context of database buffers? Also, do you use the instantaneous or cumulative hit ratio for tuning?
17. 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?
18. When should copy latches be increased, and what parameters are used to do this?
19. 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?
20. Can you explain the use of TKPROF and the initialization parameter which needs to be used to get full TKPROF output?
21. Describe the init parameter used to make use of an Oracle database optimizer?
22. How would you know that the SHARED_POOL_SIZE parameter needs to be increased?
23. What command can you use if you want to see the statistics of an Oracle table?
24. How can you assure the appropriate separation of the segments of the tablespaces when installing an Oracle database?
25. If the database tablespace has a table with 40 extents, does it need to be tuned? What other issues can impact this?
26. What types of wait events can be tuned to improve the performance of an Oracle database?
27. What would you do if you needed to get the index of a table and identify which column the index is on?
28. What is the most direct way to collect statistics on a database table?
29. What is the difference between using compute and estimate?
30. Which optimizer mode does the data dictionary follow?

Oracle Performance Tuning Interview Questions

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

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

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

4. In your opinion, what is performance tuning?

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

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

7. 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?

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

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

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

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

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

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

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

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

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

17. 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?

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

19. 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?

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

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

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

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

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

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

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

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

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

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

30. Which optimizer mode does the data dictionary follow?