1. Instance Tuning Steps. Define the Problem. Examine the Host System.
2. Interpreting Oracle Database Statistics. Examine Load.
3. Wait Events Statistics. buffer busy waits.
4. Real-Time SQL Monitoring. SQL Plan Monitoring.
5. Tuning Instance Recovery Performance: Fast-Start Fault Recovery. About Instance Recovery.

How can I improve my SQL query performance interview questions?

Ans.

1. Prefer to use views and stored procedures in spite of writing long queries.
2. It’s better to introduce constraints instead of triggers.
3. Make use of table-level variables instead of temporary tables.
4. The UNION ALL clause responds faster than UNION.
5. Prevent the usage of DISTINCT and HAVING clauses.

How u can improve the performance of a query?

25 tips to Improve SQL Query Performance

1. Use EXISTS instead of IN to check existence of data.
2. Avoid * in SELECT statement.
3. Choose appropriate Data Type.
4. Avoid nchar and nvarchar if possible since both the data types takes just double memory as char and varchar.
5. Avoid NULL in fixed-length field.
6. Avoid Having Clause.

What is performance tuning in Oracle?

Oracle Performance Tuning is the tuning performed to increase the performance of systems to make sure it never faces performance issues such as increase in load. In Oracle, databases are tuned to perform better. Increasing the RAM and configuring high end processor will do justice.

What are performance based questions?

Performance based interview questions are those an interviewer asks about a candidate’s past accomplishments. These questions are created and used to help the interviewer gauge how successful potential employees will be in the future.

What is query tuning?

Query tuning is a powerful tool for DBAs and developers alike in improving SQL Server performance. Unlike measures of system-level server performance (memory, processors, and so on), query tuning puts the focus on reducing the amount of logical I/O in a given query, because the fewer I/Os, the faster the query.