Tales From A Lazy Fat DBA

Happy New Year Everyone!

Starting 2022 with a short and a quick post! 🙂

Today, one of the customer’s application architect during a team meet asked me if its possible to have the SQL ID in advance, before the SQL execution ? And luckily, I know that’s possible (through Connor McDonald‘s blog post on the same subject) and I was immediately able to answer the customer with a confident ‘Yes, that’s possible!’ 🙂

When I asked, why he want to do that, he said that they are planning to integrate a new module to their CRM application, first into development environment where they want to catch all expensive SQLs, their IDs in advance and later on compare it with the UAT environment and to get some anticipations on SQL runtime latencies. That I guess is a valid reason to have the SQL IDs in advance.

If you’re using Oracle 18c or above then you can use SET FEEDBACK ON SQL_ID, which means the SQL_ID for the currently executed SQL or PL/SQL statement is displayed after the results. This is very useful, and a huge time saver, when all you need is to know the SQL_ID. When feedback for SQL_ID is ON, then the value of the SQL_ID is assigned to a predefined variable _SQL_ID

And if you’re running below 18c then you can use dbms_sql_translator.sql_id package, it provides an interface for creating, configuring, and using SQL translation profiles, and you can use one of its function sql_id to get the SQLID in advance.

Let’s do the demo for both! Though I am running this on 12c, but still be able to use both options as connecting to the database via SQLcl (a SQL Dev CLI) version 21.4 and that has both the options available.

[oracle@ontadomain.fatdba bin]$ ./sql ontadbschema/xxxxxxxxx@testdb_ha

SQLcl: Release 21.4 Production on Tue Jan 04 02:58:04 2022
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Last Successful login time: Tue Jan 04 2022 02:58:06 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>  select * from dixdroid.testtable;

               STATS_CORRNAME    STATS_CORRVALUE  STATS_CORRDESC
_____________________________ __________________ _________________
DEFAULT_LOCK_DURATI                           60 SECONDS
FIX_ALLOWED_LOCK_DURATI                      120 SECONDS
FIX_RETURNABLE_NUMBERS                       200
FIX_RETURNABLE_RANGES                         10
DEFAULT_RESERVATION_DURATI                    60 MINUTES

SQL>


-- Now trying the first approach which will work on < 18c versions.
SQL>
SQL> select dbms_sql_translator.sql_id('select * from dixdroid.testtable where STATS_CORRVALUE > 150') from dual;

1 row selected.

SQL_ID: 16jcpmjs087ct



-- Trying the second approach using SET FEEDBACK which works great if running >= 18c 
SQL> set feedback only sql_id
SQL> select * from dixdroid.testtable where STATS_CORRVALUE > 150;

1 row selected.

SQL_ID: 16jcpmjs087ct
SQL>
SQL>

Hope It Helped!
Prashant Dixit