Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2013
    Posts
    3

    Unanswered: Implementing SQL Plan Management on Oracle Database 11.2.0.2

    Environment:

    Oracle Linux 5 update 10 (UEK)
    Oracle GI 11.2.0.2.0 (Oracle ASM 11.2.0.2)
    Oracle Database 11.2.0.2.0 Enterprise Edition with RAC option (3 nodes)
    No PSU applied/CPU July 2013 applied to RDBMS
    Database servicing Siebel CRM 8.1.1.1 Application that uses bind peeking.
    Siebel CRM soon to be version 8.1.1.11



    There are a few bugs for SQL Plan Management (SPM) on 11.2.0.2 (see below). The slowness and non-peeked binds issues seem very problematic. I've seen a few bloggers recommend to not use SPM unless your at Oracle Database 12c. Several of the bugs seemed to be fixed in 11.2.0.3 but we don't have any plans to move to 11.2.0.3 since we'll most likely be at 12.1.0.x in 7 months. Any recommendation from the community on whether I should capture and use SQL Plan Management with 11.2.0.2? Why, why not? If there's other relevant information needed, let me know.



    Bug 9910484 - SQL Plan Management Capture uses excessive space in SYSAUX (Doc ID 9910484.8)

    Affects:
    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1
    Versions confirmed as being affected

    11.2.0.2

    11.2.0.1

    11.1.0.7

    Platforms affected Generic (all / most platforms affected)

    Fixed:

    12.1.0.1 (Base Release)

    11.2.0.3 (Server Patch Set)

    11.2.0.2 Patch 4 on Windows Platforms

    11.1.0.7 Patch 41 on Windows Platforms


    Bug 11719151 - SQL Plan Management capture causes slowness (Doc ID 11719151.8)

    Affects:
    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions >= 11.2.0.2 but BELOW 12.1
    Versions confirmed as being affected

    11.2.0.2

    Platforms affected Generic (all / most platforms affected)

    Fixed:

    12.1.0.1 (Base Release)

    11.2.0.3 (Server Patch Set)

    11.2.0.2 Patch 22 on Windows Platforms


    Bug 9942454 - DBMS_SPM.LOAD_PLANS_FROM_SQLSET gets XML parsing error (Doc ID 9942454.8)

    Affects:
    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions BELOW 12.1
    Versions confirmed as being affected

    11.2.0.2

    11.2.0.1

    11.1.0.7

    Platforms affected Generic (all / most platforms affected)

    Fixed:

    12.1.0.1 (Base Release)

    11.2.0.3 (Server Patch Set)


    Bug 12732879 - Execution Plan of Query with non-peeked binds is not reproducible (Doc ID 12732879.8)

    Affects:
    Product (Component) Oracle Server (Rdbms)
    Range of versions believed to be affected Versions >= 9.2 but BELOW 12.1
    Versions confirmed as being affected

    11.2.0.3

    11.2.0.2

    11.1.0.7

    Platforms affected Generic (all / most platforms affected)

    Fixed:

    12.1.0.1 (Base Release)

    11.2.0.4 (Future Patch Set)


    Thanks so much.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    And what is your beef?
    You know the bugs and you know the patches ... So again: What is your beef?
    If you use it, you gain. Don't use it -- you lose.

    For us it has been a crucial tuning component due to the bad sql being submitted by the main application's "code generator".

    SPM has been our salvation.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2013
    Posts
    3

    No Beef - Just Checking Perspectives

    No Beef. I'd like to get some perspectives on others experience on my version and platform with SPM. For example, slowness crippling or ignored. I read the literature/documentation so I do understand your thinking on its potential benefits.

    Would you turn on SPM auto capture in Production or would you only import baselines into Production from Test.

    Thanks for your perspective.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    This works for us:

    cursor_sharing = FORCE #<<<-- Beware of this one!
    optimizer_dynamic_sampling = 0
    optimizer_capture_sql_plan_baselines = FALSE
    optimizer_use_sql_plan_baselines = TRUE
    query_rewrite_enabled = TRUE

    accepted.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2013
    Posts
    3

    Capturing Plans and Environment?

    We use a Siebel Application so some of the init parameters are dictated.

    How do you Capture SQL Plan with auto capture off? All manual. Prod and Test the same?

    Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Yes, use DBMS_AUTO_SQLTUNE.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •