Archive for November, 2004
I do like to keep track of a number of things tangentially connected to Oracle Databases – tangential links is in fact one of my things as anyone who has conversed with me can probably testify. Anyway today two of these outside interests converged when the SQLServer Central mailing list pointed me at an (unfortunately closed) discussion over at Joel on Software. Joel is always very, very much worth reading and so the list of nominations for best essay on software development were definitely worth a look. Clearly I haven’t read all of them, but this article on XML, definitely resonates with me right now. Rather more seriously there
is also an excellent introduction to critical thinking also shortlisted. You can find that here.
Possibly Related Posts:
Thanks to a new (to me) service from Google that allows free search of academic research papers, though the results maybe behind paid for services, I came across this excellent discussion of the state of Time based performance tuning for the Oracle practitioner.
The whole appsDBA web site however is well worth a look, thorough professional and excellently laid out.
Possibly Related Posts:
Sometimes it can be easy for proponents of time based tuning (and I am one) to focus on the time breakdown, rather than the business action that is taking too long. Mark Rittman has a concrete example of this here.
Rewriting Marks results as a resource profile would give you something like this:
SELECT [list of customer address columns]
FROM CUSTOMER_ACCOUNTS WHERE CUSTOMERID = :b1
PX Deq: Signal ACK 89.70
PX Deq Credit: send blkd 16.52
CPU Time 6.11
PX Deq: Execute Reply 2.05
enqueue 1.40
PX Deq: Join ACK 1.16
PX qref latch 1.00
PX Deq: Parse Reply 0.98
PX Deq: Table Q Normal 0.08
Unaccounted For -2.09
Total 116.91
Now faced with such a resource profile, it would be very easy to conclude either;
However Mark took the better approach – that of asking what the business was trying to achieve – at which point it becomes apparent that in fact the query would be better off not being executed at all. The problem was in fact being caused in this case by attempting to do too much procedural processing in the application when a single sql statement could be used to replace multiple statements from the client. The story is available in full at Mark’s site but anyone who sees code like the pseudo code following being executed against their database might find a dialog with the app developers useful before diving into tuning the individual statements.
select [primary key values] from master where [some condition]
for each id in [list obtained above]
select [something] from detail where primary_id = id
select s[something else from sub_detail where [some join to detail table]
select [something else] from detail2 where primary id = id
end loop
Possibly Related Posts:
I promised an update on the South African and UK Oracle User Group annual conferences, and Friday afternoon would seem like a good time to do this.
South Africa
This conference took place in Sun City which is an impressive, but to western european eyes somewhat odd venue. It is a 5 star venue – but driving through dirt poor neighbourhoods with people reduced to selling fruit at intersections to get there is somewhat disconcerting to say the least.
Overall the content was disappointing. Probably the major example of this for me was the awarding of Most Innovative presentation to a product demo.
One thing that did stand out was that Open Source in general and Linux in particular has a real opportunity in this market.
UK
It felt a little odd to be in Birmingham without all the Christmas decorations up, but the content was probably better than ever for someone with a technical bent.
Highlights for me included Julian Dyke on Logical IOs, Wolfgang Brietling on tuning by setting stats with DBMS_STATS and Rachel Carmichael on How not to be a DBA.
Another theme was the number of presentations on how things can go wrong. Connor McDonald spoke humourously on bad application design and Jeremiah Wilton spoke on real disasters.
Finally for the guys from Denmark and Iceland, I have just one word.
NOW!
Possibly Related Posts:
It looks like the initial post of SimpleProfiler was premature. This is for two reasons.
1. The event detail report was inaccurate. I believe I have now fixed this.
2. There are some problems with (I believe) session state. This will take a little more work to make the pages reliable. I have therefore regrettably pulled this release for a short while.
I apologize for this, but I’d rather produce something reliable.
Possibly Related Posts:
I realize that it has been nearly a month since I posted. I have been busy with a couple of things. The first was the South African and UK Oracle User Group annual conferences. I’ll have thoughts and highlights from both of these later this week.
The second is a project that I have been working on for a while now. This is a utility for profiling Oracle based applications that I have called SimpleProfiler. The initial public release of this utility can be found here. This utility is available free of charge but still subject to the Creative Commons license. This essentially means use it however you see fit. Some rights are however reserved in particular this utility should not be used commercially.
The utility can be used to generate a resource profile from any trace file to which your client machine has access. That is you do not have to create any directory objects or be located on the database server yourself. In addition the utility will keep a repository of analyzed files. In time this will be enhanced to provide trend analysis and repository management functionality.
If you do use it and find it useful it would be nice to hear from you. If you use it and find it useless that would be useful feedback as well.