Who's online

There are currently 0 users and 49 guests online.

Recent comments


Oakies Blog Aggregator

Exciting Oracle Developments

The summer has brought us a new version of Oracle’s flagship database and the Introduction of Oracle Mobile Application Framework. Both, truly big news.

Oracle 12c was released in July! This is a full release and includes many new features including:

  • In-Memory Database
  • Big Data SQL
  • JSON Support
  • RESTful Data Services
  • Improved Index Compression
  • Zone Maps
  • Attribute Clustering
  • Full Database Caching
  • Enhanced Multi-Tenant Features
  • Rapid Home Provisioning
  • Approximate Count Distinct

For complete documentation; see Oracle’s web site:

In July the Oracle Mobile Framework (MAF 2.0) replaced and enhanced what was formerly known as Oracle ADF Mobile Framework and Oracle JDeveloper (12.3.1). Oracle did more than remove ADF from the name; Oracle Mobile Framework’s (MAF’s) enhancements include many bug fixes and new features like:

  • Java SE 8 support
  • Improved Maven interaction (many bugs fixed)
  • Critical Patches Downloadable from Oracle Update Center
  • REST services support
  • HTTP REST Analyzer
  • WADL Viewer Improvements
  • Web Socket Support
  • New Data Visualization (DVT) Charting improvements: Thematic Maps, New Gauges, New Deck Component, Table First Page Template, Client-Side CSS, Recursive Bounded Task Flows (BTFs), Debugger for Groovy, Groovy UI hints, support for Excel 2013 and Windows 8, New Desktop Components & Properties

For more information see the Oracle Web Sites: and”>

Who deserves the title “Expert” or (even worse) “Guru”?

I wanted to expand on a thread in a LinkedIn group I’m part of, where one of the members wrote “It’s funny when 2 experts are arguing about who is better”, using Tom Kyte and Jonathan Lewis as examples of people they say are “Experts”.

Disclaimer: I have not spoken to Jonathan or Tom in regard to their viewpoints on this subject, so this shouldn’t be taken as them saying any of this, just my interpretation.

My perspective on that is I think you will rarely find two people who deserve the title of “Expert” will argue about “who is better”.  They would discuss the technical merits of a point instead.  In fact, most people I know that are generally accepted as “experts” really don’t believe they are, and most of them REALLY don’t like the term “Guru”.

I remember way back when at one of the early Hotsos conferences I was on a panel with Tom Kyte and someone asked a question on whether auto-extension for datafiles should be turned on or not (note I’m by no means saying I’m up there at Tom’s level!).  Tom and I had different perspectives because of the different situations we were in.  He was looking at the issue from the perspective of managing the AskTom site.  He had little time to do that, so switching on autoextend was a darn sight easier than having the system run out of space and generate errors.  My perspective was more from the viewpoint of sites that DID have DBA’s with time to manage the databases properly, where they could be warned by Enterprise Manager alerts that they were going to run out of space in time to address the issue.

In fact, both of us were right in our individual situations (the good old “It depends” answer!)  I still cherish the feedback we got from one attendee to the effect that it was great to see people respectfully disagreeing and backing up their perspectives with technical reasons, rather than just proclaiming they knew the answer and their answer was right.

Coming back to the original point of why people with deep technical knowledge like Jonathan and Tom don’t like the terms “Expert” or “Guru”, I think it’s because most of them believe (and rightly so) they are still on a life-long journey of learning.  What they ARE really good at is not just having that deep technical knowledge, but also in having the ability to pass that sort of knowledge on to us lesser mortals in terms that WE can understand and then apply in future.

The one place I can sort of understand those terms of “Expert” and “Guru” being applied is from a marketing perspective (well, at least for the term “Expert” – “Guru” sounds like you’re sitting on a mountain top somewhere to me! :) )  It can be much easier to make yourself marketable if you’re truly recognized as an expert.  But let’s face it – there are very few REAL experts out there.  The rest of us are on that journey towards becoming one.

That’s my two banana skins worth.  Feel free to add your own comments here as well!


Multitenant : PDB CONTAINERS Clause

