Auto SGA Management Impacts the Default Value of DB_FILE_MULTIBLOCK_READ_COUNT

The database engine determines the maximum disk I/O size used during multiblock reads (for example, full table scans or index fast full scans) by multiplying the values of the db_block_size and db_file_multiblock_read_count initialization parameters. The db_file_multiblock_read_count initialization parameter can be set explicitly, or, as of version 10.2, it’s also possible to instruct the database engine to automatically configure it. For the latter, simply don’t set it.

About the value which is automatically determined by (more...)

latch: cache buffers chains and rollback

I had an interesting encounter with latch: cbc contention early this week. During my oncall I received page for Load of 206.81 exceeded threshold of 150. After I logged into server , the server load average was continously increasing and all the top PIDs were of oracle processes. After logging into database, I saw multiple sessions waiting on latch: cache buffer chains wait event

load average: 258.52, 244.27, 226.15

select username,sql_id,event,count(*)  (more...)

ORDIM – Oracle Multimedia – Invalid after upgrade

ORDIM – The Oracle Multimedia component might be in INVALID state after the database upgrade to 12c. While I was testing the upgrade on test server, received below errors after the completion.

Serial   Phase #:69 Files: 1 Use of uninitialized value $gsRTInclusion in concatenation (.) or string at line 1500.
    Time: 49s

Phases [0-73]         End Time:[2015_08_08 08:06:45]

Grand Total Time: 4848s



Foreign Keys and Library Cache Locks

In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way.

Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). Schema used for the tests (more...)

Parallel Projection

A recent case at a client reminded me of something that isn't really new but not so well known - Oracle by default performs evaluation at the latest possible point in the execution plan.So if you happen to have expressions in the projection of a simple SQL statement that runs parallel it might be counter-intuitive that by default Oracle won't evaluate the projection in the Parallel Slaves but in the Query Coordinator - even (more...)

SQL Plan Management Choices

My thoughts on SQL plan management decision points:


SQL Patches are also available and not covered in the above flowchart.


Note to self because it’s just one of those date/timezone-related topics which just doesn’t seem to stick…

Epoch/Unix time – See

