Oracle SQL optimization set and oraclesql Optimization

Oracle SQL optimization set and oraclesql Optimization

**************************************** * ******************** 1. create an optimization set object ************************************ * ********************** --- authorize grant administer any SQL TUNING SET to scott; --- delete an existing STSBEGIN DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'ospyang _ sts'); END;/--- create a STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'ospyang _ sts ', sqlset_owner => 'Scott ', description => 'ospyangtest'); END;/--- view the created SQLSETselect owner, name, id, created, statement_count from dba_sqlset; **************************************** * ******************** 2. view AWR resource-intensive SQL statements ********************************** * ************************ --- 2.1 view the available snapshot range SELECT snap_id, instance_number, end_interval_timeFROM usage BY snap_id; --- 2.2 view sqlSELECT SQL _id, substr (SQL _text, 1,100), disk_reads, cpu_time, elapsed_timeFROM table (cost (820,841, null, null, 'disk _ reads', null, 10) order by disk_reads DESC; --- 2.3 view sqlSELECT SQL _id, substr (SQL _text, 1,100), disk_reads, cpu_time, elapsed_time, parsing_schema_nameFROM table (partition (820,841, 'parsing _ schema_name <> ''sys ''', NULL, 1, NULL, 'all'); --- 2.4 view sqlSELECT SQL _id, substr (SQL _text, 1,100), disk_reads, cpu_time, elapsed_time, of the top 10 sorted by non-SQL users between snapshot numbers 820-840, buffer_gets, parsing_schema_nameFROM table (partition (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 in_interval_time <sysdate-7; -- select max (snap_id) esnapFROM orders; -- COL SQL _text FORMAT A40COL SQL _id FORMAT A15COL parsing_schema_name FORMAT A15COL cpu_seconds FORMAT 999,999,999,999,999 set long 10000 LINES 132 PAGES 100 trimspool on-SELECT SQL _id, SQL _text, disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_nameFROM table (partition (begin_snap => & begin_snap, end_snap => & end_snap, basic_filter => 'parsing _ schema_name <> ''sys ''', ranking_measure1 => 'cpu _ time', result_limit => 10 )); **************************************** * ******************** 3. fill the optimization set with the SQL statement of high resource consumption in AWR: **************************************** * ****************** --- 3.1 create a STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'ospyang _ sts ', sqlset_owner => 'Scott ', description => 'ospyangtest'); END;/--- 3.2 view the select snap_id and begin_interval_timefrom dba_hist_snapshot order by 1 at the beginning and END of an AWR snapshot; --- 3.3 fill the SQL optimization set DECLARE test_cur dbms_sqltune.sqlset_cursor with SQL of medium and high resources in AWR; BEGIN OPEN test_cur FOR SELECT value (x) FROM table (limit (820,841, null, null, 'disk _ reads', null, 15) x; -- dbms_sqltune.load_sqlset (sqlset_owner => 'Scott ', sqlset_name => 'oss _ ss ', populate_cursor => test_cur); END;/* ------------------ common error 1st Line Error: ORA-13774: insufficient permissions, unable to select data ORA-06512 from workload profile: In SYS. DBMS_SQLTUNE ", line sequence: Use sys account on line 10. Specify sequence (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); optional * --- 3.4 view the optimization set trust Information SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, SQL _textFROM dba_sqlset_statementsWHERE sqlset_name = 'ospyang _ sts '; **************************************** * ******************** 4. view resource-intensive SQL statements in the memory ********************************* * ************************* --- 4.1 syntax DBMS_SQLTUNE.SELECT_CURSOR_CACHE (basic_filter IN VARCHAR2: = NULL, object_filter IN VARCHAR2: = NULL, ranking_measure1 IN VARCHAR2: = NULL, ranking_measure2 IN VARCHAR2: = NULL, ranking_measure3 IN VARCHAR2: = NULL, result_percentage in number: = 1, result_limit in number: = NULL, attribute_list IN VARCHAR2: = NULL) RETURN sys. sqlset PIPELINED; --- 4.2 SELECT from memory to read more than 1000000 SELECT SQL _id, substr (SQL _text,), disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_nameFROM table (DBMS_SQLTUNE.SELECT_CURSOR_CACHE ('disk _ reads> 100') order by SQL _id; --- 1000000 view the 10 queries with the longest CPU time of non-sys accounts in memory SELECT SQL _id, substr (SQL _text, 1,120), disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_nameFROM table (partition (basic_filter => 'parsing _ schema_name <> ''sys ''', ranking_measure1 => 'cpu _ time', result_limit => 10); --- 4.4 view SELECT SQL _id, substr (SQL _text, 1,120) with a non-sys account whose return time exceeds 1 second ), 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 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 cursor to load_sqlset ). CLOSE cur; END;/-- Look for statements not 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 cursor to load_sqlset ). CLOSE cur; end;/-- All statements from a participant 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 that 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 that pass a simple buffer_gets threshold and -- are coming from an APPS userDECLARE cur sys_refcursor; begin open cur for select value (P) FROM table (DBMS_SQLTUNE.SELECT_CURSOR_CACHE ('buffer _ gets> 100 and 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 reasons-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> 100', 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 100 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,100) P; -- Process each statement (or pass cursor to load_sqlset) CLOSE cur; end; /-- Select the set of statements which cumulatively account for 90% of the -- buffer gets in the cursor cache. this means that the buffer gets of all -- of these statements added up is approximately 90% of the sum of all -- statements currently in 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 cursor to load_sqlset ). CLOSE cur; END; /*************************************** * ********************* 5. fill in the tuning set with SQL statements with high memory resource consumption ****************************** * **************************** --- 5.0 Delete the existing STSBEGIN DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'oss _ sts '); END;/-- 5.1 create a tuning set BEGIN DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'ossyang _ sts', sqlset_owner => 'Scott ', description => 'ossangtest'); END; /--- 5.2 read SQL from memory through the cursor cache to fill the DECLARE cur DBMS_SQLTUNE.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, 1, NULL, 'all ')) x; -- DBMS_SQLTUNE.LOAD_SQLSET (sqlset_owner => 'Scott ', sqlset_name => 'ospyang _ sts', populate_cursor => cur); END; // ********* common error 1st Line Error: ORA-13761: Filter invalid ORA-06512: In SYS. DBMS_SQLTUNE ", line 4715ORA-06512: Use SYS account on line 11. * **********************************/SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, SQL _textFROM extends sqlset_name = 'ospyang _ sts'; --- 5.3 load all SQL statements within the specified time IN the memory -- Syntax: Explain (sqlset_name IN VARCHAR2, time_limit in positive: = 1800, repeat_interval in positive: = 300, capture_option IN VARCHAR2: = 'merge', capture_mode in number: = cursor, basic_filter IN VARCHAR2: = NULL, sqlset_owner IN VARCHAR2: = NULL); BEGIN worker (sqlset_owner => 'Scott ', sqlset_name => 'prod _ workload', time_limit => 3600 -- 3600 seconds, repeat_interval => 20); -- END every 20 seconds; /*************************************** * ********************* 6. choose to delete SQL ******************************* * *************************** select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_getsfrom values; BEGIN values (sqlset_owner => 'Scott ', sqlset_name => 'IO _ sts', basic_filter => 'disk _ reads <2000000 '); END; /*************************************** * ********************* 7. transfer SQL tuning set-STS ********************************** * ************************** 1. create an STS --- delete an existing STSBEGIN sequence (sqlset_name => 'ospyang _ sts'); END;/--- create STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'ospyang _ sts ', sqlset_owner => 'Scott ', description => 'ospyangtest'); END;/--- 2. load STS (refer to the STS collection method) declarebaseline_ref_cur partition; beginopen baseline_ref_cur forselect VALUE (p) from table (partition (& begin_snap_id, & end_snap_id, 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 baseline-collecting table BEGIN dbms_spm.create_stgtab_baseline (table_name => 'baseline _ STG01 ', table_owner => 'Scott', db_version => external); -- you cannot create an END under the SYS account; /---- 4. fill in Baseline data in the BEGIN tables (sqlset_name => 'ospyang _ sts', sqlset_owner => 'Scott ', staging_table_name => 'baseline _ STG01', staging_schema_owner => 'Scott ', db_version => begin); END; // ********** syntax begin (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. transmit Data to the target server. use Oracle Data Pump or database link or expdp to migrate table BASELINE_STG01 to the target server. --- 6. create STSBEGIN DBMS_SQLTUNE.CREATE_SQLSET on the target server (sqlset_name => 'ospyang _ S01 ', sqlset_owner => 'Scott', description => ossangtest'); END;/---- 7. import data to the target server's STSBEGIN partition (sqlset_name => 'ospyang _ STS01 ', replace => TRUE, staging_table_name => 'baseline _ STG01'); END;/--- 8. the spm baseline package is used to generate BASELINEdeclareret number for all the SQL statements in the SQL tuning set in batches; beginret: = dbms_spm.load_plans_from_sqlset (sqlset_name => 'oss _ STS01 ', sqlset_owner => 'Scott '); end ;/