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

JVM performance: OpenJ9 uses least memory. GraalVM most. OpenJDK distributions differ

In a previous blog post I created a setup to compare JVM performance of several JVMs. I received some valuable feedback on the measures I conducted and requests to add additional JVMs. In this second post I’ll look at some more JVMs and I’ve added some measures like process memory usage and startup time. Also I’ve automated the test (with a bash script) and reduced the complexity of the setup by removing haproxy and testing (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...)

Tuning Time


"“You see,” he continued, beginning to feel better, “once there was no time at all, and people found it very inconvenient.
They never knew whether they were eating lunch or dinner, and they were always missing trains. So time was invented to
help them keep track of the day and get places when they should. When they began to count all the time that was available,
what with 60 seconds in a minute and 60  (more...)

Oracle wait event ‘TCP Socket (KGAS)’

I was asked some time ago what the Oracle database event ‘TCP socket (KGAS)’ means. This blogpost is a deep dive into what this event times in Oracle database 12.1.0.2.180717.

This event is not normally seen, only when TCP connections are initiated from the database using packages like UTL_TCP, UTL_SMTP and the one used in this article, UTL_HTTP.

A very basic explanation is this event times the time that a database (more...)

ADF Task Flow Performance Boost with JET UI Shell Wrapper

ADF application with UI Shell and ADF Task Flows rendered in dynamic tabs would not offer instant switch from one tab to another experience. Thats because tab switch request goes to the server and only when browser gets response - tab switch happens. There is more to this - even if tab in ADF is not currently active (tab is disclosed), tab content (e.g. region rendered from ADF Task Flow) still may participate in (more...)

using AWR on Exadata

Oracle released a brand new Whitepaper for Exadata

How to use AWR Report on Exadata Machines

This Whitepaper gives details how the AWR reports can be used in conjunction with Exadata to monitor and analyze database performance issues

http://www.oracle.com/technetwork/database/availability/exadata-maa-best-practices-155385.html

 

 

 

SparkMeasure, a tool for performance troubleshooting of Apache Spark workloads

SparkMeasure 

 SparkMeasure simplifies the collection and analysis of Spark task metrics data. It is also intended as a working example of how to use Spark listeners for collecting and processing Spark performance metrics.
The work on sparkMeasure has been previously presented in this blog with examples. Recently, an updated version of sparkMeasure (version 0.13) introduces additional integration for the PySpark and Jupyter environments, improved documentation and additional features provided by the community via (more...)

All about headroom and mandatory patching before June 2019

This post was triggered upon rereading a blogpost by Mike Dietrich called databases need patched minimum april 2019. Mike’s blogpost makes it clear this is about databases that are connected using database links, and that:
– Newer databases do not need additional patching for this issue (11.2.0.4, 12.1.0.2, 12.2 and newer).
– Recent PSU patches contain a fix for certain older versions (11.1.0.7, 11.2. (more...)

ADF Postback Payload Size Optimization

Recently I came across property called oracle.adf.view.rich.POSTBACK_PAYLOAD_TYPE. This property helps to optimize postback payload size. It is described in ADF Faces configuration section - A.2.3.16 Postback Payload Size Optimization. ADF partial request is executing HTTP post with values from all fields included. When postback property is set to dirty, it will include into HTTP post only changed values. As result - server will get only changed attributes, (more...)

Introducing SQLdb360: merging eDB360 and SQLd360, while rising the bar to community engagement

Today, we are very happy to release SQLdb360, a new tool that merges together eDB360 and SQLd360, under a single package.

Tools eDB360 and SQLd360 can still be used independently, but now there is only one package to download and keep updated. All the new features and updates to both tools are now in that one package.

The biggest change that comes with SQLdb360 is the kind invitation to everyone interested to contribute (more...)

This Week in PostgreSQL – May 31

Since last October I’ve been periodically writing up summaries of interesting content I see on the internet related to PostgreSQL (generally blog posts). My original motivation was just to learn more about PostgreSQL – but I’ve started sharing them with a few colleagues and received positive feedback.  Thought I’d try posting one of these digests here on the Ardent blog – who knows, maybe a few old readers will find it interesting? Here’s the (more...)

Long-running delete of synopses during table stats gathering

Oracle 11.2.0.4

I recently encountered one long-running session and several blocked sessions, all performing the same delete operation.

--SQL_ID dsf21kcbjqfyk
DELETE FROM SYS.WRI$_OPTSTAT_SYNOPSIS$ WHERE BO# = :B2 AND INTCOL# = :B1;

One session had been running for several hours, and the other sessions were all blocked by a row lock from the first session. Unfortunately, each of these sessions was trying to gather stats on new partitions of tables as part (more...)

SJK is learning new tricks

SJK or (Swiss Java Knife) was my secret weapon for firefighting various types of performance problems for long time.

A new version of SJK was released not too long ago and it contains а bunch of new and powerful features I would like to highlight.

ttop contention monitoring

SJK is living it's name by bundling a number of tool into single executable jar. Though, ttop is a likely single most commonly used tool under SJK (more...)