Do you work in I.T?

If you do, then you know that only 20% of your I.T work is spent at the office. And no, I’m not talking about telecommuting or working remotely here. I’m talking about the 80% of our time we spend handling all those I.T calls from friends and family Smile

So I was motivated to put together this little ode – dedicated to all of us that have to go the extra mile to keep (more...)

Cloning a schema with one line

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional (more...)

DML error logging redux

I posted a nice little feature of DML error logging recently, and a comment came in mentioned that caution is needed with DML error logging because the errors are logged persistently. Hence if you ran a load multiple times, or multiple sessions were utilizing the facility, then the error logging table can quickly become a soup of data that cannot be tracked back to the code your own session was running.

By default this is (more...)

So Far So Good with Force Logging

I mentioned in my previous two posts that I had tried to figure out if it would be safe to turn on force logging on a production database that does a bunch of batch processing on the weekend: post1, post2. We know that many of the tables are set to NOLOGGING and some of the inserts have the append hint. We put in force logging on Friday and the heavy weekend processing ran fine (more...)

Oracle Database: Write arbitrary log messages to the syslog from PL/SQL

Syslog is a standard for message logging, often employed in *NIX environments. It allows separation of the software that generates messages, the system that stores them, and the software that reports and analyzes them. Each message is labeled with a facility code, indicating the software type generating the message, and assigned a severity level.

In *NIX systems syslog messages often end up in /var/log/messages. You can configure these messages to be forwarded to remote syslog (more...)

The holistic SQL tuning series

I did a set of articles for Oracle Magazine on a more holistic view of SQL tuning. What do I mean by “holistic”? It was a reflection of a common problem that I see when questions come into AskTOM, or when people in the community approach me at conferences, namely, there is an inclination to dive straight into the deepest levels of the tuning exercise:

  • “What index should I create?”
  • “Should I increase the (more...)

Where did my RAM go?

In my previous posts (e.g. here and here) I showed how to use ps output (e.g. from ExaWatcher) visualization to spot performance problems in Linux. Here I’d like to show that this approach can be taken a little bit further, namely, to find the source of increase in memory usage.

The R code for this is quite straightforward. I also think it shouldn’t be much of a problem to do the same (more...)

Memory fragmentation: the silent performance killer

In my last post, where I analyzed the problems caused by memory fragmentation on a Linux server, I said very little about memory fragmentation itself. I wanted to tell a story, so I had to dial down the technical stuff. But now that the story is told, I figured I should make a more technical post on the subject. I think it might be useful for many Linux DBAs or SAs, since there is not (more...)

OGB Appreciation Day: It’s All About ME!

The Oracle Groundbreakers program, and it’s previous incarnations going back to OTN and beyond, are all about me. Yes – Me!

What a great bunch of people

Well, having hopefully got you hooked in with the ego-laden title and first line, let me explain.

As Oracle Base (Dr Tim Hall) describes in this post on Oracle Groundbreakers Appreciation day, today we are celebrating what OTN/ODC/Groundbreakers means to many of us. For me it is (more...)

Be Careful When Subscribing To Oracle Learning Subscription

Subscribing to Oracle Learning Subscription seems good in theory but bad in reality.

Oracle support informed. “Oracle University’s policy regarding Learning Subscription courseware materials is that they cannot be downloaded by customers.”

How convenience of Oracle as the info should have been stated at https://education.oracle.com/oracle-learning-subscriptions

Took for granted materials can be downloaded since they are made available to download for all other training formats.

This seems to be a deceptive process by (more...)

How to hang a server with a single ping, and other fun things we learned in a 18c upgrade

Every upgrade is a bit of a lottery. But for a long running, well established system with a lot of legacy code, it can be more of a Russian roulette. You are unlikely to gain much — your system has been running fine, thanks to various tweaks the apps team applied here and there over the years. All these nice and shiny features that arrive with the upgrade — well, sure, there can be a (more...)

The definition of proof

One of the pieces of advice that I often see on the ‘net is that undo space is somehow this incredibly precious thing, and as a consequence, one should always keep the amount of uncommitted changes in the database to a small size.

Personally I think that is baloney (Ed-in reality, as an Australian I have a slightly more powerful choice of term, but lets keep things PG-rated 🙂). But when I recently challenged (more...)

A refreshing look at PIVOT

We had an AskTOM question come in recently where our customer was very excited about the PIVOT operator in SQL which lets you transpose rows to columns. This is a very common requirement in applications that want to take data that has been modelled in “pure” relational form, and present in a more “human-digestible” form. There are plenty of posts and examples out there about PIVOT, but if you haven’t seen one, here’s a trivial (more...)

LISTAGG() as a Cumulative Function

LISTAGG() can be used as an analytic function, but cannot be cumulative. What does that mean, and is there a workaround?

All the OpenWorld 2019 downloads!

Why dig around for hours in the catalog? Here are all of the downloads registered in the OpenWorld catalog!

You can pick and choose from the list below, or if you want a CURL script that will download the entire set, you can find that here. Enjoy!

BOF1167 Java on Azure BOF,Reza Rahman

BOF1321 Why You Should Be Coding with the NetBeans IDE,Mark Stephens

BOF1336 How to Improve the Quality of Your Application (I Wish (more...)

Impact of Force Logging

I am working on upgrading an Oracle database from 11.2.0.4 to 19c and migrating it from HP Unix to Linux. This 15-terabyte database is too large to copy from the old to the new system during our normal weekend downtime window. It also has a ton of weekend batch updates that overlap the normal weekend change window so it would be best for our business processing if the cut over from the (more...)

Then OpenWorld mega-list

You’ve been to OpenWorld…

You’ve seen the great content…

Networked with the community…

But now you want to keep the ball rolling and catch up with the speakers on twitter.

So here is the mega-twitter list…all the speakers from OpenWorld and CodeOne that provided their twitter handles at registration. Enjoy!

Abhinav Shroff https://twitter.com/abhinavshroff
Ahmad Gohar https://twitter.com/ansgohar
aimee pi https://twitter.com/aimeepi
Akshaya Kapoor https://twitter.com/Akshaya_Kapoor
Alan Williams https://twitter.com/alandbsec
Alasdair Nottingham https://twitter.com/Nottycode
ALBERTO (more...)

One More Thing: New Oracle Cloud free tier better than AWS free tier

Larry Ellison just concluded his Oracle OpenWorld keynote with the announcement of an Oracle Cloud free tier that is better than the AWS free tier. The Oracle Cloud free tier never expires and includes the crown jewels. The slides say it all.

Estimating how much write I/O is not logged

I am trying to figure out how much non-logged write I/O an Oracle database is doing. I want to run an ALTER DATABASE FORCE LOGGING command on the database so that I can use Oracle GoldenGate(GGS) which reads updates from Oracle’s logs. GGS will miss writes that are not logged. But if I turn on force logging it may slow down applications that depend on non-logged writes for good performance. So, I want to (more...)

Speaker info for Perth, Australia

I have just got back from the Groundbreakers Latin America tour, and the travelling was made so much easier with the information provided by the organizers. So with the APAC tour coming up, I felt duty bound to give some hopefully useful information about my home town in Perth.

Flying in/out

There are two airport terminals in Perth. In times gone by, these were known as the Domestic and International terminals because, as the names (more...)