Oracle Database 19C Performance Tunning - PART 1

Advantages:

1. Improved Query Performance
•    Optimized SQL execution plans lead to faster query response times.
•    Reduces unnecessary full table scans and improves indexing strategies.
•    Parallel execution tuning speeds up large data processing tasks.
2. Better Resource Utilization
•    Efficient use of CPU, memory, disk I/O, and network resources.
•    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache.
•    Helps in load balancing across multiple instances in RAC (Real Application Clusters).
3. Increased System Scalability
•    Ensures that the database can handle a growing number of users and transactions.
•    Proper tuning allows scaling without degrading performance.
•    Optimized parallel processing ensures better performance on multi-core servers.
4. Lower Infrastructure Costs
•    Reduces the need for additional hardware upgrades by optimizing existing resources.
•    Prevents over-provisioning of CPU and memory resources.
•    Minimizes cloud computing costs by reducing unnecessary workload and storage usage.
5. Enhanced User Experience
•    Faster database response time improves application performance.
•    Users experience fewer timeouts, delays, and slow transactions.
•    Ensures high availability and minimal disruptions.
6. Minimized Downtime & Bottlenecks
•    Identifies and resolves performance bottlenecks before they impact operations.
•    Reduces the risk of database slowdowns and outages.
•    Ensures smooth execution of batch jobs and reporting queries.
7. Efficient Workload Management
•    Helps optimize OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) workloads.
•    Ensures SQL plan stability using SQL Plan Baselines.
•    Prevents overloading of system resources with inefficient queries.
8. Improved Data Security & Compliance
•    Tuning log file switches reduces redo log contention, ensuring stable transactions.
•    Helps in better auditing and monitoring of system performance.
•    Ensures compliance with SLAs (Service Level Agreements) for data access speed.
9. Optimized Backup and Recovery
•    Reduces backup time by minimizing unnecessary data scans.
•    Ensures efficient redo and undo log management, improving recovery time.
•    Helps in faster failover and recovery in HA (High Availability) environments.
10. Better Monitoring and Predictive Analysis
•    Performance tuning tools like AWR, ASH, ADDM help in proactive monitoring.
•    Enables predictive analytics using AI-driven optimizations in Oracle 19c.
•    Allows trend analysis for capacity planning and database growth. 

Types:

1.    Performance Planning
2.    OS Tuning
3.    Instance Tuning
4.    SQL Tuning

Performance Planning:

Performance planning involves designing and configuring the system to meet performance goals.
•    Workload Analysis: Understand the nature of the workload (OLTP, OLAP, mixed).
•    Hardware Sizing: Ensure sufficient CPU, memory, storage, and network resources.
•    Database Design: Optimize schema design, indexing, and partitioning strategies.
•    Scalability: Plan for future growth and scalability.
•    High Availability: Implement backup, recovery, and disaster recovery strategies.

OS Tuning:

The operating system plays a crucial role in database performance.
Kernel Parameters: Adjust kernel parameters to optimize memory, I/O, and process management.
Shared Memory: Increase shmmax and shmall for SGA allocation.
kernel.shmmax = [size in bytes]
kernel.shmall = [total pages]
Semaphores: Set sem parameters for process synchronization.
kernel.sem = 250 32000 100 128
File Handles: Increase file-max and ulimit for open files.
fs.file-max = 6815744
File System: Use appropriate file systems (e.g., XFS, ext4).
I/O Subsystem: Optimize I/O performance by using ASM (Automatic Storage Management) or direct I/O.
Resource Management: Use tools like cgroups (Linux) or resource manager to allocate resources effectively.
Increase limits for Oracle users (e.g., nofile, nproc). ##vi /etc/security/limits.d/oracle-database-preinstall-19c.conf
Network Configuration: Ensure low-latency, high-bandwidth network settings.
Virtual Memory & Swap: Set appropriate swap space (1.5x RAM if <32GB, equal to RAM if >32GB).
Use Zswap/ZRAM if needed for efficient memory compression.
System Logging & Monitoring:
Disable excessive logging in rsyslog.conf.
Monitor system performance using below tools and commands

top
free -g
grep SwapTotal /proc/meminfo;grep MemTotal /proc/meminfo
sar 1 5
sar -u 5 10
mpstat
ipcs -lm
vmstat
iostat
netstat -tunlp
fdisk -l

Instance Tuning

