Skip navigation.

Feed aggregator

SQL Needs a Sister (Broken Link Corrected)

Gerger Consulting - Wed, 2015-11-04 01:28
I've just published an article on Medium about the missing sister of SQL. I think there is a fundamental mistake we’ve been making in using SQL. We use it both to ask a question and format the answer and I think this is just wrong. You can read the article at this link. (Sorry for the broken link in the previous post and a big thank you to the person who sent us a comment about the issue. :-) )
Categories: Development

November 17: Lumosity―Oracle ERP Cloud Customer Reference Forum

Linda Fishman Hoyle - Tue, 2015-11-03 18:04

Join us for another Oracle Customer Reference Forum on November 17, 2015 at 9:00 a.m. PDT. Tyler Chapman, VP of Finance and Controller of Lumosity will explain how Oracle ERP Cloud is helping this innovative company scale and expand globally. It is also helping to provide management insightful decision-making data and advance the finance organization as a strategic business partner.

Chapman will share his views on the best time to invest in a new ERP system and how to take advantage of Oracle ERP Cloud’s embedded best practices. He will put this into the context of Lumosity’s goals to leverage Oracle’s fully integrated ecosystem, including HCM.

Register to attend the live Forum on Tuesday, November 17, 2015 at 9:00 a.m. PDT and learn more about Lumosity’s experience with Oracle ERP Cloud.

Hide and Seek

Scott Spendolini - Tue, 2015-11-03 14:30

In migrating SERT from 4.2 to 5.0, there's a number of challenges that I'm facing. This has to do with the fact that I am also migrating a custom theme to the Universal Theme, as almost 100% of the application just worked if I chose to leave it alone. I didn't. More on that journey in a longer post later.

In any case, some of the IR filters that I have on by default can get a bit... ugly. Even in the Universal Theme:

2015 11 03 15 25 18

In APEX 4.2, you could click on the little arrow, and it would collapse the region entirely, leaving only a small trace that there's a filter. That's no longer the case:

2015 11 03 15 25 31

So what to do... Enter CSS & the Universal Theme.

Simply edit the page and add the following to the Inline CSS region (or add the CSS to the Theme Roller if you want this change to impact all IRs):

.a-IRR-reportSummary-item { display: none; }

This will cause most of the region to simply not display at all - until you click on the small triangle icon, which will expand the entire set of filters for the IR. Clicking it again makes it go away. Problem solved with literally three words (and some punctuation).

OakTable video of myself and others

Bobby Durrett's DBA Blog - Tue, 2015-11-03 10:53

You can find the full length video of my Delphix talk that I did at OakTable World on Tuesday here: url

Also, the OakTable folks have updated the OakTable World agenda page with video of all the talks. This has lots of good material and for free. Scroll down to the bottom of the page to find the links to the videos.


Categories: DBA Blogs

Partner Webcast – Oracle Mobile Security Suite (OMSS): Unified Security for Mobility

Since the latest release, Oracle Mobile Security Suite (OMSS) is a fully featured Identity-Centric Enterprise Mobility Management (EMM) Platform that can address a mix of both BYOD and corporate...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Twitter : Is it a valuable community contribution? (Follow Up)

Tim Hall - Tue, 2015-11-03 08:01

There was some pretty interesting feedback on yesterday’s post, so I thought I would mention it in a follow up post, so it doesn’t get lost in the wasteland of blog comments. :)

Remember, I wasn’t saying certain types of tweets were necessarily good or bad. I was talking about how *I* rate them as far as content production and how they *might* be rated by an evangelism program…

  • Social Tweets : A few people including Martin, Oyvind, Stew and Hermant, mentioned how social tweets are good for binding the community and helping to meet other like-minded people. I agree and I personally like the more random stuff that people post. The issue was, does this constitute good content that should be considered for your inclusion in an evangelism program? I would say no.
  • Timeline : Baback, Matthew, Noons, Hermant all mentioned things about the timeline issue associated with Twitter. Twitter is a stream of conciousness, so if you tune out for a while (to go to bed) or you live in a different time zone to other people, it is easy for stuff to get lost. You don’t often come across an old tweet, but you will always stumble upon old blog posts and articles, thanks to the wonders of Google. :) The quick “disappearance” of information is one of the reasons I don’t rate Twitter as a good community contribution.
  • Notifications : There was much love for notification posts. These days I quite often find things via Twitter before I notice them sitting in my RSS reader. I always post notifications and like the fact others do too, but as I said yesterday, it is the thing you are pointing too that is adding the most value, not the notification tweet. The tweet is useful to direct people to the content, but it in itself does not seem like valuable community participation to me, just a byproduct of being on Twitter.
  • Content Aggregation : Stew said an important point where content aggregation is concerned. If you tweet a link to someone else’s content, you are effectively endorsing that content. You need to be selective.
  • Audience : Noons mentioned the audience issue. Twitter is a public stream, but being realistic, the only people who will ever notice your tweets are those that follow you, those you tag in the tweet or robots mindlessly retweeting hashtags. Considering the effective lifespan of a tweet, it’s a rather inefficient mechanism unless you have a lot of followers, or some very influential followers.

So I’m still of a mind that Twitter is useful, but shouldn’t be the basis of your community contribution if you are hoping to join an evangelism program. :)



Update: I’ve tried to emphasize it a number of times, but I think it’s still getting lost in the mix. This is not about Twitter=good/bad. It’s about the value you as an individual are adding by tweeting other people’s content, as opposed to creating good content yourself. All community participation is good, but just tweeting other people’s content is less worthy of attention *in my opinion*, than producing original content.

If someone asked the question, “What do I need to do to become an Oracle ACE?”, would you advise them to tweet like crazy, or produce some original content? I think that is the crux of the argument. :)

Of course, it’s just my opinion. I could be wrong. :)

Twitter : Is it a valuable community contribution? (Follow Up) was first posted on November 3, 2015 at 3:01 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Live Webcast: 3 Ways to Drive Digital Workplace Collaboration and Mobility

WebCenter Team - Tue, 2015-11-03 07:35

Suffering from post #OOW15 blues or missed last week's conference? We have got a live webcast just for you. Here is your chance to catch Oracle executive, David Le Strat and one of our customers, Mythics as they discuss use cases for Oracle cloud collaboration and engagement solutions. Don't miss hearing first-hand on how a processes-intense company such as Mythics streamlined business automation and is leveraging Oracle's holistic cloud engagement solution to solve one of their biggest business challenges in house.

Register today at:

