Oracle Scratchpad

December 8, 2013

Buffer Pins

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 6:21 pm BST Dec 8,2013

Sometimes you get some questions on OTN lead to very geeky investigations. Here’s one that came up a while ago that started with a reasonable observation about recursive subquery factoring, then devolved into a real geek-attack question about buffer headers (x$bh) and buffer pins (x$kccbf).

I contributed a couple of ideas and some basic SQL to the discussion but never got around to doing anything concrete. If anyone has time and is sufficiently curious to play around I’d be interested to see what you did and what conclusions you came to.



December 1, 2013


Filed under: Infrastructure,Oracle,sorting — Jonathan Lewis @ 11:26 am BST Dec 1,2013

I have, in the past, used the dbms_rowid package to create rowids from block addresses (typically faking the first and last rowids that could appear in an extent); but I’ve just been sent a piece of information by Valentin Nikotin that’s going to make me go back and check whether what I’ve done with the package will always give me the correct results. Here’s a little demonstration code that highlights the issue:


November 14, 2013

32K Columns

Filed under: 12c,Function based indexes,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 8:06 am BST Nov 14,2013

Oracle 12c has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of lines (so similar in cost to LOBs) and need some modification to the parameter file and data dictionary (starting the database in upgrade mode) before you can use them.

Richard Foote has a pair of articles on indexing such columns:

Be cautious about enabling this option and test carefully – there are going to be a number of side effects, and some of them may require a significant investment in time to resolve. The first one that came to my mind was that if you’ve created a function-based index on a pl/sql function that returns a varchar2() type and haven’t explicitly created the index on a substr() of the return value then the data type of the function’s return value will change from the current default of varchar2(4000) to varchar2(32767) – which means the index will become invalid and can’t be rebuilt or recreated.

Obviously you can redefine the index to include an explicit substr() call – but then you have to find all the code that was supposed to use the index and modify it accordingly.

November 8, 2013

Row Movement

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 1:26 pm BST Nov 8,2013

Here’s a question that appeared recently on OTN, and it’s one I’ve wondered about a few times – but never spent any time investigating. Are there any overheads to enabling row movement on a table ? If not, why is it not enabled by default and eliminated as an option ?

Obviously there are costs when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly – but is there an inherent overhead even if you do nothing to move a single row ?

Equally obviously you’ve made it possible for some to “accidentally” shrink the table, cause short term locking problems, and longer term performance probems; similarly it becomes possible to update rows in partitioned tables in a way that causes them to move; but “someone might do it wrong” doesn’t really work as an argument for “de-featurising” something that need not have been a feature in the first place.

What have I missed ?
Answers in the comments gratefully received – and possibly discussed.

November 6, 2013

12c In-memory

Filed under: 12c,compression,Indexing,Infrastructure,Oracle — Jonathan Lewis @ 6:53 pm BST Nov 6,2013

I wrote a note about the 12c “In-Memory” option some time ago on the OTN Database forum and thought I’d posted a link to it from the blog. If I have I can’t find it now so, to avoid losing it, here’s a copy of the comments I made:


October 17, 2013

Virtual date partitions

Filed under: Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 7:40 pm BST Oct 17,2013

I posted this question on twitter earlier on today (It was a thought that crossed my mind during a (terrible) presentation on partitioning that I had to sit through a few weeks ago – it’s always possible to be prompted to think of some interesting questions no matter how bad the presentation is, though):

Quiz: if you create a virtual column as trunc(date_col,’W’) and partition on it – will a query on date_col result in partition elimination?


September 30, 2013

Virtual Integrity

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 3:59 pm BST Sep 30,2013

A recent question on the Oracle-L list server described a problem with data coming in from SQL Server and an oddity with referential integrity failing on Oracle because (for example) a child row was in lower case while the parent was in upper.

This raised a few comments on how you might handle referential integrity while allowed case to differ. No doubt it’s been done before – by Tom Kyte if no-one else – but the first thought that crossed my mind was to use virtual columns:

September 11, 2013

MV Refresh

Filed under: compression,Infrastructure,Materialized view,Oracle,Partitioning,Performance — Jonathan Lewis @ 8:47 pm BST Sep 11,2013