Instance tuning focuses on optimizing the Oracle instance parameters and memory structures:
Memory Allocation:
Use Automatic Memory Management (AMM) or Automatic Shared Memory Management (ASMM).
Tune SGA (System Global Area) and PGA (Program Global Area) parameters.
Ensure proper allocation of Shared Pool, Buffer Cache, and Redo Log Buffer.
Process Tuning:
Adjust PROCESSES and SESSIONS parameters to support workload concurrency.
Optimize job queues and background processes like DBWR, LGWR, and ARCH.
Redo Logs & Undo Management:
Ensure proper redo log sizing to avoid excessive log switches.
Use UNDO tablespaces efficiently to minimize undo segment contention.
Parallel Execution Tuning:
Enable parallel query processing for large data operations (PARALLEL_DEGREE_POLICY=AUTO).
Optimize DB_FILE_MULTIBLOCK_READ_COUNT for better read performance.

The Symptoms and the Problems

1. Slow Physical I/O

Symptoms:
•    High wait times for db file sequential read or db file scattered read.
•    Slow query performance, especially for full table scans.
•    High disk I/O rates.

Problems:
•    Poorly optimized SQL queries (e.g., missing indexes, full table scans).
•    Inefficient disk subsystem (e.g., slow disks, improper RAID configuration).
•    High contention for disk resources.

Solutions:
•    Optimize SQL queries (e.g., add indexes, use partitioning).
•    Use faster storage (e.g., SSDs) or ASM for better I/O management.
•    Monitor and tune disk I/O using tools like iostat.

2. Latch Contention

Symptoms:
•    High wait times for latch free or specific latches like cache buffers chains.
•    Poor performance during high concurrency.

Problems:
•    High contention for shared memory structures (e.g., buffer cache).
•    Inefficient SQL causing excessive buffer access.
•    Insufficient memory allocation (e.g., small buffer cache).

Solutions:
•    Optimize SQL to reduce buffer access.
•    Increase DB_CACHE_SIZE or use multiple buffer pools.
•    Use bind variables to reduce hard parsing and shared pool contention.

3. Excessive CPU Usage

Symptoms:
•    High CPU utilization by Oracle processes.
•    Slow query performance or system unresponsiveness.

Problems:
•    Poorly tuned SQL queries (e.g., excessive parsing, sorting, or hashing).
•    High concurrency leading to CPU contention.
•    Inefficient use of parallel execution.

Solutions:
•    Optimize SQL queries (e.g., reduce parsing, use indexes).
•    Enable parallel execution for large queries.
•    Monitor and tune CPU usage using tools like top or AWR reports.

When to Tune

There are two types of tuning:

1.    Proactive Monitoring
2.    Bottleneck Elimination

1. Proactive Monitoring

Proactive monitoring involves continuously tracking database performance to identify and resolve issues before they impact users.
Key Areas: CPU, memory, I/O, network, wait events, sessions.
Tools: AWR, ADDM, Enterprise Manager, Statspack, OS tools (top, vmstat).
Steps:
1.    Set up alerts for key metrics.
2.    Regularly review AWR/ADDM reports.
3.    Monitor real-time session activity.
4.    Analyze performance trends.

2. Bottleneck Elimination

Bottlenecks occur when a component limits the overall performance of the system. Identifying and eliminating bottlenecks is critical for optimal performance.

Common Bottlenecks:

•    CPU: Optimize SQL, use bind variables, enable parallel execution.
•    Memory: Adjust MEMORY_TARGET, use HugePages, fix memory leaks.
•    I/O: Optimize SQL, use indexes, upgrade to SSDs, use ASM.
•    Network: Optimize TCP settings, use compression.
•    Locks: Kill blocking sessions, optimize transactions.
•    Wait Events: Analyze and tune SQL, adjust parameters.

Steps:

1.    Identify bottlenecks using AWR/ADDM.
2.    Analyze root cause (e.g., inefficient SQL).
3.    Implement fixes (e.g., tune SQL, adjust parameters).
4.    Verify improvements and monitor.

Baselines:

A performance baseline is a reference point for tuning and troubleshooting performance issues.
1. Why Baselines Matter?
•    Helps compare current vs. past performance
•    Identifies issues during peak usage periods
•    Supports proactive monitoring & tuning
2. Key Baseline Metrics
•    Application stats – Transactions, response time
•    Database stats – Query execution, wait events
•    OS stats – CPU, memory, swap usage
•    Disk I/O stats – Read/write speeds, bottlenecks
•    Network stats – Latency, packet loss
3. How to Use Baselines?
•    Collect data during peak hours (e.g., 10AM-12PM, 1:30PM-3PM, 12AM-6AM batch window).
•    Use Automatic Workload Repository (AWR) to store and compare snapshots.
•    Implement monitoring tools for real-time analysis.

