Oracle DB Optimization Interview Questions And Answers
Download Oracle Database Optimization Interview Questions and Answers PDF
Strengthen your Oracle Database Optimization interview skills with our collection of 8 important questions. Each question is designed to test and expand your Oracle Database Optimization expertise. Suitable for all experience levels, these questions will help you prepare thoroughly. Download the free PDF to have all 8 questions at your fingertips. This resource is designed to boost your confidence and ensure you're interview-ready.
8 Oracle Database Optimization Questions and Answers:
Oracle Database Optimization Job Interview Questions Table of Contents:
1 :: Tell me in brief the cursor optimization tips?
The following are few tips for cursor optimization:
- When the cursor is not needed, close the cursor
- Deallocate the cursor after closing it.
- Fetch less number of rows.
- Avoid triggers – because trigger executes whenever data gets updated, leads to overload of the system.
- When the rows are not need to update, use the option FORWARD ONLY
- Use where instead of having clause unless it is essential
Read More- When the cursor is not needed, close the cursor
- Deallocate the cursor after closing it.
- Fetch less number of rows.
- Avoid triggers – because trigger executes whenever data gets updated, leads to overload of the system.
- When the rows are not need to update, use the option FORWARD ONLY
- Use where instead of having clause unless it is essential
2 :: Do you know what are the ways to code efficient transactions?
We shouldn't allow input from users during a transaction.
We shouldn't open transactions while browsing through data.
We should keep the transaction as short as possible.
We should try to use lower transaction isolation levels.
We should access the least amount of data possible while in a transaction.
Read MoreWe shouldn't open transactions while browsing through data.
We should keep the transaction as short as possible.
We should try to use lower transaction isolation levels.
We should access the least amount of data possible while in a transaction.
3 :: Explain Execution Plan?
SQL Server caches the plan of execution of query or stored procedure which it uses in subsequent call.
This is a very important feature with regards to performance enhancement.
You can view execution plan of data retrieval graphically or textually.
Read MoreThis is a very important feature with regards to performance enhancement.
You can view execution plan of data retrieval graphically or textually.
4 :: Tell me some reasons of poor performance of query?
No indexes
Excess recompilations of stored procedures.
Procedures and triggers without SET NOCOUNT ON.
Poorly written query with unnecessarily complicated joins
Highly normalized database design.
Excess usage of cursors and temporary tables.
Read MoreExcess recompilations of stored procedures.
Procedures and triggers without SET NOCOUNT ON.
Poorly written query with unnecessarily complicated joins
Highly normalized database design.
Excess usage of cursors and temporary tables.
5 :: Do you have any idea what are indexes?
Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record to locate record.
Index is used to speed up query performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.
Index is the way to order the records in a database according to the field values. It is the way to have fast access to the particular information. Indexes are created to the columns that are queried frequently.
Read MoreIndex uses one or more column index keys and pointers to the record to locate record.
Index is used to speed up query performance.
Both exist as B-tree structure.
Kind of the indexes are clustered and non-clustered.
Index is the way to order the records in a database according to the field values. It is the way to have fast access to the particular information. Indexes are created to the columns that are queried frequently.
6 :: Tell me what are the types of Indexes?
Indexes can be clustered and non-clustered indexes.
Clustered index keeps all the records in the database in the order of clustered index key.
There can be only one clustered index per table.
Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table.
Read MoreClustered index keeps all the records in the database in the order of clustered index key.
There can be only one clustered index per table.
Non-clustered indexes are stored as B-tree structure in their own storage separate from data storage in the table.
7 :: Explain some disadvantages of the Indexes?
Indexes improve query performance but it slows down data modification operations.
Indexes consume disk space.
Read MoreIndexes consume disk space.
8 :: Tell me what is Index tuning?
Index tuning is part of database tuning for selecting and creating indexes. The index tuning goal is to reduce the query processing time. Potential use of indexes in dynamic environments with several ad-hoc queries in advance is a difficult task. Index tuning involves the queries based on indexes and the indexes are created automatically on-the-fly. No explicit actions are needed by the database users for index tuning.
Read More