Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Original Post can be viewed at Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Oracle Goldengate supports Oracle to PostgreSQL migrations by supporting PostgreSQL as a target database, though reverse migration i.e PostgreSQL to Oracle is not supported. One of the key aspect of these database migrations is initial data load phase where full tables data have to copied to the target datastore. This can be a time (more...)

Lies, darn lies and sampling bias

Sampling profiling is very powerful technique widely used across various platforms for identifying hot code (execution bottlenecks).

In Java world sampling profiling (thread stack sampling to be precise) is supported by every serious profiler.

While being powerful and very handy in practice, sampling has well known weakness – sampling bias. It is real and well-known problem, though its practical impact is often being over exaggerated.

A picture is worth a thousand of words, so let (more...)

12c Snapshots

I published a note a few years ago about using the 12c “with function” mechanism for writing simple SQL statements to takes deltas of dynamic performance views. The example I supplied was for v$event_histogram but I’ve just been prompted by a question on ODC to supply a couple more – v$session_event and v$sesstat (joined to v$statname) so that you can use one session to get an idea of the work done and time spent (more...)

Less slamming V$SQL

It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today Smile

IMG_20190303_114220_012

When I’m contacted by customers or friends and asked to help out with tuning their applications, probably the two most common issues are one or two poorly performing SQL statements, or the server being overly stressed with CPU load with no single SQL being the obvious cause.

In either case, a common plan of (more...)

PostgreSQL – LWLock:multixact_offset

On one of the PostgreSQL db , the active sessions were waiting on IO:SLRURead and LWLock:multixact_offset causing application latency.

Multioffset

As per PostgreSQL doc, SLRURead is waiting for a read of an SLRU page and multixact_offset is waiting for I/O on a multixact offset buffer.

Diagnosing the issue took some time so cut to the chase

1. Identified the SQLs, relation (and its size) for which these wait events were high. These table had ~1500 DMLs (more...)

A Performance Dashboard for Apache Spark

Topic: This post dives into the steps for deploying and using a performance dashboard for Apache Spark, using Spark metrics system instrumentation, InfluxDB and Grafana.

What problem does it solve: The dashboard can provide important insights for performance troubleshooting and online monitoring of Apache Spark workloads. In particular when running Spark applications in a distributed environment (Kubernetes, YARN, etc.).

Context: Familiarity with Spark architecture built-in instrumentation, notably the Web UI. This discussion also overlaps (more...)

shared resources

Some days ago I had one of these calls from an application colleague:
The database was slow yesterday, can you please check what's the problem?
Of course, I had some short discussion if he really means the DB or should it be called the application is slow. Also some other questions needed to be asked first, e.g. if it's a response time or throughput issue, when it was "good" last time, what "bad" and (more...)

ADF Performance Improvement with Nginx Compression

We are using Nginx web server for Oracle ADF WorkBetter hosted demo hosted on DigitalOcean cloud server. Nginx helps to serve web application content fast and offer improved performance. One of the important tuning options - content compression, Nginx does this job well and is simple to setup.

Content compression doesn't provide direct runtime performance, a browser would run the same code, doesn't matter it was compressed or not. But it brings improved perceived performance (which (more...)

JDeveloper 12c IDE Performance Boost

There is a way to optimize JDeveloper 12c IDE performance by disabling some of the features you are not using.

I was positively surprised with improved JDeveloper responsiveness after turning off some of the features. ADF BC, Task Flow, and ADF Faces wizards started to respond in a noticeably faster way. Simple change and big performance gain, awesome.

One of the strongest JDeveloper performance improvements come from disabling TopLink feature. Ironically - TopLink is an (more...)

Oracle USER vs sys_context

This post was inspired by the fact I couldn't find many good references for what I thought was 'previously discovered truth'. So what does any good science-nut do? Add their own contribution.

So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.

1) Stop using USER


I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a (more...)

DML Tablescans

This note is a follow-up to a recent comment a blog note about Row Migration:

So I wonder what is the difference between the two, parallel dml and serial dml with parallel scan, which makes them behave differently while working with migrated rows. Why might the strategy of serial dml with parallel scan case not work in parallel dml case? I am going to make a service request to get some clarifications but maybe (more...)

Top-N Queries

This is an old story. It started in 2015 when I found a problem with TOP-N query performance. I wrote a blog post about it and later an update. Here I want to show the full testcase and some updates after a twitter discussion. Background I don’t want to spend too much time writing the … Continue reading "Top-N Queries"

Making some more sense of direct path reads during primary key lookups

After having published my first article of 2019 I have received feedback I felt like including. With a nod to @fritshoogland, @ChrisAntognini and @FranckPachot.

In the previous post I showed you output of Tanel Poder’s ashtop.sql as proof that direct path reads can occur even if all you do is look up data by primary key. This script touches v$active_session_history, and I’m not getting tired of mentioning that you need to license the system (more...)

Scripts to deal with SQL Plan Baselines, SQL Profiles and SQL Patches

To mitigate SQL performance issues, I do make use of SQL Plan Baselines, SQL Profiles and SQL Patches, on a daily basis. Our environments are single-instance 12.1.0.2 CDBs, with over 2,000 PDBs. Our goal is Execution Plan Stability and consistent performance, over CBO plan flexibility. The CBO does a good job, considering the complexity imposed by current applications design. Nevertheless, some SQL require some help in order to enhance their plan stability.

(more...)

Querying the amount of redo in Oracle Database

When a database starts to churn more redo then normal, it is handy to be able to extract the amount of redo over time, to be able to plot this in a graph.  The below allows you to extract this info 🙂

Query for redo generation

Query to obtain the amount of redo generation over time by hour and MB:

set pages 999 lines 400
select to_char(trunc(first_time, 'HH24'), 'DD/MM/YYYY HH24:MI:SS') date_by_hour, sum(round(blocks*block_size/1024/1024)) CHURN_IN_MB
from v$archived_log
 (more...)

PostgreSQL – CPU Utilization and Index

One of the Production Aurora PostgreSQL instance running on db.r4.16xlarge instance (64 vCPU and 488 GB ) was reporting high CPU Utilization spiking upto 100%.

Screen Shot 2018-12-09 at 11.14.49 AM

With such issues, one of the first thing is to look for the SQLs with high buffers shared hit. I have built a small tool called pgsnap which is something similar to AWR respostory in Oracle maintaining the SQL stat history.  So, with pg_stat_statements and hist_pg_stat_statements(that’s what (more...)

Extended Events with Azure Analysis Services

Its almost standard fare to be using Azure Analysis Services with our customer deployments these days.  As our customers evolve the value of their data.  SSIS integration runtimes were pivotal to this and now that there is Azure Analysis Services, it’s even easier to get started with just a few clicks in the portal interface, (or for me, a simple step in a script… :)) and migrate runtimes to the cloud.

One of (more...)

Automatic Clustering, Materialized Views and Automatic Maintenance in Snowflake 

Boy are things going bananas at Snowflake these days. The really big news a few weeks back was another round of funding! This week we announced two new major features.

Column And Table Redefinition With Minimal Locking

TLDR: Note to future self… (1) Read this before you modify a table on a live PostgreSQL database. If you do it wrong then your app might totally hang. There is a right way to do it which avoids that. (2) Especially remember the lock_timeout step. Many blog posts around the ‘net are missing this and it’s very important.

Yesterday I was talking to some PostgreSQL users (who, BTW, were doing rather large-scale cool stuff (more...)

Lazy Loading Menu

I've been using the lazy loading concept demonstrated in Maxime's post quite a lot recently, I'd love to see this as a declarative feature one day.

I also wondered if I could apply this concept to the badge count in the side menu.


Turns out it wasn't that hard, particularly since I already had the jQuery I needed from a previous requirement.

We first need to add a unique class to the link definition, so (more...)