oracle sql調優集，oraclesql調優

oracle sql調優集，oraclesql調優

************************************************************1.建立調優集對象************************************************************---授權grant ADMINISTER ANY SQL TUNING SET  to scott;---刪除存在的STSBEGIN  DBMS_SQLTUNE.DROP_SQLSET(    sqlset_name => 'OCPYANG_STS'    );END;/---建立STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS',     sqlset_owner => 'SCOTT',    description  => 'ocpyangtest');END;/---查看資料庫已經建立的SQLSETselect owner, name, id, created, statement_count from dba_sqlset;************************************************************2.查看AWR資源密集型SQL語句************************************************************---2.1查看可用的快照範圍SELECT snap_id, instance_number, end_interval_timeFROM dba_hist_snapshotORDER BY snap_id;---2.2 查看快照編號820-840之間磁碟使用率前10的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, 10))ORDER BY disk_reads DESC;---2.3查看沒有被sys使用者解析的sqlSELECT sql_id, substr(sql_text,1,100),disk_reads, cpu_time, elapsed_time, parsing_schema_nameFROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841,'parsing_schema_name <> ''SYS''',NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));---2.4 查看快照編號820-840之間非sql使用者排序的前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 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(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.使用AWR中高資源消耗的SQL來填充最佳化集:************************************************************---3.1建立STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS',     sqlset_owner => 'SCOTT',    description  => 'ocpyangtest');END;/---3.2查看AWR快照起止select snap_id, begin_interval_timefrom dba_hist_snapshot order by 1;---3.3使用AWR中高資源的sql來填充sql最佳化集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, null,'disk_reads',      null, null, null, 15)) x;  --  dbms_sqltune.load_sqlset(   sqlset_owner =>'SCOTT',    sqlset_name => 'OCPYANG_STS',    populate_cursor => test_cur);END;/*------------------常見錯誤第 1 行出現錯誤:ORA-13774: 許可權不足, 無法從工作量資料檔案庫中選擇資料 ORA-06512: 在"SYS.DBMS_SQLTUNE", line 4715ORA-06512: 在 line 10使用sys賬戶即可，在DBMS_SQLTUNE.LOAD_SQLSET指定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 查看最佳化集相信資訊SELECT sqlset_name, elapsed_time,cpu_time, buffer_gets, disk_reads, sql_textFROM dba_sqlset_statementsWHERE sqlset_name = 'OCPYANG_STS';************************************************************4.查看記憶體中資源密集型的sql************************************************************---4.1 文法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 從記憶體中選擇讀取磁碟超過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 查看記憶體中非sys賬戶使用者CPU時間最長的10個查詢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 => 10));---4.4 查看記憶體中非sys賬戶運行返回時間超過1秒的SELECT sql_id, 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 查看具體sql_id執行細節SELECT *FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''byzwu34haqkn4'''));----4.6 各種案例-- 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 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 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 > 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 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.用記憶體中高資源消耗的sql填充調優集************************************************************---5.0 刪除存在的STSBEGIN  DBMS_SQLTUNE.DROP_SQLSET(    sqlset_name => 'OCPYANG_STS'    );END;/--5.1建立調優集BEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS',     sqlset_owner => 'SCOTT',    description  => 'ocpyangtest');END;/---5.2  通過遊標緩衝從記憶體中讀取sql填充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, NULL, NULL, NULL, 1, NULL,'ALL')) x;--  DBMS_SQLTUNE.LOAD_SQLSET(   sqlset_owner =>'SCOTT',  sqlset_name => 'OCPYANG_STS',    populate_cursor => cur);END;//**********常見錯誤第 1 行出現錯誤:ORA-13761: 過濾器無效ORA-06512: 在 "SYS.DBMS_SQLTUNE", line 4715ORA-06512: 在 line 11使用SYS賬戶執行即可.************************************/SELECT sqlset_name, elapsed_time,cpu_time, buffer_gets, disk_reads, sql_textFROM dba_sqlset_statementsWHERE sqlset_name = 'OCPYANG_STS';---5.3  將記憶體中指定時間內的所有sql載入--文法:DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (    sqlset_name         IN VARCHAR2,     time_limit          IN POSITIVE := 1800,    repeat_interval     IN POSITIVE := 300,    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      => 3600  --3600秒   ,repeat_interval => 20);   --每隔20秒END;/************************************************************6.選擇性的從sql調優集中刪除sql************************************************************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 < 2000000');END;/************************************************************7.傳輸sql調優集-STS************************************************************1.建立一個STS---刪除存在的STSBEGIN  DBMS_SQLTUNE.DROP_SQLSET(    sqlset_name => 'OCPYANG_STS'    );END;/---建立STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS',     sqlset_owner => 'SCOTT',    description  => 'ocpyangtest');END;/---2.載入STS(可以參考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.建立搜集baseline的表BEGIN  dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_STG01',table_owner => 'SCOTT',db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );  --不能建立在SYS賬戶下END;/----4.把Baseline資料填到表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;/ /**********文法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.傳遞資料到目標伺服器使用Oracle Data Pump or database link or expdp等將表BASELINE_STG01遷移到目標伺服器.---6.目標伺服器建立STSBEGIN  DBMS_SQLTUNE.CREATE_SQLSET(    sqlset_name => 'OCPYANG_STS01',     sqlset_owner => 'SCOTT',    description  => 'ocpyangtest');END;/----7.匯入資料到目標伺服器的STSBEGIN  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(      sqlset_name  => 'OCPYANG_STS01',      replace  => TRUE,      staging_table_name => 'BASELINE_STG01');END;/---8.通過SPM BASELINE的包來把SQL調優集裡的SQL都批量的產生BASELINEdeclareret number;beginret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'OCPYANG_STS01',sqlset_owner => 'SCOTT');end;/