Oracle SQL Tuning Set

1. New tuning Set Object ***************************************** ---Authorize grant administer any SQL TUNING SET to Scott;---Delete the existing stsbegin Dbms_sqltune. Drop_sqlset (sqlset_name = ' ocpyang_sts '); end;/---new Stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/---View sqlsetselect owner, name, ID, created, statement_count from dba_sqlset;**************************** the database has been created 2. View AWR resource-intensive SQL statements ************************************************************- --2.1 Viewing available snapshot ranges Select snap_id, Instance_number, End_interval_timefrom dba_hist_snapshotorder by snap_id;---2.2 View the disk usage between snapshot number 820-840 Sqlselect sql_id,substr (sql_text,1,100), Disk_reads, Cpu_time, Elapsed_timefrom table (dbms_ Sqltune. Select_workload_repository (820,841, NULL, NULL, ' disk_reads ', NULL, NULL, NULL, ten)) ORDER by Disk_reads DESC;--- 2.3 views Sqlselect sql_id, substr (sql_text,1,100), Disk_reads, Cpu_time, Elapsed_time, Parsing_schema_namefrom, not resolved by SYS user Table (Dbms_sqltune. Select_workload_repository (820,841, ' parsing_schema_name <> ' SYS ', NULL, null,null,null, 1, NULL, ' all '));--- 2.4 View snapshot number 820-840 between non-SQL user sort Top 10 sqlselect sql_id, substr (sql_text,1,100), Disk_reads, Cpu_time, Elapsed_time, Buffer_ Gets, Parsing_schema_namefrom table (Dbms_sqltune. Select_workload_repository (Begin_snap = 820,end_snap = 841,basic_filter = ' Parsing_schema_name <> ' SYS ", Ranking_measure1 = ' buffer_gets ', Result_limit = 10)); COL bsnap new_value begin_snapcol esnap new_value end_snap--select MAX (snap_id) bsnapfrom dba_hist_snapshotwhere begin_ Interval_time < Sysdate-7;--select MAX (snap_id) esnapfrom dba_hist_snapshot;--col sql_text FORMAT A40COL sql _id format a15col parsing_schema_name format a15col cpu_seconds format 999,999,999,999,999set LONG 10 LINES PAGES Trimspool ON--select sql_id, Sql_text,disk_reads, Cpu_time cpu_seconds, Elapsed_time, buffer_gets, Parsing_schema_nameFROM table ( Dbms_sqltune. Select_workload_repository (Begin_snap = &begin_snap,end_snap = &end_snap,basic_filter = ' parsing _schema_name <> ' SYS ', ranking_measure1 = ' cpu_time ', result_limit = 10); *************************** 3. Populate the optimization set with high-resource-consuming SQL in AWR: ***************************************************** ---3.1 New stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/---3.2 View awr snapshot starting and ending select snap_id, Begin_interval_timefrom dba_hist_snapshot ORDER by 1;--- 3.3 Use the high-resource SQL in AWR to populate the SQL optimization set declare test_cur dbms_sqltune.sqlset_cursor; BEGIN OPEN test_cur for SELECT value (x) from table (Dbms_sqltune.select_workload_repository (820,841, NULL, nul  L, ' disk_reads ', NULL, NULL, NULL, +) x; --Dbms_sqltune.load_sqlset (SqlsEt_owner = ' SCOTT ', sqlset_name = ' ocpyang_sts ', populate_cursor = test_cur); end;/*------------------Common Error 1th error: ORA-13774: Insufficient permissions to select data ORA-06512 from the workload repository: in the SYS. Dbms_sqltune ", line 4715ora-06512: Use the SYS account in line 10, in Dbms_sqltune. LOAD_SQLSET Specifies Sqlset_ownerdbms_sqltune. Load_sqlset (Sqlset_name in VARCHAR2, populate_cursor in Sqlset_cursor, load_option in VARCHAR2: =  ' INSERT ', update_option in VARCHAR2: = ' REPLACE ', update_condition in VARCHAR2: = NULL, update_attributes in      VARCHAR2: = null, ignore_null in BOOLEAN: = TRUE, commit_rows in POSITIVE: = null, Sqlset_owner In VARCHAR2: = NULL);-----------------------------*---3.4 View optimization set Trust information Select Sqlset_name, Elapsed_time,cpu_time, Buffer_ Gets, disk_reads, sql_textfrom dba_sqlset_statementswhere sqlset_name = ' ocpyang_sts '; ****************************** 4. View in-memory resource-intensive sql************************************************************---4.1 Syntax Dbms_sqltune. Select_cursor_cache (basic_filter in VARCHAR2: = null, object_filter in VARCHAR2: = null, Ranking_mea Sure1 in VARCHAR2: = null, ranking_measure2 in VARCHAR2: = null, ranking_measure3 in VARCHAR2: = NULL, R Esult_percentage in Number: = 1, result_limit in number: = NULL, attribute_list in VARCHAR2: = N ULL) RETURN Sys.sqlset pipelined;---4.2 Select from memory read disk more than 1000000 select sql_id, substr (sql_text,1,20), Disk_reads,cpu_time, Elapsed_time,buffer_gets, Parsing_schema_namefrom table (Dbms_sqltune. Select_cursor_cache (' disk_reads>1000000 ')) Order by sql_id;---4.3 view memory of non-SYS account users with the longest CPU time of 10 queries select sql_id, substr (sql_text,1,120), Disk_reads,cpu_time, elapsed_time,buffer_gets, Parsing_schema_namefrom table (DBMS_SQLTUNE. Select_cursor_cache (basic_filter = ' parsing_schema_name <> ' SYS ', ranking_measure1 = ' cpu_time ', RESULT_LIMIT);---4.4 View non-SYS accounts in memory run a select sql_id that returns more than 1 seconds, substr (sql_text,1,120),Disk_reads, Cpu_time, Elapsed_timefrom table (Dbms_sqltune. Select_cursor_cache (' parsing_schema_name <> ' SYS ' and elapsed_time ; 1000000 ') ORDER by sql_id;---4.5 View specific sql_id execution details Select *from table (Dbms_sqltune. Select_cursor_cache (' sql_id = ' byzwu34haqkn4 '));----4.6 Various cases-Select all statements in the CURSOR CACHE. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT value (P) from table (Dbms_sqltune.   Select_cursor_cache) P;   --Process Each statement (or pass the cursor to load_sqlset). CLOSE cur; end;/--Look for statements isn't parsed by SYS. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.   Select_cursor_cache (' parsing_schema_name <> ' SYS ') P;   --Process Each statement (or pass the cursor to load_sqlset). CLOSE cur;end;/--all statements from a particular module/action. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.   Select_cursor_cache (' module = ' my_application ' and action = ' my_action ')) P; --Process Each statement (or pass cursor to load_sqlset) CLOSE cur; end;/--All statements, this ran for at least five secondsdeclare cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.   Select_cursor_cache (' elapsed_time > 5000000 ')) P; --Process Each statement (or pass cursor to Load_sqlset) CLOSE cur;end;/--Select all statements this pass a simple B Uffer_gets threshold and--is coming from an APPS userdeclare cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.   Select_cursor_cache (' buffer_gets > parsing_schema_name = ' APPS ') P; --Process Each statement (or pass cursor to Load_sqlset) CLOSE cur;end;/--Select all statements exceeding 5 seconds In elapsed time, but also--select the plans (by default we only select execution stats and binds--for performance reason S-in this CASe the Sql_plan attribute of sqlset_row--is NULL) DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.select_cursor_cache (' Elapsed_time > 5000000 ',   NULL, NULL, NULL, NULL, 1, NULL, ' Execution_statistics, Sql_binds, Sql_plan ')) P; --Process Each statement (or pass cursor to load_sqlset) CLOSE cur; end;/-Select The top statements in the cursor, cache ordering by Elapsed_time. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.                                                Select_cursor_cache (null, NULL,                                                ' Elapsed_time ', NULL, NULL, 1,   ) P; --Process Each statement (or pass cursor to Load_sqlset) CLOSE cur;end;/--Select The set of statements which Cumulat  ively account for 90% of the--buffer gets in the cursor cache. This means THat the buffer gets the all--of these statements added up was approximately 90% of the sum of all--statements currently I n the cache. DECLARE cur sys_refcursor; BEGIN OPEN cur for SELECT VALUE (P) from table (Dbms_sqltune.                                                Select_cursor_cache (null, NULL,   ' Buffer_gets ', NULL, NULL,. 9) P;   --Process Each statement (or pass the cursor to load_sqlset). CLOSE cur; End;/************************************************************5. Populating the tuning set with SQL in memory high resource consumption ************************* ---5.0 delete the existing Stsbegin Dbms_sqltune. Drop_sqlset (sqlset_name = ' ocpyang_sts '); end;/--5.1 new tuning set begin Dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/---5.2 reads SQL fill declare cur dbms_sqltune from memory through the cursor cache. Sqlset_cursor; BEGIN OPEN cur for SELECT VALUE(x) from table (Dbms_sqltune. Select_cursor_cache (' parsing_schema_name <> ' SYS ' and disk_reads > 1000000 ', NULL, NULL, NULL, NULL, 1, NUL L, ' all ')) x;--Dbms_sqltune. Load_sqlset (Sqlset_owner = ' SCOTT ', sqlset_name = ' ocpyang_sts ', populate_cursor = cur); end;//********** Common error Line 1th error: ORA-13761: Filter Invalid ORA-06512: In "SYS. Dbms_sqltune ", line 4715ora-06512: Execute on line 11 using the SYS account. ************************************/select sqlset_name, Elapsed_time,cpu_time, Buffer_gets, Disk_reads, sql_textfrom dba_sqlset_statementswhere sqlset_name = ' OCPYANG_STS ';- --5.3 loads all SQL in the specified time in memory--syntax: Dbms_sqltune. Capture_cursor_cache_sqlset (Sqlset_name in VARCHAR2, time_limit in POSITIVE: = 1800, Repeat_i Nterval in POSITIVE: = +, capture_option in VARCHAR2: = ' MERGE ', capture_mode in number: = Mode_ Replace_old_stats, basic_filter in VARCHAR2: = null, Sqlset_owner in VARCHAR2: = null); BEGIN Dbms_sqltune. CaptuRe_cursor_cache_sqlset (Sqlset_owner = ' SCOTT ', sqlset_name = ' prod_workload ', Time_limit = 3   600--3,600 sec, repeat_interval = 20); --end;/************************************************************6 every 20 seconds. Selectively remove sql******************* from the SQL tuning set Select Sqlset_name, Disk_reads, Cpu_time, Elapsed_time, Buffer_getsfrom dba _sqlset_statements; BEGIN Dbms_sqltune. Delete_sqlset (Sqlset_owner = ' SCOTT ', sqlset_name = ' io_sts ', basic_filter = ' Disk_reads < 200000 0 '); End;/************************************************************7. Transferring SQL tuning sets-sts******************************* 1. Create a new STS---Delete the existing stsbegin Dbms_sqltune. Drop_sqlset (sqlset_name = ' ocpyang_sts '); end;/---new Stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/---2. Load sts (can refer to the methods collected by STS) Declarebaseline_ref_cur DBMS_sqltune. Sqlset_cursor;beginopen baseline_ref_cur forselect VALUE (P) from table (Dbms_sqltune.    Select_workload_repository (&begin_snap_id, &end_snap_id,null,null,null,null,null,null,null, ' all ') p; Dbms_sqltune. Load_sqlset (sqlset_name = ' My_sql_tuning_set ', populate_cursor = baseline_cursor); end ;/select * from TABLE (dbms_sqltune. Select_sqlset (' My_sql_tuning_set ', ' (disk_reads/buffer_gets) >= 0.75 ');---3. Create a new table to collect baseline begin Dbms_spm.creat E_stgtab_baseline (table_name = ' baseline_stg01 ', table_owner = ' SCOTT ', db_version = Dbms_sqltune.  Sts_stgtab_11_2_version); --cannot be created under the SYS account end;/----4. Fill in the baseline data to the table begin Dbms_sqltune.pack_stgtab_sqlset (sqlset_name = Ocpyang _sts ', Sqlset_owner = ' SCOTT ', staging_table_name = ' baseline_stg01 ', Staging_schema_ Owner = ' SCOTT ', db_version = Dbms_sqltune. Sts_stgtab_11_2_version); end;//********** Syntax Dbms_sqltune.Pack_stgtab_sqlset (Sqlset_name in VARCHAR2, sqlset_owner in VARCHAR2: = NULL, Staging_table_name In VARCHAR2, staging_schema_owner in VARCHAR2: = null, db_version in number: = null); ***********/---5. Passing data Migrate the table BASELINE_STG01 to the destination server using Oracle Data Pump or database link or EXPDP.---6. The target server creates a new Stsbegin dbms_sqltune. Create_sqlset (sqlset_name = ' ocpyang_sts01 ', Sqlset_owner = ' SCOTT ', description = ' ocpyangtest '); end;/----7. Import the data to the Stsbegin Dbms_sqltune of the destination server. Unpack_stgtab_sqlset (sqlset_name = ' ocpyang_sts01 ', replace = TRUE, Staging_table_name = ' BA Seline_stg01 '); end;/---8. The SQL in the SQL tuning set is generated Baselinedeclareret Number;beginret by a package of SPM baseline: = Dbms_spm.load_plans_from_sqlset (sqlset_name = ' ocpyang_sts01 ', Sqlset_owner = ' SCOTT '); end;/

Oracle SQL Tuning Set