Chunking tables 4: Histograms

After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!

Chunking tables 3: working with blocks

So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?

Show all views including a specific string

Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail:
Mehr »

Oracle Spatial (Teil 3) – Entfernung von Punkten berechnen

Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMTRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.
Mehr »

Importing XML file with invalid character 22 (U+0016)

I have to import a set of XML files from time to time. Most of those XML files can be imported with out any problems. But at least one file includes a special character U+0016 which occurs randomly some where inside the file.

When I try to import that file I get this ORA- error message:
ORA-31011: XML-Parsing nicht erfolgreich
ORA-19202: Fehler bei XML-Verarbeitung
LPX-00217: Ungültiges Zeichen 22 (U+0016)
Error at line 39409 aufgetreten

Mehr »

How an Oracle error can send you the wrong way…

The Twelve Days of SQLT: Day Four: Nobody Did It

Previous installment: Day Three: Just a Mess Without a Clue

I know a funny little man,
As quiet as a mouse,
Who does the mischief that is done
In everybody’s house!
There’s no one ever sees his face,
And yet we all agree
That every plate we break was cracked
By Mr. Nobody.

—Unknown author

The purpose of a SQLT report is to collect all the information that could possibly shed light on a problem (more...)

The Twelve Days of SQLT: Day Three: Just a Mess Without a Clue

P.O.I.S.E.D. is an acronym for a six-step performance tuning method; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. Every problem-solving exercise has these six stages, whether the exercise takes six minutes or six weeks.(read more)

Update multiple columns

The Twelve Days of SQLT: Day Two: Miles To Go Before We Sleep

SQLT adoption has been limited even though it is the best tool for investigating SQL performance: It is only available as a download from Oracle Support. It creates tables and PL/SQL procedures in the database. It does not support Statspack, only AWR. (read more)

The Twelve Days of SQLT: Day One: Do What You Do Do Well

In his fifteen years with Oracle Support, Carlos Sierra created and perfected a tool called SQLTXPLAIN (SQL Tuning and Explain Plan; SQLT for short) for SQL performance investigations. SQLT collects all the information that could possibly be required to investigate SQL performance including environment information, execution plan history, statistics history (system statistics, table statistics, index statistics), and object definitions.(read more)

Using Apache Drill REST API to Build ASCII Dashboard With Node

Read this article on my new blog Apache Drill has a hidden gem: an easy to use REST interface. This API can be used to Query, Profile and Configure Drill engine. In this blog post I will explain how to use Drill REST API to create ascii dashboards using Blessed Contrib. The ASCII Dashboard looks like Prerequisites Node.js Apache Drill 1.2 For this post, you will use the SFO

Did it really fix it 1: OFE & nasty FIRST_ROWS plan on

Plan degradations on upgrade are normal.

This one’s no different.

On further investigation, turned out application was setting optimizer_mode = first_rows somewhere.

First point about this is that first_rows really shouldn’t be used either as a hint or as an optimizer_mode.

What does FIRST_ROWS mean?
From 11g doco:

The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.

If any sort of (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist

If the inputs to the CBO change, the plan can change. For example, changes to bind variables can cause the plan to change when it is hard parsed. Therefore one should never be surprised when plans change.(read more)

SQL Tuning: Thinking in Sets / How and When to be Bushy

Below is a SQL statement from a performance problem I was looking at the other day.

This is a real-world bit of SQL which has slightly simplified and sanitised but, I hope, without losing the real-worldliness of it and the points driving this article.

You don’t really need to be familiar with the data or table structures (I wasn’t) as this is a commentary on SQL structure and why sometimes a rewrite is the best (more...)

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Indentity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:

SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );

Table created.


Fundamentals of SQL Writeback in Dodeca

One of the features of Dodeca is read-write functionality to SQL databases.  We often get questions as to how to write data back to a relational database, so I thought I would post a quick blog entry for our customers to reference.

This example will use a simple table structure in SQL Server though the concepts are the same when using Oracle, DB2, and most other relational databases.  The example will use a simple Dodeca (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 40: Why is it so hard to get SQL performance right the first time?

In the August 2015 issue of the NoCOUG Journal, we asked Stéphane Faroult why it is so hard to get SQL performance right the first time. His answer implies that SQL itself and the way it is taught are the problems.(read more)

Add Color to your SQL

Topic: this post is about some simple techniques to add color to SQL scripts and their terminal output using ANSI escape codes.

Colors can be used to improve the output of command line tools. This is common practice, for example with the (bash) shell. Colors can also be very useful to improve the quality of the output of SQL scripts. In my experience this is not used frequently, probably because of the the need of (more...)

Autonomous transaction to the rescue