Trying to compile the PRO*C utils from Guy Harrison
From: <chiappa_at_my-deja.com>
Date: Mon, 04 Dec 2000 13:39:13 GMT
Message-ID: <90g6q1$sfe$1_at_nnrp1.deja.com>
}
}
location data */
}
}
}
ROLLBACK', 3, 'TEMPORARY',
}
return(-1);
}
}
}
Date: Mon, 04 Dec 2000 13:39:13 GMT
Message-ID: <90g6q1$sfe$1_at_nnrp1.deja.com>
[Quoted] Hi ! I downloaded the source-code and was trying to compile the tk_waits.pc utility (created and copyrighted-by the Oracle-expert Guy Harrison), to calculate and extract the wait-data present on the trace files (generated with level 4 or 12), to use it in some performancerelated investigations. But it needs Microsoft C to compile (mainly because the getopt function) and I have only Borland C in my personal machine, and IBM C Compiler in the AIX developer´s machine. So, I want to know :
- anyone knows if it´s possible to compile it in one of the C compiler above ? If yes, how to do ?
- anyone know another utils to extract the data ignored by tkprof in [Quoted] 7.x (as everyone knows, until 7.1 the tkprof read and display the waitevents [Quoted] and times present in a trace-file, but in the before-releases don´t.) ?
[]s
J. Laurindo Chiappa
OBS : follows the mencioned code :
/*
$Source: /u2/gharriso/Src/RCS/tk_waits.pc,v $ $Revision: 1.4 $ $Author: gharriso $
Analyse an ORACLE trace file and report on data (currently) ignored by tkprof (as of 7.1)
Most useful in conjunction with event 10046 which records wait events in the trace file
To build:
make -f $ORACLE_HOME/proc16/lib/proc16.mk tk_waits
OR
make -f $ORACLE_HOME/proc/lib/proc.mk EXE=tk_waits OBJS=tk_waits.o
You need to be a DBA or have SELECT ANY TABLE privelege or SELECT on DBA_DATA_FILES,$OBJ, etc ,etc
--------------------+-----------------+------------------------------
Guy Harrison | January 1996 | Initial
| |
*/
#define ORACA_INIT
#include <stdio.h> #include <stdlib.h> #include <string.h>
EXEC ORACLE OPTION (ORACA=YES);
EXEC ORACLE OPTION (SELECT_ERROR=NO);
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE ORACA;
EXEC SQL WHENEVER SQLERROR DO oerr();
EXEC SQL BEGIN DECLARE SECTION;
char event_name[65]; /* Name of the system
event */
EXEC SQL VAR event_name IS STRING(65);
char *uid; /* connection string */
EXEC SQL VAR uid IS STRING(61);
char seg_type[30]; /* Segment type */
EXEC SQL VAR seg_type IS STRING(31);
char object_name[61]; /* Object name */
EXEC SQL VAR object_name IS STRING(61);
char latch_name[51]; /* latch name */
EXEC SQL VAR latch_name IS STRING(51);
char segment_type[20];
EXEC SQL VAR segment_type IS STRING(20);
char tablespace_name[31];
EXEC SQL VAR tablespace_name IS STRING(31);
char object_type[31]; /* Object type */
EXEC SQL VAR object_type IS STRING(31);
unsigned long p1,p2,p3,ela; /* fields from
the WAIT# line (see */
long segfile,segblock,objno; /* the session_waits table
*/
EXEC SQL END DECLARE SECTION;
#include "tk_waits.h" /* local declarations and function protos */
main (int argc, char *argv)
{
init(argc,argv); /* Process command line, connect etc */ process_file(); /* read and store data in the trace file */ report(); /* Report to standard output */ exit(0);
}
void init(int argc,char *argv[])
{
/**
** Process command line arguments and connect to ORACLE
*/
int c; /* argument */
extern char *optarg; /* argument value (char)*/
if (argc==1) usage();
while((c=getopt(argc,argv,"f:v:d"))!=EOF)
{
switch(c)
{
case 'f':
trace_file=(char *)strdup(optarg);
break;
case 'v':
uid=(char *)strdup(optarg);
verbose=1;
break;
case 'd':
debug=1;
break;
case '?':
usage();
}
}
/**
** Open the trace file for reading
*/
if ((trace_file_p=fopen(trace_file,"r"))==NULL)
{
fprintf(stderr,"Can't open %s for reading\n",
trace_file);
exit(1);
}
/**
** Only need to connect to ORACLE if in verbose mode
*/
if (verbose)
{
/**
** Connect to ORACLE
*/
EXEC SQL CONNECT :uid;
if (debug)
{
EXEC SQL ALTER SESSION SET SQL_TRACE TRUE;
}
}
}
void process_file()
{
/**
** Read the trace file, storing or interpreting contents
*/
char input_line[255];
char fixed_input_line[255];
int csr;
int pos;
char *s1,*s2;
char c;
s2=malloc(256);
while(fgets(input_line,255,trace_file_p)!=NULL)
{
/**
** Only process lines with WAIT
*/
if (strncmp(input_line,"WAIT #",6) ==0)
{
/**
** 7.0 trace files use " rather than ', so
** substitute " with '
**
*/
repstr(fixed_input_line,input_line,'"','\'');
/* Look for the "nam=" markter" */
if ((s1=strstr(fixed_input_line,"nam="))==NULL)
continue;
pos=instr(s1+5,'\'');
strncpy(event_name,s1+5,pos);
event_name[pos]='\0';
/**
** Ignore "idle" events
*/
if (strcmp(event_name,"client message")==0 ||
strcmp(event_name,"SQL*Net message from
client")==0)
continue;
/**
** Extract elapsed times and p1,p2
*/
if ((s1=strstr(s1,"ela="))==NULL)
continue;
pos=instr(s1+4,'p');
strncpy(s2,s1+4,pos);
ela=atoi(s2);
if ((s1=strstr(s1,"p1="))==NULL)
continue;
pos=instr(s1+3,'p');
strncpy(s2,s1+3,pos);
p1=atoi(s2);
if ((s1=strstr(s1,"p2="))==NULL)
continue;
pos=instr(s1+3,'p');
strncpy(s2,s1+3,pos);
p2=atoi(s2);
if (debug) printf("\n%s\n",input_line);
/**
** accumulate the events to an array
*/
store_totals(event_name,ela,p1,p2);
}
}
fclose(trace_file_p);
}
void store_totals(char *event_name , long ela,long p1,long p2)
{
/**
** Store the totals for each wait category
*/
int i;
char *location;
int found=0;
total_count++;
total_elapsed+=ela;
/**
** FIrst, store overall totals for each event
*/
for (i=0;i<=event_array_max;i++)
{
if (strcmp(event_array[i].event_name,event_name)==0)
{
/*
** Matching entry for this wait category
already exists
*/
event_array[i].count++;
event_array[i].elapsed+=ela;
if (debug)
printf("Incrementing %s=%
d\n",event_name,event_array[i].count);
found=1;
break;
}
}
/**
** If we get here, then this is the first entry of it's type
*/
if (found==0)
{
event_array_max++;
event_array[event_array_max].event_name=(char *)strdup
(event_name);
event_array[event_array_max].count=1;
event_array[event_array_max].elapsed=ela;
}
if (verbose)
{
/** -------------------------------------------------
** Now, store totals for each resource waited on, when
** the resource can be dereferenced (eg file/block,
latch#,
** etc)
** -----------------------------------------------*/
/**
** First get the location (eg latch, segment, etc)
*/
location=get_location(event_name);
if (debug) printf("location=%s\n",location);
found=0;
for (i=0;i<=event_detail_array_max;i++)
{
if (strcmp(event_detail_array
[i].event_name,event_name)==0 &&
strcmp(event_detail_array
[i].location,location)==0)
{
/*
** Matching entry for this wait/p1/p2
category already exists
*/
event_detail_array[i].count++;
event_detail_array[i].elapsed+=ela;
found=1;
break;
}
}
/**
** If we get here, then this is the first entry of
it's type
*/
if (found==0)
{
event_detail_array_max++;
event_detail_array
[event_detail_array_max].event_name=(char *)strdup(event_name);
event_detail_array
[event_detail_array_max].location=(char *)strdup(location);
event_detail_array
[event_detail_array_max].count=1;
event_detail_array
[event_detail_array_max].elapsed=ela;
}
}
}
void sort_events()
{
/**
** Sort events in decreasing order of elapsed time
*/
int i;
for(i=1;i<=event_array_max;i++)
{
sorted_events[i-1]=i;
}
qsort((void *) &sorted_events[0],event_array_max,sizeof
(int),compare_events);
}
int sort_event_details(char *event_name)
{
/**
** Sort resources for a given event (if knowable)
*/
int i,j,k;
j=0;
elapsed_sub_total=0;
count_sub_total=0;
for (i=1;i<=event_detail_array_max;i++)
{
if (strcmp(event_detail_array[i].event_name,event_name)
==0)
{
/**
** If it looks like there is no location
data, return
** -1 to indicate this to the calling module
*/
if (strncmp(event_detail_array[i].location,"Not
A",5)==0)
return(-1); /* No
location data */
sorted_event_details[j++]=i;
}
}
if (debug)
printf("About to sort %s: %d elements\n",event_name,j);
qsort((void *) &sorted_event_details[0],j,sizeof
(int),compare_details);
return(j-1);
}
int compare_details(const void *arg1,const void *arg2)
{
/**
** COmparison routine to support sort_event_details()
*/
int i,j,rc;
i=*((int*) arg1);
j=*((int*) arg2);
if (debug)
printf("Comparing elements %d with %d\n",i,j);
if (event_detail_array[i].elapsed > event_detail_array
[j].elapsed)
rc=-1;
else if (event_detail_array[i].elapsed == event_detail_array
[j].elapsed)
rc=0;
else
rc=1;
if (debug)
printf("compare %d %d:%d\n",
event_array[i].elapsed,event_array[j].elapsed,rc);
return(rc);
}
int compare_events(const void *arg1,const void *arg2)
{
/**
** Comparison routine to compare sort_events()
*/
int i,j,rc;
i=*((int*) arg1);
j=*((int*) arg2);
if (event_array[i].elapsed > event_array[j].elapsed)
rc=-1;
else if (event_array[i].elapsed > event_array[j].elapsed)
rc=0;
else
rc=1;
if (debug)
printf("compare %d %d:%d\n",
event_array[i].elapsed,event_array[j].elapsed,rc);
return(rc);
}
char *get_location(char *event)
{
/**
** Get the resource or "location" of a particular wait. For
instance,
** in the case of db file sequential read, work out the
segment
involved
*/
static char return_value[120];
char *location;
if (strncmp(event,"db file",7)==0 ||
strncmp(event,"free buffer",11)==0 ||
strncmp(event,"buffer busy",11)==0 ||
strncmp(event,"write compl",11)==0 )
{
location=get_object();
}
else if (strncmp(event,"latch free",10)==0)
{
location=get_latch();
}
else if (strncmp(event,"enqueue",7)==0)
{
location=get_lock();
}
else
{
location=(char *)strdup("Not Applicable ");
}
strcpy(return_value,location);
free(location);
return(return_value);
}
char *get_latch()
{
/**
** Get the latch name
*/
static char location[120];
char buffer[120];
EXEC SQL SELECT NAME
into :latch_name
FROM V$LATCH
WHERE LATCH#=:p2;
if (sqlca.sqlcode > 0 )
strdup("Unknown latch");
else
strcpy(location,latch_name);
return(location);
}
char *get_lock()
{
/** ** Get the name of an object being locked (this is only possible ** for explicit locks) ** */ static char location[90]; char *type; EXEC SQL SELECT OWNER||'.'||OBJECT_NAME INTO :object_name FROM SYS.DBA_OBJECTS WHERE OBJECT_ID=:p2; type=(char *)get_lock_type(p1); if (sqlca.sqlcode>0) strcpy(location,type); else sprintf(location,"%s %s",type,object_name); return(location);
}
char *get_lock_type(long p1)
{
/** ** Get the lock type which is stored in p1. ** first two lots of 8 bits is lock type. Last 8 bits is lock mode */ char a,b; unsigned long num; int mode; char long_type[60]; char long_mode[30]; char type[2]; static char return_value[90]; num=p1; a=(num>>24)& 0x000000ff; /* First 8 bits */ b=(num>>16)& 0x000000ff; /* Second 8 bits */ mode=(num)& 0x000000ff; /* Last 8 bits */ sprintf(type,"%c%c",a,b); if (strcmp(type,"MR")==0) strcpy(long_type,"Media Recovery"); else if (strcmp(type,"RT")==0) strcpy(long_type,"Redo Thread"); else if (strcmp(type,"UN")==0) strcpy(long_type,"User Name"); else if (strcmp(type,"TX")==0) strcpy(long_type,"Transaction"); else if (strcmp(type,"TM")==0) strcpy(long_type,"DML"); else if (strcmp(type,"UL")==0) strcpy(long_type,"PL/SQL user lock"); else if (strcmp(type,"DX")==0) strcpy(long_type,"Distributed Xaction"); else if (strcmp(type,"CF")==0) strcpy(long_type,"Control FIle"); else if (strcmp(type,"IS")==0) strcpy(long_type,"Instance State"); else if (strcmp(type,"FS")==0) strcpy(long_type,"File Set"); else if (strcmp(type,"IR")==0) strcpy(long_type,"Instance Recovery"); else if (strcmp(type,"ST")==0) strcpy(long_type,"Space Transaction"); else if (strcmp(type,"TS")==0) strcpy(long_type,"Temp Segment"); else if (strcmp(type,"LS")==0) strcpy(long_type,"Log start or switch"); else if (strcmp(type,"RW")==0) strcpy(long_type,"Row Wait"); else if (strcmp(type,"SQ")==0) strcpy(long_type,"Seq Nbr"); else if (strcmp(type,"TE")==0) strcpy(long_type,"Extend Table"); else if (strcmp(type,"TT")==0) strcpy(long_type,"Temp Table"); else strcpy(long_type,type); if (mode==0) strcpy(long_mode,"None"); else if (mode==1) strcpy(long_mode,"Null"); else if (mode==2) strcpy(long_mode,"Row-S (SS)"); else if (mode==3) strcpy(long_mode,"Row-X (SX)"); else if (mode==4) strcpy(long_mode,"Share"); else if (mode==5) strcpy(long_mode,"S/Row-X (SSX)"); else if (mode==6) strcpy(long_mode,"Excl"); else sprintf(long_mode,"Mode %d",mode); sprintf(return_value,"%s %s",long_type,long_mode); return(return_value);
}
char *get_object()
{
static char location[90];
char buffer[90];
/**
** Get the segment at p1,p2. This is a bit awkward, but there
** Doesn't seem to be an efficient method. ext_to_obj would be
** slower
*/
/**
** First, what are the segment file and block locations
*/
EXEC SQL SELECT SEGFILE#,SEGBLOCK#
INTO :segfile, :segblock
FROM SYS.UET$
WHERE FILE#=:p1
AND BLOCK# <= :p2
AND BLOCK# > :p2-UET$.LENGTH;
if (sqlca.sqlcode>0)
{
/**
** Segment doesn't exist - at least get tablespace
*/
EXEC SQL SELECT TABLESPACE_NAME
INTO :tablespace_name
FROM SYS.DBA_DATA_FILES
WHERE FILE_ID=:p1;
if (sqlca.sqlcode>0)
{
strcpy(location,"Strange segment");
return(location);
}
else
{
sprintf(location,"Seg in %s tspace",
tablespace_name);
return(location);
}
}
if (debug) printf("got segfile/segblock %d/%
d\n",segfile,segblock);
/**
** Revist: wish there was a adirect way of getting segment
type!
** Is it a table?
*/
EXEC SQL SELECT OBJ#
INTO :objno
FROM SYS.TAB$
WHERE TAB$.FILE#=:segfile
AND TAB$.BLOCK#=:segblock;
if (sqlca.sqlcode > 0 )
{
if (debug) printf("Not table\n");
/* Not a table, index? */
EXEC SQL SELECT OBJ#
INTO :objno
FROM SYS.IND$
WHERE FILE#=:segfile
AND BLOCK#=:segblock;
if (sqlca.sqlcode > 0)
{
if (debug) printf("Not index\n");
/* Not index, rollback segment? */
EXEC SQL SELECT NAME
INTO :object_name
FROM SYS.UNDO$
WHERE FILE#=:segfile
AND BLOCK#=:segblock;
if (sqlca.sqlcode==0)
{
strcpy(location,object_name);
return(location);
}
else
{
if (debug) printf("Not rollback\n");
/* Not index , perhaps temp segment? */
EXEC SQL SELECT decode(type,2,
'DEFERRED
ROLLBACK', 3, 'TEMPORARY',
4, 'CACHE',
'UNDEFINED')
INTO :segment_type
FROM SYS.SEG$
WHERE FILE#=:segfile
AND BLOCK#=:segblock;
if (sqlca.sqlcode > 0 )
{
if (debug) printf("Not Anything!
\n");
sprintf(location,"Unknown
segment at file/block %d/%d",
p1,p2);
return(location);
}
else
{
if (debug) printf("%
s\n",segment_type);
sprintf(location,"%s at
file/block %d/%d",
segment_type,p1,p2);
return(location);
}
}
}
}
/**
** Now that we have an object ID, get the object name
*/
if (debug) printf("objno=%d\n",objno);
EXEC SQL SELECT OWNER||'.'||OBJECT_NAME,OBJECT_TYPE
INTO :object_name,:object_type
FROM SYS.DBA_OBJECTS
WHERE OBJECT_ID=:objno;
if (sqlca.sqlcode>0)
sprintf(buffer,"Inconsistent object at file/block %d%d",
p1,p2);
else
if (debug) printf("Segment %s\n",object_name);
sprintf(location,"%s",
object_name);
return(location);
}
void report()
{
/**
** Display all totals
*/
int i,j,k,l,n_event_details;
float elapsed_pct;
sort_events();
printf("\nAnalysis of wait events in trace file %
s\n\n",trace_file);
printf(" Summary of waits by category\n");
printf(" ----------------------------\n\n");
printf("%40-s %8-s %8-s %8-s %8-s\n","Event","No of","Pct of",
"Time","Pct of");
printf("%40-s %8-s %8-s %8-s %8-s\n","Name","Waits","Total",
"Waited","Total");
printf("%40-s %8-s %8-s %8-s %8-s\n","--------------------------
----",
"--------","--------","--------","--------");
for (i=0;i<=event_array_max;i++)
{
j=sorted_events[i];
if (j==0)
continue;
if (debug)
printf("i=%d j=%d\n",i,j);
printf("%40-s %8d %8.2f %8.2f %8.2f\n",
event_array[j].event_name,
event_array[j].count,
((float) event_array[j].count*100/total_count),
((float) event_array[j].elapsed)/100,
((float) event_array
[j].elapsed*100/total_elapsed));
}
if (verbose)
{
/**
** If in verbose mode, display a breakdown by resource
*/
printf("\n\n");
printf(" Breakdown of waits by
resource\n");
printf(" ----------------------------
--\n\n");
printf("%40-s %8-s %8-s %8-s %8-s\n","Event or resource",
"No of","Pct of","Time","Pct of");
printf("%40-s %8-s %8-s %8-s %8-
s\n","Name","Waits","Total",
"Waited","Total");
printf("%40-s %8-s %8-s %8-s %8-s\n",
"----------------------------------------",
"--------","--------","--------","--------");
for (i=0;i<=event_array_max;i++)
{
j=sorted_events[i];
if ((n_event_details=
sort_event_details(event_array
[j].event_name))>=0)
{
printf("\n%40s ** %30-s\n\n"," ",
event_array[j].event_name);
count_sub_total=0;
elapsed_sub_total=0;
for (k=0;k<=n_event_details;k++)
{
l=sorted_event_details[k];
count_sub_total+=
event_detail_array
[l].count;
elapsed_sub_total+=
event_detail_array
[l].elapsed;
}
if (elapsed_sub_total==0)
elapsed_sub_total=1;
for (k=0;k<=n_event_details;k++)
{
l=sorted_event_details[k];
printf("%40-s %8d %8.2f %8.2f %
8.2f\n",
event_detail_array
[l].location,
event_detail_array
[l].count,
( (float)
event_detail_array[l].count*100)
/count_sub_total,
( (float)
event_detail_array[l].elapsed)/100,
( (float)
event_detail_array[l].elapsed*100)
/elapsed_sub_total);
}
printf("\n%40-s %8d %8s %8.2f %8s\n\n",
"** Sub Total
**",count_sub_total,
"",((float)
elapsed_sub_total)/100,"");
}
if (debug)
printf("%s: %d locations\n",
event_array[j].event_name,
n_event_details);
}
}
}
void usage(char *pname)
{
/** ------------------------------------------------ ** Display a Usage message and exit ** -----------------------------------------------*/ fprintf(stderr,"Usage: %s [-v username/password] -f trace_file \n", pname); fprintf(stderr," -v results in more verbose output\n"); exit(2);
}
int instr(char *s,char c)
{
/* Emulate the instr function */
int loc=0;
while(*s!='\0')
{
if (*s==c)
return(loc);
loc++;
s++;
}
return(-1);
}
char *substr(char *s,int b,int l)
{
/* Emulate a substr function */ char * out_string; int i,j=0; out_string=malloc(l+1); for(i=b; i<b+l&&s[i] ;i++) out_string[j++]=s[i]; out_string[j]='\0'; return(out_string);
}
int repstr(char *s1,char *s2, char c1, char c2)
{
/* Replace all occurances of c1 with c2 in s */
int i,j,l=0;
l=(int) strlen((const char *) s2);
for (i=0;i<=l;i++)
{
if (s2[i]==c1)
s1[i]=c2;
else
s1[i]=s2[i];
}
return(strlen(s1));
}
void oerr()
{
/** ** ORACLE error handler */ char error_msg[1024]; int error_size=1024; int error_len; EXEC SQL WHENEVER SQLERROR CONTINUE; sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0'; oraca.orastxt.orastxtc[oraca.orastxt.orastxtl] = '\0'; fprintf(stderr,"%s\n",oraca.orastxt.orastxtc); fprintf(stderr,"\nSQL Error at line %d\n%s\n", oraca.oraslnr,oraca.orastxt.orastxtc); fprintf(stderr,"%s\n",sqlca.sqlerrm.sqlerrmc); sqlglm(error_msg,&error_size,&error_len); error_msg[error_len]='\0'; fprintf(stderr,"%s\n",error_msg); EXEC SQL ROLLBACK WORK RELEASE; exit(1); EXEC SQL WHENEVER SQLERROR DO oerr();
}
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Dec 04 2000 - 14:39:13 CET