Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: constant monitoring and enqueues?

Re: constant monitoring and enqueues?

From: <hailey_kyle_at_my-deja.com>
Date: Fri, 12 Jan 2001 18:47:16 GMT
Message-ID: <93njfh$rb6$1@nnrp1.deja.com>

     Whoops, thanks for the link error info. It should work now.

    I also started writing a monitor in JAVA just to play with JAVA but my mind just doesn't work like that and TCL/TK is easy so I did it in TCL/TK.      Accessing the SGA directly is rarely done by anyone. Oracle Corp has no products that do it. One guy in particular at Oracle wrote an amaizing program that accessed the SGA directly over many versions of Oracle 7.0-8.0 but this he did on his own. One of the big problems of writing the program is having it work across versions and platforms. There is no way to do SGA direct access on NT as far as I know. You'd actually have to compile code into ORacle and have a separate thread. ON UNIX, the SGA is accessible by anyone who has the right UNIX permissions. Anyway it's hardly worth doing for most people unless you are a firefighter and that is all you do. 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 a byte 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 archictecture etc, but of course if we have access to the source this is easy. IT is great for times when the system is so heavly loaded no SQL works, but hopefully that isn't a situation most people get into. One thing accessing the SGA gives sometimes is access to data that wouldn't otherwise be externalized. I know people have used x$ksmem to get OPS lock info that wwould not otherwise be accessible. X$ksmmem allows you to get SGA data at any SGA address, but unless you have access to ORacle source code its hard to find out what is where and generally not worth the headache. So the long and short of it is SGA can only be accessed on UNIX by a process running on the UNIX box and hardly anyone out there does it, not even Oracle, but there are a few 3rd party tools apparently.

Best
Kyle

In article <979300578.8175.0.nnrp-07.c30bdde2_at_news.demon.co.uk>,   "andrew_webby at hotmail" <spam_at_no.thanks.com> wrote:
> 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/
>
>

Sent via Deja.com
http://www.deja.com/ Received on Fri Jan 12 2001 - 12:47:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US