11gR2

randolf.geist's picture

Parallel Execution Analysis Using ASH - The XPLAN_ASH Tool

Preface

Note: This blog post actually serves three purposes:

  1. It introduces and describes my latest contribution to the Oracle Community,  the "XPLAN_ASH" tool

  • It accompanies a future OTN article on Parallel Execution that will be published some time in the future

  • It is supposed to act as a teaser for my upcoming "Parallel Execution Masterclass" that will be organized by Oracle University and can be booked later this year
  • Table Of Contents

    Introduction

    Real-Time SQL Monitoring Overview

    Real-Time SQL Monitoring Shortcomings

    Chris Antognini's picture

    Event 10046 – Full List of Levels

    Extended SQL trace (a.k.a. debugging event 10046 at a level higher than 1) is one of the key features provided by Oracle to troubleshoot applications using Oracle Database. For many years the available levels were always the same (4, 8 and 12). In fact, since I wrote my first paper about it in May 2000 and the release of 11g nothing changed.

    With 11g, as I described in this post, new levels (16 and 32) were introduced.

    randolf.geist's picture

    Forced Cursor Sharing And Virtual Columns

    So you have that application that cannot be changed but makes use of some weird expressions that screw up the cardinality estimates of the optimizer.

    Consider this simple example:

    create table t as select rownum as id , case when mod(rownum, 100000) = 0 then 'y' else 'n' end as flag , rpad('x', 100) as filler from dual connect by level <= 1000000 ; exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1 for columns flag size 2', estimate_percent => 30) select flag, count(*) from t group by flag; explain plan for select count(*) from t where flag = 'y' ; set linesize 200 tab off pagesize 0 select * from table(dbms_xplan.display);
    randolf.geist's picture

    Report Generators And Query Transformations

    Usually the Cost-Based Optimizer arrives at a reasonable execution plan if it gets the estimates regarding cardinality and data scattering / clustering right (if you want to learn more about that why not watch my Webinar available at "AllThingsOracle.com"?).

    Here is an example I've recently come across where this wasn't case - the optimizer obviously preferred plans with a significantly higher cost.

    The setup to reproduce the issue is simple:

    Chris Antognini's picture

    Display System Activity without Enterprise Manager

    I regularly use the system-level activity chart available in Enterprise Manager. In my opinion it is a simple and effective way to know how much a specific database is loaded at a specific time. This is for example an interesting way for observing how a specific load is processed (see this post for an example).
    Unfortunately it also happens that this possibility is not available. The main reasons I faced in the past are the following:

    oraclebase's picture

    Fedora 17 and Oracle 11gR2…

    Fedora 17 was released yesterday. I mentioned in a previous post I had run through the installation of Oracle 11gR2 on Fedora 17 alpha. With the arrival of the final Fedora 17 release I ran through the articles again last night to make sure everything was OK. You can see the finished versions here:

    As always, installing Oracle on Fedora 17 is just for fun and totally not supported. For anything proper you should be using Oracle Linux or RHEL.

    Cheers

    Tim…

    randolf.geist's picture

    Nested Loop Join Costing

    The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times.

    In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality of the driving row source.

    Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource)

    Obviously there are cases where Oracle has introduced refinements to the above formula where this is no longer true. Here is one of these cases that is probably not uncommon.

    randolf.geist's picture

    Column Groups - Edge Cases

    Oracle 11g added Extended Statistics support for column groups in order to detect correlated columns for filter predicates using an equal comparison.

    Note that Oracle 11g also added the ability to use the number of distinct keys of a composite index as an upper limit for the cardinality estimates for matching column predicates, which means that the optimizer is now capable of detecting correlated columns without the explicit addition of Extended Statistics / Column Groups.

    Chris Antognini's picture

    COMMIT_WAIT and COMMIT_LOGGING

    Recently I used the COMMIT_WAIT and COMMIT_LOGGING parameters for solving (or, better, working around) a problem I faced while optimizing a specific task for one of my customers. Since it was the first time I used them in a production system, I thought to write this post not only to shortly explain the purpose of the these two parameters, but also to show a case where it is sensible to use them.

    The purpose of the two parameters is the following:

    COMMIT_WAIT

    randolf.geist's picture

    Coalesce Subquery Transformation - COALESCE_SQ

    Oracle 11.2 introduced a set of new Query Transformations, among others the ability to coalesce subqueries which means that multiple correlated subqueries can be merged into a number of less subqueries.

    Timur Akhmadeev already demonstrated the basic principles in a blog entry, but when I was recently involved into supporting a TPC-H benchmark for a particular storage vendor I saw a quite impressive application of this optimization that I would like to share here.

    To prevent automated spam submissions leave this field empty.
    Syndicate content