WEBCAST: Three Ways to Power Digital Workplace Collaboration and Mobility a:link { color: #1f4f82; } a:visited { color: #1f4f82; } a:hover { color: #1f4f82; } a:active { color: #1f4f82; } table, th, td { border-color: #c0c0c0 !important; } #bold{ font-weight:bold; } #SpeakerImg{ border: 1px solid #c0c0c0 ; padding: 2px; } #connectionBox{ border: 1px #c0c0c0 solid; width: 178px; margin: 0px auto 0px auto; } .footerLinks{ color: #FF0000 !important; font-size: 10px; font-style: Arial, Helvetica, sans-serif; } #agenda{ border-color: #CCCCCC; border-style: solid; border-width: 1px 0px 0px 0px; } #bodyTable{ border-right: 1px solid; border-left: 1px solid; border-top: 1px solid; border-bottom: 1px solid; } #headerLogo{ padding-left: 21px; } #headerImg{ display: block; }

Oracle Corporation  Three Ways to Power Digital Workplace Collaboration and Mobility Cloud Engagement beyond Simple File Sharing

A recent study showed that over a year, organizations with enterprise social collaboration saw a 96% improvement in customer response times. 96%! And with the mobile workforce reaching 1.3 billion this year, it is no wonder that organizations are looking differently at user engagement today.

Join this webcast to find out how Oracle is addressing the critical need for social- and mobile-enabled cloud engagement, and hear directly from Mythics on how the company is leveraging Oracle Cloud Services to drive workplace collaboration, streamline contracts management and accelerate business value. Learn how your organization can drive speed to market and improve mobility and productivity for Marketing, Sales, HR, Operations and Customer Experience.

You will learn about:
  • Oracle's strategy, vision and comprehensive solution to address the market need
  • Mythics' business automation and collaboration use cases and cloud strategy
  • A roadmap to driving business innovation, mobility and productivity with cloud engagement

Register Now for this webcast.

Red Button Top Register Now Red Button Bottom Live Webcast Calendar November 10, 2015
10:00 AM PT
/ 1:00 PM ET
Brent Seaman Brent Seaman,
Vice President Cloud Solutions
Mythics, Inc.
David Le Strat David Le Strat,
Senior Director Product Management,
WebCenter and Business Process Management
Oracle Integrated Cloud Applications and Platform Services Copyright © 2015, Oracle Corporation and/or its affiliates.
All rights reserved.
Contact Us | Legal Notices and Terms of Use | Privacy Statement

Owning Your Content

Tim Hall - Tue, 2015-11-03 05:49

Another thing that came out of my conversation with Zahid Anwar at OOW15, was about owning your content.

If your intention is to make a name for yourself in the community, it’s important you think about your “brand”. Most of us old-timers didn’t have to worry about this, and sometimes get a bit snooty about the idea of it, but we started early, so it was relatively easy to get noticed. For new people on the scene, it’s a much harder proposition.

It’s possible to write content on sites like Facebook, Google+ and LinkedIn, but I’m not sure that’s the best way to promote “your brand”. In some communities it might be the perfect solution, but in others I think you are in danger of becoming a faceless contributor to their brand.

In my opinion, it would be better to start a blog or website, then post links to your content to the other resources as part of promoting yourself. That way, you remain the owner of the content and it helps promote your brand.

I’ve said similar stuff to this in my Writing Tips series.



Owning Your Content was first posted on November 3, 2015 at 12:49 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

SQL Needs a Sister

Gerger Consulting - Tue, 2015-11-03 03:24
I just published an article on Medium about the missing sister of SQL. I think there is a fundamental mistake we’ve been making in using SQL. We use it both to ask a question and format the answer and I think this is just wrong. You can read the article at this link.
Categories: Development

Nul points

Jonathan Lewis - Tue, 2015-11-03 02:16

(To understand the title, see this Wikipedia entry)

The title could also be: “Do as I say, don’t do as I do”, because I want to remind you of an error that I regularly commit in my demonstrations. Here’s an example:

SQL> create table t (n number); 

Table created 

Have you spotted the error yet ? Perhaps this will help:

SQL> insert into t select 1 - 1/3 * 3 from dual; 

1 row created. 

SQL> insert into t select 1 - 3 * 1/3 from dual; 

1 row created. 

SQL> column n format 9.99999999999999999999999999999999999999999 
SQL> select * from t; 


2 rows selected. 

Spotted the error yet ? If not then perhaps this will help:

SQL> select * from dual where 3 * 1/3 = 1/3 * 3;

no rows selected 

SQL> select * from dual where 3 * (1/3) = (1/3) * 3; 


1 row selected. 

Computers work in binary, people (tend to) work in decimal. 10 = 2 * 5, and 5 (more precisely, dividing by 5) is something that a computer cannot do accurately. So when you do arbitrary arithmetic you should use some method to deal with tiny rounding errors.

In Oracle this means you ought to define all numbers with a precision and scale. Look on it as another form of constraint that helps to ensure the correctness of your data as well as improving performance and reducing wasted storage space.

Oracle Open World 2015: Notes from the Trenches on Your Digital Strategy

WebCenter Team - Mon, 2015-11-02 18:29

The beat of the Oracle Open World drum still resonates in my ears as we came to yet another grand finish of one of the world's greatest confluence of brilliant minds and ground-breaking technology. What astounds me with Oracle is how nimble we are for a giant the size that we are, especially when it comes to innovation in the Cloud. 

Thomas Kurian, Oracle President of Product Development, discussed new cloud services that show Oracle's deep innovation across cloud applications, platform, and infrastructure services. What I personally love about Kurian, being the geek that I am, is the demonstrations of the several services that are interspersed in his presentation, showing how easily people can tap Oracle's cloud services. "Any person, anyplace in the world, with just a browser, can access our cloud and get access to all these new product innovations we've delivered", Kurian said during his keynote Tuesday in San Francisco.  You can learn more on his message here: Press Release on Oracle's Cloud Innovation.

The WebCenter and BPM sessions ran to packed audiences clinging to the edge of their seats as Oracle spearheads the digital market. Oracle announced a new and upcoming Sites Cloud Service in the digital category for creation of multi-channel enagagement sites on the cloud. In addition Oracle Process Cloud Service, Document Cloud Service and Oracle Social Network, are well integrated with the rest of the cloud service stack, coming together to deliver on the next-generation enablement for line of business workers.

The highlight of the show was the Innovation Awards Night - the Grammy of Oracle - where our customers gain limelight for their creativity and innovation. In the Digital Experience category, we had Atradius, Credit Suisse, AFG and Sutton Tools steal the show. 

The sessions included customer successes, product deep dives, partner discussions, persona and/or industry based discussions, Cloud/PaaS lessons, live product demonstrations and Hands-On-Labs (HOL). To get a quick overview of the many sessions in the digital umbrella including Process Cloud Service, Document Cloud Service and the up and coming Sites Cloud Service, you can review the focus-ons:

Focus on: Digital Experience, Content and Business Process Management

Focus on: Content and Collaboration in the Cloud

Oracle's unique vision and offering gathered resounding attention for setting itself apart as the global leader in the digital business. The underlying theme of Oracle BPM and WebCenter this year established it as a complete, business driven, and solutions based offering that can transform organizations to deliver on the digital vision not just on-premise but also on the cloud. 

Overall Oracle Open World 2015 was a mindblowing event with outstanding teamwork and exceptional delivery on every front from the customer, partner, and employee base of the organization. To continue learning about how we can enable your digital business, visit us: and

And if you missed Oracle Open World 2015, no problem, just find an Oracle Day 2015 in a city near you to meet our experts locally.

Updated Testing Advanced Pack Overview Whitepaper

Anthony Shorten - Mon, 2015-11-02 13:00

An updated version of the Oracle Functional/Load Testing Advanced Pack for Oracle Utilities has been released on My Oracle Support at Doc Id: 2014163.1. The updates to the whitepaper include updates for the release with the following information:

  •  Updates for the new content for Oracle Mobile Workforce Management, Oracle Real Time Scheduler, Oracle Utilities Application Framework, Oracle Utilities Customer Care and Billing and Oracle Work And Asset Management.
  • Updates for the new Component Builder and Component Verifier utilities that allow the addition of new custom components.
  • Updates to the Frequently Asked Questions covering licensing and the common implementation questions you may have about the Advanced Pack.

This new version now only adds new content and new capabilities but utilizes the power and facilities of the Oracle Application Testing Suite (Oracle Flow Builder, OpenScript and Oracle Load Testing) to deliver the ability to rapidly test, implement and upgrade Oracle Utilities products.

Rittman Mead and Oracle Big Data Webcast Series – November 2015

Rittman Mead Consulting - Mon, 2015-11-02 12:45

We’re running a set of three webcasts together with Oracle on three popular use-cases for big data within an Oracle context – with the first one running tomorrow, November 3rd 2015 15:00 – 16:00 GMT / 16:00 – 17:00 CET on extending the data warehouse using Hadoop and NoSQL technologies.

The sessions are running over three weeks this month and look at ways we’re seeing Rittman Mead use big data technologies to extend the and capabilities of their data warehouse, create analysis sandpits for analysing customer behaviour, and taking data discovery into the Hadoop era using Oracle Big Data Discovery. All events are free to attend, we’re timing them to suit the UK,Europe and the US, with details of each webcast are as follows:


Extending and Enhancing Your Data Warehouse to Address Big Data

Organizations with data warehouses are increasingly looking at big data technologies to extend the capacity of their platform, offload simple ETL and data processing tasks and add new capabilities to store and process unstructured data along with their existing relational datasets. In this presentation we’ll look at what’s involved in adding Hadoop and other big data technologies to your data warehouse platform, see how tools such as Oracle Data Integrator and Oracle Business Intelligence can be used to process and analyze new “big data” data sources, and look at what’s involved in creating a single query and metadata layer over both sources of data.

Audience: DBAs, DW managers, architects Tuesday 3rd November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

Audience : DBAs, DW managers, architects

What is Big Data Discovery and how does it complement traditional Business Analytics?

Data Discovery is an analysis technique that complements traditional business analytics, and enables users to combine, explore and analyse disparate datasets to spot opportunities and patterns that lie hidden within your data. Oracle Big Data discovery takes this idea and applies it to your unstructured and big data datasets, giving users a way to catalogue, join and then analyse all types of data across your organization. At the same time Oracle Big Data Discovery reduces the dependency on expensive and often difficult to find Data Scientists, opening up many Big Data tasks to “Citizen” Data Scientists. In this session we’ll look at Oracle Big Data Discovery and how it provides a “visual face” to your big data initiatives, and how it complements and extends the work that you currently do using business analytics tools.

Audience : Data analysts, market analysts, & Big Data project team members Tuesday 10th November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

Adding Big Data to your Organization to create true 360-Degree Customer Insight

Organisations are increasingly looking to “big data” to create a true, 360-degree view of their customer and market activity. Big data technologies such as Hadoop, NoSQL databases and predictive modelling make it possible now to bring highly granular data from all customer touch-points into a single repository and use that information to make better offers, create more relevant products and predict customer behaviour more accurately. In this session we’ll look at what’s involved in creating a customer 360-degree view using big data technologies on the Oracle platform, see how unstructured and social media sources can be added to more traditional transactional and customer attribute data, and how machine learning and predictive modelling techniques can then be used to classify, cluster and predict customer behaviour.

Audience : MI Managers, CX Managers, CIOs, BI / Analytics Managers Tuesday 24th November, 15:00 – 16:00 GMT / 16:00 – 17:00 CET – Click here to register

The post Rittman Mead and Oracle Big Data Webcast Series – November 2015 appeared first on Rittman Mead Consulting.

Categories: BI & Warehousing

Should we be muddying the relational waters? Use cases for MySQL & Mongodb

Sean Hull - Mon, 2015-11-02 11:55
Many of you know I publish a newsletter monthly. One thing I love about it is that after almost a decade of writing it regularly, the list has grown considerably. And I’m always surprised at how many former colleagues are actually reading it. So that is a really gratifying thing. Thanks to those who are, … Continue reading Should we be muddying the relational waters? Use cases for MySQL & Mongodb →

Log Buffer #447: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2015-11-02 09:34


This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.


  • An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
  • SQL Monitoring in Oracle Database 12c.
  • RMAN Full Backup vs. Level 0 Incremental.
  • Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
  • How to move OEM12c management agent to new location.

SQL Server:

  • Automate SQL Server Log Monitoring.
  • 10 New Features Worth Exploring in SQL Server 2016.
  • The ABCs of Database Creation.
  • Top 10 Most Common Database Scripts.
  • In-Memory OLTP Table Checkpoint Processes Performance Comparison.


  • The Client Library, Part 1: The API, the Whole API and Nothing but the API.
  • Performance of Connection Routing plugin in MySQL Router 2.0.
  • MariaDB 10.0.22 now available.
  • Semi-synchronous Replication Performance in MySQL 5.7.
  • MySQL and Trojan.Chikdos.A.


Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

Twitter : Is it a valuable community contribution?

Tim Hall - Mon, 2015-11-02 08:54

During a conversation with Zahid Anwar at OOW15, the question was asked, is Twitter content a valuable contribution to the community?

The following is *my opinion* on the matter. Other opinions are valid.

The sort of tweets I see fall into the following basic categories:

  • Technical Questions and Answers. When these are done well, they are really useful and a quick way to get to the bottom of something. When answers come as links to content, that’s really cool as there is some depth to the answer. An answer in 140 chars is not always so good, and is often missing vital information that usually starts in a flame/caveat war. Though I do think of this as a useful community contribution, I think this sort of thing is better dealt with in a forum. I guess you could maybe Tweet about the question to raise some attention, but that feels a bit like the “URGENT” prefix to a question, which turns many people off. :)
  • Notifications. Tweeting about your latest blog post, article or video is part of getting your message out to your followers, but the tweets have little or no value in themselves. It’s just self promotion, which we all do. It’s the things you are promoting that hopefully have value, not the tweets themselves. In this sense, the tweets are not a valuable community contribution.
  • Content Aggregation. Tweeting other people’s content is a good way to introduce your followers to it and “spread a specific message”. I do this when I read something I really like, or if I am trying to help promote someone who I think deserves more attention. I think you have to be careful not to become a “blog aggregator by proxy” and blanket tweet everything you come across, or the value of your tweets drops. It just feels like lazy way to look busy. Just my opinion though. I’m sure there are people out there that love it.
  • Random tweets. These can give you some insight into the individuals that make up the community, which I like, but there is no long term value in these, even if they are fun. :)

