Keeping contact

It is now nearly one month I signed up for facebook and yes – I am still alive and doing fine :-). But facebook certainly did give me some things to think about. First of all I think it is an amazingly nice tool to keep contact with a family member far, far away like my niece in Australia. We all who are interested in her well being can follow her and every time we find an entry we know she is alive and is doing fine. On the other side she knows what’s up on this side of the world without spending her money on the telephone companies. Yet her mother (e.g. my sister) has spent nearly one year in Australia herself – only about 25 years ago – and we all survived without facebook. But when I imagine being a mother with a child on the other side of the world I would want to have something like facebook very dearly.
But then I am reading all the other stuff people publish and I think to myself that my day did not really improve by that. There are things that are nice to know (like people marrying or getting kids) – but I would have learnt them the sooner or latter anyway. So facebook provided me kind of a gain in time – if useful or not is up to yourself to decide. Other things I could have lived very well without knowing them (e.g. who was pissed, when or why) and these do by far outnumber the useful entries. For me it is very much like television – if you choose carefully what to watch (or read) it is a nice pastime, otherwise it is a waste of time.
Just another thing are these entries in times like this: it is so easy to write “Merry Christmas and a Happy New Year to you all” and all my friends might read it or not. But if I really want to wish something to somebody I’d rather make sure the wishes arrive properly. That does not leave very much but calling the people I care for – which gives me above all the chance to exchange some words with them. Now you might hold against that there are by far too many people you have to call, but I admittedly select very carefully whom I call shortly before christmas and I always do end up with enough time left in case I forgot somebody (what of course could not happen if I used facebook!). I also usually split it and call some people before christmas and others before New Years Eve. After all I do know quite a lot of people but for sure there are some nearer to my heart than others… And if calling is not possible there is still good old email which at least allows me some personalized message.
One thing yet did not prove to be right – I do not have to fear that because I am on facebook people have any reason to think bad of me. I can very easily choose which information I reveal about myself and to whom. It is my choice to share something on facebook or to comment on other peoples messages. Even if I am marked on someone else’s picture I have it in my hands to leave the mark or delete it. So it seems to be true what is valid for most amazing tools: it always depends how you use it…

Tracing MS Dynamics AX on Oracle

Performance tuning normally requires an SQL trace. If you ever tried to produce one for MS Dynamics AX on Oracle you might have realized that this is not an easy task. At least I did have a couple of difficulties in producing one so I want to share here what I found out about it.
When using MS Dynamics AX you usually do not know which or how many sessions will be created on the database. It is even hardly possible to find a connection between the AX user and the Oracle session. So

alter session set events ’10046 trace name context forever, level 12′

is out of question as is every other method to trace one specific session. That leaves nothing but dbms_monitor, as long as you are using Oracle 10g or above. Naturally it is much easier to trace a slow running functionality if you have a dev system without too much traffic.
I am using MS Dynamics AX 4.0 on Oracle 10.2.0.3 and have an sysdba access to the database as well as access to the directory where trace files are stored. As I trace with dbms_monitor I also need to know which sessions were active during the analysis, so I can extract the relevant parts from all trace files with trcsess. The last step then will be tkprof.

Step 1: v_$session

First of all you should have a look at v_$session – it will show you two important things unless you know them already:
1.) the osuser AX is using – you will need that one to identify the sessions during your analysis
2.) the service_name of the database – this is a necessary information for enabling dbms_monitor
When you start the functionality in AX you want to analyze you do not know which or how many sessions on oracle are involved. There are several ways to keep track of them – for me that one worked best:
- I created a new table which will be truncated every time I start an analysis
- Before starting the functionality in AX I start a job that will insert me every 5 seconds all sessions running with the AX osuser in that afore mentioned table:

insert into trace_table
select sid, serial#, audsid, service_name, module, username, status, osuser, process, sql_id, sql_child_number, sql_hash_value, prev_sql_id, prev_child_number, prev_hash_value
from sys.v_$session
where osuser=…

Step 2: dbms_monitor

I am using dbms_monitor.serv_mod_act_trace_enable and I have tried to specify the module but I did not succeed – the reason why I have not figured out yet. AX does not specify any actions so the only information to start the monitor is the service_name from v_$session. Assuming the service_name is ‘DEV’ the statement will have to look like

