How to find the SQL_ID of your SQL statement

There are different ways to find the SQL_ID of an SQL statement. First of all, 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

On the other hand, V$SESSION can also be used in order to find the last executed SQL statement’s SQL_ID that you ran. PREV_SQL_ID column correspond 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;

However, we couldn’t get the expected result. Because, DBMS_OUTPUT is implicitly called by default. So, 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

In addition, 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 🙂