Coskan’s Approach to Oracle

June 28, 2007

Sub Latch Wait Events with weak documentation

Filed under: RAC — coskan @ 8:57 am

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.

Ps: Any comment about definition of this wait event will be welcome.


June 26, 2007

ORA-02067: transaction or savepoint rollback required

Filed under: Tips — coskan @ 8:09 am

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 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

Log Buffer #49: a Carnival of the Vanities for DBAs

Filed under: Diary — coskan @ 11:16 am

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.

Lets start the Carnival with Oracle Blog entries captured by my RSS feed reader, Sage.

Lewis Cunningham (Oracle ACE) listed his mini-tip series on his Mini-tip Aggregate List entry. These tips are very helpful for both developers and DBAs.

Writers of Eye on Oracle blog community wrote very interesting entries about Oracle world. Tim DiChiara asked 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 Brunelli summarized 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.

about truncating partitions. Data geek Beth continued to her data quality posting series with Standardizing Product Attributes post.

Alejandro Vargas , One of my favorite technical Oracle Bloggers, who has no story but so much technical useful information on his blog, wrote Production Upgrade from to guide for the ones who still find it risky to upgrade their 8i servers. Atul Kumar who totally deserves his ACE title by guiding the application DBA’s , started his Oracle SOA Suite Installation Series with database installation part. Another Oracle ACE Syed Jaffar Hussain mentioned the importance of creating password file to solve ORA-16191 Error for Standby Databases.

Dominic Brooks wrote about his approach to use Thomas Kytes famous runstats (try it I am sure that you wont leave it) script for performance tuning. Alex Gorbachev wrote about what will happen when you leave database in backup mode with a real story. Another stylish DBA of Pythian Group Christo Kutrovsky wrote about his experience as a Beta Tester of Oracle 11G . Jeremy Schneider wrote about Monitoring Oracle on NFS again with an excellent article. Kevin Closson responded to the previous Log Buffer #48 entry by Powel Barut about his Oracle over NFS approach and asserted that it is not so weird according to his I/O performance estimations while admitting the current superior performance of the FCP.

Top notch PL/SQL guru of the Oracle World, Steven Feuerstein continued his Queusiful (Quick and Useful tip on how to write programs in the Oracle PL/SQL language more effectively) series on his another blog at TOAD World community with Quseful #3: Don’t put COMMIT; in your code! post. Hampus Linden wrote about reclaiming Lob spaces by alter table shrink command. Mennan Tekbir’s
new post about shortest path algorithm demonstration on Oracle interesting is worthy to read for planning your trips πŸ™‚ .

Last advice about Oracle is not a blog entry, but it is colloquy with author of Oracle Hackers Handbook David Litchfield. He claims that SQL Server 2005 is more secure than Oracle.

Lets talk about what was written about IBM DB2 this week;

Craig Mullins told about tips of managing performance tuning on DB2 applications. Sheryl Larsen also wrote Extreme SQL Performance Techniques for DB2 on DB2 Magazine. On DB2 News & Tips blog I came into an entry which guides, where to find information about new DB2 releases.

MSSQL’s upcoming new release SQL Server 2008 with code name Katmai was the main topic of the MSSQL Server bloggers. Rafik Robeal wrote about Katmai’s strength for building offline applications. SQL Server MVP Brian Knight wrote his favorite BI features of Katmai . I think war on BI market will be escalated next year.

Jeff Smith explained, how to use group by statement to avoid self joins, by clear examples . It would be very useful if I should have learned this trick when I was a SQL Server DBA. MS SQL Master wrote about how to view backup and restore performance info and how to get blockers info from dynamic management views (DMV) of SQL Server 2005. Curt Monash wrote about how you can use MSSQL Server 2005 behind a Massively MultiPlayer Role-Playing Game .

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.

Chris Brown wrote about how much he likes the HA option on Sybase Adaptive Server Enterprise edition. Despite all his wishes for not to write a marketing related post, he wrote marketing related and he accepted that he wrote πŸ™‚ Fernando Nunes showed how to create multiple triggers on the same table for the same event on IBM Informix.

Peter K wrote his thoughts with a great post, about IT businesses never ending debate “Are Job Interviews and resumes useful or not ?

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.

Blog at