Top 10 Oracle Performance Tuning Interview Questions – First Lesson

Distinct vs Group By performance oracle can be changed due to several conditions. Generally, both are distinct and grouped by clauses that do similar work. But then why do we have two? Yes, that is not related to performance. purposely both clauses work differently

Oracle Both the DISTINCT and GROUP BY clauses return unique values from the result set. Performance can be different because each has a different context of usability. 

DISTINCT:

DISTINCT: The DISTINCT keyword returns unique values on a single or multiple columns without any aggregate functions

-- Syntax
SELECT DISTINCT column_name FROM table; 
--

• Simple queries.
• Low cardinality column
• Distinct use with Single-column

GROUP BY:

GROUP BY: The GROUP BY clause takes unique values BUT with some aggregate values such as COUNT, SUM, AVG, etc.

-- Syntax
SELECT column_name, COUNT(*) FROM able GROUP BY column_name; 
--

• More complex queries
• Use with other aggregate functions like AVG, COUNT, or others.

Once comparing the Oracle COALESCE vs NVL performance most of the searches say those are mostly equal. Before moving into that where to use Oracle Coalesce and NVL.

NVL: evaluates both arguments

--Syntax:
NVL(expr, default) 
SELECT NVL(NULL, 'A') AS result FROM dual; 
-- Output: 'A' 
--

COALESCE: Looking for the arguments until finding the NON-NULL value (exceptions, can be there such as sequence NEXTVAL )

-- 
Syntax: 
COALESCE (expr1, expr2, ..., expr_n) 
SELECT COALESCE(NULL, 'A', 'B') AS result FROM dual; 
-- Output: 'A' 
--

NVL is a much old function but there are a lot of benefits COALESCE does the same thing and in addition, there are some extra benefits. If you doing some Simple Replacement then considering performance there is not much difference with those.

But if you have complex functions as a default value in NVL then that may be a concern in performance. COALESCE is good if you have a lot of possible columns as your expressions

System views like V$SESSION, V$SQLTEXT_WITH_NEWLINES, and V$SQL to retrieve information about running queries now. Use active sessions to retrieve the required information     
This is the focus of this article you can find currently running queries by or already started.

--
SELECT
    s.username,
    s.sid,
    s.serial#,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.sql_exec_start,
    q.sql_text
FROM
    v$session s
JOIN
    v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE'
    AND s.sql_exec_start IS NOT NULL;
--

Currently Executing SQL Statements with Details

--
select s.username,
    s.sid,
    s.serial#,
    s.machine,
    s.program,
    s.status,
    s.sql_id,
    s.sql_child_number,
    s.sql_exec_start,s.module,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username is not null
 AND s.sql_exec_start IS NOT NULL
order by s.sid,t.piece;
--

There are more examples you can find out from the linked article Because sometimes we need to find out long-running queries or never-ending queries

DECODE was introduced in the early Oracle release but CASE was introduced later in Oracle 8.1.6

performance seems they are about the same, According to investigations we can say case vs decode in Oracle performance is nearly the same

• CASE is designed for complex situations that have a lot of conditions and complex functional queries in logic. Also, it also improves the readability and clean code  SELECT (e.g., WHERE, HAVING).

Complex Conditions:

-- Example:
SELECT company, 
CASE WHEN sales_amount >= 1000 THEN 'High Sales' 
WHEN sales_amount >= 500 THEN 'Moderate Sales' 
ELSE 'Low Sales' END AS sales_type 
FROM sales_orders; 
--

1. DECODE is designed with one-to-one mapping conditions and simple conditions in select statements, but good to ignore complex conditions and multiple conditions. Readability is low with decode.

Simple Value Replacements:

--Example:
SELECT product_name, DECODE(purchase_amount > 1000, 1, 'High', 'Low') AS category_type 
FROM purchase_order; 
--

2. Conciseness: Some cases in the database table column have low cardinal values in a column. Those are not readable to others But you need real value to show to clients.

--Example:
SELECT order_id, DECODE(order_status, 'P', 'Pending', 'S', 'Shipped', 'C', 'Cancelled') AS orader_status 
FROM purchase_orders;
--

3. NULL Handling: NULL is everywhere in SQL therefore handling NULL in queries is important. In those kinds of situations, DECODE is a great option because it is straightforward work.

--Example
SELECT order_id, DECODE(order_status, NULL, 'Pending', order_status) AS order_status 
FROM purchase_orders;
--

Simply if the database wants to sequence the rows in the table oracle does the sorting. Oracle uses the sort_area_size for a small dataset. This is done very quickly in the memory.

Sometimes sort_area_size is not enough for sorting then those situations are handled by creating a TEMP table in the database below pointed common ways of improving Oracle order by performance

1. Indexing for Sorting: Creating an index definitely improves the significant performance
2. Limit the Result Set: reduces the time and the load for sorting data
3. Use Parallel Processing: parallel processing reduces the time for Oracle order by performance
4. Increase Memory Allocation for SORT_AREA_SIZE
5. Materialized Views: if data is more static then materialized views are suiting for it
6. Caching Results: data is static, so caching is another solution for sorting

--Few Examples.........
-- Indexing for Sorting
--
CREATE INDEX index_date ON purchase_order_table (need_date, order_date)
--
--Limit the Result Set
--
SELECT * FROM purchase_order_table  
WHERE need_date < sysdate+5 
ORDER BY need_date, order_date 
FETCH FIRST 10 ROWS ONLY 
--
--Materialized Views
--
CREATE MATERIALIZED VIEW sorted_purchase_orders   AS 
SELECT * FROM purchase_order_table   
ORDER BY need_date, order_date;
--

The above tips improve the Oracle ORDER BY performance and reduce the execution time. See the example for each tip by clicking the relative post. also follow remaining Oracle Performance Tuning Interview Questions

Oracle SQL connect by mapping records with the same table connecting with parent records and selecting all the hierarchy by saying the position of the row. 

• Add index 

Add index into Connect by and START WITH clause and if there is a where condition then if required add index there as well.

--
CREATE INDEX emp_hierarchy_idx ON employee_table(manager_id);
--

• Limit the Depth:

The LEVEL keyword is used to set the number of levels in the tree hierarchy. If you are not required all the levels use the LEVEL keyword to limit it.

--
SELECT * FROM employee_table 
WHERE LEVEL <= 3 
CONNECT BY PRIOR employee_id = manager_id;

• Add inner query

The below examples show how to add an inner query if required. This is to filter out the CONNECT BY dataset. Query explicitly reduces the set of data set who work in Sales, which Oracle can then do before performing the CONNECT BY.

--
SELECT * FROM (
               SELECT * FROM employees 
                WHERE dept_id = 'SALE'
              )
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id
--

First thing you have to identify the performance issues and figure out which columns need to be added to the index This is the basic syntax for creating indexes in Oracle.

-- Syntax --
CREATE INDEX index_name ON table_name (column_name);
--

There are a lot of ways to create and index with Oracle using keywords.

7.1    Create a B-Tree Index :

2.	-- Syntax
3.	CREATE INDEX idx_employee_ix1 ON employees(employee_id);
4.	--

7.2  Create a Bitmap Index: columns with low cardinality

3.	--Syntax 
4.	CREATE BITMAP INDEX idx_gender ON employees(gender);
5.	--

7.3 Create a Function-Based Index: when consists of a function or expression in where condition

4.	-- Syntax
5.	CREATE INDEX idx_lower_last_name ON employees(LOWER(last_name));
6.	--

7.4 Create a Reverse Key Index: suit for index column values are added like a sequence

6.	-- Syntax
7.	CREATE INDEX idx_reverse_order_id ON orders(order_id) REVERSE;
8.	--

7.5 Create a Domain Index: used in such data types as video, LOB, or custom data types

7.6 Create a Clustered Index data is frequently retrieved from 2 tables

7.7 Create a Descending Index would want to ORDER BY DESC

7.8 Create a Unique Index: The index ensures values are unique

-- Syntax
CREATE UNIQUE INDEX idx_unique_email ON employees(email);
--

7.9 Create a Composite Index: multiple columns

7.10 There are 19 different types click here

This will improve the performance when consists of a function or expression in where condition or more columns in a table that you need to filter data by computed values

Use Cases for Function-Based Indexes

Substring Search: 

-- Syntax 
CREATE INDEX idx_substr_1 ON customer_table(SUBSTR(last_name,3,4));
--

Case-Insensitive Searches: 

-- Syntax 
CREATE INDEX idx_case_insensitive ON product_table(UPPER(part_name));
--

Mathematical Calculations:

-- Syntax 
CREATE INDEX idx_compute_1 ON products(unit_price * 0.9/100);
--

This is creating a custom index accruing to the computed values on your function and that index is used for filtering data with complex functionalities.

oracle bitmap index is proposed for low cardinality columns. oracle bitmap index uses a two-dimensional array for the index and each row index as an array value. Those indexed values match with rowid and rowid directly map with information.

this is suited with columns like department name, Job Title, Gender, Marriage State, manufacturing year, etc. 

--
CREATE BITMAP INDEX customer_gendor_idx ON Customers (gender);
--

A specialized type of index in Oracle that is used to retrieve text, spatial, or user-defined data speedily.  this specialized type of index provides a way to retrieve structure data from a table. 

Domain index design for retrieving data set that is huge like JSON text, XML file, or very complex set of data.

-- Use index type as INDEXTYPE IS MDSYS.SPATIAL_INDEX:
CREATE INDEX spatial_idx ON spatial_table (column_name) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
--
--  
-- Use end syntax for INDEXTYPE IS CTXSYS.CONTEXT:
CREATE INDEX text_idx ON my_table (column_name) INDEXTYPE IS CTXSYS.CONTEXT;
--
-- 
--To create a domain index for XML data, you would use:
CREATE INDEX xml_idx ON xml_table (xml_column) INDEXTYPE IS XDB.XMLIndex;
--