digging into mrskew internals

I'm spending some time working with Method-Rs trace file analyzer mrskew.
As the requirements are beyond simple analyses (here an example about the uncommon things to do) I'm consulting the documentation and all available information in detail. After many readings I started to take this part of the documentation serious:

Wherever mrskew requires an expression (such as in --group and --where option arguments), you may use any valid Perl expression consisting of:

wrong permission on shm kills JAVA_JIT

We found a lot of trace files from several DBs on one of our DB Servers.
They look like:

*********_ora_26444.trc or *********_m000_5598.trc
*** 2017-09-08 15:11:29.181
*** SESSION ID:(632.5995) 2017-09-08 15:11:29.181
*** CLIENT ID:(SYSADMIN) 2017-09-08 15:11:29.181
*** SERVICE NAME:(****_****) 2017-09-08 15:11:29.181
*** MODULE NAME:(*::***:******.****.***.****.******) 2017-09-08 15:11:29.181
*** ACTION NAME:(/) 2017-09-08 15:11:29.181

Map_Length = 4096
Map_Protection = 7
Flags = 1
File_Offset = (more...)

SecureFile LOB – managing free blocks

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert SecureFile LOB - the 1st update and SecureFile LOB - more inserts.

It starts with some inserts:
insert into berx.TEST_BLOBS
select 4, comments, file_content
from berx.source_blobs
where id=4;


insert into berx.TEST_BLOBS
select 5, comments, file_content
from berx.source_blobs
where id=1;

insert into berx.TEST_BLOBS
select 6, comments, file_content
from berx. (more...)

SecureFile LOB – more inserts

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert and SecureFile LOB - the 1st update

The next insert is a small LOB with 2 bytes again:
insert into berx.TEST_BLOBS
select 2, comments, file_content
from berx.source_blobs
where id=2;
--2 byte
The table row points to a new block in the LOB segment:
LOBID = 00000001000099BF67D1

SecureFile LOB – the 1st update

This blog post continues with all the objects and settings from SecureFile LOB - the empty table and SecureFile LOB - the 1st insert

the statement is quite easy:
update berx.TEST_BLOBS
set (comments, file_content) =
(select comments, file_content
from berx.source_blobs
where id=4)

The new CLOB is about 11.1 kb in size - so it will require more than 1 block.

Fortunately the number of extents did not change.

New information needs to be gathered (more...)

SecureFile LOB – move online in 12.2

Today we had a "new features in 12.2" workshop. Thank you Mathias Zarick for this great event.
One of the new features was Online Table Move. But the doc was quite unspecific if this also includes LOBs:
Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
Data maintenance on nonpartitioned tables does not (more...)

SecureFile LOB – the 1st insert

This blog post continues with all the objects and settings from SecureFile LOB - the empty table.

The insert of one row with a small (1byte) LOB increases the complexity.
berx.source_blobs contains some BLOBs of different size.
insert into berx.TEST_BLOBS
select 1, comments, file_content
from berx.source_blobs
where id=1; --1 byte


we have a new extent now

col owner for A10  
col segment_name for A30
select owner, segment_name, file_id, block_id, blocks
from (more...)

SecureFile LOB – spfile parameters

Here is a list of all the spfile parameters I found (and guess are) related to SecureFile LOBs.

This Post will be updated with additional information whenever I collect them.


Default value




DBFS Link allows implicit fetch on modify - only on SecureFiles



Enable securefile flashback optimization



undocumented parameter for internal use only


0 12. (more...)

SecureFile LOB – the empty table

I have to dig into structures of SecureFile LOBs right now. Unfortunately I do not find a lot of resources available, so I have to investigate at my own.
This post is to document my findings. There is a lot of guesswork and interpretation here, so be careful if you use or refer to any information here.

The whole testcase is on BundlePatch EE, Linux, 2 node RAC.

I start (more...)

The Limits of Data Redaction

