analsis

3. Next: Check the actual SQL performance

Before considering any index change, we need to understand the work already performed by the running DELETE.

Run the following query in DEV03.

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    inst_id,
    sql_id,
    child_number,
    plan_hash_value,
    executions,
    rows_processed,
    buffer_gets,
    disk_reads,
    ROUND(elapsed_time / 1000000, 2)
        AS elapsed_seconds,
    ROUND(cpu_time / 1000000, 2)
        AS cpu_seconds,
    ROUND(user_io_wait_time / 1000000, 2)
        AS io_wait_seconds,
    last_active_time
FROM
    gv$sql
WHERE
    sql_id = '6vhqjj8bwpga6'
ORDER BY
    inst_id,
    child_number;

Run it twice, five minutes apart.

We need to compare the changes in physical reads, buffer gets, elapsed time, and I/O wait time.

The results will help establish whether the statement is accumulating substantial read activity. These are cumulative cursor statistics, so they must be interpreted alongside the current session and transaction information.

4. Check whether the running DELETE is actually modifying rows

SELECT
    SYSDATE AS sample_time,
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    s.event,
    t.used_ublk,
    t.used_urec,
    t.log_io,
    t.phy_io,
    t.cr_get,
    t.cr_change
FROM
    gv$session s
JOIN
    gv$transaction t
ON
    s.inst_id = t.inst_id
AND s.taddr = t.addr
WHERE
    s.sid = 268;

============== 

4. Run these five diagnostic queries next

All queries below are read-only. Run them in the DEV03 PDB.

Query 1 – Verify the columns of GL_BALANCES_N2

We need to understand why Oracle is using only PERIOD_NAME as its index access predicate.

SET LINESIZE 200
SET PAGESIZE 100

SELECT
    index_name,
    column_position,
    column_name,
    descend
FROM
    dba_ind_columns
WHERE
    table_owner = 'GL'
AND table_name = 'GL_BALANCES'
AND index_name = 'GL_BALANCES_N2'
ORDER BY
    column_position;

This will identify the actual index column order.

Even if the index contains additional columns, Oracle may still need to examine many index entries because of the access predicate shown in your plan.

Query 2 – Identify all indexes on GL_BALANCES

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    i.index_name,
    i.index_type,
    i.status,
    i.blevel,
    i.leaf_blocks,
    i.clustering_factor,
    i.num_rows,
    LISTAGG(
        c.column_name,
        ', '
    ) WITHIN GROUP (
        ORDER BY c.column_position
    ) AS index_columns
FROM
    dba_indexes i
JOIN
    dba_ind_columns c
ON
    i.owner = c.index_owner
AND i.index_name = c.index_name
WHERE
    i.table_owner = 'GL'
AND i.table_name = 'GL_BALANCES'
GROUP BY
    i.index_name,
    i.index_type,
    i.status,
    i.blevel,
    i.leaf_blocks,
    i.clustering_factor,
    i.num_rows
ORDER BY
    i.index_name;

We want to establish whether an existing index includes the relevant columns:

• LEDGER_ID

• PERIOD_NAME

• ACTUAL_FLAG

Do not create or modify an index at this stage.

Query 3 – Check how many records match the purge criteria

This query counts rows currently matching the captured bind values.

SELECT
    COUNT(*) AS matching_rows
FROM
    gl.gl_balances
WHERE
    ledger_id = 50
AND period_name = '2000-12'
AND actual_flag = 'A';

Important consideration

This is a separate query against a table being modified by the running purge. It sees committed data according to Oracle's read-consistency rules, not the uncommitted deletions performed by the purge session.

The count may therefore include rows already deleted but not committed by the running request. It is not a reliable live progress counter.

If the table is very large, this COUNT may itself be expensive. Run it only after reviewing the table size and the available indexes, preferably during a suitable diagnostic window.

Query 4 – Check the SQL's actual I/O activity

This is the most important query for your current execution plan.

SET LINESIZE 250
SET PAGESIZE 100

SELECT
    inst_id,
    sql_id,
    child_number,
    plan_hash_value,
    executions,
    rows_processed,
    buffer_gets,
    disk_reads,
    ROUND(
        elapsed_time / 1000000,
        2
    ) AS elapsed_seconds,
    ROUND(
        cpu_time / 1000000,
        2
    ) AS cpu_seconds,
    ROUND(
        user_io_wait_time / 1000000,
        2
    ) AS io_wait_seconds,
    last_active_time
FROM
    gv$sql
WHERE
    sql_id = '6vhqjj8bwpga6'
ORDER BY
    inst_id,
    child_number;

Run this query twice, five minutes apart.

Compare the increase in:

• BUFFER_GETS

• DISK_READS

• USER_IO_WAIT_TIME

• ROWS_PROCESSED

These statistics are cumulative for the cached cursor and may include multiple executions. They should not be treated as an exact measure of the currently running DELETE unless that execution has been isolated.

Query 5 – Check the current transaction's progress

Use the SID and serial number verified for the running request.

SET LINESIZE 250

SELECT
    SYSDATE AS sample_time,
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    s.event,
    s.state,
    t.used_ublk,
    t.used_urec,
    t.log_io,
    t.phy_io,
    t.cr_get,
    t.cr_change
FROM
    gv$session s
JOIN
    gv$transaction t
ON
    s.inst_id = t.inst_id
AND s.taddr = t.addr
WHERE
    s.sid = 268;