Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Creating a SQL Patch with Many Hints Requires a Hack

7 February 2022 Written by Christian Antognini

In the past, when I created a SQL patch, I always specified a small number of hints. Last week, for the first time, I created one with more than 100 of them. Given their number, I didn’t want to specify them manually. Instead, my goal was to create a SQL patch that contained the outline […]

Read More 7 Comments

AWR: Multitenant-Specific Initialization Parameters

9 September 2020 Written by Christian Antognini

By default, the database engine automatically takes snapshots in the root container only. Such snapshots cover the root container as well as all open PDBs belonging to it. From version 12.2 onward, you can control whether the database engine automatically takes also PDB-level snapshots through the dynamic initialization parameter AWR_PDB_AUTOFLUSH_ENABLED. In case you want to […]

Read More 1 Comment

AWR Flush Levels

31 August 2020 Written by Christian Antognini

From version 12.1.0.2 onward, for taking AWR snapshots, you have the choice between four AWR flush levels: BESTFIT, LITE, TYPICAL and ALL. If you check the Oracle Database documentation, you won’t find much information about the difference between them. The best you will find, in the PL/SQL Packages and Types Reference, is the following: The […]

Read More Leave a Comment

MIN/MAX Optimization and Asynchronous Global Index Maintenance

28 June 2019 Written by Christian Antognini

In this short post I would like to point out a non-obvious issue that one of my customers recently hit. On the one hand, it’s a typical case where the query optimizer generates a different (suboptimal) execution plan even though nothing relevant (of course, at first sight only) was changed. On the other hand, in […]

Read More 1 Comment

V$SQL_CS_HISTOGRAMS: What Are the Buckets’ Thresholds?

16 April 2019 Written by Christian Antognini

The contents of the V$SQL_CS_HISTOGRAM view is used by the SQL engine to decide when a cursor is made bind aware, and therefore, when it should use adaptive cursor sharing. For each child cursor, the view shows three buckets. It is of general knowledge that the first one (BUCKET_ID equal 0) is associated with the […]

Read More 2 Comments

Partition-Wise Operations – New Features in 12c and 18c

17 May 2018 Written by Christian Antognini

Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c. The new features […]

Read More 4 Comments

TKPROF’s Argument PDBTRACE

21 March 2018 Written by Christian Antognini

If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them (here the output generated by version 18.1.0): $ tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema.tablename Use ‘schema.tablename’ with ‘explain=’ option. explain=user/password Connect […]

Read More 5 Comments

CDB Views and Query Optimizer Cardinality Estimations

18 January 2018 Written by Christian Antognini

Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post. First of all, when checking the execution plan of a query already running for more […]

Read More 2 Comments

How Well a Query Optimizer Handles Subqueries?

31 December 2017 Written by Christian Antognini

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence: Simple sub-queries that are not correctly optimized […]

Read More 7 Comments

SQL_ADAPTIVE_PLAN_RESOLVED Is Broken

11 December 2017 Written by Christian Antognini

You can use the V$SQL.IS_RESOLVED_ADAPTIVE_PLAN column to know whether the execution plan associated to a child cursor is adaptive or not. Specifically, to know whether the query optimizer selected either an adaptive join method or an adaptive star transformation (notice that it is not set when the hybrid hash distribution is involved). The column takes […]

Read More 1 Comment
Older Entries