ASH Report For CCPROD/CCPROD1

DB NameDB IdInstanceInst numReleaseRACHost
CCPROD1971250524CCPROD1111.1.0.7.0YESprod-rac01.cc.cl.datapipe.net

CPUsSGA SizeBuffer CacheShared PoolASH Buffer Size
162,039M (100%)304M (14.9%)864M (42.4%)29.0M (1.4%)

Sample TimeData Source
Analysis Begin Time:26-Oct-10 12:30:54V$ACTIVE_SESSION_HISTORY
Analysis End Time:26-Oct-10 14:30:54V$ACTIVE_SESSION_HISTORY
Elapsed Time: 120.0 (mins)  
Sample Count: 2,885 
Average Active Sessions: 0.40 
Avg. Active Session per CPU: 0.03 
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 CPUCPU16.980.07
gc buffer busy acquireCluster10.780.04
log file syncCommit9.950.04
gc current block busyCluster7.520.03
gc buffer busy releaseCluster3.880.02

Back to Top Events
Back to Top

Top Background Events

EventEvent Class% ActivityAvg Active Sessions
db file parallel writeSystem I/O8.700.03
control file parallel writeSystem I/O8.010.03
control file sequential readSystem I/O5.720.02
CPU + Wait for CPUCPU5.680.02
log file parallel writeSystem I/O5.370.02

Back to Top Events
Back to Top

Top Cluster Events

Event% EventRemote Instance#% Activity
gc buffer busy acquire10.8126.62
   4.19
gc current block busy7.6627.66
gc buffer busy release3.88 1.98
  21.91
gc cr block busy1.8721.87

Back to Top Events
Back to Top

Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
gc buffer busy acquire10.81"6","63410","1"2.05 file#block#class#
  "6","62879","1"1.14    
  "6","62866","1"1.01    
log file sync9.95"30","0","0"1.07 buffer#NOT DEFINEDNOT DEFINED
db file parallel write8.70"1","0","2147483647"8.70 requestsinterrupttimeout
control file parallel write8.01"3","3","3"7.45 filesblock#requests
control file sequential read6.69"0","35","1"3.33 file#block#blocks

Back to Top Events
Back to Top

Load Profile

Back to Top

Top Service/Module

ServiceModule% ActivityAction% Action
CCPRODJDBC Thin Client56.67UNNAMED56.67
SYS$BACKGROUNDUNNAMED37.50UNNAMED37.50
SYS$USERSracgimon@prod-rac01.cc.cl.datapipe.net (TNS V1-1.91UNNAMED1.91
SYS$BACKGROUNDMMON_SLAVE1.56Auto-Flush Slave Action0.76

Back to Load Profile
Back to Top

Top Client IDs

No data exists for this section of the report.

Back to Load Profile
Back to Top

Top SQL Command Types

SQL Command TypeDistinct SQLIDs% ActivityAvg Active Sessions
INSERT1025.160.10
SELECT30220.420.08

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution% ActivityAvg Active Sessions
SQL Execution42.500.17
PLSQL Execution5.340.02
Parse4.960.02
Hard Parse4.190.02
Connection Management2.180.01

Back to Load Profile
Back to Top

Top Remote Instances

Wait Class% Wait ClassRemote Instance#% Activity
Cluster25.89219.62
   6.20

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
ffb720ahf723v 58423.88 gc buffer busy acquire9.46** Row Source Not Available **9.46 insert into AUDIT_FILTER_LOG (...
  58423.88 gc current block busy7.07** Row Source Not Available **7.07 insert into AUDIT_FILTER_LOG (...
  58423.88 enq: TX - index contention2.50** Row Source Not Available **2.50 insert into AUDIT_FILTER_LOG (...
7frqszzdu8sgg4109302118361.56 control file sequential read0.87FIXED TABLE - FULL0.55 select DECODE(UPPER(d.OPEN_MOD...
fzkvszqchfpc12274523690411.42 CPU + Wait for CPU1.42SORT - ORDER BY1.32 Select PATIENT_INSURANCE_ID, ...
fzdsykgx23nxx3164524713231.25 CPU + Wait for CPU0.52TABLE ACCESS - FULL0.42 select this_.User_ID as User1_...
59nf178vrwr1k1341607311251.07 CPU + Wait for CPU0.62TABLE ACCESS - FULL0.45 select this_.SESSION_LOG_ID as...

Back to Top SQL
Back to Top

Top SQL with Top Row Sources

SQL IDPlanHashSampled # of Executions% ActivityRow Source% RwSrcTop Event% EventSQL Text
ffb720ahf723v 58423.88 ** Row Source Not Available **23.88gc buffer busy acquire9.46 insert into AUDIT_FILTER_LOG (...
7frqszzdu8sgg4109302118361.56 FIXED TABLE - FULL0.87control file sequential read0.55 select DECODE(UPPER(d.OPEN_MOD...
fzkvszqchfpc12274523690411.42 SORT - ORDER BY1.32CPU + Wait for CPU1.32 Select PATIENT_INSURANCE_ID, ...
fzdsykgx23nxx3164524713231.25 TABLE ACCESS - FULL1.14gc buffer busy acquire0.49 select this_.User_ID as User1_...
59nf178vrwr1k1341607311251.07 TABLE ACCESS - FULL0.90CPU + Wait for CPU0.45 select this_.SESSION_LOG_ID as...

Back to Top SQL
Back to Top

Top SQL using literals

FORCE_MATCHING_SIGNATURE% Activity# of Sampled SQL VersionsExample SQL 1Example SQL TEXT 1Example SQL 2Example SQL TEXT 2
180219527914117310101.9856 174u0fggcjyyq Select user_id from user_profi... g1n67hrm8zqk0 Select user_id from user_profi...
56214942430483173281.4943 070uxapgbuyzj select user_id from user_profi... g86u27w2866qz select user_id from user_profi...

Back to Top SQL
Back to Top

Top Parsing Module/Action

ModuleAction% ActivityEvent% Event
JDBC Thin Client 4.58CPU + Wait for CPU3.99

Back to Top SQL
Back to Top

Complete List of SQL Text

SQL IdSQL Text
070uxapgbuyzjselect user_id from user_profile where (lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210')) = 'prescription1@loadtest.com' or lower(encryption.decrypt_char(user_name, '0123456789ABCDEFFEDCBA9876543210')) = 'prescription1@loadtest.com') and password = '7cbfa25a0ba328ee7c8058e62dcdbedf8544f293' and is_active = 1
174u0fggcjyyqSelect user_id from user_profile where lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210'))='load08test@tuscan.com'
59nf178vrwr1kselect this_.SESSION_LOG_ID as SESSION1_48_0_, this_.USER_ID as USER2_48_0_, this_.LOGIN_TIME as LOGIN3_48_0_, this_.LOGOUT_TIME as LOGOUT4_48_0_, this_.SESSION_TOKEN as SESSION5_48_0_, this_.IP_ADD as IP6_48_0_, this_.AGENT as AGENT48_0_, this_.DESCRIPTION as DESCRIPT8_48_0_, this_.IS_AUTO_LOGGED_OUT as IS9_48_0_ from SESSION_LOG this_ where (this_.USER_ID=:1 and this_.SESSION_TOKEN=:2 )
7frqszzdu8sggselect DECODE(UPPER(d.OPEN_MODE), 'READ WRITE', 'R', 'N') , DECODE(UPPER(i.STATUS), 'OPEN', 'O', 'N') into :b0, :b1 from v$database d , gv$instance i where i.INSTANCE_NAME=:b2
ffb720ahf723vinsert into AUDIT_FILTER_LOG (SESSION_LOG_ID, USER_ID, AUDIT_TIME, URI, MODULE_NAME, HTTP_METHOD, OPERATION_NAME, QUERY_STRING, DATA, AUDIT_LOG_ID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )
fzdsykgx23nxxselect this_.User_ID as User1_47_0_, this_.Session_Token as Session2_47_0_, this_.Last_Action as Last3_47_0_, this_.Is_Logged_in as Is4_47_0_ from User_Profile_Login this_ where (this_.Is_Logged_in=:1 and this_.Session_Token=:2 ) and this_.User_ID=:3
fzkvszqchfpc1 Select PATIENT_INSURANCE_ID, PATIENT_ID, encryption.decrypt_char(PATIENT_NAME, '0123456789ABCDEFFEDCBA9876543210') as PATIENT_NAME, encryption.decrypt_char(PLAN_ID, '0123456789ABCDEFFEDCBA9876543210') as PLAN_ID, encryption.decrypt_char(PLAN_NAME, '0123456789ABCDEFFEDCBA9876543210') as PLAN_NAME, encryption.decrypt_char(INSURANCE_NAME, '0123456789ABCDEFFEDCBA9876543210') as INSURANCE_NAME, encryption.decrypt_char(GROUP_NUMBER, '0123456789ABCDEFFEDCBA9876543210') as GROUP_NUMBER, encryption.decrypt_char(NOTES, '0123456789ABCDEFFEDCBA9876543210') as NOTES, encryption.decrypt_char(CONTACT_NUMBER, '0123456789ABCDEFFEDCBA9876543210') as CONTACT_NUMBER, encryption.decrypt_char(PRIMARY_INSURANCE_HOLDER, '0123456789ABCDEFFEDCBA9876543210') as PRIMARY_INSURANCE_HOLDER, encryption.decrypt_char(PENSION_NUMBER, '0123456789ABCDEFFEDCBA9876543210') as PENSION_NUMBER, encryption.decrypt_char(DEDUCTIBLE, '0123456789ABCDEFFEDCBA9876543210') as DEDUCTIBLE, encryption.decrypt_char(MEDICAL_NUMBER, '0123456789ABCDEFFEDCBA9876543210') as MEDICAL_NUMBER, encryption.decrypt_char(EMPLOYER, '0123456789ABCDEFFEDCBA9876543210') as EMPLOYER, encryption.decrypt_char(STATUS, '0123456789ABCDEFFEDCBA9876543210') as STATUS, encryption.decrypt_char(POLICY_NUMBER, '0123456789ABCDEFFEDCBA9876543210') as POLICY_NUMBER, INSURANCE_TYPE_LKP_ID, INSURANCE_TYPE as INSURANCETYPE from vw_insurance_lkp where patient_id = 1857 order by INSURANCE_TYPE asc
g1n67hrm8zqk0Select user_id from user_profile where lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210'))='loadtest454_tuscan1@its.jnj.com'
g86u27w2866qzselect user_id from user_profile where (lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210')) = 'loadtest1@tuscan.com' or lower(encryption.decrypt_char(user_name, '0123456789ABCDEFFEDCBA9876543210')) = 'loadtest1@tuscan.com') and password = '7cbfa25a0ba328ee7c8058e62dcdbedf8544f293' and is_active = 1

Back to Top SQL
Back to Top

Top PL/SQL Procedures

PLSQL Entry Subprogram% ActivityPLSQL Current Subprogram% Current
CCUSER.ENCRYPTION.DECRYPT_CHAR4.71 SYS.DBMS_CRYPTO_FFI.DECRYPT#11.11
   UNKNOWN_PLSQL_ID <7223,27>1.04


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
535, 18.98control file parallel write8.01 SYSoracle@prod-ra...pe.net (CKPT)231/7,200 [ 3%]0
536, 15.48log file parallel write5.37 SYSoracle@prod-ra...pe.net (LGWR)155/7,200 [ 2%]0
531, 14.68CPU + Wait for CPU3.43 SYSoracle@prod-ra...pe.net (ASMB)99/7,200 [ 1%]0
  ASM file metadata operation1.25   36/7,200 [ 1%]0
538, 14.68db file parallel write4.61 SYSoracle@prod-ra...pe.net (DBW0)133/7,200 [ 2%]0
546, 14.26control file sequential read3.81 SYSoracle@prod-ra...pe.net (LMON)110/7,200 [ 2%]0

Back to Top Sessions
Back to Top

Top Blocking Sessions

Blocking Sid (Inst)% ActivityEvent Caused% EventUserProgram# Samples ActiveXIDs
536, 1( 1)12.37log file sync9.95 SYSoracle@prod-ra...pe.net (LGWR)158/7,200 [ 2%]0
  gcs log flush sync2.43     
501,60545( 1)1.77gc buffer busy acquire1.77 CCUSERJDBC Thin Client100/7,200 [ 1%]46
485,54182( 1)1.32gc buffer busy acquire1.32 CCUSERJDBC Thin Client58/7,200 [ 1%]28
451,55126( 1)1.25gc buffer busy acquire1.25 CCUSERJDBC Thin Client30/7,200 [ 0%]11

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
7932819.69gc buffer busy acquire9.53 CCUSER.AUDIT_FILTER_LOG_PK (INDEX)CCDATA
  gc current block busy7.07   
  gc buffer busy release2.36   
3761.80gc buffer busy release1.53 SYS.AUD$ (TABLE)SYSTEM
792461.32db file sequential read1.32 CCUSER.AUDIT_FILTER_LOG (TABLE)CCDATA

Back to Top Objects/Files/Latches
Back to Top

Top DB Files

File ID% ActivityEvent% EventFile NameTablespace
624.96gc buffer busy acquire10.61 +CCPROD/ccprod/ccdata_01.dbfCCDATA
  gc current block busy7.38   
  gc buffer busy release2.36   
12.11gc buffer busy release1.53 +CCPROD/ccprod/system01.dbfSYSTEM

Back to Top Objects/Files/Latches
Back to Top

Top Latches

No data exists for this section of the report.

Back to Top Objects/Files/Latches
Back to Top

Activity Over Time

Slot Time (Duration)Slot CountEventEvent Count% Event
12:30:54 (5.1 min)21control file parallel write80.28
  CPU + Wait for CPU60.21
  control file sequential read60.21
12:36:00 (12.0 min)49control file sequential read180.62
  CPU + Wait for CPU160.55
  reliable message80.28
12:48:00 (12.0 min)285gc buffer busy acquire632.18
  control file parallel write411.42
  gc current block busy351.21
13:00:00 (12.0 min)880gc buffer busy acquire1695.86
  db file parallel write1394.82
  gc current block busy1063.67
13:12:00 (12.0 min)160db file parallel write642.22
  control file parallel write491.70
  CPU + Wait for CPU190.66
13:24:00 (12.0 min)136CPU + Wait for CPU361.25
  log file sync150.52
  control file sequential read140.49
13:36:00 (12.0 min)590CPU + Wait for CPU1766.10
  log file sync1003.47
  gc buffer busy release712.46
13:48:00 (12.0 min)137CPU + Wait for CPU541.87
  gc buffer busy acquire130.45
  gc current block busy130.45
14:00:00 (12.0 min)94CPU + Wait for CPU391.35
  db file sequential read100.35
  control file sequential read70.24
14:12:00 (12.0 min)118CPU + Wait for CPU531.84
  log file sync140.49
  log file parallel write110.38
14:24:00 (6.9 min)415CPU + Wait for CPU1675.79
  log file sync602.08
  gc buffer busy acquire431.49


Back to Top

End of Report