How To Find the SQL_ID of a SQL Statement

Have you ever been in a situation where a user comes to you and says “My report is running slow” or “My query used to run fast, and now it’s slow”? If you are a DBA, you most likely have!

Many times we do not have the actual SQL Statement that the user ran, or it is very complicated to get the statement from the actual report or application. The user might give you bits and pieces of the query “Well, it queries the employees table”…or something like that. In cases like this, you will need to identify the statement and the SQL_ID as well.

When tuning and troubleshooting performance problems, as a DBA you need to know the SQL_ID of a SQL statement! Why is that you might wonder? Because, the SQL_ID helps you identify the SQL statement in the database, which then helps you check the execution plan, identify plan changes, and execution times from the past. Most of the time, you need to track down the SQL Statement and SQL_ID, you don’t get the actual statement and run it yourself. The statement usually runs through the application or some kind of a report.

What is the SQL_ID?

The SQL_ID uniquely identifies each SQL Statement, meaning each SQL Statement has its own ID. When 2 SQL Statements are identical, they have the same SQL_ID, however if the SQL statements are different by a space, or a character, think uppercase instead of lowercase,
the SQL statements will have different SQL_ID. Let me show you:

--Statement 1:
select username from dba_users order by 1;

--Statement 2:
select username from dba_users order by 1;

--The above 2 statements are identical.

--Statement 1:
select username from dba_users order by 1;

--Statement 2:
select username from DBA_USERS order by 1;

--The last 2 statements are not identical. even though the meaning of the statement is the same, the syntax is different in the FROM clause, the view name is uppercase.

Where is the SQL_ID stored?

The V$SQL view has at least one row for each SQL_ID that is still in the cursor cache. The view can have multiple rows, depending on how many children the statement has. The important thing to note here is that the V$SQL view has a column: sql_text. How is this helpful? When you run a query, you do know the text of the SQL statement. You can query this view, based on the text, and try to return the SQL_ID.

If you have the Diagnostic and Tuning Pack license, then a historical view is also populated with the SQL_ID and the SQL_TEXT of the SQL Statement: DBA_HIST_SQLTEXT.

How to find the SQL_ID?

If you are lucky and the SQL Statement is still in the cursor cache, that is, it is not aged out of the V$SQL view, then run the following query:

select /* findsql */ sql_id, child_number, executions, sql_text
from v$sql
where command_type in (2,3,6,7,189)
and UPPER(sql_text) like UPPER('%part-of-your-sql-text%')
and UPPER(sql_text) not like UPPER('%findsql%');

--actual example:
select /* findsql */ sql_id, child_number, executions, sql_text
from v$sql
where command_type in (2,3,6,7,189)
and UPPER(sql_text) like UPPER('%DBA_USERS%')
and UPPER(sql_text) not like UPPER('%findsql%');

Otherwise, if the SQL Statement is aged out from the cursor cache, you will not find it in V$SQL view. With the helps of the tuning pack, you can query the DBA_HIST_SQLTEXT:

select /* histsql */ sql_id, to_char(substr(sql_text,1,4000)) 
from dba_hist_sqltext 
where UPPER(sql_text) like UPPER('%part-of-your-sql-text%')
and   UPPER(sql_text) not like UPPER('%histsql%');

You will notice I added a hint to both my queries, and that is because I want to exclude my searching queries.

You can add the queries above into a script with bind variables, and create your own script to find the SQL_ID based on parts of the SQL text of the query!

If you can run the SQL Statements yourself, meaning you have access to the report, it is much easier to get the SQL_ID, I’ll show you next time!

Free Video Tutorial