ORACLE SQL Performance Analyzer的使用

标签: oracle sql performance | 发表时间:2014-06-03 17:46 | 作者:yangzhawen
出处:http://blog.csdn.net


通过 SPA,您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的
SQL 或整个 SQL 负载,然后生成比较报告,帮助您评估它们的影响.


在 Oracle Database 11g 之前的版本中,我必须捕获所有 SQL 语句,通过跟踪运行这些语句,
然后得到执行计划 — 这是一项极其耗时又极易出错的任务。新版本中,我们不需要再那样做了,
我改用非常简单而有效的 SQL Performance Analyzer。

---使用场景

1.数据库升级
2.实施优化建议
3.更改方案
4.收集统计信息
5.更改数据库参数
6.更改操作系统和硬件

 

create tablespace test
datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\test01.DBF'
size 5000m
autoextend on
next 100m maxsize unlimited
extent management local autoallocate
segment   space management auto;

 

create table t1
(
sid int not null ,
sname varchar2(10)
)
tablespace test;

 

 

-2.-循环导入数据
declare
        maxrecords constant int:=1000000;
        i int :=1;
    begin
        for i in 1..maxrecords loop
          insert into t1 values(i,'ocpyang');
        end loop;
    dbms_output.put_line(' 成功录入数据! ');
    commit;
    end;
/


update t1 set sname='苏州' where sid=500001;

update t1 set sname='南京' where sid=600001;


---3.收集统计信息

exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)


alter system flush shared_pool;

---4.执行查询

select count(*) from t1 where sid<=100;


select count(*) from t1 where sid<=500;


select count(*) from t1 where sid>50000;


---5.新建STS

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'OCPYANG_STS'
    );
END;
/

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'OCPYANG_STS',
    sqlset_owner => 'SYS',
    description  => 'ocpyangtest');
END;
/


---6.加载sql优化集

set serveroutput on
DECLARE
cur01 dbms_sqltune.sqlset_cursor;
BEGIN
open cur01 for select value(a) from table(dbms_sqltune.select_cursor_cache
(
basic_filter => 'sql_text like ''%T1%'' and parsing_schema_name =''SYS''',
attribute_list => 'ALL'
)
) a;
dbms_sqltune.load_sqlset(
sqlset_name => 'OCPYANG_STS',
populate_cursor => cur01);
close cur01;
END;
/

/*********有两个参数值得特别说明:

1)SELECT_CURSOR_CACHE的第一个参数是basic_filter ,它可以取的值有:

  sql_id                   VARCHAR(13),
  force_matching_signature NUMBER,
  sql_text                 CLOB,
  object_list              sql_objects,
  bind_data                RAW(2000),
  parsing_schema_name      VARCHAR2(30),
  module                   VARCHAR2(48),
  action                   VARCHAR2(32),
  elapsed_time             NUMBER,
  cpu_time                 NUMBER,
  buffer_gets              NUMBER,
  disk_reads               NUMBER,
  direct_writes            NUMBER,
  rows_processed           NUMBER,
  fetches                  NUMBER,
  executions               NUMBER,
  end_of_fetch_count       NUMBER,
  optimizer_cost           NUMBER,
  optimizer_env            RAW(1000),
  priority                 NUMBER,
  command_type             NUMBER,
  first_load_time          VARCHAR2(19),
  stat_period              NUMBER,
  active_stat_period       NUMBER,
  other                    CLOB,
  plan_hash_value          NUMBER,
  sql_plan                 sql_plan_table_type,
  bind_list                sql_binds

2)SELECT_CURSOR_CACHE的最后一个参数是attribute_list

BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

ALL - return all attributes

Comma separated list of attribute names this allows to return only a subset of SQL attributes: EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics

*********/


---7.查询sql优化集

select sql_id,sql_text from dba_sqlset_statements
where sqlset_name='OCPYANG_STS' and sql_text like '% from t1%';

 

 

 

---8.新建SPA

var v_task varchar2(64);
begin
:v_task:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'OCPYANG_STS',
task_name => 'SPA01'
);
end;
/

 

 

 


/**********语法


Syntax

SQL text format. This form of the function is called to prepare the analysis of a single statement given its text.

DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sql_text         IN CLOB,
  bind_list        IN sql_binds := NULL,
  parsing_schema   IN VARCHAR2  := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;
SQL ID format. This form of the function is called to prepare the analysis of a single statement from the cursor cache given its identifier.

DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;
Workload Repository format. This form of the function is called to prepare the analysis of a single statement from the workload repository given a range of snapshot identifiers.

DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  begin_snap       IN NUMBER,
  end_snap         IN NUMBER,
  sql_id           IN VARCHAR2,
  plan_hash_value  IN NUMBER    := NULL,
  task_name        IN VARCHAR2  := NULL,
  description      IN VARCHAR2  := NULL)
RETURN VARCHAR2;


SQLSET format. This form of the function is called to prepare the analysis of a SQL tuning set.

DBMS_SQLPA.CREATE_ANALYSIS_TASK(
  sqlset_name       IN VARCHAR2,
  basic_filter      IN VARCHAR2 :=  NULL,
  order_by          IN VARCHAR2 :=  NULL,
  top_sql           IN VARCHAR2 :=  NULL,
  task_name         IN VARCHAR2 :=  NULL,
  description       IN VARCHAR2 :=  NULL
  sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;


**********/

 

