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