ASH Report For HOLLYC5/hollyc5
| DB Name | DB Id | Instance | Inst num | Release | RAC | Host |
|---|
| HOLLYC5 | 2589500184 | hollyc5 | 1 | 10.2.0.4.0 | NO | a3oracle |
| CPUs | SGA Size | Buffer Cache | Shared Pool | ASH Buffer Size |
|---|
| 8 | 4,160M (100%) | 2,400M (57.7%) | 547M (13.1%) | 15.5M (0.4%) |
| Sample Time | Data Source |
|---|
| Analysis Begin Time: | 09-Apr-13 09:52:00 | V$ACTIVE_SESSION_HISTORY |
| Analysis End Time: | 09-Apr-13 09:57:00 | V$ACTIVE_SESSION_HISTORY |
| Elapsed Time: | 5.0 (mins) | |
| Sample Count: | 3,272 | |
| Average Active Sessions: | 10.91 | |
| Avg. Active Session per CPU: | 1.36 | |
| Report Target: | None specified | |
ASH Report
Back to Top
Top Events
Back to Top
Top User Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|
| db file sequential read | User I/O | 57.70 | 6.29 |
| read by other session | User I/O | 30.23 | 3.30 |
| CPU + Wait for CPU | CPU | 5.35 | 0.58 |
Back to Top Events
Back to Top
Top Background Events
| Event | Event Class | % Activity | Avg Active Sessions |
|---|
| CPU + Wait for CPU | CPU | 3.76 | 0.41 |
Back to Top Events
Back to Top
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|
| db file sequential read | 57.70 | "1","182","1" | 0.03 |
file# | block# | blocks |
| read by other session | 30.23 | "9","372385","1" | 0.18 |
file# | block# | class# |
Back to Top Events
Back to Top
Load Profile
Back to Top
Top Service/Module
| Service | Module | % Activity | Action | % Action |
|---|
| SYS$USERS | JDBC Thin Client | 94.16 | UNNAMED | 94.16 |
| SYS$BACKGROUND | UNNAMED | 4.95 | UNNAMED | 4.95 |
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
- 'Distinct SQLIDs' is the count of the distinct number of SQLIDs
with the given SQL Command Type found over all the ASH samples
in the analysis period
| SQL Command Type | Distinct SQLIDs | % Activity | Avg Active Sessions |
|---|
| SELECT | 99 | 88.45 | 9.65 |
| INSERT | 14 | 3.30 | 0.36 |
Back to Load Profile
Back to Top
Top SQL
Back to Top
Top SQL Statements
| SQL ID | Planhash | % Activity | Event | % Event | SQL Text |
|---|
| 971t88sd375j3 | 4147465347 | 10.27 | read by other session | 7.06 |
** SQL Text Not Available ** |
| | | | db file sequential read | 3.03 |
|
| 0yw3p5agy7vmm | 2576131269 | 8.86 | read by other session | 5.65 |
SELECT COUNT(1) FROM (select t... |
| | | | db file sequential read | 3.18 |
|
| fc3q43f2y7djy | 3871842275 | 8.01 | db file sequential read | 8.01 |
SELECT COUNT(1) FROM (select d... |
| ck1z3nx4j41ar | 3240916582 | 6.72 | db file sequential read | 3.27 |
SELECT * FROM ( SELECT rs_row_... |
| | | | read by other session | 3.15 |
|
| 1cuy4p6gpmnpb | 4147465347 | 6.45 | read by other session | 4.06 |
select t.customer_id from c5_c... |
| | | | db file sequential read | 2.23 |
|
Back to Top SQL
Back to Top
Top SQL using literals
| Plan Hash | % Activity | # of Sampled SQL Versions | Example SQL 1 | Example SQL TEXT 1 | Example SQL 2 | Example SQL TEXT 2 |
|---|
| 4147465347 | 38.17 | 1249 |
00pnhk94ud0cc |
select t.customer_id from c5_c... |
gz0s3n7dxf71v |
select t.customer_id from c5_c... |
| 2576131269 | 15.37 | 503 |
02h5chcusvtr1 |
select count(0) from c5_work_s... |
gcujat01jya18 |
SELECT COUNT(1) FROM (select t... |
Back to Top SQL
Back to Top
Complete List of SQL Text
| SQL Id | SQL Text |
|---|
| 00pnhk94ud0cc | select t.customer_id from c5_customer t where exists (select * from c5_customer_contact c where t.customer_id = c.customer_id and c.contact_no in ('15927679269')) and t.customer_name = :1 and t.account_id=:2 and t.customer_type=:3 |
| 02h5chcusvtr1 | select count(0) from c5_work_sheet t1 where t1.ACCOUNT_ID = 'B000000005486' and ( t1.current_handler = '24fcddbf-76a6-4933-86bb-6f5071da91a4' or ( t1.current_handler is null and (t1.owner is null or t1.owner='24fcddbf-76a6-4933-86bb-6f5071da91a4' ))) and (1!=1 or (t1.workflow_id ='b2b7af06-c636-4815-86e2-ea909b42a921' and t1.now_step ='62' ) or (t1.workflow_id ='b552abf0-30e6-488a-a2c9-6fc73bdd0
7e9' and t1.now_step ='62' ) or (t1.workflow_id ='ab627b1d-213e-4070-9261-20e094316965' and t1.now_step ='61' ) or (t1.workflow_id ='d4d154cc-f228-4fdf-83fd-017364c5dfe3' and t1.now_step ='61' ) or (t1.workflow_id ='c8c7f9a9-5117-4468-b965-26d6fe6f682c' and t1.now_step ='62' ) ) and t1.WORKFLOW_ID <> '_opportunity' |
| 0yw3p5agy7vmm | SELECT COUNT(1) FROM (select t1.*, fun_getcustomername(t1.customer_id) customer_name from c5_work_sheet t1 where t1.ACCOUNT_ID = :1 and(t1.current_handler = :2 or ( t1.current_handler ='none' and (t1.owner ='none' or t1.owner =:3))) and (1!=1 or (t1.workflow_id = 'b2b7af06-c636-4815-86e2-ea909b42a921' and t1.now_step = 28 ) or (t1.workflow_id = 'd4d154cc-f228-4fdf-83fd-017364c5dfe3' and t1.now_ste
p = 28 ) or (t1.workflow_id = 'ab627b1d-213e-4070-9261-20e094316965' and t1.now_step = 28 ) or (t1.workflow_id = 'c8c7f9a9-5117-4468-b965-26d6fe6f682c' and t1.now_step = 29 ) or (t1.workflow_id = 'b552abf0-30e6-488a-a2c9-6fc73bdd07e9' and t1.now_step = 28 )) and t1.WORKFLOW_ID <> :4 order by t1.recent_update_time desc ) |
| 1cuy4p6gpmnpb | select t.customer_id from c5_customer t where exists (select * from c5_customer_contact c where t.customer_id = c.customer_id and c.contact_no in ('18021004588')) and t.customer_name = :1 and t.account_id=:2 and t.customer_type=:3 |
| 971t88sd375j3 | ** SQL Text Not Available ** |
| ck1z3nx4j41ar | SELECT * FROM ( SELECT rs_row_.*, rownum rownum_ FROM ( select cu.*, fun_getContactByCustId(cu.customer_id) telContacts from C5_CUSTOMER cu where 1=1 and cu.OWNER=:1 and cu.ACCOUNT_ID=:2 and cu.recalls=:3 order by RECENT_UPDATE_TIME desc , 1 asc ) rs_row_ where rownum < :4) WHERE rownum_ >= :5 |
| fc3q43f2y7djy | SELECT COUNT(1) FROM (select distinct t1.*, fun_getcustomername(t1.customer_id) customer_name from c5_work_sheet t1, c5_work_sheet_record t2 where t1.work_sheet_id = t2.work_sheet_id and t1.account_id =:1 and t2.handler=:2 and t1.RECENT_UPDATE_TIME >= :3 and t1.RECENT_UPDATE_TIME <= :4 and t1.WORKFLOW_ID <> :5 order by t1.recent_update_time desc ) |
| gcujat01jya18 | SELECT COUNT(1) FROM (select t1.*, fun_getcustomername(t1.customer_id) customer_name from c5_work_sheet t1 where t1.ACCOUNT_ID = :1 and(t1.current_handler = :2 or ( t1.current_handler ='none' and (t1.owner ='none' or t1.owner =:3))) and (1!=1 or (t1.workflow_id = 'd4d154cc-f228-4fdf-83fd-017364c5dfe3' and t1.now_step = 3 ) or (t1.workflow_id = 'c8c7f9a9-5117-4468-b965-26d6fe6f682c' and t1.now_st
ep = 3 ) or (t1.workflow_id = 'ab627b1d-213e-4070-9261-20e094316965' and t1.now_step = 3 ) or (t1.workflow_id = 'b552abf0-30e6-488a-a2c9-6fc73bdd07e9' and t1.now_step = 3 ) or (t1.workflow_id = 'b2b7af06-c636-4815-86e2-ea909b42a921' and t1.now_step = 3 )) and t1.WORKFLOW_ID <> :4 order by t1.recent_update_time desc ) |
| gz0s3n7dxf71v | select t.customer_id from c5_customer t where exists (select * from c5_customer_contact c where t.customer_id = c.customer_id and c.contact_no in ('15973586078')) and t.customer_name = :1 and t.account_id=:2 and t.customer_type=:3 |
Back to Top SQL
Back to Top
Top PL/SQL Procedures
- 'PL/SQL entry subprogram' represents the application's top-level
entry-point(procedure, function, trigger, package initialization
or RPC call) into PL/SQL.
- 'PL/SQL current subprogram' is the pl/sql subprogram being executed
at the point of sampling . If the value is 'SQL', it represents
the percentage of time spent executing SQL for the particular
plsql entry subprogram
| PLSQL Entry Subprogram | % Activity | PLSQL Current Subprogram | % Current |
|---|
| C5_APP.FUN_GETCONTACTBYCUSTID | 7.30 |
SQL | 7.24 |
Back to Top
Top Sessions
Back to Top
Top Sessions
- '# Samples Active' shows the number of ASH samples in which the session
was found waiting for that particular event. The percentage shown
in this column is calculated with respect to wall clock time
and not total database activity.
- 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the session was waiting for that particular event
- For sessions running Parallel Queries, this section will NOT aggregate
the PQ slave activity into the session issuing the PQ. Refer to
the 'Top Sessions running PQs' section for such statistics.
| Sid, Serial# | % Activity | Event | % Event | User | Program | # Samples Active | XIDs |
|---|
| 366, 120 | 8.04 | db file sequential read | 8.04 |
C5_APP | JDBC Thin Client | 263/300 [ 88%] | 0 |
| 522,15673 | 5.47 | read by other session | 3.03 |
C5_APP | JDBC Thin Client | 99/300 [ 33%] | 0 |
| | | db file sequential read | 2.35 |
| | 77/300 [ 26%] | 1 |
| 351, 1046 | 5.32 | read by other session | 3.73 |
C5_APP | JDBC Thin Client | 122/300 [ 41%] | 0 |
| | | db file sequential read | 1.53 |
| | 50/300 [ 17%] | 0 |
| 275, 52 | 5.26 | read by other session | 3.33 |
C5_APP | JDBC Thin Client | 109/300 [ 36%] | 0 |
| | | db file sequential read | 1.71 |
| | 56/300 [ 19%] | 1 |
| 356,59451 | 5.23 | read by other session | 2.84 |
C5_APP | JDBC Thin Client | 93/300 [ 31%] | 0 |
| | | db file sequential read | 2.29 |
| | 75/300 [ 25%] | 1 |
Back to Top Sessions
Back to Top
Top Blocking Sessions
No data exists for this section of the report.
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
- With respect to Application, Cluster, User I/O and buffer busy waits only.
| Object ID | % Activity | Event | % Event | Object Name (Type) | Tablespace |
|---|
| 54831 | 18.83 | db file sequential read | 9.96 |
C5_APP.C5_CUSTOMER (TABLE) | USERS |
| | | read by other session | 8.86 |
| |
| 55470 | 15.77 | db file sequential read | 10.42 |
C5_APP.IDX_CUSTOMER_CONTACT (INDEX) | TABLE_IDX |
| | | read by other session | 5.35 |
| |
| 135402 | 15.22 | db file sequential read | 7.82 |
C5_APP.IDX_WORKSHEET_ACCID (INDEX) | TABLE_IDX |
| | | read by other session | 7.40 |
| |
| 55469 | 9.05 | read by other session | 6.17 |
C5_APP.IDX_CUSTOMER (INDEX) | TABLE_IDX |
| | | db file sequential read | 2.87 |
| |
| 135436 | 4.37 | db file sequential read | 4.37 |
C5_APP.IDX_CALLSHEET_OPTION_1 (INDEX) | CALLSHEET_IDX |
Back to Top Objects/Files/Latches
Back to Top
Top DB Files
- With respect to Cluster and User I/O events only.
| File ID | % Activity | Event | % Event | File Name | Tablespace |
|---|
| 12 | 21.12 | db file sequential read | 13.23 |
/u02/oradata/oradata/hollyc5/tableIdx01.dbf | TABLE_IDX |
| | | read by other session | 7.89 |
| |
| 13 | 18.46 | db file sequential read | 11.74 |
/u02/oradata/oradata/hollyc5/tableIdx02.dbf | TABLE_IDX |
| | | read by other session | 6.72 |
| |
| 19 | 11.52 | db file sequential read | 7.00 |
/var/oradata/hollyc5/tableIdx03.dbf | TABLE_IDX |
| | | read by other session | 4.52 |
| |
| 7 | 9.08 | db file sequential read | 6.94 |
/u02/oradata/oradata/hollyc5/users02.dbf | USERS |
| | | read by other session | 2.14 |
| |
| 4 | 8.56 | db file sequential read | 5.78 |
/u02/oradata/oradata/hollyc5/users01.dbf | USERS |
| | | read by other session | 2.51 |
| |
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
- Analysis period is divided into smaller time slots
- Top 3 events are reported in each of those slots
- 'Slot Count' shows the number of ASH samples in that slot
- 'Event Count' shows the number of ASH samples waiting for
that event in that slot
- '% Event' is 'Event Count' over all ASH samples in the analysis period
| Slot Time (Duration) | Slot Count | Event | Event Count | % Event |
|---|
| 09:52:00 (0 secs) | 11 | db file sequential read | 8 | 0.24 |
| | | read by other session | 3 | 0.09 |
| 09:52:00 (1.0 min) | 935 | db file sequential read | 598 | 18.28 |
| | | read by other session | 267 | 8.16 |
| | | CPU + Wait for CPU | 46 | 1.41 |
| 09:53:00 (1.0 min) | 1,068 | db file sequential read | 669 | 20.45 |
| | | read by other session | 288 | 8.80 |
| | | CPU + Wait for CPU | 89 | 2.72 |
| 09:54:00 (1.0 min) | 822 | read by other session | 430 | 13.14 |
| | | db file sequential read | 306 | 9.35 |
| | | CPU + Wait for CPU | 71 | 2.17 |
| 09:55:00 (1.0 min) | 287 | db file sequential read | 212 | 6.48 |
| | | CPU + Wait for CPU | 57 | 1.74 |
| | | log file parallel write | 7 | 0.21 |
| 09:56:00 (1.0 min) | 149 | db file sequential read | 95 | 2.90 |
| | | CPU + Wait for CPU | 35 | 1.07 |
| | | log file sync | 9 | 0.28 |
Back to Top
End of Report