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:
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) withV$SQL(s) onsql_id.V$SQL_PLANgives us the execution plan details, includingOBJECT_OWNERandOBJECT_NAMEfor each step.V$SQLgives us the actualSQL_TEXTfor context.We filter the results by providing our specific
SQL_ID.The
ORDER BY t.idensures 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:
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_valueis included in theORDER BYclause because a singleSQL_IDcan 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!
