Video : Invisible Indexes in Oracle Database 11g Onward

In today’s video we’ll discuss Invisible Indexes, introduced in Oracle 11g.

The video is based on this article.

The star of today’s video is Chris Muir, who was instrumental in me becoming a presenter. He invited me down to Australia to speak at a couple of AUSOUG events, which were the first proper conferences I ever presented at.

Cheers

Tim…

A layered approach to product design

Some time ago I wrote this post.

The Problem With Oracle : If a developer/user can’t do it, it doesn’t exist.

As a result of that post I was invited to speak to a bunch of product managers at Oracle. In that session I spoke about the contents of that post and also about my thoughts regarding a layered approach to product design. I thought I would briefly outline the latter here.

I used to be an Oracle specialist, back in the days when it was possible to have a normal job as an Oracle specialist. Nowadays I find myself doing a really wide range of things. That inevitably means I am Googling my way to solutions a lot of the time. If I had stayed completely in the Oracle world I think my attitude to technology would be very different. There’s something about going outside your comfort zone that helps you gain perspective. In my previous post I said,

“I’m just a generalist that uses a whole bunch of products from a whole bunch of companies, and most of them are too damn hard to use, even with the assistance of Uncle Google.”

In my head, the solution to this issue is for companies to take a layered approach to every aspect of their products. They should be asking themselves.

  1. Who is the audience for this product today?
  2. Who do we expect the audience for this product to be in the future?
  3. Does the product we’ve created meet the needs of these audiences?

Let’s use the Oracle database as an example of this. We could use many other database and non-database products.

  1. There are aspects of the Oracle database that are focused on the developer audience, but there are also aspects of the database that are focused heavily on the administrator audience. So the audience for this product is split.
  2. Over time I expect the DBA role to completely disappear. We have already seen the beginning of this with cloud-based databases. I expect this trend to continue. As a result, I would suggest the future audience of the Oracle database will be solely developers.
  3. Since the product currently has a split audience, and we expect one of those roles to go, I don’t think the database is targeting the correct audience. Anything that is currently a DBA task needs to be automated out of existence. Anything where the DBA is a gatekeeper needs some redesign.

So how do we achieve this? My suggestion would be to take a layered approach. I’ve discussed a similar idea of a layered approach to documentation.

  • Layer 1: Simple how-to.
  • Layer 2: Give me some details, but don’t make my head hurt.
  • Layer 3: I’ve taken some paracetamol. Do your worst!

This time I’m thinking about how the product itself works.

  • Layer 1: The product works out of the box. A developer with no experience of administering this engine can start doing something useful with it immediately. There are no commonly used features that are out of their realm of control. A company or person may choose to stay within this layer for the lifetime of a project and see no ill effects.
  • Layer 2: If the company have some skills in the product, or a specific aspect of the product, they may choose to dip down into this layer. Here they can perform some actions that are not directly covered in layer 1, but this doesn’t mean they lose all the benefits of layer 1. They are not switching off all automations just because they want to deviate from one bit of default functionality.
  • Layer 3: The company have some people with God-mode skills and want to do almost everything by hand. They want to take full control of the system.

The important point is, people want work in the layer(s) they are comfortable in, and still do an effective job. This makes the product accessible to everyone, but doesn’t discriminate against those that want to no-life the product, if they can see a benefit in doing so.

I know there will be objections to this approach, and believe me I can make counter-arguments to it myself, but I don’t see a way forward without taking this sort of approach. I’ll go back to a quote by Jacob Duval where he was comparing MySQL and PostgreSQL.

simply put: mysql has a superior developer experience. postgres has a superior DBA experience. DBA is not really a job anymore, so I pick the developer experience every time.

Jacob Duval

The developer experience has to be the #1 focus from now on!

I’m not underestimating the impact of that statement. It is a massive job for a huge and mature product such as the Oracle database, but not doing so is a death sentence IMHO.

I know some people will see this as a cloud sales pitch, but actually it’s not. I think the on-prem products need to live up to these ideals too. Why? Because I see the future as multi-cloud. If Oracle focus entirely on their cloud offerings, people who decide not to pick Oracle Cloud will be left with a sub-par experience when running Oracle products on other clouds. The result of that is they will pick non-Oracle solutions. I don’t think this is a road Oracle should go down.

Cheers

Tim…

PS. I’ve kept this post purposely vague, because I think focussing on individual features will make the post huge, and detract from the overall message…

Update. Akiva Lichtner raised an interesting point. I thought I would add it here, in case someone else is using their own interpretation of what I am suggesting.

“You can see from Tesla’s speed of change that vertical integration which is the opposite of a layered approach is a superior approach. Also reminds me of Bryan Cantrill’s old DTrace talk where he says that the layers conspire to make the whole system impossible to troubleshoot”

Akiva Lichtner

My response.

“It depends what your interpretation of the layers are. Sounds like you are interpreting it as something different to me. The Tesla interface has layers. It can self-drive, or you can drive for yourself. Just like what I’m talking about…”

Me

I’m not suggested we should just keep stacking layer-upon-layer on the existing products. My focus is very much on the potential for a layered audience. As I mentioned above, a Tesla can use autopilot or be driven by a human. Same car. Two modes of operation. We could consider them two audiences.

Video : Adaptive Cursor Sharing

In today’s video we’ll discuss the Adaptive Cursor Sharing feature, introduced in Oracle 11g Release 1.

This video is based on the following article.

Here are some other things you might want to check out.

The star of todays video is Franck Pachot, who is clearly looking for an exit. πŸ™‚

Cheers

Tim…

Video : CURSOR_SHARING : Automatically Convert Literals to Bind Variables in SQL Statements

In today’s video we’ll discuss the CURSOR_SHARING parameter, which determines how the database handles statements containing literal values.

The video is based on this article.

You may also find these useful.

The star of today’s video is Toon Koppelaars from the Real World Performance Group at Oracle, and of #SmartDB fame. πŸ™‚

Cheers

Tim…

PS. I’ve already noticed I say “after login”, when I mean “after logon”. πŸ™‚

Video : Bind Variables : For Performance and Protection Against SQL Injection

In today’s video we’ll discuss how using bind variables in your database applications can improve performance, and protect against SQL injection attacks.

This videos is based on a demo I do in one of my presentations, which was itself based on these articles.

The star of today’s video is Bjoern Rost, of asymmetric man thong fame. In his past life, Bjoern was one of the many people who got me through a speaking tour in one piece. πŸ™‚

Cheers

Tim…

Vagrant & Docker Builds : ORDS and SQLcl 20.3

In a previous post I discussed the recent release of APEX 20.2 and the subsequent builds it triggered. Last night I pulled down ORDS 20.3 and SQLcl 20.3, so I updated my Vagrant and Docker builds again.

The ORDS download page is here. At the time of writing, the SQLcl download page is still showing 20.2, but the SQL Developer download page has a link for the 20.3 SQLcl download at the bottom. Both these versions have been available for about a week. Update: It’s showing 20.3 on both SQLcl pages now. πŸ™‚

Vagrant

All my GitHub Vagrant builds that include ORDS and SQLcl have been updated to use version 20.3.

I had previously updated Tomcat, and a few days ago I updated Java as soon as AdoptOpenJDK was available.

Docker

This is pretty much the same as the Vagrant story.

The relevant GitHub Docker builds, like the ORDS containers, have been updated to include ORDS and SQLcl 20.3.

They are on the latest release of Tomcat and Java from AdoptOpenJDK.

Conclusion

As always, this is made simple using automation! πŸ™‚

Cheers

Tim…

Video : DBMS_JOB Jobs Converted to DBMS_SCHEDULER Jobs in Oracle Database 19c Onward

In today’s video we discuss a change to the old DBMS_JOB scheduler from 19c onward.

This video is based on the following article.

You may find these useful as well.

The star of today’s video is Liron Amitzi, who took a break from shopping to film this. You might notice some familiar faces in the background and on the video screen to the side… πŸ™‚

Cheers

Tim…

Video : Scalable Sequences in Oracle Database 18c Onward

In today’s video we’ll discuss Scalable Sequences, which were documented for the first time in Oracle 18c.

The video is based on this article.

The star of today’s video is David Peak, who is now working on the Oracle Pandemic Response Systems. This video is a throwback to a hotel we stayed in at SΓ£o Paulo a few years back.

Cheers

Tim…

Vagrant & Docker Builds : APEX 20.2 and other updates

The recent release of APEX 20.2 has triggered a build frenzy.

Vagrant

All my GitHub Vagrant builds that include APEX have been updated to APEX 20.2. The builds themselves are unchanged. This was literally an update to the environment files, so it took longer to test the builds than it did to make the changes.

While I was at it, I did a couple of extra updates. I updated Tomcat to version 9.0.39 on all relevant builds, and updated the optional patch script for the single instance database 19c on OL8 build to use the October 2020 bundle patch. The GI bundle isn’t available yet, so I’ve not altered the OL8 19c RAC build. That will happen soon.

Update: I’ve got the GI bundle patch now, and the OL8 19c RAC build has been updated to use it.

There will of course be more updates to the builds once we get the new versions of AdoptOpenJDK, ORDS and SQLcl, that are probably coming soon.

Packer

I mentioned in my VirtualBox 6.1.16 post I would be updating the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes to include the VirtualBox 6.1.16 guest additions. Those are done now.

Docker

This is pretty much the same as the Vagrant story.

The relevant GitHub Docker builds for Oracle database and ORDS containers have been updated to include APEX 20.2.

I’ve also added Tomcat 9.0.39 to the ORDS builds, and updated the optional patch script for the database 19c on OL8 build to use the October 2020 bundle patch.

Once again, more changes will appear as the new versions of AdoptOpenJDK, ORDS and SQLcl appear.

Conclusion

Automation is awesome! A few minutes and we are bang up to date!

Cheers

Tim…

VirtualBox 6.1.16

VirtualBox 6.1.16 has been released.

The downloads and changelog are in the usual places.

I’ve done the installation on Windows 10, macOS and Oracle Linux 7 hosts with no drama.

I’ll be running new Packer builds for the oraclebase/oracle-7 and oraclebase/oracle-8 vagrant boxes, so they should appear with the new version of the guest additions over the next day or so.

Cheers

Tim…

PS. It seems I didn’t write a blog post about the release of 6.1.14, but I am using it on all my servers. Interesting… I can only assume I was abducted by aliens immediately after upgrading to 6.1.14. It’s the only possible explanation!