Category Archives: Tuning

Unshared Queries

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/02/24
-- # Name: unshared_queries.sql
-- ################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ################################################
-- #
-- # Display unshared queries
-- # To find the reason look at V$SQL_SHARED_CURSOR :
-- # http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
-- #
-- ################################################
 
set lines120 pages2000
spool non_shared.log
SELECT sysdate, a.hash_value, a.version_count , a.users_opening , a.users_executing, a.sharable_mem, a.sql_text, c.*
FROM v$sqlarea a, v$sql_shared_cursor c
WHERE a.address=c.address
and a.version_count > 5
order by a.version_count desc;
spool off

PGA Target advice histogram

 
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2010/01/01
-- # Name: pga_target_advice_histogram.sql
--  ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # First statement: Display pga target advice histogram
-- # Second statement: Display pga target advice (%cache hit)
-- #
-- ################################################
 
SELECT
   low_optimal_size/1024 "Low(K)",
   (high_optimal_size+1)/1024 "High(K)",
   estd_optimal_executions "Optimal",
   estd_onepass_executions "One Pass",
   estd_multipasses_executions "Multi-Pass"
FROM
   v$pga_target_advice_histogram
WHERE
   pga_target_factor = 2
AND
   estd_total_executions != 0
ORDER BY
   1;

column c1     heading 'Target(M)'
column c2     heading 'Estimated|Cache Hit %'
column c3     heading 'Estimated|Over-Alloc.'
 
SELECT
   ROUND(pga_target_for_estimate /(1024*1024)) c1,
   estd_pga_cache_hit_percentage         c2,
   estd_overalloc_count                  c3
FROM
   v$pga_target_advice;

Quick tune

This script shows the different memory pools of the SGA and some indicative ratios for the Oracle DBA

-- ################################################
-- # Creator: Ph de Saint Aignan
-- # Created: 2002/06/02
-- # Name: quicktune.sql
--  ################################################
-- #
-- # Compatible: Oracle 7 8i 9i 10g 11g
-- #
-- ################################################

set echo off linesize 200 pagesize 100 wrap off timing off doc off

COL "MEMORY TYPE" 		FOR A16 
--HEAD "SGA + RATIOS"
--COL "Total_Mem(Ko)" 	FOR A12 
--COL "Free(Ko)"	        FOR A8
COL "|||"		FOR A3
COL "MEMORY RATIOS"	FOR A28
COL "RATIO %"		FOR A7
COL IDEAL 		FOR A8 ;
compute sum of "Total_Mem K" on report
compute sum of "Free K" on report
break on report ;

select pool "MEMORY TYPE", Total_Mem "ALLOCATION K",  Free_Mem "FREE K", '|||' "|||", 
RUBRIQUE "MEMORY RATIOS", to_char(round(RATIO*100,1),'999.9') "RATIO %", IDEAL
from 
------------------First  subset Construction :
(select rownum0,A.pool, A.Total_Mem  , B.Free_Mem  
from
(select (rownum ) rownum0, A.* from (
   select pool, round(sum(bytes)/1024,0) Total_Mem from v$sgastat
   where pool is not null group by pool 
   UNION 
   select name, round(bytes/1024)  from v$sgastat where pool is null and name !='fixed_sga') A
UNION ALL
select 6,'Sort Area Size' ,round(value/1024,0)  from v$parameter 
where  name in ('sort_area_size') 
UNION ALL
select 7,'Hash Area Size' ,round(value/1024,0)  from v$parameter 
where  name in ('hash_area_size') ) 
 A ,
(select pool, round(bytes/1024,0) Free_Mem from v$sgastat 
        where name = 'free memory'
        UNION ALL
 select 'db_block_buffers',
           (select count(*)   from v$bh  where status='free')* 
           (select (round(value/1024,0))  from v$parameter where name = 'db_block_size')
           from dual )
          B
where A.pool=B.pool(+)  ) SGA ,
------------------2nd  subset Construction :
(select 6 rownum0, 'DATA DICTIONARY CACHE' "RUBRIQUE", 
	sum(getmisses)/sum(gets) "RATIO", ' < 15 %' "IDEAL"
from v$rowcache
UNION ALL
select 3,'SHARED POOL HIT RATIO',sum(pinhits-reloads)/sum(pins),' > 85 %'
from v$librarycache
UNION ALL
select 4 ,'SHARED POOL RELOAD %',sum (reloads)/sum(pins), ' <  2 %'
from v$librarycache
UNION ALL
select 2,'BUFFER CACHE Hit Ratio',
     (1-(sum (decode (name, 'physical reads',value,0))/
    (sum(decode(name,'db block gets',value,0)) +
    (sum(decode(name,'consistent gets',value,0)))))
    ),  ' > 95 %'
from v$sysstat 
UNION ALL
select 1,'BUFFER CACHE MISS RATIO',
((G-F)/(G-F+C+E)),' < 15 %'
from
(select sum(value) C  from v$sysstat where name like '%- consistent read gets') c,
--http://www.ixora.com.au/tips/tuning/cache_miss.htm : Steve Adams
(select value E from v$sysstat where name = 'db block gets') e,
(select value F from v$sysstat where name = 'physical reads direct') f,
(select value G from v$sysstat where name = 'physical reads') g
UNION ALL
select 5, 'LOG BUFFER REQUESTS Ratio', -- '#Redo Space requests/#redo entries'
((req.value * 50)/entries.value), 
' < 0.02%' 
from v$sysstat req, v$sysstat entries
where req.name='redo log space requests' and entries.name ='redo entries'
UNION ALL
select 7,'MEM SORTS/TOTAL SORTS',
mem.value/(mem.value+disk.value),' > 95 %'
from v$sysstat mem, v$sysstat disk
 where mem.name = 'sorts (memory)'
 and disk.name = 'sorts (disk)' 
)  RATIOS
where SGA.rownum0(+) = RATIOS.ROWNUM0
order by SGA.rownum0 asc;

