Sunday, January 29, 2012

How to rename SQL Tuning Set ( STS )

Rename SQL Tuning Set‎( STS )

We can use SQL Performance Analyzer (SPA) to evaluate impact on SQLs of 11g R2 database upgrade from 10.2 .So created some demo , example ( with full code ). EM was very good option , but still preferred sql,pl-sql, command / api.

We all know to, use SPA for 10.2 to 11.2 database upgrade , we need one intermeiate 11g DB.
So we can run trial remotely.So we collected SQL Tuning Set (STS ) ( using demo code ) from Multiple databases and import on intermediate 11g DB. Problem started when unpacking STS, collected from different production / Test environment having same name because all
used demo code :).

So started looking for solution , is there any way to rename STS !!!! and end up with following code

-- Create new STS and copy from exiting one
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
-- Create STS
DBMS_SQLTUNE.create_sqlset(sqlset_name => 'STS_DBNAME_CMUL',description => 'new STS SQL tuning set from XYZ DB');
OPEN l_cursor FOR
SELECT VALUE(r)
FROM TABLE (DBMS_SQLTUNE.select_sqlset (
'STS_RAG_CMUL' -- old sqlset_name which need to rename
)
) r;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_DBNAME_CMUL',
populate_cursor => l_cursor);
-- Drop STS
dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
close l_cursor;
END;
/

1 comment:

  1. It's very important information and it will be helpful for app development. because with the help of this, the app developer can do easy work and it's very helpful for developers. But if you don't know about this, So for understanding, We can help you. Because I have used this technology in my work. Because I am a app developer and I am doing work in this company for a long time. If you want to get good information devops engineer aus der ukraine so you are on a good platform. By the way, Most people haven't any idea about this. But I have good knowledge about this, I can clarify all the important points of this engineer . If you want to go very deep in this, So I can give you a site link and it will be easy for you. because on this site, you can read all information about this. I hope so, you will focus on this.

    ReplyDelete

Google