I have a fairly strong preference for choosing simple solutions over complex solutions, and using Oracle-supplied packages over writing custom code – provided the difference in cost (whether that’s in human effort, run-time resources or licence fees) is acceptable. Sometimes, though, the gap between simplicity and cost is so extreme that a hand-crafted solution is clearly the better choice. Here’s an idea prompted by a recent visit to a site that makes use of materialized views and also happens to be licensed for the partitioning option.


August 28, 2013

Sorted Hash Clusters RIP

Filed under: Bugs,Infrastructure,Oracle — Jonathan Lewis @ 8:22 am BST Aug 28,2013

Sorted Hash Clusters have been around for several years, but I’ve not yet seen them being used, or even investigated in detail. This is a bit of a shame, really, because they seem to be engineered to address a couple of interesting performance patterns.


August 15, 2013

MV Refresh

Filed under: Bugs,CBO,Infrastructure,Materialized view,Oracle,Statistics — Jonathan Lewis @ 6:12 pm BST Aug 15,2013

Here’s a funny little problem I came across some time ago when setting up some materialized views. I have two tables, orders and order_lines, and I’ve set up materialized view logs for them that allow a join materialized view (called orders_join) to be fast refreshable. Watch what happens if I refresh this view just before gathering stats on the order_lines table.


August 9, 2013


Filed under: 12c,Infrastructure,Oracle — Jonathan Lewis @ 10:03 am BST Aug 9,2013

Just glancing through the 12c manuals (Server Reference 12.1 June 2013 – E17615-16) to check a particular database limit, I came across the following: “Services – maximum per instance – 115″. That’s a bit of a problem, given that you can have 254 pluggable (tenant) databases in a single container database, and each plugged database gets its own service – but I’m guessing that that bit of the manual is wrong, after all it didn’t say anything about pluggable databases at all. It’s hard to keep documentation up to date as things change.

Here’s a random thought, though, loosely linked to database limits. If you’re looking ahead to a time when you have lots of tenants in a container database, you might want to start by migrating your existing databases from smallfile tablespaces to bigfile tablespaces (which may make it a good idea to run with change tracking enabled) so that the final container database doesn’t have a totally unmanageable number of database files.

Update 13th Aug 2013

Read the comments for a limit on the total number of services a container database can run.


July 30, 2013


Filed under: ASSM,Infrastructure,Oracle — Jonathan Lewis @ 6:53 am BST Jul 30,2013

A couple of thoughts.

The intent of ASSM is to minimise contention when multiple small transactions are busy inserting data concurrently into the same table. As a consequence, you may be able to find a number of odd behaviour patterns if you do experiments with a single session running one transaction at a time; or when executing a single large transaction, or when experimenting with small tables.


July 4, 2013

12c trivia

Filed under: 12c,Infrastructure,Oracle — Jonathan Lewis @ 4:51 am BST Jul 4,2013

Weighing in at a massive 54 characters – the longest parameter name in 12c is:

  • _adaptive_scalable_log_writer_disable_worker_threshold

Followed very closely by (you guessed it)

  •  _adaptive_scalable_log_writer_enable_worker_threshold


June 25, 2013


Filed under: 12c,Infrastructure,Oracle,Partitioning,redo — Jonathan Lewis @ 11:43 pm BST Jun 25,2013

The news is out that 12c is now available for download (Code, Docs and Tutorials). There are plenty of nice little bits in it, and several important additions or enhancements to the optimizer, but there’s one feature that might prove to be very popular:

SQL> alter table p1 move partition solo ONLINE;

Table altered.


June 19, 2013

Wasted Space

Filed under: compression,fragmentation,Infrastructure,LOBs,Oracle — Jonathan Lewis @ 9:55 am BST Jun 19,2013

Here’s a little quiz: If I take the average row length of the rows in a table, multiply by the number of rows, and convert the result to the equivalent number of blocks, how can the total volume of data in the table be greater than the total number of blocks below the table high water mark ? I’ve got three tables in a schema, and they’re all in the same (8KB block, 1M uniform extent, locally managed) tablespace, but here’s a query, with results, showing their space utilisation – notice that I gather schema stats immediately before running my query:


« Previous PageNext Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 5,217 other followers