ASH Report For ORCL/orcl

DB NameDB IdInstanceInst numReleaseRACHost
ORCL1166960197orcl111.1.0.6.0NO11gR1NF

CPUsSGA SizeBuffer CacheShared PoolASH Buffer Size
1407M (100%)132M (32.4%)240M (59.0%)2.0M (0.5%)

Sample TimeData Source
Analysis Begin Time:26-May-08 16:44:48V$ACTIVE_SESSION_HISTORY
Analysis End Time:26-May-08 16:45:57V$ACTIVE_SESSION_HISTORY
Elapsed Time: 1.2 (mins)  
Sample Count: 216 
Average Active Sessions: 3.13 
Avg. Active Session per CPU: 3.13 
Report Target:None specified 

ASH Report


Back to Top

Top Events

Back to Top

Top User Events

EventEvent Class% EventAvg Active Sessions
CPU + Wait for CPUCPU46.301.45
db file sequential readUser I/O11.570.36
buffer deadlockOther11.110.35
cursor: pin S wait on XConcurrency5.090.16
log file syncCommit5.090.16

Back to Top Events
Back to Top

Top Background Events

EventEvent Class% ActivityAvg Active Sessions
log file parallel writeSystem I/O3.240.10
CPU + Wait for CPUCPU1.850.06
control file parallel writeSystem I/O1.850.06

Back to Top Events
Back to Top

Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
db file sequential read11.57"1","14419","1"1.39 file#block#blocks
buffer deadlock11.11"20972126","12","8192"2.78 dbaclass*10+modeflag
  "20972864","12","8192"1.85    
  "20972162","12","8192"1.39    
cursor: pin S wait on X5.09"175268898","7274496","327696"0.46 idnvaluewhere|sleeps
log file sync5.09"4964","0","0"0.93 buffer#NOT DEFINEDNOT DEFINED
latch: shared pool3.70"537530160","277","0"3.70 addressnumbertries

Back to Top Events
Back to Top

Load Profile

Back to Top

Top Service/Module

ServiceModule% ActivityAction% Action
BATCH_STDLoadGenerator40.28BuildInvoiceDetail36.11
   BuildInvoices4.17
SYS$USERSAdmin Connection17.13UNNAMED17.13
BATCH_LOWLoadGenerator15.28BuildInvoiceDetail13.89
   BuildInvoices1.39
SYS$BACKGROUNDUNNAMED7.41UNNAMED7.41
DSSwrc@11gR1NF (TNS V1-V3)4.63UNNAMED4.63

Back to Load Profile
Back to Top

Top Client IDs

Client ID% ActivityAvg Active SessionsUserProgramService
SYS@127.0.0.1@Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.17.130.54 SYSOMSSYS$USERS

Back to Load Profile
Back to Top

Top SQL Command Types

SQL Command TypeDistinct SQLIDs% ActivityAvg Active Sessions
SELECT1730.560.96
PL/SQL EXECUTE1027.310.86
INSERT122.690.71

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution% ActivityAvg Active Sessions
SQL Execution81.942.57
Parse24.070.75
Hard Parse17.590.55
PLSQL Execution16.670.52
Connection Management5.090.16

Back to Load Profile
Back to Top

Top SQL

Back to Top

Top SQL with Top Events

SQL IDPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL Text
1dmv3vasxtjtu20768610073421.76 buffer deadlock10.19** Row Source Not Available **10.19 INSERT INTO AP.INVOICE_ITEMS (...
     db file sequential read4.17** Row Source Not Available **4.17
     enq: TX - index contention3.24** Row Source Not Available **3.24
0wvttswphs2ky 39.72 CPU + Wait for CPU9.26** Row Source Not Available **9.26 BEGIN AP.PKG_LOAD_GENERATO...
4ymp71aaj3bud 36.48 CPU + Wait for CPU5.56** Row Source Not Available **5.56 BEGIN AP.PKG_LOAD_GENERATO...
9cgwmxs9wdnx4207686100796.02 CPU + Wait for CPU5.09SELECT STATEMENT2.78 SELECT PRODUCT_ID FROM (SELECT...
abchp6whju3fy33094928406.02 cursor: pin S wait on X4.63** Row Source Not Available **4.63 SELECT CUSTOMER_ID FROM (SELEC...

Back to Top SQL
Back to Top

Top SQL with Top Row Sources

SQL IDPlanHashSampled # of Executions% ActivityRow Source% RwSrcTop Event% EventSQL Text
1dmv3vasxtjtu20768610073421.76 ** Row Source Not Available **21.76buffer deadlock10.19 INSERT INTO AP.INVOICE_ITEMS (...
0wvttswphs2ky 39.72 ** Row Source Not Available **9.72CPU + Wait for CPU9.26 BEGIN AP.PKG_LOAD_GENERATO...
4ymp71aaj3bud 36.48 ** Row Source Not Available **6.48CPU + Wait for CPU5.56 BEGIN AP.PKG_LOAD_GENERATO...
9cgwmxs9wdnx4207686100796.02 SELECT STATEMENT3.70CPU + Wait for CPU2.78 SELECT PRODUCT_ID FROM (SELECT...
     SORT - ORDER BY STOPKEY1.85CPU + Wait for CPU1.85
abchp6whju3fy33094928406.02 ** Row Source Not Available **6.02cursor: pin S wait on X4.63 SELECT CUSTOMER_ID FROM (SELEC...

Back to Top SQL
Back to Top

Top SQL using literals

No data exists for this section of the report.

Back to Top SQL
Back to Top

Top Parsing Module/Action

ModuleAction% ActivityEvent% Event
Admin Connection 13.89CPU + Wait for CPU11.57
   db file sequential read2.31
SQL*Plus 4.17cursor: pin S wait on X1.85
   db file sequential read1.39
LoadGeneratorBuildInvoices3.24cursor: pin S wait on X2.78
wrc@11gR1NF (TNS V1-V3) 1.85CPU + Wait for CPU0.46

Back to Top SQL
Back to Top

Complete List of SQL Text

SQL IdSQL Text
0wvttswphs2kyBEGIN AP.PKG_LOAD_GENERATOR.RandomDML(250); COMMIT; END;
1dmv3vasxtjtuINSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID , LINE_ITEM_NBR , ACTIVE_IND , PRODUCT_ID , QTY , EXTENDED_AMT , TAXABLE_IND ) VALUES( :B6 , :B5 , 'Y' , :B4 , :B3 , :B2 , :B1 )
4ymp71aaj3budBEGIN AP.PKG_LOAD_GENERATOR.RandomDML(500); COMMIT; END;
9cgwmxs9wdnx4SELECT PRODUCT_ID FROM (SELECT PRODUCT_ID FROM OE.PRODUCT_INFORMATION SAMPLE(3) WHERE LIST_PRICE IS NOT NULL ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2
abchp6whju3fySELECT CUSTOMER_ID FROM (SELECT CUSTOMER_ID FROM OE.CUSTOMERS SAMPLE(3) ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2

Back to Top SQL
Back to Top

Top PL/SQL Procedures

PLSQL Entry Subprogram% ActivityPLSQL Current Subprogram% Current
AP.PKG_LOAD_GENERATOR.RANDOMDML54.63 SQL38.43
   SYS.DBMS_RANDOM.SEED#211.57
   AP.PKG_LOAD_GENERATOR.RANDOMDML4.63
SYS.DBMS_WORKLOAD_REPLAY.REPORT17.13 SQL16.20
AP.PKG_LOAD_GENERATOR.RANDOMQUERY3.24 AP.PKG_LOAD_GENERATOR.RANDOMQUERY1.85
   SQL1.39
SYS.DBMS_WORKLOAD_REPLAY.START_REPLAY1.39 UNKNOWN_PLSQL_ID <7749,28>0.93
SYS.DBMS_WORKLOAD_REPLAY.CLIENT_CONNECT1.39 UNKNOWN_PLSQL_ID <7749,28>1.39


Back to Top

Top Java Workload

No data exists for this section of the report.


Back to Top

Top Sessions

Back to Top

Top Sessions

Sid, Serial#% ActivityEvent% EventUserProgram# Samples ActiveXIDs
132, 13417.13CPU + Wait for CPU12.50 SYSOMS27/69 [ 39%]0
  db file sequential read4.17   9/69 [ 13%]0
106, 1516.20CPU + Wait for CPU8.33 OEwrc@11gR1NF (TNS V1-V3)18/69 [ 26%]1
  buffer deadlock2.31   5/69 [ 7%]1
  db file sequential read1.85   4/69 [ 6%]1
123, 11516.20CPU + Wait for CPU9.26 OEwrc@11gR1NF (TNS V1-V3)20/69 [ 29%]1
  buffer deadlock1.39   3/69 [ 4%]1
99, 2013.43buffer deadlock5.56 SHwrc@11gR1NF (TNS V1-V3)12/69 [ 17%]1
  CPU + Wait for CPU5.09   11/69 [ 16%]1
104, 186.94CPU + Wait for CPU4.63 OEwrc@11gR1NF (TNS V1-V3)10/69 [ 14%]1
  buffer deadlock1.85   4/69 [ 6%]1

Back to Top Sessions
Back to Top

Top Blocking Sessions

Blocking Sid% ActivityEvent Caused% EventUserProgram# Samples ActiveXIDs
149, 204.63cursor: pin S wait on X4.63 SHwrc@11gR1NF (TNS V1-V3)5/69 [ 7%]0
162, 12.31log file sync2.31 SYSoracle@11gR1NF (LGWR)7/69 [ 10%]0
104, 181.85enq: TX - index contention1.85 OEwrc@11gR1NF (TNS V1-V3)15/69 [ 22%]1
123, 1151.85buffer busy waits0.93 OEwrc@11gR1NF (TNS V1-V3)35/69 [ 51%]1
99, 201.39library cache load lock0.93 SHwrc@11gR1NF (TNS V1-V3)30/69 [ 43%]1

Back to Top Sessions
Back to Top

Top Sessions running PQs

No data exists for this section of the report.

Back to Top Sessions
Back to Top

Top Objects/Files/Latches

Back to Top

Top DB Objects

Object ID% ActivityEvent% EventObject Name (Type)Tablespace
5263.70db file sequential read3.70 SYS.SYS_C00643 (INDEX)SYSTEM
733771.39buffer busy waits0.93 AP.INVOICE_ITEMS_PK_IDX (INDEX)EXAMPLE

Back to Top Objects/Files/Latches
Back to Top

Top DB Files

File ID% ActivityEvent% EventFile NameTablespace
13.70db file sequential read3.70 /u01/app/oracle/oradata/orcl/system01.dbfSYSTEM
33.24db file sequential read3.24 /u01/app/oracle/oradata/orcl/undotbs01.dbfUNDOTBS1
51.85db file sequential read1.85 /u01/app/oracle/oradata/orcl/example01.dbfEXAMPLE

Back to Top Objects/Files/Latches
Back to Top

Top Latches

Latch% LatchBlocking Sid% ActivityMax Sampled Wait secs# Waits Sampled# Sampled Wts < 10ms# Sampled Wts 10ms - 100ms# Sampled Wts 100ms - 1s# Sampled Wts > 1s
latch: shared pool3.70Held Shared2.310.47 50050
  132, 1341.390.18 30210

Back to Top Objects/Files/Latches
Back to Top

Activity Over Time

Slot Time (Duration)Slot CountEventEvent Count% Event
16:44:48 (12 secs)56CPU + Wait for CPU198.80
  cursor: pin S wait on X115.09
  db file sequential read62.78
16:45:00 (57 secs)160CPU + Wait for CPU8539.35
  buffer deadlock2411.11
  db file sequential read198.80


Back to Top

End of Report