Data Security is becoming more and more important nowadays.
In fact it was always important, just as the expected problems increase (by count or value) management seems to be more aware now. Due to many discussions I started to have a look at DBMS_REDACT - which is an implementation to show only those data to users they are allowed to use.
One of my first places to go was Tim Halls Data Redaction (DBMS_REDACT) in (more...)

The Slow Tires

Once there was a man with a car. On this car he had 4 tires.
As the car is a modern one it has a nice board computer which collects many measurements. One is the rotation per minute of the tires.
One day it took more time for the man to get home than usually. So he decided to check his car's computer for any values which could lead to that delay.
After some minutes (more...)

Hints, up and down

Last week was AOUG conference 2017. There I attended Lothar Flatz' Any Hint, anywhere. There Lothar mentioned it's not required to have hints in the first / topmost SELECT. Even the sentence itself was clear for me, the consequences and possibilities were not at that time.
During the presentation and some discussion with Lothar afterwards, some possibilities were shown.


drop table T1;
create table T1 as
select rownum as rn, 'A' as const (more...)

Method R Profiler 7.0 available

I'm seldom doing product announcements on my blog. So there must be a reason why I do not follow this rule.

Method-R recently released a new version of it's powerful SQL-Trace profiler.
Of course there are many improvements on the profiler engine itself, but as the prodict was very stable already they will only help in rare edge cases, most people would not benefit dramatically.

But there is a new featuere which brings a lot (more...)

search for the missing ORA-04068

Oracle Active Dataguard is a great software for offloading read only activities from the primary nodes to nodes which is not utilized anyhow (at least during non-disaster times).
A very good example for doing so is regular extraction of data for processing in a data warehouse. That's also the case which led to this post.

The setting is just slightly more complex: both, primary and standby site are RACs - of course when you plan (more...)

Can I flush the shared pool of all RAC Instances in an Active DataGuard?

That's a good question.

And the answer is simple: Yes, but NO!

Well, ok; that's not very helpful. But whenever is anything simple in modern IT?

So I'll show why Yes, it's possible:

first as SYS create a dummy user without special permissions and a function to drop the shared pool:

create user berx identified by xxx;
grant connect to berx;
grant select on v_$instance to berx;

create or replace function sys.flush_shared_pool_bx (more...)

force connections to use SERVICE_NAME

During the setup of a project I had a small observation: The Oracle Database is running on a 4 node RAC, but we (DBAs) wanted to provide only 2 instances to the application team.

So we created a dedicated service with Preferred instances: inst1,inst2 and Available instances: inst3.inst4

But after some minutes I saw sessions running on all 4 instances. Those sessions used the Service SYS$USERS - so they managed to "guess" the SID and created a connetionstring (more...)

interconnect fragmentation kills the cluster

On a particular Oracle 2 node cluster (12.1) we faced random instances failing. Servicerequests at Oracle were open with limited result, as it was quite random and we could not link it to any trigger.
As it looked somehow like a communication problem between the 2 nodes, network team has checked the switches involved - without any outcome.
Even crashing instances were a problem already, it get worse one day when one node rebooted (more...)

cost vs. gets

Last week I hit an interesting performance issue:
A Table (T) has 2 index (of interest). One (IX_1) is only on column S, the other (IX_2) on (C, S).
The optimizer calculates the plan with IX_1 more expensive than the plan with IX_2 - as this should be more selective.
But the gets for the plan with IX_1 were less than those the optimizer preferred.

Here the information about the statement, index and plans.
As (more...)

no peek in PL/SQL

Connor McDonald wrote a blog about differences between bind peeking and SYS_CONTEXT in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values (feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.

In the following discussion he stated (more/less
I should not have this problem at all, as (more...)

OTN Appreciation Day : ONLINE

This post is inspired by Tim Hall.

Thinking about "what is my single favourite feature of Oracle", when all the products of Oracle (or at least all I know about) can be covered sounds stupid or impossible. There are far to many features which could be interesting, worth or made my life easier over my years in IT.
So I decided to pick something more generic.

The "feature" I like most is ONLINE

With (more...)