Where's Waldo? Using a brute-force approach to find an Execution Plan the CBO hides

    Session Number: 1282
    Track: 5-Data Warehousing and SQL
    Session Type: Best Practices, Tips & Tricks
    Primary Presenter: Carlos Sierra [Consultant - Accenture Enkitec Group]
    Time: Jan 27, 2016 (02:20 PM - 03:10 PM)

    Session level: Intermediate
    Keywords: None

    Abstract (250 words or less): 

    A SQL suddenly changing its Execution Plan happens to many DBAs, so you are not alone. As you investigate what happened, you might discover this SQL hasn't made a dent on AWR since it usually executes too fast. You discard importing a good plan using a SQL Profile or capturing one using SQL Plan Baseline because the good plan is nowhere to be found! You try SQL Tuning advisor and it generates a plan that performs even worse! Call Support? You know that yours is a custom SQL and you will get a typical cold answer. Your buddies are in Hawaii, so you cannot reach them for a few days. You are tempted to update your resume, but you have been there for only 3 months...

    Tuning a SQL statement with some sort of trial an error approach is common for novice DBAs, but this method is highly discouraged for serious DBAs since there are others ways to do what is right. Still, on some rare cases you may want to try your luck on a test environment and see if you can find a better performing plan by simply trying things out. The problem is that you have so many things to try, and you want to be non-intrusive to the environment, so peppering your database with indexes is out of the question. 

     This session is for experienced DBAs that may need to explore creative ways to find an elusive Execution Plan.



    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    Copyright © 1993-2017 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy