******************************************************* * 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
Your Comments