SJK – missing link in Java profiling tool chain

Sometimes it just happens. You have a bloated Java application at your hand and it does not perform well. You may have built this application yourself or just got it as it is now. It doesn't matter, thing is - you do not have a slightest idea what is wrong here.

Java ecosystem have abundance of diagnostic tools (thank for interfaces exposed at JVM itself), but they are mostly focused on some specific narrow kinds (more...)

Using Shared AM to Cache and Display Table Data

This post is based on Steve Muench sample Nr. 156. In my personal opinion, ADF samples implemented by Steve Muench still remain one of the best source of examples and solutions for various ADF use cases. Sample Nr. 156 describes how to use Shared AM to display cached data in UI table. Typically Shared AM's are used to implement cached LOV's (session or application scope). But it could go beyond LOV, based on the use (more...)

Auto Refresh for ADF BC Cached LOV

You can configure auto refresh for ADF BC cached LOV and this works out of the box, no special coding is needed. My previous post describes how ADF BC session cached LOV behaves - ADF BC Session Cached LOV - Understanding Expire Time. Auto refresh can be configured for both application and session caching. One important thing - auto refresh doesn't work when ADF BC is configured with disconnected mode (ADF BC Tuning with (more...)

Heat Map Visualization of Latency Histograms for NetApp C-Mode

Topic: This post is about collecting and visualizing I/O latency histograms for NetApp filers in C-mode.

Motivations: The drill down of I/O latency is an important technique for troubleshooting and benchmarking storage. Average latency values can hide details of what is happening on the storage. Think for example of storage systems with flash and spindles, each serving I/O at different latency. Moreover averaging the measured values over time can hide details in case of varying (more...)

Investigating the full table direct path / buffered decision.

A lot of blogposts and other internet publications have been written on the full segment scan behaviour of a serial process starting from Oracle version 11gR2. This behaviour is the Oracle engine making a decision between scanning the blocks of a segment into the Oracle buffercache or scanning these blocks into the process’ private process global area (PGA). This decision is even more important on the Exadata platform, because the Oracle engine must have made (more...)

Event Histogram Metric and Oracle 12c

Topic: event histogram metric, a script and some related comments on collecting and displaying wait event latency histograms for Oracle performance troubleshooting.

Why: Latency histograms (and by extension wait event histograms) provide very useful information when troubleshooting performance for systems exhibiting response time with multi-mode distribution. In such cases average wait values are often not sufficient to understand the behavior of the system under study and histograms provide a finer level of details. A (more...)

Inserting million of time series data (rows) per second inside oracle database

This blog post is the first of a series of 4 related to time series and indexing.

According to wikipedia this is the definition of a time series:

A time series is a sequence of data points, typically consisting of successive measurements made over a time interval.

This blog post shows how we can efficiently store time series in an oracle database and how indexing strategy can have a big impact on insertion performance rate.


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...)

Visualizing Statspack “Average Active Sessions” in SQL Developer

This is the second post in my mini-series on leveraging SQL Developer Reports for DBA tasks, today with visualizing Average Active Sessions (AAS). In this article I’ll cover What AAS is and how to interpret it How to build a basic line graph in SQL Developer How to extend the graph with detailed child reports (Time […]

Heat Map Visualization for SystemTap

Topic: PyLatencyMap v1.2 and how it can be used to produce heat map visualization of SystemTap histograms.

Introduction: When studying storage performance, the latency drill down is a very important data source. Measuring the average I/O latency is often not enough, latency histograms are proven to more suitable for investigating modern storage systems. This is because for many storage systems the response time has multiple modes: think for example of the common case of storage (more...)

Visualizing Statspack Performance Data in SQL Developer

If you run Oracle Standard Edition or haven’t licenced Diagnostics Pack for Enterprise Edition, then you don’t have AWR and ASH Data available. This is when Statspack, the predecessor of AWR, comes in handy to keep a history of database performance metrics. But although Oracle still deliver Statspack with their recent DB releases (yes, even in […]

Tablespace growth history

Recently we had a tablespace space run out and ended up in an application failure.  I have questioned my DBA and he just plainly blamed the application team members who loaded large number records without a prior notice.  A convincing answer, but you can’t really fool Oracle.

Oracle introduced a new DBA_TABLESPACE_USAGE_METRICS view from 10g onwards to report the space usage with in a tablespace.  I created a new tablespace and immediately (more...)

x$bh and consistency

Oracle caches the data blocks in buffer cache in various modes depends on the block usage.  As per the Oracle documentation it can CR (Consistent mode – reads), XCUR (Current mode – updates), FREE etc.   I understand and other heard saying – whenever a block READs into memory will be in CR mode while if the block is fetching for UPDATE it will be in XCUR mode so that sessions can apply the (more...)

Latest updates to PerfSheet4, a tool for Oracle AWR data mining and visualization

Topic: This post is about the latest updates to PerfSheet4 v3.7 (February 2015). PerfSheet4 is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data using Excel pivot charts.

Why PerfSheet4: PerfSheet4 is aimed at querying and displaying time-series data from AWR repository tables. This is very a rich source of information to analyze database workloads and trends in the context of performance analysis or (more...)

Binary search – is it still most optimal?

If you have a sorted collection of elements, how would you find index of specific value?
"Binary search" is likely to be your answer.
Algorithms theory is teaching us what binary search is most optimal algorithm for this task with log(N) complexity.
Well, hash table can do better, if you need to find key by exact match. In many cases, though, you have reasons to have your collection sorted, not hashed.

On my job, I'm (more...)

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

Bind peeking and session cached cursor

Bind peeking is a nice feature in Oracle to have many optimized plans for an SQL for various bind values.  DBAs believe that bind peeking happens during a soft parse which will identify an alternate plan. Why do I say that?


Hard Parse: Parsing first time, nothing exists to bind peek


Soft Parse :  SQL cursor is existing and executing not the first time.  Under the soft parse, bind peeking (more...)

Blog update!

Hi everyone!

This blog hasn’t seen any updates since over a year now and I do apologize for that. However, the reason for that is simple. Last year I’ve decided to take one a new project. But before I was going to tell everybody about it I wanted to make sure that I also really got the time to do so. In the meantime I got the confidence that the project will succeed and that (more...)

enq: TM – contention due to parallel DML and foreign keys

This is a write-up of an issue I recently posted to the OTN discussion forum ( I thought the associated test case was useful in demonstrating the issue, so is captured here for future reference. There were some useful replies to the OTN post, confirming my suspicions.

The test was performed using Oracle Enterprise Edition on Linux.

Problem statement

A parallel delete blocks insert into dependent (more...)

Complément : Analyse de performances via AWR Warehouse