`
yr512656630
  • 浏览: 321129 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle 系统性能变慢常规处理诊断及定位处理方法

阅读更多
系统性能变慢常规处理诊断及定位处理方法 

一、可能的系统现象:
1、业务变慢,尤其是工单相关的业务较慢;
2、主机系统资源率较高,数据库中的ACTIVE较多,有全表扫描;
3、数据库锁、BUFFER等待等

二、处理步骤和方法:
1、对于业务方面,有业务人员进行查询,包括程序升级,批量操作等。
2、对于系统方面可以从下面几个方面考虑;
  a、主机方面: 确定占用率较高的进程,分析对应的客户端进程的行为(什么机器、什么业务),在

服务器对端分析对应的ORACLE进程的行为(
    包括SQL语句、HASH值、调用次数、执行计划、统计值等)
  b、数据库方面:查询对应主机进程占用的系统资源,在SQL语句的行为同上;
  c、全表扫描,查看执行的语句和对应的业务,并验证相关执行计划是否合适,统计值是否合适;
   【如果执行计划有问题,此结果可以作为更新统计值的条件】
3、查询系统的等待事情和锁对象等;

三、具体可能的语句或工具
1、操作系统 使用NMON 、LSOF、glance、top、sar等;
   在HP主机上使用 glance 、lsof
   在AIX主机上使用nmon [ nmon-> u -> o  ] \lsof

2、
a、查询操作系统进程、DB SID和SQL语句,锁等待的语句
select * from v$sql  where address  in
(select sql_address
    from v$session  a
    where  paddr In
       (  select  addr  from v$process where spid
         in ( 8642776)
       )     
)

select * from v$process where addr in (select paddr from v$session where  sid in (select

session_id from v$locked_object where object_id in (
select object_id from v$locked_object where oracle_username = 'SDQD')))

select * from dba_objects  where object_id in(select object_id from v$locked_object)

select * from v$process where addr in (select paddr from v$session where  sid in (select

session_id from v$locked_object) )

select * from v$session where paddr in (select addr  from v$process where spid in ('14714'

,'14693','9807','29105' ) )
select spid  from v$process  where addr in (select paddr from v$session t where

t.SID='21')

b、统计值的查询、分析和组状图
select *  from  dba_tables;
select a.owner,a.table_name ,num_rows,blocks,last_analyzed  from dba_tables a where

a.sample_size > 1
  对比上面的抽样值和实际值 进行比较
   --非分区表
   select  a.owner   ,segment_name, a.bytes  size11, a.blocks ,b.blocks , a.blocks /

b.blocks     from dba_segments a  , dba_tables b
   where a.owner = b.owner and a.segment_name =b.table_name and b.sample_size > 1 and

b.blocks > 0 and a.partition_name  is null
  
 
--分区表
select  a.owner   ,segment_name,a.partition_name , a.bytes  , a.blocks ,b.blocks ,

a.blocks / b.blocks     from dba_segments a  , dba_tab_partitions  b
   where a.owner = b.table_owner  and a.segment_name =b.table_name and

b.partition_name=a.partition_name  and b.sample_size > 1 and b.blocks > 0 and

a.partition_name  is not   null

   对于结果 偏离1较多的>> 1或者 <<1 ,结合实际空间,可以进行快速的统计值更新。
   【此结果可以作为更新统计值的条件】


对于oracle 10G还需要考虑组状图 可以使用下列语句直接收集
  详细参考柱状图的统计方式。
大致的语句可以如下:
   在看Cost-Based Oracle Fundamentals一书时, 使用了如下的sql来模拟直方图的收集:

SQL> desc sales;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PROD_ID                                   NOT NULL NUMBER
CUST_ID                                   NOT NULL NUMBER
TIME_ID                                   NOT NULL DATE
CHANNEL_ID                                NOT NULL NUMBER
PROMO_ID                                  NOT NULL NUMBER
QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

SQL>   select                                                          
  2      tenth                                           tenth,       
  3      min(PROD_ID)                                     low_val,    
  4      max(PROD_ID)                                     high_val,   
  5      max(PROD_ID) - min(PROD_ID)                       width,     
  6      round(100000 / (max(PROD_ID) - min(PROD_ID)),2)   height     
  7   from (                                                          
  8      select                                                       
  9              PROD_ID,                                             
10              ntile(10) over (order by PROD_ID) tenth              
11      from SALES                                                   
12   )                                                               
13   group by tenth                                                  
14   order by tenth                                                  
15   ;                                                               

     TENTH    LOW_VAL   HIGH_VAL      WIDTH     HEIGHT
---------- ---------- ---------- ---------- ----------
         1         13         24         11    9090.91
         2         24         30          6   16666.67
         3         30         34          4      25000
         4         34         40          6   16666.67
         5         40         48          8      12500
         6         48        117         69    1449.28
         7        117        124          7   14285.71
         8        124        130          6   16666.67
         9        130        138          8      12500
        10        138        148         10      10000

10 rows selected.

为了验证结果是否与直方图统计信息一致,对该表进行统计信息收集,并收集直方图信息。





---快速更新统计值
SQL>alter session set  db_file_multiblock_read_count=128;
--对表
SQL> exec dbms_stats.gather_table_stats('UCR_CRM1','TF_B_TRADE_BATDEAL', degree =>

6,estimate_percent => 20) ;
--对一个分区
SQL>exec dbms_stats.gather_table_stats(ownname => 'ownname',tabname => 'tabname',partname

=> 'partname',estimate_percent => 20,degree => 6)


c、执行计划的验证

   最简单的方式:在session 中使用SQL>set autotrace on ;
   更详细的方法见后面的方法。




    ORACLE EXPLAIN PLAN的总结
在ORACLE数据库中,需要对SQL语句进行优化的话需要知道其执行计划,从而针对性的进行调整.ORACLE

的执行计划的获得有几种方法,下面就来总结下

1、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里的数据罗列出

来。

2 、AUTOTRACE的使用方法

2.1、安装

用sys用户运行脚本ultxplan.sql

建立这个表的脚本是:(UNIX:$ORACLE_HOME/rdbms/admin, Windows:%ORACLE_HOME%\rdbms\admin)

ultxplan.sql。

SQL> connectsys/sys@colm2as 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了

2、2 使用

使用起来非常方便,只要使用一条命令就可以了

SQL>SET AUTOTRACE ON;



*autotrace功能只能在SQL*PLUS里使用



  其他一些使用方法:

2.2.1、在SQLPLUS中得到语句总的执行时间

SQL> set timing on;

2.2.2、只显示执行计划--(会同时执行语句得到结果)

SQL>set autotrace on explain

比如:

sql> select count(*) from test;

count(*)

-------------

4



Execution plan

----------------------------

0            select statement ptimitzer=choose (cost=3 card=1)

1     0        sort(aggregate)

2     1           partition range(all)

3     2             table access (full) of 't_test'  (cost=3 card=900)


2.2.3、只显示统计信息---(会同时执行语句得到结果)

SQL>set  autotrace on statistics;

(备注:对于SYS用户,统计信息将会是0)

2.2.4、显示执行计划,屏蔽执行结果--(但语句实质还执行的

SQL> set autotrace on traceonly;  
(备注:同SET AUTOTRACE ON; 只不过不显示结果,显示计划和统计)

2.2.5、仅仅显示执行计划,屏蔽其他一切结果--(语句还是执行了)
SQL>set autotrace on traceonly explain;
对于仅仅查看大表的Explain Plan非常管用。
2.2.6、关闭
SQL>set autotrace off;
总结:SQLPLUS 下的自动显示功能,在看执行计划中其语句还是会被执行的。尤其在执行

UPDATE/DELETE语句时请千万注意,ORACLE是先执行脚本同时显示执行计划的,即使使用set autotrace

on traceonly explain;
这个时候推荐使用EXPLAIN PLAN FOR来看或者PL/SQL等第三方工具
3、第三方工具来看执行计划
   如果在PL/SQL中使用选择要查询语句显示执行计划,则只需要SQL WINDOWS 窗口里面输入要查询的

SQL语句,然后选择按键F5或者在菜单TOOLS?D?D>Explain Plan 菜单按键就可以在执行计划窗口查看该

语句的执行计划。
在TOAD语句中在执行当前的SQL窗口中选择下方的Explain PlanTAB页即可以查看要执行语句的执行计划

信息。
4、限制

explain真正的唯一的限制是用户不能去解释其它用户的表,视图,索引或其它类型,用户必须是所有

被解释事物的所有者,如果不是所有者而只有select权限,explain会返回一个错误。


转自:http://space.itpub.net/229689/viewspace-692853
分享到:
评论

相关推荐

    Oracle 性能诊断艺术

    它深入探讨了如何有效地诊断和解决Oracle数据库系统的性能问题,对于优化数据库性能、保障业务稳定运行具有极高的价值。以下是对该书内容的详细解读: 1. **性能监控与分析**:书中详细介绍了Oracle的性能监控工具...

    Oracle性能优化与故障诊断案例分享

    Oracle数据库性能优化与故障诊断是数据库管理中极为重要的两个方面,它们直接关系到企业业务系统的稳定运行和性能表现。在这篇文章中,我们将详细探讨针对Oracle数据库性能问题和故障的诊断案例,并分享如何通过案例...

    oracle性能诊断

    在现代企业环境中,Oracle数据库承载着大量的数据处理任务,任何性能瓶颈都可能引发连锁反应,导致整个系统的响应时间增加,用户体验下降。因此,及时准确地诊断并解决Oracle性能问题显得尤为重要。 #### 二、性能...

    《Oracle Wait Interface性能诊断与调整实践指南》

    ### Oracle Wait Interface (OWI)性能诊断与调整实践指南 #### 一、Oracle Wait Interface (OWI)概述 Oracle Wait Interface(简称OWI)是Oracle数据库管理系统中的一个重要组成部分,主要用于监控和诊断数据库...

    Oracle数据库性能诊断

    Oracle数据库性能诊断,可有效分析当前Oracle存在的问题。

    oracle数据库性能优化.pdf

    Oracle数据库性能优化是确保系统高效运行的关键环节,尤其是在大数据量和高并发的环境中。Oracle数据库因其先进、完整和集成的特性,在市场中占据主导地位,因此深入理解和掌握Oracle的优化技术至关重要。 首先,...

    oracle11g性能诊断

    Oracle 11g是Oracle公司推出的数据库管理系统的一个重要版本,其在性能优化和诊断方面有着显著的提升。本文将深入探讨Oracle 11g在性能诊断方面的关键知识点,旨在帮助IT专业人士更好地理解和解决数据库性能问题。 ...

    面向大型数据处理系统的Oracle数据库性能优化技术.pdf

    【标题】:“面向大型数据处理系统的Oracle数据库性能优化技术” 【概述】: 本文主要探讨了在大型数据处理系统中,针对Oracle数据库的性能优化策略。作者通过实例分析,提出了一套关键的优化技术和方法,以解决...

    oracle故障诊断方法

    Oracle 故障诊断方法是数据库管理员在遇到Oracle数据库系统出现问题时必须掌握的关键技能。Oracle数据库作为全球广泛使用的大型关系型数据库管理系统,其复杂性和多样性使得故障诊断成为一个涉及多个层面的技术领域...

    Oracle性能优化绝版好书:高级OWI与ORACLE性能调整

    通过学习《Oracle性能优化绝版好书:高级OWI与ORACLE性能调整》,读者将掌握如何运用OWI进行性能监控,以及如何结合其他Oracle工具和方法,系统性地解决性能问题,提升数据库的整体运行效率。这本书对于Oracle DBA和...

    Oracle Wait Interface性能诊断与调整实践指南

    Oracle Wait Interface性能诊断与调整实践指南 oracle press

    Oracle表删除大量数据(千万)后查询变慢问题(原因分析)

    Oracle 表删除大量数据后查询变慢问题分析 在 Oracle 数据库中,如果删除了大量数据(千万级别),可能会出现查询变慢的问题。下面我们将分析这个问题的原因,并提供解决方案。 问题的原因主要有两个方面: 1. 表...

    Oracle_数据库_10g:自动性能诊断.pdf

    ### Oracle 10g 自动性能诊断关键技术知识点 #### 一、Oracle 10g 可管理性概览 **1.1 自我管理数据库概念** - **定义**:自我管理数据库是指具备自动管理和自我诊断能力的数据库系统,能够简化数据库的日常管理...

    Oracle收集性能日志方法

    本文将深入探讨Oracle收集性能日志的方法,以便更好地理解系统的行为,定位问题,并进行性能调优。 首先,了解Oracle的性能日志主要包括AWR(Automatic Workload Repository)和ASH(Active Session History)。AWR...

    [Oracle.DBA手记_数据库诊断案例与性能优化实践].《Oracle.DBA手记》编委会.扫描版

    包含了精心挑选的数据库诊断案例与性能优化实践经验,内容涉及oracle典型错误的分析和诊断,各种sql优化方法(包括调整索引,处理表碎片,优化分页查询,改善执行计划等),以及优化系统性能的经验。 作者不仅强调...

    深入浅出Oracle: DBA入门、进阶与诊断案例.pdf

    这可能包括数据库性能下降、数据丢失、系统崩溃等问题的排查步骤,以及如何利用Oracle自带的工具,如AWR(Automatic Workload Repository)、ASH(Active Session History)和 trace 文件进行问题定位。这些案例分析...

    ORACLE性能诊断工具 RDA

    Oracle性能诊断工具RDA(Real-Time Diagnostics and Advice)是Oracle数据库系统中的一款强大工具,主要用于识别和解决数据库性能问题。RDA集成了多种诊断功能,可以帮助DBA(数据库管理员)快速定位性能瓶颈,提高...

Global site tag (gtag.js) - Google Analytics