I forgot to mention, I put another multitenant article live at the weekend.

I’m not sure I will ever use it, but it’s good to know it’s there.

I was originally working on an article on a completely different multitenant feature, but the examples I was using highlighted a bug, which kind-of scuppered that article. I’ve raised an SR and I’m waiting on the acknowledgement and possible fix. I’ll hold that article back until the fix is in place.



Multitenant : PDB CONTAINERS Clause was first posted on August 20, 2014 at 9:14 am.
©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.

New Tips and Techniques page

In case you haven’t noticed it – and chances are you haven’t noticed because I just created it! :) – I’ve added a new page called “Tips and Techniques” to the website. It’s where I keep a random collection of odd items I’ve come across that might be of interest yet don’t deserve a full blog post. It’s sort of like a FAQ list, but not necessarily with the “F”. :)

You can reach it either via the link I’ve just given, or from the menu on the top of each page. I’ve only created a couple of entries so far, but will slowly add to that page as time goes on. Hope you find the tips and techniques of some use!

LOB Length

It’s funny how you can make little savings in work all over the place in Oracle if you’re prepared to look a little closely at what’s going on. Here’s a quirky little example with LOBs and function calls that might just have some greater relevance in other situations. Here’s a little data set, and two queries that I might run against it:

create table tbl(
	c1      clob
lob (c1) store as c_lob(
	disable storage in row
	nocache nologging

	for i in 1..128 loop
		insert into tbl values(rpad('x',4000));
	end loop;


-- collect stats, prime dictionary cache and library cache
-- run both queries twice and check stats on second run

	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
		/*+ no_merge */
		dbms_lob.getlength(c1) len
	len > 3960

	round(sum(ceil(len/8100)) * 8/1024,0)    used_mb
		rownum rn, dbms_lob.getlength(c1) len
	len > 3960

The question that you might ask yourselves when you see these queries is: will they do similar amounts of work. Of course, I wouldn’t be asking the question if the answer were yes. Despite the no_merge() hint, which you might think would have the same effect as the rownum approach, Oracle seems to execute the call to dbms_lob.getlength() twice for each row in the first query, but only once per row for the second query. Here are the stats (from autotrace) on the second run of the two queries when autotrace is enabled:

Statistics (for no_merge)
         40  recursive calls
          0  db block gets
        271  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Statistics (for rownum)
          0  recursive calls
          0  db block gets
        131  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, the consistent gets for the no_merge() approach is roughly double that for the rownum approach – and since we have 128 rows/LOBs in the table that looks suspiciously like 2 gets vs. 1 get per LOB depending on the approach – which suggests two calls to the function. This is further corroborated by the execution plans, and especially by the predicate sections (how often have I said “always check the predicates”) which show that the predicate has been pushed inside the view that’s been hinted to be non-mergeable, but it hasn’t been pushed inside the view that uses the rownum instantion trick:

Execution Plan for no_merge()
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT    |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE     |      |     1 |    13 |            |          |
|   2 |   VIEW              |      |     6 |    78 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TBL  |     6 |   522 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):
   3 - filter("DBMS_LOB"."GETLENGTH"("C1")>3960)

Execution Plan for rownum
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT     |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE      |      |     1 |    13 |            |          |
|*  2 |   VIEW               |      |   128 |  1664 |     2   (0)| 00:00:01 |
|   3 |    COUNT             |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| TBL  |   128 | 11136 |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):
   2 - filter("LEN">3960)

My first thought on seeing this difference was to apply the /*+ no_push_pred */ hint to block predicate pushing – but then I remembered that the hint is about join predicate pushing and this is a simple filter push. A quick search of the hidden parameters, though, revealed this:

_optimizer_filter_pushdown : enable/disable filter predicate pushdown

Setting this parameter to false – either through a call to ‘alter session’ or through an /*+ opt_param( opt_param(‘_optimizer_filter_pushdown’ , ‘false’) */ hint – allowed the no_merge approach to produce the same plan and resource usage as the rownum approach. Of course, for a production system, I’d probably use the rownum approach rather than mess around with hidden parameters.


