-- Запрос к AWR
SELECT *
FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 415,
end_snap => 420,
basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p
WHERE parsing_schema_name = 'SCOTT';
-- Удаляем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'my_sts');
END;
/
-- Создаем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'my_sts',
description => 'SQL Tuning Set for loading from trace file');
END;
/
-- Загружаем SQL statements в SQL Tuning Set из AWR
DECLARE
ref_cursor sys_refcursor;
BEGIN
OPEN ref_cursor FOR
SELECT value(p)
FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 415,
end_snap => 420,
basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sts',
populate_cursor => ref_cursor,
sqlset_owner => 'SCOTT'
);
CLOSE ref_cursor;
END;
/
-- Просмотр STS:
SELECT * FROM DBA_SQLSET_STATEMENTS;
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name => 'my_sts',
sql_id => '0r1gq4aapgnxd',
plan_hash_value => 426858176
));
Список параметров функции select_workload_repository:
dbms_sqltune.select_workload_repository(
BEGIN_SNAP => 415,
END_SNAP => 420,
BASIC_FILTER => 'SQL_ID = ''64qdks642mqt2'' AND PLAN_HASH_VALUE = 3406458838',
OBJECT_FILTER => NULL,
RANKING_MEASURE1 => 'disk_reads',
RANKING_MEASURE2 => NULL,
RANKING_MEASURE3 => NULL,
RESULT_PERCENTAGE => 1,
RESULT_LIMIT => 250,
ATTRIBUTE_LIST => 'ALL',
RECURSIVE_SQL => 'Y'
)
Вместо BEGIN_SNAP и END_SNAP, можно указать BASELINE_NAME.
BEGIN_SNAP Non-inclusive beginning snapshot ID
END_SNAP Inclusive ending snapshot ID
BASELINE_NAME Name of AWR baseline
BASIC_FILTER SQL predicate to filter SQL statements from workload; if not set, then only SELECT, INSERT, UPDATE, DELETE, MERGE, and CREATE TABLE statements are captured.
OBJECT_FILTER Not currently used
RANKING_MEASURE(n) Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads, and so on;
N can be 1, 2, or 3. The elapsed_time and cpu_time are measured in seconds.
RESULT_PERCENTAGE Filter for choosing top N% for ranking measure
RESULT_LIMIT Limit of the number of SQL statements returned in the result set
ATTRIBUTE_LIST List of SQL statement attributes (TYPICAL, BASIC, ALL, and so on)
RECURSIVE_SQL Include/exclude recursive SQL (HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL)
cpu_time : Number of seconds
elapsed_time : Number of seconds
disk_reads : Number of reads from disk
buffer_gets : Number of reads from memory
rows_processed : Average number of rows
optimizer_cost : Calculated optimizer cost
executions : Total execution count of SQL statement
Примеры использования параметров:
BASIC_FILTER => 'parsing_schema_name <> "SYS"'
( 'parsing_schema_name not in (''DBSNMP'',''SYS'',''ORACLE_OCM'')',)
RANKING_MEASURE1 => 'cpu_time'
( 'elapsed_time', buffer_gets, disk_reads, )
Еще несколько примеров :
SELECT snap_id, instance_number, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;
SELECT sql_id
,substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,
null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;
==========================================================
SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,
'parsing_schema_name <> ''SYS''',
NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));
==========================================================
SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 415
,end_snap => 420
,basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'buffer_gets'
,result_limit => 10
));
==========================================================
--
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 => 415
,end_snap => 420
,basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));
-- Посмотреть менялся ли план у запроса:
select snap_id, plan_hash_value from dba_hist_sqlstat where snap_id in (415,420) and sql_id='0r1gq4aapgnxd' order by snap_id desc;
-- Посмотреть из AWR какие планы изменялись:
select a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
from dba_hist_sqlstat a, dba_hist_sqlstat b
where (a.snap_id = 415 and b.snap_id = 420)
and (a.sql_id = b.sql_id)
and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;
-- по всему репозиторию:
select distinct sql_id, plan_hash_value, f snapshot,
(select begin_interval_time
from dba_hist_snapshot
where snap_id = f) snapdate
from (select sql_id, plan_hash_value,
first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
from (select sql_id, plan_hash_value, snap_id,
count (distinct plan_hash_value) over (partition by sql_id) a
from dba_hist_sqlstat
where plan_hash_value > 0
order by sql_id)
where a > 1)
order by sql_id, f;
-- Планы из AWR можно посмотреть так:
select * from table(dbms_xplan.display_awr('5k5207588w9ry'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953 ));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'BASIC'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL -projection'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));
dbms_xplan.display_awr(
sql_id IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
db_id IN INTEGER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
Комментариев нет:
Отправить комментарий