Home » Other » Marketplace » Oracle Direct Session Memory Access - Free and open source
Oracle Direct Session Memory Access - Free and open source [message #652214] Sat, 04 June 2016 07:40 Go to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Hi All,

I've been working on a application which unitilises direct memory access and reads data directly from the databases SGA. I'm hoping that some of you might be interested in looking at the current beta version and willing to provide me with some feedback, good or bad!

LINKS:

* GitHub for source code: https://github.com/dba-bots/DirectSessionMemoryAccess
* SourceForge for dsma-*.tar.gz: https://sourceforge.net/projects/dsma

Small summary:

DSMA attaches to an Oracle session and reads data directly from Oracle's SGA. At present DSMA will return the following equivalent SQL statement:

select SEQ#, EVENT, P1TEXT, P1,P2TEXT, P2, P3TEXT, P3, PROGRAM, PROCESS, SQL_HASH_VALUE, PREV_HASH_VALUE from v$session;

Contact details:

email: neilc@dba-bots.com
Follow us on Twitter: @dbabots


Re: Oracle Direct Session Memory Access - Free and open source [message #652244 is a reply to message #652214] Sun, 05 June 2016 05:00 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
Wow, this looks very interesting! I've noticed the following in the README: "Database must be using SGA_TARGET and not ASMM". I thought SGA_TARGET was used to switch on ASMM (Oracle 10g), although I may be wrong. Anyway, this may be a showstopper for some, as most newer database would use MEMORY_TARGET and not SGA_TARGET.

Regards.
Frank
Re: Oracle Direct Session Memory Access - Free and open source [message #652245 is a reply to message #652244] Sun, 05 June 2016 05:34 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Hi Frank,

Thank you for your kind comment and for highlighting a valid point. Thanks for highlighting my typo, ASMM, should read AMM!

Most databases I've worked with have utilized HugePages which require ASMM (SGA_TARGET) so I guess is was a natural starting point.

Here are a couple of interesting articles regarding HugePages and why you might configure them:

http://www.oracle.com/technetwork/articles/servers-storage-dev/hugepages-2099009.html

https://docs.oracle.com/cd/E37670_01/E37355/html/ol_about_hugepages.html

[Updated on: Sun, 05 June 2016 06:02]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652246 is a reply to message #652245] Sun, 05 June 2016 05:46 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
Thank you for the clarification.
Re: Oracle Direct Session Memory Access - Free and open source [message #652247 is a reply to message #652214] Sun, 05 June 2016 09:28 Go to previous messageGo to next message
John Watson
Messages: 7185
Registered: January 2010
Location: Global Village
Senior Member
Could your program be used for hang analysis, if the instance is on a state where you can't even log on? It would need to show columns such as EVENT and BLOCKING_SESSION.
Re: Oracle Direct Session Memory Access - Free and open source [message #652248 is a reply to message #652247] Sun, 05 June 2016 09:45 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
Just tested it and it works as advertised (got it running is less than 10 minutes).

To compile, I had to change 'initdsma.bsh' to supply a password for SYS. This won't normally be required, but I had to do this:
sqlplus -S sys/oracle as sysdba

It core dumped with AMM. I then enabled ASMM (as per README) and bounced:
SQL> alter system set memory_target = 0;
SQL> startup force;

Recompiled:
./initdsma.bsh -d orcl -v 12102

And tested:
$ ./dsma.orcl 2
Time between wait     0.0000094472 secs 
 WAIT# 1 waiting for event: 'null event' Class:Other
 	 P1: ''=65538 P2: ''=139661606903408 P3 ':'=81
 	 command= ospid=0 sqlhv=0 psqlhv=1936681068
Re: Oracle Direct Session Memory Access - Free and open source [message #652250 is a reply to message #652247] Sun, 05 June 2016 12:10 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

John Watson wrote on Sun, 05 June 2016 15:28
Could your program be used for hang analysis, if the instance is on a state where you can't even log on? It would need to show columns such as EVENT and BLOCKING_SESSION.


Hi John,

I believe it would be helpful in such a situation.

I've managed to get EVENT, P1, P2 and P3 information from memory, but unfortunately BLOCKING_SESSION doesn't have a fixed offset so I'm unable to set this value at compile time to enable DSMA to get such information from memory. However in the next release I plan to add ROW_WAIT_OBJ# , ROW_WAIT_FILE# , ROW_WAIT_BLOCK# and ROW_WAIT_ROW# which would give a few more vital clues if DSMA was used in such a situation, would you agree?

I find DSMA really good as a session profiler. Given the very high sample rate, along with the events data and timing information it all really helps identify bottlenecks.

I'm also currently looking at add a number of flags to DSMA, which will enable a user to run in a certain mode, hang analyses or profiler so the relevant information can be included/excluded where applicable.

Regards

Neil
Re: Oracle Direct Session Memory Access - Free and open source [message #652251 is a reply to message #652247] Sun, 05 June 2016 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Sun, 05 June 2016 16:28
Could your program be used for hang analysis, if the instance is on a state where you can't even log on? ...


No it can't as it requires to first connect as sysdba to retrieve some information like shmid and address of each session entry.
The first part can be workaround using sysresv instead of oradebug (and so without any connection).
The second is harder to workaround without connecting as x$ksuse (v$session underlying "table") can be split into several arrays. You can see that using the following query which gives the distinct gaps between entry; if your "sessions" parameter is big enough you will see several values (double the X if you are in 64-bit).
select distinct to_number(addr,'XXXXXXXX')-to_number(lag(addr) over(order by addr),'XXXXXXXX') from x$ksuse;

I think it is possible to not need this if you change the program and get the values from the SGA itself but this need a serious study on x$ksuse structure.

I wrote something like that in the previous century and upgrape it as I said a decade ago (up to 10gR2). The main problem is that in the current versions the SGA can be spread over several shared segments and all the structures are automatically split (v$latch/x$ksllt) or as soon as they are large enough (v$session/x$ksuse, v$session_event/x$ksles)...
I stopped working on this program (which original aim was to query any x$ table or SGA structures) because most of the time when I come at client's there was, for security safe, no C compiler on the servers and even when there is one they refuse we compile our own programs which is understandable.

You will find a definitive presentation of how to access directly the SGA by Kyle Hailey there and complete demonstration code by Miladin Modrakovic there.

Re: Oracle Direct Session Memory Access - Free and open source [message #652252 is a reply to message #652251] Sun, 05 June 2016 12:35 Go to previous messageGo to next message
John Watson
Messages: 7185
Registered: January 2010
Location: Global Village
Senior Member
I see, thank you for explaining. I have never looked into the possibility, though I intend to do so. This is the motivation:

When Oracle introduced ASH, they cut the ground out from beneath all third party providers of administration tools because ASH can use a background process to go directly to the C data structures that make up the instance. This makes it possible to do per second sampling. Any third party product (such as Tanel Poder's Snapper, or the Quest product set) has to go through the SQL interface which can never be as efficient. Then in release 12 Oracle introduced Emergency Monitoring and Real-Time ADDM, which should let you query the instance even when you can't log on - if you have OEM running. That is also something no-one else can do.

If someone writes code that can do the same and doesn't need Enterprise Edition plus packs, it would be highly marketable. Let me know, people! My boss would be very interested in promoting it.
Re: Oracle Direct Session Memory Access - Free and open source [message #652253 is a reply to message #652251] Sun, 05 June 2016 12:37 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Hi Michel,

The tool hasn't been designed to be intrusive so I felt no reason not to use SYSDBA privs or tools DBAs know and use.

Michel Cadot wrote on Sun, 05 June 2016 18:17

John Watson wrote on Sun, 05 June 2016 16:28
Could your program be used for hang analysis, if the instance is on a state where you can't even log on? ...


No it can't as it requires to first connect as sysdba to retrieve some information like shmid and address of each session entry.


Slightly unfair statement, so long as DSMA was setup prior to a database hang the tool could be used.

Michel Cadot wrote on Sun, 05 June 2016 18:17

The first part can be workaround using sysresv instead of oradebug (and so without any connection).


You are correct "sysresv" could be used, but then you might aswell go to /proc/[PMON_PID]/maps which would save calling "sysresv" in the first instance.


[Updated on: Sun, 05 June 2016 12:41]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652254 is a reply to message #652250] Sun, 05 June 2016 12:45 Go to previous messageGo to next message
John Watson
Messages: 7185
Registered: January 2010
Location: Global Village
Senior Member
Neil, you'll have seen my answer to Michel. Can you compare your tool and where it might go against Tanel Poder's Snapper, http://blog.tanelpoder.com/files/scripts/snapper.sql ? Many people swear by Snapper (I don't use it myself) but I've always thought that it suffers from going through the SQL interface. Your product could be really good (and monetizable).
Re: Oracle Direct Session Memory Access - Free and open source [message #652255 is a reply to message #652253] Sun, 05 June 2016 12:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Slightly unfair statement, so long as DSMA was setup prior to a database hang the tool could be used.


Not unfair, just how I see my clients working, they never allow a permanent program hacking the memory structures running (maybe the term hacking is not the one you want to hear but it actually is and this is not a term of abuse for me just a method to work as long as it is done transparently and with the agreement of the client). They allow them ONLY when a hang situation occurs (if they allow it at all). Carefully note that for the client if you can read the memory you can read their data with no possible audit.

Quote:
but then you might aswell go to /proc/[PMON_PID]/maps


As far as I know, only if you are root. I mention sysresv because it is a standard Oracle program and nobody can blame you to use it (in the limits of your mission, of course).

Take no offense of what I say, it is just the thoughts of someone who has worked for a long time on this kind of subject (on both sides: the DBA and the security officer).

Re: Oracle Direct Session Memory Access - Free and open source [message #652256 is a reply to message #652252] Sun, 05 June 2016 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If someone writes code that can do the same and doesn't need Enterprise Edition plus packs, it would be highly marketable. Let me know, people! My boss would be very interested in promoting it.


Hmm, maybe it is worth I go back to my program and make it work in the current versions. Wink
I thought at this time this was possible, and I'm still thinking it is.
Just remember that x$ tables are not just array in the SGA, they mostly are procedure calls to display some information in relational format which require not so simple algorithm. v$session_wait is one of the simplest x$.

Re: Oracle Direct Session Memory Access - Free and open source [message #652257 is a reply to message #652255] Sun, 05 June 2016 13:06 Go to previous messageGo to next message
John Watson
Messages: 7185
Registered: January 2010
Location: Global Village
Senior Member
Quote:
just how I see my clients working, they never allow a permanent program hacking the memory structures running
I think this is what Uncle Oracle relies upon. The OEM agent does just this to enable the emergency monitoring facility, but because it OEM, no-one objects. Any third party product would have to be validated by a reputable source. Larry, of course, would never validate it.
Re: Oracle Direct Session Memory Access - Free and open source [message #652258 is a reply to message #652255] Sun, 05 June 2016 13:34 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Michel Cadot wrote on Sun, 05 June 2016 18:50

Quote:
Slightly unfair statement, so long as DSMA was setup prior to a database hang the tool could be used.

Not unfair, just how I see my clients working, they never allow a permanent program hacking the memory structures running (maybe the term hacking is not the one you want to hear but it actually is and this is not a term of abuse for me just a method to work as long as it is done transparently and with the agreement of the client). They allow them ONLY when a hang situation occurs (if they allow it at all). Carefully note that for the client if you can read the memory you can read their data with no possible audit.


The program will run so long as it has been compiled prior to a database hang occurring. Whether or not such a tool would be aloud by a client is another matter..

Michel Cadot wrote on Sun, 05 June 2016 18:50

Quote:
but then you might as well go to /proc/[PMON_PID]/maps

As far as I know, only if you are root. I mention sysresv because it is a standard Oracle program and nobody can blame you to use it (in the limits of your mission, of course).



/proc permissions are 0555, anyone has read


Michel Cadot wrote on Sun, 05 June 2016 18:50

Take no offense of what I say, it is just the thoughts of someone who has worked for a long time on this kind of subject (on both sides: the DBA and the security officer).


None taken.
Re: Oracle Direct Session Memory Access - Free and open source [message #652259 is a reply to message #652258] Sun, 05 June 2016 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
/proc permissions are 0555, anyone has read


/proc ok as process should able to read his own information but /proc/<another process>/<something>, no, as far as I remember but I have no Unix/Linux to check, I just remember having done that and having to grant sudo to get data for some Oracle processes (but not all of them) and it also depends on the <something>.

Re: Oracle Direct Session Memory Access - Free and open source [message #652260 is a reply to message #652257] Sun, 05 June 2016 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Sun, 05 June 2016 20:06
Quote:
just how I see my clients working, they never allow a permanent program hacking the memory structures running
I think this is what Uncle Oracle relies upon. The OEM agent does just this to enable the emergency monitoring facility, but because it OEM, no-one objects. Any third party product would have to be validated by a reputable source. Larry, of course, would never validate it.


Indeed.

Re: Oracle Direct Session Memory Access - Free and open source [message #652261 is a reply to message #652260] Sun, 05 June 2016 14:26 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

I'm in the process of putting together some formal documentation for DSMA.

I plan to cover:

* How and why I came about writing DSMA
* The design descisions I made and why
* Its usages and benefits, apart from being free!!

It's been two years in the making and I've probably hit every pitfall going, some Michel has already highlighted!

Please contact me if you have any questions, feedback or just to let me know how you're using it Smile

Regards

Neil

Re: Oracle Direct Session Memory Access - Free and open source [message #652309 is a reply to message #652252] Mon, 06 June 2016 15:40 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

John Watson wrote on Sun, 05 June 2016 18:35
I see, thank you for explaining. I have never looked into the possibility, though I intend to do so. This is the motivation:

When Oracle introduced ASH, they cut the ground out from beneath all third party providers of administration tools because ASH can use a background process to go directly to the C data structures that make up the instance. This makes it possible to do per second sampling. Any third party product (such as Tanel Poder's Snapper, or the Quest product set) has to go through the SQL interface which can never be as efficient. Then in release 12 Oracle introduced Emergency Monitoring and Real-Time ADDM, which should let you query the instance even when you can't log on - if you have OEM running. That is also something no-one else can do.

If someone writes code that can do the same and doesn't need Enterprise Edition plus packs, it would be highly marketable. Let me know, people! My boss would be very interested in promoting it.


I've just released beta v2. Now you do not need to no the SID prior to running DSMA. Running DSMA without passing in a given SID will print an overview of all active sessions. You can then pass in a given SID to get a more detailed output regarding wait event history.

/forum/fa/13160/0/

I've uploaded some screen shots on SourceForge

[Updated on: Mon, 06 June 2016 15:41]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652310 is a reply to message #652309] Mon, 06 June 2016 16:10 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Once you have ran initdsma.bsh for a given Oracle database you can call the program at anytime by running the executable DSMA.DBNAME

If you couldn't connect to the database, say via sqlplus DSMA will still work, so long as the SGA remains online and the DSMA.DBNAME executable was complied prior to this event occurring.

You just need to remember to re-run the initdsma.bsh after the database has bee bounced so it's there when you need it Smile

[Updated on: Mon, 06 June 2016 16:23]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652372 is a reply to message #652310] Tue, 07 June 2016 15:44 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

My apologies if anyone tried to download DSMA-beta-v2 this afternoon and was unable to!

I have since moved the source code from GitHub into SourceForge. You can either download the tar ball or the individual files from one location now.

DSMA-beta-v3 is now in development. I've uploaded the source code so people can still download and use the latest offering. It includes a fix regarding a BUG I found when running DSMA under Oracle 11.2.0.4 and I've added the EVENT data when running without passing in a SID

I've also created a number of message boards in sourceforge, one of them is for reporting bugs Smile

/forum/fa/13162/0/

Regards


Neil
  • Attachment: image.jpg
    (Size: 360.43KB, Downloaded 314 times)
Re: Oracle Direct Session Memory Access - Free and open source [message #652399 is a reply to message #652372] Wed, 08 June 2016 02:54 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
I've installed v.3 as well. Let's use it to resolve a blocking lock:

$ ./dsma.orcl | grep sqlplus
 SID                EVENT                   PROGRAM                         OSPID
  44 SQL*Net message from client  sqlplus@localhost.localdomain (TNS V1-V3)  4155
  47 enq: TM - contention         sqlplus@localhost.localdomain (TNS V1-V3)  4187

So, let's look at both sessions:

$ ./dsma.orcl 44
Time between wait     0.0000048355 secs 
 WAIT# 407 waiting for event: 'SQL*Net message from client' Class:Idle
 	 P1: 'driver id'=1413697536 P2: '#bytes'=1 P3 ':'=0
 	 command=LOCK TABLE ospid=4155 sqlhv=937634959 psqlhv=937634959

$ ./dsma.orcl 47
Time between wait     0.0000032143 secs 
 WAIT# 64 waiting for event: 'enq: TM - contention' Class:Application
 	 P1: 'name|mode'=1414332419 P2: 'object #'=92264 P3 ':table/partition'=0
 	 command=DELETE ospid=4187 sqlhv=147427357 psqlhv=3933222116

From the above it's clear that session 47 is hanging, waiting for session 44 to release a lock.
Easy to diagnose on a not-so-busy database.

Some suggestions, if I may:
* Add BLOCKING_SESSION in the detail display (as indicated by John above).
* Introduce an option to only list active sessions.

SQL> SELECT event, status, blocking_session FROM v$session WHERE sid IN (44, 47);

EVENT			       STATUS	BLOCKING_SESSION
------------------------------ -------- ----------------
SQL*Net message from client    INACTIVE
enq: TM - contention	       ACTIVE		      44

Best regards.
Frank
Re: Oracle Direct Session Memory Access - Free and open source [message #652428 is a reply to message #652399] Wed, 08 June 2016 12:23 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

Hi Frank,

Many thanks for taking the time to review my program again.

Having had a GitHub 101 from a friend! I've reinstated the repo! GitHub will now be the main home for the project. I've uploaded all the beta version branches and beta-v3, the last beta version has been released!

GitHub: https://github.com/dba-bots/DirectSessionMemoryAccess
Wiki: https://github.com/dba-bots/DirectSessionMemoryAccess/wiki

I will continue to upload each release in a tar ball form to SourceForge: https://sourceforge.net/projects/dsma

[Updated on: Wed, 08 June 2016 12:49]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652472 is a reply to message #652428] Thu, 09 June 2016 11:57 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

It's been a long week! But I'm pleased to announce our first stable release:

DirectSessionMemoryAccess-v1.0

You'll also be pleased to hear the release cycle will now slow right down! I hope now we've moved away from beta releases more people will be willing to give it a try and see what it has to offer.

A huge thank you to everyone who gave up there time to; download and try, write a review, leave a comment and provide feedback during the beta phase.

The GitHub wiki now includes; The storey behind DSMA, FAQs and usage cases Smile

Cheers

Neil

[Updated on: Thu, 09 June 2016 13:57]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652478 is a reply to message #652472] Thu, 09 June 2016 14:59 Go to previous messageGo to next message
Frank Naude
Messages: 4531
Registered: April 1998
Senior Member
Congratulations on your first stable release!
Re: Oracle Direct Session Memory Access - Free and open source [message #652521 is a reply to message #652478] Sat, 11 June 2016 07:41 Go to previous messageGo to next message
dba-bots
Messages: 15
Registered: June 2016
Junior Member


Oracle SGA management[ 3 votes ]
1. Automatic Shared Memory Mangement 1 / 33%
2. Automatic Memory Management 2 / 67%

I've been looking into the possibility of adding support for AMM into DSMA and would appreciate if you could vote on which current memory management option you use most with Oracle.

This will help me gauge if the dev effort to add AMM support into DSMA is worthwhile.

Many thanks


Neil

[Updated on: Sat, 11 June 2016 08:02]

Report message to a moderator

Re: Oracle Direct Session Memory Access - Free and open source [message #652522 is a reply to message #652254] Sat, 11 June 2016 08:20 Go to previous message
dba-bots
Messages: 15
Registered: June 2016
Junior Member

John Watson wrote on Sun, 05 June 2016 18:45
Neil, you'll have seen my answer to Michel. Can you compare your tool and where it might go against Tanel Poder's Snapper, http://blog.tanelpoder.com/files/scripts/snapper.sql ? Many people swear by Snapper (I don't use it myself) but I've always thought that it suffers from going through the SQL interface. Your product could be really good (and monetizable).


Hi John,

Having had little chance to have a look at Snapper, which I myself have never used either; I now know both tools sample the GV$SESSION view. Maybe someone with experience of Snapper and who has tried DSMA would be willing to make a comparison?

Hopefully you might have had a look at DSMA yourself and I would be keen to hear your comparison.

Regards

Neil




[Updated on: Sat, 11 June 2016 11:33]

Report message to a moderator

Previous Topic: DBA mentoring service: teaching and consulting for the price of one
Next Topic: Advice required
Goto Forum:
  


Current Time: Fri Dec 15 06:42:15 CST 2017

Total time taken to generate the page: 0.01538 seconds