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;
/
Posts
