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. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: