debugging delete with referential integrity

I have been working on a performance issue on an RAC database. I really wanted to get to the bottom of the issue and when I did not know how to proceed any further I turned to twitter to ask for a brainstorm of suggestions and things to try.

This started a tremendous chain of tweets from a bunch of very bright people that came up with their own theories and suggestions. But since twitter is very limited in showing tests, code and results I would like to keep these in this blog post, maybe this can be of help to some people later.
Continue reading

OTN LA Tour 2015: Panama

“Oh wie schön ist Panama!” is a famous german children’s book that I grew up with. It is a about a bear and a tiger who decide to leave home for the land of their dreams after finding an empty box of bananas that still has a tropical smell. The wander off only to be misdirected and walk in circles until they arrive back where they started. But since it has been months since they left they don’t recognize it as their home anymore, call it Panama and live happily everafter.

OUG PanamaAnd now, after 35 years I have beaten bear and tiger and actually found Panama after a short flight from El Salvador. The sun had almost set but it was still hot and humid and our driver took us through forest of green trees and glassy skyscrapers.

The conference next day took place in a hotel adjacent to the largest mall in the Americas! The Albrook Mall has over 500 shops, some of them twice at opposite ends. The conference featured three tracks, and some of us were scheduled to deliver three presentations in a row which sounded pretty daunting at first but ended up very well since it also allowed to interact better with the audience, cut one presentation a bit shorter for the benefit of another one that was more interesting to the attendees. After this fruitful event, Edgardo took us out to a place that did not only offer great panamaneian food but also showcased local dances and beautiful artisinal dresses.

panamaHe took us out to the Panama Canal and Casa Antigua the next day but the real highlight of Panama was a hike through a tropical rainforest with Heli on the day of our departure. The first thing that struck us was that in the shade of trees and foliage it was even hotter and more humid than outside the forest and it immediately hit us that we would have to take another shower before the flight. But the hike offered stunning views, weird smells, noise from countless cicades and a sloth. And no, I am not referring to Heli and it was also very anticlimactig since all we really saw was a bundle of fur that was not moving.

OTN LA tour 2015: El Salvador

If you trust all the travel warning, advisories (and extra Oracle approvals) that we received before going to El Salvador then you could think that just making it back out of the country alive was a huge success by itself. We were careful to not walk around alone outside too much but other than that always felt taken care of very well and very secure. The rather long drive from the airport allowed a brief glimpse into this beautiful place with endless green rain forests climbing up and down black volcanos under a clear blue sky. Heli and I went for a quick swim in the hotel pool and some local food before calling it an early night.

IMG_5261The event took place in a private club with a pool, they had four tracks with a mix of local and international speakers but no translators. I sat in Deiby’s session on undo management and how to avoid ORA-01555 errors. It was in spanish but was able to follow most of the talk anyway. Yay for my spanish. And yay for Deiby’s easy explanations. I delivered three talks myself that day and was very happy with how they went. Since there were no translators available and the audience was not 100% confident in english I made sure to explain things as simple and slow and with as little words as possible. Made me realize about how many things I usually say are just fillers around the core of the content. I also attended Alex session on Orion which I had previously omissed because I would rather use SLOB for IO benchmarks. But among other things I was reminded that orion has a very easy way of running a whole series of tests and plotting them is also easy as the output are easy csv files.

OTN LA Tour 2015: Guatemala

Guatemala was one of the shorter stops on the OTN Latin America tour. We arrived on midday on Sunday, spent all day Monday at the conference and left again on tuesday morning. In all of the cities we visited they closed the main streets on Sundays so that pedestrians and cyclists could enjoy the city without the risk of getting run over by cars. I used this opportunity for a run through the city and was rewarded with nice views and busy streets with people working out, walking their dogs or simply enjoying the street vendors for fruit, burritos and other local fare.
Deiby Gomez and others from the user group took us out to a local restaurant where we also met Dan and enjoyed very traditional local food. Heli and I both had a fish dish consisting of shrimp tamales, a whole fried fish and a gigantic fish soup with more shrimps, mussels, crab and snails. Yummy but way too much to finish.

otnlatour_guatemalaThe conference on the next day was huge. The user group had to cap the registration at 500 attendees because they already hit capacity which could be seen on packed rooms and felt even more at the very long lunch line. They even provided translators so that even people with only basic english skills could follow the presentations of the international speakers. I loved it and made sure not to speak too quickly. And judging by the relaxed smile of my translator I succeeded in doing so. Tons of pictures were taken after the closing session, a part I always enjoy. It is all fun as long as no selfie sticks are involved.

Organizers and speakers shared dinner at a driving range where we could show off our golf skills or rather the lack thereof. My arms still hurt a few days after the event but it was a lot of fun nonetheless. Deiby opened a celebratory bottle of local Zacapa rum (to which I would never say no) and thanked the volunteers and speakers for their efforts in helping this usergroup grow to this success so quickly.

OTN LA Tour 2015: Mexico City

After a very quick and efficient immigration to Mexico I was greeted by Arturo Viveros from the ORAMEX user group who provided much more than an efficient ride to the hotel through yet even more traffic but also a warm welcome and a nice chat. Heli and Bruno would not arrive until later that day but Anton Els was already there and I was more than happy to see him again for a perfect dinner of seafood tacos in the center of Mexico City.

