oracle数据库级别优化分析工具介绍 转贴(http://blog.csdn.net/wyzxg/archive/2010/03/04/5346974.aspx) 当我们对数据库优化诊断时,需要收集相应的信息以供参考,从个人的使用经验来说,这种统计数据分为两大类 一类是数据库级别的统计信息 二类是os级别的统计信息 下面就分别介绍在不同的级别下,常用什么工具来收集信息帮助优化诊断 首先是oracle数据库级别优化分析工具介绍 目录: 1.statspack 2.ASH 3.AWR 4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划) a.autotrace b.explain的使用 1.statspack a。安装 sql> sqlplus "/ as sysdba" SQL> select file_name from dba_data_files; SQL> create tablespace perfstat datafile 'e:\oracle\oradata\skate\perfstat.dbf' size 2000m; sql> @ORACLE_HOME\rdbms\admin\spcreate.sql b。使用 SQL> conn perfstat/passwd 收集统计信息 sql> execute statspack.snap 或 SQL> exec statspack.SNAP(i_snap_level =>5); 生成报告 sql> @ORACLE_HOME\rdbms\admin\spreport.sql 定时收集信息有两种方式,一种是oracle job,一种是os的crontab,我比较习惯用os级别的crontab 设定其每个小时自动收集一次采样的job declare Variable job number ; begin dbms_job.submit(:job, "statspack.snap;" ,trunc( sysdate + 1/24 , 'hh24' ), "trunc(sysdate+1/24,'hh24')" ); commit ; end ; / 查看job使用情况 SQL> select job,schema_user,next_date,interval,what from user_jobs 自动停止采样job declare Variable job number ; begin dbms_job.submit(:job, "dbms_job.broken(44,true);" ,trunc( sysdate + 1 ), "null" ); commit ; end ; / 清空所有stats统计信息表里的数据 sql> @ORACLE_HOME\rdbms\admin\sptrunc.sql snapshot的level,这可以通过EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=N)来修改,N可以为0,5,6,7,10,缺省为5。 0 仅提供一般性能统计 5 增加了对SQL语句总体分析 6 增加了SQL计划和使用 7 增加了分段(Segments)级的统计 10增加了对闩锁(Latches)的分析 其中文档建议对10要慎重,因为代价较高。 eg: SQL> exec statspack.SNAP(i_snap_level =>6); oracle不仅提供生成数据库报告的脚本spreport.sql,还提供另一个statspack 报告脚本sprepsql.sql来生成SQL的报告 sql> @ORACLE_HOME\rdbms\admin\sprepsql.sql 参考文档: 利用statspack来获取生成环境中top SQL及其执行计划 http://www.hellodba.com/Doc/statspack_report_sql.htm 2.AWR awr是建库是自动配置和启用的,他对性能数据的收集默认是一小时,awr对历史数据的分析 生成报告脚本在目录下生成报告使用$ORACLE_HOME/rdbms/admin/,如下: awrrpt.sql :生成指定快照区间的统计报表; awrrpti.sql :生成指定数据库实例,并且指定快照区间的统计报表; awrsqlrpt.sql :生成指定快照区间,指定SQL语句(实际指定的是该语句的SQLID)的统计报表; awrsqrpi.sql :生成指定数据库实例,指定快照区间的指定SQL语句的统计报表; awrddrpt.sql :指定两个不同的时间周期,生成这两个周期的统计对比报表; awrddrpi.sql :指定数据库实例,并指定两个的不同时间周期,生成这两个周期的统计对比报表; 修改Snapshots设置 通过MODIFY_SNAPSHOT_SETTINGS过程,DBA可以调整包括快照收集频率、快照保存时间、以及捕获的SQL数量三个方面的设置。 分别对应MODIFY_SNAPSHOT_SETTINGS 的三个参数: Retention :设置快照保存的时间,单位是分钟。可设置的值最小为1天,最大为100年。设置该参数值为0的话,就表示永久保留收集的快照信息。 Interval :设置快照收集的频率,以分钟为单位。可设置的值最小为10分钟,最大为1年。如果设置该参数值为0,就表示禁用AWR特性。 Topnsql :指定收集的比较占用资源的SQL数量,可设置的值最小为30,最大不超过100000000。 AWR相关几个视图: DBA_HIST_WR_CONTROL:查看当前快照收集的相关设置 v$active_session_history:由ASH自动在内存中维护,以每秒一次的频率收集当前系统中活动session的信息 dba_hist_active_sess_history:是视图 v$active_session_history的历史数据,保存在硬盘上 dba_hist_database_instance:显示数据库是实例的信息 dba_hist_snapshot:当前数据库收集到的快照信息 3.ASH ash和awr不是完全分离的两个功能,ash以秒为单位从v$session中收集信息并保存在内存中,这块内存可以重用,内存满时,ASH数据交给AWR,最后写入系统视图 ash包括两部分内容,一部分是SGA中的,这部分反映是本次系统启动以来的数据,并且ASH尽量保留1小时的内容,这部分内容保存在 v$active_session_history另一部分保存在系统字典表里dba_hist_active_sess_history,是永久的数据 ASH也有生成报告的脚本,在目录下$ORACLE_HOME/rdbms/admin/ ashrpt.sql: 生成数据库级别的ASH统计报表 ashrpti.sql: 生成数据库实例级别的ASH统计报表,常用于RAC单实例 4.ORACLE EXPLAIN PLAN的总结(查询sql的执行计划) a.autotrace 安装 用sys用户运行脚本ultxplan.sql 建立这个表的脚本是:(UNIX:ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin)ultxplan.sql。 SQL> connect sys/sys@colm2 as sysdba; SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql; SQL> create public synonym plan_table for plan_table;--建立同义词 SQL> grant all on plan_table to public;--授权所有用户 要在数据库中建立一个角色plustrace,用sys用户运行脚本plustrce.sql来创建这个角色,这个脚本在目 录(UNIX:ORACLE_HOME/sqlplus/admin, Windows:%ORACLE_HOME%\sqlplus\admin)中; SQL> @C:\oracle\ora92\sqlplus\admin\plustrce.sql; 然后将角色plustrace授予需要autotrace的用户; SQL>grant plustrace to public; 经过以上步骤的设置,就可以在sql*plus中使用autotrace了,autotrace功能只能在SQL*PLUS里使用 AUTOTRACE Statistics常用列解释 db block gets :从buffer cache中读取的block的数量 consistent gets:从buffer cache中读取的undo数据的block的数量 physical reads: 从磁盘读取的block的数量 redo size: DML生成的redo的大小 sorts (memory):在内存执行的排序量 sorts (disk):在磁盘上执行的排序量 eg: SYS@db>set autotrace Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] SYS@db>set timing on SYS@db>set autot trace exp stat SYS@db>select * from tab; 3809 rows selected. Elapsed: 00:00:00.06 Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1059 | 46596 | 216 (2)| | 1 | NESTED LOOPS OUTER | | 1059 | 46596 | 216 (2)| | 2 | TABLE ACCESS FULL | OBJ$ | 1059 | 39183 | 158 (2)| | 3 | TABLE ACCESS CLUSTER| TAB$ | 1 | 7 | 1 (0)| | 4 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| --------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 9077 consistent gets 0 physical reads 0 redo size 133502 bytes sent via SQL*Net to client 3252 bytes received via SQL*Net from client 255 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3809 rows processed b。EXPLAIN的使用 Oracle RDBMS执行每一条SQL语句,都必须经过Oracle优化器的评估。所以,了解优化器是如何选择(搜索)路径以 及索引是如何被使用的,对优化SQL语句有很大的帮助。Explain可以用来迅速方便地查出对于给定SQL语句中的查 询数据是如何得到的即搜索路径(我们通常称为Access Path)。从而使我们选择最优的查询方式达到最大的优化效果。 1.1 、安 装 要使用EXPLAIN首先要执行相应的脚本,创建出Explain_plan表。 具体脚本执行如下: $ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX) 该脚本后会生成一个表这个程序会创建一个名为plan_table的表。 1.2 、使用 常规使用语法: explain PLAN [ SET STATEMENT_ID [=] < string literal > ] [ INTO < table_name > ] FOR < sql_statement > 其中: STATEMENT_ID:是一个唯一的字符串,把当前执行计划与存储在同一PLAN中的其它执行计划区别开来。 TABLE_NAME:是plan表名,它结构如前所示,你可以任意设定这个名称。 SQL_STATEMENT:是真正的SQL语句。 比如: SQL>explain plan set statement_id='T_TEST' for select * from t_test; SQL> Explained 执行下面语句可以查询到执行计划 SQL>SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID 2 FROM PLAN_TABLE a 3 WHERE STATEMENT_ID='T_TEST' 4 ORDER BY Id; 也可以用这句话 select * from table(dbms_xplan.display); 可以把所有PLAN_TABLE里的数据罗列出来。 ----end----- |
Oracle 性能分析的4个利器(转)
最新推荐文章于 2024-04-16 19:26:43 发布