performance

connor_mc_d's picture

The lunchtime nuisance…

We’ve all been there … You’re trying to save some changes to the database, and no matter what you try, your application just hangs. After some to-and-fro with the DBA, or perhaps you are the DBA, you stumble across that uncommitted transaction that “Joe from marketing” commenced at 11:55am.  You pop around to Joe’s desk, and there it is on screen….and Joe has popped out to lunch for an hour ….. grrrrr!

image

martin.bach's picture

SQLDeveloper’s “mystats.sql” equivalent

It just occurred to me that I haven’t blogged about SQLDeveloper yet, something I really need to change ;) This post covers SQLDeveloper 19.4, and although I don’t know exactly when the feature you are reading about was added I doubt it was in the latest release.

A little bit of background first

Sometimes the wait interface isn’t enough, so you need to dig a little deeper into the performance issue. In my opinion there are two layers inside Oracle when it comes to performance troubleshooting:

connor_mc_d's picture

In-memory opportunities abound

There has always been a bit of a Catch-22 with some of the really cool options in the Oracle Database. You want to explore the value of them, but you don’t want to draw the ire of any licensing implications of doing so. Of course, you can use XE or a trial version of the software, but nothing really helps prove (or disprove) the value of some functionality as much as running it on real Production volumes with real Production data.

So I was very very excited to see this in the 20c documentation:

image

connor_mc_d's picture

SQL Plus … the sweet spot

Need to get a bunch from rows out of the database? Most people are aware of the ARRAYSIZE parameter to improve the fetch performance back to the client, but many people are not aware of the newer ROWPREFETCH parameter.

Let’s take a look at each to see how quickly we can drag data back to our SQL Plus client. I’ll start with table called TX which is approximately 1500MB in size, and has just over 10millions.

First let’s look at the benefits you get from ramping up ARRAYSIZE. I’ll start with the default size of 15, and quickly you’ll see why that is not a good starting point if you are fetching lots of rows. (Important emphasis on the “if” there)

Jonathan Lewis's picture

date_to_date

Every now and again someone posts a piece of SQL on the Oracle Developer Forum that includes a predicate with an expression like to_date(date_column). This is a problem for several reasons – not the least being the type of performance problem that showed up in a post from a couple of years back that has just been resurrected.

Before I examine the performance detail, here’s a simple demo of the “wrong data” problem that can go unnoticed, cut-n-paste from a 12.2.0.1 session of SQL*Plus:

Jonathan Lewis's picture

Sequence Costs

You’re probably aware of the “identity” construct that appeared in 12.1 and uses Oracle’s sequence mechanism to model the Autonumber or Identity columns that other databases have. A posting from Clay Jackson on the Oracle-L list server suggests that something about their code path has introduced a surprising overhead in 19c … when you’re not using them.

The following code is a slightly modified version of a test case that Clay Jackson posted to demonstrate a strange difference in performance between 12.2 and 19.3

Jonathan Lewis's picture

dense_rank

I’ve just been prompted to complete and publish a draft I started a few years ago. It’s (ultimately) about a feature that appeared in 9i but doesn’t seem to show up very often at client sites or as a common solution to performance problems on the various Oracle forums – but maybe that’s not surprising given how slowly analytic functions have been taken up.

I want to work towards the feature by starting with a requirement, then examine several solutions. To supply a touch of realism I’ll create an orders table, which holds a customer id and an order date (including time), ,and then ask for a report of the most recent order for each customer. Here’s some starting data:

dbakevlar's picture

Unreal Load Testing in the Cloud

Load testing as part of a cloud migration project is expected and  should be built into the requirements.  The goal is to set yourself up for success.

Log Latency

Recently I had a Cloud Solution Architect, (CSA) escalate an Oracle performance problem after migrating from on-prem to the cloud.

connor_mc_d's picture

Databases are slow right?

Man, it gets my goat when people start pontificating nonsense on Twitter.

Yeah, I know, I know, I shouldn’t get upset, because 99% of everything on social media is nonsense anyway, but it is when people roll out claims about database tech that I tend to get tetchy Smile

Today it’s that familiar old chestnut: “Constraints in the database make it slow“. And…this one even came with some numbers add some apparent weight!

To prevent automated spam submissions leave this field empty.
Syndicate content