ASH Report For CCPROD/CCPROD2

DB NameDB IdInstanceInst numReleaseRACHost
CCPROD1971250524CCPROD2211.1.0.7.0YESprod-rac02.cc.cl.datapipe.net

CPUsSGA SizeBuffer CacheShared PoolASH Buffer Size
162,039M (100%)512M (25.1%)784M (38.5%)29.0M (1.4%)

Sample TimeData Source
Analysis Begin Time:24-Nov-10 09:00:46V$ACTIVE_SESSION_HISTORY
Analysis End Time:24-Nov-10 12:00:46V$ACTIVE_SESSION_HISTORY
Elapsed Time: 180.0 (mins)  
Sample Count: 8,721 
Average Active Sessions: 0.81 
Avg. Active Session per CPU: 0.05 
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 CPUCPU49.170.40
log file syncCommit11.440.09
gc buffer busy acquireCluster8.120.07
gc buffer busy releaseCluster5.760.05
gc current block busyCluster4.600.04

Back to Top Events
Back to Top

Top Background Events

EventEvent Class% ActivityAvg Active Sessions
log file parallel writeSystem I/O5.620.05
CPU + Wait for CPUCPU1.750.01
control file sequential readSystem I/O1.590.01
gcs log flush syncOther1.560.01
db file parallel writeSystem I/O1.350.01

Back to Top Events
Back to Top

Top Cluster Events

Event% EventRemote Instance#% Activity
gc buffer busy acquire8.1215.22
   2.90
gc buffer busy release5.7613.58
   2.18
gc current block busy4.6014.60

Back to Top Events
Back to Top

Top Event P1/P2/P3 Values

Event% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3
log file sync11.44"4597","0","0"0.25 buffer#NOT DEFINEDNOT DEFINED
gc buffer busy acquire8.12"6","8521","1"0.67 file#block#class#
gc buffer busy release5.76"6","13416","1"0.63 file#block#class#
log file parallel write5.62"1","2","1"4.12 filesblocksrequests
enq: TX - index contention2.80"1415053316","9633800","306"0.26 name|modeusn<<16 | slotsequence

Back to Top Events
Back to Top

Load Profile

Back to Top

Top Service/Module

ServiceModule% ActivityAction% Action
CCPRODJDBC Thin Client84.26UNNAMED84.26
SYS$BACKGROUNDUNNAMED12.92UNNAMED12.92

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
SELECT2,91047.510.38
INSERT1723.790.19

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution% ActivityAvg Active Sessions
SQL Execution61.920.50
PLSQL Execution24.410.20
Parse9.680.08
Hard Parse8.900.07
PLSQL Compilation2.900.02

Back to Load Profile
Back to Top

Top Remote Instances

Wait Class% Wait ClassRemote Instance#% Activity
Cluster20.67115.47
   5.21

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 190022.05 gc buffer busy acquire7.77** Row Source Not Available **7.77 insert into AUDIT_FILTER_LOG (...
  190022.05 gc buffer busy release5.62** Row Source Not Available **5.62 insert into AUDIT_FILTER_LOG (...
  190022.05 gc current block busy4.44** Row Source Not Available **4.44 insert into AUDIT_FILTER_LOG (...
8y3wz9swz7zjp2955338022743.14 CPU + Wait for CPU3.14TABLE ACCESS - BY INDEX ROWID3.12 select user_id from user_profi...
d2ubmzm9u5c2k8238941701932.21 CPU + Wait for CPU2.21VIEW2.05 Select user_id from user_profi...
59nf178vrwr1k13416073111101.28 CPU + Wait for CPU1.18TABLE ACCESS - FULL0.99 select this_.SESSION_LOG_ID as...
fzdsykgx23nxx31645247131081.28 CPU + Wait for CPU1.09TABLE ACCESS - FULL0.85 select this_.User_ID as User1_...

Back to Top SQL
Back to Top

Top SQL with Top Row Sources

SQL IDPlanHashSampled # of Executions% ActivityRow Source% RwSrcTop Event% EventSQL Text
ffb720ahf723v 190022.05 ** Row Source Not Available **22.05gc buffer busy acquire7.77 insert into AUDIT_FILTER_LOG (...
8y3wz9swz7zjp2955338022743.14 TABLE ACCESS - BY INDEX ROWID3.12CPU + Wait for CPU3.12 select user_id from user_profi...
d2ubmzm9u5c2k8238941701932.21 VIEW2.05CPU + Wait for CPU2.05 Select user_id from user_profi...
59nf178vrwr1k13416073111101.28 TABLE ACCESS - FULL1.09CPU + Wait for CPU0.99 select this_.SESSION_LOG_ID as...
fzdsykgx23nxx31645247131081.28 TABLE ACCESS - FULL1.04CPU + Wait for CPU0.85 select this_.User_ID as User1_...

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
1802195279141173101014.181036 d2ubmzm9u5c2k Select user_id from user_profi... d2ubmzm9u5c2k  
562149424304831732810.56648 8y3wz9swz7zjp select user_id from user_profi... 8y3wz9swz7zjp  
88738226350581781593.90340        
138601349667356243911.66140 07m1sad7dj1g8 select User_Calender_Header_ID... gsj86dfmvsbjc select User_Calender_Header_ID...
20064292417991457521.41123 0r38cnkzx304r select User_Calender_Header_ID... gu6fw2w1rz1dw select User_Calender_Header_ID...

Back to Top SQL
Back to Top

Top Parsing Module/Action

ModuleAction% ActivityEvent% Event
JDBC Thin Client 8.77CPU + Wait for CPU8.70

Back to Top SQL
Back to Top

Complete List of SQL Text

SQL IdSQL Text
07m1sad7dj1g8 select User_Calender_Header_ID, Patient_ID, Event_Type_Lkp_ID, case when event_type='Appointment' then title when event_type='Todo' then title else encryption.decrypt_char(title, '0123456789ABCDEFFEDCBA9876543210') end as title, Start_Time, To_Time, Quantity, Cost, When_To_End, End_Time, Recurrent_ID, Notes, Assigned_To, Contact_With, Contact_Lkp_ID, Contact_Where, encryption.decrypt_char(User_Name, '0123456789ABCDEFFEDCBA9876543210') as user_name, encryption.decrypt_char(Contact_Name, '0123456789ABCDEFFEDCBA9876543210') as contact_name, Recurrent_Type, Event_Type, Drug_Id, Side_Effects, Currently_Used, Special_Instruction, Dosage, Times_Perday, Timings, Status , Created_By, Created_Date, Updated_By, Updated_Date, weekday, monthday, encryption.decrypt_char(FIRST_NAME, '0123456789ABCDEFFEDCBA9876543210') as first_name, encryption.decrypt_char(LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as last_name, encryption.decrypt_char(MIDDLE_INITIAL, '0123456789ABCDEFFEDCBA9876543210') as middle_initial, PICTURE_PATH, encryption.decrypt_char(PATIENT_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_name, encryption.decrypt_char(PATIENT_LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_last_name, NOTES_COUNT, ADD_TO_CALENDAR, ISTIMEBOUND from vw_user_calendar_iteration where Patient_ID in (2312, 2339) and (((Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI') or End _Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) or (When_To_End='Never' or When_To_End='Until')) or (((Event_Type='Todo' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or (((Event_Type='Item' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or ((Event_Type='Prescription' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null))))) and event_type !='Treatment' and add_to_calendar =1 order by start_time asc
0r38cnkzx304r select User_Calender_Header_ID, Patient_ID, Event_Type_Lkp_ID, case when event_type='Appointment' then title when event_type='Todo' then title else encryption.decrypt_char(title, '0123456789ABCDEFFEDCBA9876543210') end as title, Start_Time, To_Time, Quantity, Cost, When_To_End, End_Time, Recurrent_ID, Notes, Assigned_To, Contact_With, Contact_Lkp_ID, Contact_Where, encryption.decrypt_char(User_Name, '0123456789ABCDEFFEDCBA9876543210') as user_name, encryption.decrypt_char(Contact_Name, '0123456789ABCDEFFEDCBA9876543210') as contact_name, Recurrent_Type, Event_Type, Drug_Id, Side_Effects, Currently_Used, Special_Instruction, Dosage, Times_Perday, Timings, Status , Created_By, Created_Date, Updated_By, Updated_Date, weekday, monthday, encryption.decrypt_char(FIRST_NAME, '0123456789ABCDEFFEDCBA9876543210') as first_name, encryption.decrypt_char(LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as last_name, encryption.decrypt_char(MIDDLE_INITIAL, '0123456789ABCDEFFEDCBA9876543210') as middle_initial, PICTURE_PATH, encryption.decrypt_char(PATIENT_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_name, encryption.decrypt_char(PATIENT_LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_last_name, NOTES_COUNT, ADD_TO_CALENDAR, ISTIMEBOUND from vw_user_calendar_iteration where Patient_ID in (2418) and (((Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI') or End_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) or (When_To_End='Never' or When_To_End='Until')) or (((Event_Type='Todo' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or (((Event_Type='Item' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or ((Event_Type='Prescription' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null))))) and event_type !='Treatment' and add_to_calendar =1 order by start_time asc
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 )
8y3wz9swz7zjpselect user_id from user_profile where (lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210')) = 'loadtest222_3' or lower(encryption.decrypt_char(user_name, '0123456789ABCDEFFEDCBA9876543210')) = 'loadtest222_3') and password = '7cbfa25a0ba328ee7c8058e62dcdbedf8544f293' and is_active = 1
d2ubmzm9u5c2kSelect user_id from user_profile where lower(encryption.decrypt_char(user_email, '0123456789ABCDEFFEDCBA9876543210'))='loadtest222_3@tuscan.com'
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
gsj86dfmvsbjc select User_Calender_Header_ID, Patient_ID, Event_Type_Lkp_ID, case when event_type='Appointment' then title when event_type='Todo' then title else encryption.decrypt_char(title, '0123456789ABCDEFFEDCBA9876543210') end as title, Start_Time, To_Time, Quantity, Cost, When_To_End, End_Time, Recurrent_ID, Notes, Assigned_To, Contact_With, Contact_Lkp_ID, Contact_Where, encryption.decrypt_char(User_Name, '0123456789ABCDEFFEDCBA9876543210') as user_name, encryption.decrypt_char(Contact_Name, '0123456789ABCDEFFEDCBA9876543210') as contact_name, Recurrent_Type, Event_Type, Drug_Id, Side_Effects, Currently_Used, Special_Instruction, Dosage, Times_Perday, Timings, Status , Created_By, Created_Date, Updated_By, Updated_Date, weekday, monthday, encryption.decrypt_char(FIRST_NAME, '0123456789ABCDEFFEDCBA9876543210') as first_name, encryption.decrypt_char(LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as last_name, encryption.decrypt_char(MIDDLE_INITIAL, '0123456789ABCDEFFEDCBA9876543210') as middle_initial, PICTURE_PATH, encryption.decrypt_char(PATIENT_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_name, encryption.decrypt_char(PATIENT_LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_last_name, NOTES_COUNT, ADD_TO_CALENDAR, ISTIMEBOUND from vw_user_calendar_iteration where Patient_ID in (4852, 4978) and (((Start_Time between to_date('11-23-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-24-2010 18:29', 'MM-DD-YYYY HH24:MI') or End _Time between to_date('11-23-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-24-2010 18:29', 'MM-DD-YYYY HH24:MI')) or (When_To_End='Never' or When_To_End='Until')) or (((Event_Type='Todo' and Start_Time between to_date('11-23-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-24-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or (((Event_Type='Item' and Start_Time between to_date('11-23-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-24-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or ((Event_Type='Prescription' and Start_Time between to_date('11-23-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-24-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null))))) and event_type !='Treatment' and add_to_calendar =1 order by start_time asc
gu6fw2w1rz1dw select User_Calender_Header_ID, Patient_ID, Event_Type_Lkp_ID, case when event_type='Appointment' then title when event_type='Todo' then title else encryption.decrypt_char(title, '0123456789ABCDEFFEDCBA9876543210') end as title, Start_Time, To_Time, Quantity, Cost, When_To_End, End_Time, Recurrent_ID, Notes, Assigned_To, Contact_With, Contact_Lkp_ID, Contact_Where, encryption.decrypt_char(User_Name, '0123456789ABCDEFFEDCBA9876543210') as user_name, encryption.decrypt_char(Contact_Name, '0123456789ABCDEFFEDCBA9876543210') as contact_name, Recurrent_Type, Event_Type, Drug_Id, Side_Effects, Currently_Used, Special_Instruction, Dosage, Times_Perday, Timings, Status , Created_By, Created_Date, Updated_By, Updated_Date, weekday, monthday, encryption.decrypt_char(FIRST_NAME, '0123456789ABCDEFFEDCBA9876543210') as first_name, encryption.decrypt_char(LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as last_name, encryption.decrypt_char(MIDDLE_INITIAL, '0123456789ABCDEFFEDCBA9876543210') as middle_initial, PICTURE_PATH, encryption.decrypt_char(PATIENT_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_name, encryption.decrypt_char(PATIENT_LAST_NAME, '0123456789ABCDEFFEDCBA9876543210') as patient_last_name, NOTES_COUNT, ADD_TO_CALENDAR, ISTIMEBOUND from vw_user_calendar_iteration where Patient_ID in (2470) and (((Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI') or End_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) or (When_To_End='Never' or When_To_End='Until')) or (((Event_Type='Todo' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or (((Event_Type='Item' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null)) or ((Event_Type='Prescription' and Start_Time between to_date('11-20-2010 18:30', 'MM-DD-YYYY HH24:MI') and to_date('11-27-2010 18:29', 'MM-DD-YYYY HH24:MI')) and (End_Time is null or When_To_End is null))))) and event_type !='Treatment' and add_to_calendar =1 order by start_time asc

Back to Top SQL
Back to Top

Top PL/SQL Procedures

PLSQL Entry Subprogram% ActivityPLSQL Current Subprogram% Current
CCUSER.ENCRYPTION.DECRYPT_CHAR22.80 SYS.DBMS_CRYPTO_FFI.DECRYPT#16.70
   UNKNOWN_PLSQL_ID <7223,27>5.08
   UNKNOWN_PLSQL_ID <7223,28>3.83


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
536, 16.08log file parallel write5.62 SYSoracle@prod-ra...pe.net (LGWR)490/11K [ 5%]0
467, 96261.82CPU + Wait for CPU1.19 CCUSERJDBC Thin Client104/11K [ 1%]2
465, 46561.77CPU + Wait for CPU1.24 CCUSERJDBC Thin Client108/11K [ 1%]2
396, 65881.72CPU + Wait for CPU1.24 CCUSERJDBC Thin Client108/11K [ 1%]6
404, 95991.41CPU + Wait for CPU0.96 CCUSERJDBC Thin Client84/11K [ 1%]2

Back to Top Sessions
Back to Top

Top Blocking Sessions

Blocking Sid (Inst)% ActivityEvent Caused% EventUserProgram# Samples ActiveXIDs
536, 1( 2)13.04log file sync11.42 SYSoracle@prod-ra...pe.net (LGWR)530/11K [ 5%]0
  gcs log flush sync1.56     

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
8391518.52gc buffer busy acquire7.77 CCUSER.AUDIT_FILTER_LOG_PK (INDEX)CCDATA
  gc buffer busy release5.62   
  gc current block busy4.44   

Back to Top Objects/Files/Latches
Back to Top

Top DB Files

File ID% ActivityEvent% EventFile NameTablespace
621.41gc buffer busy acquire8.12 +CCPROD/ccprod/ccdata_01.dbfCCDATA
  gc buffer busy release5.64   
  gc current block busy4.59   

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
09:00:46 (17.2 min)43control file sequential read160.18
  CPU + Wait for CPU80.09
  control file parallel write80.09
09:18:00 (18.0 min)765CPU + Wait for CPU2422.77
  log file sync1331.53
  gc buffer busy acquire770.88
09:36:00 (18.0 min)614CPU + Wait for CPU3283.76
  log file sync840.96
  log file parallel write440.50
09:54:00 (18.0 min)900CPU + Wait for CPU3844.40
  log file sync1191.36
  gc buffer busy acquire630.72
10:12:00 (18.0 min)1,343CPU + Wait for CPU6627.59
  log file sync1561.79
  gc buffer busy acquire1421.63
10:30:00 (18.0 min)834CPU + Wait for CPU4324.95
  log file sync1201.38
  log file parallel write600.69
10:48:00 (18.0 min)1,583CPU + Wait for CPU7708.83
  gc buffer busy acquire2022.32
  log file sync1381.58
11:06:00 (18.0 min)623CPU + Wait for CPU3994.58
  log file sync700.80
  log file parallel write360.41
11:24:00 (18.0 min)1,752CPU + Wait for CPU1,09712.58
  log file sync1601.83
  gc buffer busy release1141.31
11:42:00 (18.0 min)239CPU + Wait for CPU1131.30
  enq: TX - index contention260.30
  gc buffer busy acquire250.29
12:00:00 (46 secs)25db file sequential read90.10
  CPU + Wait for CPU60.07
  reliable message50.06


Back to Top

End of Report