Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?

PL/SQL “normalizes” the SQL text as part of compiling it. It removes whitepace and comments and uppercases – everything but literals. An effect of this is potential better exploitation of the SQL cache. It also reveals duplicated statements. In my demo-package, p_Employees (see bottom for link to github), I’ve added a repeated statement, which we can easily find using PL/Scope:

with duplicate_statements as (
  select sql_id
  from user_statements
  where sql_id is not null
  group by sql_id
  having count(*) > 1
)
select full_text, line, object_name
from user_statements
join duplicate_statements using ( sql_id);

FULL_TEXT                                               LINE    OBJECT_NAME
------------------------------------------------------- ------- -----------
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 	156	P_EMPLOYEES
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 	88	P_EMPLOYEES
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 	68	P_EMPLOYEES

The queries in the source code actually looks like this (and yes select * is usually bad coding):

SELECT *
INTO lrEmployee
FROM Employees
WHERE Employee_id = p_Employee_Id;

select /*p_Employees.Employee*/ *
into loEmployee
from Employees
where Employee_Id = p_Employee_Id;

SELECT /*Heavy*/ *
INTO lrEmployee
FROM Employees
WHERE Employee_Id = c.Employee_Id;

We can see that the formatting, comments and the into part are all removed and bind-variable names are replaced.

But – if we find one of these statements in gv$sql and wonder which of these occurrences are actually being run, how can we find that out?

Hints are a crucial part of a statement, so therefore they’re not removed. We also know that invalid hints are just ignored, and that can be used to tag the statements and make them unique. We simply just put in a tag/marker to tell us where the statement is coming from. Note the added plus (+) in the comment for this statement located in the procedure named “heavy”.

SELECT /*+Heavy*/ *
INTO lrEmployee
FROM Employees
WHERE Employee_Id = c.Employee_Id;

In user_statements, we can now see that this query has the hint included and also has gotten a new sql_id:

FULL_TEXT                                                  LINE    SQL_ID
---------------------------------------------------------- ------- -------------
SELECT /*+Heavy*/ * FROM EMPLOYEES WHERE EMPLOYEE_ID = :B1 156     8cun7tu4jfcph

The side effect is that this statement is now a hidden duplicate, very much harder to locate.

Be sure that you’re not using a real hint as the tag (check with v$sql_hint).

Oh – and by the way: This normalization process does only happen to SQL in database objects like packages, procedures, types and so forth. Oracle does not spend time on the formatting part for queries coming from clients. If I execute this statement from SQL Developer, we can find it in the SQL cache just as it was sent in:

select /*from SQLDev*/ * from employees where employee_id = 100;

select sql_text
from gv$sql s 
where s.sql_text like '%employees';

SQL_TEXT
---------------------------------------------------------------
select /*from SQLDev*/ * from employees where employee_id = 100

A good tip for queries being sent from the client, is to add a small comment, indicating where it’s coming from (source file, module, whatever makes sense). That makes locating it and fixing it way easier.

You can find the mentioned demo-package in my github repo: https://github.com/nicetheory/PL-Scope.

You may also find this post interesting: Find bad performing queries or code with PL/Scope.