rmoff

March 9, 2010

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.

Filed under: oracle, performance — rmoff @ 16:29

I’ve been playing around with SQL Tuning Sets, and was trying to clear up my mess.

To list all the tuning sets:

SET WRAP OFF
SET LINE 140
COL NAME FOR A15
COL DESCRIPTION FOR A50 WRAPPED

select name,created,last_modified,statement_count,description 
from DBA_SQLSET
NAME            CREATED   LAST_MODI STATEMENT_COUNT DESCRIPTION
--------------- --------- --------- --------------- ----------------------------------------------------------------------------------------
sts_test_02     09-MAR-10 09-MAR-10               1 Test run 1
sts_test_01     12-FEB-10 12-FEB-10               1 an old STS test test test 

To delete a tuning set:

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'sts_test_01');
END;

But you may hit this message:

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.
Cause: The user attempted to update an active SQL Tuning Set.
Action: Remove all reference to the SQL Tuning Set and retry the operation.

For example: ORA-13757: “SQL Tuning Set” “sts_test_01″ owned by user “badger” is active.

Error code reference

To look up why the STS is considered active, check the SQL Tuning Information Views, in this case DBA_SQLSET_REFERENCES

SET WRAP OFF
SET LINE 140
COL NAME FOR A15
COL DESCRIPTION FOR A50 WRAPPED

select description, created, owner 
from DBA_SQLSET_REFERENCES 
where sqlset_name = 'sts_test_01';

which in my case showed this:

DESCRIPTION                                        CREATED   OWNER
-------------------------------------------------- --------- ------------------------------
created by: SQL Tuning Advisor - task: RNM_TT      12-FEB-10 badger

So we check for this on DBA_ADVISOR_TASKS:

SET WRAP OFF
SET LINE 140
COL NAME FOR A15
COL OWNER FOR A10
COL DESCRIPTION FOR A50 WRAPPED

select owner,description, created,last_modified 
from DBA_ADVISOR_TASKS 
where task_name = 'RNM_TT'

and it shows this:

OWNER      DESCRIPTION                                        CREATED   LAST_MODI
---------- -------------------------------------------------- --------- ---------
badger   SQL Advisor - sts_test_01                           12-FEB-10 12-FEB-10

So now we know it’s a stale SQL Tuning Advisor task that uses the SQL Tuning Set, and I definitely want to delete it:

BEGIN
  DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'RNM_TT');
END;

and then I can delete my original SQL Tuning Set:

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'sts_test_01');
END;

All done :)

About these ads

3 Comments

  1. Information of SQL Tuning Set is very good and thanks for sharing.

    Comment by Jack Nicholson — April 12, 2010 @ 07:10

  2. Thanks for this explanation, solved my problem.
    Well written also.

    Comment by Johan E — October 13, 2011 @ 08:59

  3. Thank you for your post… I have add the following to my own collection of “how to” snippets…

    Determining cause of: ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.

    SELECT dsr.sqlset_name,dat.*
    FROM dba_advisor_tasks dat
    ,dba_sqlset_references dsr
    WHERE INSTR(dsr.description,dat.task_name)>0
    ORDER BY dat.created DESC;

    –cleaning them up
    <>
    BEGIN
    dbms_sql_tune.drop_tuning_task(task_name =>’&TaskName’);
    dbms_sql_tune.drop_sqlset (sqlset_name=>’&SQLsetName’);
    END wrapper;–block
    /

    Comment by H.Schuerger — March 20, 2012 @ 19:58


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: