12.2 New Feature: the FLEX ASM disk group part 3

In the previous 2 parts of this mini series I introduced the Flex ASM disk group and two related concepts, the Quota Group and File Group. In what should have become the final part (but isn’t) I am interested in checking whether quotas are enforced.

(Un)fortunately I have uncovered a few more things that are worth investigating and blogging about, which is why a) this isn’t the last post and b) it got a bit (more...)

Optimistic ORA_ROWSCN 2: the SCN

In my previous post, I covered lost updates. Before saying how ORA_ROWSCN can help avoid them, I need to talk about the SCN. It's hard to be simple and correct about such a fundamental element of the Oracle database, but I'll try.

Set-based processing

Last week I participated in Oracle’s Real World Performance event — four days of lectures, quizzes, live demos and hands-on exercises. It was quite interesting, even more so than I expected it to be.

Understandably, a lot of time was spent discussing the perils of row-by-row processing. After all, it was Real World Performance, so it was based on performance problems that the authors of the course faced most often. And many, if not most, (more...)

Optimistic ORA_ROWSCN 1: Lost Updates

I've gotten lots of feedback about my proposal to use ORA_ROWSCN for optimistic locking. I'm going to start over in more detail and try to leave less scope for misinterpretation. Thanks to all for your comments, especially Jonathan Lewis who saved me from some technical doubts. "Optimistic locking" is a type of solution to the problem of "lost updates". So what do we mean by "lost update", anyway?

The Eagerly Awaited Internet Trends Report 2017

Mary MeekerEvery year, Mary Meeker – partner at Kleiner Perkins Caufield & Byers – releases her ‘Internet Trends Report’. It is probably the most eagerly awaited slide deck of the year (alongside Stacey Harris’ HR Systems Survey and Research report – which if you haven’t yet filled it in, you can do so here). Mary’s Internet Trends report gives a facts and figures based summary of the current state of the marketplace, and predicts how (more...)

SQL Logic Overkill, again …

It’s interesting to watch people try to solve problems. For example, in a lab exercise that I wrote the simple fix is to replace an external file value with a scalar subquery in an INSERT statement. There’s even a hint about how to fix the external file value in the CSV file.

The approach here was interesting and worked. However, it’s wrong on two levels:

INSERT INTO transaction
(SELECT   transaction_s1.nextval
 ,        tr.transaction_account
 ,         (more...)

In Memoriam

My mother died a few weeks ago after a couple of months in terminal care. One of my tasks while she was in care was to go through all her paperwork and while doing so I discovered a couple of stories from her past that she had typed out (remember type-writers?) about 30 years ago. I typed them up on my laptop and printed several copies to hand out for people to read at (more...)

Free Oracle HCM Cloud backgrounds

It’s a common practice to change the background colour of your environments to give a visual clue which is which. This helps reduce confusion and lessens the risk of entering test data into Production.

Whilst the core project team might easily remember which colour corresponds to which environment (e.g. blue=Prod, red=Dev, green=Test) it’s not so easy for more occasional users. There is a solution for this.

We’ve created some semi-transparent backgrounds that prominently feature (more...)

AskTOM TV episode 8

On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query.  Here is the script output from the video if you want to use this for your own exploration

SQL> create table t as
  2  select *
  3  from dba_objects
  4  where owner = 'SYS' and rownum <= 20
  5  union all
  6  select *
  7  from dba_objects
  8  where owner = 'SYSTEM'
  9  and  (more...)

CSV parsing and tokenizing strings

Most of you will be familiar with the “Comma Separated Values” data format. It is used in spreadsheets and other places to store ordered lists of character data. The delimiter is typically a comma and if a comma is in a list item the item as a whole needs to be quoted, typically with double quotes.



is actually just three items


Also, if we wish to include a double-quote in (more...)

Optimistic Locking with one Timestamp?

A reader called chris asked me a good question about optimistic locking: why not use a “user-defined” scn (e.g. timestamp) as an extra column to achieve the same goal as ORA_ROWSCN? Well, chris, you'd have to use one per row, not one per transaction.

Read Oracle Database 12.2 New Features Manual

I just finished reading the Oracle database 12c new features manual. I have postponed looking at 12.2 until now because for a long time 12.2 was not available for download even though it was available in Oracle’s cloud. Once the download became available I installed 12.2 in a test virtual machine but did not get any further than that. But, the first quarterly update of 12.2 is supposed to come out (more...)

Top Ten Travel hints and Tips

Well, let me be honest right at the top here.  These are not travel hints Smile  These will not help you in any way.

This is me having a whine and a rant about a minority of people that I occasionally encounter when travelling.

Yes, this can probably be best described as me and my first world problems, but I need to expunge these so that next time I travel, I don’t lose (more...)

SQR with 077 umask creates file with 611 permissions

I ran across this strange situation. An SQR opened a new data file for output and created the file with 611 permissions. We needed group read access so this caused a problem. I knew that our login script for the PeopleSoft Unix user set umask to 022 but that without running the login scripts the mask is 077. So, my first thought was that we had started the process scheduler without running the login scripts (more...)

Unrolling loop speeds up program

This is a follow-up to my earlier post about the assembly language book that I am working through. I have struggled to speed up a program using something that the book recommends, unrolling a loop. I think I have finally found an example where unrolling a loop speeds up a program so I wanted to share it.

I am working on Chapter 17 Exercise 2 of the book which asks you to write a program to (more...)

More on Optimistic Locking with ORA_ROWSCN

Thanks to comments by Tony Hasler and pingbacks from Jeff Kemp, here's more detail on how optimistic locking works with SCNs, especially with respect to "restarts" during update.

DIY parallel task execution

We had a question on AskTOM recently, where a poster wanted to rebuild all of the indexes in his schema that had a status of UNUSABLE.  Running the rebuild’s in serial fashion (one after the other) seemed an inefficient use of the server horsepower, and rebuilding each index with a PARALLEL clause also was not particularly beneficial because the issue was more about the volume of indexes rather than the size of each index.


Optimistic Locking: One SCN to rule them all

Previously I showed how to avoid lost updates with ORA_ROWSCN. Now let’s create an API that avoids lost updates with just one SCN. What kind of API? A transaction consists of one or more changes to data that should happen together: either all should happen or none. When the transaction commits, all the changes happen; […]

It’s just bad code or bad design … most of the time

Some years ago I wrote an article for the UKOUG magazine called “Want a faster database – Take a drive on the M25”.  For those not familiar with the United Kingdom, the M25 is one of its busiest roads (M = “motorway”) and because it moves so much traffic, and runs so close to capacity, it has often been referred to as “the world’s largest car park”.  Many people have probably spent a (more...)

Avoiding Lost Updates with ORA_ROWSCN

Applications today use "optimistic locking" to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn't work (see ORA_ROWSCN for optimistic locking). I now think it does!