I don’t know why the code with the no_merge() approach reported 40 recursive calls (on its first execution with autotrace). A couple of variations on the experiment suggested that it had something to do with the number of rows (or consequential buffer visits) that survived the predicate call – for a sufficiently small number of rows the recursive call count happened to drop to zero; but the phenomenon needs further investigation.

Zone Map Zone ID's

Just a quick follow up to my previous post on how Zone ID's are calculated.

Let's take the following example:

SQL> select rid, sys_op_zone_id(rid) zone_id
2 from
3 (
4 select chartorowid('AAAS5KAAHAAABYDAAA') rid
5 from dual
6 );

------------------ ------------

Recalling that extended ROWID has the following format (a nice picture from Oracle Documentation):

In the binary format that would correspond to:

  • Data Object Number -- 32 bits
  • Relative File Number -- 10 bits
  • Block Number -- 22 bits
  • Row Number -- 16 bits

We know that Row Number is irrelevant for the Zone Maps because they deal with block ranges. With that in mind a simple conversion to a (base 10) number would be:

(Block Number) + (Relative File Number) * 2 ^ 22 + (Data Object Number) * 2 ^ 32

Applying the above formula to our ROWID:

SQL> select
2 dbms_rowid.rowid_block_number(rid) +
3 dbms_rowid.rowid_relative_fno(rid) * power(2,22) +
4 dbms_rowid.rowid_object(rid) * power(2,32) base_10
5 from
6 (
7 select chartorowid('AAAS5KAAHAAABYDAAA') rid
8 from dual
9 );


What's left after that is to slice the number into Zone Map chunk size (2^10 by default, thanks to Timur for pointing that out in the comments sections for the previous post):

SQL> select
2 trunc((
3 dbms_rowid.rowid_block_number(rid) +
4 dbms_rowid.rowid_relative_fno(rid) * power(2,22) +
5 dbms_rowid.rowid_object(rid) * power(2,32)
6 )/power(2,10)) zone_id,
7 sys_op_zone_id(rid) sys_zone_id
8 from
9 (
10 select chartorowid('AAAS5KAAHAAABYDAAA') rid
11 from dual
12 );

------------ ------------
324580438021 324580438021

That's all there is to it!

Just Don’t Call It Science!

I’ve had two rather frustrating conversations recently with people who have claimed “science” and “proof” where it is totally inappropriate. One was regarding a 3000 year old religious text. The other was a bunch of medical-related blog posts. In both cases, the people were making very valid points and thought they were backing up their stance with “scientific proof”. The problem was, by inappropriately playing the science card, they made themselves and their beliefs sound stupid. Like I said, in both cases they were making very valid points, but their supporting arguments were just plain dumb!

I really do feel most people out there do not have a clue what science and scientific methods actually are. Just to bring this closer to home, I would just like to make this point. Please read this next line and the following explanation before you feel the need to comment…

I don’t think there is an Oracle blogger that I follow that is writing blog posts worthy of being called “science”.

That comment is not meant to shock and offend. I’m not trying to sound elitist, because I certainly rate much lower on the science scale than many other bloggers. The point of that comment is to put things into perspective. The scientific approach to gathering, validating and presenting information is a very formal affair. If you’ve experienced a research-based (not taught) Masters or PhD you will have just scratched the surface of what I’m talking about. If you’ve not experienced science to at least that level, I’m not sure you are really in a position to judge the extent of what people mean when they say “scientific proof”.

At this point I think the audience response will be split amongst these types of reaction.

  • People who saw the word “religious” in the second sentence, instantly jumped to a conclusion about what this post is about and either stopped reading or started to prepare their counter argument.
  • People who have a “favourite blogger” who *they believe* to have a super-scientific approach to Oracle and are desperate to tell me so.
  • People who follow “popular science” and believe the watered down crap they present in those articles and documentaries resembles real science and therefore think they know about science and think I’m talking crap.
  • People who just don’t give a damn and will happily call what they read in blog posts and wikipedia science, regardless of source, scientific approach etc.
  • People who understand what I’m talking about and realise we all just producing useful and informative content, but are not living up to the rigorous standards that would be required to call our tests and content “science” or “scientific proof”.

