Top 60 Oracle Blogs

Recent comments


Recursive queries on IM_DOMAIN$ at each cursor execution

At POUG 2018 conference I explained join methods by putting gdb breakpoints on the qer (Query Execution Rowsource) functions that are behind the execution plan operations. I was a bit annoyed by several calls when running a Hash Join because of recursive, internal queries on the dictionary. There are a lot of queries on the dictionary during hard parse, but this was at execution time on a query that had already been parsed before. This is new in 12.2 and seems to be related to In-Memory Global Dictionary Join Groups feature, the execution checking and setting up the Join Group aware Hash Join.

However, I must mention that even if this seems to be related with In-Memory I don’t have it enabled here:

Subquery Order

From time to time I’ve wanted to optimize a query by forcing Oracle to execute existence (or non-existence) subqueries in the correct order because I know which subquery will eliminate most data most efficiently, and it’s always a good idea to look for ways to eliminate early. I’ve only just discovered (which doing some tests on 18c) that Oracle introduced the /*+ order_subq() */ hint that seems to be engineered to do exactly that.

Here’s a very simple (and completely artificial) demonstration of use.

ASM Filter Driver — simple test on filtering

Here is a simple test on ASM Filter Driver showing that when filtering is enabled the disks presented to ASM are protected from external writes.

On a 12.2 Grid Infrastructure installation I have my disks labeled with ASM Filter Driver (AFD):

Download your SR content from MOS

You may want to keep track of your Oracle Service Requests offline. Or simply be able to read them as simple text. Here is a simple way to download all of them to a simple text file.

First, it is easy to get a list of the Service Requests as an Excel file. Just list them on the GUI. You may choose:

  • The Support Identifiers (CSI) on the right
  • Only SR where you are primary contact or all of them, with the ‘person’ icon
  • Include closed SRs with the red check icon
  • The columns: View -> Columns -> Show all

And then View -> Export to XLS

The service_requests.xls is actually in XML format which is easy to parse, but you can also convert it to CSV. Here I have saved it to service_requests.csv

Then with AWK and LYNX installed here is how to get each SR into text:


By Franck Pachot

COMMIT is the SQL statement that ends a transaction, with two goals: persistence (changes are durable) and sharing (changes are visible to others). That’s a weird title and introduction for the 499th blog post I write on the dbi-services blog. 499 posts in nearly 5 years- roughly two blog posts per week. This activity was mainly motivated by the will to persist and share what I learn every day.

Error Logging

Error logging is a topic that I’ve mentioned a couple of times in the past, most recently as a follow-up in a discussion of the choices for copying a large volume of data from one table to another, but originally in an addendum about a little surprise you may get when you use extended strings (max_string_size = EXTENDED).

Descending bug

Following on from Monday’s posting about reading execution plans and related information, I noticed a question on the ODC database forum asking about the difference between “in ({list of values})” and a list of “column = {constant}” predicates connected by OR. The answer to the question is that there’s essentially no difference as you would be able to see from the predicate section of an execution plan:

A tribute to Natural Join

By Franck Pachot

I know that lot of people are against the ANSI join syntax in Oracle. And this goes beyond the limits when talking about NATURAL JOIN. But I like them and use them quite often.

Masterclass – 1

A recent thread on the Oracle developer community database forum raised a fairly typical question with a little twist. The basic question is “why is this (very simple) query slow on one system when it’s much faster on another?” The little twist was that the original posting told use that “Streams Replication” was in place to replicate the data between the two systems.

The size of Oracle Home: from 9GB to 600MB

By Franck Pachot

This is research only and totally unsupported. When building docker images to run Oracle Database in a container, we try to get the smallest image possible. One way is to remove some subdirectories that we know will not be used. For example, the patch history is not used anymore once we have the required version. The dbca templates can be removed as soon as we have created the database… In this post I take the opposite approach: run some workload on a normal Oracle Home, and keep only the files that were used.

I have Oracle Database 18c installed in /u00/app/oracle/product/18EE and it takes 9GB on my host:

[oracle@vmreforatun01 ~]$ du --human-readable --max-depth=1 $ORACLE_HOME | sort -h | tail -10
352M /u00/app/oracle/product/18EE/jdk
383M /u00/app/oracle/product/18EE/javavm
423M /u00/app/oracle/product/18EE/inventory