12c

12c Parse

Following on from a comment to a recent posting of mine about “bad” SQL ending up in the shared pool and the specific detail that too much bad SQL could cause contention problems while staying virtually invisible, there’s a related note today on the ODC (formerly OTN) forum of a little change in 12.2 that alerts you to the problem.

Try executing the following anonymous block (on a non-production system):

Oracle Open World 2017 presentation

Hi,
I will be presenting about ASM internals in Oracle Open World 2017 conference on Sunday October 1st. Following are the details:

Session ID: SUN5682

Session Title: Oracle Automatic Storage Management and Internals

Room: Moscone South – Room 155 Date: 10/01/17
Start Time: 12:45:00 PM
End Time: 01:30:00 PM

See you there!

Update: Added the presentation file. ASM_internals_Riyaj_OOW2017

When UPDATE becomes an INSERT

During a research for VOODOO, we came across a lot of interesting stuff inside REDO.
One of my favourites is an UPDATE, becoming an INSERT </p />
</p></div>

    	  	<div class=

When UPDATE becomes an INSERT

During a research for VOODOO, we came across a lot of interesting stuff inside REDO.
One of my favourites is an UPDATE, becoming an INSERT </p />
</p></div>

    	  	<div class=

12.2 Introduction to Real-Time Materialized Views (The View)

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which […]

dbms_sqldiag

If you’re familiar with SQL Profiles and SQL Baselines you may also know about SQL Patches – a feature that allows you to construct hints that you can attach to SQL statements at run-time without changing the code. Oracle 12c Release 2 introduces a couple of important changes to this feature:

  • It’s now official – the feature had been copied from package dbms_sqldiag_internal to package dbms_sqldiag.
  • The limitation of 500 characters has been removed from the hint text – it’s now a CLOB column.

H/T to Nigel Bayliss for including this detail in his presentation to the UKOUG last week, and pointing out that it’s also available for Standard Edition.

12.2 Partitions

At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most) of points I’d made with a slide making the claim:

In 12.2 you can: Convert a simple table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.

 

Last night I decided I ought to demonstrate the claim – so here’s a little code, first creating a simple heap table:

Histogram Upgrade – 2

While reading a blog post by Maria Colgan a couple of weeks ago I came across an observation about histograms that I’d not noticed before; worse still, it was a feature that seemed to make some “damage-limitation” advice I’d been giving for years a really bad idea! The threat appeared in these paragraphs:

Setting SIZE REPEAT ensures a histogram will only be created for any column that already has one. If the table is a partitioned table, repeat ensures a histogram will be created for a column that already has one on the global level.

What’s the down side to doing this?

The current number of buckets used in each histogram becomes the limit on the maximum number of buckets used for any histogram created in the future.

Parallelism

Headline – if you don’t want to read the note – the /*+ parallel(N) */ hint doesn’t mean a query will use parallel execution, even if there are enough parallel execution server processes to make it possible. The parallel(N) hint tells the optimizer to consider the cost of using parallel execution for each path that it examines, but ultimately the optimizer will still take the lowest cost path (bar the odd few special cases) and that path could turn out to be a serial path.

The likelihood of parallelism appearing for a given query changes across versions of Oracle so you can be fooled into thinking you’re seeing bugs as you test new versions but it’s (almost certainly) the same old rule being applied in different circumstances. Here’s an example – which I’ll start off on 11.2.0.4:

255 Again!

There are so many things that can go wrong when you start using tables with more than 255 columns – here’s one I discovered partly because I was thinking about a client requirement, partly because I had a vague memory of a change in behaviour in 12c and Stefan Koehler pointed me to a blog note by Sayan Malakshinov when I asked the Oak Table if anyone remembered seeing the relevant note. Enough of the roundabout route, I’m going to start with a bit of code to create a table, stick a row in it, then update that row: