Oracle性能监控和性能调优

-- Oracle性能监控和性能调优 ---
By LXM. 2009-07-17

1. 使用StatsPack: 性能监控和统计工具包。
     a. 安装:
        - 用oracle帐号进入$ORACLE_HOME/rdbms/admin目录，然后运行sqlplus "/ as sysdba" (用SYSDBA权限登录)
        - SQL> @spcreate
        这个脚本会创建perfstat账户，生成监控和统计相关的表。提示输入默认表空间和默认临时表空间，可以使用TOOLS和TEMP。输入perfstat用户的秘密并记住这个密码。
     b. 使用
       之后可以运行应用程序访问数据库。然后可以在TOAD或SQLPLus下生成监控快照(SNAP)，并根据各个快照输出统计结果。

       注意，使用之前，确保timed_statistics参数为true。
       SQL> alter system set timed_statistics=true;

       - 在TOAD下，用perfstat用户登录：
        选择“DataBase"-"Monitor"-"StatsPack Browser"，在左下角“Charts & Data Grids”中选中复选框中的"Built-in"所有组件，然后点击左上角"New Snap Shot"，以生成快照。每隔几分钟生成一次。结果会显示在右边的图形中。
      
     - SQLPLus，先进入到$ORACLE_HOME/rdbms/admin目录，然后登录到Oracle服务器（不是客户端）：
       - 用perfstat用户登录，执行生成快照的过程语句:
       SQL> connect perfstat/perfstat
       SQL> execute STATSPACK.SNAP
       - 多执行几次。然后使用以下命令查看报表（注意先进入rdms/admin目录)：
       - 查看SNAP表，以确定snap_id:
         SQL> select snap_id,TO_CHAR(snap_time,'yyyymmddhhmmss') from STATS$SNAPSHOT;
   SNAP_ID TO_CHAR(SNAP_T
   ---------- --------------
        21 20090722040736
         22 20090722050748
    
        SQL>@spreport

       注意，使用@spreport需要输入起始snap_id。即使在TOAD中生成的快照，也可以使用这个语句进行查看。

      Report中，以下是要关注的：
     - Instance Activity Stats(实例活动):
       -sorts (disk): 磁盘排序，这个值应该低。否则应提高sort_area_size值。
       -sorts (memory) ：内存排序，这个值应该高。
       -table scans (long tables) : 大表扫描，这个值应该低。否则应查看是否未使用索引，是否有聚合语句(Group by, sum,count等）
      
     - Library Cache Activity for DB （库缓存）
       SQL_Area,Index等Pct Miss要低！
  
    
     注意，对于上线产品，监控完成后，应该将timed_statistics设成false.
       SQL> alter system set timed_statistics=false;

 

2. TOAD的"DataBase"-"Monitor"-"Top Session Finder"工具
   使用Toad的Top Session Finder，可以找到最耗资源的Session，然后查看此Session所在的机器/进程、执行的SQL语句等信息。
   从而判断哪个进程的哪个SQL语句比较耗数据库资源。
  
   进入"Top Session Finder"后，点击左上角绿色的“三角”运行按钮(List Sessions)，列出所有的Sessions.
   然后点击右边的"Weighted sum"进行排序，也就是按权重的从高到低排序（这个权重按照CPU占用率、磁盘访问率等参数综合计算，越高表示资源耗用越大）。
  
   然后选中排在前面的一些SID(SessionID), 单击右键，选择“Find Selected session in session browser"：
   双击相应的SID，在右侧会列出各项具体参数，例如：
   -”Current Statements"会列出这个Session当前的SQL语句。可以判断哪个SQL语句比较耗资源。
   -"Open Cursors"会列出游标情况，点中"Sql txt"，则下侧的"Explain Plan"中，会给出执行计划。执行计划可以看到是否有全表扫描、是否按索引操作等关键信息。
  
  
3. ORACLE Enterprise Manager Console 自带的Top SQL 工具
   进入Tools-"Diagnostic Pack"-"Top Sql“.
  
   利用这个工具，也可以找到最耗资源的SQL语句情况。
  
  

4. 执行计划：使用Explain Plan确定SQL访问路径。
     - 安装. 先进入oracle服务器的rdbms/admin目录，然后在SQLPlus下运行:
       SQL> connect emsp_v3/emsp
          Connected.
       SQL> @utlxplan
       此脚本会创建PLAN_TABLE，创建在emsp_v3用户下。这样，可以在这个用户下执行计划。

    - 用上述用户登录后，执行计划,如下:
      explain plan
      set Statement_Id = 'TEST'
      for
      select * from smshistory where submittime>'20090717' and rownum<2;
      commit;

      for后面是需要解释查询的语句。这个语句不会实际执行，而是解释查询的执行计划。
       
    - 查看结果:
     select ID, Parent_ID,operation,options,cost,cardinality,io_cost,bytes,filter_predicates from plan_table;
    (在TOAD中看方便一些）

    如果出现TABLE ACCESS FULL，表示进行了全表或Partition扫描。

SQL> connect username/userpass@dbservicename

    另一种查看执行计划的方法是自动跟踪：
    - 第一步：登入sql/plus 执行命令（无先后顺序）
      SQL> set   time   on; (说明：打开时间显示）    
      SQL> set   autotrace   on; (说明：打开自动分析统计，并显示SQL语句的运行结果）   
      SQL> set   autotrace   traceonly; (说明：打开自动分析统计，不显示SQL语句的运行结果）二者选一个
     第二步： 输入你要查看的sql 语句。