RE: SQL with High memory

I think in the AWR or statspack there is "SQL ordered by Sharable Memory" and by 
version count sections, which may be helpful

In the recent past, we used the following script:
-----------------------------------
rem script: sql_nb.sql
rem  -- find sql not using bind variable
set linesize 200;
col "Total Memory" format 999,999,999
col "Average Memory" format 999,999,999
col Num_Statements format 999,999
col "TotExecs" format 99,999
col "Sample Hash Value" format 99999999
col "SQL" format a50

SELECT sum(sharable_mem)/1024/1024 "Total Memory MB" ,
       avg(sharable_mem) "Avee Memory" ,
       sum(parse_calls) "Total Parses",
       count(*) Num_Statements ,
       sum(executions) "TotExecs" ,
       min(hash_value) "Sample Hash Value" ,
       substr(sql_text,1,150) "SQL"
FROM v$sqlarea
 WHERE executions = 1
 GROUP BY substr(sql_text,1,150)
 HAVING count(*) > 10 and sum(sharable_mem) > 1024000
 ORDER BY 1  ;
-----------------------------------------------

I sometime use Tom Kyte's approach to identify sql not using bind varialbe too:

-------------
rem script: bind_var_diag.sql
rem
rem    Display the count of the identical sql statements in
rem    the shared pool after literals are replaced.
rem
rem    This helps understanding if bind variable is used or not by the 
application
rem
rem    A table called t1 and a function remove_constant will be created.
rem
rem    Literal string is replaced by '#'; number by '_at_'
rem
rem Ref : 
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1163635055580

rem
set echo off
prompt !!!  Execute in your own schema, going to drop table t1
prompt !!!  Ctrl-c to abort !
set echo on
pause;
drop table t1;
create table t1
as
select
  sql_text
-- , executions
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', '_at__at_@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('_at_',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);
col sql_text_wo_constants format a50
select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/
------------------

Yu "Denis" Sun


      

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