Add ORDER BY to make ANY query faster

Yes it’s SCBT day here in Perth!

SCBT = Silly Click Bait Title Smile

This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach of assessing performance based on the true requirements of the relevant component of the application.  I say “true requirements” because it may vary depending on what is important to the (more...)

Interval versus Range partitions

One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the values provided do not map to any partitions.

Here’s a simple example of a range partitioned table which only cover the values from (less than) 1 to a ceiling of 6.

Oracle 18c and 12c on Fedora 29

Danger, Will Robinson! Obligatory warning below.

So here we go…

Fedora 29 has been out for a bit over a week now. Over the weekend I had a play with it and noticed a couple of differences between Fedora 28 and Fedora 29 as far as Oracle installations are concerned. There are some extra packages that need to be installed. Also, one of the two symbolic (more...)

The Openworld Mega-Download!

If you are sad and pathetic enthusiastic and interested in content like me, once a large conference like OpenWorld is over, I like to have the content (slides etc ) from not just the sessions I attended, but all of the sessions I could not attend. In fact, I typically would like to have any and all available content, because that is probably the next best thing to attending the conference.

In particular, now that (more...)

The strange place for INHERIT PRIVILEGES

A while back in an Office Hours session, I touched on a relatively new privilege in the database called INHERIT PRIVILEGES which is designed to avoid erroneous privilege escalation via AUTHID CURRENT_USER routines.

You can watch the full video below

But in a nutshell, it protects against of the concept of a low privileged account “asking” a higher privileged account to run something in order to get access to those higher privileges. In human terms, (more...)

OpenWorld is done!

The OpenWorld conference is over for another year. It’s always a thrill, albeit a hard-working thrill, to visit San Francisco and be a part of such a huge event.

I’ll have a full wrap up post and video soon, but I wanted to get quick blog post out there so people could get a link to see the slides from my sessions.

See my OpenWorld 2018 content here

To all those people that attended my (more...)

Hear my Python for the Oracle DBA talk in Scottsdale on November 16th

I am going to giving a talk about why Python is a good programming language for Oracle DBAs in Scottsdale on November 16th for the Arizona Oracle User Group, AZORA. We may get some other speakers together for the meeting but I will be there for sure. I did this talk for Collaborate and for the RMOUG Training Days last year. I may try to make it a little more interactive if I can get (more...)

18c XE–running locally

There’s already a few blog posts out there, showing people (easily) getting their 18c XE instance downloaded, installed and running, so I won’t rehash that here.

For the majority of people, I’d imagine they’ll have their XE instance running on a small box and connecting to it from “afar”, namely, their own PC or an application server or similar.

But for those of you, especially in these early stages of release, who like me, want (more...)

18c XE is live!

Just a quick post because this is perhaps what I think is one of the biggest game changers for the Oracle Database.

18c Express Edition (18x XE) is now available for general use. For those people with experience with 11g XE, this might not seem to be a big deal, but there is one crucial difference.

Unlike 11g XE, the new version has virtually no restrictions on the functionality offered by the database. And yes, (more...)

OpenWorld Wednesday – step right up!

If you’ve read my two previous posts on the OpenWorld schedule, you’re probably expecting a huge long list of sessions to pique your interest.

But no! There’s only ONE you need on your list Smile

The Fast Lane to Database Success [TIP4094]
Connor McDonald, Developer Advocate for SQL, Oracle
Wednesday, Oct 24, 4:45 p.m. – 5:30 p.m. | Moscone West – Room 3009

The skill set of a database practitioner is much more than (more...)

Compressed LOB–my table got bigger?

We had an interesting question on AskTOM the other day about LOB compression. Someone was very concerned that after applying compression to the LOBS in the column of their table, the table actually got larger! Cue much confusion and panic, but after a little exploration, the explanation was pretty simple.

By default, when you create a LOB column in a table, the default storage definition is ENABLE STORAGE IN ROW. As per the documentation:

(more...)

LOBs vs Data – when compression occurs

Just a quick tip for SECUREFILE lobs.

You may be familiar with basic compression on data in tables. It is a dictionary attribute of the table, which is then taken into account when you perform:

  • a SQL Loader direct load, or
  • an INSERT with the APPEND hint, or
  • a bulk bind INSERT in PLSQL using the APPEND_VALUE hint.

Whichever of the above you perform does not really matter – the key thing is that when (more...)

IO Calibration

One of the long time problems with Oracle is IO calibration. I am talking, of course, about DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. The most problematic aspect of this procedure is the fact that the results are not repeatable. It’s like rolling of the dice, it doesn’t really measure anything. I hoped that Oracle 18c will finally fix that, but no such luck:

[oracle@ora18c ~]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 – Production (more...)

Aurora MySQL synch/mutex/innodb/aurora_lock_thread_slot_futex wait

Thanks to Jeremiah Wilton for the following info:

This wait event indicates that there is a thread which is waiting on an InnoDB record lock. Check your database for conflicting workloads. More information on InnoDB locking can be found here: https://dev.mysql.com/doc/refman/5.6/en/innodb-locking.html

 

In other words, record-level lock conflicts are happening. More than one connection is trying to update the last_login for a particular id in the_table at the same time. Those (more...)

Copy Oracle performance reports to SharePoint using Python

I have a couple of Oracle performance reports that I upload from my work Windows 7 laptop to our corporate SharePoint site on Office 365 on a regular basis. I thought that it would save me time to automate the uploading of these reports using Python. This post describes the continuing saga of my attempts to build a working Python script just to upload a file to SharePoint. I have a script that works for (more...)

ICS to OIC

We have had a number of interesting conversations of late about the transition from ICS to OIC and to spice the discussion whether it should be OIC or Autonomous OIC. The reality of the situation is that the transition between ICS and OIC is a relatively straight forward one using the export and import tooling. […]

The post ICS to OIC appeared first on Implementing Oracle Integration Cloud Service.

Mary Meeker’s Internet Trends 2018

Mary Meeker has delivered her annual Internet Trends report and – as usual – there’s a lot to consider. Others have picked it over for general points of interest so I won’t repeat that, so instead I’ll focus on possible ramifications for Oracle’s Cloud Apps.

Mary Meeker

Mobile Users / Internet Users

There was no growth (in 2017, compared to 2016) on new mobile phone shipments. Have we reached smart-phone saturation point? Also of note is that (more...)

OUG Scotland – Why to Come & Survival Guide

The UKOUG’s Scottish conference is on the 21st June in the centre of Edinburgh, at the Sheraton Grand Hotel, not far from Edinburgh Castle in the centre of the city.

Picture from viator.com, who do tours etc

 

The Event

There is a six-stream agenda covering Database, Apex & Development, Platform & Services, Coud Apps, EBS Apps tech, and Business Analytics/systems & EPM, so pretty much the whole breadth of Oracle Tech, Apps (more...)

MySQL client slow to display the first row of a large result set

A coworker of mine asked me to look at a MySQL query that was running longer than we want it too. If they added a LIMIT 1000 clause at the end of the query it popped up the results in a couple of minutes but without the LIMIT clause it apparently hung forever without returning a row of the result set. I figured out that the query was returning 4 million rows with a bunch (more...)

Python script to backup remote directory using sftp

This is the sftp version of the ftp script I published in an earlier post. It logs into a remote host using sftp and backs up a remote directory recursively backing up all subdirectories.

This one uses Paramiko instead of ftplib so you can do encrypted file copies.

Bobby