Extent Allocation

Some references on extents dealing with extent trimming, initial extent for parallel load, tablespace fragmentation, etc…


Parallel Load: Uniform or AutoAllocate extents?

Tom Kyte covers this problem in great details in his post Loading and Extents

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to or Later (Doc ID (more...)

Oracle Wait Events Investigated with Extended Stack Profiling and Flame Graphs

Topic: this post is about investigating Oracle wait events using stack profiles and flame graphs extended with OS-process state and Oracle wait event details.

Context: The case of the DB Time > CPU Time + Wait Time

Oracle instrumentation provides wait event and CPU time accounting, a powerful and readily accessible data source for performance troubleshooting. An Oracle session at a given point in time is either on CPU, for example when processing data from cache, (more...)

Oracle Week 2015: Oracle Database Advanced Querying Presentation

Oracle week is one of the biggest Oracle related events in Israel which has going on yearly for the past 22 years (!).
It’s a 5 days long event with about 2000 participants in total over 80 different day-long seminars.
The seminars are around 8.5 hours (9am to 4:30pm) and are basically a day long course on various Oracle technologies which is nothing like the 45-90 minutes sessions I’m used to in the (more...)

Oracle 12c in-memory option and IO

This article is about the Oracle 12c in-memory option, and specifically looks at how the background worker processes do IO to populate the in-memory column store.

Hardware: Apple Macbook with VMWare Fusion 7.1.3.
Operating system: Oracle Linux 6.7, kernel: 3.8.13-118.el6uek.x86_64.
Database version: Oracle
Patch: opatch lspatches
19392604;OCW PATCH SET UPDATE : (19392604)
19303936;Database Patch Set Update : 12.1. (more...)

TABLE ACCESS INMEMORY FULL – but there may be more

While preparing to teach a class this week I have had the time to look into the In Memory (cost) option a bit closer. I noticed a few interesting things along the way and would like to share one of these here.


One of the questions I was asking myself was:

“What happens if I scan segments that are within the IM area, and some are not?”

I was primarily thinking of joins in (more...)

Pragma UDF – Some Current Limitations

There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions.

In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.

In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds (more...)

Little things worth knowing: automatic generation of extended statistics in 12c Part II

In my last post I demonstrated an example how to use a PL/SQL package to monitor a workload in order to identify correlated columns. Such columns are good candidates for the creation of extended statistics since the optimiser does not assume that information stored in different columns may be related. Before starting my tests I invoked DBMS_STATS.SEED_COL_USAGE, which in turn increased Oracle’s attention level trying to find correlated columns. Eventually these have been identified (more...)

OOW’15 Session Slides – Oracle Alta UI Patterns for Enterprise Applications and Responsive UI Support

You can view slides from recent OOW'15 session (Oracle Alta UI Patterns for Enterprise Applications and Responsive UI Support [UGF2717]) on SlideShare:

I was explaining how to implement responsive layout and fast performance ADF 12c applications with Alta UI. New ADF 12.2.1 features were discussed. Session went pretty well, all demos were working without issues. I was happy with the feedback, for example a tweet from Grant Ronald:

Here you can (more...)

Pragma UDF – Speeding Up your PL/SQL Functions Called From SQL

A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.

We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.


Linux Kernel Stack Profiling and Flame Graphs Applied to Oracle Investigations

Topic: This blog post is about kernel stack profiling and visualization with flame graphs with examples.

Stack profiling and flame graphs are very useful techniques and tools for troubleshooting and investigating workloads at the OS-level and understand which code path take most of the execution time. You can find extensive material and examples o flame graphs in Brendan Gregg's website and blog. A few additional examples of using stack tracing and flame graphs for (more...)

Introducing the Rittman Mead OBIEE Performance Analytics Service

Fix Your OBIEE Performance Problems Today

OBIEE is a powerful analytics tool that enables your users to make the most of the data in your organisation. Ensuring that expected response times are met is key to driving user uptake and successful user engagement with OBIEE.

Rittman Mead can help diagnose and resolve performance problems on your OBIEE system. Taking a holistic, full-stack view, we can help you deliver the best service to your users. Fast response times enable your (more...)

Where do my trace files go? V$DIAG_INFO

Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, (more...)

OOW’15 Session Preview – Oracle Alta UI Patterns for Enterprise Applications and Responsive UI Support

This is preview post for my OOW'15 session - Oracle Alta UI Patterns for Enterprise Applications and Responsive UI Support [UGF2717]. I'm going to talk and demo various useful things about Alta UI and ADF. Title talks about UI, but there will be much more. I'm going to show you, Alta UI is not only about look and feel, it is totally new way of building enterprise applications. Certainly UI is very important, but you (more...)

Little things worth knowing: automatic generation of extended statistics in 12c

When you are migrating to Oracle 12c I hope you might this post useful. I came across this feature when researching what’s new with Oracle 12c (and yes I still find lots of new ones I haven’t noticed before). This one is a bit hidden away in section Automatic Column Group Detection of the 12c New Features Guide. And it’s a lot more complex than I first thought! In this first post (more...)

Does Linux hate Java?

Recently, I have discovered a fancy bug affecting few version of Linux kernel. Without any warnings JVM just hangs in GC pause forever. Root cause is a improper memory access in kernel code. This post by Gil Tene gives a good technical explanation with deep emotional coloring.

While this bug is not JVM specific, there are few other multithreaded processes you can find on typical Linux box.

This recent bug make me remember few other (more...)

SQL Profile and Plan Baseline Notes

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013

What is the difference between SQL Profiles and SQL Plan Baselines?
By Maria Colgan-Oracle on May 08, 2012

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011

3 OCT/10

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in (more...)

How the log writer and foreground processes work together on commit.

(warning: this is a rather detailed technical post on the internal working of the Oracle database’s commit interactions between the committing foreground processes and the log writer)

After the Trivadis Performance days I was chatting to Jonathan Lewis. I presented my Profiling the log writer and database writer presentation, in which I state the foreground (user/server) process looks at the commit SCN in order to determine if its logbuffer contents are written to disk by (more...)

Use Case of Auto Re-Execute Functionality in ADF BC

There are use cases, when data in DB is changed by background processes and we would like to display to the user latest data available. Very common implementation for this use case is to re-execute ADF iterator and VO each time when Task Flow or UI screen is accessed. Obviously this works, but performance would suffer - usually there is no need to re-fetch data each time, it must be re-fetched only when changes are (more...)

Optimizer statistics – parse time statistics aggregation

It’s getting harder and harder to find time for writing. Anyway, this post stays from a long time in my draft so, I think is time to finish and post it. That one is the last one from the optimizer statistics series. For now, of course :) After describing the horror around the way that […]

oug_scot: Oracle DB In Memory Presentation

This is the presentation I gave at the Scotland OUG back in June 2015 (Yes, I know I’m late).

The presentation was interrupted by a fire alarm (you can read all about it here).

This is a customer case study on a proof of concept we did with a customer a while ago. The PoC was successful and we are now waiting for the application to catch up and go to Oracle 12c so (more...)