---9.执行SPA

begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'before_change'
);
end;
/

 

 

/*********语法

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name         IN VARCHAR2,
   execution_type    IN VARCHAR2               := 'test execute',
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL)
 RETURN VARCHAR2;


DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
   task_name         IN VARCHAR2,
   execution_type    IN VARCHAR2               := 'test execute',
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL);

*********/


---10.改变


create index index_01 on t1(sid,sname)
tablespace test;


exec dbms_stats.gather_table_stats(USER,'T1',CASCADE=>TRUE)

---11.改变后执行

begin
dbms_sqlpa.execute_analysis_task
(
task_name => 'SPA01',
execution_type => 'test execute',
execution_name => 'after_change'
);
end;
/

 


col TASK_NAME format a30
col EXECUTION_NAME for a30
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='SPA01'
order by execution_end
/

EXECUTION_NAME                 STATUS      EXECUTION_END
------------------------------ ----------- -------------------
before_change                  COMPLETED   2014-05-28 15:43:58
after_change                   COMPLETED   2014-05-28 15:44:58

 

---12.执行任务比较

begin
dbms_sqlpa.EXECUTE_ANALYSIS_TASK(
task_name        => 'SPA01',
execution_type   => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'before_change',
'execution_name2',
'after_change'));
end;
/

 


---13.生产报告

set serveroutput on size 999999
set long 100000000
set pagesize 0
set linesize 200
set longchunksize 200
set trimspool on
spool e:\report.txt

select DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA01') from dual;

spool off;

 

作者:yangzhawen 发表于2014-6-3 9:46:51 原文链接
阅读:77 评论:0 查看评论

相关 [oracle sql performance] 推荐:

ORACLE SQL Performance Analyzer的使用

- - CSDN博客数据库推荐文章
通过 SPA,您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的. SQL 或整个 SQL 负载,然后生成比较报告,帮助您评估它们的影响.. 在 Oracle Database 11g 之前的版本中,我必须捕获所有 SQL 语句,通过跟踪运行这些语句,. 然后得到执行计划 — 这是一项极其耗时又极易出错的任务.

SQL Performance Analyzer SPA常用脚本汇总

- - CSDN博客数据库推荐文章
附件为 一个SPA报告  spa_buffergets_summary. SQL 性能分析器 SQL Performance Analyzer SPA. Oracle Database 11g 引入了 SQL 性能分析器;使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响. SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响.

oracle sql 优化大全

- - Oracle - 数据库 - ITeye博客
最近遇到了oracle sql优化的问题,找了一下,发现这文章实在不错,跟大家分享一下,如果以后有什么新的改进也会继续补充的. 1     前言… 2 . 2     总纲… 2 . 3     降龙十八掌… 3 . 第一掌 避免对列的操作… 3 . 第二掌 避免不必要的类型转换… 4 . 第三掌 增加查询的范围限制… 4 .

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

查询Oracle 耗资源sql

- - 非技术 - ITeye博客
已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

oracle各类型SQL的操作流程

- - 数据库 - ITeye博客
•Select * from test  where  object_id=200在体系中是如何运转的. 在PGA中把此条SQL语句hash成一个值;. 接下来根据此hash值到SGA的共享池中去匹配,如果没有,首先查询自己的语句语法是否正确,语义是否正确,是否有权限. 如果都通过则通过CBO解析生成执行计划(如走索引还是全表).

Oracle PL/SQL 编程基础 实例 2

- - CSDN博客数据库推荐文章
if  循环  控制语句 . --编写一个过程,可以 输入一个雇员名,如果该雇员的工资低于2000就给他增加10%.           --判断. --======####案例s33 编写一个过程,可以 输入一个雇员名,如果该雇员的补助不是0就在原基础上增加100,如果是0就加200.           --判断.

Oracle PL/SQL 编程基础 实例

- - CSDN博客数据库推荐文章
1, exec 过程名 (参数,.  call 过程名 (参数  ).   dbms_output.put_line('姓名:'||v_ename);.                           --执行部分.  --------------函数 -------.    -------包------------由包规范和包体组成的.

用SQL语言管理Oracle数据库

- - CSDN博客推荐文章
1,查看数据库的基本属性:. SELECT dbid 数据库编号,name 数据库名称,db_unique_name 全局名称,created 创建时间,log_mode 归档方式,open_mode 访问方式,platform_name 版本类型 FROM v$database;. 2,查看所有数据库对象的类别和大小:.