dbms_monitor.serv_mod_act_trace_enable (service_name=>’DEV’,
module_name=>DBMS_MONITOR.ALL_MODULES, action_name=>DBMS_MONITOR.ALL_ACTIONS, waits=>TRUE, binds=>TRUE)

– very important: including wait information and bind variables. But be careful – Tracing is persistent, even across instance restart! So don’t forget

dbms_monitor.serv_mod_act_trace_disable(service_name=>’DEV’,
module_name=>DBMS_MONITOR.ALL_MODULES, action_name=>DBMS_MONITOR.ALL_ACTIONS)

afterwards…

Step3: MS Dynamics AX

Now you can start the functionality in AX that does not perform – e.g. a dunning run. As already mentioned it is highly advisable to do so in a test environment with not very much traffic because otherwise you will not be able to identify the sessions used. An alternative could be identifying the SQL ID with ASH – any functionality worth analyzing will appear here. Thus you will also know the session details, but you might not find out which other session(s) you have to take into account. Moreover to complete the trace the sessions in question have to be disconnected before you disable dbms_monitor – if you do so on the productive system you might have some user complaints afterwards…

Step 4: trcsess

As soon as you finished the task in AX, have disconnected the sessions in question and disabled dbms_monitor you will find you produced a whole lot of trace files and you will have to eliminate the wanted information with trcsess. Now the information you hopefully gathered out of v_$session comes very handy – with sid and serial# known you do not have to specify the trace files you want to use for trcsess. Specially in a test environment you will not get too much information if you have trcsess scan all available trace files so your command line text should look like

trcsess output=choose_a_name.trc session=sid.serial#
(for example: trcsess output=sess2165.trc session=2165.13898)

Doing this I encountered a couple of times an error – mainly when I closed AX before finishing the task:

oracle.ss.tools.trcsess.SessTrcException: SessTrc-00001: Interner Fehler bei Session Trace: Datei kann nicht geöffnet werden.D:\oracle\product\10.2.0\admin\DEV\udump\dev_ora_5032.trc

telling you trcsess cannot open one of the trace files. If you want to know what process uses this file you have to query v_$process:

select S.SID, P.SPID, S.SERIAL# as session_serial, S.audsid, S.USERNAME, s.status, s.osuser, s.program, s.machine, s.module, s.action, s.client_info
from v_$session s, v_$process p
where P.ADDR=S.PADDR
and P.SPID in (5032)

taking the p.spid from the trace file name in the error message. But regardless what process it is – the only way I was able to get rid of that message was to kill the process with

orakill dev 5032

in the command line – just another reason to have a rarely used dev system, where it might not matter too much if you kill some processes…

Step5: tkprof

You will find tons of information about tkprof, so I will reduce it to the command I use:

tkprof sess2165.trc tkprof_sess2165.txt explain=schema@dev/pwd sort=fchela

which produces the tkprof output named “tkprof_sess2165.txt” including the explain plan and is sorted by elapsed time fetching data – this proved to be the best sort order for most problematic AX functionalities.

Step by Step

1.) I start a job that gathers information from v_$session every 5 seconds and puts them in a table
2.) I run “dbms_monitor.serv_mod_act_trace_enable”
3.) I start the functionality to be analyzed in AX
4.) When AX is finished, I stop the job inserting information from v_$session
5.) I check the inserted rows to find out which sessions have been involved
6.) I close AX and disconnect or kill the sessions involved
7.) I run “dbms_monitor.serv_mod_act_trace_disable”
8.) I use trcsess to get one tracefile per session involved
9.) For every session involved I produce a tkprof output

Thus you made the first step towards solving any performance problem in AX, which leaves “only” to interpret the tkprof output – but thats just another story (which fills a couple of books and will not be a post in this blog…)

Sigh…

I refused to sign up for facebook when everybody else around me did because I did not want to drink the kool-aid. But three days ago my niece left for Australia and she will be abroad for approx. one year. Of course she decided to keep us updated via facebook… I signed up today.

Having a cold

Do you know how it feels to have a cold? Your whole body is aching, your head feels as if there was a little man with a drum in it, you cannot eat properly because of a sore throat and as visual sign your nose is running. So how do you behave? If possible you stay in bed, you will drink loads of hot tea and if it is unavoidable to go out you will put on something really warm.
But what can you do if it is your dog being the one with a cold? The world’s best dogs nose is running like hell and from time to time he starts sneezing a couple of times. When he is sleeping he sometimes awakes gasping for air like you do when you have a clogged nose. I am only waiting for the moment when he tries to put his paws above his head to make the little man with the drum go away. Yet he is still happy with his food, he is drinking enough and when we are going for a walk he is just happily running around as ever. So I will not take him to the vet, but I will give him Bella Donna (helps with inflammation) and take care that our walks do not take too long. And if the cold does not go away I might try to teach him to blow his nose!