Unix time (also known as POSIX time or erroneously as Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,

Firstly when converting from Oracle date or timestamp – we need to (more...)

Temp Table Transformation Cardinality Estimates – 2

Continuing from the previous part - which was about the Temp Table Transformation and join cardinality estimates - using the same simple table setup here is a slight variation of the previously used query to demonstrate the potential impact on single table cardinality estimates:

explain plan for
cte as (
select /* inline */ id from t1 t
where 1 = 1
select /*+
no_merge(a) no_merge(b)
*/ * from cte a, cte (more...)

Temp Table Transformation Cardinality Estimates – 1

Having published recently two notes about the Temp Table Transformation highlighting the heuristics based decision and other weaknesses, for example regarding the projection of columns, it's time to publish some more notes about it.The transformation can also have significant impact on cardinality estimates, both join and single table cardinality.Looking at the difference in the join cardinality estimates of following simple example:

create table t1
rownum as id
, mod(rownum, 10) (more...)

SQL Plan Baseline Manual Evolution

I’ve never really spent time on evolution because most of the time I use baselines for emergency sql plan management and not with the added value of controlled evolution with which the feature was conceived.

But some observations on sql plan baseline evolution originating from the questions:

  • What does evolution actually do?
  • What information does it use?
  • What happens with DML?

Starting point – one baselined plan

1. FTS plan in memory from SQL which (more...)

Heuristic Temp Table Transformation – 2

Heuristic Temp Table Transformation - 2 Some time ago I've demonstrated the non-cost based decision for applying the temp table transformation when using CTEs (Common Table/Subquery Expressions). In this note I want to highlight another aspect of this behaviour.Consider the following data creating a table with delibrately wide columns:

create table a
rownum as id
, rownum as id2
, rpad('x', 4000) as large_vc1
, rpad('x', 4000) as large_vc2
, rpad('x', 4000) (more...)

Strategies for Minimising SQL Execution Plan Instability

Execution Plan Instability – What is the problem?

The Oracle Optimizer is a complex piece of software and with every release it becomes more complex.


In the beginning, the Optimizer was rule-based.

The Optimizer had a ranked list of heuristics used to optimize a query, picking the lowest ranked rule available.

This rule-based mode, whilst still in use with some internal Oracle dictionary queries, has been unsupported since version 10.1.

This means that (more...)

Enabling Edition Based Redefinition On A Schema With Object-Relational Tables

This is just a heads-up for those thinking about using Edition Based Redefinition (EBR) and enabling it on an existing schema with objects. Although EBR isn't exactly a new feature its current adoption level is probably not that high (which probably changes in future as Oracle E-Business Suite uses EBR now as part of their default upgrade procedure as far as I understood).I was recently contacted by someone who enabled EBR on an existing (more...)

Function-Based Indexes And CURSOR_SHARING = FORCE

In general it is known that Function-Based Indexes (FBIs) can no longer be used by the optimizer if the expression contains literals and CURSOR_SHARING = FORCE / SIMILAR (deprecated) turns those literals into bind variables. Jonathan Lewis described the issue quite a while ago here in detail.In a recent OTN thread this issue was raised again, but to my surprise when I played around with a test case that mimicked the OP's problem query (more...)

ORA-00353: log corruption near block change time –

Recently I came across “ORA-00353: log corruption near block change time” error.

Errors in file /oracle/app/diag/rdbms/test/test/trace/test_ms00_54284.trc  (incident=1490189):
ORA-00353: log corruption near block  change  time
ORA-00353: log corruption near block 4631374 change 263470473806052 time 04/16/2015 06:53:16
ORA-00312: online log 43 thread 3: '+REDO02/test/onlinelog/group_43.258.875922665'
ORA-00312: online log 43 thread 3: '+REDO01/test/onlinelog/group_43.258.875922633'
Incident details in: /oracle/app/diag/rdbms/test/test/incident/incdir_1490189/test_ms00_54284_i1490189.trc
Thu Apr 16 08:14:42 2015
Sweep [inc][1490188]: completed
Thu Apr 16 08:14:55 2015
LOGMINER: WARNING: error  (more...)

Combined ACCESS And FILTER Predicates – Excessive Throw-Away

Catchy title... Let's assume the following data setup:

create table t1
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by
level < = 1e4

create table t2
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by
level < = 1e4

create table t3
rownum as id
, 1 as id2
, rpad('x', 100) as filler
connect by

ORA-01628: max # extents (32765) reached for rollback segment _SYSSMUxxx

Recently I came across “ORA-01628: max # extents (32765)” error. The database had resumable_timeout parameter set I received page for a session in resumable state. Looking into the alert log showed

statement in resumable session ‘User USER_E3(375), Session 9028, Instance 1′ was suspended due to
ORA-01628: max # extents (32765) reached for rollback segment _SYSSMU375_247595031$
Wed Apr 08 02:41:42 2015

Looking at the sid# 9028 details, INSERT sql was running and the session was on (more...)

ORA-17629 ORA-20079

Yesterday I received page for ” ORA-17629: Cannot connect to the remote database server”  reported in Primary Database alert log.

Fri Apr 10 06:47:41 2015
Errors in file /oracle/app/diag/rdbms/testdb/testdb/trace/testdb_ora_161991.trc:
ORA-17629: Cannot connect to the remote database server
Errors in file /oracle/app/diag/rdbms/testdb/testdb/trace/testdb_ora_161991.trc:
ORA-17629: Cannot connect to the remote database server
ORA-17629: Cannot connect to the remote database server
Fri Apr 10 06:48:22 2015

The error trace file shows

*** 2015-04-10 (more...)


Just been tearing my hair out on some unexpected SQL behaviour in SQL Developer.
Eventually I managed to get to the root cause of the problem as illustrated below.


SQL> var ps number
SQL> exec :ps := 4001644945;

PL/SQL procedure successfully completed.

SQL> select :ps from dual;



SQL Developer v3.2 and v4.0 via “Run As Script(F5)”:

var ps number
exec :ps := 4001644945;
select :ps from dual;

anonymous  (more...)

SQL Developer: Viewing Trace Files

Just a quick plug for looking at raw sql trace files via SQL Developer.

There is a nice Tree View:

Which can be expanded:

Also summary view of statistics, filterable:

And a list view, filterable and orderable:


Some sort of right click summary for binds/waits might be a nice addition.