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 (12.1.0.2) 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 : 12.1.0.2.2 (19769480)
Patch Apply Time: 2015-05-14 10:15:32 GMT+02:00
Bugs Fixed: 14643995,16359751,16870214,17835294,18250893,18288842,18354830,
18436647,18456643,18610915,18618122,18674024,18674047,18791688,18845653,
18849537,18885870,18921743,18948177,18952989,18964939,18964978,18967382,
18988834,18990693,19001359,19001390,19016730,19018206,19022470,19024808,
19028800,19044962,19048007,19050649,19052488,19054077,19058490,19065556,
19067244,19068610,19068970,19074147,19075256,19076343,19077215,19124589,
19134173,19143550,19149990,19154375,19155797,19157754,19174430,19174521,
19174942,19176223,19176326,19178851,19180770,19185876,19189317,19189525,
19195895,19197175,19248799,19279273,19280225,19289642,19303936,19304354,
19309466,19329654,19371175,19382851,19390567,19409212,19430401,19434529,
19439759,19440586,19468347,19501299,19518079,19520602,19532017,19561643,
19577410,19597439,19676905,19706965,19708632,19723336,19769480,20074391,
20284155

Patch Id: 20299023
Patch Description: Database Patch Set Update : 12.1.0.2.3 (20299023)
Patch Apply Time: 2015-05-14 10:15:40 GMT+02:00
Bugs Fixed: 16619249,17274537,18202441,18306996,18417036,18607546,18681056,
18856999,18909599,18940497,19012119,19018447,19023822,19035573,19065677,
19081128,19183343,19238590,19272708,19291380,19315691,19335438,19358317,
19385656,19393542,19487147,19512341,19524384,19536415,19547370,19597583,
19606174,19619732,19627012,19637186,19644859,19649152,19658708,19670108,
19684504,19687159,19730508,19791377,19805359,19841800,19865345,19873610,
19896336,19915271,19928926,19978542,20235511,20347562,20348653,20425790,
20440930

Patch Id: 20415564
Patch Description: Database PSU 12.1.0.2.3, 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,
19895326,19895362,19909862,20408829,20408866,20415564
===========================================================

I do not know when this started but I have not seen this in 12.1.0.1 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 otnemea.com.

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;

VERSION           MIN(STARTUP_TIME)
----------------- --------------------------
11.2.0.4.0        09-APR-14 07.40.14.000 AM
11.2.0.3.0        16-JAN-13 04.08.53.000 PM
11.2.0.2.0        09-MAY-11 03.35.26.000 PM
10.2.0.4.0        22-JAN-10 01.39.35.000 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

DOAG 2014 review

I had merely 10 hours at home between the end of the 2014 OTN APAC tour and my travel to Nuremberg for this year’s DOAG conference which I picked over the last two OTNAPAC stops in Bangkok and Auckland even though I am sure the weather there would have been much better than what Nuremberg had to offer.

the pragmas at DOAG2014But the DOAG conference is always worth a visit and has constantly been improving over the last few years. It has seen a huge increase in foreign speakers that elavated the level of the content to one of the best conferences worldwide. And it also seems to me that my fellow germans have warmed up much more to the general idea of networking and easy chatting.

Unfortunately most of my conference experience this year was dedicated to “slide polishing” in a quiet corner. They accepted two brand new talks and both slidedecks were in need of some extra last-minute work. In my first presentation I gave an introduction to puppet config management for Linux and Solaris and a bit of a taste of how to enable devops for Oracle databases and managing resources within DBs with a puppet module developed by Bert Hajee. The talk went very well and I was surprised by the rather large audience for a systems topic. Yay!
Continue reading

OTN APAC tour stop 4 – Beijing, China

Beijing Olympic Green fenced off for APECAfter arriving and resting for all afternoon, I headed out to the one sight that I was keen on seeing in Beijing. Our hotel was part of the infrastructure built for the 2008 Olympics and the birds nest and aquatic centre were just one block away. So I had planned to go there and also swim some laps in the same pool where Michael Phelps set a world record. But that was not going to happen. I learned that the whole olympic green area was still blocked after the APEC summit which concluded just a few days before our arrival. And it remainded closed during our whole time in Beijing. The upside to this was that we were treated with relatively little air pollution as according to this piece by Reuters the Chinese shut down lots of factories before and during the APEC summit and even stopped giving out marriage licenses in order to present itself in the best light. Continue reading

OTN APAC tour stop 3 – Tokyo, Japan

I woke up on Tuesday Morning with a bit of shock on confusion. Where was I? Did I oversleep? What day is it? Do I have to be at the airport and fly somewhere again? When you travel to a new destination every two to three days with jetlag, red-eye flights and all sorts of new impressions to digest, things start to get blurry. Tim and I had arrived the night before from Shanghai and even though the flight itself was just a quick 2,5 hours jump over the Sea of Japan, we were kept busy with queueing and rides in taxis and trains.

