WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numReleaseRACHost
PRECISED3261210183precisedb110.2.0.5.0NOPRECISE1

Snap IdSnap TimeSessionsCursors/Session
Begin Snap:1420922-Jul-13 00:00:4646 1.4
End Snap:1421222-Jul-13 03:00:5367 2.1
Elapsed:  180.11 (mins)  
DB Time:  131.02 (mins)  

Report Summary

Cache Sizes

BeginEnd
Buffer Cache: 880M 1,200MStd Block Size: 8K
Shared Pool Size: 4,208M 3,888MLog Buffer: 14,328K

Load Profile

Per SecondPer Transaction
Redo size: 19,047.31 8,261.45
Logical reads: 137,607.48 59,684.95
Block changes: 84.63 36.71
Physical reads: 120.05 52.07
Physical writes: 8.86 3.84
User calls: 22.39 9.71
Parses: 31.32 13.59
Hard parses: 0.63 0.27
Sorts: 5.94 2.58
Logons: 0.02 0.01
Executes: 97.57 42.32
Transactions: 2.31 

% Blocks changed per Read: 0.06Recursive Call %: 95.95
Rollback per transaction %: 20.65Rows per Sort: 807.17

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00Redo NoWait %: 100.00
Buffer Hit %: 99.91In-memory Sort %: 100.00
Library Hit %: 98.54Soft Parse %: 97.98
Execute to Parse %: 67.90Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 74.43% Non-Parse CPU: 99.64

Shared Pool Statistics

BeginEnd
Memory Usage %: 45.06 18.64
% SQL with executions>1: 96.73 96.34
% Memory for SQL w/exec>1: 93.61 91.86

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time  6,870  87.4 
db file scattered read 130,382 679 5 8.6User I/O
io done 79,709 470 6 6.0System I/O
db file parallel write 42,185 342 8 4.4System I/O
log file sync 22,387 233 10 3.0Commit

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic NameTime (s)% of DB Time
sql execute elapsed time 7,452.18 94.80
DB CPU 6,869.61 87.39
parse time elapsed 40.94 0.52
PL/SQL execution elapsed time 40.06 0.51
hard parse elapsed time 33.69 0.43
PL/SQL compilation elapsed time 4.32 0.05
hard parse (sharing criteria) elapsed time 3.15 0.04
hard parse (bind mismatch) elapsed time 2.43 0.03
connection management call elapsed time 0.60 0.01
repeated bind elapsed time 0.23 0.00
failed parse elapsed time 0.00 0.00
sequence load elapsed time 0.00 0.00
DB time 7,861.15  
background elapsed time 1,306.97  
background cpu time 52.59  

Back to Wait Events Statistics
Back to Top

Wait Class

Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)%Total Call Time
CPU time     6,870   87.39
System I/O 182,186 0 987 5 12.56
User I/O 379,257 0 794 2 10.10
Commit 22,387 2 233 10 2.96
Concurrency 1,552 0 8 5 0.10
Application 6,945   6 1 0.08
Configuration 14 7 0 29 0.01
Network 157,172   0 0 0.01
Other 569 6 0 0 0.00

Back to Wait Events Statistics
Back to Top

Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
db file scattered read 130,382   679 5 5.23
io done 79,709 0 470 6 3.20
db file parallel write 42,185   342 8 1.69
log file sync 22,387 2 233 10 0.90
db file sequential read 246,061   106 0 9.88
log file parallel write 29,870   98 3 1.20
control file parallel write 4,303   75 17 0.17
library cache lock 152   7 44 0.01
direct path sync 243   5 20 0.01
SQL*Net break/reset to client 6,892   5 1 0.28
local write wait 193 1 4 19 0.01
enq: RO - fast object reuse 53   1 26 0.00
Log archive I/O 483   1 2 0.02
os thread startup 30   1 32 0.00
log file sequential read 486   1 1 0.02
buffer busy waits 371   0 1 0.01
log file switch completion 6   0 52 0.00
SQL*Net more data from client 18,819   0 0 0.76
control file sequential read 25,148   0 0 1.01
db file single write 11   0 16 0.00
SQL*Net message to client 136,750   0 0 5.49
latch free 16   0 7 0.00
undo segment extension 1 100 0 96 0.00
cursor: pin S wait on X 4 100 0 12 0.00
latch: In memory undo latch 612   0 0 0.02
rdbms ipc reply 20   0 1 0.00
SQL*Net more data to client 1,603   0 0 0.06
latch: shared pool 54   0 0 0.00
latch: session allocation 4   0 5 0.00
latch: cache buffers chains 282   0 0 0.01
read by other session 701   0 0 0.03
LGWR wait for redo copy 422   0 0 0.02
reliable message 48   0 0 0.00
enq: TX - contention 4   0 1 0.00
latch: library cache 4   0 1 0.00
enq: HW - contention 7   0 0 0.00
enq: TX - index contention 7   0 0 0.00
row cache lock 4   0 0 0.00
direct path write 204   0 0 0.01
direct path read temp 581   0 0 0.02
latch: row cache objects 2   0 1 0.00
direct path read 221   0 0 0.01
direct path write temp 656   0 0 0.03
log file single write 2   0 0 0.00
db file parallel read 4   0 0 0.00
buffer deadlock 32 100 0 0 0.00
latch: object queue header operation 5   0 0 0.00
cursor: mutex X 30   0 0 0.00
latch: enqueue hash chains 1   0 0 0.00
latch: redo allocation 3   0 0 0.00
latch: cache buffers lru chain 4   0 0 0.00
latch: messages 1   0 0 0.00
cursor: pin S 9   0 0 0.00
SQL*Net message from client 136,833   192,415 1406 5.49
i/o slave wait 558,095 19 115,366 207 22.40
virtual circuit status 360 100 10,534 29261 0.01
Streams AQ: qmn slave idle wait 384   10,510 27370 0.02
Streams AQ: qmn coordinator idle wait 786 51 10,510 13371 0.03
Streams AQ: waiting for time management or cleanup tasks 3 100 9,650 3216686 0.00
jobq slave wait 120 100 352 2930 0.00
class slave wait 23   0 0 0.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
io done 79,709 0 470 6 3.20
db file parallel write 42,185   342 8 1.69
log file parallel write 29,870   98 3 1.20
control file parallel write 4,302   75 17 0.17
Log archive I/O 483   1 2 0.02
os thread startup 30   1 32 0.00
log file sequential read 486   1 1 0.02
control file sequential read 9,823   0 0 0.39
db file single write 11   0 16 0.00
db file scattered read 1,183   0 0 0.05
events in waitclass Other 451   0 0 0.02
direct path sync 1   0 21 0.00
db file sequential read 164   0 0 0.01
log file single write 2   0 0 0.00
latch: cache buffers chains 3   0 0 0.00
buffer busy waits 2   0 0 0.00
direct path write 2   0 0 0.00
i/o slave wait 558,095 19 115,366 207 22.40
rdbms ipc message 57,704 60 113,786 1972 2.32
pmon timer 3,618 100 10,551 2916 0.15
Streams AQ: qmn slave idle wait 384   10,510 27370 0.02
Streams AQ: qmn coordinator idle wait 786 51 10,510 13371 0.03
smon timer 38 87 10,342 272168 0.00
Streams AQ: waiting for time management or cleanup tasks 3 100 9,650 3216686 0.00

Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
BUSY_TIME 762,916
IDLE_TIME 16,577,941
IOWAIT_TIME 171,063
NICE_TIME 1,423
SYS_TIME 28,605
USER_TIME 731,441
LOAD 1
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 14,798,147,584
VM_OUT_BYTES 2,367,115,264
PHYSICAL_MEMORY_BYTES 67,555,950,592
NUM_CPUS 16
NUM_CPU_SOCKETS 4

Back to Wait Events Statistics
Back to Top

Service Statistics

Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
SYS$USERS 7,861.10 6,869.60 1,281,300 1,486,928,161
SYS$BACKGROUND 0.00 0.00 16,036 181,784
precisedb 0.00 0.00 0 0
precisedbXDB 0.00 0.00 0 0

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
SYS$USERS 376916 794 1517 7 0 0 156688 0
SYS$BACKGROUND 2336 1 35 1 0 0 0 0

Back to Wait Events Statistics
Back to Top

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

Elapsed Time (s)CPU Time (s)Executions Elap per Exec (s) % Total DB Time SQL IdSQL ModuleSQL Text
3,535 3,614 0   44.97 0qtcgfnrqzwzq Precise - pw.fp INSERT INTO PW_PWSP_SIZE_PREDI...
2,341 2,395 94,599 0.02 29.78 cvhk2j2gymzhd DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
376 13 1 376.34 4.79 6h9k803pq0wz8 Precise - pw.fp BEGIN dbms_stats.gather_table_...
372 11 1 372.38 4.74 gzxq2885kmt94 Precise - pw.fp select /*+ no_parallel(t) no_p...
247 10 1 246.59 3.14 0d028ms9d2dnj Precise - pw.fp BEGIN dbms_stats.gather_table_...
197 9 1 196.92 2.50 fhrsm4khrtm4u Precise - pw.fp select /*+ no_parallel(t) no_p...
186 190 5 37.19 2.37 1421ffggd4x6x JDBC Thin Client select * from (SELECT to_char(...
90 92 189,200 0.00 1.14 djupx9h0ggd9t Precise - pw.fp SELECT /*+ rule */ OI.NAME FRO...
77 79 8,053 0.01 0.98 d89c1mh5pvbkz DBMS_SCHEDULER SELECT /*+ rule */ U.NAME, OT....
47 3 1 47.41 0.60 5qp8mr2fffmyb Precise - pw.fp BEGIN dbms_stats.gather_table_...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

