Find duplicate queries

This query prints out duplicate queries submitted by the same user, identified by v$session.client_info.

Users sometimes submit queries through an application mulitple times, especially in environments where they are not in a position to maintain/manage the queries they submit. This takes up resources (i.e., temp space, CPU, IO, etc) and causes performance degradation for all users. The query below is useful for reporting and managing duplicate queries. It prints out duplicate queries submitted by the same user, identified by v$session.client_info....

It has been tested on version 8.1.7.3.

Select client_info
       ,'alter system kill session '''||sid||','||b.serial#||'''' as Command
       ,MAX(DECODE(piece, 0,sql_text)) ||
        MAX(DECODE(piece, 1,sql_text)) ||
        MAX(DECODE(piece, 2,sql_text)) ||
        MAX(DECODE(piece, 3,sql_text)) ||
        MAX(DECODE(piece, 4,sql_text)) ||
        MAX(DECODE(piece, 5,sql_text)) ||
        MAX(DECODE(piece, 6,sql_text)) ||
        MAX(DECODE(piece, 7,sql_text)) ||
        MAX(DECODE(piece, 8,sql_text)) ||
        MAX(DECODE(piece, 9,sql_text)) ||
        MAX(DECODE(piece,10,sql_text)) ||
        MAX(DECODE(piece,11,sql_text)) ||
        MAX(DECODE(piece,12,sql_text)) ||
        MAX(DECODE(piece,13,sql_text)) ||
        MAX(DECODE(piece,14,sql_text)) ||
        MAX(DECODE(piece,15,sql_text)) ||
        MAX(DECODE(piece,16,sql_text)) ||
        MAX(DECODE(piece,17,sql_text)) ||
        MAX(DECODE(piece,18,sql_text)) ||
        MAX(DECODE(piece,19,sql_text)) ||
        MAX(DECODE(piece,20,sql_text)) ||
        MAX(DECODE(piece,21,sql_text)) ||
        MAX(DECODE(piece,22,sql_text)) ||
        MAX(DECODE(piece,23,sql_text)) ||
        MAX(DECODE(piece,24,sql_text)) ||
        MAX(DECODE(piece,25,sql_text)) ||
        MAX(DECODE(piece,26,sql_text)) ||
        MAX(DECODE(piece,27,sql_text)) ||
        MAX(DECODE(piece,28,sql_text)) ||
        MAX(DECODE(piece,29,sql_text)) ||
        MAX(DECODE(piece,30,sql_text)) ||
        MAX(DECODE(piece,31,sql_text)) ||
        MAX(DECODE(piece,32,sql_text)) ||
        MAX(DECODE(piece,33,sql_text)) ||
        MAX(DECODE(piece,34,sql_text)) ||
        MAX(DECODE(piece,35,sql_text)) ||
        MAX(DECODE(piece,36,sql_text)) ||
        MAX(DECODE(piece,37,sql_text)) ||
        MAX(DECODE(piece,38,sql_text)) ||
        MAX(DECODE(piece,39,sql_text)) ||
        MAX(DECODE(piece,40,sql_text)) ||
        MAX(DECODE(piece,41,sql_text)) ||
        MAX(DECODE(piece,42,sql_text)) ||
        MAX(DECODE(piece,43,sql_text)) ||
        MAX(DECODE(piece,44,sql_text)) ||
        MAX(DECODE(piece,45,sql_text)) ||
        MAX(DECODE(piece,46,sql_text)) ||
        MAX(DECODE(piece,47,sql_text)) ||
        MAX(DECODE(piece,48,sql_text)) ||
        MAX(DECODE(piece,47,sql_text)) ||
        MAX(DECODE(piece,49,sql_text)) ||
        MAX(DECODE(piece,50,sql_text)) ||
        MAX(DECODE(piece,51,sql_text)) ||
        MAX(DECODE(piece,52,sql_text)) ||
        MAX(DECODE(piece,53,sql_text)) ||
        MAX(DECODE(piece,54,sql_text)) ||
        MAX(DECODE(piece,55,sql_text)) ||
        MAX(DECODE(piece,56,sql_text)) ||
        MAX(DECODE(piece,57,sql_text)) ||
        MAX(DECODE(piece,58,sql_text)) ||
        MAX(DECODE(piece,59,sql_text)) ||
        MAX(DECODE(piece,60,sql_text)) ||
        MAX(DECODE(piece,61,sql_text)) ||
        MAX(DECODE(piece,62,sql_text)) ||
        MAX(DECODE(piece,63,sql_text)) ||
        MAX(DECODE(piece,64,sql_text)) ||
        MAX(DECODE(piece,65,sql_text)) ||
        MAX(DECODE(piece,66,sql_text)) ||
        MAX(DECODE(piece,67,sql_text)) ||
        MAX(DECODE(piece,68,sql_text)) ||
        MAX(DECODE(piece,69,sql_text)) ||
        MAX(DECODE(piece,70,sql_text)) as sql_text
