By Franck Pachot

.
This post is something I discovered by chance when writing about tagging SQL statement with recognizable comments. We know that Oracle is introducing more and more artificial intelligence and machine learning in the database engine, but here is the first time I see something where random or hash values seems to bring some meaning.

There are two common ways to run a query and find it in V$SQL:

  • add some tag as a comment in the query
  • get the sql_id from the executed query

The idea of tag is not new at all, and not only for the Oracle database. For example, the Google SQL Insight handles some pre-formatted tags.


SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
01-APR-21 06.31.51.678270 AM +02:00

If I just want a unique tag to add to the query, an convenient way is to add the current timestamp. It can even be set as a substitution variable to put in a comment. With microseconds included, I know it will be unique.


SQL> set feedback on sql_id

SQL> select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.31.51.678270 AM +02:00 */
  2  /

     EMPNO ENAME                            JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------------------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING                             PRESIDENT            17-NOV-81       5000                    10

1 row selected.

SQL_ID: 5c0tty145sddp
SQL>

I have added the timestamp as a /* 01-APR-21 06.31.51.678270 AM +02:00 */ comment at the end of my statement. And, in order to show the second way to identify the query, I use the SQL*Plus SET FEEDBACK ON SQL_ID. There you can see 5c0tty145sddp as the SQL_ID for my query. This is fully reproducible through databases and versions: the SQL_ID depends only on the SQL text. If you have installed the SCOTT schema, you will get the same 5c0tty145sddp for this query. Funny coincidence, here 5c0tt is SCOTT in 1337 language… but, as you will see, I’m not sure it is a coincidence.



SQL> select sql_id,sql_fulltext from v$sql where sql_text like '%01-APR-21 06.31.51.678270 AM +02:00%' and users_executing=0;

SQL_ID        SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
5c0tty145sddp select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.31.51.678270 AM +02:00 *

1 row selected.

If I can’t get the SQL_ID from the SET FEEDBACK ON, here is how I get it thanks to my tag, from V$SQL. The USERS_EXECUTING=0 predicates avoids to return my current query which verifies also the same LIKE predicate.

So in the SCOTT.EMP table the president is “KING”. Let’s change that to the real boss:


SQL> update EMP set ENAME='LARRY ELLISON' where JOB='PRESIDENT' /* 01-APR-21 06.32.11.963644 AM +02:00 */
  2  /

1 row updated.

SQL_ID: 0rac13hrmtrqm

Oh, that’s funny. I’ve put my new timestamp as a tag, but also display the SQL_ID from SQL*Plus. 0rac13hrmtrqm starts with ORACLE in 1337 language… and the new ENAME I’ve updated is the boss of ORACLE. Is this really a coincidence? Note that this is not exactly LEET (it would be “0r4c13” or “024c13”). The idea of SQL_ID, since it was introduced in 10g, was that the letters “eilo” are not there as they could be mistaken with 1 and O on some terminals.


SQL> select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.33.82.691464 AM +02:00 */
  2  /

     EMPNO ENAME                            JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------------------- --------- ---------- --------- ---------- ---------- ----------
      7839 LARRY ELLISON                    PRESIDENT            17-NOV-81       5000                    10

1 row selected.

SQL_ID: 0c12g0mwq09va

I’m probably interpreting this too far, but I read “0c12g” like OCI 2nd gen… Is this sql_id really a random hash? OCI 2nd gen is the Oracle Cloud Infrastructure, and I got this after changing the PRESIDENT to “LARRY ELLISON” in the EMP table.

Let’s try with another Cloud vendor.


SQL> update EMP set ENAME='LARRY PAGE' where JOB='PRESIDENT' /* 01-APR-21 06.35.66.5728590 AM +02:00 */
  2  /

1 row updated.

SQL_ID: g00g13fys2fqd

When changing the boss to LARRY PAGE, one of the Google founders, the SQL_ID for this update is starting by… g00g13 and again that’s how to write Google in the SQL_ID allowed characters.


SQL> select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.41.26.238008094 AM +02:00 */
  2  /

     EMPNO ENAME                            JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------------------- --------- ---------- --------- ---------- ---------- ----------
      7839 LARRY PAGE                       PRESIDENT            17-NOV-81       5000                    10

1 row selected.

SQL_ID: gcp12yf4n2r3c
SQL>

The abbreviation of the Google Cloud Platform: GCP which has be started in 2012… and SQL_ID starts with gcp12…


SQL> update EMP set ENAME='BILL GATES' where JOB='PRESIDENT' /* 01-APR-21 06.30.35.4055430 AM +02:00 */
  2  /

1 row updated.

SQL_ID: bq8ru4dsjv67n

SQL> select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.31.18.2381810 AM +02:00 */
  2  /

     EMPNO ENAME                            JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------------------- --------- ---------- --------- ---------- ---------- ----------
      7839 BILL GATES                       PRESIDENT            17-NOV-81       5000                    10

1 row selected.

SQL_ID: azur3g4pr7mng

This is Azure, the Microsoft cloud, when I’ve set the President to BILL GATES…


SQL> update EMP set ENAME='JEFF BEZOS' where JOB='PRESIDENT' /* 01-APR-21 06.45.35.4055430 AM +02:00 */
  2  /

1 row updated.

SQL_ID: 4m4z0n7sadpm2

When I changed the president to JEFF BEZOS, the SQL_ID starts with 4m4z0n so let’s run the same select as before:


SQL> select * from EMP where JOB='PRESIDENT' /* 01-APR-21 06.51.00.0102180 AM +02:00 */
  2  /

     EMPNO ENAME                            JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- -------------------------------- --------- ---------- --------- ---------- ---------- ----------
      7839 JEFF BEZOS                       PRESIDENT            17-NOV-81       5000                    10

1 row selected.

SQL_ID: awss8rgcfkprm

And when querying it, that’s clearly starting with AWS.

The requirement for SQL_ID is that it identifies a SQL statement, but there’s no need for it to be really random. Just that one statement generates always the same SQL_ID. You can copy-paste those queries on any supported Oracle database and see the same SQL_ID, whatever the version. Please, tell me in comments if you don’t see the same. The only thing I did on the SCOTT schema was increasing the ENAME to put larger names: ALTER TABLE EMP MODIFY ENAME VARCHAR2(32);

Here is how you can run all that on db<>fiddle:

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c0bb7dad6447ed4b2ff9bc7475496aa6

Do you think it is a coincidence? Or a random value influenced by some Artificial Intelligence on the query text and parameters, to add a little business meaning encoded in LEET? Did you also find some funny SQL_ID? Please share in comments.

Update 02-APR-21

Yes, of course this was an April Fool. I mean, all this works but was biased by the tag I passed. SQL_ID is calculated from a MD5 of the SQL statement text, including the comments. Initially I’ve put the comments in front but SQLcl removes them so I’ve put them at the end. Tagging your SQL statements is not a bad idea but maybe you have something better than a timestamp. What I did to generate this is calling DBMS_SQL_TRANSLATOR.SQL_ID (which generates the SQL_ID from a SQL text) in a loop where I iterate on the fractional seconds part in the comment. Same query, similar tag, but able to generate different SQL_ID. Of course it may take a lot of CPU time to generate a specific 13 characters but stopping after a small prefix takes a few minutes.

Here is the quick script I used to generate those:

set serveroutput on
declare
s varchar2(100);
d varchar2(16) ;
p varchar2(16):='&1.%';
begin
for i in 1..1e9
loop
s:=q'[&3 /* 01-APR-21 &2..]'||to_char(i/1e6,'FM09D0000000')||q'[ AM +02:00 */]';
d:=DBMS_SQL_TRANSLATOR.SQL_ID(s);
if translate(d,'abcdfghjkmnpqrstuvwxyz','4bcdf6hjkmnpqr57uvwxyz')
like translate(p,'%abcdfghjkmnpqrstuvwxyzeilo','%4bcdf6hjkmnpqr57uvwxyz3110')
then dbms_output.put_line(to_char(i,'B999999999')||' '||d||': '||s); end if;
exit when d like p;
end loop;
end;
/

I call it with the prefix I want, the HH:MM part of the timestamp, and the SQL text and it displays the few matches among the billion of fractional time. Now, rather than mining bitcoins, you can personalize your SQL_ID 🤓

Post Views: 804
Thumbnail [60x60]
by
Oracle Team