Attaching the SGA to a C Program


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.




Oracle Wait Interface
Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)
ISBN: 007222729X
EAN: 2147483647
Year: 2004
Pages: 114

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net