SQL performance interview questions.

Can Index be Renamed?If Yes How?(90% asked inPerformance Tuning Interview Questions)

Yes we can rename the indexes.User should have create any index privilege to rename the index. Alter index Index_name Rename to New_indexname;

What are functional Based indexes?Explain with Example

1.Function based indexes allows us to index on the functional columns so that oracle engine will take the index and improves the performance of the query. 2.As per requirements we are using lot of SQL functions to fetch the results.Function based indexs gives ability to index the computed columns. 3.Function based indexes are easy to implement and it also provides immediate value.These indexes speeds up the application without changing application code or query. Example: Syntax: Create index indexname on tablename(Function_name(column_name)); Example: Create index FI_Employee on Employee(trunc(Hire_date));

What is Bit-map index?Explain with Example.(90 % Asked in Performance Tuning Interview Questions)

1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes. 2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index. 3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values. 4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only. Syntax: Create bitmap index Index_name on Table_name(Columns which have distinct values); Example: CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);

What is mean by Clustered index?

1.The clustered indexes are indexes which are physically stored in order means it stores in ascending or descending order in Database. 2.Clustered indexes are created once for each table.When primary key is created then clustered index has been automatically created in the table. 3.If table is under heavy data modifications the clustered indexes are preferable to use.

What is mean by non clustered indexes?(90 % asked in Performance Tuning Interview Questions)

1.The clustered indexes are used for searching purpose as we can create clustered indexes where primary is is defined.But Non clustered indexes are indexes which will be created on the multiple joining conditions,multiple filters used in query. 2.We can create 0 to 249 non-clustered indexes on single table.Foreign keys should be non clustered. 3.When user wants to retrieve heavy data from fields other than primary key the non clustered indexes are useful.

What are the factors to consider for creating Index on Table? Also, How to select a column for Index?

Creation of index depends on the following factors. 1. Size of table, 2. Volume of data If Table size is large and we need a smaller report, then it's better to create Index. Regarding the column to be used for Index, as per the business rule, you should use a primary key or a unique key for creating a unique index.

What are the steps involved in improving the SQL performance?

Discover - First of all, find out the areas of improvement. Explore tools like Profiler, Query execution plans, SQL tuning advisor, dynamic views, and custom stored procedures. Review - Brainstorm the data available to isolate the main issues. Propose - Here is a standard approach one can adapt to boost the performance. However, you can customize it further to maximize the benefits. 1. Identify fields and create indexes. 2. Modify large queries to make use of indexes created. 3. Refresh table and views and update statistics. 4. Reset existing indexes and remove unused ones. 5. Look for dead blocks and remove them. Validate - Test the SQL performance tuning approach. Monitor the progress at a regular interval. Also, track if there is any adverse impact on other parts of the application. Publish - Now, it's time to share the working solution with everyone in the team. Let them know all the best practices so that they can use it with ease.

What do you check first if there are multiple fragments in the SYSTEM tablespace?

First of all, check if the users don't have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace assignment by verifying the DBA_USERS view.

What is a Latch Free Event? And when does it occur? Alos, how does the system handles it?

In Oracle, Latch Free wait event occurs when a session requires a latch, attempts to get it but fails because someone else has it. So it sleeps with a wait eying for the latch to get free, wakes up and tries again. The time duration for it was inactive is the wait time for Latch Free. Also, there is no ordered queue for the waiters on a latch, so the one who comes first gets it.

What are the bottlenecks that affect the performance of a Database?

In a web application, the database tier can prove to be a critical bottleneck in achieving the last mile of scalability. If a database has performance leakage, that can become a bottleneck and likely to cause the issue. Some of the common performance issues are as follows. 1. Abnormal CPU usage is the most obvious performance bottleneck. However, you can fix it by extending CPU units or replacing with an advanced CPU. It may look like a simple issue but abnormal CPU usage can lead to other problems. 2. Low memory is the next most common bottleneck. If the server isn't able to manage the peak load, then it poses a big question mark on the performance. For any application, memory is very critical to perform as it's way faster than the persistent memory. Also, when the RAM goes down to a specific threshold, then the OS turns to utilize the swap memory. But it makes the application to run very slow. You can resolve it by expanding the physical RAM, but it won't solve memory leaks if there is any. In such a case, you need to profile the application to identify the potential leaks within its code. 3. Too much dependency on external storage like SATA disk could also come as a bottleneck. Its impact gets visible while writing large data to the disk. If output operations are very slow, then it is a clear indication an issue becoming the bottleneck. In such cases, you need to do scaling. Replace the existing drive with a faster one. Try upgrading to an SSD hard drive or something similar.

What is a explain plan?

It's a term used in Oracle. And it is a type of SQL clause in Oracle which displays the execution plan that its optimizer plans for executing the SELECT/UPDATE/INSERT/DELETE statements.

What could most likely cause a SQL query to run as slow as 5 minutes?

Most probably, a sudden surge in the volume of data in a particular table could slow down the output of a SQL query. So collect the required stats for the target table. Also, monitor any change in the DB level or within the underlying object level.

