Creating a SQL Plan Baseline from Cursor Cache or AWR

A DBA deals with performance issues often, and having a SQL suddenly performing poorly is common. What do we do? We proceed to “pin” an execution plan, then investigate root cause (the latter is true if time to next fire permits).

DBMS_SPM provides some APIs to create a SQL Plan Baseline (SPB) from the Cursor Cache, or from a SQL Tuning Set (STS), but not from the Automatic Workload Repository (AWR). For the latter, you (more...)

Purging a cursor in Oracle – revisited

A few years ago I created a post about “how to flush a cursor out the shared pool“, using DBMS_SHARED_POOL.PURGE. For the most part, this method has helped me to get rid of an entire parent cursor and all child cursors for a given SQL, but more often than not I have found than on 12c this method may not work, leaving active a set of cursors I want to flush.

Script below (more...)

Baselines – session creating privs v session running privs

A colleague Richard Wilkinson was telling me about an issue he had come across with baselines and I asked him to write it up as it was an interesting experience.

The following MERGE SQL runs once a day. It always uses the same plan and roughly takes between 30 and 80 minutes:

SQL_ID/ PHV 6zs5dk6t6pkfs / 3064471754

 MERGE INTO DWB_X_PLNGRM_ATTR_DAY t
 USING (
 SELECT ilv.bsns_unit_cd,
 ilv.sku_item_nbr,
 ilv.cntnt_cd ,
 ilv.sel_units_cntnt_status,
 ilv.plngrm_item_grp_cd,
  (more...)

ADF Performance on Docker – Lighting Fast

ADF performance depends on server processing power. Sometimes ADF is blamed for poor performance, but in most of the cases real issue is related to poor server hardware, bad programming style or slow response from DB. Goal of this post is to show how fast ADF request could execute and give away couple of suggestions how to minimize ADF request time. This would apply to ADF application running on any environment, not only Docker. I'm (more...)

ADF Performance Story – This Time Developer Was Wrong

ADF is fast. If ADF application is slow, most likely this is related to development mistakes. I would like to tell you one story, based on my ADF tuning experience. Problem description: ADF application runs fast in DEV, when DB size is small. Same application runs slow in TEST/PROD, when DB size is large. Question - what is slow. Answer - slow means forms are loading slow. Ok, lets go to the story.

Developer decides (more...)

Beware of intensive slow query logging when using – log_queries_not_using_indexes

MySQL slow query log is great for identifying slow queries that are good candidates for optimisation. Slow query logging is disabled by default, but it is activated by DBA's or developers on most environments.

You can use slow query log to record all the traffic but be careful with this action as logging all traffic could be very I/O intensive and could have negative impact on general performance. It is recommended to record all traffic (more...)

Oracle and the Autonomous Database (a personal perspective from afar)

Yeah, if you hadn’t seen that one coming, hmmm, what can I say… Lot’s of…

nVision Performance Tuning: Table of Contents

This post is an index for a series of blog posts that discuss how to get good performance from nVision as used in General Ledger reporting.  As the posts become available links will be updated in this post.
  • Introduction
  • nVision Performance Options
  • Indexing of Ledger, Budget and Summary Ledger Tables
  • Partitioning of Ledger, Budget and Summary Ledger Tables
  • Additional Oracle Instrumentation for nVision
  • Logging Selector Usage
  • Analysis of Tree Usage  with the Selector Log
  • Interval (more...)

Autonomous Online Index Rebuild for Oracle Multitenant

First things first: Do not rebuild Oracle indexes! … Unless you have to.

If you are even considering rebuilding indexes on an autonomous manner, please stop now, and first spend some time reading some of the many things Richard Foote has to say on his well-recommended blog.

A little side story: Many, many years ago, as I was supporting EBS at Oracle, one day I got a call from a guy with an aussie accent (more...)

Performance Analysis of a CPU-Intensive Workload in Apache Spark

Topic: This post is about techniques and tools for measuring and understanding CPU-bound and memory-bound workloads in Apache Spark. You will find examples applied to studying a simple workload consisting of reading Apache Parquet files into a Spark DataFrame.
 

Why are the topics discussed here relevant

Many workloads for data processing on modern distributed computing architectures are often CPU-bound. Typical servers underlying current data platforms have a large and increasing amount of RAM and (more...)

Parallel Reality


"Expect everything, I always say, and the unexpected never happens."
-- Norton Juster, The Phantom Tollbooth

The following question was recently posted in an Oracle forum:


hi Friends,
I see  this wait event latch: parallel query alloc buffer, when a job meant for doing some cleanup ran this query.
Why does this wait event come happen? , i searched google,MOS no exact hit for explanation of the exact same event.
Looking at query does  (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...)

Validate Performance Improvement Using Query Folding Feature in Power BI

I’ve been using Power BI for a couple months now, not as a developer, but as a system architecture. I may not deal with dashboard and report development on a daily basis, however, I, as an end user, use Power BI extensively to monitor Azure and Power BI usage including audit and billing. I would like to learn more about this tool to its nuts and bolts. The intention of this blog series is to (more...)

Diving into Spark and Parquet Workloads, by Example

Topic: In this post you can find a few simple examples illustrating important features of Spark when reading partitioned tables stored in Parquet, in particular with a focus on performance investigations. The main topics covered are:
  • Partition pruning
  • Column projection
  • Predicate/filter push-down
  • Tools for investigating Parquet metadata
  • Tools for measuring Spark metrics

Motivations: The combination of Spark and Parquet currently is a very popular foundation for building scalable analytics platforms. At least this is what (more...)

OGh Tech Experience 2017 – recap

On June 15th and 16th 2017 the very first OGh Tech Experience was held. This 2-day conference was a new combination of the DBA Days and Fusion Middleware Tech Experience that were held in previous years. To summarize: OGh hit bullseye. It was two days packed with excellent in-depth technical sessions, good customer experiences and great networking opportunities.

The venue was well chosen. De Rijtuigenloods in Amersfoort is a former maintenance building of the Dutch (more...)

#Javaland 2017 wrap up

Yes – I did it again and attend Javaland conference in Phantasialand Brühl.

It was not easy this year to concentrate on the sessions because of the hottest march of the last 100 years. But the quality of the sessions beats the weather. Maybe again my invest in reading the abstracts and filter the sessions before the conference has payed off.

Day 1 Conference

Jens Schauderdocumentation & slides with AsciiDoc, Git, Gradle and Reveal.js

(more...)

On Measuring Apache Spark Workload Metrics for Performance Troubleshooting

Topic: This post is about measuring Apache Spark workload metrics for performance investigations. In particular you can find the description of some practical techniques and a simple tool that can help you with Spark workload metrics collection and performance analysis. The post is released with accompanying code on GitHub: sparkMeasure

Introduction to Spark performance instrumentation

  
From recent experience I find that scalability and performance are some of the key motivating factors that drive people (more...)

Business rules, common sense and query performance

Very often, significant performance benefits can be obtained by using some very basic knowledge of the application, its data and business rules. Sometimes even less than that: even if you are not familiar with the application logic at all, you can still use common sense to make some reasonable guesses that would get you a long way in improving query’s performance. Here is an example (based on an actual query that I had to tune (more...)

LNS and LOG_BUFFER

In a oracle data guard environment Log Network Server (LNS) process transports the redo from the primary to the standby site. The behavior of LNS process is different from SYNC and ASYNC mode replication. In ASYNC mode transport, LNS read the redo from log buffer and hand over it to the RFS process in the target site. It is not necessary the redo is always available in the buffer cache. If there is not enough (more...)

Long Parsing and PGA limits

Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.


Both cases were similar and involved queries which were accessing views; and those views’ code is generated by an (more...)