I think everyone is entitled to their own opinion, educated or not, but what I find it really hard to tolerate is when people start playing the science card where it is not appropriate. Do that and I’m gonna call bullshit every time!


Tim… (Not a “Computer Scientist”, “Data Scientist”, “Software Engineer”, “Data Architect” or any of those other bullshit titles that offend the professions they reference)


Just Don’t Call It Science! was first posted on August 19, 2014 at 8:58 am.
©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.

Zone Maps

Zone Maps is a new feature that got officially introduced in so I've decided to take a closer look.

From the Oracle Documentation:

For full table access, zone maps allow I/O pruning of data based on the physical location of the data on disk, acting like an anti-index.

Let's start by creating a test table:

SQL> create table t pctfree 95 clustering by linear order (n) as
2 select level n, rpad('x', 200, 'x') v
3 from dual
4 connect by level <= 5000;

Table created

I've used a high pctfree setting to make sure the table gets spread out on disk -- each row will occupy it's own block:

SQL> select count(*) num_rows,
2 count(distinct dbms_rowid.rowid_block_number(rowid)) num_blocks
3 from t;

---------- ----------
5000 5000

Zone Maps do not require attribute clustering, however, I did use clustering in this particular case to make sure that value ranges for column N do not overlap when physically stored on disk to make it easier to see what's happening when a Zone Map is created.

Now let's create a Zone Map:

SQL> create materialized zonemap zm$t on t (n);


The first thing to notice is we now got additional table in our schema with the same name as a Zone Map:

SQL> select * from zm$t order by zone_id$;

------------ ---------- ---------- ----------- ----------- ----------
324421046272 1 748 0 0 748
324421046273 749 1756 0 0 1008
324421046274 1757 2764 0 0 1008
324421046275 2765 3772 0 0 1008
324421046276 3773 4780 0 0 1008
324421046277 4781 5000 0 0 220

6 rows selected

As you can see we've got six zones defined with most of them covering a range of about thousand rows with the exception of the first and the last ones. I can now map each ZONE_ID$ to it's respective block range on disk:

SQL> select zone_id$, min_block_id, max_block_id, zone_rows$
2 from (
3 select zm$t.zone_id$,
4 min(dbms_rowid.rowid_block_number(t.rowid)) min_block_id,
5 max(dbms_rowid.rowid_block_number(t.rowid)) max_block_id,
6 max(zone_rows$) zone_rows$
7 from t, zm$t
8 where t.n between zm$t.min_1_n and zm$t.max_1_n
9 group by zm$t.zone_id$
10 ) order by zone_id$;

------------ ------------ ------------ ----------
324421046272 179 1023 748
324421046273 1026 2047 1008
324421046274 2050 3071 1008
324421046275 3074 4095 1008
324421046276 4098 5119 1008
324421046277 5122 5343 220

6 rows selected

Based on a couple more tests I've done the algorithm appears to work itself out until the fist block number in a segment which divides evenly by 1024 after which all subsequent blocks get mapped to 8MB regions. The last map has 221 blocks because that's where the end of the last table block happens to be.

Zone Maps seems to be completely independent from the actual extents on disk. In my tests I was able to get multiple Zone Maps pointing at the same extent and in the case above we have multiple extents covered by the same Zone Map. In all cases zones were 8MB in size (more on that a little bit later). By the way if you're wondering why do we have 1008 rows and a little bit strange starting block_ids (i.e. 1026 instead of 1024 and so on) remember that the fist couple blocks are used for first level bmb and do not store actual table data.

So how is this all pieced together?


SYS_OP_ZONE_ID function computes ZONE_ID$ for a given ROWID value. The calculation appears to be a straightforward math based on the ROWID value and assumes 8MB Zone Map chunks -- this explains why Zone Maps are created adjacent to 8MB boundaries since ZONE_ID$ values in the Zone Map are computed using the same function:

SQL> select rowid, SYS_OP_ZONE_ID(rowid)
2 from t
3 where rownum=1;

------------------ ---------------------
AAAS4kAAFAAAACzAAA 324421046272

