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

Does the Parse Time Increase Linearly with the Number Of Child Cursors?

24 October 2012 1 Comment Written by Christian Antognini

In the last post I discussed a test case generating lot of child cursors. Today I wanted to show you, for the very same test case, that in 11.2 the parse time might increases linearly with the number of child cursors per parent cursor. This is the expected behavior. In fact, to check whether an already available child cursor can be reused, the list of child cursors must be scanned. And, in case no one of the already available child cursors is compatible, every entry needs to be probed.

Note that to generate the previous chart I simply run, in 11.2.0.2, the PL/SQL block of the previous post while SQL trace was enabled. Then I extracted from the trace file the elapsed time for every parse operation and loaded the values in Excel.

In 11.2.0.3, because of the artificial limitation of the number of child cursors per parent cursor, the parse time is almost constant. Notice, however, that in this case the number of child cursors is spread across a lot of parent cursors, not a single one as in 11.2.0.2.

11gR2, Library Cache, Oracle Database
How Many Children Can a Parent Cursor Have? 1,000,000?
Ad: CBO Days 2012 – Final Agenda

1 Comment

  1. Marcus Mönnig Marcus Mönnig
    24 October 2012    

    Looks like there is an index missing on the column(s) that define if a child cursor can be reused (optimizer environment). But on the other hand, the high number of child cursors that would justify the index is the actual problem.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.