Shared pool queries

-- Whos using pga

SELECT sid,  trunc((VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS", value
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session pga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
    order by 3 desc;

SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session pga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;

 

select * --count(distinct kghluidx) num_subpools
    from sys.x$kghlu
  where kghlushrpool = 1;

  select * --child#, gets
   from v$latch_children
   where name = 'shared pool'
  order by child#;

select child#, gets
   from v$latch_children
   where name = 'shared pool'
   order by child#;

select * from V$SGASTAT;

select * from v$sgastat
  where pool = 'shared pool'
  and name = 'free memory';

  select name, (bytes/1024/1024/1024), resizeable from v$sgainfo;

 

More Memory queries

SELECT sid, value/1024/1024 mb  -- || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#
    and value/1024/1024 > 3;
SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MAX MEM FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory max'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT count(*), trunc(SUM(VALUE/1024/1024)) || ' MB' "TOTAL MEMORY FOR ALL SESSIONS"
    FROM V$SESSTAT, V$STATNAME
    WHERE NAME = 'session uga memory'
    AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
select * from v$session where sid = 2514;

 

More queries on sqlarea for binds

select plan_hash_value, count(*) from v$sqlarea group by plan_hash_value having count(*) > 2000 order by count(*) desc ;
select sql_text, count(*)  from v$sqlarea group by sql_text; where plan_hash_value = 0 order by first_load_time ;

select substr(sql_text,1,40), count(*)  from v$sqlarea group by substr(sql_text,1,40) order by 2 desc; where upper(sql_text) like '%%' and upper(sql_text) like '%%' and first_load_time > '2012-08-02/11:30:00';  where last_load_time between '07/30/2012 17:00:00' and  '07/30/2012 19:00:00';

select plan_hash_value, first_load_time , last_load_time, loads, sql_text  from v$sqlarea where plan_hash_value = 0 and upper(sql_text) like '%%' order by last_load_time
                                                                                                  ;
select *
from v$sqlarea
where plan_hash_value = 0
and substr(sql_text,1,50) = ‘%’;
select * from error table where err_date>sysdate-365   and instr(err_message,'ORA-04031')>0 order by err_id;

 

Stats

SELECT *
  FROM stats$sysstat a, stats$snapshot b
WHERE --a.name = 'logons cumulative'
a.snap_id = b.snap_id
and b.snap_time = '08/01/2012 09:55:56'
and name like '%ses%'
;