The SGA can be attached to a C program using the shmat system call. You can get more information about this system call by issuing man shmat at a Unix prompt, or from your favorite Unix programming books. The system call must be executed by a Unix user who has read permission to the Oracle SGA, and the syntax is as follows :
void *shmat(int shmid, const void *shmaddr, int shmflg);
The arguments are:
shmid shared memory identifier (SGA id)
shmaddr starting address of the shared memory (SGA base address)
shmflg - flag
Caution ‚ | To avoid data corruption, it is imperative that the SGA be attached as read-only. The shmflg value must be SHM_RDONLY. You must never alter the memory content because it will corrupt the database. |
You must have C programming experience to develop a working application. The following programs are written by Kyle Hailey. The C program reads the X$ KSUSECST structure directly. The programs can be downloaded from Hailey ‚ s website at http://oraperf. sourceforge .net/. It consists of two modules ‚ xksuse.sql and xksuse.c. The first is a SQL script that prepares the xksuse.h header file to be included in the xksuse.c module, which is a C program.
set echo off
create or replace function to_dec (hex_input raw)
return number
is
input_length pls_integer := length(hex_input);
integer_value number := 0;
begin
for i in 1..input_length loop
select integer_value +
(decode(substr(hex_input,(length(hex_input)+1- i),1),'A',10,'B',11,'C',12,'D',13,'E',14,'F',15,substr(hex_input,(length(hex_input)+1 -i),1)) * power(16,i-1))
into integer_value
from dual;
end loop;
return integer_value;
end;
/
/* Script: xksuse.sql
Author: Kyle Hailey
Dated: June 2002
Purpose: create defines for xksuse.c
copyright (c) 2002 Kyle Hailey
*/
set pagesize 0
set verify off
set feedback off
set echo off
spool xksuse.h
select '#define SGA_BASE 0x'addr from x$ksmmem where rownum < 2;
select '#define START 0x'min(addr) from x$ksusecst;
select '#define PROCESSES 'to_char(value - 1) from v$parameter where name = 'processes';
select '#define STATS 'count(*) from x$ksusd;
select '#define NEXT '((to_dec(e.addr)-to_dec(s.addr)))
from (select addr from x$ksusecst where rownum < 2) s,
(select max(addr) addr from x$ksusecst where rownum < 3) e;
select '#define '
replace(c.kqfconam,'#','_NUM') ' '
to_char(c.kqfcooff - mod(c.kqfcooff,2))
' /* offset ' c.kqfcooff ' size ' c.kqfcosiz ' */ '
from x$kqfco c, x$kqfta t
where t.indx = c.kqfcotab
and (t.kqftanam = 'X$KSUSECST' or
t.kqftanam = 'X$KSUSE' or
t.kqftanam = 'X$KSUSESTA')
and kqfcooff > 0
order by c.kqfcooff;
select '#define '
upper(translate(s.name,' :-()/*''','________'))' '
to_char(c.kqfcooff - mod(c.kqfcooff,2)+ STATISTIC# * 4)
from x$kqfco c, x$kqfta t, v$statname s
where t.indx = c.kqfcotab
and t.kqftanam = 'X$KSUSESTA'
and c.kqfconam = 'KSUSESTV'
and kqfcooff > 0
order by c.kqfcooff;
select 'char latch[][100]={' from dual;
select '"'name'",' from v$latchname;
select ' "" };' from dual;
select 'char event[][100]={' from dual;
select '"'name'",' from v$event_name;
select ' "" };' from dual;
select 'int users[]={' from dual;
select '0x'addr',' from x$ksuse;
select '0x0};' from dual;
spool off
exit
The following is the xksuse.c C program module.
/* Script: xksuse.c
Author: Kyle Hailey
Dated: June 2002
Purpose: read x$ksuse direclty from the SGA
copyright (c) 2002 Kyle Hailey
# compile
cc -o xksuse xksuse.c
# run
./xksuse SGA_ID (example: ./xksuse 1027)
*/
#include <stdio.h>
#include <sys/ipc.h>
#include <sys/shm.h>
#include <errno.h>
#include "xksuse.h"
#define FORMAT1 "%4s %6s %-20.20s %10s %10.10s %10s %6s %4s %10s %10s %10s %10s\n"
#define FORMAT2 "%4d %6d %-20.20s %10X %10.10X %10X %6u %4d %10d %10d %10u %10u\n"
#define FORMAT3 "%4d %6d %-20.20s %10X %10.10s %10X %6u %4d %10d %10d %10u %10u\n"
void *sga_attach (void *addr, int shmid)
{
if (addr != 0) addr=(void *)shmdt(addr);
addr=(void *)shmat(shmid,(void *)SGA_BASE,SHM_RDONLY);
if (addr == (void *)-1) {
printf("shmat: error attatching to SGA\n");
exit();
} else {
printf("address %lx %lu\n",(int *)addr,(long *)addr);
}
return addr;
}
main(argc, argv)
int argc;
char **argv;
{
void *addr;
int shmid[100];
void *sga_address;
int seqs[PROCESSES];
long p1r, p2r, p3r, psqla, sqla;
unsigned int cpu,i, tim, sid, uflg, flg, evn, psqlh, sqlh, wtm, ctm, stm, ltm ;
unsigned int cur_time = 0;
int seq;
for (i=0;i<PROCESSES;i++) { seqs[i]=0; }
if (argc != 2) {
fprintf(stderr, "Usage: %s shmid \n", *argv);
exit(1);
}
shmid[0]=atoi(argv[1]);
addr=0;
addr=sga_attach(addr,shmid[0]);
while (1) {
addr=sga_attach(addr,shmid[0]);
sga_address=(void *)START;
sleep(1);
printf("[H [J");
printf(FORMAT1,"sid", "seq#", "wait", "p1", "p2", "p3", "cpu", "uflg", "stm", "wtm", "sqlh", "psqlh");
printf("procs %i\n",PROCESSES);
for (i=0; i < PROCESSES ; i++) {
sga_address=(void *)((int)users[i]);
seq=*(unsigned short *)((int)sga_address+KSUSSSEQ);
evn=*(short *)((int)sga_address+KSUSSOPC);
p1r=*(long *)((int)sga_address+KSUSSP1R);
p2r=*(long *)((int)sga_address+KSUSSP2R);
p3r=*(long *)((int)sga_address+KSUSSP3R);
tim=*(int *)((int)sga_address+KSUSSTIM);
sid=*(short *)((int)sga_address+KSUSENUM);
/*
uflg=*(short *)(((int)sga_address));
uflg=*(int *)((int)sga_address+KSUSEFLG);
*/
#ifdef __linux
uflg=*(short *)((int)sga_address)>>8;
#else
uflg=*(short *)((int)sga_address);
#endif
flg=*(int *)((int)sga_address+KSUSEFLG);
stm=*(int *)((int)sga_address+KSUSSTIM);
ltm=*(int *)((int)sga_address+KSUSELTM);
ctm=*(int *)((int)sga_address+KSUSELTM-8);
wtm=*(int *)((int)sga_address+KSUSELTM-4);
psqla=*(long *)((int)sga_address+KSUSEPSQ);
sqla=*(long *)((int)sga_address+KSUSESQL);
sqlh=*(int *)((int)sga_address+KSUSESQH) ;
psqlh=*(int *)((int)sga_address+KSUSEPHA) ;
cpu=*(int *)((int)sga_address+CPU_USED_WHEN_CALL_STARTED) ;
if (wtm > cur_time) cur_time=wtm;
if (seqs[i] != seq 1 == 1) {
if (strcmp(event[evn],"SQL*Net message from client")) {
if (flg%2 == 1 && uflg%2 == 1) {
if (! strcmp(event[evn],"latch free")) {
printf(FORMAT3, sid, seq, event[evn], p1r, latch[p2r], p3r, cpu, uflg, stm, (cur_time - wtm), sqlh, psqlh);
} else {
printf(FORMAT2, sid, seq, event[evn], p1r, p2r, p3r, cpu, uflg, stm, (cur_time - wtm), sqlh, psqlh);
}
}
}
}
seqs[i]=seq;
}
}
}
When the code is written and compiled, you will discover that the C program has a superb sampling performance. However, for this to be a useful application, the wait event data must be captured in a repository for future reference. You also need to capture the SQL statements that are associated with the wait events, as wait events by themselves are of little value.