Re: constant monitoring and enqueues?

Thanks Kyle.

I know nothing of TCL either, but went to look at your web page and was intrigued by the screenshot there. Tried to download the linux version (which no doubt I could adapt - if need be - for Solaris), but the link wasn't found?

All this looking directly into the SGA scares me, so it's not an avenue I'm likely to explore.

However, there is the question of how my running, for example, Instance Monitor on my NT workstation can query the SGA on my Solaris box (assuming that's what's going on...)?

Andrew

<hailey_kyle_at_my-deja.com> wrote in message news:93lo2k$9k0$1_at_nnrp1.deja.com...
> Yeah, I like Instance Monitor. I wanted to be able to customize it
> so I put some of the funcionality together with TCL/TK on
>
> http://www.geocities.com/oraperf/oramon.html
>
> I sample every 10 seconds or so without any problem. It all depends
> what you look at and what state the system is in.
>
> For some ideas on monitoring see:
> http://www.geocities.com/oraperf/seminar/collect.html
>
> As far as accessing the SGA directly here is previous response I sent
> inside oracle:
>
>
> Received: Sent: OCTOBER 20,
> 1997 17:48
> From: KHAILEY.FR.ORACLE.COM <KHAILEY.FR.ORACLE.COM>
> Subject: Re: Precise/SQL
>
> Yeah, it looks pretty curious to me. I imagine its the same
> story as SQL*TRAX where some x-oracle developers left and started a
> company that read the redo logs. Of course they already new the
> internal structure of the redo logs.
>
> It is possible of course that someone could write this from
> scratch, but "Oscams Raisor's" would say that these people either
> worked for Oracle or have a freind at Oracle who could answer some key
> questions. (My only question is why doesn't Oracle Corp provide these
> kind of tools at least to Oracle consultants, but then again Oracle
> Corp is doing fine without them on the competative terain)
>
> How does a program like this work? Well Roger Saunders in the
> UK who came up with the idea/program back in 1991 to sample the SGA
> could probably explain it the best, but maybe as a rather neophyte
> programer, I can provide a simple perspective.
>
> 1) The program needs to attach to the SGA. On Unix you need the
> address and id of the shared memory segment to attach the segment (and
> permissions). The address and id can be gotten from the sgadef file,
> whose structure is not too complicated, but a little help would go
> along way.
> On NT it sounds like a whole other ballgame.
>
> 2) The program needs to know where to get the information in
> the SGA. This is pretty easy for X$ tables. The X$ table have an
> address column which is the SGA memory address, so you just run a
> preliminary sql statement to get the address of the first row of the
> X$table. Once you have this address you run your C program to attach to
> the SGA and then read the data out of the address corresponding to the
> X$ table.
>
> 3) Its not that easy though. The x$ tables represent structures
> in the C code. The x$ tables are just an externalisation of the
> underlying C structure. The trick is, not all the contents of the
> structure are externalised in the X$ table. So if one were reading the
> X$ table from the SGA they would expect field 2 to be next to field 1
> in memory. Well maybe in the C structure there are several other values
> between the field 1 and field 2 that are not externised into the
> X$table, so instead of finding field 2 we see some other value that
> supposididly since we don't have access to the code, we don't
> know what it is. Example
>
> strucutre foo
> int id
> int A
> int B
>
> if struct foo is externalised as an x$ table with only B visible then
> we get
>
> desc x$foo
> Column Name Null? Type
> ------------------------------ -------- ----
> ADDR RAW(8)
> INDX NUMBER
> ID NUMBER
> B NUMBER
>
> select * from x$foo;
> ADDR INDX ID B
> ---------------- ---------- ---------- ----------
> 000000000082E640 1 1 81
> 000000000082E648 2 1 81
> 000000000082E650 3 1 81
>
> address
> 82E640
> ||
> \/
> we expect:
> ----------------------------------------------------------------
> -
> Memory | ID | B | ID | B | ID | B | ...
> ----------------------------------------------------------------
> -
>
> in realitly:
> ----------------------------------------------------------------
> -
> Memory | ID | A | B | ID | A | B | ID | A | B | ...
> ----------------------------------------------------------------
> -
>
> Actually its worse than that. Without knowledge of the underlying C
> structure
> we don't know the widths of the fields in the structure. Oracle often
> packs
> alot of bit flags into the same C-code integer variable, but a describe
> of the
> X$table doesn't say they are bit fields; it says they are numbers. So
> if we
> start at address X for field A, which is actually a bit value, and then
> try to
> find field B which is also a bit value, assuming we skip 8 bytes to
> find the
> next "number" we have gone too far since A and B are bit values and
> only 1 bit
> wide, so B is at X+1 not X+8. It gets all the more complicated with
> pointers
> and longs and characters and 64 bit archectecture etc, but of course if
> we
> have access to the source this is easy.
>
> Back to the example. Supposing we did know how to get the info
> out of
> the SGA. The above principle applies to X$ tables. The X$ tables
> provide the
> foundation for the V$ tables. What really interests us is the V$
> tables. The
> question remains open as to how someone would know to make the
> connection
> between the interesting V$tables and their underlying X$tables. IF
> someone had
> a copy of the code (kqfv.h) then this would be easy. With out the code
> it
> would be alot of detective work.
>
>
> Best
> Kyle Hailey
>
>
>
>
>
>
>
>
> ---- Included Message ----
>
> Received: OCTOBER 19, 1997 14:06 Sent: OCTOBER 19,
> 1997 12:07
> To: khailey.fr
> Subject: Fwd: Precise/SQL
>
> Kyle,
>
> This sounds a lot like the M2 toolkit you've been describing. Do you
> think it feasible that a 3rd-party has developed a tool to sample
> directly from the SGA? If they do not have a source-code agreement
> with Oracle and they reverse-engineered the SGA, I bet that Oracle8
> comes as a nasty surprise (no surprise!)...
>
> What do you think? Has anybody else had experience with this?
>
> Thanks!
>
> ---- Included Message ----
>
> Received: Sent: OCTOBER 13,
> 1997 20:33
> Subject: Precise/SQL
>
> IHAC that is interested in Precise/SQL, an integrated application
> performance
> tuning tool for Oracle databases. What is of more interest to this
> highly
> qualified customer is the following Precise statement:
>
> "Precise/SQL's low-overhead monitoring agents use sub-second sampling
> rates to capture ALL executing SQL statements - guaranteeing you have
> accurate, detailed information to help you make intelligent tuning
> decisions.
>
> Precise/SQL monitors the operating system and the database without
> consuming any Oracle resources. Precise/SQL's server-based
> architecture
> captures information directly from the Oracle SGA - not V$ tables - so
> you don't tie up Oracle resources while trying to identify
> bottlenecks."
>
> Should anybody of you know any Oracle7 customer that is a user of this
> product, as well as eventually your own personal experience with it, I
> would
> be grateful for the corresponding reference.
>
> Thank you in advance for your time.
>
> Best regards,
>
>
> In article <93deuf$d8m$1_at_nnrp1.deja.com>,
> Ethan Post <epost1_at_my-deja.com> wrote:
> > My applications grab data from the V$ views on average every 5 minutes
> > which causes very little performance degradation. You are free to
> > check out the code, although it is in PL/SQL only. Check out:
> >
> > www.gnumetrics.com
> >
> > and
> >
> > www.gnumetrics.com/gnumetrics.gif
> >
> > The latter is a screen shot of an Access application I wrote that uses
> > the data collected from GnuMetrics. It is not publically available
 but
