Search

Top 60 Oracle Blogs

Recent comments

11gR2

How Many Children Can a Parent Cursor Have? 1,000,000?

The patch set 11.2.0.3 includes a fix for bug# 10187168 which, in reality, is an enhancement request. Its purpose is to artificially limit the number of child cursors that a parent cursor can have. The concept is quite easy: when a parent cursor reaches _cursor_obsolete_threshold (default value is 100) child cursors the parent cursor is obsoleted and, as a result, a new one is created.

So, as of 11.2.0.3 (or with some PSUs and bundle patches), the answer to the question is: 100.

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

    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.

    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);

    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:

    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:

    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…

    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.

    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.

    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