-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|