|
bms_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 VAL |