Category Archives: Uncategorized

PostgresBuild 2021 Session Highlights: Dr. Michael Stonebraker on Maximizing Database Application Performance


Among everything we do and talk about in our space, one thing is crystal clear—all of the technology and methodologies that exist serve one purpose: to support distinct businesses. Industries include e-commerce,  banking, insurance, manufacturing, telecommunications, and everything in between. This leads us to my main focus points in this article.

Building for business application success

One of the pinnacles of the IT industry is to create applications—applications for business workers, end users, and other applications. Apart from the obvious requirements that the application has to function correctly, two major challenges reign in the space:

  1. The speed required to add new features
  2. The speed at which applications work

These are age-old application development challenges that Dr. Michael Stonebraker addressed, much to my enthusiasm, in his closing keynote at  PostgresBuild 2021. Let us explore this in a little more detail.

Dr. Stonebraker remarked that “[a] cursor interface [to your database] is insanely expensive.” Using a database like Postgres as a storage mechanism to simply store and retrieve data makes no sense. You will get no benefit from all the intelligence that has been programmed in Postgres to help optimize application performance and you will add “insanely expensive” overhead to your application.

There have been multiple attempts to find solutions for this so-called “impedance mismatch,” but none have taken flight thus far. My colleague Hettie Dombrovskaya wrote some interesting papers on this. Let’s also dive in a bit, here.

Applications

For many years, applications have been separated from the databases they use to store, retrieve, and manipulate data. We have grown accustomed to having applications written in specialized platforms and languages to facilitate the need for speed (as mentioned above) and maximize user experience.

Databases

Databases are a logical and practically unstoppable part of applications. Relational databases will fulfill a continuously increasing role in data management. A relational database engine like Postgres is an extremely powerful and versatile platform that gives virtually unlimited possibilities to not only store and retrieve data but also manipulate data—very much so.

Business logic

With the split of where applications live and where data is stored and processed, a development started that was focussed on a further decoupling of these two parts of an application. When writing this, going into all of the individual elements is not part of the objectives at hand.

A 3-tier model—frontend, middle tier, backend—was developed that would lay the foundation for future models of application development. We will also disregard the front end here, as this is the realm of browsers and high-end user experiences. While that is certainly important, it is less relevant or interesting for database folks. Well, up to a point, but that will have to wait for another article.

The way applications are built focuses on user functionality, which typically gets organized into objects or classes.
The way databases operate focuses on data transformation, which typically is organized in rows and columns.

The problem, addressed by both Stonebaker and Dombrovskaya, is that the transformation from rows to objects takes place where the application lives. This has three important implications that cause sorrow and much toil.

  • An incredible amount of traffic between the two most costly elements in the equation: between the database server and the application server, and for every data element (row) a message is sent
  • Processing of data takes place at a point in the chain where information handling is the objective, creating a responsibility mashup. The application layer is responsible for interaction with information rather than transforming data into information.
  • By disregarding database functionality for integrity and transformation, you deny yourself the optimal security measures of having consistent, reliable, and idempotent data processing.

Making it usable

So where does this leave us?

A lot has been written and researched about this topic. Research has been referenced early on in this article and many other approaches have been considered. This includes research on “SmartDB” from Oracle’s Toon Koppelaars, where he empirically proved that the process of doing the actual data transformation in the middle layer of a modern application sets you up for imminent scalability issues.

Business logic in the database

I believe that the reason for this is the rock-solid conviction that “business logic should never be in the database.” This has been a teaching since the mid-nineties of the previous millennium and I think we have arrived at a time where that needs to be re-evaluated.

In the 25-odd years that have passed, much has changed. One of the reasons for this stance was the concept of “database agnostic applications,” which we all know today is impractical and relatively senseless. Senseless due to the development in data management systems such as Postgres, delivering unparalleled speed and functionality to this point.

Logical split

Additionally, the understanding has emerged that the concept of business logic is two-fold:

  1. There is application business logic, which handles how information inside an application is managed and which business requirements the application needs to fulfill.
  2. The remaining part of business logic is the data business logic, which deals with how data is transformed into information and defines which actions are required to maintain consistency inside the database.

Again, there is a lot more to discuss around the split in logic than we have room for here.

If you were to do this, and that is nothing more than a simple architectural decision when designing any application, you can find a wealth of opportunity.

Reflecting on the three challenges we faced when revising our original business logic stance:

  1. Excessive traffic
  2. Mashup of responsibilities
  3. Data security risks

Sparking further discussion

I am fully aware that a topic as extensive as this, one that is potentially controversial and challenging, needs more discussion and thought than a blog post can cover. There have been countless studies and debates, yet as an industry, we still trip over this.

My goal is to fan the flame of this discussion in 2022, which was sparked by Dr. Stonebraker in his keynote at PostgresBuild.

The key to applications is performance; working with a technology like the Postgres database gives you many of the tools to achieve this. Use the force wisely.


A new North Star has risen

While this post is not going to be about pulsars, black holes or any other form of astrological phenomenon…It still is going to cover one of the most exciting and fundamental shifts in the IT industry today.

A tale of two convergences

This tale is discussing convergence.
To understand the significance of any convergence, it is necessary to understand the lines that are coming together. Please indulge me when I review these.
The challenging bit is the fact that inside both of the lines there are only the very early glimpses of this new era. They are still very much busy with the day-to-day operations in open source or conquering more of their base realm.

Why Postgres is the answer

I migrated myself from Oracle to Postgres! Moving from a steady path as an Oracle ACE to this—at least for me at that time—brand new world of open source data management. If you want to know how and what, I wrote a trilogy about that here, here and here.
After working in the Oracle realm for almost a quarter of a century, moving from Oracle to Postgres truly felt like following a new North Star. It has proven to provide good guidance.
With the phrase still in my head—”horses for courses”—the PostgreSQL Global Development Group does focus exclusively on Postgres. Coming from Oracle, though, it made me wonder who focuses on all the other aspects that vendors create to make their systems work. This “emergence from the red bubble” made me realize that there are much broader challenges, which leads to the “second thread” for convergence, in this story.

I like to think this puts me in a position that allows me to oversee this part of the spectrum (databases and data management) to a certain degree.

Note: if you realize where the data management industry is moving, Postgres is the answer.
Hold that thought!

Brain-breaking challenges

Over the years, much focus has been on infrastructure. Simply because it is expensive, tedious, error-prone and has lots and lots of room for improvement.

  • Infrastructure as code – your server is not your pet, you do not tend to it, you replace it.
  • Cloud infrastructure – your server?? It is not your server… you just use it, you are server-less.
  • Many more of these developments have shaken the world since I started in IT.

There are a number of drivers that we can distinguish and that play a part here:

  • Cost needs to go down!! The ever emerging CIO challenge is: do more with less. And, we are succeeding in that, year after year…
  • Speed needs to go up!! We need more features for our applications, we need them sooner, we need them working more flawlessly.

Working in IT operations during various points in time, these elements have always given me the hardest brain-breaking challenges.

The winner is…Postgres

It is really, and undeniably so (or at least it is so in my book), Postgres has won, and where it is still competing, it will win <full stop>

Why?

There are several reasons why I think—why I know—this is going to happen.

  1. Publicly governed open source, community driven open source, give it a name. It is not a for-profit thing that is behind the technology…Hence, there are no barriers or boundaries to opportunities and direction.
  2. Relational will never die. Dr. Michael Stonebraker said it himself, there is going to be no post-relational era, and the more data management / processing methodologies we get, the more we will need SQL to make sense of things.
  3. Metcalfe and Reed’s laws. More ideas, more contributors, more firepower to Postgres. Postgres’ strong and unwavering foundation grows and evolves. The foundations laid by the PostgreSQL core team will continue to feed the fire of Postgres for the foreseeable future.
  4. Datawarehouse, Datalakehouse, Graph, NoSQL, NewSQL, distributed, and what other mumbo-magic words you can put together. We’ve seen it in the past and we will see it again…it will all converge back to Postgres. It’s inevitable. The latest  evidence: Apache AGE. I rest my case.

Stop for a minute… just a brief pause to think about some of the implications of this…Put prejudice aside, and just consider this: the Power of Postgres, the most transformative tech since Linux.

Kubernetes (K8s)

Enter Kubernetes! If you have not yet done so, I strongly recommend watching the two-part documentary on the origins of K8s here and here.

Once upon a time, at a conference somewhere in Eastern Europe, a keynote speaker was talking about IT operations. At some point in the story, he told us that he was on the phone with his CEO after a failed deployment. He said; “Look, Sir, the application worked on my computer!” and his CEO replied: “Well, that’s all good, except that I am not paying you to make it work on your computer, but I pay you to make it work on my computer”.
In my opinion, this is one of the key elements that container infrastructure solves. Immutable infrastructure, as part of running Postgres, the Kubernetes way. A critical new way of looking at integrating Postgres as a core element for data processing in a true cloud native manner.

Apart from doing infrastructure very well, K8s turns the world of application development on its head. Monolithic Applications become Microservices based solutions, the paradigm that allows for things such as Continuous Integration / Continuous Delivery, and many more of the super-cool things that DORA describes.

Oh, and did I mention that Kubernetes is also a publicly governed open source community driven project?! Check out what the CNCF is all about! I won’t go on about Seven of Nine this time, I promise.

Data on Kubernetes

So basically, there you have it!

We have seen AMAZING things from Postgres!
But they never really focussed on deployment and alike; “horses for courses”.

We have seen AMAZING things from Kubernetes!
But they never really focussed on data and alike because developers need to build features!

Under the awe-inspiring guidance of the CNCF, we have an actual Data on Kubernetes Community!
A first, profound and fundamental step on the path of convergence, where Postgres meets Kubernetes and we start enabling a new era that might start bringing a couple of answers to some age-old (well, as far back as the invention of computers, really) and some newer challenges such as:

From here it is basically: “Hi-ho, Silver! Away!”
There is no stopping this, or as a colleague once paraphrased Babylon 5: “The avalanche has already started. It is too late for the pebbles to vote.”

This is the new North Star.

In the end, we’re just getting started

  1. Oracle to Postgres—well, that’s done. What has not yet been migrated will probably die out at some point.
  2. Postgres is established, no debate there.
  3. Kubernetes is so strong, so appealing, it answers so many questions that it will be with us for quite some time.

There is, though, this one fundamental gap. However you twist or turn it, the user of your app needs data, otherwise what’s the use of all of your mega cool features?

Postgres and Kubernetes, the two most powerful technologies of today answer that question.

2021 F1 World Championship thoughts

It was Marco (https://twitter.com/Irosmarco) who prompted me on Twitter.

Question to you Jan
Formula 1 fan to another Formula 1 fan.
Not Hamilton or Verstappen. But as a Formula 1 fan.

Do you think last race was a fair race?
Do you think Verstappen deserved to win the last race?
Did Masi make a correct decision?

Don’t be biased but be fair.

(https://twitter.com/Irosmarco/status/1475565657441906690)

And that question intrigued me to such an extent that I though; “This warrants a real answer and not just something that is limited by Twitter’s character count”. So, Marco, here goes nothing…

There are so many angles to this question that I will limit myself to a couple of them.

Loose / loose

Ultimately, for Masi, it was a loose / loose decision.
What I mean by that is he has now lost out for everyone who roots for Hamilton, obviously, or I would not even be writing this.
If he had done nothing, and the race had finished behind the safety car, he would also loose. There would be not a living Max Verstappen fan who would not have hated his guts. Christian Horner cried out; “we only need one lap to race.” Everybody would have said that the race was “given” to Lewis, as that would be the case in that situation, as Max would have had no chance to overtake him.

That leaves the bit that the cars that were behind Max were not allowed to overtake them both. Well, basically this would effectively also lead to a race that ends behind the safety car, as there was only a good lap left in the race.

Circumstances

Rubber

If we step back just a little bit, we have the issue of strategies. If we would put ourselves in the situation that both teams had somehow wounded up with the strategy that put both Lewis as well as Max on fresh red rubber… Would Max then even have had the opportunity to overtake? Seeing how the rest of the race was, probably not, but finally, they were not both on fresh red rubber. Is this something that should have influenced the decision? If we apply this rubber-fact either way? It should not have and I am no mindreader.

The crash

In that same light, if Latifi would not have crashed, none of this would have happened. No safety car, no issues with cars being left in the queue and so on, and so on.

Everything together

If you look at this race, if you look at the outcome…
If you put this in perspective over the racing season…
If you look at “the purpose of Formula one racing”

Completely unbiassed, looking at Formula one, the best possible decisions were made.
Why?
Because they have brought about emotion and passion in so many people. For years and years F1 has been dominated by either a single driver or a single team. Up to a point where folks just gave up, as you could already predict the world champion after the first lap of the first race.
Additionally, car racing is not something you do with knife and fork. Dangerous? Hell, yes, it is dangerous. And here you can go off in a complete tantrum of it’s own around halo’s and what have you.

If I look at the whole picture, Michael Masi could not have done a better job. Even more people than ever before will be so excited to see the 2022 F1 World Championship battle taking place, and, as far as I am concerned (which essentially is about “zero”) is what this is all about.

Marco, I hope this is an answer to your call. I do not really expect you to agree with me, but that is okay. As Morgan Freeman said: “We might not agree, but that doesn’t mean we cannot be friends.” Well, okay, he did not say that, but it is my free interpretation of it…

Getting traction through action!

Weird title, right! Very slick and marketing talk…
It is also a deviation of the usual topics I discuss here, but at least (!)as important.

But it is true. And this is what we truly aim to do at JK-Consult. Not just professionally, but in everything we stand for.
Of course, as long as this is just words, what does it really mean? Not so much…

Therefor, starting in 2018, we are a sponsor for Senna Rodijk, up and coming Dutch karting-talent. Senna has chosen the bold path to be part of Formula 1 and will make this happen through her focus on karting with Chrono Karting.
Starting this year in a new class, the minimax karts, we have seen her racing today in the first race of the new season!

Go Senna!

For the complete story, as she has some unique challenges, check out her website.
Not only is she trying to establish herself as a champion in a field with only guys. In a time that everybody wants to try their hand at karting, to be the second Max Verstappen! This alone is a challenge in it’s self…
Senna’s major sponsor and coach, her bonus-father, Carlo Hoevenaars, is diagnosed with terminal cancer and his treatments have been recently ended… this is not pity full -as they don’t desire pity-, it is an incredible challenge!

Deep, deep respect for Carlo and deep respect for Senna and Suzanne!!

They can  always do with any (additional) support they can get.
Click here for more details and some words from Senna herself.

We have put our actions where our believes are! You only get traction through action.
We stepped up and JK-Consult is the proud sponsor for Senna’s racing tires for 2018!

At the time of this writing we have finished the first race of 2018, with an amazing result!! Senna finished 5th and 6th in the two heats at the circuit Horensbergdam in Genk!


Who will follow and help us let Senna take the 2018 championship for Carlo!!

#DOAG2016, definitely a crazy week.

#DOAG2016, the largest Oracle Community gathering in Europe. Taking place in Nuremberg, at the Nuremberg Convention Center NCC, one of the more impressive places to hold such a conference, towering 4 stories high, with a big central atrium!!
It is a huge effort to get all of this together!

In this blog-post I want to highlight some of the crazy things I experienced this week… And… I did try to follow my own schedule, but I wasn’t overly successful.

Young talent

One of the things that was somehow quite clear this week, is that we have a lot of young talent out there, eager to learn and share experiences. It is not just the #NextGen “movement” of DOAG, of which Carolin Hagemann made me aware, but just young people on the conference itself.

Discussing “Young PL/SQL” at the unconference session made us all aware that our part of the IT trade is no very sexy and popular with the youngsters. This all despite what was mentioned above. In universities we train SQL, but we don’t train to create real-life business applications, leveraging the power of the one language that keeps SQL close to the data it feasts on, PL/SQL. But, more on that below (Thick Database Paradigm).
To promote PL/SQL, basically two ground requirements were defined:

  1. Create a free ‘PDB as a Service’ for schools;
  2. Inspire teachers to talk about data centric computing

By finding somebody to be regionally or globally owner of this quest, it should be possible to get young professionals as familiar using PL/SQL for creating performant and business-ready applications as they were familiar using Microsoft Excel to do their accounting “back in the days”

ACE program

“There is a disturbance in the force!”

For everybody not acquainted with the Oracle ACE Program by the Oracle Technology Network… You should be!! Please read up, as it is an incredible cool initiative.

The disturbance, you ask?
Well, to retain your “status”, Oracle expects you to do “stuff” and this “stuff” is then evaluated on a yearly basis. Basically the initiative, the disturbance, is to get some transparency in “the stuff”. And, as always, everybody wants change, but few actually are good at “change”. There are rimples and things that change, but in the end; everything will be fine, unless, obviously, when it will not be fine.

Talks

I was honored to (co)host to talks at #DOAG2016:

Bad Boys of Replication – Changing everything…
With Oracle ACED and good friend Björn Rost, about an intense migration project we did some time ago. We were even offered to host our talk in Tokio, the biggest hall at DOAG!

Saving lives at sea at an industrial scale using Oracle Cloud Technology
An insightful (at least I like to think so) talk with my colleague Oliver Limberg. The talk is about the rapid development of a global portal for the maritime logistics branch.

I had a blast, and I hope you did too!

Community spirit

Oracle User Group conferences are about sharing and are about fun. Mr. Martin Widlake wrote a good post about that.

Apart from all the “more formal” things that happened, there were quite a few extracurricular activities, mostly involving an Irish Pub or a restaurant.

This all may sound quite funny and exciting, and, yes, it is alto talk with your co-workers: “Oh, hey, you are going to have fun and party all week!” Of course it is not a drag and a bore, but it has very profound function!
Whenever you run into trouble, these are the exact same people that are not only able, but probably also inclined to help you out, as you would help them out, as friends do among each other. In the end, they, you, your boss and your clients benefit. This is not to be underestimated too much.

The extra, special bit, that DOAG offers are the so called “unconference sessions”.
Not scheduled, no slides, nothing official, just getting together and discussing subjects of interest. Our “Young PL/SQL” was one of these “unconference session”, which turned out to be a great (and valuable) success!

Meeting people

Just to name a few, heroes of long and of yet to come for #DOAG2016:

Dietmar Neugebauer
Frank Dernoncourt
Joel Kallman
Johannes Ahrends
Kamil Stawiarski
Laurent Leturgez
Maja Veselica
Marcel Hofstetter
Piet de Visser
Sabine Heimsath
Stefan Kinnen
Stew Ashton
Uwe Hesse
Zoran Pavlovic
And alle the ones I forget to mention here!!

Thick Database Paradigm

Noting new in IT…

Well, no.

The Thick Database Paradigm (opposed to the “No PL/SQL Paradigm”) is nothing new. We have actually all been doing this since the eighties. Program your business rules, your constraints, everything that makes sure that your data is all that you want it to be, close to that data.
There are so many reasons that speak in favor of this approach that it is nearly overwhelming and deserves at least a book in itself. But, let me make a small attempt to highlighting a few here:

  • spare yourself network bandwidth, by not sending data all over your network to be processed
  • safeguard your data inside the (Oracle) database, so it can be protected by all that has been invented to do so
  • Transact data where it lives and combine and aggregate it there, you will be amazed by the efficiency
  • Remind yourself why you used to think “business logic in middle teer” was a good idea

If you leave possibile religious believes aside, there is no other conclusion possible then that the reinvention of “Thick Database” is the (re)discovery of 2016, right from the time when IT still made sense.

Yes, there are cases where an “Enterprise Service Bus” makes sense, but, as with every technology withing IT, it has a very specific area where it actually adds value or even makes sense. At best, a lot less than all the places where it is used currently!
Not to get carried away in this joyful blog-post, I will leave this topic at this.

The end

I hope to see you at the next Oracle User Group conference, somewhere… Please watch for the asterisk at his page for the conferences that I will attend.

#doag2016 my picks and suggestions

As many conferences evolve over the years, the number of sessions on offer can easily be overwhelming. I have overheard many conference attendees wrestling with their choices for what to see and which sessions to attend.

For DOAG 2016 I have a short overview with my picks and with one or two tip-sessions. I hope this helps, though it is just my personal preference of course…

Please note that this post is based on the printed version of the conference planner and this may obviously be subject to change. Find the on-line version of the conference planner here!

Tuesday, November 15th
08:30
Goto-session
Connecting Oracle & Hadoop by Tanel Poder
Tip
Structuring an APEX Application by Alex Nuyten
Meet your match: Advanced Row Pattern Matching by Stew Ashton

11:00
Goto-session
How to identify the Right Workload for Database In-Memory by Andy Rivenes
Tip
Die Schlechten ins Kröpfchen – SQL analyse für DBAs by Martin Klier

12:00
Goto-session
Was die IT von der Luftfahrt lernen kann by Uwe Küchler
Tip
Using image copies for Oracle Database Backup by Ilmar Kerm
Using SQL Transaction Framework to rewrite Bad SQL on the fly by Kerry Osborne

13:00
Goto-session
Plötzlich Multitennant – was ändert sich für den DBA by Uwe Hesse
Tip
Oracle VM auf Exadata – Erfahrungen aus der Praxis by Christian Pfundtner
Einsatz von Maps in APEX by Denis Kubicek

14:00
Goto-session (TOP-tip)
–> Session got cancelled, but will be at UKOUG!
Patch you application with No Downtime (& No extra Costs!) by Oren Nakdimon
Tip
Hacking Oracle’s memory – About Internals & Troubleshooting by Stefan Koehler

15:00
Goto-session
XML in der Oracle DB by Wolfgang Nast
Tip
PL/SQL Performance – Best practices für Laufzeitoptimierung by Jan Gorkow

16:00
Goto-session
The Oracle Optimizer – Upgrading Without Pain by Nigel Bayliss
Tip
Erfahrung nach einem Jahr Fusion Middleware 12c by Jan-Peter Timmerman

17:00
Goto-session
Active Session History: Advanced Analytics by David Kurtz
Tip
MySQL for Oracle DBAs by Philipp Michaly
Deploying PL/SQL Applications, Building Rome in a Day by Alan Arentsen

Wednesday, November 16th.
08:00
Goto-session
Logical Replication in 12cR2 – What are the options now? by Vit Špinka
Tip
Function madness: Use and Abuse of PL/SQL Functions by Piet de Visser

09:00
Goto-session
Ensuring your Physical Standby is Usable by Michael Abbey
Tip
RMAN – From Beginner to Advanced by Marcin Przepiorowski

10:00
Goto-session
Oracle Secure Backup – eine Livedemo by Sven-Olaf Hilmer
Tip
Oracle Hacking Session by Kamil Stawiarski
Advanced Interactive Grids by Patrick Wolf

11:00
Goto-session
The Battle: Linux vs. Windows by Dierk Lenz, Johannes Ahrends and Martin Klier
Tip
Adaptive Features or: How I Learned to Stop Worrying… by Ludovico Caldara
Controlling Execution Plans – Workshop by Kerry Osborne

12:00
Goto-session
Application Express für den DBA? Geht das? by Joel Kallman
Tip
Und Sie bewegt sich doch by Lothar Flatz
APEX Desktop Apps – Interaktion mit dem Client System by Daniel Hochleitner

13:00
Goto-session
Hash Joins and Bloom Filters by Toon Koppelaars
Tip
Ansible für Oracle DBAs by Alexander Hofstetter

14:00
Goto-session
Delivering Continuous Availability for Database Services by Michael Timpanaro-Perrotta
Tip
Dbvisit – Oder doch lieber Data Guard by Andreas Kother
Chase the Optimizer Every Step of the Way by Mauro Pagano

15:00
Goto-session
Top 7 Plan Stability Pitfalls & How to Avoid Them by Neil Chandler
Tip
Advanced RAC Programming Features by Martin Bach
Weblogic 101 for DBA by Osama Mustafa

16:00
Goto-session
Bad Boys of Replication – Changing Everything by Björn Rost and yours truly

17:00
Goto-session
Oracle System Statistics by Paul Matuszyk
Tip
Compression – Technik und sinnvolle Umsetzung by Klaus Reimers
Node.js der Alleskönner by Kai Donato

Thursday, November 17th.
08:00
Goto-session
FAQ about Masking Sensitive Data in Oracle Database by Maja Veselica
Tip
Data Guard in Oracle 12.2 – Crash Course by Zoran Pavlovic

09:00
Goto-session
Mining the AWR v2: Trend Analysis by Maris Elsins
Tip
Regular Expressions: Say What? by Alex Nuyten

10:00
Goto-session
Databases Clone Using ACFS by David Hueber
Tip
R.I.P. Oracle Database by Markus Lohn

12:00
Goto-session
Writing Efficient SQL Statements by Joze Senegacnik
Tip
Validate User Input in APEX by Richard Martens

13:00
Goto-session
Backup und Recovery PoC auf der Recovery Appliance by Frank Schneede
Tip
Ready, Steady, GIT: Einführung eines Versionskontrollsystems by Carolin Hagemann

14:00
Goto-session
Warum sollte man die Multitennant Database Option Verwenden by Johannes Ahrends
Tip
Collections in PL/SQL by Frank Haney

15:00
Goto-session
Saving Lives at Sea – At an Industrial Scale Using Oracle Cloud Technology by Oliver Limberg and yours truly
Tip
Part 1: The NoPL/SQL and Thick Database Paradigms by Toon Koppelaars and Bryn Llewellyn

16:00
Goto-session
Part 2: The NoPL/SQL and Thick Database Paradigms by Bryn Llewellyn and Toon Koppelaars

And!!
Do not forget…
The first ever APEX Hack’a’thlon is going down on Friday the 18th of November at the DOAG Education day. If you are interested or just want more information, don’t hesitate to drop a line.

@HrOUG_2015 in Rovinj, Croatia

In a hectic year it is good to attend and contribute to Oracle user group sessions. This adds an element of a ‘Working Holiday’ to someones schedule. I can promise you, the vacation isle of Rovinj is a perfect venue for this and especially since it is the last week of the opening of the Hotel for this season.
Of course you can find all information about contributing to these events right here!!

@HrOUG_2015, as the official twitter-account of the conference goes, brings just this!! Content combined with pleasure. Ranging from quality sessions by Rock star speakers to relaxation in the pool and late night party in “The Castle”.

Currently the biggest worry is rain… At least for the attendees. As always the (very) hard working organizers are doing their best to create a super experience for everyone attending the conference, and my personal biggest worry is that the participants will actually bring their laptop to the hands-on experience. Actually doing logical replication yourself is so much cooler than seeing it demonstrated. It will be an interesting experience anyhow.

This conference also led to another series of Oracle Hero’s I got to meet in person!

And as always there is really serious stuff going on as well. One of the main challenges or worries today is the developments surrounding Oracle Database Standard Edition Two, and the impact it brings for the development of the European market.
Eliminating this database version forces emerging projects to use the Oracle Cloud, as the super-sharp priced project startup version is no longer available. We had this with Standard Edition One. It also counters Oracles own statement, quite recently presented by Andrew Sutherland, of hybrid cloud functionality, since there is no “on-premise” equivalent for a small scale project anymore!
We are hoping for a good discussion on Friday during the Standard Edition Round Table version at HrOUG, co-hosted by Philippe Fierens, as this development is very heartfelt in Croatia as it is in many European countries.

If you want to read more about this years event in Croatia, please checkout the many tweets and facebook entries by @helifromfinland, @alexnuijten, @roelhartman (ps. Vote for Roel as member of the ODTUG board) and many more!!

Oh, and as far a basic life’s needs go… The Internet on the island is the best ever!!

Updating SQL Developer to use newer Java version

I was being teased by SQL Developer.

Everytime I started it came nagging about that it was being forced to live in an old Java version called jdk1.7.0_45 and that is was not feeling happy about it.
So, I should remedy this, I thought to myself.

First visit was, inspired by some search-work on the WWW, a file called product.conf. Which offered two possiblities:

java

SetJavaHome to some logical location
or
SetJavaHome to nothing, and then SQL Developer would kindly ask me to point it to somwhere to live.

Well… no. My SQL Developer refused it all and just started with this jdk 1.7.

Same hack done in another file on another location, a file called sqldeveloper.conf.
Same result.

Freshly downloaded SQL Developer, put in place… No help!

Erm…

Rename
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 jdk1.7.0_45.jdk
in /Library/Java/JavaVirtualMachines
to
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 xxx-jdk1.7.0_45.jdk

Nope! Still the same nagging…

What now?

In the end, I wound up with one of Jeff Smits’s helpers.
This guy aksed me to “start SQL Developer from the commandline”. Right, but how?

So I finally found:
/Applications/SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh

And that did start SQL Developer from the command-line…

But… wait… an .sh-file!! Interesting!!

And, behold… in this .sh-file lies the answer:sqldev_startup1

So the file reads:
export JAVA_HOME=`/usr/libexec/java_home -v 1.7`
Which I hacked to:
export JAVA_HOME=`/usr/libexec/java_home -v 1.8`

And, presto, error-message gone and SQL Developer now happily lives in Java 8.

Hope this helps somebody out!!

Communication limit with mod_plsql through PlsqlMaxParameters

There is always a lot to do when you run your page. And, somehow, these days there are some default limits to the amount of work you are supposed to do.
Okay, but what does this mean…

In an application, we have a screen to create some appointment between a bunch of people. This bunch can become quite substantial indeed.
In those days we were running Oracle HTTP Server 9i to lift mod_plsql up in the IP, and we never had any issue creating these somewhat larger appointments in our application.

Recently we got a call on our service desk, which said there were some issues in creating an appointment with a greater number of participants. The message read, somewhere in the error-text:

HTTP-400 Too many arguments passed in. Got 2009 parameters. Upper limit is 2000

Okay, somehow this application function is not going to work!

On research we identified the parameter PlsqlMaxParameters to be the problem. This parameter was introduced with the first version of Oracle HTTP Server right after 9i. If unset, PlsqlMaxParameters defaults to a value of 2000.
I am guessing here, but my bet would be this to be a restriction to prevent a buffer-overrun of some kind.
To resolve the issue the PlsqlMaxParameter needed to be added to plsql.conf. (We tried to do this in dads.conf to keep most of Oracle HTTP Server unaffected, but got errored out).
We chose the value of 4000, meaning a 100% increase while maintaining a fair and safe limit.

For Oracle HTTP Server, the parameter is set in plsql.conf which can be found in either:

  • $ORACLE_BASE/Oracle_WT1/instances/instance1/config/OHS/ohs1/moduleconf
  • $ORACLE_BASE/ohs/Apache/modplsql/conf

For Oracle EPG, the parameter can be set as follows:

  • dbms_epg.set_global_attribute(‘max-parameters’, ‘100’);

And this is where it gets tricky!

Currently we are leveraging Oracle 11g Standalone HTTP Server (11.1.1.7), so without this bulky and difficult WebLogic overhead and we thought we’d aught to test this installation with these big appointments.
As expected, we received an error-message, so the behavior is consistent for this part. The scary bit though was that the error-message is no longer an error-message but a hint to go in the woods…

The request could not be understood by the server due to malformed syntax.

Which could virtually mean anything, and if we hadn’t run into the previous error, we’d have a helluvatime trying to resolve this.
Luckily the theater was set and we knew what we were searching for and testing, so we quickly resolved this also by adding PlsqlMaxParameters.

I hope this post will help you troubleshooting this catch-all message too!

Update on July 25th 2014
As we’re running into problems a new, I decided to call upon Oracle Support too… Raise and SR and see what comes floating to the top.

Well, a confirmation of what we already concluded, there is no conclusion…

I'm not able to determine what the maximum value is that you can set this to as it does not appear to have been documented in the bug, and the parameter has not been documented at all in the product documentation. I don't have access to the source code, so I'm not able to see if a hard limit has been set or not.

Raising the number of MaxPlsqlParameters is not considered to be a definite solution. Which kinda sounds logical since where you need a page-call with more than four thousand !! parameters (or even 2,000 when you think of it), you would think something could be optimized.

And the suggestions of MOS, which I will support:

  • I would suggest you raise the value to what you need it to be for your environment. If there is a hard limit in the code, and you configure this above that limit, then you will get an error.
  • I would then suggest you look into your application and work to reduce the number of parameters as per the recommendation of the developer of the parameter.