> > GnuMetrics is. The links appear to be down at the moment, thanks to
> > freeservers.com, but hopefully they will be back up soon.
> >
> > -Ethan
> > www.freetechnicaltraining.com
> >
> > In article <978964262.25159.0.nnrp-12.c30bdde2_at_news.demon.co.uk>,
> > "andrew_webby at hotmail" <spam_at_no.thanks.com> wrote:
> > > Hi
> > >
> > > I'm looking to write something (mainly to try and teach myself
 Java -
 urk!)
> > > that will monitor the performance of various oracle counters (cache
 hits
> > > etc) and graph them.
> > >
> > > My question is this: if I'm taking a sample every couple of seconds
 say, am
> > > I reasonably safe if I stay to selecting from v$ views for example?
 I'm
> > > thinking of repeatedly querying things and am worried for
 performance
> > > reasons here...
> > >
> > > My basic idea-template for this is from looking at the neat graphs
 in
 the
> > > demo of Quest Software's Instance Monitor which from the quick look
 I
 had,
> > > appears to garner rather a lot of information, quickly and
 repeatedly. I
> > > notice in this months Oracle mag that there's an ad for SQLab Vision
 which
> > > claims to collect SQL statements non-intrusively, with no overhead
 to
> > > Oracle. How?
> > >
> > > Is it perhaps tunneling in some other way and getting at the
 performance
> > > information in some other less-intrusive manner? Or is my thinking
 on
 this
> > > all wrong?
> > >
> > > While I'm on the subject, if I want to decipher exactly where my
 enqueues
> > > are queueing, I know which tables to read. The problem is that I
 need
 to
> > > read them at enqueue time - is there an event I can set that will
 grab these
> > > when they occur or do I have to rely on hoping that I'm querying
 these
> > > tables just in time? A list of events is where btw?
> > >
> > > Thanks in advance for any ideas/avenues to explore.
> > >
> > > Andrew
> > >
> > >
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Fri Jan 12 2001 - 05:55:39 CST