If you are trying to get on to a community program, like the Oracle ACE Program, *I would* rate twitter contributions quite low. I would focus on stuff where you are providing original content (blogging, whitepapers, books, YouTube etc) or directly helping people, like forums or presenting. Short-form social media is a nice addition, but it’s value is rather limited in my opinion.

Remember, it’s just my opinion, but I’m interested to know your thoughts.



Update: I think it’s worth clarifying my point some more. I don’t have a problem with any of these types of tweets. I do them all to a greater or lesser extent. The point I’m trying to make (badly), is the content that is pointed to is the “high value” in my opinion. The “pointer” (tweet) is of far less value. If someone came to me and said, “I tweet a lot about other people’s content, can I join your community program (if I had one), I would probably say no and encourage them to produce their own content. That was the context of the conversation that initiated this post. :)

Twitter : Is it a valuable community contribution? was first posted on November 2, 2015 at 3:54 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Show me the money – Business Intelligence

Nilesh Jethwa - Mon, 2015-11-02 06:33

This Business Intelligence article discusses one very important question “Have we lost the true meaning and purpose of Business Intelligence?”

In summary, it says that the bottom line for any Business Intelligence is to increase profit and performance and if done wrong it is absolute waste of time and money.

So if your management is hooked on the Big-Data wagon and fantasizing about the hidden gold behind the untapped data sources such as social media, click stream, web logs etc then it is time to keep the focus on the main purpose of “Business Intelligence”. All these systems help us to analyze big data or small data but whether they really impact our bottom line is the question that management needs to keep it as the top priority.