--@defaultenv

Rebuild index

Author: Vincent Fenoll, DBA Oracle Montreal

-- ################################################
-- # Created: 2005/01/01
-- # Name: rebuild_indx.sql
-- ################################################
-- #
-- # Compatible: 7 8i 9i 10g 11g
-- #
-- ################################################

spool rebuild_indx.log

drop table v_perso;
create table v_perso (text varchar2(500), index_name varchar2(100));

prompt
ACCEPT spoolfile CHAR prompt 'Fichier de spool : ';
ACCEPT schema CHAR prompt 'Schema (% autorisé) : ';
prompt
prompt
prompt Il faut reconstruire un index quand :
prompt   - le nombre d entrees detruites represente 20% ou plus des entrees courantes
prompt   - la profondeur de l index est superieure a 3
prompt Les candidats possibles pour des index bitmaps :
prompt   - lorsque les donnees sont distinctes a plus de 99%
prompt
spool &spoolfile

set serveroutput on
set verify off
declare
 c_name        INTEGER;
 ignore        INTEGER;
 height        index_stats.height%TYPE := 0;
 lf_rows       index_stats.lf_rows%TYPE := 0;
 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
 distinct_keys index_stats.distinct_keys%TYPE := 0;
 cursor c_indx is
  select owner, table_name, index_name
  from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
begin
 dbms_output.enable (1000000);
 dbms_output.put_line ('Owner           Index Name                              % Deleted Entries Blevel Distinctiveness');
 dbms_output.put_line ('--------------- --------------------------------------- ----------------- ------ ---------------');

 c_name := DBMS_SQL.OPEN_CURSOR;
 for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);

  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
         into height, lf_rows, del_lf_rows, distinct_keys
  from index_stats;
--
-- Index is considered as candidate for rebuild :
--   - when deleted entries represent 20% or more of the current entries
--   - when the index depth is more then 2 levels.(height starts counting from 1 so > 5)
-- Index is (possible) candidate for a bitmap index when :
--   - distinctiveness is more than 99%
--
-- VFL ajout de and (del_lf_rows/lf_rows) <> 1) pour enlever les indexes vides
  if ( height > 2 ) OR ( (del_lf_rows/lf_rows) > 0.2 and (del_lf_rows/lf_rows) <> 1) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
    -- VFL
    insert into v_perso values ('ALTER INDEX '|| r_indx.owner || '.' || r_indx.index_name || ' REBUILD;', r_indx.index_name );
  end if;

 end loop;
 DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

select text from v_perso;
-- drop table v_perso;
spool off
set verify on

Resources waits

REM # Creator: Vincent Fenoll – DBA Oracle Montreal

REM ################################################
REM # Created: 2005/01/01
REM # Name: resources_waits.sql
REM  ################################################
REM #
REM # Compatible: Oracle 7 8i 9i 10g 11g
REM#
REM ################################################
REM #
REM # Display importants waits events in our Oracle databases in Montreal
REM #
REM ################################################
column "Average wait cs" format 9999990.00 heading "Attente moyenne|en cent-secondes"
column "Time waited seconds" format 999,999,999,990.00 heading "Temps attendu|en secondes"
colum event format a35 heading "Evenement" truncate
select
  substr(e.event, 1, 40)  event,
  e.time_waited/100 "Time waited seconds",
  e.time_waited / decode(
    e.event,
    'latch free', e.total_waits,
    decode(
      e.total_waits - e.total_timeouts,
      0, 1,
      e.total_waits - e.total_timeouts
    )
  ) "Average wait cs"
from
  sys.v_$system_event  e,
  sys.v_$instance  i
where
  e.event = 'buffer busy waits' or
  e.event = 'enqueue' or
  e.event = 'free buffer waits' or
  e.event = 'global cache freelist wait' or
  e.event = 'latch free' or
  e.event = 'log buffer space' or
  e.event = 'parallel query qref latch' or
  e.event = 'pipe put' or
  e.event = 'write complete waits' or
  e.event like 'library cache%' or
  e.event like 'log file switch%' or
  e.event = 'log file sync' or
  ( e.event = 'row cache lock' and
    i.parallel = 'NO'
  )
order by "Time waited seconds" desc
/