Pre-creating Interval Partitions

One of the major problems with interval-based partitioning is the statistics. Partitions appear dynamically as-needed based upon data being inserted or udpated, and any partition magically appearing isn’t going to have any statistics.

This is generally a stability issue as you will, at best, be using dynamic statistics for your optimizations. So how do we deal with it? My preferred method is to pre-create the partitions and copy statistics from a good partition into the (more...)

Primarys Keys and their supporting indexes

Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).

Today I noticed that a release was creating a table, then an (more...)

When did I update that row?

I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.

So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the (more...)

The Boat

Coming soon in March there’s one of the most popular global user group conferences, The Norwegian Oracle User Group Spring conference, known fondly as “The Boat“, it takes place from 10th to 12th of March sailing on board a cruise ship. The ship sails from Olso on 10th and arrives in Kiel, Germany the following day where it turns round and sails back to Oslo, arriving at 10am on Saturday 12th.


I (more...)

UKOUG Tech 15

It’s nearly early December, so it must be time for the UK Oracle User Group Conferences – JDE15, Apps15 and Tech15

There’s some absolutely wonderful presentations, and there’s the 2 I’m giving as well:

An Introduction to RAC (12c) at 9am on Monday 6th, and Troubleshooting Goldengate at 12:20 on Tuesday 7th.

I’m also helping our with a new style database Keynote this year. We are inviting questions from the audience and having them answered (more...)

Decoding DBA_TAB_COLUMNS high_value and low_value

When performance tuning, it can be important to understand the statistics in the database. It is worth noting that every column records it lowest (low_value) and highest (high_value) values in DBA_TAB_COLUMNS when you gather stats, like a mini-histogram. If the optimizer believes it knows the range of values within a column, plus the number of different values (cardinality), it can make guesses about the suitability and cost of index access when a predicate references that (more...)

OEM and monitoring the -MGMTDB GMIR Database

When you create Grid Infrastructure in, you are presented with a new (annoyingly named) “-MGMTDB”

This database is a standard, 12C CDB/PDB storing transient performance and other information. If it is lost, no biggie. Just re-create it (in your voting disk DG. Aside: create a new MGMTDB_DG, move your voting disk there, re-create -MGMTDB, then move your voting disk back out to the proper multiple voting volumes.)

However, Oracle Enterprise (more...)

Oracle Midlands Presentation

Ever wonder why your SQL execution plan has changed? Wondering why your boss is shouting something about “traders can’t trade” or “shoppers can’t spend” or “well that’s the Airport shut!” ?

When you write SQL, Oracle runs it through the Optimiser to determine the fastest way to access the data. This access path sometimes changes without anyone noticing, sometimes with sub-optimal results. I’m giving a presentation at an OracleMidlands even (more...)

12C Paging through Data and Restricting Result Sets

Prior to Oracle 12C, if you wanted to page through result sets only returning a bit at a time, or only wanted the first dozen lines of data, it was a right pain. Writing your SQL, then having it as an in-line view with order by, and a rownum on the outer view. And the whole or that in an in-line view with another named rownum around that to create a sliding window. Horrid. Horrid. (more...)

Oracle 12C Problem with datapatch. Part 2, the “fix”

so after much too-ing and fro-ing with Oracle Support, we finally have a solution to my datapatch problem. Prevent the timeout from happening when running datapatch! We know which part of the code was timing out, and we can do this by setting an undocumented event: 18219841.

Here’s a show test to show you how that was done:

SQL: select dbms_sqlpatch.verify_queryable_inventory from dual;

ORA-20008: timed out. Job Load_opatch_inventory_1execution time is more than  (more...)