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

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 […]

12cR2, 18c, Documentation, TKPROF
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 […]

18c, Documentation
Read More 4 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 […]

12.1.0.2, 12cR2, Multitenant, Object Statistics, Query Optimizer
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 […]

10.1, 12cR2, 8.0.3, MySQL, Oracle Database, PostgreSQL, Query Optimizer
Read More 7 Comments

Purging Unused Triples Is a Never-ending Operation

18 December 2017 Written by Christian Antognini

This is a short post to point out a problem I recently discovered and for which I couldn’t find information online. Hence, in case you hit it, thanks to this short post I hope that you’ll save some time. The documentation describes a procedure you can use to get rid of dangling triples stored in […]

12.1.0.2, Bug, RDF Semantic Graph
Read More Leave a Comment

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 […]

12.1.0.2, 12cR2, Active Session History (ASH), Adaptive Plans, Bug
Read More 1 Comment

SPD State Does Not Change If Adaptive Statistics Are Disabled

28 November 2017 Written by Christian Antognini

The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD). As of version 12.1.0.2, an SPD has two state information. You can see both of them through […]

12.1.0.2, 12cR2, Adaptive Query Optimization, Bug, Object Statistics, Query Optimizer, SQL Plan Directives
Read More 4 Comments

The APPROX_MEDIAN Function – A Test Case

30 October 2017 Written by Christian Antognini

The aim of this post is not to explain how the APPROX_MEDIAN function works (you find basic information in the documentation) but to show you the results of a test case I ran to assess how well it works. Here’s what I did… I started in the Oracle Database Public Cloud an instance of version […]

12cR2, Approximate Query Processing, Oracle Cloud
Read More Leave a Comment

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 […]

12cR1, 12cR2, 18c, Adaptive Query Optimization, Parallel Processing, Statistics Feedback
Read More 1 Comment

Offline Analysis of ASH Data with ASHDUMP

6 July 2017 Written by Christian Antognini

From time to time, it happens to me to carry out offline analyses of ASH data. For that, I mean to analyze the ASH data without having access to the database instance that generated it. For that purpose, Oracle Database provides the possibility to dump the content of the ASH buffer as well as information […]

11gR2, 12cR1, 12cR2, Active Session History (ASH)
Read More 1 Comment
Newer Entries
Older Entries