Tip

Find duplicate queries

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 >

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.