Every decision should flow and trickle down from this focus point “Show me the money!!”

Click here to read more about what business intelligence really means and what should be the core focus for management

APEX 5.0.2 : Let’s get patching!

Tim Hall - Mon, 2015-11-02 06:01

APEX 5.0.2 was released just before OOW15. Today is my first day back to work, so I’ve started to patch some stuff. We were already on APEX 5.0.1 across the board, so we didn’t need to do any full installations, just patches.

SO far, so good. No problems in any Dev or Test databases. I expect a pretty quick roll-out across the board.



APEX 5.0.2 : Let’s get patching! was first posted on November 2, 2015 at 1:01 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

MobaXterm 8.3

Tim Hall - Mon, 2015-11-02 05:16

MobaXterm 8.3 has been released.

Downloads and changelog in the usual places.

This is a must for Windows users who use SSH and X Emulation!



MobaXterm 8.3 was first posted on November 2, 2015 at 12:16 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.


Jonathan Lewis - Mon, 2015-11-02 04:27

I had a recent conversation at Oracle OpenWorld 2015 about a locking anomaly in a 3-node RAC system which was causing unexpected deadlocks. Coincidentally, this conversation came about shortly after I had been listening to Martin Widlake talking about using the procedure dbms_stats.set_table_prefs() to adjust the way that Oracle calculates the clustering_factor for indexes. The juxtaposition of these two topics made me realise that the advice I had given in “Cost Based Oracle – Fundamentals” 10 years ago was (probably) incomplete, and needed some verification. The sticking point was RAC.

In my original comments about setting the “table_cached_blocks” preference (as it is now known) I has pointed out that the effect of ASSM (with its bitmap space management blocks) was to introduce a small amount of random scattering as rows were inserted by concurrent sessions and this would adversely affect the clustering_factor of any indexes on the table, so a reasonable default value for the table_cached_blocks parameter would be 16.

I had overlooked the fact that in RAC each instance tries to acquire ownership of its own level 1 (L1) bitmap block in an attempt to minimise the amount of global cache contention.  If each instance uses a different L1 bitmap block to allocate data blocks then (for tables and their partitions) they won’t be using the same data blocks for inserts, and they won’t even have to pass the bitmap blocks between instances when searching for free space. The consequence of this, though, is that if N separate instances are inserting data into a single table there are typically 16 * N different blocks into which sessions could be inserting concurrently, so the “most recent” data could be scattered across 16N blocks, which means the appropriate value table_cached_blocks is 16N.

To demonstrate the effect of RAC and multiple L1 blocks, here’s a little demonstration code from a 12c RAC database with 3 active instances.

create tablespace test_8k_assm_auto
datafile size 67108864
logging online permanent
blocksize 8192
extent management local autoallocate default
nocompress segment space management auto

create table t1 (n1 number, c1 char(1000)) storage (initial 8M next 8M);