Performance Parameters

1. Memory Management

•    MEMORY_TARGET: Total memory allocated for SGA and PGA (automatic memory management).
•    MEMORY_MAX_TARGET: Maximum memory allowed for MEMORY_TARGET.
•    SGA_TARGET: Total memory for SGA (automatic shared memory management).
•    PGA_AGGREGATE_TARGET: Total memory for PGA (program global area).

2. SGA Components

•    DB_CACHE_SIZE: Size of the buffer cache for data blocks.
•    SHARED_POOL_SIZE: Memory for shared SQL and PL/SQL areas.
•    LARGE_POOL_SIZE: Memory for large operations like RMAN backups.
•    JAVA_POOL_SIZE: Memory for Java objects and sessions.
•    STREAMS_POOL_SIZE: Memory for Oracle Streams.

3. PGA Components

•    SORT_AREA_SIZE: Memory for sorting operations (manual PGA management).
•    HASH_AREA_SIZE: Memory for hash joins (manual PGA management).

4. Process and Session Management

•    PROCESSES: Maximum number of processes.
•    SESSIONS: Maximum number of sessions.
•    OPEN_CURSORS: Maximum number of open cursors per session.

5. I/O and Storage

•    DB_BLOCK_SIZE: Size of Oracle data blocks (default is 8KB).
•    DB_FILE_MULTIBLOCK_READ_COUNT: Number of blocks read in a single I/O operation.
•    DB_WRITER_PROCESSES: Number of DBWR processes for writing dirty buffers.

6. Query Optimization

•    OPTIMIZER_MODE: Optimizer behavior (e.g., ALL_ROWS, FIRST_ROWS).
•    OPTIMIZER_INDEX_COST_ADJ: Adjusts the cost of index access.
•    OPTIMIZER_USE_SQL_PLAN_BASELINES: Enables SQL Plan Baselines.
•    OPTIMIZER_ADAPTIVE_PLANS: Enables adaptive query optimization.

7. Concurrency and Parallelism

•    PARALLEL_DEGREE_POLICY: Controls parallel execution behavior.
•    PARALLEL_MAX_SERVERS: Maximum number of parallel execution servers.
•    FAST_START_MTTR_TARGET: Target time for instance recovery (affects checkpoint frequency).

8. Redo and Recovery

•    LOG_BUFFER: Size of the redo log buffer.
•    LOG_CHECKPOINT_INTERVAL: Frequency of checkpoints based on redo block count.
•    LOG_CHECKPOINT_TIMEOUT: Frequency of checkpoints based on time.

9. Cursors and SQL

•    CURSOR_SHARING: Controls cursor sharing behavior (e.g., EXACT, FORCE).
•    SESSION_CACHED_CURSORS: Number of cursors cached per session.

10. Diagnostics and Monitoring

•    STATISTICS_LEVEL: Level of statistics collection (e.g., TYPICAL, ALL).
•    AWR_SNAPSHOT_INTERVAL: Interval for Automatic Workload Repository (AWR) snapshots.

SQL Tuning

SQL tuning is the process of optimizing SQL queries to improve database performance by reducing execution time, resource usage, and contention.

RBO & CBO

The optimizer is responsible for determining the most efficient way to execute a SQL query. There are two types of optimizers: Rule-Based Optimizer (RBO) and Cost-Based Optimizer (CBO).

1. Rule-Based Optimizer (RBO)

•    A legacy optimizer that uses a fixed set of rules to determine the execution plan.
•    Does not consider data distribution or statistics.

How It Works:

•    Follows a predefined ranking of access paths (e.g., index access is always preferred over full table scans).
•    Ignores table and index statistics.

Advantages:

•    Simple and predictable behaviour.
•    No overhead of gathering statistics.

Disadvantages:

•    Often produces suboptimal execution plans.
•    Does not adapt to changes in data distribution or volume.
•    Limited to simple queries and small datasets.

When to Use:

•    Rarely used in modern Oracle databases (deprecated in recent versions).
•    Only for backward compatibility with very old applications.

Example:
If an index exists, RBO will always use it, even if a full table scan would be faster.

2. Cost-Based Optimizer (CBO)

•    The default optimizer in modern Oracle databases.
•    Uses statistics about tables, indexes, and data distribution to determine the most efficient execution plan.

How It Works:

•    Calculates the "cost" of different execution plans based on:
•    Table and index statistics (e.g., number of rows, data distribution).
•    System resources (e.g., CPU, I/O).
•    Chooses the plan with the lowest cost.

Advantages:

•    Produces more efficient execution plans.
•    Adapts to changes in data distribution and volume.
•    Supports advanced features like partitioning, parallel execution, and materialized views.

Disadvantages:

•    Requires up-to-date statistics for accurate decisions.
•    Can be complex to troubleshoot if the optimizer chooses a suboptimal plan.

When to Use:

•    Default for all modern Oracle databases (including Oracle 19c).
•    Suitable for complex queries and large datasets.
Example:
If a table has 1 million rows and an index exists on a column with high selectivity, CBO may choose an index scan. If the selectivity is low, it may choose a full table scan.

Check Current Optimizer Mode:

SELECT value FROM v$parameter WHERE name = 'optimizer_mode';
Common values: ALL_ROWS, FIRST_ROWS, RULE.

Set Optimizer Mode:
ALTER SESSION SET optimizer_mode = ALL_ROWS;    ##session Level
ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE=BOTH;    ## system level (requires restart)

Hints

Optimizer hints are instructions embedded in SQL statements to influence the execution plan chosen by the Oracle optimizer.

Categories of Optimizer Hints

1.    Join Method Hints
2.    Scanning Method Hints
3.    Optimization Goal Hints
4.    Parallel Execution Hints
5.    Miscellaneous Hints

1. Join Method Hints

These hints influence how tables are joined.

a. Nested Loop Join (USE_NL)

For each row in the outer table, the inner table is searched for matching rows. This is efficient for small datasets or when the inner table has an index.
Example:
SELECT /*+ USE_NL(employees departments) */ * FROM employees, departments WHERE employees.department_id = departments.department_id;

b. Hash Join (USE_HASH)

Builds a hash table for one table and probes it with the other table. This is efficient for large datasets or when there are no indexes.
Example:
SELECT /*+ USE_HASH(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;

c. Sort-Merge Join (USE_MERGE)

Sorts both tables and merges them. This is efficient when both tables are large and already sorted.
Example:
SELECT /*+ USE_MERGE(employees departments) */ *
FROM employees, departments
WHERE employees.department_id = departments.department_id;

2. Scanning Method Hints

These hints influence how tables or indexes are scanned.

a. Full Table Scan (FULL)

Scans the entire table. This is efficient for small tables or when a large percentage of rows are selected.
Example:
SELECT /*+ FULL(employees) */ * FROM employees WHERE salary > 5000;

b. Index Scan (INDEX)

Uses an index to find rows. This is efficient for selective queries (small percentage of rows).
Example:
SELECT /*+ INDEX(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;

c. Index Fast Full Scan (INDEX_FFS)

Scans the entire index quickly, without following the tree structure. This is efficient for queries that need all rows from an index.
Example:
SELECT /*+ INDEX_FFS(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;

d. Ignore Index (NO_INDEX)

Prevents the use of a specific index. This is useful when the optimizer chooses an inefficient index.
Example:
SELECT /*+ NO_INDEX(employees emp_dept_idx) */ * FROM employees WHERE department_id = 10;

3. Optimization Goal Hints

These hints influence the optimizer's goal.

a. Optimize for Best Throughput (ALL_ROWS)

Optimizes for best throughput, minimizing total resource consumption.
Example:
SELECT /*+ ALL_ROWS */ * FROM employees WHERE salary > 5000;

b. Optimize for Quick Retrieval of First Rows (FIRST_ROWS)

Optimizes for quick retrieval of the first set of rows, even if the overall query takes longer.
Example:
SELECT /*+ FIRST_ROWS */ * FROM employees WHERE salary > 5000;

4. Parallel Execution Hints

These hints influence parallel execution.

a. Enable Parallel Execution (PARALLEL)

Enables parallel execution for a table or query, distributing the workload across multiple CPUs.
Example:
SELECT /*+ PARALLEL(employees, 4) */ * FROM employees WHERE salary > 5000;

b. Disable Parallel Execution (NO_PARALLEL)

Disables parallel execution for a table or query.
Example:
SELECT /*+ NO_PARALLEL(employees) */ * FROM employees WHERE salary > 5000;

5. Miscellaneous Hints

These hints provide additional control over the execution plan.

a. Specify Leading Table (LEADING)

