************************************************************
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
oracle sql调优集
最新推荐文章于 2024-08-01 23:00:00 发布
这篇博客详细介绍了如何在Oracle数据库中进行SQL调优,包括新建SQL调优集(STS)、分析AWR报告中的资源密集型SQL、使用内存中的SQL填充调优集,以及SQL调优集的删除、传输和生成基线。通过实例展示了DBMS_SQLTUNE包的相关函数和步骤,帮助优化数据库性能。
摘要由CSDN通过智能技术生成