Essentially if we imagine the entire possible database space divided into 8MB regions this function would return into which region a particular ROWID value would belong.


Let's look at the explain plan for the following query:

SQL> select * from t where n=1;

Execution Plan
Plan hash value: 2931408918

| Id | Operation | Name | Rows | Bytes | Cost (%C
| 0 | SELECT STATEMENT | | 1 | 205 | 1380

Predicate Information (identified by operation id):

1 - storage("N"=1)
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_1_N" > :1 OR zm."MAX_1_N"
zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),1,1)<3 AND "N"=1)

We can see that SYS_ZMAP_FILTER appears to be the function involved in figuring out which Zone Maps needs to be accessed in order to execute the query. The condition inside the query (zm."MIN_1_N" > :1 OR zm."MAX_1_N" < :2) will be used to eliminate Zone Maps which do not have a value we're looking for and is dynamically constructed based on the predicate(-s) we have in the query. From here ZONE_ID$ can be mapped back to ROWID ranges (a reverse operation relative to SYS_OP_ZONE_ID) in order to scan only required portions of the data on disk.

This looks to be a very exciting feature and I can't help but think that it's a little bit disappointing that it's limited to Exadata storage only as it could have been very useful on other systems due to lack of storage indexes support.

In Search of Plan Stability (Part 1) - August 27 webinar

Register by clicking here.

Hope you'll join me!

Why am I seeing “Result: Failed. Insufficient resource available” messages?

This is an interesting point that came up recently, where someone had a DBaaS environment running on an Exadata system for a couple of months, with no issues.  It has recently started to return errors on the Database Cloud Self Service portal in the “Placement Algorithm” region:

Constraint : Number Of Databases
Current Resource Usage: 18
Total Resource Available: 10
Maximum Limit Defined: 10
Requested Value: 1
Total Resource that can be used: 10
Result: Failed. Insufficient resource available.

But if I grep for PMON processes,  the number of databases running on the machine is 8.  Why would I see such an error?

The reason here is fairly simple – there are a couple of databases that are down, and if we include those, the number of databases on the machine has reached the maximum limit defined. But the explanation needs a bit more detail.

So why do we include databases that are down in this count? Well, even in a non-cloud environment, shutting down a database doesn’t release the storage used by that database (thankfully!) A mere shutdown of the database cannot be assumed to reduce the quota count, as the database may be restarted at any time, either manually by the database administrator using Enterprise Manager Cloud Control, EM Express, or SQL*Plus, or automatically, using tools such as cron or init.d(rc2.d) and the machine resources will again be used. Over-allocation in quota is generally used to solve such requirements where not all databases will be up and running all the time.

The next question that comes to mind is whether this algorithm is based only on the number of databases, or machine resources such as CPU and memory when the database is down? At a high level, the answer is the algorithm is controlled by 3 factors:

  1. The number of RDBMS instances*: this is the number of instance targets on a specific host. This data is fetched from the Enterprise Manager repository, which is why databases that are down are counted. So if you really want to ensure a database that is shutdown does not count towards the total number of databases, then remove the database target from being managed by Enterprise Manager. :)
  2. Memory – there are two flavours here:
    • Memory usage based on the host – calculated as the average memory utilization on the host over the last 7 days
    • Memory usage based on the databases – calculated as the total sum of memory allocated for the databases on the host
  3. CPU – the average CPU utilization over the lat 7 days on the host

Hopefully this explains why we call the overall theme of database placement as ‘Optimistic’. :)

*You’ll notice I used the term “RDBMS instances” here, NOT “database instance”. There’s a reason for that. It’s one of my pet peeves to see the term “database instance”. In Oracle terms, a database is a set of physical files – the datafiles, redo log files and control files (but not the initialization parameter file, whichever variety – SPFile or the old init.ora text file – you use). An instance is the set of background processes and memory structures in the SGA. While there is most often a one to one mapping between a database and an instance (the exception of course being a Real Application Clusters environment where multiple instances share a single database), there really is no overlap between the term “database” and the term “instance”. Yes, I know our products use the term all the time, and so does our documentation, but to my pedantic way of thinking, they’re WRONG! :)