Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Anyone using YAPP? Possible bug in yapp_rep?

RE: Anyone using YAPP? Possible bug in yapp_rep?

From: Bob Metelsky <>
Date: Fri, 09 May 2003 08:42:01 -0800
Message-ID: <>

> Actually, it will even work on 8.0 if you want to tweak it. :)

Hi Jared, thanks for helping however the scripts did not attach

Below are modified scripts that run on 8.1.6

I made several modifications and it actaully generates a cvs file rather than a ; delimited

What seems to be an issue right now is I can only gather 2-3 rows of data at the prompt

If I say start snap 100
End snap 110

I get an empty file

It dsnt seem to like more than 2 rows of data


How may rows is the script supposed to bring in? Any ideas on that usage?


Working scripts of 8.1.6


####################yapp_rep.sql ####################

rem @F:\projects\yapp\DOS\yapp_rep.sql
rem YAPPPACK - by Miracle A/S
rem Version history
rem 21-jun-2002 Release 1.0

clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 60 linesize 4000 newpage 2 recsep off;
set trimspool on trimout on;

column inst_num  heading "Inst Num"    new_value inst_num  format 99999;
column inst_name heading "Instance"  new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format
9999999999 just c;
select d.dbid            dbid

, db_name
, i.instance_number inst_num
, i.instance_name inst_name
from v$database d, v$instance i; variable dbid number;

variable inst_num number;
variable inst_name varchar2(20);
variable db_name varchar2(20);
  :dbid      :=  &dbid;
  :inst_num  :=  &inst_num; 
  :inst_name := '&inst_name';
  :db_name   := '&db_name';

--  Ask for the snapshots Id's which are to be compared

set termout on;
column instart_fmt noprint;
column versn noprint    heading 'Release'  new_value versn;
column host_name noprint heading 'Host'    new_value host_name;
column para  noprint    heading 'OPS'      new_value para;
column level format 99  heading 'Snap|Level';
column snap_id      	heading 'Snap|Id' format 9990;
column snapdat      	heading 'Snap Started' just c	format a17;
column comment          heading 'Comment' format a22;
break on inst_name on db_name on instart_fmt;
ttitle lef 'Completed Snapshots' skip 2;

select di.instance_name                                  inst_name

