Labeling Queries

To quickly identify queries for profiling and debugging purposes, include the LABEL hint.

LABEL hints are valid in the following statements:

For example:

SELECT /*+label(myselectquery)*/ COUNT(*) FROM t;
INSERT /*+label(myinsertquery)*/ INTO t VALUES(1);

After you add a label to one or more queries, query the QUERY_PROFILES system table to see which queries ran with your supplied labels. The QUERY_PROFILES system table IDENTIFIER column returns the user-defined label that you previously assigned to a query. You can also obtain other query-specific data that can be useful for querying other system tables, such as transaction IDs.

For example:

=> SELECT identifier, query FROM query_profiles;
     identifier | query
  myselectquery | SELECT /*+label(myselectquery)*/ COUNT(*) FROM t;
  myinsertquery | INSERT /*+label(myinsertquery)*/ INTO t VALUES(1);
  myupdatequery | UPDATE /*+label(myupdatequery)*/ t SET a = 2 WHERE a = 1;
  mydeletequery | DELETE /*+label(mydeletequery)*/ FROM t WHERE a = 1;
                | SELECT identifier, query from query_profiles;
(5 rows)