1 / 102

Oaktable

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

margo
Download Presentation

Oaktable

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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 Lewison x$kqfco • Goldmine – all the offsets • Thanks Mogens Nogard! • Thanks to TomKyte's Decimal to Hex

  2. http://oraperf.sourceforge.net

  3. Direct Oracle SGA Memory Access Reading data directly from Oracle’s shared memory segment using C code Wednesday, March 12, 2014

  4. SGA on UNIX Snnn SMON Dnnn Pnnn PMON • SGA CKPT Redo LogBuffer Shared Pool Database Buffer Cache DBWR ARCH LGWR Machine Memory oracle sqlplus

  5. PMON DBWR ARCH LGWR SGA on NT Dnnn Snnn Pnnn CKPT Machine Memory SMON Redo LogBuffer Shared Pool Database Buffer Cache oracle Process Space sqlplus

  6. What is the SGA • Memory Cache • Often Used Data • Rapid Access • Shareable • Concurrently Access

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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  

  13. 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

  14. SGA SGA Machine Memory 0x80000000

  15. Buffer Cache Graphic SGA SGA 0x80000000 Fixed Area Buffer Cache Shared Pool Log Buffer

  16. Fixed Area X$KSUSECST- user waits SGA 0x80000000 0x85251EF4

  17. X$KSUSECST 170 Records 2328 bytes 0x85251EF4 Row 1 Row 2 Row 3 …

  18. 2328 bytes X$KSUSECST Record One Record in X$KSUSECST 1276

  19. X$KSUSECST Fields 1276 1278 1280 1284 1288 Seq # Event # p1 p2 p3

  20. Externalization of C structs: X$ tables If Structure foo was externalized in a X$ SQL> describe x$foo Column Name Type ------------------------------ -------- ADDR RAW(8) INDX NUMBER ID NUMBER B NUMBER

  21. SGA is One Large C Struct struct foo { int id; int A; int B; int C; }; struct foo foo[N];

  22. 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 */

  23. Struct Record main(){ 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;

  24. 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; }

  25. Simulate SGA with a File write(fp,foo,sizeof(foo));

  26. Memory address Increasing 0 0 0 0 4 16 4 4 8 10 32 8 12 C 48 14 10 16 20 64 14 24 20 80 Simulate SGA with a File Row 1 Row 0 ID A B C ID A … bits bytes hex bytes oct bytes

  27. 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

  28. od – octal dump $ od -l foo.out 0000000 1 12 13 13 0000020 2 22 23 24 0000040 0 0 0 0 * 0000500

  29. Struct File Contents Address is in Hex Column 2 is the ID Column 3 is field A Column 4 is field B Column 5 is field C

  30. X$ tables ? • Ok, x$foo =~ foo[20] • How do I get a list of x$ tables? • Where is each X$ located? • V$Fixed_Tables

  31. 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

  32. Graphic: X$ Addresses SGA 0x80000000 0x8???????? X$????

  33. V$Fixed_Table spool addr.sql select 'select 'addr, ||''''||name||''''||' from ' || name ||' where rownum < 2;' from v$fixed_table where name like 'X%' / spool off @addr.sql

  34. Example: finding the address select a.addr , 'X$KSUSE' from X$KSUSE where rownum < 2 ;

  35. X$ layout 6802B244 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

  36. 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

  37. V$Fixed_View_Definition SQL> desc V$Fixed_View_Definition Name Type ----------------------------------- -------------- VIEW_NAME VARCHAR2(30) VIEW_DEFINITION VARCHAR2(4000)

  38. 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 bit and(s.ksspaflg,1)!=0 and bitand(s.ksuseflg,1)!=0 and s.ksussseq!=0 and s.ksussop c=e.indx

  39. The Fields in X$ tables • OK, I've picked an X$ • I've got the starting address • Now, how do I get the fields?

  40. X$KQFTA • Kernel Query Fixed_view Table • INDX use to find column information • KQFTANAM X$ table names

  41. 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

  42. X$KSUSECST Fields Address 1276 1278 1280 1284 1288 Seq # Event # p1 p2 p3 2 2 4 4 4 BYTES

  43. 0x8000000 Memory address Increasing Fixed SGA Buffer Cache Redo Buffer Library Cache SGA Contents in Resume 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

  44. **** Procedure ***** • Choose a V$ view • Find base X$ Tables for v$ view • Map X$ fields to V$ fields • Get address of X$ table in SGA • Get the size of each record in X$ table • Get the number of records in X$ table • Get offsets for each desired field in X$ table • Get the base address of SGA

  45. 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

  46. V$SESSION_WAIT Description SQL> 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) )

  47. V$SESSION_WAIT Short SQL> desc v$session_wait Name Type ---------------------------- ------------- SID NUMBER SEQ# NUMBER EVENT VARCHAR2(64) P1 NUMBER P2 NUMBER P3 NUMBER)

  48. 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‘;

  49. V$SESSION_WAIT View Definition 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, 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

More Related