RE: figure out which queries are not using bind variables?

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 4 May 2006 20:09:28 +0100
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC0BF94135@ENYC11P32005.corpny.csfb.com>

Apologies...the second SQL should be:

select t2.sql_text from v$sql t1, v$sqltext t2 where t1.plan_hash_value = &plan_hash_value and t1.hash_value = t2.hash_value
order by t2.hash_value, t2.piece
/

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Baumgartel, Paul Sent: Thursday, May 04, 2006 3:07 PM
To: ryan_gaffuri_at_comcast.net
Cc: oracle-l_at_freelists.org
Subject: RE: figure out which queries are not using bind variables?

To the best of my knowledge, Oracle does not maintain a directory of non-bind SQL. You have to inspect the contents of the shared pool. One way is to find queries that have the same plan hash value:

break on plan_hash_value
select plan_hash_value, count(*) from v$sql where plan_hash_value > 0
group by plan_hash_value having count(*) > 4 order by count(*)
/

(there was a suggestion on the list that a clause be added to this to ensure that the SQL is really the same, as very different SQL often gets the same plan...I don't remember details but I'm sure you can find it in the archives.)

Then you can get the SQL with

break on plan_hash_value
select plan_hash_value, count(*) from v$sql where plan_hash_value > 0
group by plan_hash_value having count(*) > 4 order by count(*)
/

There is also a Tom Kyte script that returns SQL with the literals replaced by @ (for character literals) and # (for numeric literals):

drop table t1;

create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2 as

    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;

begin

    for i in 1 .. length( p_query )
    loop

        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;

    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );     for i in 0 .. 8 loop

        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );

    end loop;
    return upper(l_query);
end;
/

update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Alex Gorbachev Sent: Thursday, May 04, 2006 3:01 PM
To: ryan_gaffuri_at_comcast.net
Cc: oracle-l_at_freelists.org
Subject: Re: figure out which queries are not using bind variables?

Recently, in this list there was a proposal to group sql statements by execution plan. Chances are that they are actually the same! Not a 10g feature though, or not a feature at all.

2006/5/4, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net>: >
> Oracle 10g. Does Oracle track which queries do not use bind variables?

--
Best regards,
Alex Gorbachev

http://oracloid.blogspot.com
--
http://www.freelists.org/webpage/oracle-l



==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

--
http://www.freelists.org/webpage/oracle-l



==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 04 2006 - 14:09:28 CDT