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

Scripts to Download Oracle Database 18c Documentation

27 February 2018 Written by Christian Antognini

In the past I already shared with you the scripts I use to download the Oracle Database documentation. The aim of this short post is to reference the script I just wrote for 18c. download18.sh Happy downloading as well as happy reading! PS: I also refreshed the scripts for 12.1 and 12.2. Change 2020-02-09: removed […]

Read More 4 Comments

Activating and Deactivating Performance Feedback

26 September 2017 Written by Christian Antognini

Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration. The parameters that control performance feedback in 12c are the following: OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set […]

Read More 1 Comment