29 October, 2020

Getting your SQL Statement's SQL_ID

 SQL*Plus now can provide you the SQL_ID of the last statement executed in your own session with SET FEEDBACK SQL_ID.

A quick demo :


SQL> set feedback on sql_id
SQL> select count(*) from my_target where factory='SYS';

  COUNT(*)
----------
     52217

1 row selected.

SQL_ID: g1mk14hdxc1ww
SQL> select * from table(dbms_xplan.display_cursor('g1mk14hdxc1ww'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1mk14hdxc1ww, child number 0
-------------------------------------
select count(*) from my_target where factory='SYS'

Plan hash value: 1690349505

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_TARGET_NDX |  2683 | 13415 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FACTORY"='SYS')


19 rows selected.

SQL_ID: 5dyyqqwuyu01v
SQL>


After I executed my query against the "my_target" table, SQL*Plus provided my the SQL_ID ("g1mk14hdxc1ww").  I could then immediately get the Execution Plan for the statement, without having to query for the SQL_ID in V$SQL or V$SQLAREA.

This demonstration is with SQL*Plus 19.0 against a 19c Database.  (I think "set feedback on sql_id" was introduced in SQL*Plus 18)




1 comment:

Derya Oktay said...

Very handy! Thanks for it.