statspack使用笔记 3-4

roothomes

statspack使用笔记 3

SQL优化

Response Time(响应时间) = Service Time(服务时间) + Wait Time(等待时间)

Service Time = CPU Parse + CPU Recursive + CPU Other

CPU other = CPU used by this session - parse time cpu - recursive cpu usage

Service Time from CPU used by this session or CPU used when call started(会话占用CPU的时间，CPU的服务时间)

Wait Time is the sum of time waited for non-idle Wait Events(会话脱离CPU处于等待状态，非空闲等待时间的总和，空闲等待表示一个会话在数据库实例中未工作，非空闲等待表示会话在数据库实例中调用了其他的进程进行工作，例如DBWR从磁盘读数据，此时没有使用CPU)

CPU Parse from parse time cpu(CPU分析时间)

CPU Recursive from recursive cpu usage(CPU递归调用时间)

CPU Other from block accesses(逻辑读，CPU工作时间)

例如：

        Top 5 Timed Events

        ~~~~~~~~~~~~~~~~~~                                                     % Total

        Event                                               Waits    Time (s) Ela Time

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

        CPU time                                                        4,776    73.32

        db file sequential read                           185,588       1,577    24.22

        log file sync                                     115,489          93     1.43

        log file sequential read                            1,012          31      .48

        db file parallel write                              2,816          13      .20

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

        

        

        Statistic                               Total (cs)  per Second    per Trans  

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

        CPU used by this session                  477,566     513.5          4.0

        CPU used when call started                477,560     513.5          4.0

        parse time cpu                              7,374       7.9          0.1

        recursive cpu usage                       255,893     275.2          2.2

        

        

        由上述数据计算出来: 

        Service Time = CPU time = CPU used when call started = 477560/100 s = 4776 s

        Response Time = 4776/0.7332 = 6514 s

        Wait Time = Response Time - Service Time = 6514 – 4776 = 1738 s

        CPU Other = Service Time - parse time cpu - recursive cpu usage = 4776 – 74 - 2559

                  = 2143 s

        

        计算各项数据响应百分比: 

        CPU time% = CPU time/Response Time = 4776/6514 = 73.32%

        CPU Other% = CPU Other/Response Time = 2143/6514 = 32.90%

        CPU Parse% = parse time cpu/Response Time = 74/6514 = 1.14%

        CPU Recursive% = recursive cpu usage/Response Time = 2559/6514 = 39.29%

        Wait Time% = Wait Time/Response Time = 1738/6514 = 26.68%

        db file sequential read% = db file sequential read/Response Time = 1577/6514 = 24.21%

statspack使用笔记 4

根据计算出来的响应百分比，可以大致了解当前系统的性能瓶颈。

如果CPU time占用的百分比较高，则观察消耗CPU的是哪一部分：

主要用于CPU Parse，那么很有可能是大量SQL语句正在进行硬分析(未绑定变量造成)。需要察看SQL ordered by Parse Calls for DB中的语句。

主要用于CPU Other，那么很有可能是SQL语句正在访问大量的数据块(全表扫描，全索引扫描，错误的索引范围扫描)。需要察看SQL ordered by Gets for DB中的语句。

如果磁盘I/O占用的百分比较高(db file scattered read，db file sequential read)，需要察看SQL ordered by Reads for DB中的语句

从操作系统中分析过高的CPU占用和磁盘I/O是由一个还是多个Oracle进程引起的。

        如果操作系统工具显示大量CPU的消耗是由某些Oracle进程占用，那么需要分析这些进程在长时间的执行那些SQL语句，可以通过作SQL TRACE进行跟踪或者观察Session调用的SQL语句主要是那一些。

        --检查进程的sql语句  通过TOP检查性能

        select p.spid, s.sid, q.sql_text, s.last_call_et, s.status

          from v$process p,

               v$session s,

               v$sqltext q

         where p.addr = s.paddr

           and s.sql_address = q.address

           and p.spid = 27053    --操作系统进程号，需要根据实际情况进行更改

         order by s.sid, q.piece

        如果CPU消耗分布在大多数的Oracle进程中，Cpu Other在相应时间中占有大部分，那么，需要分析statspack报告中的SQL ordered by Gets的语句，从中找到逻辑读最高的SQL语句进行优化。

        如果从操作系统中观察到磁盘I/O很高(sar –d进行观察)，那么需要分析statspack报告中SQL ordered by Reads的语句，从中找到物理读最高的SQL语句进行优化。

statspack使用笔记 4

检查后台作业和批量处理的事务，以及长时间处理的session。

        --检查运行时间超过20s的session和sql语句

        select p.spid, s.sid, q.sql_text, s.last_call_et, s.status

          from v$process p,

               v$session s,

               v$sqltext q

         where p.addr = s.paddr

           and p.background <> 1

        and s.sql_address = q.address

           and s.status = 'ACTIVE'

           and s.last_call_et > 20

         order by s.sid, q.piece