How to find the SQL_ID of your SQL statement

There are different ways to find the SQL_ID of an SQL statement. In this post, I am going to show two different ways of it. First, via dynamic performance views. If you have the select privilege, you can use the dynamic performance views such as V$SQL, V$SQLAREA, V$SQLSTATS.

Keep in mind that, excessively querying the V$SQL may increase the data block contention and workload. Instead querying V$SQLSTATS is much faster and scalable. Moreover, data might remain in V$SQLSTATS even after the cursor has been aged out of the shared pool.

SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     92633


SQL> SELECT SQL_ID FROM V$SQLSTATS WHERE SQL_TEXT = 'SELECT COUNT(*) FROM DBA_OBJECTS';

SQL_ID
-------------
7r0kgzntdn7sq

Besides, V$SESSION can also be used in order to find the SQL_ID of the last SQL statement you ran. V$SESSION.PREV_SQL_ID column corresponds to it.

SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     92633


SQL> SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = sys_context('USERENV', 'SID');

PREV_SQL_ID
-------------
9babjv8yq8ru3


SQL> SELECT SQL_TEXT FROM V$SQLSTATS WHERE SQL_ID = '9babjv8yq8ru3';

SQL_TEXT
--------------------------------------------------------------------------------
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

This SQL_ID is not the correct SQL_ID for us. So, why we couldn’t get the expected result? Because, DBMS_OUTPUT is implicitly called by default. Hence, let’s turn off serveroutput and then run the statements again.

SQL> set serveroutput off
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     92633


SQL> SELECT PREV_SQL_ID FROM V$SESSION WHERE SID = sys_context('USERENV', 'SID');

PREV_SQL_ID
-------------
7r0kgzntdn7sq

Now we’ve reached the right conclusion.

Another way other than dynamic performance views is to use a feature of SQLPlus. From Oracle database 18c onwards, you can also use SET FEEDBACK SQLPlus command in order to get the currently executed SQL or PL/SQL statement’s SQL_ID. As it is known, the FEEDBACK command is used to display the number of records returned by the sql statement that run on SQLPlus. There is an additional SQL_ID parameter of the FEEDBACK command that returns SQL_ID after the resultset. You can use this feature as shown below.

SQL> SHOW FEEDBACK
FEEDBACK ON for 6 or more rows SQL_ID OFF

SQL> SET FEEDBACK ON SQL_ID
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     92633

1 sat?r secildi.

SQL_ID: 7r0kgzntdn7sq

SQL> SET FEEDBACK OFF SQL_ID
SQL> SELECT COUNT(*) FROM DBA_OBJECTS;

  COUNT(*)
----------
     92633

SQL_ID: 7r0kgzntdn7sq

Besides, you can also display the number of records without displaying the result set.

SQL> SET FEEDBACK ONLY
SQL> SELECT * FROM DBA_OBJECTS;

92633 sat?r secildi.

I hope it helps you 🙂