Title: How to Find the Tables Behind a SQL_ID in Oracle

 Title: How to Find the Tables Behind a SQL_ID in Oracle

As a database administrator or developer, you've likely encountered a situation where you need to troubleshoot a slow-running query. You have the SQL_ID from a performance report or a monitoring tool, but the SQL text itself is long, complex, or a mystery. The first step to understanding the query's behavior is to figure out which tables it's actually accessing.

Fortunately, in an Oracle database, this is a straightforward process. The execution plan—the set of steps the database uses to execute a SQL statement—holds all the answers. By examining the plan, we can quickly identify the tables, views, and indexes involved.

Let's dive into the two primary methods for doing this.

Method 1: Finding Tables for Active or Recent Queries

If the SQL statement is currently running or was executed very recently, its execution plan will still be in the shared pool. We can access this information using the V$SQL_PLAN view.

The V$ views are dynamic performance views, which means they show the current state of the database. V$SQL_PLAN specifically stores the execution plans for all SQL_IDs in the shared pool.

Here's the query you'll use:

SQL

SELECT
    t.object_owner,
    t.object_name,
    t.operation,
    t.options,
    s.sql_text
FROM
    v$sql_plan t,
    v$sql s
WHERE
    t.sql_id = s.sql_id
    AND t.sql_id = '&sql_id'
ORDER BY
    t.id;

How it works:

• We join V$SQL_PLAN (t) with V$SQL (s) on sql_id.

• V$SQL_PLAN gives us the execution plan details, including OBJECT_OWNER and OBJECT_NAME for each step.

• V$SQL gives us the actual SQL_TEXT for context.

• We filter the results by providing our specific SQL_ID.

• The ORDER BY t.id ensures the plan steps are displayed in the correct sequence.

When you run this query, you'll be prompted to enter the sql_id. The output will be a list of operations, and any line with an OPERATION like 'TABLE ACCESS', 'INDEX FULL SCAN', or 'INDEX RANGE SCAN' will show you the corresponding table or index name under OBJECT_NAME.

Pro-Tip: If the query is on a view, the execution plan will typically show the underlying base tables, giving you a complete picture.

Method 2: Finding Tables for Historical Queries

What if the query was executed days or even weeks ago? The SQL_ID is no longer in the shared pool, so V$SQL_PLAN won't help. This is where the Automatic Workload Repository (AWR) comes in.

Oracle's AWR automatically collects, processes, and maintains performance statistics, including execution plans for popular or resource-intensive queries. This data is stored in historical views, which are prefixed with DBA_HIST_.

The view we're interested in is DBA_HIST_SQL_PLAN.

Here's the query to find historical tables for a SQL_ID:

SQL

SELECT
    t.object_owner,
    t.object_name,
    t.operation,
    t.options
FROM
    dba_hist_sql_plan t
WHERE
    t.sql_id = '&sql_id'
ORDER BY
    t.plan_hash_value,
    t.id;

How it works:

• This query is very similar to the previous one, but we're now querying DBA_HIST_SQL_PLAN.

• The plan_hash_value is included in the ORDER BY clause because a single SQL_ID can have multiple execution plans over time. This helps to group the steps for each plan together.

Permissions are Key! To run these queries, you'll need the necessary permissions. Typically, users with roles like SELECT_CATALOG_ROLE or DBA will have access to these views. If you encounter an "insufficient privileges" error, you'll need to contact your DBA to grant you the required permissions.

By using these simple but powerful queries, you can quickly demystify any SQL_ID and get a clear understanding of the tables and objects it's interacting with, putting you on the right path to performance tuning.

Happy troubleshooting!