pedro_otn_mexicoThe conference itself was at a technical university a good 42 minutes away from the city center. We quickly found the rooms that we were supposed to present in and made our way to the general registration, exhibition and keynote area. And again, I was surprised by the very long line and the huge number of attendees. In fact, the organizers had to close the registration ahead of time because they were already at capacity. Wow. We made it to the keynote hall just in time to see my good friend and president of the Mexican OUG address the audience before Pedro Ciccarello of OTN Latin America introduced the OTN community and the ACE program. The event was not just attended beyond what I could have imagined, it was also very well organized. There was a seperate quiet area for speakers, good catering and most of all lovely room ambassadors that coordinated the timings and were there for all of our needs. A job very well done! My sessions had a decent audience, I received a number of questions and was just answering some follow-up emails before writing this blog. The day finished with dinner and laughs in an old part of the town that was bustling with life around a central plaza.
Continue reading

OTN LA Tour 2015: San Jose, Costa Rica – pura vida!

As I am currently sitting on a plane flying over lush rainforests and impressive volcanoes between Guatemala and El Salvador I realize that this almost marks the halfway point of the nothern leg of this year’s OTN tour of Latin America and I yet have to write a review about it.

San Jose Costa RicaAfter a long layover in Newark between two even longer flight legs I arrived in San Jose, Costa Rica, two days before the conference there. A small angel and diablo inside my head were fighting out wether I should follow Edward to the rain forest of the Arenal volcano or if I should just take it easy, adjust to the location and time zone and put some work in. A decision I am still regretting as I would have loved to get out of the capital city to experience the great gifts of nature that Costa Rica has to offer. The time flew by and I was excited to reconnect with my old friends Edward (and his lovely fiance Dawn) and Alex at the conference hotel the night before the event. I also met Bruno Borges from Brazil and we all shared guaro drinks, stories and a traditional tico meal. Pura Vida!
Continue reading

damn those defaults!

Most defaults are awesome and one of my consulting mantras is to stick with defaults as much as possible and find and verify good reasons when you want to deviate. The oracle database is pretty amazing out of the box and does most things right. This post however is about two defaults that I find to be very dangerous. Why? Because a DBA might just click through a wizard and select an option that has a lot of negative consequences and cannot be easily fixed. Continue reading

a case study of pagination, first_rows and hibernate

I want to share a bit of what I had been working on this week. During a quick check of one of our databases I came across a SQL in EM12c’s top activity section that drew my attention. And upon closer inspection I found more than this one that looked alike and were also pretty high up on the top SQL list. All of them did these pagination type queries from a java application using hibernate as an OR mapper to generate their SQL. Continue reading

12c shows patch information in alert log

I just noticed that on a pretty recent database installation ( it shows information on applied patches in the alert log during instance startup. Usually one had to run opatch to get this kind of information and I think it is neat to have this in the alert log aswell now.

Dumping current patch information
Patch Id: 19769480
Patch Description: Database Patch Set Update : (19769480)
Patch Apply Time: 2015-05-14 10:15:32 GMT+02:00
Bugs Fixed: 14643995,16359751,16870214,17835294,18250893,18288842,18354830,

Patch Id: 20299023
Patch Description: Database Patch Set Update : (20299023)
Patch Apply Time: 2015-05-14 10:15:40 GMT+02:00
Bugs Fixed: 16619249,17274537,18202441,18306996,18417036,18607546,18681056,

Patch Id: 20415564
Patch Description: Database PSU, Oracle JavaVM Component (Apr2015)
Patch Apply Time: 2015-05-14 10:17:58 GMT+02:00
Bugs Fixed: 19153980,19223010,19231857,19245191,19699946,19855285,19877336,

I do not know when this started but I have not seen this in installations.

OTN EMEA tour 2015

I am thrilled to be able to go on another OTN tour this year. As a successor to the 2014 MENA tour to Tunisia, Saudi Arabia and Dubai we are organizing a true EMEA tour that visits all three continents of the region. Together with my fellow ACE Directors Heli, Debra and Tariq we will visit Lisbon, Amman and Johannesburg in a week filled with presentations, networking and a lot of travel. You can read more about the tour at

Unfortunately, the tour will “only” be three stops this year but we are already talking to more local sponsors about adding more new locations to the next tour. And if you are interested in hosting an event as part of an EMEA OTN tour in your country, please let me know.

find database upgrade dates in AWR

Just a really tiny quick tip. Today I was wondering when exactly we performed upgrades on a particular database. Luckily, there is a view in AWR for that.

SQL> select version, min(startup_time) from dba_hist_database_instance group by version order by 1 desc;

----------------- --------------------------        09-APR-14 AM        16-JAN-13 PM        09-MAY-11 PM        22-JAN-10 PM

debugging puppet and facter with strace

I worked on a slightly annoying problem yesterday and thought I’d share the process with you. On one of our linux boxes puppet stopped working with an error that was not very helpful:

[root@portrix]# puppet agent --test
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Loading facts
Error: Could not retrieve local facts: private method `split' called for nil:NilClass
Error: Failed to apply catalog: Could not retrieve local facts: private method `split' called for nil:NilClass

All this told me was that something was wrong with facter during the puppet run. I was able to reproduce the error with just facter but even the debug output was not much more helpful:
Continue reading