How to find SQL_ID for SQL query step by step?

In my previous article I have given ways to find out the Username for SQL query. I got question in my mind that many SQL developers will be facing issue to find the SQL_ID for SQL query step by step. There are multiple ways to find out the SQL_ID for SQL query in oracle. The most useful way is you require to execute some System views to get SQL_ID information. Before executing the System SQL views we require to know that if you can use these system views extensively it may cause the performance issue. You can use the following system views :

1. V$SQL
2. V$SQLAREA
3. V$SQLSTATS

Queries to find SQL_ID for SQL Query using V$SQLSTATS :

In this section I would like to give the steps to find SQL_ID for SQL Query with using V$SQLSTATS view. The V$SQLSTATS view is one of the most important view which is faster than V$SQL view so DBA’s recommends this view to find SQL_ID extensively.

Lets Say we require to find out the SQL_ID for following query :

Select * from Employees where employee id In ( 1,2,3,4,5);

You can directly use the following syntax to find the SQL Id :

Select SQL_ID from V$SQLSTATS Where SQL_TEXT = ‘SQL Query’;

Query :

Select SQL_ID from V$SQLSTATS Where SQL_TEXT = ‘Select * from Employees where employee id In ( 1,2,3,4,5)’;

Output :

SQL_ID

-------------

56rrstssrdsr1

The above query will give you the SQL_ID with simple steps.

Queries to find SQL_ID for SQL Query using V$SESSION :

You can also use V$SESSION view to find out the SQL_ID for executed SQL query. But by this statement you can only able to find out the SQL_ID for last executed SQL statement.

Lets say the following query is last executed query :

Select * from Employees where employee id In ( 1,2,3,4,5);

You can utilize the following query to find out the SQL_ID

Select Prev_SQL_ID from V$SESSION where SID=System_Context(‘Amit_DBA’,’SID’);

The above query will give you following output :

SQL_ID

-------------

56rrstssrdsr1

Oracle 18c SET FEEDBACK Command :

You can use the Set feedback command of oracle 18c. The SQL_ID parameter of set feedback command will easily give you the SQL_ID for sql or plsql executed.

Kindly execute following command on SQLPlus.

SET FEEDBACK ON SQL_ID;

Then execute any query :

Select Count(*)from Employees;

Output will be :

12

SQL_ID: 9r8kgzntdn9sq

Queries to find SQL_ID for SQL Query using V$SQLAREA :

You can use V$SQLAREA view to find out the SQL_ID easily.

Query :

SELECT * FROM V$SQLAREA WHERE sql_text=’Select count(*) from Employees’;

These are multiple ways to find out SQL_ID for SQL query step by step. I hope you like this article. If you like this article or if you have issues with same kindly comment in comments section.