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 01:53:27 GMT
Message-ID: <93lo2k$9k0$1@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













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!





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 Thu Jan 11 2001 - 19:53:27 CST












Original text of this message













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