Shinagawa stationAfter a shower and some toying around with the japanese high-tech toilet (there were LEDs and buttons to explore) I got on my way to the conference location using the public transit system that I had researched way ahead of time. The google maps app has been a great help with this, too. When searching for public transit connections, it not only shows the line you need to take but also the ticket price and the number and names of the stations in between. Back in Singapore I was already impressed by the ability of the app to display information on the different floors in an indoor shopping mall by floor.
Continue reading

OTN APAC tour stop 2 – Shanghai, China

Contrary to what my family, friends and coworkers believe, not every aspect of these OTN tours is glamour or relaxation, but there is actually quite a lot of dull travelling involved (with the time used to compose blog posts like this one). The travel to Shanghai was such a day. After a last dinner in Perth, Tim and I hitched a ride with Connor to the airport, then spent the first part of the night there, waiting for our plane to depart shortly after 2am. I managed to sleep a fair bit of that first leg to Singapore where the little transition time that we had was a great reminder of the wonderful time I spent there just the prior weekend. This was one of those times were I would not have minded a cancelled flight and an extra day there. But our flight to Shanghai was right on time and another 4 to 5 hours long.
We took the maglev train from the airport which was quite an experience because a) you do not get to ride 430km/h every day and b) it was good to see what was done with my german tax money that went into the research of this technology. I felt adventurous so instead of taking a taxi for the final bit to the hotel we took the metro after a bit of a fight with the ticket machine. First of all there was only one machine that accepted bills so it had a bit of a queue. And when it was our turn we could not buy the tickets we needed because the english screen did not allow us to select the right line (only a subset). But after a bit of pointing the guy behind us helped out.

OTN APAC Shanghai posterLater I enjoyed a wonderful dinner in a restaurant that was also partly a museum, met two of the other speakers. Maclean Liu from the UG and also the sponsororing partner in Shanghai, Joe Huang who has been working for Oracle at HQ as a product manager for years but was raised in Taiwan, so he knew mandarin. And then also our liasion and local coordinator Bo Feng who did an excellent job at looking after the international speakers and introduced us to a lot of new and strange food items.

Again, I felt adventurous and walked from my hotel to the conference site which was about 40 minutes. And I was pretty much blown away by stepping into a completely different world. I passed a street packed with little hardware shops selling all kinds of screws, nuts and bolts and other building materials, then some very local and raw parts of town next to the river. It really felt like diving into a completely different world.

The conference started with updates by 刘冰冰, a lady from Oracle, and even though all I could understand were a couple of keywords (CDB, PDB, big data SQL, cloud) I am pretty sure I have heard a very similar presentation in English a few times already. The audience understood english well enough but the organizers also translated the slides of Tim and Giusseppe Maxia into chinese so when they were talking they sometimes had to guess at what each bullet point said because there was not much english text left. Tim talked about how to avoid the most commin performance problems in PL/SQL and it was an excellent presentation with good points and a lot of small demos.
Giuseppe talked about mysql replication, how to set it up and also how to monitoring and some advanced cases, I am already looking forward to seeing him again at the Beijing conference in a few days.

OTN APAC Shanghai raffle winnersI did my talk on RAC connectivity which I have done many times now. I am sometimes getting a bit tired of my older presentations but still like this one a lot and think I should maybe turn this more into a general talk about RAC rather than just talk about load balancing and services.

Since the rest of the sessions were going to be in chinese we snuck out and walked around a bit. One of the more bizarre things we saw was a shopping mall with 3 or 4 floors of nothing but mobile phones and accessories. Even some repair shops were people were taking phones apart. We wondered how many of the phones we saw were counterfeits and how many were originals.

OTN APAC Shanghai selfieWe made it back to for the end of the conference and enjoyed another great dinner with the guys from the Shanghai User Group and speakers. The oddest food was fermented stinky tofu. Think of blue cheese. Tim made the mistake of taking a sniff first, then eating, I just avoided breathing through my nose. The taste was actually pretty good.

I decided to go for a brief walk over to the Bund and take some pictures of the Pudong skyline. It was a bit over the top with the colourful blinking lights and screens and couples in cheap tuxedos and polyester dresses having their pictures taken. The constant presence of hawkers advertising cheap “massages” was very annoying and I dared to walk about in some of the sidestreets and was rewarded with scenes of street food carts, interesting smells (both good and bad) and just generally loads of bustling life happening in the streets.

OTN APAC tour 2014 stop 1 – Perth, Australia

I arrived in Singapore a day before the conference and used this bit of quiet time to catch up on work stuff rather than do sightseeing. We had a nice ACE dinner downtown even though Tim did not make it in time. So it was Craig from the US and the local heroes (really, a very high density of excellent Oracle presenters and ACEs) Penny, Gavin, Connor and Scott. To me, one of the biggest benefites of the ACE program is getting to meet all these birght people and being able to call a lot of them great friends.

Connor took us to the beach for a quick swim on both days since – again – Tim did not make it on the first morning even though he said he was up and ready. The swell as a tad gnarly with pretty big waves on Friday. It was good fun and a refreshing way to start both days.