, di.host_name host_name
, di.db_name db_name
, to_char(s.startup_time,' dd Mon "at" HH24:mi:ss') instart_fmt
, s.snap_id
, to_char(s.snap_time,'dd Mon YYYY HH24:mi') snapdat
, s.snap_level "level"
, substr(s.ucomment, 1,60) "comment"
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid and di.dbid = :dbid and s.instance_number = :inst_num and di.instance_number = :inst_num order by db_name, instance_name, snap_id; clear break; ttitle off; prompt prompt prompt Specify the Begin and End Snapshot Ids prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Begin Snapshot Id specified: &&begin_snap prompt prompt End Snapshot Id specified: &&end_snap prompt set termout on; variable bid number; variable eid number; variable versn varchar2(10); variable para varchar2(9); variable host_name varchar2(64); begin :bid := &&begin_snap; :eid := &&end_snap; :versn := '&versn'; :para := '&para'; :host_name := '&host_name'; end; / -- -- Use report name if specified, otherwise prompt user for output file -- name (specify default), then begin spooling set termout off; column dflt_name new_value dflt_name noprint; select 'C:\yapp_'||:bid||'_'||:eid||'.csv' dflt_name from dual; set termout on; prompt prompt Specify the Report Name prompt ~~~~~~~~~~~~~~~~~~~~~~~ prompt The default report file name is &dflt_name.. To use this name, prompt press <return> to continue, otherwise enter an alternative. set heading off; column report_name new_value report_name noprint; select 'Using the report name ' || nvl('&&report_name','&dflt_name')
, nvl('&&report_name','&dflt_name') report_name
from sys.dual; set heading on; prompt set termout off column dflt_decsep new_value dflt_decsep noprint; select '.' dflt_decsep from dual; set termout on prompt prompt Specify the decimal separator prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt The default decimal separator is a '&dflt_decsep'. To use this press <return> prompt to continue, otherwise enter a single alternative character. set heading off; column decsep new_value decsep noprint; select 'Using the decimal separator: ' || nvl('&&decsep','&dflt_decsep')
, nvl('&&decsep','&dflt_decsep') decsep
from dual; prompt alter session set nls_numeric_characters = '&&decsep='; -- -- Verify begin and end snapshot Ids exist for the database, and that -- there wasn't an instance shutdown in between the two snapshots -- being taken. set heading off; select 'ERROR: Database/Instance does not exist in STATS$DATABASE_INSTANCE' from dual where not exists (select null from stats$database_instance where instance_number = :inst_num and dbid = :dbid); select 'ERROR: Begin Snapshot Id specified does not exist for this database/instance' from dual where not exists (select null from stats$snapshot b where b.snap_id = :bid and b.dbid = :dbid and b.instance_number = :inst_num); select 'ERROR: End Snapshot Id specified does not exist for this database/instance' from dual where not exists (select null from stats$snapshot e where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num); rem select 'WARNING: timed_statitics setting changed between begin/end snaps: TIMINGS ARE INVALID' rem from dual rem where not exists rem (select null rem from stats$parameter b rem , stats$parameter e rem where b.snap_id = :bid rem and e.snap_id = :eid rem and b.dbid = :dbid rem and e.dbid = :dbid rem and b.instance_number = :inst_num rem and e.instance_number = :inst_num rem and = rem and = 'timed_statistics' rem and b.value = e.value); select 'WARNING: Snapshots chosen span an instance shutdown: Intervals will be omitted' from dual where not exists (select null from stats$snapshot b , stats$snapshot e where b.snap_id = :bid and e.snap_id = :eid and b.dbid = :dbid and e.dbid = :dbid and b.instance_number = :inst_num and e.instance_number = :inst_num and b.startup_time = e.startup_time); set heading on; set newpage 1 heading on; @@yapp_run set feedback off echo off verify off heading off pagesize 0 spool &report_name; select csvline from yapp_csv order by id; spool off Exit ########## yapp_run.sql ############################### declare -- -- YAPPPACK - by Miracle A/S -- Version history -- -- 21-jun-2002 Release 1.0 -- -- This PL/SQL block will do the whole thing of yapp -- reporting of your statspack data bv yapp_rep.number_array; -- to hold begin values ev yapp_rep.number_array; -- to hold end values dv yapp_rep.number_array; -- to hold the difference it date; -- instance start time of previous snap st date; -- snap_time of previous snap bi number; -- snap id of previous snap dbix number; insn number; bid number; -- begin snap_id eid number; -- end snap_id state number; -- 0: just started, 1: values found in bv, etc. i binary_integer; tval number; -- Unit of timing (1/100 or 1/1000000) vstr varchar2(8) :=8; --stats$database_instance.version%type; csv yapp_csv.csvline%type; begin -- Note that certain variables must exist in SQL*Plus -- Don't run this alone, but only from yapp_rep state := 0; dbix := :dbid; insn := :inst_num; bid := :bid; eid := :eid; -- -- Put a heading line into yapp_csv containing the column headings csv := null; for evtxt in (select c, event name from yapp_event union select c, name from yapp_sysstat order by c) loop if csv is null then csv := 'timestamp ,' ||; else csv := csv || ',' ||; -- csv := csv || ',' ||; end if; end loop; insert into yapp_csv(id, csvline) values (0, csv); -- for snaps in (select s.snap_time, s.snap_id, s.startup_time from stats$snapshot s where s.dbid = dbix and s.instance_number = insn and s.snap_id between bid and eid) loop -- loop over all snapshots in the selected interval if state = 0 then -- This is the very start -- Which Oracle version -- select version into vstr -- from stats$database_instance i -- where i.dbid = dbix -- and i.instance_number = insn -- and i.startup_time = snaps.startup_time; if vstr like '8%' then tval := 100; -- cs in version 8 else tval := 1000000; -- microseconds later end if; -- yapp_rep.get_vals(bv, dbix, insn, snaps.snap_id); it := snaps.startup_time; st := snaps.snap_time; bi := snaps.snap_id; state := 1; else -- We already have a set of values in bv -- see if the new snapshot is at the same instance start time if snaps.startup_time = it then -- cool - we can calculate values yapp_rep.get_vals(ev, dbix, insn, snaps.snap_id); -- and their difference and fill up the csv line yapp_rep.csv_diff(bv, st, ev, snaps.snap_time, dv); -- put the date so excel can understand it csv := to_char(st-to_date('01.01.1900 00:00:00','DD.MM.YYYY HH24:MI:SS'), '9999999D999999999'); for i in dv.first .. dv.last loop csv := csv || ',' || to_char(dv(i)/tval, '9999999D9999'); end loop; -- Write the data to our table insert into yapp_csv(id, csvline) values (yapp_seq.nextval,csv); -- save newest in bv, etc bv := ev; it := snaps.startup_time; st := snaps.snap_time; bi := snaps.snap_id; else -- Instance was started in between ignore last value set yapp_rep.get_vals(bv, dbix, insn, snaps.snap_id); it := snaps.startup_time; st := snaps.snap_time; bi := snaps.snap_id; end if; end if; end loop; -- do not commit - we are saving lines in a transaction temporary table end; / -- Please see the official ORACLE-L FAQ: -- Author: Bob Metelsky INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Received on Fri May 09 2003 - 11:42:01 CDT

Original text of this message