direct sga access without sql
DESCRIPTION
TRANSCRIPT
![Page 1: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/1.jpg)
Oaktable Jonathan Lewis and ORACLE_TRACE Oracle_Trace crashes my Database I start the SGA attach by searching every offset Anjo Kolk says James Morle wrote a program using
x$ksmmem I show James my first draft using x$ksmmem James is baffled by why I'm hard coding offsets James says the offsets are in some X$ table I search, turn up a mail by Jonathan Lewis on
x$kqfco Goldmine – all the offsets Thanks Mogens Nogard! Thanks to TomKyte's Decimal to Hex
![Page 2: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/2.jpg)
![Page 3: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/3.jpg)
http://oraperf.sourceforge.net
![Page 4: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/4.jpg)
Direct Oracle SGA Memory Access
Reading data directly from Oracle’s shared memory segment using C code
Monday, April 10, 2023
![Page 5: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/5.jpg)
SGA on UNIX
oracle oracle sqlplussqlplus
DBWRDBWR
CKPTCKPT
LGWRLGWR
ARCHARCH
PMONPMON
SMONSMON SSnnnnnnPPnnnnnn
SGAShared PoolShared Pool Database Buffer CacheDatabase Buffer Cache
Redo LogRedo LogBufferBuffer
DDnnnnnn
Machine Memory
![Page 6: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/6.jpg)
SGA on NT
Shared PoolShared Pool Database Buffer CacheDatabase Buffer CacheRedo LogRedo Log
BufferBuffer
SMONSMON
PMONPMON
DBWRDBWRLGWRLGWR
SSnnnnnn DDnnnnnn PPnnnnnn CKPTCKPT
ARCHARCH oracle oracle
sqlplussqlplusProcess Space
Machine Memory
![Page 7: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/7.jpg)
What is the SGA
Memory CacheOften Used Data Rapid AccessShareable Concurrently Access
![Page 8: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/8.jpg)
SGA 4 main regions
Fixed information– Users info– Database statistics– X$dual– etc
Data block cache SQL cache ( library cache/shared pool) Redo log buffer
![Page 9: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/9.jpg)
How is the SGA info Used?
Automatically– data blocks cached– Log buffer– Sql cache– Updates of system and user statistics
User Queries– User info v$session– System info v$parameter– Performance statistics v$sysstat, v$latch, v$system_event– Buffer cache headers, x$bh
![Page 10: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/10.jpg)
Why Direct Access with C?
Reading Hidden Information– Sort info on version 7 – OPS locking info version 8 – Contents of data blocks (only the headers or visible in X$)
Access while Database is Hung High Speed Access
– Sampling User Waits, catch ephemeral data– Scan LRU chain in X$bh– Statistically approximate statistics
SQL statistics per user
Low overhead
![Page 11: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/11.jpg)
Database Slow or Hung
Often happens at the largest sites when cutting edge support is expected.
Shared Pool errors ORA 4031 Archiver or Log file Switch Hangs Hang Bugs Library Cache Latch contention ORA-00379: no free buffers available in
buffer pool DEFAULT
![Page 12: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/12.jpg)
Statistical Sampling
By Rapidly Sampling SQL statistics and the users who have the statistics open, one can see how much work a particular user does with a particular SQL statement
![Page 13: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/13.jpg)
Low Overhead
Marketing Appeal Clients are sensitive about their
production databases Heisenberg uncertainty affect – less
overhead less affect monitoring has on performance which we are monitoring
![Page 14: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/14.jpg)
SGA made visible through x$tables
Most of the SGA is not visible X$KSMMEM Exception, Raw Dump of SGA Information Externalized through X$ tables Useful or Necessary information is Externalized Externalized publicly through V$ Tables
![Page 15: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/15.jpg)
Machine Memory
0x80000000
SGASGA
![Page 16: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/16.jpg)
Graphic SGA
SGA0x80000000
Buffer Cache
Buffer Cache
Fixed Area
Shared Pool
Log Buffer
![Page 17: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/17.jpg)
Fixed Area
0x85251EF4
X$KSUSECST- user waitsSGA0x80000000
![Page 18: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/18.jpg)
X$KSUSECST
170 Records2328 bytes
Row 10x85251EF4 Row 2Row 3 …
![Page 19: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/19.jpg)
X$KSUSECST Record
One Record in X$KSUSECST
2328 bytes
1276
![Page 20: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/20.jpg)
X$KSUSECST Fields
1276 1278 1280 1284 1288
Seq # Event # p3p2p1
![Page 21: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/21.jpg)
Externalization of C structs: X$ tables
If Structure foo was externalized in a X$
SQL> describe x$fooColumn Name Type------------------------------ --------ADDR RAW(8)INDX NUMBERID NUMBERB NUMBER
![Page 22: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/22.jpg)
SGA is One Large C Struct
struct foo{
int id; int A; int B; int C;};
struct foo foo[N];
![Page 23: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/23.jpg)
Struct C code
#include <stdio.h>
#include <fcntl.h>
#define N 20
/* structure definition: */
struct foo
{
int id;
int a;
int b;
int c;
};/* end structure definition */
![Page 24: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/24.jpg)
Struct Recordmain(){
struct foo foo[20];
int fptr;
/* zero out memory of struct */
memset(foo,0,sizeof(foo));
foo[0].id=1; /* row 0 */
foo[0].a=12;
foo[0].b=13;
foo[0].c=13;
![Page 25: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/25.jpg)
Struct Write to File
foo[1].id=2; /* row 1 */
foo[1].a=22;
foo[1].b=23;
foo[1].c=24;/* write to file, simulate SGA */
if ((fptr = open("foo.out",O_WRONLY | O_CREAT,0777)) < 0 )
return -1;
write(fptr,foo,sizeof(foo)); return 0;
}
![Page 26: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/26.jpg)
Simulate SGA with a File
write(fp,foo,sizeof(foo));
![Page 27: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/27.jpg)
Simulate SGA with a File
Memory address Increasing
0 16
32
48
64
80
AID B IDC A …
Row 0 Row 1
0 4 8 12
16
20
bitsbytes
0 4 8 C 10
14
hex bytes0 4 1
014
20
24
oct bytes
![Page 28: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/28.jpg)
Struct File Contents
$ ./foo
$ ls -l foo.out
-rw-r--r-- joe dba 320 Feb 10 19:41 foo.out
int = 32 bits
Int = 4 bytes
20 entries * 4 int * 4 bytes/int = 320 bytes
![Page 29: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/29.jpg)
od – octal dump
$ od -l foo.out
0000000 1 12 13 13
0000020 2 22 23 24
0000040 0 0 0 0
*
0000500
![Page 30: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/30.jpg)
Struct File Contents
Address is in HexColumn 2 is the IDColumn 3 is field AColumn 4 is field BColumn 5 is field C
![Page 31: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/31.jpg)
X$ tables ?
Ok, x$foo =~ foo[20]How do I get a list of x$ tables?Where is each X$ located?V$Fixed_Tables
![Page 32: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/32.jpg)
V$Fixed_Table – list of X$ tables
SQL> desc v$fixed_table;
Name Null? Type
----------------------------------------- -------- -----------------
NAME VARCHAR2(30)
OBJECT_ID NUMBER
TYPE VARCHAR2(5)
TABLE_NUM NUMBER
![Page 33: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/33.jpg)
Graphic: X$ Addresses
0x8????????
X$????
SGA0x80000000
![Page 34: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/34.jpg)
V$Fixed_Table
spool addr.sqlselect 'select 'addr, ||''''||name||''''||' from ' || name ||'
where rownum < 2;' from v$fixed_table where name like 'X%'/spool [email protected]
![Page 35: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/35.jpg)
Example: finding the address
select a.addr ,'X$KSUSE'
from X$KSUSE
where rownum < 2 ;
![Page 36: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/36.jpg)
X$ layout6802B244 X$KSLEMAP
6802B7EC X$KSLEI
6820B758 X$KSURU
6820B758 X$KSUSE - v$session
6820B758 X$KSUSECST – v$session_wait
6820B758 X$KSUSESTA – v$session_stat
6820B758 X$KSUSIO
6826FBD0 X$KSMDD
6831EA0C X$KSRCHDL
![Page 37: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/37.jpg)
What's in these X$ views
V$ views are documented V$ views are based often on X$ tables The map from v$ to X$ is described in :
V$Fixed_View_Definition
![Page 38: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/38.jpg)
V$Fixed_View_Definition
SQL> desc V$Fixed_View_Definition Name Type ----------------------------------- -------------- VIEW_NAME VARCHAR2(30) VIEW_DEFINITION
VARCHAR2(4000)
![Page 39: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/39.jpg)
Definition of V$Session_Wait
SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name='GV$SESSION_WAIT';VIEW_DEFINITION-----------------------------------------------------------------------select s.inst_id,s.indx,s.ksussseq,e.kslednam, e.ksledp1,s.ksussp1,s.ksussp1r,e.ksledp2, s.ksussp2,s.ksussp2r,e.ksledp3,s.ksussp3,s.ksussp3r, decode(s.ksusstim,0,0,-1,-1,-2,-2, decode(round(s.ksusstim/10000),0,-1,round(s.ksusstim/10000))), s.ksusewtm, decode(s.ksusstim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME', 'WAITED KNOWN TIME') from x$ksusecst s, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0 and s.ksussopc=e.indx
![Page 40: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/40.jpg)
The Fields in X$ tables
OK, I've picked an X$ I've got the starting address Now, how do I get the fields?
![Page 41: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/41.jpg)
X$KQFTA
Kernel Query Fixed_view Table INDX use to find column information KQFTANAM X$ table names
![Page 42: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/42.jpg)
X$KQFCO
Kernel Query Fixed_view Column KQFCOTAB Join with X$KQFTA.INDX KQFCONAM Column name KQFCOOFF Offset from beginning of the
row KQFCOSIZ Columns size in bytes
![Page 43: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/43.jpg)
X$KSUSECST Fields
1276 1278 1280 1284 1288
Seq # Event # p3p2p1
2 2 4 4 4 BYTES
Address
![Page 44: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/44.jpg)
SGA Contents in Resume
Fixed SGA
Buffer Cache
Redo Buffer
Library Cache
Memory address Increasing0x8000000
In resume:
Oracle takes the C structure defining the SGA and maps it onto a shared memory segment
Oracle provides access to some of the SGA contents via X$ tables
![Page 45: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/45.jpg)
**** Procedure *****
1. Choose a V$ view
2. Find base X$ Tables for v$ view
3. Map X$ fields to V$ fields
4. Get address of X$ table in SGA
5. Get the size of each record in X$ table
6. Get the number of records in X$ table
7. Get offsets for each desired field in X$ table
8. Get the base address of SGA
![Page 46: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/46.jpg)
1) V$SESSION_WAIT Example
List of all users waiting Detailed information on the waits Data is ephemeral Useful in Bottleneck diagnostics High sampling rate candidate Event 10046 captures this info
Good table for SGA sampling
![Page 47: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/47.jpg)
V$SESSION_WAIT DescriptionSQL> desc v$session_wait
Name Type
----------------------------------------- --------------------------
SID ,NUMBER
SEQ# ,NUMBER
EVENT ,VARCHAR2(64)
P1TEXT ,VARCHAR2(64)
P1 ,NUMBER
P1RAW ,RAW(4)
P2TEXT ,VARCHAR2(64)
P2 ,NUMBER
P2RAW ,RAW(4)
P3TEXT ,VARCHAR2(64)
P3 ,NUMBER
P3RAW ,RAW(4)
WAIT_TIME ,NUMBER
SECONDS_IN_WAIT ,NUMBER
STATE ,VARCHAR2(19)
)
![Page 48: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/48.jpg)
V$SESSION_WAIT Short
SQL> desc v$session_wait
Name Type
---------------------------- -------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1 NUMBER
P2 NUMBER
P3 NUMBER)
![Page 49: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/49.jpg)
V$FIXED_VIEW_DEFINITION
Gives mappings of V$ views to X$ tables
SQL> select
VIEW_DEFINITION
from
V$FIXED_VIEW_DEFINITION
where
view_name='V$SESSION_WAIT‘;
![Page 50: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/50.jpg)
V$SESSION_WAIT View DefinitionVIEW_DEFINITION---------------------------------------------------------------------select s.inst_id, s.indx, s.ksussseq, e.kslednam,e.ksledp1,s.ksussp1,s.ksussp1r,e.ksledp2,s.ksussp2,s.ksussp2r,e.ksledp3,s.ksussp3,s.ksussp3r,round(s.ksusstim / 10000), s.ksusewtm, decode(s.ksusstim, 0, 'WAITING', -2, 'WAITED UNKNOWN TIME', -1, 'WAITED SHORT TIME', 'WAITED KNOWN TIME') from x$ksusecst s, x$ksled e where bitand(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0 and s.ksussopc=e.indx
![Page 51: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/51.jpg)
View Definition ShortVIEW_DEFINITION---------------------------------------------------------------------select
s.indx,s.ksussseq,e.kslednam,s.ksussp1,s.ksussp2,s.ksussp3
from x$ksusecst s, x$ksled e
where s.ksussopc=e.indx
![Page 52: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/52.jpg)
2) V$SESSION_WAIT Based on X$KSUSECT
VIEW_DEFINITION----------------------------------------------------select
indx,ksussseq,ksussopc,ksussp1,ksussp2,ksussp3
from x$ksusecst
![Page 53: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/53.jpg)
Equivalent SQL Statements
select indx,ksussseq,ksussopc,ksussp1,ksussp2,ksussp3
from x$ksusecst
select sid seq# event p1 p2 p3 from v$session_wait )
Note: x$ksusecst. Ksussopc is the event #
x$ksled.kslednam is a list of the event names where
x$ksled.indx = x$ksusecst. ksussopc
![Page 54: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/54.jpg)
3) V$ to X$ Field Mapping
![Page 55: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/55.jpg)
4) Get base SGA address for X$ table
Find the location of X$KSUSECST in the SGA
SQL> select addr from x$ksusecst where rownum < 2
ADDR
--------
85251EF4
![Page 56: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/56.jpg)
5) Find the Size of Each Record
SQL> select
((to_dec(e.addr)-to_dec(s.addr))) row_size
from
(select addr from x$ksusecst where rownum < 2) s,
(select max(addr) addr from x$ksusecst where rownum < 3) e ;
ROW_SIZE
----------------
2328
![Page 57: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/57.jpg)
6) Find the Number of Records in the structure
SQL> select count(*) from x$ksusecst ;
COUNT(*)
--------------
170
![Page 58: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/58.jpg)
Get Offsets for Each Desired Field in X$ table
SQL> select c.kqfconam field_name, c.kqfcooff offset, c.kqfcosiz szfrom x$kqfco c, x$kqfta twhere t.indx = c.kqfcotab and t.kqftanam='X$KSUSECST'order by offset;
![Page 59: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/59.jpg)
X$KQFTA - X$ Tables Names
List of X$ tables
INDX use to find column information
KQFTANAM X$ table names
To get Column information join with X$KQFCO
X$KQFTA.INDX = X$KQFCO.KQFCOTAB
![Page 60: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/60.jpg)
X$KQFCO – X$ Table Columns
List of all the columns in X$ Tables
KQFCOTAB Join with X$KQFTA.INDX KQFCONAM Column name KQFCOOFF Offset from beginning of the
row KQFCOSIZ Columns size in bytes
![Page 61: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/61.jpg)
Field OffsetsFIELD_NAME OFFSET SZ------------------------------ ---------- ----------ADDR 0 4INDX 0 4KSUSEWTM 0 4INST_ID 0 4KSSPAFLG 1 1KSUSSSEQ 1276 2KSUSSOPC 1278 2KSUSSP1 1280 4KSUSSP1R 1280 4KSUSSP2 1284 4KSUSSP2R 1284 4KSUSSP3 1288 4KSUSSP3R 1288 4KSUSSTIM 1292 4KSUSENUM 1300 2KSUSEFLG 1308 4
![Page 62: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/62.jpg)
What are all the fields at OFFSET 0?
These are all calculated values and not stored explicitly in the SGA.
ADDR memory address
INDX record number, like rownum
INST_ID database instance ID
KSUSEWTM calculated field
![Page 63: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/63.jpg)
Unexposed Fields
What happens between OFFSET 1 and 1276?
• Unexposed Fields• Sometimes exposed elsewhere, in our case
• V$SESSION• V$SESSTAT
![Page 64: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/64.jpg)
Fields at Same Address
Why do some fields start at the same address?KSUSSP1KSUSSP1R
Are at the same addressEquivalent of
V$SESSION_WAIT.P1 V$SESSION_WAIT.P1RAW
These are the same data, just exposed asHexDecimal
![Page 65: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/65.jpg)
7) Offsets of Fields
![Page 66: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/66.jpg)
8) Get Base SGA Address
SQL> select addr from x$ksmmem where rownum < 2
ADDR
--------------
80000000
![Page 67: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/67.jpg)
Results X$KSUSECST
![Page 68: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/68.jpg)
Machine Memory
0x80000000
SGASGA
![Page 69: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/69.jpg)
Fixed Area
0x85251EF4
X$KSUSECST- user waitsSGA0x80000000
![Page 70: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/70.jpg)
X$KSUSECST
170 Records2328 bytes
Row 10x85251EF4 Row 2Row 3 …
![Page 71: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/71.jpg)
X$KSUSECST Record
One Record in X$KSUSECST
2328 bytes
1276
![Page 72: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/72.jpg)
X$KSUSECST Fields
1276 1278 1280 1284 1288
Seq # Event # p3p2p1
![Page 73: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/73.jpg)
Attaching to the SGA
UNIX System Call “shmat”
To attach to shared memory Unix as a system call
void *shmat( int shmid,
const void *shmaddr,
int shmflg );
![Page 74: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/74.jpg)
ID and Address arguments to “shmat”
The arguments are:
shmid – shared memory identifier specified shmaddr – starting address of the shared memory shmflg - flags
The argument shmflg can be set to SHM_RDONLY . To avoid any possible data corruption the SGA should only be attached read only.
The arguments shmid and shmaddr need to be set to Oracle’s SGA id and address.
![Page 75: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/75.jpg)
Finding Oracle SGA’s ID and Address
Use ORADEBUG to find the SGA id
SQL> oradebug setmypid
Statement processed.
SQL> oradebug ipc
Information written to trace file.
![Page 76: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/76.jpg)
Finding Trace File
SQL> show parameters user_dumpNAME VALUE
----------------------- --------------------------------
user_dump_dest /u02/app/oracle/admin/V901/udump
SQL> exit
$ cd /u02/app/oracle/admin/V901/udump
$ ls -ltr | tail -1
-rw-r----- usupport dba Aug 24 18:01 v901_ora_23179.trc
![Page 77: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/77.jpg)
Finding SHMID in Trace File
$ vi v901_ora_23179.trc
…
Total size 004456c Minimum Subarea size 00000000
Area Subarea Shmid Stable Addr Actual Addr
0 0 34401 0080000000 0080000000
…
![Page 78: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/78.jpg)
Attaching to the SGA
Shmid 34401Shmaddr0x80000000Shmflg SHM_RDONLY The SGA attach call in C would be: Shmat(34401, 0x80000000, SHM_RDONLY); This call needs to be executed as a UNIX user who has
read permission to the Oracle SGA
![Page 79: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/79.jpg)
C Code Headers
#include <stdio.h>
#include <sys/ipc.h>
#include <sys/shm.h>
#include <errno.h>
#include "event.h"
event.h is for translating the event #s into event names
![Page 80: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/80.jpg)
Events.h
Spool events.h
select 'char event[][100]={' from dual;
select '"'||name||'",' from v$event_name;
select ' "" };' from dual;
spool off
![Page 81: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/81.jpg)
Define Base Addresses and Sizes
/* SGA BASE ADDRESS */
#define SGA_BASE 0x80000000
/* START ADDR of KSUSECST(V$SESSION_WAIT) */
#define KSUSECST_ADDR 0x85251EF4
/* NUMBER of ROWS/RECORDS in KSUSECST */
#define SESSIONS 150
/* SIZE in BYTES of a ROW in KSUSECST */
#define RECORD_SZ 2328
![Page 82: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/82.jpg)
Define Offsets to Fields
#define KSUSSSEQ 1276 /* sequence # */
#define KSUSSOPC 1278 /* event # */
#define KSUSSP1R 1280 /* p1 */
#define KSUSSP2R 1284 /* p2 */
#define KSUSSP3R 1288 /* p3 */
![Page 83: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/83.jpg)
Set Up Variables
main(argc, argv) int argc; char **argv; { void *addr; int shmid; int shmaddr; void *current_addr; long p1r, p2r, p3r; unsigned int i, seq, tim, flg, evn;
![Page 84: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/84.jpg)
Attach to SGA
/* ATTACH TO SGA */ shmid=atoi(argv[1]); shmaddr=SGA_BASE; if (
(void *)shmat(shmid,(void *)shmaddr,SHM_RDONLY)
== (void *)-1 ) { printf("shmat: error attatching to SGA\n"); exit(); }
![Page 85: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/85.jpg)
Set Up Sampling Loop
/* LOOP OVER ALL SESSIONS until CANCEL */
while (1) {
/* set current address to beginning of Table */
current_addr=(void *)KSUSECST_ADDR;
sleep(1);
printf("^[[H ^[[J"); /* clear screen */
/* print page heading */
printf("%4s %8s %-20.20s %10s %10s %10s \n",
"sid", "seq", "wait","p1","p2","p3");
![Page 86: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/86.jpg)
Loop over all Sessions
for ( i=0; i < SESSIONS ; i++ ) { seq=*(unsigned short *)((int)current_addr+KSUSSSEQ); evn=*(short *) ((int)current_addr+KSUSSOPC); p1r=*(long *) ((int)current_addr+KSUSSP1R); p2r=*(long *) ((int)current_addr+KSUSSP2R); p3r=*(long *) ((int)current_addr+KSUSSP3R); if ( evn != 0 ) { printf("%4d %8u %-20.20s %10X %10X %10X \n", i, seq, event[evn] ,p1r, p2r,p3r ); } current_addr=(void *)((int)current_addr+RECORD_SZ); } } }
![Page 87: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/87.jpg)
Output
$ sga_read_session_wait 34401
sid seq wait p1 p2 p3
0 40582 pmon timer 12C 0 0
1 40452 rdbms ipc message 12C 0 0
2 43248 rdbms ipc message 12C 0 0
3 24706 rdbms ipc message 12C 0 0
4 736 smon timer 12C 0 0
5 88 rdbms ipc message 2BF20 0 0
8 178 SQL*Net message from 6265710 1 0
![Page 88: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/88.jpg)
Pitfalls
Byte Swapping 32 bit vs 64 bit Multiple Shared Memory Segments Segmented Memory Addresses are "unsigned int" Misaligned Access
![Page 89: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/89.jpg)
Little Endian vs Big Endian
Is low byte values first or high byte values first ? a byte is 8 bits
– 00000000-11111111 bits,0 – 31 dec, 0x0 - 0xFF hex Big Endian is "normal" , highest bit first In ascii, the word "byte" is stored as
– b = 62, y = 79, t = 74, e = 65 echo 'byte' | od -x
– b y t e– 62 79 74 65
Little Endian, ie byte swapped (Linux, OSF, Sequent, ? )
– y b e t – 79 62 65 74
![Page 90: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/90.jpg)
Byte Swap Example
Short = 2 bytes ie 16 bitsGoal, get the flag in the "second" byte
#ifdef __linux uflg=*(short *)
((int)sga_address)>>8;#else uflg=*(short *)((int)sga_address);#endif
![Page 91: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/91.jpg)
Byte Swap
Big Endian:00 00 00 00 00 00 00 01Little Endian00 00 00 01 00 00 00 00Solution, push the value over 8 places, to
the right, ie >>8
![Page 92: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/92.jpg)
64 bit vs 32 bit
SQL> desc x$ksmmem Name Type
------------------------------------- --------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER KSMMMVAL RAW(4)-> 32 bitRaw(8) -> 64 bit
![Page 93: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/93.jpg)
Segmented Memory
x$ksuse – can be dis-contiguous
Work around:select 'int users[]={' from dual;select '0x'||addr||',' from x$ksuse;select '0x0};' from dual;
![Page 94: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/94.jpg)
Misaligned Access
Some platforms seg fault when addressing misaligned bytes, need to read in even bytes or units of 4 bytes depending on platform
1 2 3 4 5 6 7 8
![Page 95: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/95.jpg)
x$ksusecst Record: What's Missing?
One Record in X$KSUSECST
2328 bytes
1276
??? ???
![Page 96: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/96.jpg)
Select Addr from X$? where Rownum< 2;
6802B244 X$KSLEMAP
6802B7EC X$KSLEI
6820B758 X$KSURU
6820B758 X$KSUSE – v$session
6820B758 X$KSUSECST – v$session_wait
6820B758 X$KSUSESTA – v$sesstat
6820B758 X$KSUSIO
6826FBD0 X$KSMDD
6831EA0C X$KSRCHDL
![Page 97: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/97.jpg)
x$ksuse Record Contains x$ksusecst
One Record in X$Ksusecst
2328 bytes
1276
v$session_wait v$sessionv$session v$sesstat
x$ksusecstx$ksusesta
x$ksuse
236
![Page 98: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/98.jpg)
Getting v$sesstat addresses
select '#define '|| upper(translate(s.name,' :-()/*''','________'))||' '|| to_char(c.kqfcooff + STATISTIC# * 4 )from x$kqfco c, x$kqfta t, v$statname swhere t.indx = c.kqfcotab and ( t.kqftanam='X$KSUSESTA' ) and
c.kqfconam='KSUSESTV' and kqfcooff > 0order by c.kqfcooff/
![Page 99: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/99.jpg)
User Drilldown Query: 4 joins select w.sid sid, w.seq# seq, w.event event, w.p1raw p1, w.p2raw p2, w.p3raw p3, w.SECONDS_IN_WAIT ctime, s.sql_hash_value sqlhash, s.prev_hash_value psqlhash, st.value cpu from v$session s, v$sesstat st, v$statname sn, v$session_wait w where w.sid = s.sid and st.sid = s.sid and st.statistic# = sn.statistic# and
sn.name = 'CPU used when call started' andw.event != 'SQL*Net message from client'
order by w.sid;
![Page 100: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/100.jpg)
Other Fun Stuff
The next example is output from an SGA program that follows the LRU of the Buffer Cache
The program demonstrates the • insertion point of LRU• cold end of LRU• hot end of the LRU• Full Table Scan Insertion Point
![Page 101: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/101.jpg)
LRU HOT
![Page 102: Direct SGA access without SQL](https://reader033.vdocuments.us/reader033/viewer/2022061223/54c6ed354a7959dd368b4575/html5/thumbnails/102.jpg)
LRU COLD