CPU Time (s)Elapsed Time (s)Executions CPU per Exec (s)% Total% Total DB Time SQL IdSQL ModuleSQL Text
3,614 3,535 0   52.61 44.97 0qtcgfnrqzwzq Precise - pw.fp INSERT INTO PW_PWSP_SIZE_PREDI...
2,395 2,341 94,599 0.03 34.86 29.78 cvhk2j2gymzhd DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
190 186 5 38.05 2.77 2.37 1421ffggd4x6x JDBC Thin Client select * from (SELECT to_char(...
92 90 189,200 0.00 1.34 1.14 djupx9h0ggd9t Precise - pw.fp SELECT /*+ rule */ OI.NAME FRO...
79 77 8,053 0.01 1.14 0.98 d89c1mh5pvbkz DBMS_SCHEDULER SELECT /*+ rule */ U.NAME, OT....
27 27 7 3.89 0.40 0.34 4pxq205ftyan4 JDBC Thin Client select * from (select min(udss...
26 26 7 3.79 0.39 0.33 2k8rnvzac77sa JDBC Thin Client select dbms_lob.substr(udsx_un...
19 19 188,078 0.00 0.28 0.24 6qg99cfg26kwb DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(P...
13 376 1 13.21 0.19 4.79 6h9k803pq0wz8 Precise - pw.fp BEGIN dbms_stats.gather_table_...
12 12 94,043 0.00 0.17 0.15 g1xapjmt4vm5c DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
1,381,134,415 94,599 14,599.88 92.87 2394.90 2341.03 cvhk2j2gymzhd DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
36,133,814 8,053 4,487.00 2.43 78.52 77.12 d89c1mh5pvbkz DBMS_SCHEDULER SELECT /*+ rule */ U.NAME, OT....
30,663,635 189,200 162.07 2.06 91.97 89.55 djupx9h0ggd9t Precise - pw.fp SELECT /*+ rule */ OI.NAME FRO...
5,736,628 94,043 61.00 0.39 11.93 11.84 g1xapjmt4vm5c DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
5,225,962 3,542 1,475.43 0.35 7.19 6.93 1vnaz995h6hgw DBMS_SCHEDULER SELECT (SUM(LENGTH)+COUNT(*))*...
3,731,788 188,078 19.84 0.25 19.41 19.17 6qg99cfg26kwb DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(P...
2,629,365 1 2,629,365.00 0.18 5.12 5.07 5b0cwhnywbuhv Precise - pw.fp BEGIN dbms_stats.gather_table_...
2,616,099 1 2,616,099.00 0.18 4.82 4.72 31au3jnhnxzjr Precise - pw.fp BEGIN dbms_stats.gather_table_...
2,616,094 1 2,616,094.00 0.18 4.83 4.72 gz9scsvjabwqk Precise - pw.fp BEGIN dbms_stats.gather_table_...
2,616,088 1 2,616,088.00 0.18 4.82 4.71 5syvh9bprg9hu Precise - pw.fp BEGIN dbms_stats.gather_table_...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
303,674 1 303,674.00 23.41 13.21 376.34 6h9k803pq0wz8 Precise - pw.fp BEGIN dbms_stats.gather_table_...
292,808 1 292,808.00 22.57 10.88 372.38 gzxq2885kmt94 Precise - pw.fp select /*+ no_parallel(t) no_p...
265,929 1 265,929.00 20.50 10.17 246.59 0d028ms9d2dnj Precise - pw.fp BEGIN dbms_stats.gather_table_...
248,353 1 248,353.00 19.14 8.97 196.92 fhrsm4khrtm4u Precise - pw.fp select /*+ no_parallel(t) no_p...
86,171 1 86,171.00 6.64 3.69 38.76 95f7yr63uxcb3 Precise - pw.fp BEGIN dbms_stats.gather_table_...
83,779 1 83,779.00 6.46 11.64 36.91 7m13562pb27t4 Precise - pw.fp BEGIN dbms_stats.gather_table_...
76,489 1 76,489.00 5.90 2.97 47.41 5qp8mr2fffmyb Precise - pw.fp BEGIN dbms_stats.gather_table_...
74,420 1 74,420.00 5.74 2.33 46.53 8aruk2kv6svg2 Precise - pw.fp select /*+ no_parallel(t) no_p...
72,279 1 72,279.00 5.57 2.77 34.53 39v14w2nr1hk8 Precise - pw.fp BEGIN dbms_stats.gather_table_...
52,626 1 52,626.00 4.06 1.98 33.93 f7nur2tfjg0mq Precise - pw.fp select /*+ no_parallel(t) no_p...
46,335 42 1,103.21 3.57 5.00 5.08 d564m67yc8p28 Precise - db2.focal select * from ( select UDSM_S...
41,902 1 41,902.00 3.23 1.57 17.74 1d1n1fa2b5d1b Precise - pw.fp select /*+ no_parallel(t) no_p...
24,610 1 24,610.00 1.90 6.55 6.44 641fnvsjmz608 Precise - pw.fp BEGIN dbms_stats.gather_table_...
22,780 0   1.76 3614.24 3534.99 0qtcgfnrqzwzq Precise - pw.fp INSERT INTO PW_PWSP_SIZE_PREDI...
22,633 1 22,633.00 1.74 1.20 11.96 7ty6x9zvf8zzw Precise - pw.fp select /*+ no_parallel(t) no_p...
20,632 1 20,632.00 1.59 7.72 7.59 26k7fcy3fwnbj Precise - pw.fp BEGIN dbms_stats.gather_table_...
19,434 1 19,434.00 1.50 5.19 6.23 8yruu56rg3uwx Precise - pw.fp select /*+ no_parallel(t) no_p...
17,362 7 2,480.29 1.34 27.21 26.61 4pxq205ftyan4 JDBC Thin Client select * from (select min(udss...
16,087 5 3,217.40 1.24 190.25 185.93 1421ffggd4x6x JDBC Thin Client select * from (SELECT to_char(...
13,754 24 573.08 1.06 5.60 5.55 2anpc942uq2g5 SQL*Plus select owner||'|'||object_name...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text
189,200 11,146 0.06 0.00 0.00 djupx9h0ggd9t Precise - pw.fp SELECT /*+ rule */ OI.NAME FRO...
188,078 188,078 1.00 0.00 0.00 6qg99cfg26kwb DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(P...
113,936 113,934 1.00 0.00 0.00 2ym6hhaq30r73   select type#, blocks, extents,...
94,599 0 0.00 0.03 0.02 cvhk2j2gymzhd DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
94,043 0 0.00 0.00 0.00 g1xapjmt4vm5c DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
23,174 23,175 1.00 0.00 0.00 bunvx480ynf57 Precise - main SELECT 1 FROM DUAL
9,653 9,654 1.00 0.00 0.00 1y95zktz71h35 Precise - pw.fp SELECT PWTR_PWII_ID, PWTR_TABL...
9,650 9,645 1.00 0.00 0.00 2qqznx6a98xv7 Precise - pw.fp SELECT 1 FROM PW_PWII_INSTANCE...
8,053 8,053 1.00 0.01 0.01 d89c1mh5pvbkz DBMS_SCHEDULER SELECT /*+ rule */ U.NAME, OT....
7,481 7,481 1.00 0.00 0.00 27xdav1rg5g14   select u.name, o.name, o.names...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL Text
113,936 113,936 33.66 2ym6hhaq30r73   select type#, blocks, extents,...
23,174 23,174 6.85 bunvx480ynf57 Precise - main SELECT 1 FROM DUAL
9,648 9,653 2.85 1y95zktz71h35 Precise - pw.fp SELECT PWTR_PWII_ID, PWTR_TABL...
9,648 9,650 2.85 2qqznx6a98xv7 Precise - pw.fp SELECT 1 FROM PW_PWII_INSTANCE...
8,053 8,053 2.38 d89c1mh5pvbkz DBMS_SCHEDULER SELECT /*+ rule */ U.NAME, OT....
7,481 7,481 2.21 27xdav1rg5g14   select u.name, o.name, o.names...
7,282 7,282 2.15 3axxxnjp5jjwj   delete from ind_stats$ where o...
7,236 7,236 2.14 gfw9v7ckmbdb6   delete from ind_online$ where ...
6,870 6,870 2.03 1y41akzwjytsq Precise - db2.focal INSERT INTO PS_PWSC_SUMMARY_CO...
6,511 6,509 1.92 1bfzmm2bbajqq Precise - pw.fp UPDATE PW_PWJH_JOB_HISTORY SET...
6,467 6,467 1.91 9ccf9ygspmv2v Precise - pw.fp INSERT INTO PW_PWJH_JOB_HISTOR...
5,725 5,725 1.69 3kywng531fcxu   delete from tab_stats$ where o...
4,084 4,084 1.21 350f5yrnnmshs   lock table sys.mon_mods$ in ex...
3,982 3,982 1.18 b5cr4hhndmbuf   update tab$ set ts#=:2, file#=...
3,982 3,982 1.18 gsfnqdfcvy33q   delete from superobj$ where su...
3,736 3,763 1.10 1fp87jmavgnvs   insert into sys.wri$_optstat_i...
3,736 3,736 1.10 c9vs68pbjptu1   update ind$ set ts#=:2, file#=...
3,473 3,473 1.03 ajqf1bpj72q71   update indsubpart$ set dataobj...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL IdSQL Text
0d028ms9d2dnjBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDEO_EXPLAIN_OPER', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
0qtcgfnrqzwzqINSERT INTO PW_PWSP_SIZE_PREDICTION SELECT PWPS_PWII_ID, PWPS_INSTANCE_NAME, PWPS_TECHNOLOGY, PWPS_SERVER, PWPS_TABLE_NAME, GREATEST(trunc(PWPS_PARTITION_SIZE_AVG, 7), 0), GREATEST(trunc(PWPS_TIME_TO_KEEP_AVG, 7), 0), GREATEST(trunc(PWPS_ESTIMATED_SIZE, 7), 0), PWPS_PARTITIONS_COUNT, NULL AS PWIS_INDEX_NAME FROM PW_PWPS_PARTITIONS_SIZES UNION ALL SELECT PWIS_PWII_ID , PWIS_INSTANCE_NAME, PWIS_TECHNOLOGY, PWIS_SERVER, PWIS_TABLE_NAME, GREATEST(trunc(PWIS_PARTITION_SIZE_AVG, 7), 0), GREATEST(trunc(PWIS_TIME_TO_KEEP_AVG, 7), 0), GREATEST(trunc(PWIS_ESTIMATED_SIZE, 7), 0), PWIS_PARTITION_COUNT, PWIS_INDEX_NAME FROM PW_PWIS_INDEXES_SIZES
1421ffggd4x6x select * from (SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24')as Timestamp, 'DB2UsingCPU' as consumption_type, SUM(UDAS_USING_CPU_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :1 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2SyncIOWait' as consumption_type, SUM(UDAS_SYNC_IO_WAIT_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :2 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2PrefetchWait' as consumption_type, SUM(UDAS_WAIT_PREFETCH_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :3 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2DirectIOWait' as consumption_type, SUM(UDAS_DIRECT_IO_WAIT_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_I NCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :4 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2LockWait' as consumption_type, SUM(UDAS_APPL_LOCK_WAIT_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :5 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2RemoteQueryWait' as consumption_type, SUM(UDAS_REMOTE_QRY_WAIT_TIME_SUM) "consumption_time&quo t; FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :6 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2UtilityTime' as consumption_type, SUM(UDAS_UTILITY_ACTIVE_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :7 GROUP BY TO_CH AR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') union all SELECT to_char((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') as Timestamp, 'DB2IOErrorWait' as consumption_type, SUM(UDAS_IO_ERROR_WAIT_TIME_SUM) "consumption_time" FROM PW_PWGI_GENERAL_INFORMATION, PS_INEN_ENVIRONMENT, PS_INAP_APP_TIER, PS_INCE_INSTANCE, PW_UDAS_APPLICATION_STATS_T, PS_INII_INSTANCE_APPTIER WHERE UDAS_PWII_INSTANCE_ID = PS_INCE_INSTANCE.INCE_ID AND INII_INAP_ID = INAP_ID AND INII_INCE_ID = PS_INCE_INSTANCE.INCE_ID AND INAP_INEN_ID =INEN_ID AND ceil(sysdate-to_date(UDAS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 AND INCE_NAME = :8 GROUP BY TO_CHAR((UDAS_TIMESTAMP+8/24), 'YYYY/MM/DD_HH24') ) order by Timestamp
1bfzmm2bbajqqUPDATE PW_PWJH_JOB_HISTORY SET PWJH_PWII_ID = :1 , PWJH_END_TIMESTAMP = :2 , PWJH_PROCESSED_OBJECT = :3 , PWJH_SUB_PROCESS = :4 , PWJH_DATA_START_TIMESTAMP = :5 , PWJH_DATA_END_TIMESTAMP = :6 , PWJH_STATUS = :7 , PWJH_ROWS_PROCESSED = :8 , PWJH_ERROR_MESSAGE = :9 , PWJH_FILE_NAME = :10 WHERE PWJH_ID = :11
1d1n1fa2b5d1bselect /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count(distinct "UDEP_STATEMENT_HV"), count(distinct "UDEP_EXPLAIN_TIME"), count("UDEP_INDEXABLE_TABLE_NAME"), sum(sys_op_opnsize("UDEP_INDEXABLE_TABLE_NAME")), count("UDEP_INDEXABLE_COLUMN"), sum(sys_op_opnsize("UDEP_INDEXABLE_COLUMN")) from "PRECISE_PMDB"."PW_UDEP_EXPLAIN_PRED" sample ( .7867411245) t
1fp87jmavgnvsinsert into sys.wri$_optstat_ind_history(obj#, rowcnt, leafcnt, distkey, lblkkey, dblkkey, clufac, blevel, analyzetime, samplesize, guessq, cachedblk, cachehit, logicalread, savtime, flags) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16)
1vnaz995h6hgwSELECT (SUM(LENGTH)+COUNT(*))*2 FROM USER$ U, OBJ$ O, IND$ I, ICOL$ IC, COL$ C WHERE U.NAME = :B2 AND O.NAME = :B1 AND U.USER# = O.OWNER# AND O.OBJ# = I.OBJ# AND O.OBJ# = IC.OBJ# AND IC.BO# = C.OBJ# AND IC.INTCOL# = C.INTCOL#
1y41akzwjytsqINSERT INTO PS_PWSC_SUMMARY_CONTROLLER (PWSC_PWII_INSTANCE_ID, PWSC_TABLE_NAME, PWSC_DATA_START_TIMESTAMP, PWSC_PROCESS_START_TIMESTAMP) VALUES(:1 , :2 , :3 , :4 )
1y95zktz71h35SELECT PWTR_PWII_ID, PWTR_TABLE_NAME, PWTR_SUMMARY_LEVEL, PWTR_START_TIMESTAMP, PWTR_END_TIMESTAMP FROM PS_PWTR_TABLE_RANGE WHERE PWTR_PWII_ID = :1 AND PWTR_TABLE_NAME = :2
26k7fcy3fwnbjBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDSS_STATEMENTS_STATS_D', partname=>'P01072013_01082013', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'PARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
27xdav1rg5g14select u.name, o.name, o.namespace, o.type#, decode(bitand(i.property, 1024), 0, 0, 1) from ind$ i, obj$ o, user$ u where i.obj#=:1 and o.obj#=i.bo# and o.owner#=u.user#
2anpc942uq2g5select owner||'|'||object_name||'|'||object_type||'|'||status from dba_objects where status='INVALID'
2k8rnvzac77saselect dbms_lob.substr(udsx_unformatted_text, 4000, 1) as consumer_name, udss_statement_hv from pw_udsx_statement_text, ( select * from ( select min(udss_statement_hv) as udss_statement_hv, min(udss_pwii_instance_id) as udss_pwii_instance_id, sum(udss_total_indb2_time_sum) as udss_total_indb2_time_sum from pw_udss_statements_stats_t, PS_INCE_INSTANCE where ceil(sysdate-to_date(UDSS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 and udss_statement_hv != 0 and INCE_NAME= :1 and ince_id=UDSS_PWII_INSTANCE_ID and ( 1 = 1 ) group by udss_statement_hv order by udss_total_indb2_time_sum desc ) where rownum < 21 ) stats where udsx_statement_hv = stats.udss_statement_hv order by udss_total_indb2_time_sum desc
2qqznx6a98xv7SELECT 1 FROM PW_PWII_INSTANCE_ID WHERE PWII_ID = :1
2ym6hhaq30r73select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
31au3jnhnxzjrBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDDD_DATABASES_DB_STATS_M', partname=>'P01062013_01072013_S1913', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'SUBPARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
350f5yrnnmshslock table sys.mon_mods$ in exclusive mode nowait
39v14w2nr1hk8BEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDEP_EXPLAIN_PRED', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
3axxxnjp5jjwjdelete from ind_stats$ where obj#=:1
3kywng531fcxudelete from tab_stats$ where obj#=:1
4pxq205ftyan4 select * from (select min(udss_statement_hv) as udss_statement_hv, sum(udss_total_indb2_time_sum) as udss_total_indb2_time_sum, sum(sum(udss_total_indb2_time_sum)) over() as udss_in_db2_time_parent_sum, sum(udss_executions_stats_sum) as udss_executions_stats_sum, sum(udss_using_cpu_time_sum) as udss_using_cpu_time_sum, sum(udss_wait_for_cpu_time_sum) as udss_wait_for_cpu_time_sum, sum(udss_sync_io_wait_time_sum) as udss_sync_io_wait_time_sum, sum(udss_direct_io_wait_time_sum) as udss_direct_io_wait_time_sum, sum(udss_wait_prefetch_time_sum) as udss_wait_prefetch_time_sum, sum(udss_appl_lock_wait_time_sum) as udss_lock_wait_sum, sum(udss_io_error_wait_time_sum) as udss_io_error_wait_sum, sum(udss_remote_qry_wait_time_sum) as udss_remote_qry_wait_sum, sum(udss_utility_active_time_sum) as udss_utility_active_time_sum from pw_udss_statements_stats_t, PS_INCE_INSTANCE where ceil(sysdate-to_date(UDSS_TIMESTAMP+8/24, 'DD/MON/YY'))=2 and udss_statement_hv != 0 and INCE_NAME= :1 and ince_id=UDSS_PWII_INSTANCE_ID and ( 1 = 1 ) group by udss_statement_hv) where rownum < 21 order by udss_total_indb2_time_sum desc
5b0cwhnywbuhvBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDDD_DATABASES_DB_STATS_M', partname=>'P01062013_01072013', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'PARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
5qp8mr2fffmybBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDEX_EXPLAIN_TEXT', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
5syvh9bprg9huBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDDD_DATABASES_DB_STATS_M', partname=>'P01062013_01072013_S1960', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'SUBPARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
641fnvsjmz608BEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDSA_STMT_APPL_STATS_D', partname=>'P01072013_01082013', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'PARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
6h9k803pq0wz8BEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDSX_STATEMENT_TEXT', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
6qg99cfg26kwbSELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND CD.INTCOLS = 1 AND CD.TYPE# IN (2, 3) AND BITAND(CD.DEFER, 2+4) = 4 AND ROWNUM < 2 UNION ALL SELECT /*+ first_rows(1) leading(i) */ CASE WHEN I.INTCOLS = 1 AND BITAND(I.PROPERTY, 1) = 1 THEN 3 ELSE NULL END UNQ, CASE WHEN IC.POS# = 1 THEN 1 ELSE NULL END PFX FROM SYS.IND$ I, SYS.ICOL$ IC WHERE I.BO# = :B2 AND I.BO# = IC.BO# AND IC.INTCOL# = :B1 AND I.OBJ# = IC.OBJ# AND BITAND(I.FLAGS, 1025) = 0 AND ROWNUM < 2 )
7m13562pb27t4BEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDSM_STATEMENTS', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
7ty6x9zvf8zzwselect /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count(distinct "UDSM_STATEMENT_HV"), count("UDSM_STATEMENT_ID"), count(distinct "UDSM_STATEMENT_ID"), count("UDSM_STATEMENT_NAME"), count(distinct "UDSM_STATEMENT_NAME"), count("UDSM_COMMENTS"), sum(sys_op_opnsize("UDSM_COMMENTS")) from "PRECISE_PMDB"."PW_UDSM_STATEMENTS" sample ( 1.9848257975) t
8aruk2kv6svg2select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), sum(sys_op_opnsize("UDEX_EXPLAIN_TEXT_HV")), substrb(dump(min("UDEX_EXPLAIN_TEXT_HV"), 16, 0, 32), 1, 120), substrb(dump(max("UDEX_EXPLAIN_TEXT_HV"), 16, 0, 32), 1, 120), sum(sys_op_opnsize("UDEX_EXPLAIN_TEXT")) from "PRECISE_PMDB"."PW_UDEX_EXPLAIN_TEXT" sample ( 1.0707340794) t
8yruu56rg3uwxselect /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count(distinct "UDSM_STATEMENT_HV"), count("UDSM_STATEMENT_ID"), count(distinct "UDSM_STATEMENT_ID"), count("UDSM_STATEMENT_NAME"), count(distinct "UDSM_STATEMENT_NAME"), count("UDSM_COMMENTS"), sum(sys_op_opnsize("UDSM_COMMENTS")) from "PRECISE_PMDB"."PW_UDSM_STATEMENTS" sample ( 19.8482579747) t
95f7yr63uxcb3BEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDEJ_EXPLAIN_OBJECT', method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', granularity => 'ALL', cascade=>TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE ); END;
9ccf9ygspmv2vINSERT INTO PW_PWJH_JOB_HISTORY (PWJH_ID, PWJH_PWJG_ID, PWJH_PWII_ID, PWJH_BEGIN_TIMESTAMP, PWJH_PROCESSED_OBJECT, PWJH_DATA_START_TIMESTAMP, PWJH_DATA_END_TIMESTAMP, PWJH_END_TIMESTAMP, PWJH_STATUS, PWJH_ROWS_PROCESSED, PWJH_SUB_PROCESS, PWJH_PWSE_ID) VALUES (:1 , :2 , :3 , :4 , :5 , NULL, NULL, NULL, :6 , 0, :7 , :8 )
ajqf1bpj72q71update indsubpart$ set dataobj# = :1, subpart# = :2, flags = :3, ts# = :4, file# = :5, block# = :6, pctfree$ = :7, initrans = :8, maxtrans = :9, analyzetime = :10, samplesize = :11, rowcnt = :12, blevel = :13, leafcnt = :14, distkey = :15, lblkkey = :16, dblkkey = :17, clufac = :18, spare2 = :19 where obj# = :20
b5cr4hhndmbufupdate tab$ set ts#=:2, file#=:3, block#=:4, bobj#=decode(:5, 0, null, :5), tab#=decode(:6, 0, null, :6), intcols=:7, kernelcols=:8, clucols=decode(:9, 0, null, :9), audit$=:10, flags=:11, pctfree$=:12, pctused$=:13, initrans=:14, maxtrans=:15, rowcnt=:16, blkcnt=:17, empcnt=:18, avgspc=:19, chncnt=:20, avgrln=:21, analyzetime=:22, samplesize=:23, cols=:24, property=:25, degree=decode(:26, 1, null, :26), instances=decode(:27, 1, null, :27), dataobj#=:28, avgspc_flb=:29, flbcnt=:30, trigflag=:31, spare1=:32, spare2=decode(:33, 0, null, :33), spare4=:34, spare6=:35 where obj#=:1
bunvx480ynf57SELECT 1 FROM DUAL
c9vs68pbjptu1update ind$ set ts#=:2, file#=:3, block#=:4, intcols=:5, type#=:6, flags=:7, property=:8, pctfree$=:9, initrans=:10, maxtrans=:11, blevel=:12, leafcnt=:13, distkey=:14, lblkkey=:15, dblkkey=:16, clufac=:17, cols=:18, analyzetime=:19, samplesize=:20, dataobj#=:21, degree=decode(:22, 1, null, :22), instances=decode(:23, 1, null, :23), rowcnt=:24, pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27, spare1=:28, spare4=:29, spare2=:30, spare6=:32where obj#=:1
cvhk2j2gymzhdSELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, OBJ$ TY WHERE O.OWNER#=U.USER# AND A.OBJ#=TY.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# AND CT.TOID=TY.OID$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 AND O.NAME=:B3 AND U.NAME=:B2 AND C.NAME=:B1
d564m67yc8p28 select * from ( select UDSM_STATEMENT_HV, UDSM_PARSING_USER, UDSM_ACCESS_PATH_HV, current_timestamp, max(( to_char(current_timestamp, 'dd') - coalesce(to_char(UDSM_LAST_EXPLAINED, 'dd'), to_char(current_timestamp-:1 , 'dd')))*samp.total_indb2_time) from (select * from (select max(s.UDSS_PWII_INSTANCE_ID) as UDSS_PWII_INSTANCE_ID, s.UDSS_STATEMENT_HV, sum(cast(s.UDSS_TOTAL_INDB2_TIME_SUM as number(9))) total_indb2_time from PW_UDSS_STATEMENTS_STATS_T s left outer join ( select coalesce(max(g.pwjg_begin_timestamp), current_timestamp - cast (:2 as numeric)) as last_explain_time from PW_PWJG_JOB_GROUP g, PW_PWJH_JOB_HISTORY h, PS_UDDA_ACT_INS_DATA i where g.PWJG_ID = h.PWJH_PWJG_ID and g.PWJG_PWSE_ID=61 and g.PWJG_STATUS = 'OK' and i.UDDA_INSTANCE_ID = h.pwjh_pwii_id and i.UDDA_INCL_ID = :3 ) a on (1=1) where s.UDSS_CLUSTER_ID = :4 and s.UDSS_TIMESTAMP >= last_explain_time and s.UDSS_STATEMENT_HV <> 0 group by s.UDSS_STATEMENT_HV) where rownum < :5 *3 ) samp inner join PW_UDSM_STATEMENTS on UDSM_CLUSTER_ID = :6 and UDSM_PWII_INSTANCE_ID = samp.UDSS_PWII_INSTANCE_ID and UDSM_STATEMENT_HV = samp.UDSS_STATEMENT_HV where coalesce (UDSM_LAST_EXPLAINED, to_date('1900-01-01', 'yyyy-mm-dd')) < current_timestamp - cast ( :7 as numeric) and UDSM_DO_NOT_EXPLAIN = 'N' and UDSM_SOURCE = 'C' group by UDSM_STATEMENT_HV , UDSM_PARSING_USER , UDSM_ACCESS_PATH_HV order by max( to_char(current_timestamp, 'dd') - coalesce(to_char(UDSM_LAST_EXPLAINED, 'dd'), to_char(current_timestamp-:8 , 'dd'))*samp.total_indb2_time) desc ) where rownum < :9
d89c1mh5pvbkz SELECT /*+ rule */ U.NAME, OT.NAME, NULL, NULL, C.NAME, BITAND(H.SPARE2, 2), BITAND(H.SPARE2, 1), H.DISTCNT, H.DENSITY, H.SPARE1, H.SAMPLE_SIZE, H.NULL_CNT, H.TIMESTAMP#, H.AVGCLN, H.LOWVAL, H.HIVAL, H.MINIMUM, H.MAXIMUM, NULL, NULL, H.CACHE_CNT, HG.BUCKET, HG.ENDPOINT, HG.EPVALUE, BITAND(H.SPARE2, 4), NULL FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.COL$ C, SYS.HIST_HEAD$ H, HISTGRM$ HG WHERE :B4 IS NULL AND U.NAME = :B3 AND OT.OWNER# = U.USER# AND OT.NAME = :B2 AND OT.TYPE# = 2 AND C.OBJ# = OT.OBJ# AND (:B1 IS NULL OR C.NAME = :B1 ) AND H.OBJ# = OT.OBJ# AND H.INTCOL# = C.INTCOL# AND HG.OBJ#(+) = H.OBJ# AND HG.INTCOL#(+) = H.INTCOL# UNION ALL SELECT U.NAME, OT.NAME, OP.SUBNAME, NULL, C.NAME, BITAND(H.SPARE2, 2), BITAND(H.SPARE2, 1), H.DISTCNT, H.DENSITY, H.SPARE1, H.SAMPLE_SIZE, H.NULL_CNT, H.TIMESTAMP#, H.AVGCLN, H.LOWVAL, H.HIVAL, H.MINIMUM, H.MAXIMUM, NULL, NULL, H.CACHE_CNT, HG.BUCKET, HG.ENDPOINT, HG.EPVALUE, BITAND(H.SPARE2, 4), NULL FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.COL$ C, SYS.TABPART$ TP, SYS.OBJ$ OP, SYS.HIST_HEAD$ H, HISTGRM$ HG WHERE U.NAME = :B3 AND OT.OWNER# = U.USER# AND OT.NAME = :B2 AND OT.TYPE# = 2 AND C.OBJ# = OT.OBJ# AND (:B1 IS NULL OR C.NAME = :B1 ) AND TP.BO# = OT.OBJ# AND TP.OBJ# = OP.OBJ# AND ((:B4 IS NULL AND :B5 IS NOT NULL) OR OP.SUBNAME = :B4 ) AND H.OBJ# = OP.OBJ# AND H.INTCOL# = C.INTCOL# AND HG.OBJ#(+) = H.OBJ# AND HG.INTCOL#(+) = H.INTCOL# UNION ALL SELECT U.NAME, OP.NAME, OP.SUBNAME , NULL, C.NAME, BITAND(H.SPARE2, 2), BITAND(H.SPARE2, 1), H.DISTCNT, H.DENSITY, H.SPARE1, H.SAMPLE_SIZE, H.NULL_CNT, H.TIMESTAMP#, H.AVGCLN, H.LOWVAL, H.HIVAL, H.MINIMUM, H.MAXIMUM, NULL, NULL, H.CACHE_CNT, HG.BUCKET, HG.ENDPOINT, HG.EPVALUE, BITAND(H.SPARE2, 4), NULL FROM SYS.USER$ U, SYS.COL$ C, SYS.TABCOMPART$ TP, SYS.OBJ$ OP, SYS.HIST_HEAD$ H, HISTGRM$ HG WHERE U.NAME = :B3 AND OP.OWNER# = U.USER# AND OP.NAME = :B2 AND OP.TYPE# = 19 AND ((:B4 IS NULL AND :B5 IS NOT NULL) OR OP.SUBNAME = :B4 ) AND TP.OBJ# = OP.OBJ# AND C.OBJ# = TP.BO# AND (:B1 IS NULL OR C.NAME = :B1 ) AND H.OBJ# = OP.OBJ# AND H.INTCOL# = C.INTCOL# AND HG.OBJ#(+) = H.OBJ# AND HG.INTCOL#(+) = H.INTCOL# UNION ALL SELECT U.NAME, OP.NAME, OP.SUBNAME, OS.SUBNAME, C.NAME, BITAND(H.SPARE2, 2), BITAND(H.SPARE2, 1), H.DISTCNT, H.DENSITY, H.SPARE1, H.SAMPLE_SIZE, H.NULL_CNT, H.TIMESTAMP#, H.AVGCLN, H.LOWVAL, H.HIVAL, H.MINIMUM, H.MAXIMUM, NULL, NULL, H.CACHE_CNT, HG.BUCKET, HG.ENDPOINT, HG.EPVALUE, BITAND(H.SPARE2, 4), NULL FROM SYS.USER$ U, SYS.COL$ C, SYS.TABCOMPART$ TP, SYS.OBJ$ OP, SYS.TABSUBPART$ TS, SYS.OBJ$ OS, SYS.HIST_HEAD$ H, HISTGRM$ HG WHERE U.NAME = :B3 AND OP.OWNER# = U.USER# AND OP.NAME = :B2 AND OP.TYPE# = 19 AND TP.OBJ# = OP.OBJ# AND C.OBJ# = TP.BO# AND (:B1 IS NULL OR C.NAME = :B1 ) AND TS.POBJ# = TP.OBJ# AND TS.OBJ# = OS.OBJ# AND ((:B4 IS NULL AND :B5 IS NOT NULL) OR (OP.SUBNAME = :B4 AND (:B5 IS NOT NULL OR OS.SUBNAME IS NULL)) OR OS.SUBNAME = :B4 ) AND H.OBJ# = OS.OBJ# AND H.INTCOL# = C.INTCOL# AND HG.OBJ#(+) = H.OBJ# AND HG.INTCOL#(+) = H.INTCOL# ORDER BY 1, 2, 3, 4, 5, 22
djupx9h0ggd9tSELECT /*+ rule */ OI.NAME FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.COL$ C, SYS.ICOL$ IC, SYS.OBJ$ OI WHERE U.NAME = :B3 AND OT.OWNER# = U.USER# AND OT.NAME = :B2 AND OT.TYPE# = 2 AND OT.OBJ# = C.OBJ# AND C.NAME = :B1 AND OT.OBJ# = IC.BO# AND IC.INTCOL# = C.INTCOL# AND IC.OBJ# = OI.OBJ#
f7nur2tfjg0mqselect /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count(distinct "UDEJ_STATEMENT_HV"), count(distinct "UDEJ_EXPLAIN_TIME") from "PRECISE_PMDB"."PW_UDEJ_EXPLAIN_OBJECT" sample ( 1.5929963216) t
fhrsm4khrtm4uselect /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), count(distinct "UDEO_STATEMENT_HV"), count(distinct "UDEO_EXPLAIN_TIME"), count("UDEO_PART_NUM"), sum(sys_op_opnsize("UDEO_PART_NUM")) from "PRECISE_PMDB"."PW_UDEO_EXPLAIN_OPER" sample ( .8548438895) t
g1xapjmt4vm5cSELECT SU.NAME, SO.NAME, A.STATSTYPE#, A.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU WHERE O.OWNER#=U.USER# AND A.OBJ#=O.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=A.INTCOL# AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 AND U.NAME=:B3 AND O.NAME=:B2 AND C.NAME=:B1
gfw9v7ckmbdb6delete from ind_online$ where obj#= :1
gsfnqdfcvy33qdelete from superobj$ where subobj# = :1
gz9scsvjabwqkBEGIN dbms_stats.gather_table_stats(ownname=>'PRECISE_PMDB', tabname=>'PW_UDDD_DATABASES_DB_STATS_M', partname=>'P01062013_01072013_S1826', method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1', granularity=>'SUBPARTITION', cascade=>TRUE, estimate_percent=>dbms_stats.auto_sample_size ); END;
gzxq2885kmt94select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*), sum(sys_op_opnsize("UDSX_STATEMENT_HV")), substrb(dump(min("UDSX_STATEMENT_HV"), 16, 0, 32), 1, 120), substrb(dump(max("UDSX_STATEMENT_HV"), 16, 0, 32), 1, 120), count("UDSX_CONSISTENT_HV"), count(distinct "UDSX_CONSISTENT_HV"), sum(sys_op_opnsize("UDSX_CONSISTENT_HV")), substrb(dump(min("UDSX_CONSISTENT_HV"), 16, 0, 32), 1, 120), substrb(dump(max("UDSX_CONSISTENT_HV"), 16, 0, 32), 1, 120), sum(sys_op_opnsize("UDSX_STATEMENT_TEXT")), sum(sys_op_opnsize("UDSX_UNFORMATTED_TEXT")) from "PRECISE_PMDB"."PW_UDSX_STATEMENT_TEXT" sample ( .5159082258) t

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Instance Activity Stats

StatisticTotalper Secondper Trans
CPU used by this session 323,475 29.93 12.98
CPU used when call started 321,736 29.77 12.91
CR blocks created 8,641 0.80 0.35
Cached Commit SCN referenced 10,145 0.94 0.41
Commit SCN cached 35 0.00 0.00
DB time 463,669 42.90 18.61
DBWR checkpoint buffers written 77,060 7.13 3.09
DBWR checkpoints 51 0.00 0.00
DBWR object drop buffers written 224 0.02 0.01
DBWR revisited being-written buffer 0 0.00 0.00
DBWR tablespace checkpoint buffers written 0 0.00 0.00
DBWR transaction table writes 301 0.03 0.01
DBWR undo block writes 11,505 1.06 0.46
IMU CR rollbacks 18 0.00 0.00
IMU Flushes 4,527 0.42 0.18
IMU Redo allocation size 5,858,608 542.12 235.13
IMU commits 14,756 1.37 0.59
IMU contention 484 0.04 0.02
IMU ktichg flush 3,272 0.30 0.13
IMU pool not allocated 513 0.05 0.02
IMU recursive-transaction flush 24 0.00 0.00
IMU undo allocation size 55,306,000 5,117.66 2,219.70
IMU- failed to get a private strand 513 0.05 0.02
Misses for writing mapping 0 0.00 0.00
PX local messages recv'd 0 0.00 0.00
PX local messages sent 0 0.00 0.00
RowCR - row contention 0 0.00 0.00
RowCR attempts 115 0.01 0.00
RowCR hits 112 0.01 0.00
SMON posted for undo segment shrink 5 0.00 0.00
SQL*Net roundtrips to/from client 136,472 12.63 5.48
active txn count during cleanout 10,941 1.01 0.44
application wait time 608 0.06 0.02
background checkpoints completed 1 0.00 0.00
background checkpoints started 1 0.00 0.00
background timeouts 34,768 3.22 1.40
branch node splits 4 0.00 0.00
buffer is not pinned count 1,417,631,910 131,178.42 56,896.45
buffer is pinned count 2,280,520,773 211,024.53 91,528.37
bytes received via SQL*Net from client 85,639,513 7,924.52 3,437.13
bytes sent via SQL*Net to client 28,331,936 2,621.65 1,137.10
calls to get snapshot scn: kcmgss 8,020,237 742.14 321.89
calls to kcmgas 63,824 5.91 2.56
calls to kcmgcs 6,802 0.63 0.27
change write time 919 0.09 0.04
cleanout - number of ktugct calls 15,434 1.43 0.62
cleanouts and rollbacks - consistent read gets 45 0.00 0.00
cleanouts only - consistent read gets 1,895 0.18 0.08
cluster key scan block gets 6,737,250 623.42 270.40
cluster key scans 3,038,664 281.18 121.96
commit batch/immediate performed 79 0.01 0.00
commit batch/immediate requested 79 0.01 0.00
commit cleanout failures: block lost 4,209 0.39 0.17
commit cleanout failures: buffer being written 0 0.00 0.00
commit cleanout failures: callback failure 467 0.04 0.02
commit cleanout failures: cannot pin 802 0.07 0.03
commit cleanouts 174,637 16.16 7.01
commit cleanouts successfully completed 169,159 15.65 6.79
commit immediate performed 79 0.01 0.00
commit immediate requested 79 0.01 0.00
commit txn count during cleanout 11,518 1.07 0.46
concurrency wait time 809 0.07 0.03
consistent changes 57,948 5.36 2.33
consistent gets 1,486,159,405 137,519.51 59,646.79
consistent gets - examination 64,049,386 5,926.71 2,570.61
consistent gets direct 421 0.04 0.02
consistent gets from cache 1,486,158,984 137,519.47 59,646.77
current blocks converted for CR 0 0.00 0.00
cursor authentications 111 0.01 0.00
data blocks consistent reads - undo records applied 28,385 2.63 1.14
db block changes 914,641 84.63 36.71
db block gets 950,747 87.98 38.16
db block gets direct 3,414 0.32 0.14
db block gets from cache 947,333 87.66 38.02
deferred (CURRENT) block cleanout applications 84,275 7.80 3.38
dirty buffers inspected 4,335 0.40 0.17
enqueue conversions 2,433 0.23 0.10
enqueue deadlocks 0 0.00 0.00
enqueue releases 251,619 23.28 10.10
enqueue requests 251,622 23.28 10.10
enqueue timeouts 1 0.00 0.00
enqueue waits 67 0.01 0.00
exchange deadlocks 32 0.00 0.00
execute count 1,054,396 97.57 42.32
failed probes on index block reclamation 4,830 0.45 0.19
free buffer inspected 1,306,895 120.93 52.45
free buffer requested 1,318,688 122.02 52.93
heap block compress 1,286 0.12 0.05
hot buffers moved to head of LRU 174,553 16.15 7.01
immediate (CR) block cleanout applications 1,940 0.18 0.08
immediate (CURRENT) block cleanout applications 23,717 2.19 0.95
index crx upgrade (found) 2 0.00 0.00
index crx upgrade (positioned) 43,491 4.02 1.75
index fast full scans (full) 5,180 0.48 0.21
index fetch by key 22,844,198 2,113.85 916.85
index scans kdiixs1 2,616,098 242.08 105.00
leaf node 90-10 splits 803 0.07 0.03
leaf node splits 2,286 0.21 0.09
lob reads 229 0.02 0.01
lob writes 772 0.07 0.03
lob writes unaligned 463 0.04 0.02
logons cumulative 193 0.02 0.01
messages received 72,436 6.70 2.91
messages sent 72,436 6.70 2.91
no buffer to keep pinned count 21,434,134 1,983.38 860.26
no work - consistent read gets 1,415,277,404 130,960.55 56,801.95
opened cursors cumulative 365,232 33.80 14.66
parse count (failures) 4 0.00 0.00
parse count (hard) 6,822 0.63 0.27
parse count (total) 338,501 31.32 13.59
parse time cpu 2,500 0.23 0.10
parse time elapsed 3,359 0.31 0.13
physical read IO requests 377,254 34.91 15.14
physical read bytes 10,628,046,848 983,450.19 426,555.10
physical read total IO requests 402,955 37.29 16.17
physical read total bytes 11,544,916,480 1,068,291.33 463,353.53
physical read total multi block requests 131,499 12.17 5.28
physical reads 1,297,369 120.05 52.07
physical reads cache 1,288,286 119.21 51.71
physical reads cache prefetch 911,948 84.39 36.60
physical reads direct 9,083 0.84 0.36
physical reads direct (lob) 277 0.03 0.01
physical reads direct temporary tablespace 8,715 0.81 0.35
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 46,471 4.30 1.87
physical write bytes 784,416,768 72,584.82 31,482.45
physical write total IO requests 90,571 8.38 3.64
physical write total bytes 1,720,933,888 159,244.01 69,069.43
physical write total multi block requests 39,442 3.65 1.58
physical writes 95,754 8.86 3.84
physical writes direct 12,129 1.12 0.49
physical writes direct (lob) 257 0.02 0.01
physical writes direct temporary tablespace 11,847 1.10 0.48
physical writes from cache 83,625 7.74 3.36
physical writes non checkpoint 45,053 4.17 1.81
pinned buffers inspected 4,277 0.40 0.17
prefetch warmup blocks aged out before use 0 0.00 0.00
prefetched blocks aged out before use 410,825 38.02 16.49
process last non-idle time 10,932 1.01 0.44
recursive calls 5,738,760 531.03 230.32
recursive cpu usage 282,248 26.12 11.33
redo blocks written 430,560 39.84 17.28
redo buffer allocation retries 3 0.00 0.00
redo entries 448,845 41.53 18.01
redo log space requests 6 0.00 0.00
redo log space wait time 32 0.00 0.00
redo ordering marks 11,350 1.05 0.46
redo size 205,842,400 19,047.31 8,261.45
redo subscn max counts 27,653 2.56 1.11
redo synch time 24,192 2.24 0.97
redo synch writes 21,781 2.02 0.87
redo wastage 7,199,300 666.18 288.94
redo write time 12,067 1.12 0.48
redo writer latching time 1 0.00 0.00
redo writes 29,870 2.76 1.20
rollback changes - undo records applied 113 0.01 0.00
rollbacks only - consistent read gets 8,318 0.77 0.33
root node splits 1 0.00 0.00
rows fetched via callback 11,140,548 1,030.87 447.12
session connect time 0 0.00 0.00
session cursor cache hits 291,814 27.00 11.71
session logical reads 1,487,110,152 137,607.48 59,684.95
shared hash latch upgrades - no wait 56,775 5.25 2.28
shared hash latch upgrades - wait 4 0.00 0.00
sorts (memory) 64,200 5.94 2.58
sorts (rows) 51,820,340 4,795.12 2,079.80
sql area evicted 3,256 0.30 0.13
sql area purged 3,487 0.32 0.14
summed dirty queue length 23,448 2.17 0.94
switch current to new buffer 409 0.04 0.02
table fetch by rowid 1,287,399,358 119,127.55 51,669.58
table fetch continued row 413,142 38.23 16.58
table scan blocks gotten 2,431,034 224.95 97.57
table scan rows gotten 82,439,520 7,628.42 3,308.70
table scans (cache partitions) 1 0.00 0.00
table scans (long tables) 367 0.03 0.01
table scans (short tables) 2,360,271 218.40 94.73
total number of times SMON posted 5 0.00 0.00
transaction rollbacks 79 0.01 0.00
transaction tables consistent read rollbacks 0 0.00 0.00
transaction tables consistent reads - undo records applied 0 0.00 0.00
undo change vector size 72,616,864 6,719.49 2,914.47
user I/O wait time 79,410 7.35 3.19
user calls 241,997 22.39 9.71
user commits 19,771 1.83 0.79
user rollbacks 5,145 0.48 0.21
workarea executions - onepass 3 0.00 0.00
workarea executions - optimal 56,360 5.22 2.26
write clones created in background 192 0.02 0.01
write clones created in foreground 5 0.00 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

StatisticBegin ValueEnd Value
session pga memory max 1,056,451,392 1,313,382,616
session cursor cache count 85,697 88,010
session uga memory 1.3E+12 1.3E+12
opened cursors current 65 138
workarea memory allocated 0 59,993
logons current 46 67
session uga memory max 24,456,383,288 25,086,941,928
session pga memory 424,143,008 509,369,912

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

StatisticTotalper Hour
log switches (derived) 1 0.33

Back to Instance Activity Statistics
Back to Top

IO Stats

Back to Top

Tablespace IO Stats

TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
PRECISE_PMDB_TAB_SMALL 197,333 18 3.64 4.63 9,631 1 34 4.71
PRECISE_PMDB_IND_SMALL 124,805 12 0.49 1.64 24,412 2 972 0.19
SYSTEM 29,501 3 0.11 4.76 3,689 0 1 0.00
PRECISE_PMDB_TAB_LARGE 14,719 1 0.07 1.00 338 0 0 0.00
SYSAUX 5,475 1 0.04 1.01 5,670 1 0 0.00
PRECISE_PMDB_IND_LARGE 2,499 0 0.53 1.04 87 0 0 0.00
PRECISE_PMDB_TMP 795 0 0.25 14.89 713 0 0 0.00
UNDOTBS1 31 0 0.00 1.00 1,418 0 65 0.31
PRECISE_PMDB_IND_MEDIUM 1,263 0 0.55 1.26 108 0 0 0.00
PATROL_DATA 479 0 0.02 1.03 379 0 0 0.00
PRECISE_PMDB_TAB_MEDIUM 290 0 2.48 6.75 19 0 0 0.00
TEMP 7 0 0.00 1.00 7 0 0 0.00

Back to IO Stats
Back to Top

File IO Stats

TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
PATROL_DATA /oradata/PRECISEDB/patrol_data.dbf 479 0 0.02 1.03 379 0 0 0.00
PRECISE_PMDB_IND_LARGE /oradata/PRECISEDB/PRECISE_PMDB_IND_LARGE.dbf 2,499 0 0.53 1.04 87 0 0 0.00
PRECISE_PMDB_IND_MEDIUM /oradata/PRECISEDB/PRECISE_PMDB_IND_MEDIUM.dbf 1,263 0 0.55 1.26 108 0 0 0.00
PRECISE_PMDB_IND_SMALL /oradata/PRECISEDB/PRECISE_PMDB_IND_SMALL.dbf 124,805 12 0.49 1.64 24,412 2 972 0.19
PRECISE_PMDB_TAB_LARGE /oradata/PRECISEDB/PRECISE_PMDB_TAB_LARGE.dbf 14,719 1 0.07 1.00 338 0 0 0.00
PRECISE_PMDB_TAB_MEDIUM /oradata/PRECISEDB/PRECISE_PMDB_TAB_MEDIUM.dbf 290 0 2.48 6.75 19 0 0 0.00
PRECISE_PMDB_TAB_SMALL /oradata/PRECISEDB/PRECISE_PMDB_TAB_SMALL.dbf 197,333 18 3.64 4.63 9,631 1 34 4.71
PRECISE_PMDB_TMP /oradata/PRECISEDB/PRECISE_PMDB_TMP.dbf 795 0 0.25 14.89 713 0 0  
SYSAUX /oradata/PRECISEDB/o1_mf_sysaux_7fxm0zks_.dbf 5,475 1 0.04 1.01 5,670 1 0 0.00
SYSTEM /oradata/PRECISEDB/o1_mf_system_7fxm0zko_.dbf 29,501 3 0.11 4.76 3,689 0 1 0.00
TEMP /oradata/PRECISEDB/o1_mf_temp_7fxm2f8k_.tmp 7 0 0.00 1.00 7 0 0  
UNDOTBS1 /oradata/PRECISEDB/o1_mf_undotbs1_7fxm0zm7_.dbf 31 0 0.00 1.00 1,418 0 65 0.31

Back to IO Stats
Back to Top

Buffer Pool Statistics

PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
D 148,424 100 1,487,099,200 1,288,280 83,625 0 0 1,072


Back to Top

Advisory Statistics

Back to Top

Instance Recovery Stats

Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual Redo BlksTarget Redo BlksLog File Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
B 0 8 173 557 29562 3774870 29562  
E 0 8 224 885 41143 3774870 41143  

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

PSize for Est (M)Size FactorBuffers for EstimateEst Phys Read FactorEstimated Physical Reads
D 112 0.09 13,853 1.35 24,462,184
D 224 0.19 27,706 1.07 19,429,357
D 336 0.28 41,559 1.05 19,056,875
D 448 0.37 55,412 1.04 18,877,544
D 560 0.47 69,265 1.03 18,765,378
D 672 0.56 83,118 1.03 18,670,139
D 784 0.65 96,971 1.02 18,589,507
D 896 0.75 110,824 1.02 18,519,483
D 1,008 0.84 124,677 1.02 18,433,989
D 1,120 0.93 138,530 1.01 18,336,790
D 1,200 1.00 148,425 1.00 18,157,491
D 1,232 1.03 152,383 1.00 18,085,779
D 1,344 1.12 166,236 0.96 17,520,921
D 1,456 1.21 180,089 0.93 16,870,997
D 1,568 1.31 193,942 0.89 16,107,190
D 1,680 1.40 207,795 0.84 15,165,198
D 1,792 1.49 221,648 0.78 14,222,473
D 1,904 1.59 235,501 0.73 13,278,580
D 2,016 1.68 249,354 0.68 12,334,323
D 2,128 1.77 263,207 0.63 11,390,371
D 2,240 1.87 277,060 0.57 10,435,828

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
98.20 4,644 85

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

PGA Aggr Target(M)Auto PGA Target(M)PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem%Auto W/A Mem%Man W/A MemGlobal Mem Bound(K)
B 512 413 84.06 0.00 0.00 0.00 0.00 102,400
E 512 403 239.51 58.59 24.46 100.00 0.00 102,400

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K 4K 51,857 51,857 0 0
64K 128K 697 697 0 0
128K 256K 438 438 0 0
256K 512K 113 113 0 0
512K 1024K 2,518 2,518 0 0
1M 2M 527 527 0 0
2M 4M 66 66 0 0
4M 8M 14 12 2 0
8M 16M 62 62 0 0
16M 32M 20 20 0 0
32M 64M 7 7 0 0
64M 128M 1 0 1 0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc Count
64 0.13 150,857.41 45,263.42 77.00 1,115
128 0.25 150,857.41 22,179.58 87.00 0
256 0.50 150,857.41 5,893.18 96.00 0
384 0.75 150,857.41 5,834.23 96.00 0
512 1.00 150,857.41 5,735.27 96.00 0
614 1.20 150,857.41 5,291.10 97.00 0
717 1.40 150,857.41 5,291.10 97.00 0
819 1.60 150,857.41 5,291.10 97.00 0
922 1.80 150,857.41 5,291.10 97.00 0
1,024 2.00 150,857.41 5,291.10 97.00 0
1,536 3.00 150,857.41 5,291.10 97.00 0
2,048 4.00 150,857.41 5,291.10 97.00 0
3,072 6.00 150,857.41 5,291.10 97.00 0
4,096 8.00 150,857.41 5,291.10 97.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits (K)
688 0.18 440 38,259 98,567 1.00 1,336 1.08 9,833
1,088 0.28 824 59,504 98,623 1.00 1,280 1.04 9,839
1,488 0.38 1,237 67,259 98,651 1.00 1,252 1.01 9,842
1,888 0.49 1,650 68,869 98,660 1.00 1,243 1.01 9,843
2,288 0.59 2,066 70,832 98,663 1.00 1,240 1.00 9,843
2,688 0.69 2,491 72,117 98,664 1.00 1,239 1.00 9,843
3,088 0.79 2,920 73,150 98,665 1.00 1,238 1.00 9,844
3,488 0.90 3,313 74,534 98,666 1.00 1,237 1.00 9,844
3,888 1.00 3,524 76,458 98,667 1.00 1,236 1.00 9,845
4,288 1.10 3,604 77,401 98,667 1.00 1,236 1.00 9,845
4,688 1.21 3,604 77,401 98,667 1.00 1,236 1.00 9,845
5,088 1.31 3,604 77,401 98,667 1.00 1,236 1.00 9,845
5,488 1.41 3,604 77,401 98,667 1.00 1,236 1.00 9,845
5,888 1.51 3,604 77,401 98,667 1.00 1,236 1.00 9,845
6,288 1.62 3,604 77,401 98,667 1.00 1,236 1.00 9,845
6,688 1.72 3,604 77,401 98,667 1.00 1,236 1.00 9,845
7,088 1.82 3,604 77,401 98,667 1.00 1,236 1.00 9,845
7,488 1.93 3,604 77,401 98,667 1.00 1,236 1.00 9,845
7,888 2.03 3,604 77,401 98,667 1.00 1,236 1.00 9,845

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
1,284 0.25 288,086 32,650,685
2,568 0.50 285,435 23,103,510
3,852 0.75 285,036 18,157,427
5,136 1.00 285,036 18,157,427
6,420 1.25 285,036 18,157,427
7,704 1.50 285,036 18,157,427
8,988 1.75 285,036 18,157,427
10,272 2.00 285,036 18,157,427

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

Back to Top

Buffer Wait Statistics

ClassWaitsTotal Wait Time (s)Avg Time (ms)
data block 998 0 0
undo header 63 0 0
segment header 7 0 0
2nd level bmb 2 0 0
undo block 2 0 0

Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
RO-Multiple Object Reuse (fast object reuse) 450 450 0 50 1 27.20
TX-Transaction (index contention) 73 73 0 7 0 0.00
HW-Segment High Water Mark 6,351 6,350 1 6 0 0.00
TX-Transaction 42,533 42,541 0 4 0 0.00

Back to Wait Statistics
Back to Top

Undo Statistics

Back to Top

Undo Segment Summary

Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
1 11.80 44,868 3,537 7 15/73 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
22-Jul 03:03 640 2,338 3,537 7 73 0/0 0/0/0/0/0/0
22-Jul 02:53 703 1,788 2,934 6 63 0/0 0/0/0/0/0/0
22-Jul 02:43 17 459 2,334 3 53 0/0 0/0/0/0/0/0
22-Jul 02:33 388 1,625 1,734 7 43 0/0 0/0/0/0/0/0
22-Jul 02:23 551 2,304 1,249 6 35 0/0 0/0/0/0/0/0
22-Jul 02:13 8 21 648 3 25 0/0 0/0/0/0/0/0
22-Jul 02:03 1,176 3,690 48 7 15 0/0 0/0/0/0/0/0
22-Jul 01:53 662 3,550 158 5 17 0/0 0/0/0/0/0/0
22-Jul 01:43 962 2,649 2,361 3 53 0/0 0/0/0/0/0/0
22-Jul 01:33 1,388 5,534 1,761 5 43 0/0 0/0/0/0/0/0
22-Jul 01:23 1,670 5,656 1,160 6 33 0/0 0/0/0/0/0/0
22-Jul 01:13 753 3,279 559 3 23 0/0 0/0/0/0/0/0
22-Jul 01:03 860 3,242 0 5 15 0/0 0/0/0/0/0/0
22-Jul 00:53 549 2,153 0 7 15 0/0 0/0/0/0/0/0
22-Jul 00:43 11 23 0 2 15 0/0 0/0/0/0/0/0
22-Jul 00:33 477 2,143 0 6 15 0/0 0/0/0/0/0/0
22-Jul 00:23 592 2,281 0 5 15 0/0 0/0/0/0/0/0
22-Jul 00:13 19 516 0 2 15 0/0 0/0/0/0/0/0
22-Jul 00:03 374 1,617 0 6 15 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
ASM db client latch 7,279 0.00   0 0  
AWR Alerted Metric Element list 44,948 0.00   0 0  
Consistent RBA 29,874 0.00 0.00 0 0  
FAL request queue 218 0.00   0 0  
FAL subheap alocation 218 0.00   0 0  
FIB s.o chain latch 22 0.00   0 0  
FOB s.o list latch 975 0.00   0 0  
In memory undo latch 125,345 0.58 0.85 0 22,285 0.00
JS mem alloc latch 18 0.00   0 0  
JS queue access latch 18 0.00   0 0  
JS queue state obj latch 78,110 0.00   0 0  
JS slv state obj latch 12 0.00   0 0  
KMG MMAN ready and startup request latch 3,602 0.00   0 0  
KMG resize request state object freelist 8 0.00   0 0  
KTF sga latch 21 0.00   0 3,357 0.00
KWQMN job cache list latch 3 0.00   0 0  
MQL Tracking Latch 0     0 216 0.00
Memory Management Latch 80 0.00   0 3,602 0.00
OS process 678 0.00   0 0  
OS process allocation 4,075 0.00   0 0  
OS process: request allocation 353 0.00   0 0  
PL/SQL warning settings 9,026 0.00   0 0  
SQL memory manager latch 3 0.00   0 3,578 0.00
SQL memory manager workarea list latch 277,291 0.00 0.00 0 0  
Shared B-Tree 384 0.00   0 0  
active checkpoint queue latch 46,340 0.00   0 0  
active service list 22,805 0.03 0.67 0 6,040 0.00
alert log latch 2 0.00   0 0  
archive control 403 0.00   0 0  
archive process latch 4,016 0.02 0.00 0 0  
begin backup scn array 221 0.00   0 0  
buffer pool 24 0.00   0 0  
cache buffer handles 5,374 0.00   0 0  
cache buffers chains 2,913,946,806 0.00 0.04 0 2,670,114 0.00
cache buffers lru chain 248,781 0.03 0.05 0 3,107,128 0.02
cache table scan latch 0     0 130,377 0.00
channel handle pool latch 354 0.00   0 0  
channel operations parent latch 51,762 0.00 0.00 0 0  
checkpoint queue latch 1,635,440 0.00 0.00 0 83,396 0.01
client/application info 1,290 0.00   0 0  
commit callback allocation 6,722 0.01 0.00 0 0  
compile environment latch 193 0.00   0 0  
dictionary lookup 98 0.00   0 0  
direct msg latch 76,183 0.00   0 0  
dml lock allocation 202,810 0.14 0.02 0 0  
done queue latch 72,378 0.00 0.00 0 72,581 0.02
dummy allocation 365 0.00   0 0  
enqueue hash chains 505,795 0.02 0.01 0 2 0.00
enqueues 259,068 0.07 0.00 0 0  
event group latch 187 0.00   0 0  
file cache latch 1,178 0.00   0 0  
global KZLD latch for mem in SGA 157 0.00   0 0  
hash table column usage latch 2,375 0.00   0 165,839 0.00
hash table modification latch 339 0.00   0 0  
internal temp table object number allocation latc 42 0.00   0 0  
job workq parent latch 0     0 12 0.00
job_queue_processes parameter latch 186 0.00   0 0  
kks stats 20,966 0.00   0 0  
kmcptab latch 1 0.00   0 0  
kmcpvec latch 0     0 1 0.00
kokc descriptor allocation latch 34 0.00   0 0  
ksfv messages 148,549 0.00   0 0  
ksuosstats global area 726 0.00   0 0  
ktm global data 48 0.00   0 0  
kwqbsn:qsga 384 0.00   0 0  
lgwr LWN SCN 32,141 0.06 0.00 0 0  
library cache 1,148,708 0.17 0.00 0 54,125 0.07
library cache load lock 2,796 0.00   0 0  
library cache lock 3,167,476 0.01 0.00 0 77 0.00
library cache lock allocation 6,498 0.00   0 0  
library cache pin 371,416 0.07 0.00 0 0  
library cache pin allocation 4,918 0.00   0 0  
list of block allocation 2,421 0.00   0 0  
loader state object freelist 1,056 0.00   0 0  
longop free list parent 4,802 0.00   0 3,733 0.00
message pool operations parent latch 150 0.00   0 0  
messages 237,596 0.02 0.02 0 0  
mostly latch-free SCN 32,389 0.55 0.00 0 0  
msg queue latch 0     0 148,302 0.69
multiblock read objects 285,480 0.00 0.00 0 0  
ncodef allocation latch 173 0.00   0 0  
object queue header heap 437,678 0.00 0.00 0 42,957 0.01
object queue header operation 3,488,696 0.00 0.03 0 0  
object stats modification 2,844 0.00   0 0  
parallel query alloc buffer 1,432 0.00   0 0  
parameter list 36 0.00   0 0  
parameter table management 379 0.00   0 0  
post/wait queue 29,555 0.01 0.00 0 22,380 0.06
process allocation 353 0.57 1.50 0 187 0.00
process group creation 353 0.00   0 0  
qmn task queue latch 1,536 0.00   0 0  
redo allocation 140,172 0.09 0.02 0 448,503 0.22
redo copy 0     0 448,828 0.10
redo writing 144,193 0.01 0.00 0 0  
resmgr group change latch 229 0.00   0 0  
resmgr:active threads 320 0.00   0 0  
resmgr:actses change group 163 0.00   0 0  
resmgr:free threads list 317 0.00   0 0  
resmgr:resource group CPU method 142 0.70 0.00 0 0  
resmgr:schema config 3 0.00   0 0  
row cache objects 1,634,826 0.09 0.00 0 2,456 0.04
sequence cache 1,066 0.00   0 0  
session allocation 96,243 0.42 0.01 0 0  
session idle bit 527,669 0.01 0.00 0 0  
session queue latch 76,183 0.00   0 0  
session state list latch 358 0.00   0 0  
session switching 1,003 0.00   0 0  
session timer 3,618 0.00   0 0  
shared pool 880,334 0.24 0.03 0 0  
shared pool sim alloc 187 0.00   0 0  
shared pool simulator 42,075 0.05 0.00 0 0  
simulator hash latch 84,525,461 0.00 0.00 0 0  
simulator lru latch 84,131 0.07 0.02 0 84,243,280 0.00
slave class 24 0.00   0 0  
slave class create 96 3.13 1.00 0 0  
sort extent pool 20,911 0.31 0.00 0 0  
state object free list 6 0.00   0 0  
statistics aggregation 1,680 0.00   0 0  
temp lob duration state obj allocation 2 0.00   0 0  
temporary table state object allocation 17 0.00   0 0  
threshold alerts latch 676 0.00   0 0  
transaction allocation 1,045,431 0.00   0 0  
transaction branch allocation 3,618 0.00   0 0  
undo global data 211,635 0.01 0.00 0 0  
user lock 598 2.01 0.00 0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
cache buffers chains 2,913,946,806 7,954 282 7,697 0 0 0
shared pool 880,334 2,145 54 2,094 0 0 0
library cache 1,148,708 1,983 4 1,979 0 0 0
row cache objects 1,634,826 1,479 2 1,477 0 0 0
In memory undo latch 125,345 722 612 111 0 0 0
session allocation 96,243 401 4 397 0 0 0
dml lock allocation 202,810 276 5 271 0 0 0
object queue header operation 3,488,696 173 5 168 0 0 0
redo allocation 140,172 129 3 126 0 0 0
enqueue hash chains 505,795 126 1 125 0 0 0
cache buffers lru chain 248,781 79 4 75 0 0 0
simulator lru latch 84,131 55 1 54 0 0 0
messages 237,596 46 1 45 0 0 0
active service list 22,805 6 4 4 0 0 0
slave class create 96 3 3 0 0 0 0
process allocation 353 2 3 0 0 0 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch NameWhereNoWait Misses SleepsWaiter Sleeps
In memory undo latch ktiFlush: child 0 369 280
In memory undo latch kticmt: child 0 271 130
In memory undo latch ktichg: child 0 13 234
In memory undo latch kturbk 0 2 9
active service list kswslogon: session logout 0 6 6
cache buffers chains kcbgtcr: kslbegin excl 0 408 392
cache buffers chains kcbgtcr: fast path 0 121 10
cache buffers chains kcbzib: multi-block read: nowait 0 50 0
cache buffers chains kcbgcur: kslbegin 0 42 47
cache buffers chains kcbzgb: scan from tail. nowait 0 40 0
cache buffers chains kcbget: pin buffer 0 31 5
cache buffers chains kcbnew: new latch again 0 24 1
cache buffers chains kcbrls: kslbegin 0 10 197
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 8 41
cache buffers chains kcbbic1 0 6 2
cache buffers chains kcbbxsv 0 6 0
cache buffers chains kcbnlc 0 6 7
cache buffers chains kcbso1: set no access 0 5 9
cache buffers chains kcbget: exchange 0 1 5
cache buffers chains kcbzwb 0 1 0
cache buffers lru chain kcbzgws 0 4 0
dml lock allocation ktaiam 0 4 4
dml lock allocation ktaidm 0 1 1
enqueue hash chains ksqgtl3 0 1 0
library cache kglpndl: child: after processing 0 3 0
library cache kglobld 0 1 1
messages ksaamb: after wakeup 0 1 0
object queue header operation kcbo_switch_cq 0 7 9
object queue header operation kcbw_unlink_q 0 6 1
object queue header operation kcbw_link_q 0 2 0
object queue header operation kcbo_swap_buf_bg 0 1 18
process allocation ksucrp 0 2 0
process allocation ksuapc 0 1 3
redo allocation kcrfw_redo_gen: redo allocation 1 0 3 0
session allocation ksudlp 0 4 1
shared pool kghalo 0 44 45
shared pool kghfrunp: alloc: cursor dur 0 5 0
shared pool kghfre 0 3 5
shared pool kghalp 0 1 1
shared pool kghfrunp: clatch: nowait 0 1 0
shared pool kghfrunp: clatch: wait 0 1 0
shared pool kghupr1 0 1 2
simulator lru latch kcbs_simulate: simulate set 0 1 0
slave class create ksvcreate 0 3 0

Back to Latch Statistics
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Segment Statistics

Back to Top

Segments by Logical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
SYS SYSTEM I_OBJ2   INDEX 1,130,461,584 76.02
SYS SYSTEM OBJ$   TABLE 275,619,312 18.53
SYS SYSTEM I_OBJ1   INDEX 24,647,840 1.66
SYS SYSTEM I_OBJ#   INDEX 6,353,968 0.43
PATROL PATROL_DATA P$OBJ_EXCLUSION   TABLE 5,713,984 0.38

Back to Segment Statistics
Back to Top

Segments by Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
PRECISE_PMDB PRECISE_PMDB_TAB_SMALL PW_UDSX_STATEMENT_TEXT   TABLE 293,241 22.60
PRECISE_PMDB PRECISE_PMDB_TAB_SMALL PW_UDEO_EXPLAIN_OPER   TABLE 263,278 20.29
PRECISE_PMDB PRECISE_PMDB_TAB_SMALL PW_UDSM_STATEMENTS   TABLE 71,879 5.54
SYS SYSTEM OBJ$   TABLE 69,160 5.33
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_UDEJ_IX1   INDEX 62,379 4.81

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST   INDEX 4,805 93.59
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_UDSX_01   INDEX 205 3.99
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_PWJG_PK   INDEX 27 0.53
SYS SYSAUX I_WRI$_OPTSTAT_H_ST   INDEX 27 0.53
SYS SYSAUX I_WRI$_OPTSTAT_TAB_OBJ#_ST   INDEX 14 0.27

Back to Segment Statistics
Back to Top

Segments by ITL Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeITL Waits% of Capture
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_UDOU_01_D 2013_S1107 INDEX SUBPARTITION 1 100.00

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_PWJH_PWJG_ID 2_16_00_00 INDEX PARTITION 132 43.14
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_PWJH_PWSE_ID 2_16_00_00 INDEX PARTITION 108 35.29
PRECISE_PMDB PRECISE_PMDB_TAB_SMALL PW_PWJH_JOB_HISTORY 2_16_00_00 TABLE PARTITION 33 10.78
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IW_PWJH_ID 2_16_00_00 INDEX PARTITION 25 8.17
PRECISE_PMDB PRECISE_PMDB_IND_SMALL IS_UDCU_ID   INDEX 6 1.96

Back to Segment Statistics
Back to Top

Dictionary Cache Stats

CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control 191 0.00 0   6 1
dc_constraints 2,229 33.33 0   2,229 1
dc_database_links 320 0.00 0   0 1
dc_files 2,068 0.00 0   0 11
dc_global_oids 8,097 0.07 0   0 113
dc_histogram_data 13,694 19.44 0   3,272 13,289
dc_histogram_defs 31,635 22.26 0   5,618 11,359
dc_object_grants 19,419 0.03 0   0 24
dc_object_ids 42,333 0.99 0   34 2,759
dc_objects 43,809 11.13 0   194 8,703
dc_profiles 169 0.00 0   0 1
dc_rollback_segments 1,242 0.00 0   0 22
dc_segments 167,217 68.11 0   560 167,354
dc_sequences 11 0.00 0   11 4
dc_table_scns 27 100.00 0   0 0
dc_tablespace_quotas 492 0.00 0   492 3
dc_tablespaces 66,141 0.00 0   0 15
dc_usernames 8,517 0.00 0   0 42
dc_users 107,305 0.00 0   0 64
outstanding_alerts 234 0.00 0   0 13


Back to Top

Library Cache Activity

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
BODY 342 0.00 20,215 0.00 0 0
CLUSTER 90 0.00 112 0.00 0 0
INDEX 2,219 11.45 24,055 2.14 58 0
SQL AREA 85,684 6.70 1,268,651 1.29 3,798 356
TABLE/PROCEDURE 5,930 3.17 114,988 3.68 3,637 0
TRIGGER 863 0.00 14,266 0.00 0 0


Back to Top

Memory Statistics

Back to Top

Process Memory Summary

CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
B Other 60.18   1.25 2.60 11 11 48 48
  Freeable 22.88 0.00 1.04 0.50 3   22 22
  SQL 0.58 0.05 0.02 0.03 0 28 27 21
  PL/SQL 0.44 0.32 0.01 0.01 0 0 46 46
E Freeable 103.44 0.00 3.04 11.94 71   34 34
  Other 75.86   1.10 2.20 11 11 69 69
  SQL 59.65 58.84 1.24 8.49 59 114 48 43
  PL/SQL 0.59 0.42 0.01 0.01 0 0 67 67

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers 922,746,880 1,258,291,200
Fixed Size 2,103,896  
Redo Buffers 14,671,872  
Variable Size 4,445,963,688 4,110,419,368

Back to Memory Statistics
Back to Top

SGA breakdown difference

PoolNameBegin MBEnd MB% Diff
java free memory 16.00 16.00 0.00
large PX msg pool 1.03 1.03 0.00
large free memory 14.97 14.97 0.00
shared KQR M PO 91.73 88.81 -3.19
shared free memory 2,311.91 3,163.30 36.83
shared mvobj part des 1,114.72   -100.00
shared obj stat memo 73.21 74.03 1.13
shared partitioning d 120.85 65.74 -45.60
shared sql area 129.11 107.08 -17.06
  buffer_cache 880.00 1,200.00 36.36
  fixed_sga 2.01 2.01 0.00
  log_buffer 13.99 13.99 0.00

Back to Memory Statistics
Back to Top

Streams Statistics

Back to Top

Streams CPU/IO Usage

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Queues

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Subscribers

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Rule Set

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Resource Limit Stats

No data exists for this section of the report.


Back to Top

init.ora Parameters

Parameter NameBegin valueEnd value (if different)
audit_file_dest /oracle/product/admin/precisedb/adump   
background_dump_dest /oracle/product/admin/precisedb/bdump   
backup_tape_io_slaves TRUE   
compatible 10.2.0.5.0   
control_files /oradata/PRECISEDB/control01.ctl, /oradata/PRECISEDB/control02.ctl, /oradata/PRECISEDB/control03.ctl   
core_dump_dest /oracle/product/admin/precisedb/cdump   
db_block_size 8192   
db_create_file_dest /oradata   
db_domain      
db_file_multiblock_read_count 16   
db_name precised   
db_recovery_file_dest /oradata/recovery   
db_recovery_file_dest_size 2147483648   
db_unique_name precisedb   
dbwr_io_slaves 10   
dispatchers (PROTOCOL=TCP) (SERVICE=precisedbXDB)   
job_queue_processes 10   
log_archive_dest_1 LOCATION=/oradata/oraarch   
open_cursors 2100   
pga_aggregate_target 536870912   
processes 2000   
remote_login_passwordfile EXCLUSIVE   
sga_max_size 5385486336   
sga_target 5385486336   
undo_management AUTO   
undo_tablespace UNDOTBS1   
user_dump_dest /oracle/product/admin/precisedb/udump   


Back to Top

End of Report