*******************************************************
* Description: Knowledge of Oracle SQL Tuing Set [STS]
* Date: 06:24 PM EST, 06/24/2018
*******************************************************

		 
<1> Oracle SQL Tuning Sets:
     |
     |__ o. A SQL tuning set (STS) is a database object that you can use as input to tuning tools. An STS includes:
         |
         |__ o. A set of SQL statements.
         |
         |__ o. Associated execution context, such as user schema, application module name and action, list of bind values, and the environment for SQL compilation of the cursor.
         |
         |__ o. Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, 
         |      the number of complete executions, optimizer cost, and the command type.
         |
         |__ o. Associated execution plans and row source statistics for each SQL statement (optional).
	 
	 
	 

	 
	 
<2> Create SQL Tuning Set:
     |
     |__ SQL> begin
                     dbms_sqltune.create_sqlset(sqlset_name=>'ISSUED_SQL', description=>'SOURCETONET LONG RUNNING SQL');
              end;
              /
			  
			  
			  
			  
			  
			  
<3> Loading SQL metadata into STS:
     |
     |__ SQL> declare
                     cur sys_refcursor;
              begin
                     open cur for
                     select value(P) from table(
                     	     dbms_sqltune.select_workload_repository(begin_snap=>65595, end_snap=>65775, basic_filter=>'sql_id = ''dkj34tdtbhmfp''',attribute_list=>'ALL'))P;
                     	     dbms_sqltune.load_sqlset(sqlset_name=>'ISSUED_SQL', populate_cursor=>cur);
                     close cur;
              end;
              /			  
			  
			  
			  
			  


<4> Review STS metadata:
     |
     |__ SQL> select first_load_time, executions as execs, parsing_schema_name, elapsed_time / 1000000 as elapsed_time_secs, buffer_gets, disk_reads, 
              direct_writes, rows_processed, fetches, optimizer_cost, plan_hash_value, sql_id
              from table(dbms_sqltune.select_sqlset(sqlset_name=>'ISSUED_SQL'));

			  
              FIRST_LOAD_TIME   EXECS PARSING_SCHEMA_NAME  ELAPSED_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES OPTIMIZER_COST PLAN_HASH_VALUE SQL_ID
              ---------------- ------ -------------------- ----------------- ----------- ---------- ------------- -------------- ---------- -------------- --------------- -------------
                                    1 REPORTVIEW                  1248.39268      841708     384134       323                118          1            509      3086611361 dkj34tdtbhmfp

			  
	 
	 
	
<5> Reference:
     |
     |__ o. https://docs.oracle.com/database/121/TGSQL/tgsql_sts.htm#TGSQL517