/*
|| Oracle 11g Database Replay - Part 3: Report Examples
||
|| Demonstrates Oracle 11g …
|| - Creation of …
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| These examples are provided to demonstrate various features of Oracle 11g
|| Database Replay (DBR), and they should be carefully proofread before being
|| executed against any existing Oracle database to avoid potential damage!
*/
/*
|| Report 3.1:
|| What were the results of the last successful DBR Workload Capture operation?
*/
Database Capture Report For DB10G
DB Name DB Id Release RAC Capture Name Status
------------ ----------- ----------- --- -------------------------- ----------
DB10G 33332549 10.2.0.4.0 NO DBR_CAPTURE_100 COMPLETED
Start time: 23-Jun-08 23:26:25 (SCN = 983513)
End time: 23-Jun-08 23:32:35 (SCN = 988306)
Duration: 6 minutes 10 seconds
Capture size: 338.08 KB
Directory object: DBRCONTROL
Directory path: /home/oracle/DBRControl
Directory shared in RAC: TRUE
Filters used: 2 EXCLUSION filters
Error code:
Error message: Termination (after 90 seconds)
Captured Workload Statistics DB/Inst: DB10G/ Snaps: 57-58
-> 'Value' represents the corresponding statistic aggregated
across the entire captured database workload.
-> '% Total' is the percentage of 'Value' over the corresponding
system-wide aggregated total.
Statistic Name Value % Total
---------------------------------------- ------------- ---------
DB time (secs) 812.67 99.91
Average Active Sessions 2.20 N/A
User calls captured 1844 100.00
User calls captured with Errors 0 N/A
Session logins 80 90.91
Transactions 1365 97.01
-------------------------------------------------------------
Top Events Captured DB/Inst: DB10G/ Snaps: 57-58
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 82.76 1.96
buffer deadlock Other 5.37 0.13
latch: cache buffers chains Concurrency 2.63 0.06
log file switch completion Configuration 1.71 0.04
buffer busy waits Concurrency 1.48 0.04
-------------------------------------------------------------
Top Service/Module Captured DB/Inst: DB10G/ Snaps: 57-58
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
db10g LoadGenerator 94.52 BuildInvoiceDetail 89.50
BuildInvoices 3.54
SpinCPUs 1.48
-------------------------------------------------------------
Top SQL Captured DB/Inst: DB10G/ Snaps: 57-58
SQL ID % Activity Event % Event
----------------------- -------------- ------------------------------ -------
1dmv3vasxtjtu 17.81 CPU + Wait for CPU 8.22
INSERT 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 )
buffer deadlock 4.79
latch: cache buffers chains 2.63
9qqv0czupcx22 17.58 CPU + Wait for CPU 17.58
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(50); COMMIT; END;
9cz4yg1a8m2km 13.93 CPU + Wait for CPU 13.93
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(125); COMMIT; END;
5jzk6sd0qjafm 13.24 CPU + Wait for CPU 13.24
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(75); COMMIT; END;
45cd1yp2y379a 10.73 CPU + Wait for CPU 10.73
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(30); COMMIT; END;
-------------------------------------------------------------
Top Sessions Captured DB/Inst: DB10G/ Snaps: 57-58
-> '# 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
-------------------- ------------------------------ ------------------ --------
148, 29 9.02 CPU + Wait for CPU 8.22
OE sqlplus@11gR1NF (TNS V1-V3) 72/370 [ 19%] 41
144, 65 7.99 CPU + Wait for CPU 6.16
OE sqlplus@11gR1NF (TNS V1-V3) 54/370 [ 15%] 38
buffer deadlock 1.03
9/370 [ 2%] 1
143, 37 7.65 CPU + Wait for CPU 6.39
HR sqlplus@11gR1NF (TNS V1-V3) 56/370 [ 15%] 25
log file switch completion 1.26
11/370 [ 3%] 1
147, 214 5.48 CPU + Wait for CPU 5.48
OE sqlplus@11gR1NF (TNS V1-V3) 48/370 [ 13%] 22
137, 96 5.25 CPU + Wait for CPU 4.79
OE sqlplus@11gR1NF (TNS V1-V3) 42/370 [ 11%] 34
-------------------------------------------------------------
Top Events containing Unreplayable Calls DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module containing Unreplayable Calls DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL containing Unreplayable Calls DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions containing Unreplayable Calls DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Events Filtered Out DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module Filtered Out DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Filtered Out DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions Filtered Out DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Events (Jobs and Background Activity) DB/Inst: DB10G/ Snaps: 57-58
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
log file parallel write System I/O 2.63 0.06
Log archive I/O System I/O 1.26 0.03
-------------------------------------------------------------
Top Service/Module (Jobs and Background Activity)DB/Inst: DB10G/ Snaps: 57-58
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 5.14 UNNAMED 5.14
-------------------------------------------------------------
Top SQL (Jobs and Background Activity) DB/Inst: DB10G/ Snaps: 57-58
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions (Jobs and Background Activity) DB/Inst: DB10G/ Snaps: 57-58
-> '# 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
-------------------- ------------------------------ ------------------ --------
166, 1 2.63 log file parallel write 2.63
SYS oracle@11gR1NF (LGWR) 23/370 [ 6%] 0
154, 1 1.83 Log archive I/O 1.26
SYS oracle@11gR1NF (ARC0) 11/370 [ 3%] 0
-------------------------------------------------------------
Workload Filters DB/Inst: DB10G/ Snaps: 57-58
# Filter Name Type Attribute Value
--- ------------------------ ------- ------------ --------------------------
1 ORACLE MANAGEMENT AGENT EXCLUDE PROGRAM emagent%
2 ORACLE MANAGEMENT SERVIC EXCLUDE PROGRAM OMS
-------------------------------------------------------------
End of Report
/*
|| Report 3.2:
|| Monitoring the results of an active DBR Workload Replay operation
*/
>> Just after execution of DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY:
Mon Jun 23 page 1
Replayed Workloads
(From DBA_WORKLOAD_REPLAYS)
# of Replay Replay Replay Total Clnt
Rply Replay Replayed Replay Replay Cptr User Prepare Start End Replay Clnt Sess
ID# Operation On Database DB Version Directory ID# Calls Status Time Time Time Time (s) Sess Done
---- ------------ ------------ ---------- ---------------- ---- ------ ------------ ----------- ----------- ----------- --------- ------ ------
25 DBR_RPL_100 RACDB 11.1.0.6.0 DBRCONTROL 22 INITIALIZED 0 0
>> Just after execution of DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY:
Mon Jun 23 page 1
Replayed Workloads
(From DBA_WORKLOAD_REPLAYS)
# of Replay Replay Replay Total Clnt
Rply Replay Replayed Replay Replay Cptr User Prepare Start End Replay Clnt Sess
ID# Operation On Database DB Version Directory ID# Calls Status Time Time Time Time (s) Sess Done
---- ------------ ------------ ---------- ---------------- ---- ------ ------------ ----------- ----------- ----------- --------- ------ ------
25 DBR_RPL_100 RACDB 11.1.0.6.0 DBRCONTROL 22 PREPARE 2008-06-23 0 0
21:23:09
>> Just after execution of DBMS_WORKLOAD_REPLAY.START_REPLAY:
Mon Jun 23 page 1
Replayed Workloads
(From DBA_WORKLOAD_REPLAYS)
# of Replay Replay Replay Total Clnt
Rply Replay Replayed Replay Replay Cptr User Prepare Start End Replay Clnt Sess
ID# Operation On Database DB Version Directory ID# Calls Status Time Time Time Time (s) Sess Done
---- ------------ ------------ ---------- ---------------- ---- ------ ------------ ----------- ----------- ----------- --------- ------ ------
25 DBR_RPL_100 RACDB 11.1.0.6.0 DBRCONTROL 22 221 IN PROGRESS 2008-06-23 2008-06-23 1 0
21:23:09 21:27:53
>> At last, a successful conclusion!
Mon Jun 23 page 1
Replayed Workloads
(From DBA_WORKLOAD_REPLAYS)
# of Replay Replay Replay Total Clnt
Rply Replay Replayed Replay Replay Cptr User Prepare Start End Replay Clnt Sess
ID# Operation On Database DB Version Directory ID# Calls Status Time Time Time Time (s) Sess Done
---- ------------ ------------ ---------- ---------------- ---- ------ ------------ ----------- ----------- ----------- --------- ------ ------
25 DBR_RPL_100 RACDB 11.1.0.6.0 DBRCONTROL 22 1840 COMPLETED 2008-06-23 2008-06-23 2008-06-23 730 1 1
21:23:09 21:27:53 21:40:03
/*
|| Report 3.3:
|| Summary of Database Replay operation results (from DBMS_WORKLOAD_REPLAY.REPORT)
*/
DBMS_WORKLOAD_REPLAY.REPORT(45,'TEXT')
------------------------------------------------------------------------------------------------------------------------------------------------------
DB Replay Report for DBR_RPL_100
---------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
| DB Name | DB Id | Release | RAC | Replay Name | Replay Status |
------------------------------------------------------------------------
| RACDB | 646658104 | 11.1.0.6.0 | YES | DBR_RPL_100 | COMPLETED |
------------------------------------------------------------------------
Replay Information
------------------------------------------------------------------------
| Information | Replay | Capture |
------------------------------------------------------------------------
| Name | DBR_RPL_100 | DBR_CAPTURE_100 |
------------------------------------------------------------------------
| Status | COMPLETED | COMPLETED |
------------------------------------------------------------------------
| Database Name | RACDB | DB10G |
------------------------------------------------------------------------
| Database Version | 11.1.0.6.0 | 10.2.0.4.0 |
------------------------------------------------------------------------
| Start Time | 24-JUN-08 19:09:38 | 23-JUN-08 23:26:25 |
------------------------------------------------------------------------
| End Time | 24-JUN-08 19:18:11 | 23-JUN-08 23:32:35 |
------------------------------------------------------------------------
| Duration | 8 minutes 33 seconds | 6 minutes 10 seconds |
------------------------------------------------------------------------
| Directory Object | DBRCONTROL | DBRCONTROL |
------------------------------------------------------------------------
| Directory Path | /home/oracle/DBRControl | /home/oracle/DBRControl |
------------------------------------------------------------------------
Replay Options
---------------------------------------------------------
| Option Name | Value |
---------------------------------------------------------
| Synchronization | TRUE |
---------------------------------------------------------
| Connect Time | 100% |
---------------------------------------------------------
| Think Time | 100% |
---------------------------------------------------------
| Think Time Auto Correct | TRUE |
---------------------------------------------------------
| Number of WRC Clients | 1 (1 Completed, 0 Running ) |
---------------------------------------------------------
Replay Statistics
---------------------------------------------------------------
| Statistic | Replay | Capture |
---------------------------------------------------------------
| DB Time | 282.493 seconds | 812.668 seconds |
---------------------------------------------------------------
| Average Active Sessions | .55 | 2.2 |
---------------------------------------------------------------
| User calls | 1844 | 1844 |
---------------------------------------------------------------
| Network Time | 0.320 seconds | . |
---------------------------------------------------------------
| Think Time | 0.305 seconds | . |
---------------------------------------------------------------
Replay Divergence Summary
-------------------------------------------------------------------
| Divergence Type | Count | % Total |
-------------------------------------------------------------------
| Session Failures During Replay | 0 | 0.00 |
-------------------------------------------------------------------
| Errors No Longer Seen During Replay | 0 | 0.00 |
-------------------------------------------------------------------
| New Errors Seen During Replay | 0 | 0.00 |
-------------------------------------------------------------------
| Errors Mutated During Replay | 0 | 0.00 |
-------------------------------------------------------------------
| DMLs with Different Number of Rows Modified | 0 | 0.00 |
-------------------------------------------------------------------
| SELECTs with Different Number of Rows Fetched | 0 | 0.00 |
-------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
Workload Profile Top Events
-----------------------------------------------------
| Event | Event Class | % Activity |
-----------------------------------------------------
| CPU + Wait for CPU | CPU | 27.27 |
-----------------------------------------------------
| gc current block busy | Cluster | 7.27 |
-----------------------------------------------------
| gc current block 2-way | Cluster | 5.45 |
-----------------------------------------------------
| gc current split | Cluster | 1.82 |
-----------------------------------------------------
| gc current grant busy | Cluster | 1.82 |
-----------------------------------------------------
Top Service/Module/Action
---------------------------------------------------------------------------------------------------
| Service Name | Module Name | % Activity | Action Drilldown |
---------------------------------------------------------------------------------------------------
| TESTLBA | LoadGenerator | 43.64 | --------------------------------------------------- |
| | | | | Action Name | % Activity | |
| | | | --------------------------------------------------- |
| | | | | BuildInvoiceDetail | 43.64 | |
| | | | --------------------------------------------------- |
---------------------------------------------------------------------------------------------------
Top SQL with Top Events
---------------------------------------------------------------------------------------------------
| SQL ID | Planhash | Sampled Number of | % Activity | Event Drilldown |
| | | Executions | | |
---------------------------------------------------------------------------------------------------
| 1dmv3vasxtjtu | 2076861007 | 10 | 18.18 | ------------------------- |
| | | | | | Event | % | |
| | | | | | | Activity | |
| | | | | ------------------------- |
| | | | | | gc | 7.27 | |
| | | | | | current | | |
| | | | | | block | | |
| | | | | | busy | | |
| | | | | ------------------------- |
| | | | | | gc | 5.45 | |
| | | | | | current | | |
| | | | | | block | | |
| | | | | | 2-way | | |
| | | | | ------------------------- |
| | | | | | CPU + | 1.82 | |
| | | | | | Wait for | | |
| | | | | | CPU | | |
| | | | | ------------------------- |
| | | | | | gc | 1.82 | |
| | | | | | current | | |
| | | | | | grant | | |
| | | | | | busy | | |
| | | | | ------------------------- |
| | | | | | gc | 1.82 | |
| | | | | | current | | |
| | | | | | split | | |
| | | | | ------------------------- |
---------------------------------------------------------------------------------------------------
| 9cz4yg1a8m2km | 0 | 5 | 9.09 | ------------------------- |
| | | | | | Event | % | |
| | | | | | | Activity | |
| | | | | ------------------------- |
| | | | | | CPU + | 9.09 | |
| | | | | | Wait for | | |
| | | | | | CPU | | |
| | | | | ------------------------- |
---------------------------------------------------------------------------------------------------
| 5jzk6sd0qjafm | 0 | 3 | 5.45 | ------------------------- |
| | | | | | Event | % | |
| | | | | | | Activity | |
| | | | | ------------------------- |
| | | | | | CPU + | 5.45 | |
| | | | | | Wait for | | |
| | | | | | CPU | | |
| | | | | ------------------------- |
---------------------------------------------------------------------------------------------------
| 2cfatxwm4dtjp | 1666523684 | 2 | 3.64 | ------------------------- |
| | | | | | Event | % | |
| | | | | | | Activity | |
| | | | | ------------------------- |
| | | | | | CPU + | 3.64 | |
| | | | | | Wait for | | |
| | | | | | CPU | | |
| | | | | ------------------------- |
---------------------------------------------------------------------------------------------------
| 9cgwmxs9wdnx4 | 2076861007 | 2 | 3.64 | ------------------------- |
| | | | | | Event | % | |
| | | | | | | Activity | |
| | | | | ------------------------- |
| | | | | | CPU + | 3.64 | |
| | | | | | Wait for | | |
| | | | | | CPU | | |
| | | | | ------------------------- |
---------------------------------------------------------------------------------------------------
Top Sessions with Top Events
--------------------------------------------------------------------------------------------------
| Session ID | Session Serial | Username | Program | % Activity | Event Drilldown |
--------------------------------------------------------------------------------------------------
| 108 | 40 | OE | wrc@racnode2 (TNS | 3.64 | ----------------- |
| | | | V1-V3) | | | Event | % Act | |
| | | | | | | | ivity | |
| | | | | | ----------------- |
| | | | | | | CPU + | 3.64 | |
| | | | | | | Wait | | |
| | | | | | | for | | |
| | | | | | | CPU | | |
| | | | | | ----------------- |
--------------------------------------------------------------------------------------------------
| 112 | 229 | HR | wrc@racnode2 (TNS | 3.64 | ----------------- |
| | | | V1-V3) | | | Event | % Act | |
| | | | | | | | ivity | |
| | | | | | ----------------- |
| | | | | | | CPU + | 1.82 | |
| | | | | | | Wait | | |
| | | | | | | for | | |
| | | | | | | CPU | | |
| | | | | | ----------------- |
| | | | | | | gc cu | 1.82 | |
| | | | | | | rrent | | |
| | | | | | | block | | |
| | | | | | | 2-way | | |
| | | | | | ----------------- |
--------------------------------------------------------------------------------------------------
| 119 | 123 | HR | wrc@racnode2 (TNS | 3.64 | ----------------- |
| | | | V1-V3) | | | Event | % Act | |
| | | | | | | | ivity | |
| | | | | | ----------------- |
| | | | | | | gc cu | 1.82 | |
| | | | | | | rrent | | |
| | | | | | | block | | |
| | | | | | | busy | | |
| | | | | | ----------------- |
| | | | | | | gc cu | 1.82 | |
| | | | | | | rrent | | |
| | | | | | | split | | |
| | | | | | ----------------- |
--------------------------------------------------------------------------------------------------
| 144 | 45 | OE | wrc@racnode2 (TNS | 3.64 | ----------------- |
| | | | V1-V3) | | | Event | % Act | |
| | | | | | | | ivity | |
| | | | | | ----------------- |
| | | | | | | gc cu | 1.82 | |
| | | | | | | rrent | | |
| | | | | | | block | | |
| | | | | | | busy | | |
| | | | | | ----------------- |
| | | | | | | gc cu | 1.82 | |
| | | | | | | rrent | | |
| | | | | | | grant | | |
| | | | | | | busy | | |
| | | | | | ----------------- |
--------------------------------------------------------------------------------------------------
| 101 | 143 | HR | wrc@racnode2 (TNS | 1.82 | ----------------- |
| | | | V1-V3) | | | Event | % Act | |
| | | | | | | | ivity | |
| | | | | | ----------------- |
| | | | | | | CPU + | 1.82 | |
| | | | | | | Wait | | |
| | | | | | | for | | |
| | | | | | | CPU | | |
| | | | | | ----------------- |
--------------------------------------------------------------------------------------------------
Replay Divergence Session Failures By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Error Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By Session
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
DML Data Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
SELECT Data Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
End of Report.
/*
|| Report 3.4:
|| Automatic Workload Repository (AWR) Reports
*/
>> From instance RACDB1:
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
RACDB 646658104 racdb1 1 24-Jun-08 18:06 11.1.0.6.0 YES
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
racnode1 Linux IA (32-bit) 1 1.16
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 70 24-Jun-08 19:09:39 45 1.3
End Snap: 71 24-Jun-08 19:17:58 46 1.3
Elapsed: 8.33 (mins)
DB Time: 2.31 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 92M 88M Std Block Size: 8K
Shared Pool Size: 156M 160M Log Buffer: 5,820K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.3 0.2 0.00 0.11
DB CPU(s): 0.2 0.1 0.00 0.06
Redo size: 60,547.4 42,193.8
Logical reads: 1,707.4 1,189.8
Block changes: 422.4 294.3
Physical reads: 0.8 0.6
Physical writes: 4.1 2.9
User calls: 2.6 1.8
Parses: 5.9 4.1
Hard parses: 0.5 0.4
W/A MB processed: 182,227.0 126,988.9
Logons: 0.1 0.1
Executes: 234.6 163.5
Rollbacks: 0.1 0.1
Transactions: 1.4
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.95 In-memory Sort %: 100.00
Library Hit %: 99.31 Soft Parse %: 91.53
Execute to Parse %: 97.50 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 97.71
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.14 84.43
% SQL with executions>1: 61.89 77.92
% Memory for SQL w/exec>1: 67.78 68.78
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 81 58.6
gc current block busy 718 13 18 9.6 Cluster
gc current block 2-way 2,523 6 2 4.1 Cluster
log file sync 52 3 51 1.9 Commit
db file sequential read 98 2 22 1.6 User I/O
Host CPU (CPUs: 1 Cores: Sockets: )
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
2.33 1.89 12.0 46.2 7.0 40.2
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 23.0
% of busy CPU for Instance: 38.5
%DB time waiting for CPU - Resource Mgr: 0.0
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 1,184.9 1,184.9
SGA use (MB): 276.0 276.0
PGA use (MB): 272.4 274.1
% Host Mem used for SGA+PGA: 46.28 46.28
RAC Statistics DB/Inst: RACDB/racdb1 Snaps: 70-71
Begin End
----- -----
Number of Instances: 2 2
Global Cache Load Profile
~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Global Cache blocks received: 9.04 6.30
Global Cache blocks served: 9.98 6.96
GCS/GES messages received: 37.50 26.13
GCS/GES messages sent: 36.06 25.13
DBWR Fusion writes: 0.87 0.61
Estd Interconnect traffic (KB) 166.50
Global Cache Efficiency Percentages (Target local+remote 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer access - local cache %: 99.42
Buffer access - remote cache %: 0.53
Buffer access - disk %: 0.05
Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg global enqueue get time (ms): 0.4
Avg global cache cr block receive time (ms): 2.6
Avg global cache current block receive time (ms): 5.8
Avg global cache cr block build time (ms): 0.1
Avg global cache cr block send time (ms): 0.4
Global cache log flushes for cr blocks served %: 4.3
Avg global cache cr block flush time (ms): 22.1
Avg global cache current block pin time (ms): 0.2
Avg global cache current block send time (ms): 0.3
Global cache log flushes for current blocks served %: 17.3
Avg global cache current block flush time (ms): 7.2
Global Cache and Enqueue Services - Messaging Statistics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg message sent queue time (ms): 1.2
Avg message sent queue time on ksxp (ms): 2.1
Avg message received queue time (ms): 0.0
Avg GCS message process time (ms): 0.1
Avg GES message process time (ms): 0.0
% of direct sent messages: 42.25
% of indirect sent messages: 37.77
% of flow controlled messages: 19.98
-------------------------------------------------------------
Cluster Interconnect
~~~~~~~~~~~~~~~~~~~~
Begin End
-------------------------------------------------- -----------
Interface IP Address Pub Source IP Pub Src
---------- --------------- --- ------------------------------ --- --- ---
eth2 10.10.10.11 N Oracle Cluster Repository
Time Model Statistics DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Total time in database user-calls (DB Time): 138.8s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 130.3 93.9
DB CPU 81.3 58.6
PL/SQL execution elapsed time 55.5 40.0
connection management call elapsed time 4.9 3.5
parse time elapsed 4.8 3.5
hard parse elapsed time 3.8 2.7
PL/SQL compilation elapsed time 1.2 .9
hard parse (sharing criteria) elapsed time 0.6 .5
sequence load elapsed time 0.1 .1
hard parse (bind mismatch) elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 138.8
background elapsed time 146.0
background cpu time 32.8
-------------------------------------------------------------
Operating System Statistics DB/Inst: RACDB/racdb1 Snaps: 70-71
-> *TIME statistic values are diffed.
All others display actual values. End Value is displayed if different
-> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
Statistic Value End Value
------------------------- ---------------------- ----------------
BUSY_TIME 29,639
IDLE_TIME 19,934
IOWAIT_TIME 3,492
NICE_TIME 127
SYS_TIME 22,924
USER_TIME 5,956
LOAD 2 2
PHYSICAL_MEMORY_BYTES 1,242,509,312
NUM_CPUS 1
GLOBAL_RECEIVE_SIZE_MAX 4,194,304
GLOBAL_SEND_SIZE_MAX 262,144
TCP_RECEIVE_SIZE_DEFAULT 87,380
TCP_RECEIVE_SIZE_MAX 174,760
TCP_RECEIVE_SIZE_MIN 4,096
TCP_SEND_SIZE_DEFAULT 16,384
TCP_SEND_SIZE_MAX 131,072
TCP_SEND_SIZE_MIN 4,096
-------------------------------------------------------------
Operating System Statistics - Detail DB/Inst: RACDB/racdb1 Snaps: 70-71
Snap Time Load %busy %user %sys %idle %iowait
--------------- -------- -------- -------- -------- -------- --------
24-Jun 19:09:39 2.3 N/A N/A N/A N/A N/A
24-Jun 19:17:58 1.9 59.8 12.0 46.2 7.0 40.2
-------------------------------------------------------------
Foreground Wait Class DB/Inst: RACDB/racdb1 Snaps: 70-71
-> s - second, ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Captured Time accounts for 82.9% of Total DB time 138.79 (s)
-> Total FG Wait Time: 33.69 (s) DB CPU time: 81.30 (s)
Avg
%Time Total Wait wait
Wait Class Waits -outs Time (s) (ms) %DB time
-------------------- ---------------- ----- ---------------- -------- ---------
DB CPU 81 58.6
Cluster 4,319 0 25 6 18.4
Commit 52 0 3 51 1.9
User I/O 98 0 2 22 1.6
Other 441 56 2 4 1.1
Concurrency 287 0 1 4 0.9
Configuration 2 0 0 154 0.2
Application 101 1 0 3 0.2
Network 746 0 0 0 0.0
System I/O 0 0 0.0
-------------------------------------------------------------
Foreground Wait Events DB/Inst: RACDB/racdb1 Snaps: 70-71
-> s - second, ms - millisecond - 1000th of a second
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by wait time desc, waits desc (idle events last)
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Avg
%Time Total Wait wait Waits % DB
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
gc current block busy 718 0 13 18 1.0 9.6
gc current block 2-way 2,523 0 6 2 3.5 4.1
log file sync 52 0 3 51 0.1 1.9
db file sequential read 98 0 2 22 0.1 1.6
gc current grant busy 251 0 2 8 0.4 1.4
gc buffer busy release 244 0 2 8 0.3 1.4
gc current split 10 0 1 85 0.0 .6
gc cr block 2-way 437 0 1 2 0.6 .5
PX Deq: Slave Session Stat 9 0 1 70 0.0 .5
enq: TX - index contention 13 0 1 45 0.0 .4
reliable message 25 0 0 18 0.0 .3
gc current retry 2 0 0 210 0.0 .3
gc cr block busy 25 0 0 13 0.0 .2
log file switch completion 2 0 0 154 0.0 .2
SQL*Net break/reset to cli 98 0 0 3 0.1 .2
library cache pin 99 0 0 2 0.1 .2
gc cr grant 2-way 70 0 0 3 0.1 .2
row cache lock 105 0 0 2 0.1 .1
library cache lock 64 0 0 2 0.1 .1
WCR: RAC message context b 11 0 0 11 0.0 .1
IPC send completion sync 61 100 0 2 0.1 .1
DFS lock handle 22 0 0 3 0.0 .0
gc current multi block req 24 0 0 2 0.0 .0
enq: PS - contention 10 0 0 2 0.0 .0
enq: JQ - contention 1 100 0 20 0.0 .0
latch: cache buffers chain 2 0 0 10 0.0 .0
latch: ges resource hash l 3 0 0 6 0.0 .0
rdbms ipc reply 13 0 0 1 0.0 .0
SQL*Net message to client 746 0 0 0 1.0 .0
WCR: Sync context busy 6 0 0 3 0.0 .0
enq: UL - contention 2 50 0 7 0.0 .0
enq: FB - contention 7 0 0 2 0.0 .0
cursor: pin S wait on X 1 100 0 11 0.0 .0
PX Deq: reap credit 183 100 0 0 0.3 .0
latch: shared pool 2 0 0 3 0.0 .0
gc current grant 2-way 15 0 0 0 0.0 .0
PX Deq Credit: Session Sta 10 0 0 1 0.0 .0
latch free 1 0 0 6 0.0 .0
KJC: Wait for msg sends to 73 0 0 0 0.1 .0
enq: TT - contention 2 0 0 2 0.0 .0
latch: gc element 2 0 0 2 0.0 .0
enq: TM - contention 1 0 0 2 0.0 .0
SQL*Net message from clien 746 0 1,543 2068 1.0
WCR: replay clock 324 94 937 2893 0.5
jobq slave wait 200 49 492 2458 0.3
PL/SQL lock timer 28 71 184 6580 0.0
Streams AQ: waiting for me 49 100 49 1003 0.1
PX Deq: Execution Msg 56 27 1 9 0.1
PX Deq: Msg Fragment 24 33 0 1 0.0
-------------------------------------------------------------
Background Wait Events DB/Inst: RACDB/racdb1 Snaps: 70-71
-> ordered by wait time desc, waits desc (idle events last)
-> Only events with Total Wait Time (s) >= .001 are shown
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Avg
%Time Total Wait wait Waits % bg
Event Waits -outs Time (s) (ms) /txn time
-------------------------- ------------ ----- ---------- ------- -------- ------
db file parallel write 716 0 26 36 1.0 17.7
control file sequential re 1,410 0 22 16 2.0 15.4
events in waitclass Other 9,672 67 16 2 13.5 11.1
log file sequential read 58 0 15 254 0.1 10.1
log file parallel write 1,494 0 14 9 2.1 9.6
control file parallel writ 257 0 4 14 0.4 2.5
os thread startup 4 0 1 256 0.0 .7
Log archive I/O 191 0 0 2 0.3 .3
gc cr multi block request 69 0 0 5 0.1 .2
gc cr block 2-way 71 0 0 3 0.1 .1
row cache lock 15 0 0 5 0.0 .1
latch: cache buffers chain 6 0 0 11 0.0 .0
gc cr block busy 1 0 0 32 0.0 .0
log file single write 4 0 0 2 0.0 .0
gc current multi block req 2 0 0 1 0.0 .0
rdbms ipc message 12,343 60 9,738 789 17.2
DIAG idle wait 17,250 98 990 57 24.1
Streams AQ: qmn slave idle 25 4 598 23904 0.0
class slave wait 25 0 507 20287 0.0
wait for unread message on 496 100 498 1004 0.7
pmon timer 271 56 498 1836 0.4
ASM background timer 158 0 497 3148 0.2
Streams AQ: waiting for ti 56 79 497 8876 0.1
ges remote message 9,481 52 496 52 13.2
Space Manager: slave idle 99 100 495 5003 0.1
PING 272 10 493 1811 0.4
gcs remote message 20,969 46 486 23 29.2
Streams AQ: qmn coordinato 34 50 479 14098 0.0
smon timer 2 0 411 205566 0.0
fbar timer 1 100 300 300003 0.0
KSV master wait 326 92 3 10 0.5
SGA: MMAN sleep for compon 8 50 0 10 0.0
Streams AQ: RAC qmn coordi 36 100 0 0 0.1
-------------------------------------------------------------
Wait Event Histogram DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%. Value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ARCH wait for archivelog l 1 100.0
ASM file metadata operatio 48 52.1 16.7 6.3 2.1 2.1 2.1 16.7 2.1
CGS wait for IPC msg 4225 99.4 .4 .1 .0
DFS lock handle 60 33.3 28.3 25.0 13.3
IPC send completion sync 63 47.6 27.0 14.3 7.9 1.6 1.6
KJC: Wait for msg sends to 200 98.5 1.0 .5
LGWR wait for redo copy 4 75.0 25.0
Log archive I/O 191 55.0 20.9 13.6 6.8 2.6 .5 .5
PX Deq Credit: Session Sta 10 80.0 20.0
PX Deq: Signal ACK EXT 3 33.3 66.7
PX Deq: Signal ACK RSG 3 100.0
PX Deq: Slave Session Stat 17 70.6 5.9 5.9 5.9 11.8
PX Deq: reap credit 449 97.8 1.1 .2 .2 .7
SQL*Net break/reset to cli 98 80.6 2.0 2.0 2.0 10.2 1.0 2.0
SQL*Net message to client 857 99.8 .2
Streams AQ: qmn coordinato 1 100.0
WCR: RAC message context b 11 18.2 18.2 18.2 9.1 36.4
WCR: Sync context busy 6 66.7 16.7 16.7
buffer busy waits 4 75.0 25.0
control file parallel writ 257 2.3 31.1 40.5 14.4 3.5 8.2
control file sequential re 2131 10.2 27.3 26.4 15.4 6.1 7.0 7.6
cursor: pin S wait on X 1 100.0
db file parallel write 716 24.0 10.9 8.4 10.3 6.4 11.2 28.8
db file sequential read 393 2.0 8.4 7.6 9.7 20.9 31.0 20.4
direct path read 1 100.0
direct path write 4 100.0
enq: CF - contention 26 11.5 23.1 38.5 23.1 3.8
enq: FB - contention 22 59.1 31.8 4.5 4.5
enq: HW - contention 36 52.8 22.2 22.2 2.8
enq: JQ - contention 1 100.0
enq: JS - job run lock - s 1 100.0
enq: PE - contention 1 100.0
enq: PG - contention 1 100.0
enq: PS - contention 13 38.5 30.8 15.4 15.4
enq: RR - contention 2 50.0 50.0
enq: TA - contention 1 100.0
enq: TD - KTF dump entries 1 100.0
enq: TM - contention 8 37.5 50.0 12.5
enq: TQ - TM contention 10 10.0 60.0 20.0 10.0
enq: TT - contention 7 28.6 14.3 28.6 28.6
enq: TX - allocate ITL ent 1 100.0
enq: TX - index contention 14 14.3 35.7 21.4 7.1 7.1 14.3
enq: UL - contention 2 100.0
enq: US - contention 3 66.7 33.3
enq: WF - contention 10 50.0 40.0 10.0
gc buffer busy release 244 19.3 15.2 21.3 16.4 18.9 4.1 4.9
gc cr block 2-way 653 49.8 27.6 13.5 6.1 1.7 1.4
gc cr block busy 27 3.7 7.4 14.8 22.2 29.6 7.4 14.8
gc cr grant 2-way 126 49.2 25.4 13.5 7.1 3.2 .8 .8
gc cr multi block request 85 67.1 16.5 3.5 2.4 3.5 3.5 3.5
gc current block 2-way 2904 46.3 30.8 13.8 5.8 1.9 .8 .6
gc current block busy 735 .1 1.6 32.9 26.8 13.9 9.8 14.8
gc current grant 2-way 68 67.6 27.9 1.5 2.9
gc current grant busy 338 58.0 22.5 10.1 3.0 .6 2.7 3.3
gc current multi block req 38 68.4 18.4 7.9 2.6 2.6
gc current retry 2 100.0
gc current split 11 18.2 18.2 9.1 9.1 45.5
gcs log flush sync 2893 67.1 17.0 8.7 3.3 1.8 1.3 .7
ksxr poll remote instances 2166 99.5 .3 .0 .1 .1
latch free 11 54.5 9.1 9.1 27.3
Wait Event Histogram DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%. Value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
latch: cache buffers chain 8 100.0
latch: checkpoint queue la 1 100.0
latch: gc element 39 2.6 2.6 94.9
latch: gcs resource hash 2 100.0
latch: ges resource hash l 13 92.3 7.7
latch: messages 2 100.0
latch: object queue header 2 100.0
latch: redo allocation 9 88.9 11.1
latch: shared pool 2 50.0 50.0
library cache lock 182 46.7 29.7 13.7 4.9 3.3 .5 1.1
library cache pin 228 50.4 29.4 11.0 6.1 1.8 .4 .9
lock escalate retry 1 100.0
log file parallel write 1494 30.1 14.1 25.2 15.9 7.3 3.4 4.1
log file sequential read 58 5.2 3.4 5.2 86.2
log file single write 4 75.0 25.0
log file switch completion 2 100.0
log file sync 54 13.0 9.3 5.6 16.7 18.5 14.8 22.2
os thread startup 4 100.0
rdbms ipc reply 24 41.7 33.3 16.7 8.3
reliable message 62 35.5 8.1 14.5 11.3 11.3 9.7 9.7
row cache lock 350 47.4 25.4 18.9 6.3 1.4 .6
wait for scn ack 154 31.8 21.4 14.3 7.8 20.1 3.9 .6
ASM background timer 157 16.6 4.5 3.8 .6 .6 1.3 9.6 63.1
DIAG idle wait 17K .8 .5 .3 .2 4.6 58.9 34.8
KSV master wait 322 92.2 1.9 1.9 .6 .3 .3 2.5 .3
PING 272 64.3 6.3 2.6 2.2 2.2 1.1 21.3
PL/SQL lock timer 28 28.6 14.3 57.1
PX Deq Credit: send blkd 2 100.0
PX Deq: Execute Reply 4 75.0 25.0
PX Deq: Execution Msg 56 35.7 17.9 21.4 10.7 3.6 5.4 5.4
PX Deq: Join ACK 4 50.0 25.0 25.0
PX Deq: Msg Fragment 24 62.5 20.8 8.3 8.3
PX Deq: Parse Reply 3 33.3 33.3 33.3
PX Idle Wait 200 2.5 97.5
SGA: MMAN sleep for compon 8 25.0 12.5 12.5 25.0 25.0
SQL*Net message from clien 857 16.8 23.3 22.9 14.2 7.7 2.3 1.1 11.7
Space Manager: slave idle 99 100.0
Streams AQ: RAC qmn coordi 36 97.2 2.8
Streams AQ: qmn coordinato 34 20.6 2.9 11.8 8.8 2.9 52.9
Streams AQ: qmn slave idle 25 8.0 92.0
Streams AQ: waiting for me 49 100.0
Streams AQ: waiting for ti 56 14.3 1.8 42.9 41.1
WCR: replay clock 324 .6 2.5 96.9
class slave wait 25 32.0 12.0 4.0 16.0 12.0 12.0 12.0
dispatcher timer 8 100.0
fbar timer 1 100.0
gcs remote message 20K 15.5 9.4 10.4 7.5 5.6 7.2 44.3
ges remote message 9401 16.2 8.1 6.6 4.5 3.1 3.3 58.3
jobq slave wait 200 .5 1.5 98.0
pmon timer 270 9.3 3.3 2.6 2.2 2.6 1.9 15.2 63.0
rdbms ipc message 12K 10.5 3.7 3.4 3.6 2.6 2.3 52.9 21.1
smon timer 3 100.0
virtual circuit status 16 100.0
wait for unread message on 493 99.0 1.0
-------------------------------------------------------------
Service Statistics DB/Inst: RACDB/racdb1 Snaps: 70-71
-> ordered by DB Time
Physical Logical
Service Name DB Time (s) DB CPU (s) Reads (K) Reads (K)
---------------------------- ------------ ------------ ------------ ------------
TESTLBA 130 79 0 832
SYS$USERS 8 2 0 3
SYS$BACKGROUND 0 0 0 18
racdb 0 0 0 0
racdbXDB 0 0 0 0
-------------------------------------------------------------
Service Wait Class Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Wait Class info for services in the Service Statistics section.
-> Total Waits and Time Waited displayed for the following wait
classes: User I/O, Concurrency, Administrative, Network
-> Time Waited (Wt Time) in seconds
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
TESTLBA
0 0 177 1 0 0 555 0
SYS$USERS
92 2 109 0 0 0 191 0
SYS$BACKGROUND
336 8 610 2 0 0 0 0
-------------------------------------------------------------
SQL ordered by Elapsed Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> Total DB Time (s): 139
-> Captured SQL account for 150.5% of Total
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
44 22 6 7.3 31.4 5jzk6sd0qjafm
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(75); COMMIT; END;
34 10 31,684 0.0 24.3 1dmv3vasxtjtu
Module: LoadGenerator
INSERT 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 )
33 22 4 8.2 23.7 9qqv0czupcx22
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(50); COMMIT; END;
18 11 3 6.1 13.3 9cz4yg1a8m2km
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(125); COMMIT; END;
14 12 31,700 0.0 10.3 9cgwmxs9wdnx4
Module: LoadGenerator
SELECT 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
13 3 1 13.2 9.5 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
12 8 6 2.0 8.4 d9fbkhp0q5amx
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(25); COMMIT; END;
11 6 4 2.8 8.2 45cd1yp2y379a
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(30); COMMIT; END;
2 0 1 2.5 1.8 7vgmvmy8vvb9s
insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_c
hange#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phy
blkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num
ber, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts.
2 0 1 2.4 1.8 9bhvms9my13tg
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
2 2 31,696 0.0 1.4 2cfatxwm4dtjp
Module: LoadGenerator
SELECT P.LIST_PRICE FROM OE.PRODUCT_INFORMATION P WHERE PRODUCT_ID = :B1
2 0 218 0.0 1.3 db78fxqxwxt7r
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
nd intcol#=:2 and row#=:3 order by bucket
2 1 576 0.0 1.3 41n4g99ucshck
Module: LoadGenerator
INSERT INTO AP.INVOICES ( INVOICE_ID ,ACTIVE_IND ,INVOICE_TYPE ,VENDOR_ID ,CUSTO
MER_ID ,BALANCE_DUE ,TAXABLE_AMT ) VALUES( :B3 ,'Y' ,'C' ,:B2 ,:B1 ,0 ,0 )
2 1 9 0.2 1.2 4cpkqmdsq46br
SQL ordered by Elapsed Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> Total DB Time (s): 139
-> Captured SQL account for 150.5% of Total
Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.SpinCPUs(250); END;
-------------------------------------------------------------
SQL ordered by CPU Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total is the CPU Time divided into the Total CPU Time times 100
-> Total CPU Time (s): 81
-> Captured SQL account for 129.0% of Total
CPU Elapsed CPU per % Total
Time (s) Time (s) Executions Exec (s) % Total DB Time SQL Id
---------- ---------- ------------ ----------- ------- ------- -------------
22 44 6 3.73 27.5 31.4 5jzk6sd0qjafm
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(75); COMMIT; END;
22 33 4 5.48 26.9 23.7 9qqv0czupcx22
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(50); COMMIT; END;
12 14 31,700 0.00 14.3 10.3 9cgwmxs9wdnx4
Module: LoadGenerator
SELECT 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
11 18 3 3.63 13.4 13.3 9cz4yg1a8m2km
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(125); COMMIT; END;
10 34 31,684 0.00 12.0 24.3 1dmv3vasxtjtu
Module: LoadGenerator
INSERT 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 )
8 12 6 1.33 9.8 8.4 d9fbkhp0q5amx
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(25); COMMIT; END;
6 11 4 1.50 7.4 8.2 45cd1yp2y379a
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(30); COMMIT; END;
3 13 1 3.01 3.7 9.5 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
2 2 31,696 0.00 1.9 1.4 2cfatxwm4dtjp
Module: LoadGenerator
SELECT P.LIST_PRICE FROM OE.PRODUCT_INFORMATION P WHERE PRODUCT_ID = :B1
1 2 9 0.13 1.5 1.2 4cpkqmdsq46br
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.SpinCPUs(250); END;
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> Total Buffer Gets: 853,109
-> Captured SQL account for 98.5% of Total
Gets CPU Elapsed
Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ------------ ------------ ------ -------- --------- -------------
507,200 31,700 16.0 59.5 11.64 14.36 9cgwmxs9wdnx4
Module: LoadGenerator
SELECT 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
273,950 4 68,487.5 32.1 21.91 32.89 9qqv0czupcx22
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(50); COMMIT; END;
261,852 6 43,642.0 30.7 22.40 43.59 5jzk6sd0qjafm
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(75); COMMIT; END;
246,278 31,684 7.8 28.9 9.76 33.78 1dmv3vasxtjtu
Module: LoadGenerator
INSERT 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 )
128,617 3 42,872.3 15.1 10.89 18.40 9cz4yg1a8m2km
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(125); COMMIT; END;
95,817 6 15,969.5 11.2 8.00 11.71 d9fbkhp0q5amx
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(25); COMMIT; END;
67,904 4 16,976.0 8.0 5.98 11.39 45cd1yp2y379a
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(30); COMMIT; END;
63,380 31,696 2.0 7.4 1.53 1.94 2cfatxwm4dtjp
Module: LoadGenerator
SELECT P.LIST_PRICE FROM OE.PRODUCT_INFORMATION P WHERE PRODUCT_ID = :B1
11,311 1 11,311.0 1.3 3.01 13.18 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
5,109 576 8.9 0.6 0.58 1.74 41n4g99ucshck
Module: LoadGenerator
INSERT INTO AP.INVOICES ( INVOICE_ID ,ACTIVE_IND ,INVOICE_TYPE ,VENDOR_ID ,CUSTO
MER_ID ,BALANCE_DUE ,TAXABLE_AMT ) VALUES( :B3 ,'Y' ,'C' ,:B2 ,:B1 ,0 ,0 )
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Total Disk Reads: 407
-> Captured SQL account for 59.7% of Total
Reads CPU Elapsed
Physical Reads Executions per Exec %Total Time (s) Time (s) SQL Id
-------------- ----------- ------------- ------ -------- --------- -------------
211 1 211.0 51.8 3.01 13.18 6ajkhukk78nsr
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
73 1 73.0 17.9 0.31 2.45 9bhvms9my13tg
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
68 218 0.3 16.7 0.16 1.81 db78fxqxwxt7r
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
nd intcol#=:2 and row#=:3 order by bucket
48 17 2.8 11.8 0.12 1.31 cvn54b7yz0s8u
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
26 17 1.5 6.4 0.05 0.75 39m4sx9k63ba2
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
20 1 20.0 4.9 0.16 0.73 d11xq9vmhrwxs
INSERT INTO SYS.WRI$_ADV_TASKS (ID,OWNER#,OWNER_NAME,NAME,DESCRIPTION,ADVISOR_ID
,ADVISOR_NAME, CTIME,MTIME,PARENT_ID,PARENT_REC_ID, PROPERTY,VERSION, EXEC_START
,EXEC_END,STATUS,STATUS_MSG_ID,PCT_COMPLETION_TIME, PROGRESS_METRIC,METRIC_UNITS
,ACTIVITY_COUNTER,REC_COUNT,ERROR_MSG#, SOURCE,HOW_CREATED ) VALUES (:B10 ,:B9 ,
18 1 18.0 4.4 0.13 0.45 2nszajb0qbyvp
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_H
OST_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT')); :mydate := ne
xt_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
15 87 0.2 3.7 0.08 1.28 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p
1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
14 1 14.0 3.4 0.05 0.54 0tdm5skk57ukp
Module: DBMS_SCHEDULER
SELECT ROWID, ACTSCHAT, RSET_OWNER, RSET_NAME, RSETPROC, SYS_OP_TOSETID(RSETINCR
RS) FROM RLM$SCHACTLIST FUS WHERE FUS.ROWID = (SELECT MIN(ROWID) KEEP (DENSE_RAN
K FIRST ORDER BY ACTSCHAT, ROWID) FROM RLM$SCHACTLIST WHERE ACTSCHAT IS NOT NULL
) FOR UPDATE NOWAIT
10 1 10.0 2.5 0.17 0.71 14wnf35dahb7v
SELECT A.ID,A.TYPE FROM SYS.WRI$_ADV_DEFINITIONS A WHERE A.NAME = :B1
9 1 9.0 2.2 0.05 0.45 45bv8pz1znkhr
UPDATE WRI$_ADV_RECOMMENDATIONS SET FILTERED = 'Y' WHERE TASK_ID = :B1 AND ID IN
(SELECT REC_ID FROM (SELECT A.REC_ID AS REC_ID, COUNT(*) AS TOTAL_CNT, SUM(CASE
WHEN A.FILTERED = 'Y' THEN 1 ELSE 0 END) AS FILT_CNT FROM DBA_ADVISOR_ACTIONS A
WHERE A.TASK_ID = :B1 GROUP BY A.REC_ID) WHERE TOTAL_CNT = FILT_CNT)
9 1 9.0 2.2 0.10 0.52 akh9zqqkx3wj7
select 1, max(id) from sys.wri$_adv_objects where task_id = :1 union all select
2, max(id) from sys.wri$_adv_recommendations where task_id = :1 union all sele
ct 3, max(id) from sys.wri$_adv_actions where task_id = :1 union all select 4,
max(id) from sys.wri$_adv_findings where task_id = :1 union all select 5, max(i
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Total Executions: 117,205
-> Captured SQL account for 98.4% of Total
CPU per Elap per
Executions Rows Processed Rows per Exec Exec (s) Exec (s) SQL Id
------------ --------------- -------------- ---------- ----------- -------------
31,700 31,688 1.0 0.00 0.00 9cgwmxs9wdnx4
Module: LoadGenerator
SELECT 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
31,696 31,684 1.0 0.00 0.00 2cfatxwm4dtjp
Module: LoadGenerator
SELECT P.LIST_PRICE FROM OE.PRODUCT_INFORMATION P WHERE PRODUCT_ID = :B1
31,684 31,684 1.0 0.00 0.00 1dmv3vasxtjtu
Module: LoadGenerator
INSERT 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 )
15,586 15,586 1.0 0.00 0.00 g8tm45j2jdgq8
Module: LoadGenerator
SELECT (SQRT(:B2 /:B1 )) FROM DUAL
576 571 1.0 0.00 0.00 41n4g99ucshck
Module: LoadGenerator
INSERT INTO AP.INVOICES ( INVOICE_ID ,ACTIVE_IND ,INVOICE_TYPE ,VENDOR_ID ,CUSTO
MER_ID ,BALANCE_DUE ,TAXABLE_AMT ) VALUES( :B3 ,'Y' ,'C' ,:B2 ,:B1 ,0 ,0 )
576 576 1.0 0.00 0.00 8wzc2r9wadx8u
Module: LoadGenerator
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 ) AND TABLE_
NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE INTO :O0
576 571 1.0 0.00 0.00 gcdp623mad41b
Module: LoadGenerator
SELECT VENDOR_ID FROM (SELECT VENDOR_ID FROM AP.VENDORS SAMPLE(3) ORDER BY DBMS_
RANDOM.VALUE) WHERE ROWNUM < 2
559 559 1.0 0.00 0.00 bzbqb10q243ct
Module: LoadGenerator
UPDATE AP.INVOICES SET BALANCE_DUE = :B3 ,TAXABLE_AMT = :B2 WHERE INVOICE_ID = :
B1
331 1,629 4.9 0.00 0.00 0h6b2sajwb74n
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p
rivilege#>0 start with grantee#=:1 and privilege#>0
218 2,650 12.2 0.00 0.01 db78fxqxwxt7r
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
nd intcol#=:2 and row#=:3 order by bucket
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Total Parse Calls: 2,927
-> Captured SQL account for 68.2% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
331 331 11.31 0h6b2sajwb74n
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p
rivilege#>0 start with grantee#=:1 and privilege#>0
146 146 4.99 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
87 87 2.97 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p
1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
75 75 2.56 0k8522rmdzg4k
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#
>0
71 71 2.43 f80h0xb1qvbsk
SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
49 49 1.67 3972rvxu3knn3
Module: racgimon@racnode1 (TNS V1-V3)
delete from sdo_geor_ddl__table$$
49 49 1.67 a6u3yjca29nqc
Module: racgimon@racnode1 (TNS V1-V3)
declare m_stmt varchar2(512); begin m_stmt:='delete from sdo_geor_ddl__tab
le$$'; EXECUTE IMMEDIATE m_stmt; EXCEPTION WHEN OTHERS THEN NU
LL; end;
46 46 1.57 bsa0wjtftg3uw
select file# from file$ where ts#=:1
42 42 1.43 0kkhhb2w93cx0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:
17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
37 37 1.26 0ws7ahf1d78qa
select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_
NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE
_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from
v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
37 37 1.26 1uzhrfn574t12
Module: sqlplus@racnode1 (TNS V1-V3)
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_P
RIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID)
37 37 1.26 209fr01svbb5s
update sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(SYS_EXTRACT_UTC(
systimestamp) as date), logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logo
ff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
37 37 1.26 3mqbg0gn1ky0v
Module: LoadGenerator
insert into wrr$_replay_divergence (id, time, type, exp_num_rows, obs_num_rows,
exp_error, obs_error, file_id, call_counter, sql_id, session_id, session_ser
SQL ordered by Parse Calls DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Total Parse Calls: 2,927
-> Captured SQL account for 68.2% of Total
% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
ial#, service, module, action) select :rep_id, time, type, exp_num_rows,
obs_num_rows, exp_error, obs_error, file_id, call_counter, sql_id
37 37 1.26 459f3z9u4fb3u
select value$ from props$ where name = 'GLOBAL_DB_NAME'
37 37 1.26 4vs91dcv7u1p6
Module: sqlplus@racnode1 (TNS V1-V3)
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,t
erminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, n
ew$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,co
mment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, in
37 37 1.26 5qgz1p0cut7mx
Module: wrc@racnode2 (TNS V1-V3)
BEGIN DBMS_OUTPUT.DISABLE; END;
37 37 1.26 5ur69atw3vfhj
select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE
', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT',
4, 1), failover_retries, failover_delay, flags from service$ where name = :1
37 37 1.26 c4pc3jhzjcmc7
Module: sqlplus@racnode1 (TNS V1-V3)
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPE
R(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPE
R(ATTRIBUTE) = 'ROLES')
37 37 1.26 cfrvd7bn0fd05
Module: wrc@racnode2 (TNS V1-V3)
select seq_name, seq_bnm, seq_bow, first_value, last_value, change, num_values,
call_ctr, rank from WRR$_REPLAY_SEQ_DATA where file_id = :file_id and call_ctr >
= :call_ctr and rank > :rank order by call_ctr, rank
37 37 1.26 d6vwqbw6r2ffk
Module: sqlplus@racnode1 (TNS V1-V3)
SELECT USER FROM DUAL
37 37 1.26 dyk4dprp70d74
Module: SQL*Plus
SELECT DECODE('A','A','1','2') FROM DUAL
37 37 1.26 g4y6nw3tts7cc
Module: sqlplus@racnode1 (TNS V1-V3)
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
37 37 1.26 grwydz59pu6mc
select text from view$ where rowid=:1
-------------------------------------------------------------
SQL ordered by Sharable Memory DB/Inst: RACDB/racdb1 Snaps: 70-71
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Version Count DB/Inst: RACDB/racdb1 Snaps: 70-71
No data exists for this section of the report.
-------------------------------------------------------------
SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> %Ela is Cluster Wait Time as percentage of SQL Elapsed Time
-> %Total is Cluster Wait Time as percentage of Total Cluster Wait Time
-> Only SQL with Cluster Wait Time > .005 seconds is reported
-> Total Cluster Wait Time (s): 27,871,874
-> Captured SQL accounts for 20.08% of Total Cluster Wait Time
Cluster Elapsed CPU
Wait Time (s) %Ela %Total Time(s) Time(s) Executions SQL Id
------------- ------- ------- ----------- ----------- -------------- -----------
22.7 67.1 81.3 33.78 9.76 31,684 1dmv3vasxtj
Module: LoadGenerator
INSERT 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 )
14.2 32.6 51.0 43.59 22.40 6 5jzk6sd0qja
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(75); COMMIT; END;
4.0 35.0 14.3 11.39 5.98 4 45cd1yp2y37
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(30); COMMIT; END;
3.9 21.2 14.0 18.40 10.89 3 9cz4yg1a8m2
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(125); COMMIT; END;
2.1 17.7 7.4 11.71 8.00 6 d9fbkhp0q5a
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(25); COMMIT; END;
.9 6.9 3.2 13.18 3.01 1 6ajkhukk78n
begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
.8 76.3 2.8 1.03 0.24 559 bzbqb10q243
Module: LoadGenerator
UPDATE AP.INVOICES SET BALANCE_DUE = :B3 ,TAXABLE_AMT = :B2 WHERE INVOICE_ID = :
B1
.8 45.1 2.8 1.74 0.58 576 41n4g99ucsh
Module: LoadGenerator
INSERT INTO AP.INVOICES ( INVOICE_ID ,ACTIVE_IND ,INVOICE_TYPE ,VENDOR_ID ,CUSTO
MER_ID ,BALANCE_DUE ,TAXABLE_AMT ) VALUES( :B3 ,'Y' ,'C' ,:B2 ,:B1 ,0 ,0 )
.4 1.2 1.4 32.89 21.91 4 9qqv0czupcx
Module: SQL*Plus
BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(50); COMMIT; END;
.3 75.9 1.2 0.44 0.06 1 4tg8mr2bvy6
select smontabv.cnt, smontab.time_mp, smontab.scn, smontab.num_mappings, smon
tab.tim_scn_map from smon_scn_time smontab, (select max(scn) scnmax,
count(*)+sum(NVL2(TIM_SCN_MAP,NUM_MAPPINGS,0)) cnt
from smon_scn_time) smontabv where smontab.scn = smontabv.scnmax
.3 51.7 1.2 0.65 0.23 37 209fr01svbb
update sys.aud$ set action#=:2, returncode=:3, logoff$time=cast(SYS_EXTRACT_UTC(
systimestamp) as date), logoff$pread=:4, logoff$lread=:5, logoff$lwrite=:6, logo
ff$dead=:7, sessioncpu=:8 where sessionid=:1 and entryid=1 and action#=100
.3 22.1 1.0 1.28 0.08 87 3m8smr0v7v1
INSERT INTO sys.wri$_adv_message_groups (task_id,id,seq,message#,fac,hdr,lm,nl,p
1,p2,p3,p4,p5) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13)
.3 11.0 1.0 2.45 0.31 1 9bhvms9my13
Module: DBMS_SCHEDULER
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :myda
te; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname
VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start
TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME
SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> %Ela is Cluster Wait Time as percentage of SQL Elapsed Time
-> %Total is Cluster Wait Time as percentage of Total Cluster Wait Time
-> Only SQL with Cluster Wait Time > .005 seconds is reported
-> Total Cluster Wait Time (s): 27,871,874
-> Captured SQL accounts for 20.08% of Total Cluster Wait Time
Cluster Elapsed CPU
Wait Time (s) %Ela %Total Time(s) Time(s) Executions SQL Id
------------- ------- ------- ----------- ----------- -------------- -----------
.2 23.5 0.8 0.94 0.66 37 4vs91dcv7u1
Module: sqlplus@racnode1 (TNS V1-V3)
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,t
erminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, n
ew$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,co
mment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, in
.2 15.1 0.7 1.31 0.12 17 cvn54b7yz0s
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
.2 47.9 0.6 0.37 0.16 576 8wzc2r9wadx
Module: LoadGenerator
UPDATE ADMIN.NEXT_IDS SET VALUE = VALUE + 1 WHERE OWNER = UPPER(:B3 ) AND TABLE_
NAME = UPPER(:B2 ) AND KEY_ID = UPPER(:B1 ) RETURNING VALUE INTO :O0
.1 68.0 0.2 0.10 0.04 89 04xtrk7uyhk
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f
rom obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null an
d linkname is null and subname is null
.0 2.6 0.2 1.81 0.16 218 db78fxqxwxt
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a
nd intcol#=:2 and row#=:3 order by bucket
.0 9.4 0.2 0.45 0.13 1 2nszajb0qby
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN :
= FALSE; BEGIN wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_H
OST_ADDRESS'),wwv_flow_platform.get_preference('SMTP_HOST_PORT')); :mydate := ne
xt_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
.0 14.6 0.1 0.28 0.12 68 7ng34ruy5aw
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf
ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl
ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(
.0 4.7 0.1 0.73 0.16 1 d11xq9vmhrw
INSERT INTO SYS.WRI$_ADV_TASKS (ID,OWNER#,OWNER_NAME,NAME,DESCRIPTION,ADVISOR_ID
,ADVISOR_NAME, CTIME,MTIME,PARENT_ID,PARENT_REC_ID, PROPERTY,VERSION, EXEC_START
,EXEC_END,STATUS,STATUS_MSG_ID,PCT_COMPLETION_TIME, PROGRESS_METRIC,METRIC_UNITS
,ACTIVITY_COUNTER,REC_COUNT,ERROR_MSG#, SOURCE,HOW_CREATED ) VALUES (:B10 ,:B9 ,
.0 1.3 0.1 2.46 0.25 1 7vgmvmy8vvb
insert into wrh$_tempstatxs (snap_id, dbid, instance_number, file#, creation_c
hange#, phyrds, phywrts, singleblkrds, readtim, writetim, singleblkrdtim, phy
blkrd, phyblkwrt, wait_count, time) select :snap_id, :dbid, :instance_num
ber, tf.tfnum, to_number(tf.tfcrc_scn) creation_change#, ts.kcftiopyr, ts.
.0 8.8 0.1 0.35 0.26 1 bqnn4c3gjtm
insert into wrh$_bg_event_summary (snap_id, dbid, instance_number, event_id
, total_waits, total_timeouts, time_waited_micro) select /*+ ordered use_nl(
e) */ :snap_id, :dbid, :instance_number, e.event_id, sum(e.total_waits),
sum(e.total_timeouts), sum(e.time_waited_micro) from v$session bgsids, v$s
.0 30.6 0.1 0.07 0.05 42 0kkhhb2w93c
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:
17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> %Ela is Cluster Wait Time as percentage of SQL Elapsed Time
-> %Total is Cluster Wait Time as percentage of Total Cluster Wait Time
-> Only SQL with Cluster Wait Time > .005 seconds is reported
-> Total Cluster Wait Time (s): 27,871,874
-> Captured SQL accounts for 20.08% of Total Cluster Wait Time
Cluster Elapsed CPU
Wait Time (s) %Ela %Total Time(s) Time(s) Executions SQL Id
------------- ------- ------- ----------- ----------- -------------- -----------
.0 2.8 0.1 0.71 0.17 1 14wnf35dahb
SELECT A.ID,A.TYPE FROM SYS.WRI$_ADV_DEFINITIONS A WHERE A.NAME = :B1
.0 4.2 0.1 0.45 0.05 1 45bv8pz1znk
UPDATE WRI$_ADV_RECOMMENDATIONS SET FILTERED = 'Y' WHERE TASK_ID = :B1 AND ID IN
(SELECT REC_ID FROM (SELECT A.REC_ID AS REC_ID, COUNT(*) AS TOTAL_CNT, SUM(CASE
WHEN A.FILTERED = 'Y' THEN 1 ELSE 0 END) AS FILT_CNT FROM DBA_ADVISOR_ACTIONS A
WHERE A.TASK_ID = :B1 GROUP BY A.REC_ID) WHERE TOTAL_CNT = FILT_CNT)
.0 3.3 0.1 0.52 0.10 1 akh9zqqkx3w
select 1, max(id) from sys.wri$_adv_objects where task_id = :1 union all select
2, max(id) from sys.wri$_adv_recommendations where task_id = :1 union all sele
ct 3, max(id) from sys.wri$_adv_actions where task_id = :1 union all select 4,
max(id) from sys.wri$_adv_findings where task_id = :1 union all select 5, max(i
.0 24.2 0.1 0.07 0.05 146 9tgj4g8y4rw
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
.0 6.9 0.1 0.21 0.06 71 f80h0xb1qvb
SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
.0 8.1 0.0 0.16 0.10 37 cfrvd7bn0fd
Module: wrc@racnode2 (TNS V1-V3)
select seq_name, seq_bnm, seq_bow, first_value, last_value, change, num_values,
call_ctr, rank from WRR$_REPLAY_SEQ_DATA where file_id = :file_id and call_ctr >
= :call_ctr and rank > :rank order by call_ctr, rank
.0 47.5 0.0 0.03 0.01 23 1gu8t96d0bd
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco
ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl
kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro
perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(
.0 3.1 0.0 0.39 0.27 3 1rswbxwhbpm
select decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_
enabled_time), a.obj#, decode(bitand(a.flags, 16384), 0, 0, 1), a.sch_jo
b from (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date,
p.job_status job_status, p.class_oid class_oid, p.last_enabled_time last_e
.0 1.5 0.0 0.75 0.05 17 39m4sx9k63b
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
.0 4.0 0.0 0.26 0.13 1 0rc20hyugnr
SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name,
sd_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WH
EN (sd_xe_ash_nm.parameter1 is NULL OR sd_
xe_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END)
.0 23.7 0.0 0.04 0.03 108 5n1fs4m2n2y
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
.0 22.3 0.0 0.04 0.02 29 52kfqq6q4nt
select o.obj#, t.flags from tab$ t, obj$ o, user$ u where u.user# = :1 and u.us
er# = o.owner# and o.name = :2 and o.obj# = t.obj#
.0 1.5 0.0 0.54 0.05 1 0tdm5skk57u
SQL ordered by Cluster Wait Time DB/Inst: RACDB/racdb1 Snaps: 70-71
-> %Ela is Cluster Wait Time as percentage of SQL Elapsed Time
-> %Total is Cluster Wait Time as percentage of Total Cluster Wait Time
-> Only SQL with Cluster Wait Time > .005 seconds is reported
-> Total Cluster Wait Time (s): 27,871,874
-> Captured SQL accounts for 20.08% of Total Cluster Wait Time
Cluster Elapsed CPU
Wait Time (s) %Ela %Total Time(s) Time(s) Executions SQL Id
------------- ------- ------- ----------- ----------- -------------- -----------
Module: DBMS_SCHEDULER
SELECT ROWID, ACTSCHAT, RSET_OWNER, RSET_NAME, RSETPROC, SYS_OP_TOSETID(RSETINCR
RS) FROM RLM$SCHACTLIST FUS WHERE FUS.ROWID = (SELECT MIN(ROWID) KEEP (DENSE_RAN
K FIRST ORDER BY ACTSCHAT, ROWID) FROM RLM$SCHACTLIST WHERE ACTSCHAT IS NOT NULL
) FOR UPDATE NOWAIT
.0 30.6 0.0 0.02 0.01 29 3ktacv9r56b
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj
#=obj#(+) order by order#
.0 11.9 0.0 0.06 0.01 37 grwydz59pu6
select text from view$ where rowid=:1
.0 4.8 0.0 0.13 0.08 37 459f3z9u4fb
select value$ from props$ where name = 'GLOBAL_DB_NAME'
.0 5.1 0.0 0.13 0.11 1 7tb5svkb5g0
SELECT a.plsql_entry_object_id, a.plsql_entry_subprogram_id, dba.object
_name, dba.object_type, dba.owner, dba.procedure_name, to_number(dba.ove
rload), a.cnt FROM dba_procedures dba, ( SELECT cnt , PLSQL_ENTRY_OBJECT_
ID, PLSQL_ENTRY_SUBPROGRAM_ID FROM ( SELECT count(*) as cnt , a.PLSQL_ENTRY_OBJ
.0 4.6 0.0 0.13 0.01 33 6aq34nj2zb2
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where
obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#,
grantee#
.0 35.0 0.0 0.01 0.01 34 2q93zsrvbdw
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w
here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#
-------------------------------------------------------------
Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
Batched IO (bound) vector count 0 0.0 0.0
Batched IO (full) vector count 0 0.0 0.0
Batched IO block miss count 0 0.0 0.0
Batched IO buffer defrag count 0 0.0 0.0
Batched IO double miss count 0 0.0 0.0
Batched IO same unit count 0 0.0 0.0
Batched IO single block count 0 0.0 0.0
Batched IO vector block count 0 0.0 0.0
Batched IO vector read count 0 0.0 0.0
CCursor + sql area evicted 88 0.2 0.1
CPU used by this session 8,630 17.3 12.0
CPU used when call started 8,138 16.3 11.4
CR blocks created 154 0.3 0.2
DB time 160,137 320.5 223.3
DBWR checkpoint buffers written 1,553 3.1 2.2
DBWR checkpoints 1 0.0 0.0
DBWR fusion writes 437 0.9 0.6
DBWR object drop buffers written 0 0.0 0.0
DBWR tablespace checkpoint buffe 0 0.0 0.0
DBWR transaction table writes 24 0.1 0.0
DBWR undo block writes 889 1.8 1.2
DFO trees parallelized 2 0.0 0.0
Effective IO time 120 0.2 0.2
HSC Heap Segment Block Changes 34,070 68.2 47.5
Heap Segment Array Inserts 92 0.2 0.1
Heap Segment Array Updates 38 0.1 0.1
LOB table id lookup cache misses 24 0.1 0.0
Number of read IOs issued 1 0.0 0.0
PX local messages recv'd 5 0.0 0.0
PX local messages sent 5 0.0 0.0
PX remote messages recv'd 44 0.1 0.1
PX remote messages sent 26 0.1 0.0
Parallel operations not downgrad 2 0.0 0.0
RowCR attempts 0 0.0 0.0
RowCR hits 0 0.0 0.0
SMON posted for instance recover 0 0.0 0.0
SMON posted for undo segment shr 2 0.0 0.0
SQL*Net roundtrips to/from clien 746 1.5 1.0
Workload Replay: dbtime 130,402,224 260,983.5 181,872.0
Workload Replay: network time 195,928 392.1 273.3
Workload Replay: think time 119,884 239.9 167.2
Workload Replay: time loss 2,053,885,362 4,110,590.6 2,864,554.2
Workload Replay: user calls 851 1.7 1.2
active txn count during cleanout 1,276 2.6 1.8
application wait time 28 0.1 0.0
background checkpoints started 1 0.0 0.0
background timeouts 4,128 8.3 5.8
buffer is not pinned count 523,288 1,047.3 729.8
buffer is pinned count 8,837 17.7 12.3
bytes received via SQL*Net from 607,276 1,215.4 847.0
bytes sent via SQL*Net to client 257,930 516.2 359.7
calls to get snapshot scn: kcmgs 216,237 432.8 301.6
calls to kcmgas 7,761 15.5 10.8
calls to kcmgcs 419 0.8 0.6
calls to kcmgrs 168,594 337.4 235.1
change write time 257 0.5 0.4
cleanout - number of ktugct call 1,426 2.9 2.0
cleanouts and rollbacks - consis 143 0.3 0.2
cleanouts only - consistent read 22 0.0 0.0
cluster key scan block gets 836 1.7 1.2
Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
cluster key scans 760 1.5 1.1
cluster wait time 2,777 5.6 3.9
commit batch/immediate performed 18 0.0 0.0
commit batch/immediate requested 18 0.0 0.0
commit cleanout failures: block 324 0.7 0.5
commit cleanout failures: callba 25 0.1 0.0
commit cleanouts 23,110 46.3 32.2
commit cleanouts successfully co 22,761 45.6 31.7
commit immediate performed 18 0.0 0.0
commit immediate requested 18 0.0 0.0
commit nowait performed 582 1.2 0.8
commit nowait requested 582 1.2 0.8
commit txn count during cleanout 2,988 6.0 4.2
commit wait/nowait performed 582 1.2 0.8
commit wait/nowait requested 582 1.2 0.8
concurrency wait time 334 0.7 0.5
consistent changes 244 0.5 0.3
consistent gets 596,774 1,194.4 832.3
consistent gets - examination 75,859 151.8 105.8
consistent gets direct 3 0.0 0.0
consistent gets from cache 596,771 1,194.4 832.3
consistent gets from cache (fast 518,640 1,038.0 723.4
cursor authentications 137 0.3 0.2
data blocks consistent reads - u 231 0.5 0.3
db block changes 211,042 422.4 294.3
db block gets 256,335 513.0 357.5
db block gets direct 4 0.0 0.0
db block gets from cache 256,331 513.0 357.5
db block gets from cache (fastpa 129,639 259.5 180.8
deferred (CURRENT) block cleanou 4,802 9.6 6.7
dirty buffers inspected 315 0.6 0.4
enqueue conversions 1,352 2.7 1.9
enqueue releases 65,053 130.2 90.7
enqueue requests 65,052 130.2 90.7
enqueue timeouts 0 0.0 0.0
enqueue waits 152 0.3 0.2
exchange deadlocks 15 0.0 0.0
execute count 117,205 234.6 163.5
free buffer inspected 6,650 13.3 9.3
free buffer requested 7,329 14.7 10.2
gc CPU used by this session 1,247 2.5 1.7
gc cr block build time 10 0.0 0.0
gc cr block flush time 73 0.2 0.1
gc cr block receive time 176 0.4 0.3
gc cr block send time 28 0.1 0.0
gc cr blocks received 685 1.4 1.0
gc cr blocks served 772 1.6 1.1
gc current block flush time 525 1.1 0.7
gc current block pin time 81 0.2 0.1
gc current block receive time 2,205 4.4 3.1
gc current block send time 140 0.3 0.2
gc current blocks received 3,830 7.7 5.3
gc current blocks served 4,215 8.4 5.9
gc local grants 2,027 4.1 2.8
gc remote grants 733 1.5 1.0
gcs messages sent 12,907 25.8 18.0
ges messages sent 5,109 10.2 7.1
global enqueue get time 623 1.3 0.9
global enqueue gets async 1,241 2.5 1.7
global enqueue gets sync 14,181 28.4 19.8
Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
global enqueue releases 16,533 33.1 23.1
heap block compress 22 0.0 0.0
hot buffers moved to head of LRU 1,844 3.7 2.6
immediate (CR) block cleanout ap 165 0.3 0.2
immediate (CURRENT) block cleano 733 1.5 1.0
index crx upgrade (positioned) 214 0.4 0.3
index crx upgrade (prefetch) 0 0.0 0.0
index fast full scans (full) 0 0.0 0.0
index fetch by key 34,828 69.7 48.6
index scans kdiixs1 4,567 9.1 6.4
leaf node 90-10 splits 75 0.2 0.1
leaf node splits 228 0.5 0.3
lob reads 70 0.1 0.1
lob writes 49 0.1 0.1
lob writes unaligned 49 0.1 0.1
logons cumulative 54 0.1 0.1
messages received 3,194 6.4 4.5
messages sent 3,194 6.4 4.5
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 422,455 845.5 589.2
opened cursors cumulative 117,488 235.1 163.9
parse count (failures) 0 0.0 0.0
parse count (hard) 248 0.5 0.4
parse count (total) 2,927 5.9 4.1
parse time cpu 186 0.4 0.3
parse time elapsed 406 0.8 0.6
physical read IO requests 407 0.8 0.6
physical read bytes 3,334,144 6,672.9 4,650.1
physical read total IO requests 2,610 5.2 3.6
physical read total bytes 90,762,240 181,649.1 126,586.1
physical read total multi block 50 0.1 0.1
physical reads 407 0.8 0.6
physical reads cache 404 0.8 0.6
physical reads cache prefetch 0 0.0 0.0
physical reads direct 3 0.0 0.0
physical reads direct (lob) 1 0.0 0.0
physical reads direct temporary 0 0.0 0.0
physical reads prefetch warmup 0 0.0 0.0
physical write IO requests 922 1.9 1.3
physical write bytes 16,752,640 33,528.3 23,364.9
physical write total IO requests 4,548 9.1 6.3
physical write total bytes 193,299,456 386,864.3 269,594.8
physical write total multi block 170 0.3 0.2
physical writes 2,045 4.1 2.9
physical writes direct 4 0.0 0.0
physical writes direct (lob) 4 0.0 0.0
physical writes direct temporary 0 0.0 0.0
physical writes from cache 2,041 4.1 2.9
physical writes non checkpoint 1,864 3.7 2.6
pinned buffers inspected 2 0.0 0.0
prefetch warmup blocks aged out 130 0.3 0.2
queries parallelized 2 0.0 0.0
recursive calls 164,301 328.8 229.2
recursive cpu usage 3,780 7.6 5.3
redo blocks checksummed by FG (e 7,911 15.8 11.0
redo blocks written 61,773 123.6 86.2
redo blocks written for direct w 32,912 65.9 45.9
redo buffer allocation retries 2 0.0 0.0
redo entries 107,607 215.4 150.1
redo log space requests 2 0.0 0.0
Instance Activity Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
redo log space wait time 31 0.1 0.0
redo ordering marks 1,475 3.0 2.1
redo size 30,252,920 60,547.4 42,193.8
redo synch time 268 0.5 0.4
redo synch writes 135 0.3 0.2
redo wastage 384,252 769.0 535.9
redo write time 1,699 3.4 2.4
redo writer latching time 0 0.0 0.0
redo writes 1,494 3.0 2.1
rollback changes - undo records 919 1.8 1.3
rollbacks only - consistent read 11 0.0 0.0
rows fetched via callback 33,376 66.8 46.6
session cursor cache hits 115,877 231.9 161.6
session logical reads 853,109 1,707.4 1,189.8
shared hash latch upgrades - no 1,708 3.4 2.4
sorts (memory) 34,215 68.5 47.7
sorts (rows) 292,593 585.6 408.1
sql area evicted 243 0.5 0.3
sql area purged 0 0.0 0.0
summed dirty queue length 1,455 2.9 2.0
switch current to new buffer 64 0.1 0.1
table fetch by rowid 39,774 79.6 55.5
table fetch continued row 72 0.1 0.1
table scan blocks gotten 412,992 826.6 576.0
table scan rows gotten 310,069 620.6 432.5
table scans (direct read) 0 0.0 0.0
table scans (long tables) 0 0.0 0.0
table scans (short tables) 32,038 64.1 44.7
total number of times SMON poste 2 0.0 0.0
transaction lock background get 0 0.0 0.0
transaction lock background gets 0 0.0 0.0
transaction lock foreground requ 0 0.0 0.0
transaction lock foreground wait 0 0.0 0.0
transaction rollbacks 18 0.0 0.0
undo change vector size 10,320,268 20,654.7 14,393.7
user I/O wait time 1,004 2.0 1.4
user calls 1,299 2.6 1.8
user commits 649 1.3 0.9
user rollbacks 68 0.1 0.1
workarea executions - optimal 33,324 66.7 46.5
write clones created in backgrou 0 0.0 0.0
write clones created in foregrou 8 0.0 0.0
-------------------------------------------------------------
Instance Activity Stats - Absolute Values DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
-------------------------------- --------------- ---------------
session pga memory 432,006,060 450,753,612
session pga memory max 936,339,636 976,255,316
session cursor cache count 6,485 7,749
session uga memory 2.748980E+11 2.834903E+11
opened cursors current 57 58
logons current 45 46
session uga memory max 295,259,484 4,621,099,588
-------------------------------------------------------------
Instance Activity Stats - Thread Activity DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
-------------------------------- ------------------ ---------
log switches (derived) 1 7.20
-------------------------------------------------------------
Tablespace IO Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
-> ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
SYSAUX
208 0 23.8 1.0 463 1 0 0.0
EXAMPLE
4 0 30.0 1.0 332 1 0 0.0
SYSTEM
207 0 22.7 1.0 54 0 1 0.0
UNDOTBS1
6 0 23.3 1.0 90 0 2 0.0
-------------------------------------------------------------
File IO Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
-> ordered by Tablespace, File
Tablespace Filename
------------------------ ----------------------------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
EXAMPLE +DATA/racdb/datafile/example.264.649041435
4 0 30.0 1.0 332 1 0 0.0
SYSAUX +DATA/racdb/datafile/sysaux.257.649041137
208 0 23.8 1.0 463 1 0 0.0
SYSTEM +DATA/racdb/datafile/system.256.649041135
207 0 22.7 1.0 54 0 1 0.0
UNDOTBS1 +DATA/racdb/datafile/undotbs1.258.649041139
6 0 23.3 1.0 90 0 2 0.0
-------------------------------------------------------------
Buffer Pool Statistics DB/Inst: RACDB/racdb1 Snaps: 70-71
-> Standard block size Pools D: default, K: keep, R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
Free Writ Buffer
Number of Pool Buffer Physical Physical Buff Comp Busy
P Buffers Hit% Gets Reads Writes Wait Wait Waits
--- ---------- ---- -------------- ------------ ----------- ---- ---- ----------
D 10,648 100 852,909 411 2,041 0 0 3
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: RACDB/racdb1 Snaps: 70-71
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 4 578 5856 9234 92160 9234 N/A
E 0 24 1323 32589 70230 92160 70230 N/A
-------------------------------------------------------------
Buffer Pool Advisory DB/Inst: RACDB/racdb1 Snap: 71
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Est
Phys
Size for Size Buffers for Read Estimated
P Est (M) Factor Estimate Factor Physical Reads
--- -------- ------ ---------------- ------ ------------------
D 8 .1 968 2.0 32,300
D 16 .2 1,936 1.3 21,089
D 24 .3 2,904 1.2 19,748
D 32 .4 3,872 1.2 18,980
D 40 .5 4,840 1.1 18,415
D 48 .5 5,808 1.1 17,968
D 56 .6 6,776 1.1 17,765
D 64 .7 7,744 1.1 17,124
D 72 .8 8,712 1.0 16,357
D 80 .9 9,680 1.0 16,162
D 88 1.0 10,648 1.0 16,095
D 96 1.1 11,616 1.0 15,909
D 104 1.2 12,584 1.0 15,842
D 112 1.3 13,552 1.0 15,749
D 120 1.4 14,520 1.0 15,732
D 128 1.5 15,488 1.0 15,597
D 136 1.5 16,456 1.0 15,572
D 144 1.6 17,424 1.0 15,555
D 152 1.7 18,392 1.0 15,555
D 160 1.8 19,360 0.9