DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: loading sql plans from sql tuning set

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    loading sql plans from sql tuning set

    Anybody have any ideas or successfully used this package?

    sqlplus '/ as sysdba'

    SQL> select name from dba_sqlset;
    NAME
    ------------------------------
    10g Pre Upgrade STS
    CS_Exact
    CS_ExactComp
    CS_LoadTest1
    CS_LoadTest2
    CS_LoadTest3


    select sql_id, sql_text from dba_sqlset_statements where SQLSET_NAME = 'CS_ExactComp'

    SQL_ID
    -------------
    SQL_TEXT
    --------------------------------------------------------------------------------

    74n1vkb82g79s
    update "MTASOTAPA"."OTAPA_TRAN_HDR" t set "LAST_OTAPA_COMMAND"=:V001,"DONE_DAT

    ....
    ....
    ....
    ....

    209 rows

    /*
    ** Verify the number of rows in the table
    */

    SQL> select count(1) from dba_sql_plan_baselines;
    COUNT(1)
    ----------
    0


    set serveroutput on
    DECLARE
    l_plans_loaded PLS_INTEGER;
    BEGIN
    l_plans_loaded := DBMS_SPM.load_plans_from_sqlset
    (sqlset_name => 'CS_ExactComp');

    DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
    END;
    /
    Plans Loaded: 0


    Next test
    =======

    set serveroutput on
    DECLARE
    l_plans_loaded PLS_INTEGER;
    BEGIN
    l_plans_loaded := DBMS_SPM.load_plans_from_sqlset
    (
    sqlset_name => 'CS_ExactComp',
    basic_filter => 'sql_id="74n1vkb82g79s"',
    enabled => 'NO'
    );

    DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
    END;
    /

    ERROR at line 1:
    ORA-13761: invalid filter
    ORA-06512: at "SYS.DBMS_SPM", line 2101
    ORA-06512: at line 4

  2. #2
    Join Date
    Jul 2006
    Posts
    195
    FYI, I found my issue for those of you who are interested.

    The sql plans were not captured when the tuning set was created. That happens if the tuning set is created with the select_workload_repository function whose attribute_list parameter is set to BASIC , which is the case by default. This parameter should be set to TYPICAL or ALL to get the sql plans and then they can be loaded.

Posting Permissions

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


Click Here to Expand Forum to Full Width