Specifies the leading table in the join order.
Example:
SELECT /*+ LEADING(employees) */ * FROM employees, departments WHERE employees.department_id = departments.department_id;

b. Force Join Order (ORDERED)

Forces tables to be joined in the order they appear in the FROM clause.
Example:
SELECT /*+ ORDERED */ * FROM employees, departments WHERE employees.department_id = departments.department_id;

c. Direct-Path Insert (APPEND)

Directs the optimizer to use direct-path insert, which is faster for bulk inserts.
Example:
INSERT /*+ APPEND */ INTO employees_archive SELECT * FROM employees WHERE hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

Note:-
Use hints sparingly and only when necessary.
Test hints in a non-production environment before applying them to production.
Monitor performance using tools like AWR, ADDM, and SQL Tuning Advisor.

Query Tuning:

Create the Sample tables and inserted the sample datas for Performance Tunning Testing

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100),
    city VARCHAR2(50),
    created_at DATE
);

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER(10,2),
    category VARCHAR2(50)
);

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    product_id NUMBER,
    order_date DATE,
    quantity NUMBER,
    total_price NUMBER(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);


insert 1 million records in each table using PL/SQL loops.

BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO customers VALUES (
            i,
            'Customer_' || i,
            CASE MOD(i, 5)
                WHEN 0 THEN 'New York'
                WHEN 1 THEN 'Los Angeles'
                WHEN 2 THEN 'Chicago'
                WHEN 3 THEN 'Houston'
                ELSE 'Miami'
            END,
            SYSDATE - DBMS_RANDOM.VALUE(1, 1000)
        );
    END LOOP;
    COMMIT;
END;
/



BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO products VALUES (
            i,
            'Product_' || i,
            DBMS_RANDOM.VALUE(10, 1000),
            CASE MOD(i, 5)
                WHEN 0 THEN 'Electronics'
                WHEN 1 THEN 'Clothing'
                WHEN 2 THEN 'Furniture'
                WHEN 3 THEN 'Toys'
                ELSE 'Books'
            END
        );
    END LOOP;
    COMMIT;
END;
/



BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO orders VALUES (
            i,
            TRUNC(DBMS_RANDOM.VALUE(1, 1000000)),  -- Ensures customer_id exists
            TRUNC(DBMS_RANDOM.VALUE(1, 1000000)),  -- Ensures product_id exists
            SYSDATE - DBMS_RANDOM.VALUE(1, 1000),
            TRUNC(DBMS_RANDOM.VALUE(1, 10)),
            TRUNC(DBMS_RANDOM.VALUE(100, 10000))
        );
    END LOOP;
    COMMIT;
END;
/

Step 1: find the Slow or Long running or Issued query

get the high used cpu and ram used pid use top command in OS Level

select addr from v$process where spid=23957;

select sql_hash_value from v$session where paddr='000000007A3CC2B8';

Select sql_text from v$sql where hash_value='2560118920';

Use Shell Script find the query:

Use SHELL Script find the query

vi performance.sh

export ORACLE_SID=$1

sqlplus -s sys/sys as sysdba << EOF

select sql_text from v\$sql where hash_value=

(select sql_hash_value from v\$session where paddr=

(select addr from v\$process where spid=$2));

EOF

 

Chmod u+x performance.sh

./performance.sh pearl 23957

Test Query Performance Without Indexing:

check the execution time for a query:

SET TIMING ON;

SELECT customer_name, city FROM customers WHERE city = 'New York';

To analyze execution time, use:

EXPLAIN PLAN FOR SELECT customer_name, city FROM customers WHERE city = 'New York';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Creating an index on city will improve query performance.

CREATE INDEX idx_customers_city ON customers(city);

Check execution plan:

EXPLAIN PLAN FOR SELECT customer_name, city FROM customers WHERE city = 'New York';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Find the index of the table

select index_name,table_name from user_indexes where table_name=upper('&name');

SQL> save tblidx

Created file tblidx.sql

SQL> @tblidx

select table_name,index_name,column_name,column_position from user_ind_columns where table_name=upper('&tname');

Select table_name,last_analyzed,num_rows,partitioned,degree from user_tables where table_name=upper('&tname');

Index can’t take so use hints

EXPLAIN PLAN FOR SELECT /*+ INDEX(customers IDX_CUSTOMERS_CITY) */  customer_name, city FROM customers WHERE city = 'New York';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Check non-partition table performance report and then created the partitioned tables and move to non-partition to portioned tables and check performance

Next step de-fragmentation of the table