For me and the other speakers of the tour this was propably the hardest work at any one of the conferences since we all did two 2-hour ACE masterclasses each. But we started with an OpenWorld recap which had 7 of us talk about one aspect of OOW for 5 minutes each. This was inspired by the 12 on 12c presentation that EOUC did in San Francisco. This was good fun and I explained how you can have an excellent time and get good value out of the conference even without going to a single session. Others shared their own perspective on what to do or not and gave a summary of the hot topics at the conference.

All of my sessions went great and were very well attended. They made me feel like a real VIP during lunch when I got my own (vegetarian) meal with a big sign with my name. Since this was a local event for most attendees there was only a small networking thing with a few tame beers after the sessions and everybody went home at a decent time so after a quick dinner from the local convenience store this was another chance to get some decent rest.

Tim Hall handstandThe second day had the same strong content-rich agenda as the first day, mayben even more. Connor did a talk about 12c new features for developers. Initially we were afraid that this would clash with my presentation the day before (which was also 12c new features) but they complemented each other very well. He mentioned a lot of things that I did not even think about and also a lot of stuff around PL/SQL that I just barely ever get to play with. Penny Cookson did a brilliant session on how the optimizer works by comparing it to a girl trying to find a partner and working out the best plan all the while doing estimates based on statistics (as in how many single guys in the right age and height would be at any of Perth’s bars at a given time). It was hilarious and a great learning experience aswell. It is pretty scary to realize that an analogy like this works so well. I was still so impressed that I tried to work the analogy into my own presentation on baselines that I was doing after lunch. But it did not feel right to compare SPM to evaluating the quality of a new partner by trying out both the old and the new one. Or maybe that is an excellent analogy.

There were a few drinks and a dinner after the conference after which Tim and I went straight to the airport to catch the first of two flights to Shanghai at 2am – no point in going back to the hotel.

OTN APAC tour 2014 stop 0 – Singapore

#SOS - Singapore Oracle SessionsWait? Stop 0? How does that work? One reason may be that there is a lack of a universal understanding of what to call the first element of a finite set. Like floors in a building. Things you learn on tours like these are that 1 is not actually the ground floor in all countries (and it was the reason why I exited the lift on the wrong one). But the real reason that Singapore was stop 0 for me is that it is not actually part of the OTN tour (but in my opinion should be next year). I had planned a quick 3 day stopover and stay with Doug Burns and he decided this was a good time to start a local oracle thing in Singapore. So with less than a week notice he booked a room, organized food and drinks and managed to get over 30 people to show up (Hemant Chitale helped spreading the news) for the first installment of #SOS Singapore Oracle Sessions. Excellent! Portrix, Oracle and Doug chipped in as sponsors for the few expenses we had.

The speakers were blown away by the enthusiasm and passion of the attendees, received many great questions and chatted in the breaks. There was also great networking between people and everybody seemed to at least remotely know everybody else or made introductions. It was very clear that there was a demand for something like this format and while people were unsure if they needed a “proper” user group it was evident that everybody would like to see this continue.

Singapore Oracle Sessions Bjoern Rost on SQL Plan Management and baselinesI started by talking about SQL Plan Management. I have done this talk a few times now and am really happy to see how I am making progress with it. I was happy to make the most important points very clear, answered a few great questions and still finished ahead of time because Doug was signalling from the back of the room that the pizza was getting cold.

Doug talked about SQL Monitor (one of the most useful performance tools in Oracle) and how to use it from the commandline. What I did not know was that you could create just the same flash based report that the full-blown Cloud Control is generating and even more. Some of the tabs are grayed out in certain version of EM12c but they work in the CLI-generated one.

Morton did a Big Data primer, introducing people to what big data is and what it is not, giving funny but concise quotes from the Oak Table mailing list, giving an introduction to Hadoop and what the hadoop connector in Oracle can do for you (mostly access files on HDFS so you can either export big tables straight to hdfs for mapreduce processing and/or to import the results of a map/reduce job which will be written to HDFS). He finished with some funny correlation-type analysis which actually helped a customer identifying how to better do their job and got them to invest into big data infrastructure.

singapore supertrees and MBS from satay by the bay bathroomThe only bad thing I can say about the Sessions was that it cost me a few hours of precious sight-seeing time in Singapore. People have told me to come for a while but I never thought it would be as exciting, interesting and cool as it actually was. I wonder why we “dream” about some destinations (some of which turn out to not even be that great once you are there) and turn down the idea of visiting some others. Lesson learned: keep an open mind and good things will happen to you. To everybody else: Singapore is well worth a visit, with or withour Oracle Sessions.

The picture on the left was taken from the bathroom of a hawker centre (street food court). There was no wall behind the sinks, only the awesome view of tropical plants, supertrees and the iconic Marina Bay Sands hotel.

As I am writing this I am already in Perth for the annual Western Australia Oracle UG conference enjoying a rare moment of peace and quiet before the conference tomorrow. I have already experienced a new world and images of tropical plants, buildings old and new and exotic smells (both good and bad) fill my mind but this is merely the start of a tour that lasts two more weeks.

PS1: #SOS is a really bad hashtag
PS2: B-Tree indexes would be awesome if they were called Supertree-Indexes