oraclesql調優集
************************************************************
1.新建調優集對象
************************************************************
---授權
grant ADMINISTER ANY SQL TUNING SET to scott;
---刪除存在的STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
---新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/
---查看數據庫已經創建的SQLSET
select owner, name, id, created, statement_count from dba_sqlset;
************************************************************
2.查看AWR資源密集型SQL語句
************************************************************
---2.1查看可用的快照范圍
SELECT snap_id, instance_number, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;
---2.2 查看快照編號820-840之間磁盤使用率前10的sql
SELECT sql_id
,substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(820,841,
null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;
---2.3查看沒有被sys用戶解析的sql
SELECT sql_id, substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time, parsing_schema_name
FROM 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的sql
SELECT sql_id, substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name
FROM 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_snap
COL esnap NEW_VALUE end_snap
--
SELECT MAX(snap_id) bsnap
FROM dba_hist_snapshot
WHERE begin_interval_time < sysdate-7;
--
SELECT MAX(snap_id) esnap
FROM dba_hist_snapshot;
--
COL sql_text FORMAT A40
COL sql_id FORMAT A15
COL parsing_schema_name FORMAT A15
COL 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_name
FROM 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新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/
---3.2查看AWR快照起止
select snap_id, begin_interval_time
from 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 4715
ORA-06512: 在 line 10
使用sys賬戶即可，在DBMS_SQLTUNE.LOAD_SQLSET指定sqlset_owner
DBMS_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_text
FROM dba_sqlset_statements
WHERE 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_name
FROM 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_name
FROM 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_time
FROM 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 seconds
DECLARE
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 user
DECLARE
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 刪除存在的STS
BEGIN
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 4715
ORA-06512: 在 line 11
使用SYS賬戶執行即可.
************************************/
SELECT sqlset_name, elapsed_time
,cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements
WHERE 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_gets
from 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
---刪除存在的STS
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'OCPYANG_STS'
);
END;
/
---新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/
---2.加載STS(可以參考STS收集的方法)
declare
baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open baseline_ref_cur for
select 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.目標服務器新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS01',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/
----7.導入數據到目標服務器的STS
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'OCPYANG_STS01',
replace => TRUE,
staging_table_name => 'BASELINE_STG01');
END;
/
---8.通過SPM BASELINE的包來把SQL調優集裡的SQL都批量的生成BASELINE
declare
ret number;
begin
ret := dbms_spm.load_plans_from_sqlset(
sqlset_name => 'OCPYANG_STS01',
sqlset_owner => 'SCOTT');
end;
/
