Optimizer statistics are essential for good execution plans and fast performance of the SQL queries. Of course, this is also the case in the Autonomous Data Warehouse Cloud. But the handling of gathering statistics is slightly different from what we know from other Oracle databases.
In Oracle Autonomous Data Warehouse Cloud, External Tables can be used to read files from the cloud-based Object Storage. But take care to do it the official way, otherwise you will see a surprise, but no data.
Since Oracle 12c, the query optimizer is able to change execution plans at runtime. This feature is called “Adaptive Plans”. Something similar happened on the ODTUG Community Service Day at the Kscope conference in Orlando.
Global temporary tables (or private temporary tables, if you use Oracle 18c) are a practical feature to be used for SQL queries with multiple steps, for example complex reports in a data warehouse. If you want to use them, some details must be considered.
Complex SQL statements are hard to understand – not only for us, but for the query optimizer, too. Sometimes, it is a good idea to write a simpler SQL query to do the same work. Here a nice story about a performance improvement by rewriting the SQL statement.
I just wanted to write a small demo script for Auto-List Partitioning in Oracle 12.2. Instead, I spent an entire evening finding a bug in the Oracle data dictionary. Fortunately, it’s not a dramatic one.
Direct-Path INSERT is a very efficient way to load data into data warehouse tables. The load performance can even be improved with NOLOGGING on the target table. In Oracle 12c Release 2, this is even possible for Oracle Data Guard environments. But is it a good idea?
Turn of the year is always a good time to look back in history. In this first blog post of the new year, I want to tell you the story of my very first “data warehouse” project. A not very serious blog post with some serious recommendations in the end.
Incremental statistics reduce the time to gather global statistics for a partitioned table. Really? In some situations it may happen that incremental statistics slow down statistics calculation dramatically. An example of a real project in Oracle 12.1 and how it can be improved with Oracle 12.2.
For the first time, I had the chance to attend and speak at the UKOUG Technology Conference. In these three days at UKOUG Tech17 in Birmingham, I attended some very good sessions, had many interesting discussions about Oracle databases, data warehousing and performance tuning, met nice people and tasted a lot of chocolate.