Here it is the code I am using..
---------------------------
alter SYS flush shared_pool;
BEGIN
dbms_sqltune.create_sqlset('STS_5', 'JOBNAME_STS Test Tuning Set', 'SYS');
END;
/
BEGIN
dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name=>'STS_5',
time_limit=>60,
repeat_interval=>1,
capture_option=> 'MERGE',
capture_mode=> dbms_sqltune.MODE_ACCUMULATE_STATS ,
basic_filter=> 'PARSING_SCHEMA_NAME IN (''PROD_CL'')',
sqlset_owner=>'SYS',
recursive_sql=> dbms_sqltune.HAS_RECURSIVE_SQL
);
END;
/
exit;
--------------------------
Ran some SQLs from sqlplus command prompt.
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('STS_5', sqlset_owner=>'SYS') ) ;
when displaying, the result shows below repeated queries that started from sqlplus@host (TNS V1-V3) module in addition to the sqls parsing schema executed from sqlplus.
HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL did not help..
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM DUAL
BEGIN DBMS_OUTPUT.DISABLE; END;
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (USER LIKE USERID)
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((USER LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES')
SELECT DECODE('A','A','1','2') FROM DUAL
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
May i know what exactly the module that has (TNS V1-V3) and how can I restrict above shown SQLs from storing in tuning set which have not been executed by parsing schema.
parsing schema may issue SQLs from sqlplus, sql developer, jdbc application, etc..