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...)
There are currently some limitations to when pragma UDF will speed up your calls to PL/SQL functions.
In my post introducing the new pragma UDF feature of Oracle 12c I explained how it can be used to reduce the impact of context switching when you call a PL/SQL function from SQL.
In my example I showed how running a SQL-only SELECT statement that formatted a name for display over 100,000 records took 0.03 seconds (more...)
The Hitchhiker’s Guide to the EXPLAIN PLAN Part 41: Why has my SQL execution plan changed?—A checklist
We have today the 06.11.2015 (11-06-2015) and we have two date values 01.11.2015 and 30.11.2015.
Now I want to know how much time has past in percent since the beginning (01.11.2015):
Result: 17 %
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...)
A new feature for PL/SQL was introduced in V12, pragma UDF. UDF stands for User Defined Functions. It can speed up any SQL you have that uses PL/SQL functions you created yourself.
We can create our own functions in PL/SQL and they can be called from both PL/SQL and SQL. This has been possible since V7.3 and is used extensively by some sites to extend the capabilities of the database and encapsulate business logic.
Now I want to extend the solution by an optional ZIP export.
You can not export several files at the same time with standard APEX features. For that you have to create a ZIP file including all the files you want to download.
I made an example application "Multi CSV Download as ZIP file" where you see a (more...)
Die Gründe sind leicht erklärt: Zeitmangel!
Der kurzfristige Vorteil kann im Nachhinein aber sehr teuer / zeitaufwendig werden. Nämlich dann, wenn die schnelle Lösung mit erhöhten Datenmengen versagt und statt ursprünglich wenigen Sekunden plötzlich Minuten an Zeit verbraucht.
Hierbei möchte ich auf die goldene Regel von Tom Kyte referenzieren:
Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.
If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, (more...)
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)
Yesterday I published a SQL problem (that I I had solved – or at least, thought I had solved!) and invited readers to submit their own solutions.
SPOILER ALERT: if you haven’t had a chance to have a go yourself, go back, don’t read the comments (yet), but see if you can solve it yourself.
I was very pleased to see two excellent submissions that were both correct as far as I could (more...)
A colleague asked me if I could help solve a problem in SQL, and I was grateful because I love solving problems in SQL! So we sat down, built a test suite, and worked together to solve it.
I will first present the problem and invite you to have a go at solving it for yourself; tomorrow I will post the solution we came up with. I think our solution is reasonably simple and elegant, (more...)
Over the past couple of weeks I've been preparing my slides and presentations for Oracle Open World (2015).
One thing that occurred to me was that there was no icon or image to represent Oracle SQL and PL/SQL. I needed something that I could include in my presentations to represent these.
After a bit of Tweeting it turns out that there is no (official) icons or images for Oracle SQL and Oracle PL/SQL.
So I (more...)
I think I’ve found an (admittedly obscure) bug with PL/SQL and/or SQL.
Have a look and see if you also think this is odd – or have I missed the totally obvious?
(This is all on 188.8.131.52)
Without going into the details (we would be here for hours if I did) I’m looking into the overhead of context switching between PL/SQL and SQL. It it fairly common knowledge that when you call (more...)
Es geht darum, dass für das Jahr 2014 drei Gruppen (mit einem Hash-Wert je Gruppe) gebildet werden müssen.
- 3 zufällig ausgewählte Bundesländer die mit B anfangen
- 3 zufällig ausgewählte Bundesländer die mit S anfangen
- 3 zufällig ausgewählte Bundesländer die NICHT mit B und S anfangen
Hier das dafür notwendige SQL:
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...)
Im folgenden demonstriere ich ein Beispiel um dieses Problem zu lösen.
Beispieldaten - Land und Ort:
ORDER BY 1,2;
Often, the biggest problem with regular expressions is that those who use them sometimes don’t use them correctly. A great example occurs in the Oracle Database with the
REGEXP_LIKE function. For example, some developer use the following to validate whether a string is a number but it only validates whether the first character is a number.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
DECLARE lv_input VARCHAR2(100 (more...)
Indexes are separate data structures that provide alternate pathways to finding data. They can and do generally speed up the processing of queries and other DML commands, like the
REPLACE INTO, and
DELETE statements. Indexes are also called fast access paths.
In the scope of the InnoDB Database Engine, the MySQL database maintains the integrity of indexes after you create them. The upside of indexes is that they can improve (more...)