Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

partitioning

How to move Partitions ONLINE and make them READ ONLY in #Oracle 12c

readonlyhttps://uhesse.files.wordpress.com/2017/01/readonly.png?w=574&h=60

How to reduce Buffer Busy Waits with Hash Partitioned Tables in #Oracle

fight_contention_2https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=576&... 576w, https://uhesse.files.wordpress.com/2016/12/fight_contention_2.png?w=144&... 144w" sizes="(max-width: 288px) 100vw, 288px" />

Large OLTP sites may suffer from Buffer Busy Waits. Hash Partitioning is one way to reduce it on both, Indexes and Tables. My last post demonstrated that for Indexes, now let’s see how it looks like with Tables. Initially there is a normal table that is not yet hash partitioned. If many sessions do insert now simultaneously, the problem shows:

How to reduce Buffer Busy Waits with Hash Partitioned Indexes in #Oracle

fight_contentionhttps://uhesse.files.wordpress.com/2016/12/fight_contention.png?w=150&h=132 150w, https://uhesse.files.wordpress.com/2016/12/fight_contention.png 474w" sizes="(max-width: 300px) 100vw, 300px" />

Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:

How Partial Indexing helps you save space in #Oracle 12c

partialhttps://uhesse.files.wordpress.com/2016/12/partial1.png?w=600&h=580 600w, https://uhesse.files.wordpress.com/2016/12/partial1.png?w=150&h=145 150w" sizes="(max-width: 300px) 100vw, 300px" />

Over time certain partitions may become less popular. In 12c, you don’t have to index these partitions anymore! This can save huge amounts of space and is one of the best 12c New Features in my opinion. Really a big deal if you are working with range partitioned tables where the phenomenon of old ranges becoming unpopular is very common. Let’s have a look, first at the problem:

How to change RANGE- to INTERVAL-Partitioning in #Oracle

set_intervalhttps://uhesse.files.wordpress.com/2016/11/set_interval.png?w=578&h=600 578w, https://uhesse.files.wordpress.com/2016/11/set_interval.png?w=144&h=150 144w" sizes="(max-width: 289px) 100vw, 289px" />

An existing RANGE partitioned table can easily be changed to be INTERVAL partitioned with the SET INTERVAL command. My table has been created initially like this:

How to rebuild a 12 TB index that you accidentally dropped from a 55 TB table

Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)

DML and Bloom

One of the comments on my recent posting about “Why use pl/sql bulk strategies over simple SQL” pointed out that it’s not just distributed queries that can change plans dramatically when you change from a simple select to “insert into … select …”; there’s a similar problem with queries that use Bloom filters – the filter disappears when you change from the query to the DML.

This seemed a little bizarre, so I did a quick search on MoS (using the terms “insert select Bloom Filter”) to check for known bugs and then tried to run up a quick demo. Here’s a summary of the related bugs that I found through my first simple search:

Virtual Partitions

Here’s a story of (my) failure prompted by a recent OTN posting.

The OP wants to use composite partitioning based on two different date columns – the table should be partitioned by range on the first date and subpartitioned by month on the second date. Here’s the (slightly modified) table creation script he supplied:

DDL logging

I was presenting at the UKOUG event in Manchester on Thursday last week (21st April 2016), and one of the sessions I attended was Carl Dudley’s presentation of some New Features in 12c. The one that caught my eye in particular was “DDL Logging” because it’s a feature that has come up fairly frequently in the past on OTN and other Oracle forums.

So today I decided to write a brief note about DDL Logging – and did a quick search of my blog to see if I had mentioned it before: and I found this note that I wrote in January last year but never got around to publishing – DDL Logging is convenient, but doesn’t do the one thing that I really want it to do:

Wrong Results Involving INDEX FULL SCAN (MIN/MAX) in 12.1.0.2

One of my customers that recently upgraded to 12c hit a bug (22913528) that I think is good to be aware of. Note that as the title of this post states, the problem only occur in 12.1.0.2. At least, I wasn’t able to reproduce it in any other version.

To reproduce it you simply need a composite partitioned table with a non-partitioned or global-partitioned index. In other words, if all your indexes are local, you shouldn’t be impacted by the bug.

The SQL statements I use to prepare the schema to reproduce it are the following: