Oracle Performance Tuning 19C : Practical Techniques Every DBA Should Master

 Hi All,

In this post i am going to share about Oracle Performance Tuning: Practical Techniques Every DBA Should Master .

As a database admin,  when users start complaining about slow queries, suddenly everyone's looking at you with that "solution for slowness" . 

Let me walk you through some battle-tested techniques those will helped me till now.

Start with Statistics and Execution Plans

Before diving into complex tuning strategies, always check if your statistics are current. 

Oracle's cost-based optimizer relies heavily on accurate statistics to make smart decisions about query execution paths.

 I've seen queries run 7x slower simply because someone forgot to gather stats after a large data load.

Here's the thing about statistics gathering – 

SIZE AUTO might seem convenient, but it can be a performance killer on large databases. 

Oracle decides whether histograms are needed and how many buckets to create, which can lead to extremely long statistics collection times. 

For production environments, I prefer SIZE REPEAT to maintain consistent performance while keeping the histogram settings that already work well.

lets Check when statistics were last gathered ::

SELECT table_name, last_analyzed, num_rows, blocks, avg_row_len FROM user_tablesWHERE table_name IN ('CUSTOMERS', 'ORDERS', 'ORDER_ITEMS') ORDER BY last_analyzed DESC;

as per the above snap we have stats from CDB$ROOT and PDB level .

now lets Gather fresh statistics - performance-conscious approach

lab purpose sample schema and table creation ::

SQL> create user racsinfo identified by welcome1;

User created.

SQL> grant connect,resource to racsinfo;

Grant succeeded.

SQL> grant dba to racsinfo;

Grant succeeded.

BEGIN

    DBMS_STATS.GATHER_TABLE_STATS(

        ownname => 'RACSINFO',

        tabname => 'CUSTOMERS',

        estimate_percent => 10,  -- Fixed percentage for predictable performance

        method_opt => 'FOR ALL COLUMNS SIZE REPEAT',  -- Maintains existing histogram settings

        cascade => TRUE,

        degree => 4  -- Parallel execution for faster collection

    );

END;

/

initial setup or when you need to establish histograms

BEGIN

    DBMS_STATS.GATHER_TABLE_STATS(

        ownname => 'RACSINFO',

        tabname => 'CUSTOMERS',

        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

        method_opt => 'FOR ALL COLUMNS SIZE 254',  -- Explicit bucket count

        cascade => TRUE

    );

END;

/

Once your stats are current, examine execution plans for problematic queries. The EXPLAIN PLAN statement is your best friend here, but don't just run it and walk away – actually analyze what Oracle is telling you.

create explain plan 

display explain plan 

Indexing: The Art of Strategic Access Paths

Proper indexing can make or break your database performance. But here's the thing – more indexes aren't always better. Each index comes with maintenance overhead, so you want to be strategic about what you create.

Review historical execution plans for a specific query : 

Get the actual execution plan details::

This historical data reveals crucial insights – maybe Oracle is already using hash joins effectively, or perhaps a different execution plan performed much better in the past. Sometimes a full table scan with a hash join outperforms an index-based nested loop, especially when dealing with larger result sets.

For the query above, let's say you notice consistent full table scans on the orders table and the historical data shows this is indeed a bottleneck. A composite index might be exactly what you need:

Create a composite index to support the WHERE clause and JOIN

CREATE INDEX idx_orders_date_customer 

ON orders (order_date, customer_id);

-- Create a composite index to support the WHERE clause and JOIN

CREATE INDEX idx_orders_date_customer 

ON orders (order_date, customer_id);

-- For queries that frequently filter by customer and date range

CREATE INDEX idx_orders_customer_date 

ON orders (customer_id, order_date);

The order of columns in composite indexes matters enormously. Oracle can use an index even if you don't specify all columns, but only if you include the leading columns. Think of it like a phone book – you can find "Smith, John" easily, but finding all the "Johns" regardless of last name requires scanning the entire book.

Don't forget about function-based indexes for queries that use expressions:

-- If you frequently search by uppercase customer names

CREATE INDEX idx_customers_upper_name 

ON customers (UPPER(customer_name));

-- Now this query can use the index

SELECT * FROM customers 

WHERE UPPER(customer_name) = 'RACSINFOTECH';

SQL Tuning: Writing Smarter Queries

Sometimes the issue isn't with indexes or statistics – it's with how we write our SQL. Here are some patterns I've learned to watch for:

Avoid functions in WHERE clauses on indexed columns:

-- This prevents index usage

SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2026-05-19';

-- This allows index usage

SELECT * FROM orders 

WHERE order_date >= DATE '2026-05-19' 

  AND order_date < DATE '2026-05-19';

Use EXISTS instead of IN with subqueries:

-- Less efficient for large datasets

SELECT * FROM customers c

WHERE c.customer_id IN (

    SELECT o.customer_id FROM orders o 

    WHERE o.order_date >= DATE '2026-05-19'

);

-- More efficient - stops at first match

SELECT * FROM customers c

WHERE EXISTS (

    SELECT 1 FROM orders o 

    WHERE o.customer_id = c.customer_id 

      AND o.order_date >= DATE '2026-05-19'

);

Memory Configuration: Getting the SGA Right

Oracle's System Global Area (SGA) configuration can dramatically impact performance. The three key components you should focus on are the buffer cache, shared pool, and PGA.

 current SGA configuration :::

Monitor buffer cache hit ratio (should be > 90%)

For production environments with proper licensing, AWR provides much more accurate and less intrusive monitoring:

Top SQL by DB Time from AWR (last 7 days)::

Average Active Sessions (AAS) analysis - more meaningful than blocking sessions

This Approach is Better:

v$sqlstats provides historical aggregated data without the concurrency issues of v$sql

AAS analysis gives you a more meaningful picture of database load than simple blocking session counts

Trend analysis helps you catch performance degradation before it becomes critical

Focus on DB Time percentage helps prioritize which SQL statements truly impact your users

The key is establishing baseline performance metrics and monitoring for significant deviations, rather than just looking for blocking sessions or arbitrary thresholds.

Oracle performance tuning is both an art and a science. Start with the fundamentals – current statistics, proper indexing, and well-written SQL. Monitor your system regularly and don't be afraid to question the optimizer's decisions when something doesn't make sense.

Thanks,

Srini