from v$process a, v$session b, v$sqltext_WITH_NEWLINES c
WHERE  status  in ('ACTIVE','INACTIVExx')
and a.addr = b.paddr
and b.SQL_ADDRESS = c.ADDRESS
and b.SQL_HASH_VALUE = c.HASH_VALUE
and b.sid > 10
and (client_info,sql_text) in (select client_info,
    MAX(DECODE(piece, 0,sql_text)) ||
    MAX(DECODE(piece, 1,sql_text)) ||
    MAX(DECODE(piece, 2,sql_text)) ||
    MAX(DECODE(piece, 3,sql_text)) ||
    MAX(DECODE(piece, 4,sql_text)) ||
    MAX(DECODE(piece, 5,sql_text)) ||
        MAX(DECODE(piece, 6,sql_text)) ||
        MAX(DECODE(piece, 7,sql_text)) ||
        MAX(DECODE(piece, 8,sql_text)) ||
        MAX(DECODE(piece, 9,sql_text)) ||
        MAX(DECODE(piece,10,sql_text)) ||
        MAX(DECODE(piece,11,sql_text)) ||
        MAX(DECODE(piece,12,sql_text)) ||
        MAX(DECODE(piece,13,sql_text)) ||
        MAX(DECODE(piece,14,sql_text)) ||
        MAX(DECODE(piece,15,sql_text)) ||
        MAX(DECODE(piece,16,sql_text)) ||
        MAX(DECODE(piece,17,sql_text)) ||
        MAX(DECODE(piece,18,sql_text)) ||
        MAX(DECODE(piece,19,sql_text)) ||
        MAX(DECODE(piece,20,sql_text)) ||
        MAX(DECODE(piece,21,sql_text)) ||
        MAX(DECODE(piece,22,sql_text)) ||
        MAX(DECODE(piece,23,sql_text)) ||
        MAX(DECODE(piece,24,sql_text)) ||
        MAX(DECODE(piece,25,sql_text)) ||
        MAX(DECODE(piece,26,sql_text)) ||
        MAX(DECODE(piece,27,sql_text)) ||
        MAX(DECODE(piece,28,sql_text)) ||
        MAX(DECODE(piece,29,sql_text)) ||
        MAX(DECODE(piece,30,sql_text)) ||
        MAX(DECODE(piece,31,sql_text)) ||
        MAX(DECODE(piece,32,sql_text)) ||
        MAX(DECODE(piece,33,sql_text)) ||
        MAX(DECODE(piece,34,sql_text)) ||
        MAX(DECODE(piece,35,sql_text)) ||
        MAX(DECODE(piece,36,sql_text)) ||
        MAX(DECODE(piece,37,sql_text)) ||
        MAX(DECODE(piece,38,sql_text)) ||
        MAX(DECODE(piece,39,sql_text)) ||
        MAX(DECODE(piece,40,sql_text)) ||
        MAX(DECODE(piece,41,sql_text)) ||
        MAX(DECODE(piece,42,sql_text)) ||
        MAX(DECODE(piece,43,sql_text)) ||
        MAX(DECODE(piece,44,sql_text)) ||
        MAX(DECODE(piece,45,sql_text)) ||
        MAX(DECODE(piece,46,sql_text)) ||
        MAX(DECODE(piece,47,sql_text)) ||
        MAX(DECODE(piece,48,sql_text)) ||
        MAX(DECODE(piece,47,sql_text)) ||
        MAX(DECODE(piece,49,sql_text)) ||
        MAX(DECODE(piece,50,sql_text)) ||
        MAX(DECODE(piece,51,sql_text)) ||
        MAX(DECODE(piece,52,sql_text)) ||
        MAX(DECODE(piece,53,sql_text)) ||
        MAX(DECODE(piece,54,sql_text)) ||
        MAX(DECODE(piece,55,sql_text)) ||
        MAX(DECODE(piece,56,sql_text)) ||
        MAX(DECODE(piece,57,sql_text)) ||
        MAX(DECODE(piece,58,sql_text)) ||
        MAX(DECODE(piece,59,sql_text)) ||
        MAX(DECODE(piece,60,sql_text)) ||
        MAX(DECODE(piece,61,sql_text)) ||
        MAX(DECODE(piece,62,sql_text)) ||
        MAX(DECODE(piece,63,sql_text)) ||
        MAX(DECODE(piece,64,sql_text)) ||
        MAX(DECODE(piece,65,sql_text)) ||
        MAX(DECODE(piece,66,sql_text)) ||
        MAX(DECODE(piece,67,sql_text)) ||
        MAX(DECODE(piece,68,sql_text)) ||
        MAX(DECODE(piece,69,sql_text)) ||
        MAX(DECODE(piece,70,sql_text)) as sql_text
from v$process a, v$session b, v$sqltext_WITH_NEWLINES c
WHERE  status  in ('ACTIVE','INACTIVExx')
and a.addr = b.paddr
and b.SQL_ADDRESS = c.ADDRESS
and b.SQL_HASH_VALUE = c.HASH_VALUE
and b.sid > 10
group by sql_text,client_info
having count(*) > 1
)
group by client_info
   ,logon_time
   ,sid
   ,b.serial#
   ,spid
   ,b.status
   ,osuser;

Reader Feedback

Stephen B. writes: I read this tip, but I haven't tested out the SQL because quite simply I'm wondering why the heck you would be worried about having duplicate queries run. Duplicate queries are a good thing. One of the most costly (in terms of time) parts of most queries in an OLTP system (the only sort of system where that tip makes sense) is the parse phase. That is the phase where Oracle takes the query and works out how to run it. These plans are stored in the shared pool until they are aged out by new plans (and other stuff) going in to the pool. If the same query (exactly) is then run again Oracle can see that it has already been parsed so skips the parse phase and just reuses the old plan so saving time (and time = money).

Indeed one of the tuning methods available at the application development stage is to make sure that queries that need to be run often but with different values in the where clause use bind variables so the same SQL will be used each time (and therefore the same execution plan) with the variables being substituted in the run phase each time.

If it's about long running OLAP type queries then the same principles of plan reuse apply. Also a particular physical user with one session can only run one query at a time, yes they could start up multiple sessions and run the same query but if you have users doing that your problem is personnel not technical and should be addressed through training or, as a last resort, sackings.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in December 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close