What exactly would you do to check the performance issue of SQL queries?

Mostly the database isn't slow, but it's the worker session which drags the performance. And it's the abnormal session accesses which cause the bottlenecks. 1. Review the events that are in wait or listening mode. 2. Hunt down the locked objects in a particular session. 3. Check if the SQL query is pointing to the right index or not. 4. Launch SQL Tuning Advisor and analyze the target SQL_ID for making any performance recommendation. 5. Run the "free" command to check the RAM usage. Also, use TOP command to identify any process hogging the CPU.

What are some tips to improve the performance of SQL queries?

Optimizing SQL queries can bring substantial positive impact on the performance. It also depends on the level of RDBMS knowledge you have. Let's now go over some of the tips for tuning SQL queries. 1. Prefer to use views and stored procedures in spite of writing long queries. It'll also help in minimizing network load. 2. It's better to introduce constraints instead of triggers. They are more efficient than triggers and can increase performance. 3. Make use of table-level variables instead of temporary tables. 4. The UNION ALL clause responds faster than UNION. It doesn't look for duplicate rows whereas the UNION statement does that regardless of whether they exist or not. 5. Prevent the usage of DISTINCT and HAVING clauses. 6. Avoid excessive use of SQL cursors. 7. Make use of SET NOCOUNT ON clause while building stored procedures. It represents the rows affected by a T-SQL statement. It would lead to reduced network traffic. 8. It's a good practice to return the required column instead of all the columns of a table. 9. Prefer not to use complex joins and avoid disproportionate use of triggers. 10. Create indexes for tables and adhere to the standards.

What are Rule-based Optimizer and Cost-based Optimizer?

Oracle determines how to get the required data for processing a valid SQL statement. It uses one of following two methods to take this decision. Rule-based Optimizer - When a server doesn't have internal statistics supporting the objects referenced by the statement, then the RBO method gets preference. However, Oracle will deprecate this method in the future releases. Cost-based Optimizer - When there is an abundance of the internal statistics, the CBO gets the precedence. It verifies several possible execution plans and chooses the one with the lowest cost based on the system resources.

What is Proactive tuning and Reactive tuning?

Proactive tuning - The architect or the DBA determines which combination of system resources and available Oracle features fulfill the criteria during Design and Development. Reactive tuning - It is the bottom-up approach to discover and eliminate the bottlenecks. The objective is to make Oracle respond faster.

What is SQL Query Optimization?

Query Optimization is the process of writing the query in a way so that it could execute quickly. It is a significant step for any standard application.

What is Summary advisor and what type of information does it provide?

Summary advisor is a tool for filtering and materializing the views. It can help in elevating the SQL performance by selecting the proper set of materialized views for a given workload. And it also provides data about the Materialized view recommendations.

How do you tune a query using the explain plan?

The explain plan shows a complete output of the query costs including each subquery. The cost is directly proportional to the query execution time. The plan also depicts the problem in queries or sub-queries while fetching data from the query.

What are Explicit Indexes?

The indexes which is created by user are called as explicit indexes.You can say the indexes which are created by 'Create Index' statement are called as Explicit indexes. Syntax: create index indexname on tablename(columnname); Example: Create index IND_Employee_ID on Employee(Employee_ID);

What are types of SQL Optimizer?

There are following types of optimizer: 1.Rule Based Optimizer 2.Cost Based Optimizer 15.Explain Rule Based Optimizer? Answer: When we execute any SQL statement ,the optimizer uses the predefined rules which defines what indexes are present in the database and which indexes needs to be executed during the execution.Rule Based optimizer is used to specify which table is been full scanned and which tables are taking the indexes during the execution.In Earlier the only optimizer which is used by Oracle is Rule Based optimizer "Rule Based Optimizer specifies the rules for how to execute the query."

What is composite index?(90% asked in Performance Tuning Interview Questions)

When 2 or more columns are related to each other in the table and the same columns are used in where condition of the query then user can create index on both columns.These indexes are known as composite indexes. Example: Create index CI_Employee on Employee(Eno,Deptno);

What is mean by implicit index.Explain with example.

Whenever we define unique key or primary key constraints on the table the index will automatically create on the table.These indexes are known as implicit indexes because these are created implicitly whenever the constraint has been applied to the table.These indexes are normal indexes not unique indexes.The indexes are normal because the columns already have defined as unique so uniqueness is already been applied. Example: Create table Employee (Employee_ID varchar2(20) primary key, Employee name varchar2(50), salary number(10,0) not null); If We check description of table: Desc Employee; Name Null Type ——————————————- Employee_ID not null varchar2 Employee_name varchar2 Salary not null number Here you will see index is already created for Employee_ID as it has defined primary key.

How do you analyze an explain plan?

While analyzing the explain plan, check the following areas. 1. Driving Table 2. Join Order 3. Join Method 4. Unintentional cartesian product 5. Nested loops, merge sort, and hash join 6. Full Table Scan 7. Unused indexes 8. Access paths