Summer vs. Winter

Once upon a time – when I was much younger than I am now – I preferred winter to summer due to my lacking ability to cope with high temperatures. I also found that you can protect yourself from low temperatures by putting on more clothes – so how to protect from hot days? But now as I am growing older it is just the other way round – if it only could be summer the whole year long. There are some reasons for that:
1.) Summer provides much more daylight and being shortsighted daylight is essential for a good sight.
2.) wearing more clothes also means being much heavier and as I am growing older my back is growing weaker so every ounce counts. Also I find it very hard to dress sexy on cold winter days…
3.) Taking my dog for a walk is not the problem, but coming home tends to become a very time consuming procedure…

I do not know where you have been but I know for pretty sure where you are going to now!

Fortunately the world’s best dog usually goes into the tub by himself:

…well – almost by himself, a little help might be necessary from time to time:

Like most Retrievers the world’s best dog loves water…

…unless of course it comes out of a shower…

But everything comes to an end and we are looking forward to some biiiiig reward

YES, nowadays I do prefer Summer to Winter!!!

pics by courtesy of charlie

Oracle 10g Performance Analysis with Doug Burns

I do get regularly post from Oracle University. When this announcement landed in my inbox I first feared the seminar might be too sophisticated for me. But giving it a second thought I decided I would try to talk my boss into it and I succeeded – I was permitted to participate. And I am glad I was persistent – this was definitely the best Oracle seminar I ever attended. Doug Burns is not only an absolute expert when it comes to Oracle but he is also an exceptional teacher. He has a very good feeling when people need a break and knows how to transport technical matter in a way easy to unterstand. His enthusiasm is catching and he is most willing to answer any question. There is only one thing I did not like very much – one of his first sentences was “I am an old man”. This guy is only two years older than me! I am not old! I do not feel old! And I will not be old in two years!!!

Some topics from this seminar and how they affect me:

Performance Principles
It is not us (e.g. the IT department) who decides if performance is ok or not – we have got to talk to the users (as I am constantly avoiding to do so I have got to find somebody who will do it for me!).
Traditional Tools
I have already heard that something like Tracing exists – I have even seen some tkprof. But I have never been able to read these things – I might give it another try. I will only have to talk our DBA into giving me access to the DB-server to find my trace files…
Active Session History
Now that is something I have already seen and I have also been working with. But Dougs explanations will make me see ASH in another light and with this information everything gets a little bit clearer. As I already guessed – there is much more to it than I knew!
Active Workload Repository
That one was completely new to me, but it came very handy – I will be able to bother my DBA even more with the information I get out of AWR!
Automatic Database Diagnostic Monitor
Ok, so now I also know what this section of the Performance page in OEM is about – it will be very useful to me specially when we expect a high workload on our database.
SQL Tuning for DBAs
When Doug was talking about bind variable peeking and histograms some proceedings in our database suddenly became very clear to me. Unfortunatly I was already to tired to ask more questions about SQL Profiles in combination with third party applications.
Oracle 11g Improvements
By then I was simply exhausted. And having migrated from 9i to 10g only 18 months ago it will be not within the near future that I might get in touch with 11g…

If you ever have the chance to attend this course – do it!

Support for the Toad

I am using Toad since 2004 and tried every new version. Since a couple of years now I am using 9.0.1.8 and I am really happy with it. True – from time to time it gives an application error, but that is a minor issue for me. And: this is afaik the last version where data could be exported to files using command line options by producing a textfile containing the query. Every version afterwards uses Actions to do so and none of them worked for me.

But as I am a curious person and nearly always willing to learn I had one of the latest version installed - 10.1.0.7. Only recently I started playing around and immediately had a couple of problems, one of them being the time needed to start the application (truly measured 60 sec mininum which is for an impatient person very much!). So as responsible and knowing user of the toad I produced the Support Bundle and contacted Support. The answer came quickly:

Please can you give me step by step information on how to reproduce this issue?

I was very tempted to answer: Find the Toad icon on your desktop. Double click it. And then wait…

Of course I did not do it – I am a polite person – sometimes…