I read Daniel’s recent blog about five ways to grab the sql_id and remembered reading Connor’s about getting it before you run a SQL. At the time it hit me that one ought to be able to use this to simplify the workflow. Often we run a SQL and then use some marker – typically in the form of a comment in the SQL – to look up the sql_id to then use for other lookups.

It would of course be nicer to just change the SQL in one place and then execute it and be able to look up data for just the SQL we just ran.

To do this I set up a little example to show how it could be done. The example SQL is just a standard select on dual, as it is not the effect of tuning I’m demonstrating it works just as well as any other SQL to demonstrate how it can be used.

var sqltext varchar2(200);

declare c sys_refcursor;
begin
  :sqltext := 'select * 
  from dual';

  open c for :sqltext;
  close c;
end;
/

select sql_id
     , disk_reads
     , buffer_gets
     , user_io_wait_time
     , optimizer_cost
     , plan_hash_value
     , cpu_time
     , elapsed_time
     , physical_read_bytes
     , physical_write_bytes
  from v$sql 
 where sql_id = dbms_sql_translator.sql_id(:sqltext);

It is pretty straight forward what is done here, but let’s go through each piece.

First we declare a variable that can be accessed in different code-blocks.

Next is a block just to set the variable to a SQL-statement and open a cursor for it to make it execute. If one want to see the effects of fetching through the result set, just add a loop to spin through the data.

Last is a SQL to grab some information from v$sql for the SQL. It uses the SQL statement to calculate the sql_id it will have gotten in the open cursor.

Thus, to modify just change the SQL and run the script (or press F5 in SQL Developer) to see the effect it resulted in.

It can of course be modified to print the execution plan produced or pull any other data related to the sql_id you need for the work you do.

One note about Oracle Cloud. The above can be used in your OCI tenant on autonomous databases. It is a matter of the privileges given to an account. At least your admin-account will have the access, the rest is a matter of what privileges the admin account has given out.