June 28, 2007

Prior to Oracle Database 10g, all latch waits show up as the latch free wait event . In Oracle Database 10g, common latches are broken out and have independent wait event names and statistics but documentation about this independent wait event statistics are forgotten.

Yesterday I saw long waits of “latch: KCL gc element parent latch ” event on 10046 trace output of one of our sqls. I searched the documentation, metalink and google, all I have found is 15 bug pages on metalink, no page on documentation, 6 pages on google and all of this pages are not talking about defintion of event. They only show that this event has a place on the tkproof outputs. But my sql is suffering from this event what should I do now to lower this long waits???.

I hope documentation about latch sub events will be more definitive on upcoming 11G.

June 26, 2007

I saw this error on logfile of an archiving batch job . The batch job works on production database and it inserts the row to the archive database over database link and after insertion it deletes the row from the production database . When I looked at the error definition I got confused ??? The error definition says

ORA-02067:

transaction or savepoint rollback required

Cause:

A failure (typically a trigger or stored procedure with multiple remote updates) occurred such that the all-or-nothing execution of a previous Oracle call cannot be guaranteed.

Action:

rollback to a previous savepoint or rollback the transaction and resubmit.

There wasn’t a trigger on the remote database table and this archiving batch was working every weekend, without any exception. There must be something new and weird. After googling I found the reason on forums.oracle.com thread. It was a transaction of an inactive session which was on the archiving table on the remote database. I killled the session, restarted the batch and went to get a pint of ale :)

June 15, 2007

Here comes the 49th edition of Log Buffer. Thanks to Dave Edwards from Pythian Group for giving this nice opportunity. I hope I won’t let him and the readers down with this post :)

I want to categorize the entries with vendors for helping the readers to focus on their interests. To be honest I can’t find enough time to follow non-oracle blogs so I must thank again to Dave for sending me the non-oracle blog links of this week.

Writers of Eye on Oracle blog community wrote very interesting entries about Oracle world. Tim DiChiaraasked a provoking question “Is Database administration for suckers“ ? In my opinion being DBA is for the ones who really loves to devote himself to database technologies. Please don’t forget to read the comments of this post, I bet you will enjoy them. Another Eye on Oracle writer Mark Brunellisummarized the thoughts of other bloggers about upcoming Oracle release 11g. Read them to be ready, when your boss ask “tell me my dba what does 11g promise us”. Believe me they will ask :)) . Finally Elisa Gabbert wrote about some headlines from Oracle blogosphere agenda.

MySQL bloggers were very fruitful this week. It was very hard to choose between them because of my unfamiliarity with MySQL technology, but thanks to my colleague Orhan Biyiklioglu who helped me in the decision process. It seems that the MySQL blogosphere was mostly busy with the The 12 Days of Scaleout campaign of the MySQL AB as pointed out by Zak Greant. Ronald Bradford summarized some of the tips he found useful about Wikipedia’s use of MySQL replication to scale-out their database infrastructure on his article MySQL – Wikipedia. Giuseppe Maxia has provided a tip on using MySQL 5.1 Beta on production systems to benefit from its new Partitioning features without risking your data. Peter Zaitsev’s article explaining the problems about using stored procedures in MySQL is very noteworthy. MySQL guys should think twice before using them. MySQL Cluster SQL Tips by Brian Moon seems also to be useful for using joins on a MySQL cluster. Xaprb published a fast, three part, series on Archive strategies for OLTP servers. 1, 2, 3 well worth a reading by the serious MySQL guys.

I think it is a little long, I hope you did not get bored. It was a great pleasure for me to feel like an editor of all blogs :) . I finished my turn and start to wait for the 50th edition of Log buffer on 22th of June.