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

Generierung von Bitlisten mit Hilfe von SQL am Beispiel einer Datum zu Monat Konvertierung

Ich hatte vor kurzem die Aufgabe erhalten eine Bitliste auf Basis eines Monats zu generieren.
Bedeutet, ich habe einen String mit 31 Zeichen der je Zeichen den Zustand 1 oder 0 einnehmen kann.
 - 1 steht für aktiv
 - 0 steht für inaktiv

Beispieldaten:
'0000000001010001000000000000000'
'0000000000000000111111000011010'

Um dies anhand eines verständlichen Beispiels zu verifizieren, habe ich mir eine Dienstplan-Tabelle ausgedacht.
Mehr »

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

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.

(more...)

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

Convert CSV file to Apache Parquet… with Drill

Read this article on my new blog A very common use case when working with Hadoop is to store and query simple files (CSV, TSV, ...); then to get better performance and efficient storage convert these files into more efficient format, for example Apache Parquet. Apache Parquet is a columnar storage format available to any project in the Hadoop ecosystem. Apache Parquet has the following

Limit length of listagg

SQL> select student_name, course_id from studentx order by student_name

STUDENT_NAME COURSE_ID
------------ ---------
Chris Jones  A102     
Chris Jones  C102     
Chris Jones  C102     
Chris Jones  A102     
Chris Jones  A103     
Chris Jones  A103     
Joe Rogers   B103     
Joe Rogers   A222     
Joe Rogers   A222     
Kathy Smith  B102     
Kathy Smith  A102     
Kathy Smith  A103   (more...)

How To Use The SQL Developer Export Connections With Passwords Function

SQL Developer Export ConnectionsOracle’s SQL Developer tool lets you export a list of connections that you have created. This is great for saving time for your team. Learn how you can do that in this article.

Why Export Connections?

First of all, why would you want to export your connections?

There are a few reasons I can think of.

  1. Keep a separate file that contains your connections in case your computer crashes
  2. Save time setting up your computer (more...)

SQL Plan Management Choices

My thoughts on SQL plan management decision points:

SPM

SQL Patches are also available and not covered in the above flowchart.


Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.


SQL> create table t
2 as
3 select rownum + 42 id
4 from dual
5 connect (more...)

Oracle ADD_MONTHS Function with Examples

Oracle ADD_MONTHS Function with ExamplesIn this article, I’ll explain how the very useful ADD_MONTHS function works and use some examples.

Purpose of the Oracle ADD_MONTHS Function

The purpose of the Oracle ADD_MONTHS function is to add a specified number of months to a date, and return another month.

 

Syntax

The syntax of the ADD_MONTHS function is:

ADD_MONTHS ( input_date, number_months )

The function returns a DATE value.

 

Parameters

The parameters of the ADD_MONTHS function are: