What’s the SQL_ID?

In Oracle, each SQL statement has a generated SQL_ID based upon a hash of the SQL’s text.

This enables us to identify SQL’s in the database and manipulate them with tools such as SQL Plan Management. There are occasions when it would be useful to know what the SQL_ID is for any particular SQL text but that can be trickier than we think.

There is a few ways to discover the SQL_ID:

The SQL ID I’m trying to locate is for ‘select * from cost_tab where id = 1;‘

			
Using SQL*Plus or sqlcl outputs with the sql_id extension
> set feed on sql_id
> select * from cost_tab where id = 1;
        ID       DATA FORCE_TAB_LOOKUP_COL
---------- ---------- --------------------
         1          1 need_tab_access
1 row selected.
SQL_ID: anchpn14yhp1p

		

			
While it's running, you can see the SQL_ID
> SELECT sid,sql_id,prev_sql_id 
    FROM gv$session 
   WHERE status='ACTIVE' 
    AND  TYPE <> 'BACKGROUND';
SID    SQL_ID           PREV_SQL_ID
282    anchpn14yhp1p    9babjv8yq8ru3
287    6nap1n8vcjqzs    6nap1n8vcjqzs

		

			
Finding it in memory using GV$SQL using the text
> SELECT sql_id,sql_text 
    FROM gv$sql 
   WHERE lower(sql_text) like 'select * from cost_tab%';
  
SQL_ID           SQL_TEXT
________________ ______________________________________
anchpn14yhp1p    select * from cost_tab where id = 1

		

But what if you have not executed it?

or if you only have the SQL_TEXT but are trying to locate a specific SQL_ID in SQL Plan Management which is not in memory?

Well there’s a function in the SQL Translation Framework that does it for you: dbms_sql_translator.sql_id

			
> SELECT dbms_sql_translator.sql_id('select * from cost_tab where id = 1') sql_id FROM dual;
SQL_ID
---------------
anchpn14yhp1p

When you are looking in SQL Plan Management (DBA_SQL_PROFILES, DBA_SQL_PATCHES or DBA_SQL_PLAN_BASELINES), none of those have the SQL_ID exposed in the view. But they do have the SQL Text…

			
> SELECT dbms_sql_translator.sql_id(sql_text) sql_id
        ,dsp.*
    FROM dba_sql_profiles dsp;
EDD
SQL_ID           NAME                            CATEGORY                  SIGNATURE SQL_TEXT                               CREATED                LAST_MODIFIED          DESCRIPTION                                                               TYPE      STATUS     FORCE_MATCHING       TASK_ID TASK_EXEC_NAME       TASK_OBJ_ID    TASK_FND_ID    TASK_REC_ID    TASK_CON_DBID
________________ _______________________________ ___________ _______________________ ______________________________________ ______________________ ______________________ _________________________________________________________________________ _________ __________ _________________ __________ _________________ ______________ ______________ ______________ ________________
anchpn14yhp1p    coe_anchpn14yhp1p_3865827752    DEFAULT        12553149506940042398 select * from cost_tab where id = 1    2026-04-26 23:35:07    2026-04-26 23:35:07    coe anchpn14yhp1p 3865827752 12553149506940042398 16250295567494165074    MANUAL    ENABLED    NO                     

		

Adding that function to the SQL_TEXT column in the SPM views makes it so much easier to find the SQL you are after 🙂

Like most of my blog posts, this one really is about me forgetting for the nth time the name of what I was trying to do, and making it a little easier for me to find it