Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
-------------------------------------------------------------
Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
CPU used by this session 358,806 130.5 12,372.6
parse time cpu 38 0.0 1.3
recursive cpu usage 186,636 67.9 6,435.7
From these figures we can obtain:
* Wait Time = 10,827 x 100% / 52,01% = 20,817 cs
* Service Time = 358,806 cs
* Response Time = 358,806 + 20,817 = 379,623 cs
* CPU Other = 358,806 - 38 - 186,636 = 172,132 cs
If we now calculate percentages for the top Response Time components:
* CPU Other = 45.34%
* CPU Recursive = 49.16%
* direct path read = 2.85%
* etc. etc.
CPU Other is a significant component of Response Time, so a possible next step is to look at the SQL ordered by Gets section.
Example from Statspack: (Oracle9i Release 2 & above)
Starting with Oracle9i Release 2, Statspack presents Service Time (obtained from the statistic CPU used by this session ) together with the top Wait Events in a section called Top 5 Timed Events, which replaces the section Top 5 Wait Events of previous releases.
Here is an example:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
library cache lock 141 424 76.52
db file scattered read 3,367 96 17.40
CPU time 32 5.79
db file sequential read 161 1 .18
control file parallel write 245 0 .05
-------------------------------------------------------------
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 3,211 4.3 1,605.5
parse time cpu 59 0.1 29.5
recursive cpu usage 232 0.3 116.0
These figures give us directly the percentages of the Wait Events against the total Response Time so no further calculations are necessary to assess the impact of Wait Events. Service Time is presented as CPU time in this section and corresponds to the total CPU utilisation. We can drill down to the various components of Service Time as follows:
* CPU Other = 3,211 - 59 - 232 = 2,920 cs
* CPU Other = 2,920 / 3,211 x 5.79% = 5.26%
* CPU Parse = 59 / 3,211 x 5.79% = 0.11%
* CPU Recursive = 232 / 3,211 x 5.79% = 0.42%

Buffer Gets Executions Gets per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
91,938,671 4,249 21,637.7 24.1 3503723562
SELECT "TEKSTI_IK", "U_VERSION", "ASIAKIR_IK", "KONTAKT_IK", "L
OMAKE_IK", "PVM", "TIEDON_LKA", "TEKSTI_VER", "TEKST_TILA", "VA
LK_AUKKO", "SUOR_PA_IK", "SUOR_PA_ID", "RESURSS_IK", "RESURSS_I
39,196,483 4,257 9,207.5 10.3 576408779
SELECT "KONTAKT_IK", "ASIAKAS_IK", "KAYNTIPVM", "KLO_JNRO", "KT
_PIKASEL", "RESURSS_ID", "SUOR_PA_IK", "SUOR_PA_ID", "TEKSTI_IK
", "KT_TEKSTI", "KONT_LAJI" FROM "TEI1000_VIEW" WHERE (kontakt_i

31,870,113 4,262 7,477.7 8.3 3583640853

SELECT "LAAKE_T_IK", "U_VERSION", "TAPAHTU_IK", "ASIAKAS_IK", "

TOT_DATIM", "LAAKE_IK", "KAUPPANIMI", "VALM_MUOTO", "VAHVUUS",

"PAKK_KOKO", "ANNOS", "INDIKAATIO", "PYSYVAIS", "VOIM_OLEVA", "S

30,567,449 4,259 7,177.1 8.0 1901268379

SELECT "LAB_TUL_IK", "U_VERSION", "TAPAHTU_IK", "ASIAKAS_IK", "

TOT_DATE", "TOT_TIME", "PALVELU_IK", "PALV_LYHEN", "SL_KOODI",

"OSAVAS_NRO", "OSAVAS_HOP", "VAST_TYYPP", "VAST_ARVO", "VAST_SIJ

这个第一个sql语句，哈希值为3503723526占有实例中所有缓存区的24.1%，剩下的三条语句分别占10.3%，8.3%，8.0%，第一条语句使用跟多，所以应该优先的调整，因为它花费cpu的时间更多。

下面是再给出一个例子

Buffer Gets Executions Gets per Exec % Total Hash Value

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

3,200,593 1 3,200,593.0 52.2 397365298

select c.contract_no||'/'||c.contract_version, c.owner_ag

ency_id, a.agency_name, TERRITORYPACK.getsalescont

act(a.agency_id,'SALES','N'), c.start_date, LEAST(

404,024 88,481 4.6 6.6 985453413

select cv_dist_flag from applicant

where applicant_id = :p1

178,600 3,028 59.0 2.9 3013728279

select privilege#,level from sysauth$ connect by grantee#=prior

privilege# and privilege#>0 start with (grantee#=:1 or grantee#=

Statspack报告不是总能完全显示sql语句，哈希值可以来使用以下的查询

SELECT sql_text

FROM v$sql_text

WHERE hash_value = '&hash_value_of_SQL'

ORDER BY piece;

高cpu的statspack分析是响应时间的重要组成部分，它可以使因为游标反复打开或者关闭，所以要避免这一点

Parse Calls Executions Parses Hash Value

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

13,632,745 13,632,745 98.90 3980186470

SELECT distinct TS002.C_JOB_DEP, TS002.C_JOB FROM TS002_JO

B_DEP TS002, TS001_JOB TS001 WHERE TS001.C_JOB = TS002.C_JO

B_DEP AND TS002.C_JOB = :b1 AND TS001.C_TIP_JOB !=

11,701 27,255,840 0.08 3615375148

COMMIT

8,192 8,192 0.06 238087931

select t.schema, t.name, t.flags, q.name from system.aq$_queue_t

ables t, sys.aq$_queue_table_affinities aft, system.aq$_que

ues q where aft.table_objno = t.objno and aft.owner_instance = :

1 and q.table_objno = t.objno and q.usage = 0 and b

itand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, aft
8,192 8,192 0.06 2780709284
select q_name, state, delay, expiration, rowid, msgid, dequeue
_msgid, chain_no, local_order_no, enq_time, enq_tid, step_no,
priority, exception_qschema, exception_queue, retry_count, corri
d, time_manager_info, sender_name, sender_address, sender_prot
ocol from SYS.AQ_SRVNTFN_TABLE where time_manager_info