Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.

Well, it's one thing to say 'create all new tables like this', (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 4

This is the fourth part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time the previous read-only test was modified to be a 100% update read-write test. So every row read was updated in the following way:

    for rec in (
          select /*+

Spark 2.0 Performance Improvements Investigated With Flame Graphs

Topic: This post is about performance optimizations introduced in Apache Spark 2.0, in particular whole-stage code generation. A test case is introduced and investigated with diagnostic tools.

Introduction: performance troubleshooting of a slow query using parallel query execution in a Hadoop cluster

The idea for this post comes from a performance troubleshooting case that has come up recently on our database services. It started with a user reporting slow response time from a query (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 3

This is the third part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using logical I/O only (see part 2) the test harness executes a SQL statement that has to perform physical I/O mostly, and to be more precise this round is a read-only test, so doesn't include any modification to data (except the logging and any other background (more...)

Set all WebLogic log levels to “Inherit” via WLST

Logging is a very useful feature of WebLogic.

Unfortunately the log levels, which are set after a clean install of WebLogic or some of the Fusion Middleware product creates a lot of noise and therefore it costs I/O performance.

Additional after analyzing an issue with logging often resetting the log level is forgotten.

Here you get a script to reset the log levels at regular intervals or after a trace session.


Adaptive Cursor Sharing Fail

Here is another example (besides the fact that Adaptive Cursor Sharing only gets evaluated during a PARSE call (still valid in 12c) and supports a maximum of 14 bind variables) I've recently come across at a client site where the default implementation of Adaptive Cursor Sharing fails to create a more suitable execution plan for different bind variable values.Broken down to a bare minimum the query was sometimes executed using non-existing values for a (more...)

Nested Loop Join Physical I/O Optimizations

Having done my mini-series on Nested Loop join logical I/O optimizations a while ago I unfortunately never managed to publish anything regarding the Nested Loop join physical I/O optimizations, which are certainly much more relevant to real-life performance.Therefore the main purpose of this blog post is to point you to Nikolay Savvinov's (whose blog I can recommend in general) great mini-series covering various aspects of these optimizations:Part 1Part 2Part 3Summary (more...)

Oracle Database Cloud (DBaaS) Performance Consistency – Part 2

This is the second part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using a trivial PL/SQL loop (see part 1) the test harness executes a SQL statement that performs logical I/O only, so no physical I/O involved.

In order to achieve that a variation of Jonathan Lewis' good old "kill_cpu" script got executed. In principle each thread performed (more...)

Comparing Columns Containing NULL Values

Prompted by a (not really that) recent discussion on the OTN forum I've decided to publish this note.Sometimes you have the task of comparing column values and handling the NULL value cases correctly makes this rather cumbersome for columns that are allowed to be NULL.The "official" SQL way of comparing two column values and to find out whether they are equal or not - under the assumption that having NULL in both columns (more...)

New Version Of XPLAN_ASH Utility

A new version 4.23 of the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

This version comes only with minor changes, see the change log below.

Here are the notes from the change log:

- Finally corrected the very old and wrong description of "wait times" in the script comments, where it was talking about "in-flight" wait events but that is not correct. ASH performs a "fix-up" (more...)

Unified Auditing – some insights

Oracle 12c Unified Auditing is a brand new feature in the latest Oracle version which consolidates database level auditing records into a single location. DBAs can access the audit information from the view UNIFIED_AUDIT_TRAIL for all kind audit records, and they are

SQL> select distinct COMPONENT from all_unified_audit_actions;

Direct path API
Database Vault
Label Security

8 rows selected.

UNIFIED_AUDIT_TRAIL is a view owned by SYS and a public (more...)

Hotsos 2016 Recap

I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when (more...)

PerfSheet.js: Oracle AWR Data Visualization in the Browser with JavaScript Pivot Charts

PerfSheet.js is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data in the browser using javascript.

The reason for a tool like PerfSheet.js is to make the analysis of AWR data easier by providing a graphical interactive interface and by automating several repetitive steps of data extraction and chart preparation. Pivot charts provide a flexible and easy to use way to navigate (more...)

Big Data – Tez, MR, Spark Execution Engine : Performance Comparison

| Feb 25, 2016

There is no question that massive data is being generated in greater volumes than ever before. Along with the traditional data set, new data sources as sensors, application logs, IOT devices, and social networks are adding to data growth. Unlike traditional ETL platforms like Informatica, ODI, DataStage that are largely proprietary commercial products, the majority of Big ETL platforms are powered by open source.

With many execution engines, customers are always curious about their usage (more...)


Oracle PARTIAL INDEXES – a new feature in Oracle 12c, initial impression was really good, but the implementation is quite poor. With this feature, you can define INDEXING OFF for a partition so that any index with PARTIAL clause will skip this partition while creating the index. The advantage is you can selectively create indexes on partitions. And for a global index or a global partitioned index, the rows from the INDEXING OFF partitions will (more...)

Linux Perf Probes for Oracle Tracing

Topic: this post is about Linux perf and uprobes for tracing and profiling Oracle workloads for advanced troubleshooting.


The recent progress and maturity of some of the Linux dynamic tracing tools has raised interest in applying these techniques to Oracle troubleshooting and performance investigations. See Brendan Gregg's web pages for summary and future developments on dynamic traces for Linux. Some recent work on applying these tools and techniques to Oracle can be found (more...)

Flame Graphs Vs. Cold Numbers

Stack trace sampling is very powerful technique for performance troubleshooting. Advantages of stack trace sampling are

  • it doesn't require upfront configuration
  • cost added by sampling is small and controllable
  • it is easy to compare analysis result from different experiments

Unfortunately, tools offered for stack trace analysis by stock profilers are very limited.

Solving performance problem in complex applications (a lot of business logic etc) is one of my regular challenges. Let's assume I have another (more...)

Extended Stack Profiling – Ideas, Tools and Comments

Topic: This post provides a short summary and pointers to previous work on Extended Stack Profiling for troubleshooting and performance investigations.

Understanding the workload is an important part of troubleshooting activities. We seek answers to questions like: what is the system doing, where is the time spent, which code paths are most used, what are the wait events, etc. Sometimes the relevant diagnostic data is easy to find, other times we need to dig (more...)

Unindexed Foreign Keys on empty/unused table and locks

It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.

My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.

Imagine that you have highly active table (supplier) with lots DML operations from many (more...)

Reporting Long Running Operations in SQL Developer

Today here’s a shorter post about my experiments with Oracle SQL Developer’s user-defined reports: A report on all long running operations (“LongOps”) with details on session wait events, explain plans and live SQL monitoring. “Wait a minute”, you might say, “there’s already the session report in SQL Developer’s standard reports that shows Session_LongOps”! – and you’re […]