The code above simply creates a tablespace using locally managed extents with system allocated extent sizes, then creates a table in that tablespace with a starting requirement of 8MB. Without this specification of initial the first few extents for the table would have been 64KB thanks to the system allocation algorithm, and that would have spoiled the demonstration because the table would have started by allocating a single extent of 64KB, with just one L1 bitmap block; slightly different effects would also have appeared with an extent size of 1MB – with 2 L1 bitmap blocks – which is the second possible extent size for system allocation.

Having created the table I connected one session to each of the three instances and inserted one row, with commit, from each instance. Then I ran a simple SQL statement to show me the file and block numbers of the rows inserted:

        dbms_rowid.rowid_relative_fno(rowid)    file_no,
        dbms_rowid.rowid_block_number(rowid)    block_no,
        count(*)                                rows_in_block
group by
order by

---------- ---------- -------------
        19        518             1
        19        745             1
        19       2157             1

As you can see, each row has gone into a separate block – more significantly, though, those blocks are a long way apart from each other – they are in completely different sets of 16 block – each instance is working with its own L1 block (there are 16 of them to choose from in an 8MB extent), and has formatted 16 blocks associated with that L1 for its own use.

In fact this simple test highlighted an anomaly that I need to investigate further. In my first test, after inserting just 3 rows into the table I found that Oracle had formatted 288 blocks (18 groups of 16) across 2 extents, far more than seems reasonable. The effect looks hugely wasteful, but that’s mainly because I’ve implied that I have a “large” table into which I’ve then inserted very little data – nevertheless something a little odd has happened. In my second test it got worse because Oracle formatted 16 blocks on the first insert,  took that up to 288 blocks on the second insert, then went up to 816 blocks (using a third extent) on the third insert; then in my third test Oracle behaved as I had assumed it ought to, formatting 3 chunks of 16 blocks each in a single extent – but that might have been because I did a truncate rather than a drop and recreate.


Whatever else is going on, the key point of this note is that if you’re trying to get Oracle to give you a better estimate for the clustering_factor in a RAC system then “16 * instance-count” is probably a good starting point for setting the table preference known as table_cached_blocks.

The anomaly of data being scattered extremely widely with more extents being allocated than you might expect is probably a boundary condition that you don’t have to worry about – until I’ve had time to look at it a little more closely.