1、进入数据库 sqlplus / as sysdba

2、查看用户 show parameter db_name

3、开始压测后执行 exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();   

4、 结束压测后执行 exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

5、输入命令 @?/rdbms/admin/awrrpt

 备注：4和5步骤中不执行，后面默认产生系统规定的时间节点（默认人一小时生成一次报告）

6、oracle中如何生成awr报告_sql      

      输入回车  默认是html格式

 

 7、 oracle中如何生成awr报告_sql_02 

 

     输入1，查询最近一天的 

 

8、oracle中如何生成awr报告_ios_03

       输入开始节点和结束节点

 

9、输入想要生成的报告名称，以html格式结尾 

 

10、生成的awr默认在登陆数据库的路径（试验机：C:\Users\wangxh.PANACRO）

 

WORKLOAD REPOSITORY report for

 

DB Name DB Id Instance Inst num Startup Time Release RAC
WXH 142723844 wxh 1 31-8月 -18 09:08 11.2.0.1.0 NO

 

 

Host Name Platform CPUs Cores Sockets Memory (GB)
PANACEABJ-003 Microsoft Windows x86 64-bit 4 2 1 7.48

 

  Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 112 01-9月 -18 10:00:55 31 1.5
End Snap: 117 01-9月 -18 15:00:49 29 1.8
Elapsed:   299.90 (mins)    
DB Time:   24.16 (mins)    

 

Report Summary

Cache Sizes

 

  Begin End    
Buffer Cache: 1,376M 1,376M Std Block Size: 8K
Shared Pool Size: 400M 400M Log Buffer: 16,584K

Load Profile

 

  Per Second Per Transaction Per Exec Per Call
DB Time(s): 0.1 0.6 0.00 0.00
DB CPU(s): 0.1 0.6 0.00 0.00
Redo size: 8,634.2 68,897.4    
Logical reads: 168.1 1,341.7    
Block changes: 56.7 452.5    
Physical reads: 0.4 3.1    
Physical writes: 1.1 8.8    
User calls: 199.7 1,593.4    
Parses: 182.5 1,456.0    
Hard parses: 0.1 0.9    
W/A MB processed: 0.0 0.3    
Logons: 0.1 0.5    
Executes: 187.2 1,494.1    
Rollbacks: 0.0 0.0    
Transactions: 0.1      

Instance Efficiency Percentages (Target 100%)

 

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.77 In-memory Sort %: 100.00
Library Hit %: 99.98 Soft Parse %: 99.94
Execute to Parse %: 2.55 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 97.62 % Non-Parse CPU: 94.43

Shared Pool Statistics

 

  Begin End
Memory Usage %: 73.36 81.67
% SQL with executions>1: 77.11 92.73
% Memory for SQL w/exec>1: 70.94 86.50

 

Top 5 Timed Foreground Events

 

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU   1,355   93.47  
SQL*Net message to client 1,797,405 3 0 0.20 Network
Disk file operations I/O 631 1 2 0.08 User I/O
log file sync 578 1 2 0.06 Commit
db file scattered read 222 0 1 0.02 User I/O

Host CPU (CPUs: 4 Cores: 2 Sockets: 1)

 

Load Average Begin Load Average End %User %System %WIO %Idle
    9.6 17.9   72.5

Instance CPU

 

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
1.9 6.9 0.0

Memory Statistics

 

  Begin End
Host Mem (MB): 7,659.2 7,659.2
SGA use (MB): 1,840.0 1,840.0
PGA use (MB): 71.4 74.3
% Host Mem used for SGA+PGA: 24.96 24.99

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

  • Total time in database user-calls (DB Time): 1449.4s
  • 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
DB CPU 1,354.76 93.47
sql execute elapsed time 942.39 65.02
PL/SQL execution elapsed time 529.23 36.51
parse time elapsed 95.33 6.58
hard parse elapsed time 17.82 1.23
PL/SQL compilation elapsed time 2.25 0.16
hard parse (sharing criteria) elapsed time 2.21 0.15
connection management call elapsed time 0.56 0.04
failed parse elapsed time 0.39 0.03
repeated bind elapsed time 0.13 0.01
sequence load elapsed time 0.00 0.00
hard parse (bind mismatch) elapsed time 0.00 0.00
DB time 1,449.39  
background elapsed time 97.40  
background cpu time 13.38  

Back to Wait Events Statistics 
Back to Top

Operating System Statistics

  • *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
AVG_BUSY_TIME 494,705  
AVG_IDLE_TIME 1,303,789  
AVG_SYS_TIME 322,199  
AVG_USER_TIME 172,047  
BUSY_TIME 1,980,616  
IDLE_TIME 5,217,027  
SYS_TIME 1,290,643  
USER_TIME 689,973  
VM_IN_BYTES -7,363,472,657,615,470,592  
VM_OUT_BYTES 50,841,417,351,663,616  
PHYSICAL_MEMORY_BYTES 8,031,227,904  
NUM_CPUS 4  
NUM_CPU_CORES 2  
NUM_CPU_SOCKETS 1  

Back to Wait Events Statistics 
Back to Top

Operating System Statistics - Detail

Snap Time Load %busy %user %sys %idle %iowait
01-9月 10:00:55 0.00          
01-9月 11:00:06 0.00 23.36 7.01 16.35 76.64 0.00
01-9月 12:00:17 0.00 20.51 5.61 14.89 79.49 0.00
01-9月 13:00:27 0.00 28.49 9.86 18.62 71.51 0.00
01-9月 14:00:39 0.00 43.01 18.17 24.85 56.99 0.00
01-9月 15:00:49 0.00 22.15 7.23 14.92 77.85 0.00

Back to Wait Events Statistics 
Back to Top

Foreground Wait Class

  • 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 93.8% of Total DB time 1,449.39 (s)
  • Total FG Wait Time: 5.44 (s) DB CPU time: 1,354.76 (s)
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time
DB CPU     1,355   93.47
Network 1,797,547 0 3 0 0.20
User I/O 1,081 0 1 1 0.10
Commit 578 0 1 2 0.06
Application 121 0 0 1 0.01
Concurrency 1,419 0 0 0 0.01
Other 53,517 100 0 0 0.00
System I/O 77 0 0 0 0.00
Configuration 3 0 0 3 0.00

Back to Wait Events Statistics 
Back to Top

Foreground Wait Events

  • 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
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
SQL*Net message to client 1,797,405 0 3 0 797.08 0.20
Disk file operations I/O 631 0 1 2 0.28 0.08
log file sync 578 0 1 2 0.26 0.06
db file scattered read 222 0 0 1 0.10 0.02
db file sequential read 200 0 0 0 0.09 0.01
SQL*Net break/reset to client 110 0 0 1 0.05 0.01
library cache: mutex X 417 0 0 0 0.18 0.00
asynch descriptor resize 53,505 100 0 0 23.73 0.00
control file sequential read 77 0 0 0 0.03 0.00
library cache pin 995 0 0 0 0.44 0.00
enq: RO - fast object reuse 11 0 0 2 0.00 0.00
SQL*Net more data to client 138 0 0 0 0.06 0.00
local write wait 12 0 0 1 0.01 0.00
log file switch (private strand flush incomplete) 2 0 0 3 0.00 0.00
latch: shared pool 6 0 0 1 0.00 0.00
direct path write temp 11 0 0 0 0.00 0.00
log file switch completion 1 0 0 3 0.00 0.00
latch: row cache objects 1 0 0 3 0.00 0.00
reliable message 11 0 0 0 0.00 0.00
SQL*Net message from client 1,797,405 0 53,250 30 797.08  
jobq slave wait 37,693 100 18,818 499 16.72  
pipe get 4,377,380 0 6,962 2 1,941.19  
PL/SQL lock timer 1 100 1 1000 0.00  

Back to Wait Events Statistics 
Back to Top

Background Wait Events

  • 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
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % bg time
os thread startup 681 0 27 40 0.30 27.92
control file sequential read 19,875 0 16 1 8.81 16.63
control file parallel write 6,537 0 16 2 2.90 16.19
log file parallel write 5,847 0 6 1 2.59 6.37
db file parallel write 5,764 0 5 1 2.56 4.68
Disk file operations I/O 323 0 1 2 0.14 0.61
db file sequential read 392 0 0 0 0.17 0.13
db file scattered read 91 0 0 1 0.04 0.07
rdbms ipc reply 4 0 0 7 0.00 0.03
ADR block file read 62 0 0 0 0.03 0.03
ADR file lock 30 0 0 0 0.01 0.01
direct path write 22 0 0 0 0.01 0.01
ADR block file write 25 0 0 0 0.01 0.01
LGWR wait for redo copy 105 0 0 0 0.05 0.01
asynch descriptor resize 1,037 100 0 0 0.46 0.01
log file sync 10 0 0 0 0.00 0.00
log file sequential read 10 0 0 0 0.00 0.00
latch free 196 0 0 0 0.09 0.00
direct path read 17 0 0 0 0.01 0.00
log file single write 10 0 0 0 0.00 0.00
rdbms ipc message 128,507 96 215,839 1680 56.99  
Streams AQ: waiting for time management or cleanup tasks 2 50 98,205 49102603 0.00  
DIAG idle wait 35,982 100 35,984 1000 15.96  
smon timer 82 73 18,044 220053 0.04  
shared server idle wait 600 100 18,004 30006 0.27  
dispatcher timer 300 100 18,002 60005 0.13  
pmon timer 6,636 90 17,993 2711 2.94  
Space Manager: slave idle wait 3,646 98 17,985 4933 1.62  
Streams AQ: qmn slave idle wait 652 0 17,980 27577 0.29  
Streams AQ: qmn coordinator idle wait 1,115 58 17,980 16126 0.49  
SQL*Net message from client 473 0 1 1 0.21  
class slave wait 83 0 0 0 0.04  

Back to Wait Events Statistics 
Back to Top

Wait Event Histogram

  • 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
Event Total Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
ADR block file read 62 95.2     4.8        
ADR block file write 25 88.0 12.0            
ADR file lock 30 86.7 13.3            
Data file init write 3 100.0              
Disk file operations I/O 954 .2 82.0 16.4 1.3 .1 .1    
LGWR wait for redo copy 105 98.1 1.0 1.0          
SQL*Net break/reset to client 110 68.2 25.5 6.4          
SQL*Net message to client 1797.8K 100.0 .0            
SQL*Net more data from client 4 100.0              
SQL*Net more data to client 138 100.0              
asynch descriptor resize 54.5K 100.0              
control file parallel write 6537 55.5 .7 .5 42.7 .2 .1 .4  
control file sequential read 20K 90.5 1.2 .4 7.7 .1 .0 .1  
db file parallel write 5764 85.8 4.0 1.5 8.4 .1 .1 .0  
db file scattered read 313 77.3 12.1 8.9 1.0 .6      
db file sequential read 592 97.6 .7 .2 1.5        
db file single write 3 100.0              
direct path read 17 100.0              
direct path write 22 90.9 4.5 4.5          
direct path write temp 15 93.3 6.7            
enq: JS - queue lock 1 100.0              
enq: RO - fast object reuse 11 63.6 9.1 9.1 9.1 9.1      
latch free 197 100.0              
latch: row cache objects 1     100.0          
latch: shared pool 6 83.3   16.7          
library cache pin 995 100.0              
library cache: mutex X 417 98.6 .7 .7          
local write wait 12 83.3 8.3 8.3          
log file parallel write 5847 80.0 2.3 1.6 15.6 .2 .1 .2  
log file sequential read 10 100.0              
log file single write 10 100.0              
log file switch (private strand flush incomplete) 2     100.0          
log file switch completion 1     100.0          
log file sync 588 74.8 6.1 2.7 15.6   .2 .5  
os thread startup 681           26.6 73.4  
rdbms ipc reply 4 25.0 25.0 25.0     25.0    
reliable message 14 100.0              
utl_file I/O 5 100.0              
DIAG idle wait 36K             100.0 .0
PL/SQL lock timer 1             100.0  
SQL*Net message from client 1797.9K 26.7 70.3 2.3 .4 .1 .0 .2 .0
Space Manager: slave idle wait 3646 .4       .0   .4 99.1
Streams AQ: qmn coordinator idle wait 1115 42.4             57.6
Streams AQ: qmn slave idle wait 652 1.5             98.5
Streams AQ: waiting for time management or cleanup tasks 2             50.0 50.0
class slave wait 83 96.4 2.4 1.2          
dispatcher timer 300               100.0
jobq slave wait 37.7K .0 .0   .0 .1 .0 99.9  
pipe get 4377.3K 66.3 31.4 2.0 .2 .0 .0 .0 .0
pmon timer 6636 1.4 3.5 2.9 1.3 .2 .2 .1 90.4
rdbms ipc message 128.5K 1.7 .2 .3 .3 37.2 .3 15.0 44.9
shared server idle wait 600               100.0
smon timer 82               100.0

Back to Wait Events Statistics 
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

  • Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
  • Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
  • % of Total Waits: total waits for all wait classes, including Idle
  • % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
  • Ordered by Event (only non-idle events are displayed)
    % of Total Waits
Event Waits 64ms to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s
control file parallel write 23 99.6 .2 .1 .1        
control file sequential read 21 99.9 .1 .0 .0        
db file parallel write 2 100.0     .0 .0      
log file parallel write 9 99.8 .1 .1 .0        
log file sync 3 99.5   .5          
os thread startup 500 26.6 72.1 1.2 .1        

Back to Wait Events Statistics 
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

No data exists for this section of the report.

Back to Wait Events Statistics 
Back to Top

Wait Event Histogram Detail (4 min to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics 
Back to Top

Service Statistics

  • ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads (K) Logical Reads (K)
WXH 1,326 1,250 0 1,800
SYS$USERS 124 105 5 901
SYS$BACKGROUND 0 0 2 324
WXHXDB 0 0 0 0

Back to Wait Events Statistics 
Back to Top

Service Wait Class Stats

  • 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 Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
WXH 295 0 1416 0 0 0 1797531 3
SYS$USERS 786 1 3 0 0 0 16 0
SYS$BACKGROUND 856 1 681 27 0 0 0 0

Back to Wait Events Statistics 
Back to Top

 

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

  • 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 - Elapsed Time as a percentage of Total DB time
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 37.6% of Total DB Time (s): 1,449
  • Captured PL/SQL account for 82.4% of Total DB Time (s): 1,449
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
469.56 7 67.08 32.40 105.42 0.00 0mf6d1n1zzm0z PL/SQL Developer begin -- Call the procedure oc...
355.26 1 355.26 24.51 104.95 0.00 977mq4aw9jc36 PL/SQL Developer INSERT INTO 理化检查 SELECT SUBJID...
347.51 1,431,072 0.00 23.98 99.66 0.00 du7nyv586fquh PL/SQL Developer declare runtime_info sys.dbms_...
326.43 1,431,218 0.00 22.52 104.15 0.00 g0unxwd406yu0 PL/SQL Developer declare ret binary_integer; be...
95.84 1 95.84 6.61 107.26 0.00 25986tpfa5wc3 PL/SQL Developer INSERT INTO 理化检查 SELECT SUBJID...
34.46 1 34.46 2.38 93.54 0.82 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
15.73 1 15.73 1.09 107.12 0.01 04p6yvxfsv7cy PL/SQL Developer INSERT INTO 体格检查 SELECT SUBJID...
15.71 131 0.12 1.08 93.77 0.00 c0j6cx9kzjf7g DBMS_SCHEDULER SELECT EXTRACTVALUE(VALUE(T), ...
14.75 176,608 0.00 1.02 100.45 0.05 dr4mnnwqwm3rq PL/SQL Developer insert into shuju1 (SUBJID, V...
13.65 153,748 0.00 0.94 106.62 0.04 845hn917wsu63 PL/SQL Developer insert into shuju1 (SUBJID, V...

Back to SQL Statistics 
Back to Top

SQL ordered by CPU Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • %Total - CPU Time as a percentage of Total DB CPU
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 42.0% of Total CPU Time (s): 1,355
  • Captured PL/SQL account for 90.7% of Total CPU Time (s): 1,355
CPU Time (s) Executions CPU per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
494.99 7 70.71 36.54 469.56 105.42 0.00 0mf6d1n1zzm0z PL/SQL Developer begin -- Call the procedure oc...
372.86 1 372.86 27.52 355.26 104.95 0.00 977mq4aw9jc36 PL/SQL Developer INSERT INTO 理化检查 SELECT SUBJID...
346.32 1,431,072 0.00 25.56 347.51 99.66 0.00 du7nyv586fquh PL/SQL Developer declare runtime_info sys.dbms_...
339.97 1,431,218 0.00 25.09 326.43 104.15 0.00 g0unxwd406yu0 PL/SQL Developer declare ret binary_integer; be...
102.80 1 102.80 7.59 95.84 107.26 0.00 25986tpfa5wc3 PL/SQL Developer INSERT INTO 理化检查 SELECT SUBJID...
32.23 1 32.23 2.38 34.46 93.54 0.82 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
16.85 1 16.85 1.24 15.73 107.12 0.01 04p6yvxfsv7cy PL/SQL Developer INSERT INTO 体格检查 SELECT SUBJID...
14.82 176,608 0.00 1.09 14.75 100.45 0.05 dr4mnnwqwm3rq PL/SQL Developer insert into shuju1 (SUBJID, V...
14.73 131 0.11 1.09 15.71 93.77 0.00 c0j6cx9kzjf7g DBMS_SCHEDULER SELECT EXTRACTVALUE(VALUE(T), ...
14.55 153,748 0.00 1.07 13.65 106.62 0.04 845hn917wsu63 PL/SQL Developer insert into shuju1 (SUBJID, V...

Back to SQL Statistics 
Back to Top

SQL ordered by User I/O Wait Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • %Total - User I/O Time as a percentage of Total User I/O Wait time
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 46.8% of Total User I/O Wait Time (s): 2
  • Captured PL/SQL account for 58.8% of Total User I/O Wait Time (s): 2
User I/O Time (s) Executions UIO per Exec (s) %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
0.66 299 0.00 29.79 6.29 90.01 10.57 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
0.48 299 0.00 21.68 0.51 97.17 94.18 7mdacxfm37utk EM_PING SELECT COUNT(*) FROM MGMT_FAIL...
0.28 1 0.28 12.68 34.46 93.54 0.82 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
0.18 1 0.18 7.89 4.05 91.25 4.35 05s9358mm6vrr   begin dbms_feature_usage_inter...
0.17 1 0.17 7.48 0.21 29.79 79.70 2gjv8dam6m03g DBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
0.12 268 0.00 5.51 0.70 87.33 17.66 3am9cfkvx7gq1   CALL MGMT_ADMIN_DATA.EVALUATE_...
0.07 50 0.00 3.31 0.08 178.43 93.75 459f3z9u4fb3u   select value$ from props$ wher...
0.06 12 0.01 2.85 0.11 55.15 56.12 7mgr3uwydqq8j   select decode(open_mode, 'MOU...
0.06 34 0.00 2.64 1.21 99.55 4.88 32bph60y97zdc PL/SQL Developer SELECT DISTINCT DESCRIPTION, F...
0.03 5 0.01 1.20 0.21 65.84 12.56 gjm43un5cy843   SELECT SUM(USED), SUM(TOTAL) F...
0.02 129 0.00 1.04 0.03 55.01 82.19 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...

Back to SQL Statistics 
Back to Top

SQL ordered by Gets

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • %Total - Buffer Gets as a percentage of Total Buffer Gets
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Total Buffer Gets: 3,025,488
  • Captured SQL account for 79.0% of Total
Buffer Gets Executions Gets per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
723,020 176,608 4.09 23.90 14.75 100.45 0.05 dr4mnnwqwm3rq PL/SQL Developer insert into shuju1 (SUBJID, V...
629,883 153,748 4.10 20.82 13.65 106.62 0.04 845hn917wsu63 PL/SQL Developer insert into shuju1 (SUBJID, V...
623,390 1 623,390.00 20.60 34.46 93.54 0.82 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
167,625 1 167,625.00 5.54 4.05 91.25 4.35 05s9358mm6vrr   begin dbms_feature_usage_inter...
161,400 299 539.80 5.33 6.29 90.01 10.57 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
149,474 2,054 72.77 4.94 0.29 101.89 0.32 cvhk2j2gymzhd DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STA...
98,063 2 49,031.50 3.24 3.87 95.13 0.11 fqmpmkfr6pqyk PlSqlDev.exe select s.synonym_name object_n...
78,847 1 78,847.00 2.61 5.20 93.67 0.12 2tr12b1b8uj71 DBMS_SCHEDULER MERGE /*+ dynamic_sampling(ST ...
71,345 2,663 26.79 2.36 1.03 99.98 0.00 43ruajmwv9fus   insert into histgrm$(obj#, int...
59,115 16,240 3.64 1.95 0.43 97.39 0.00 cm5vu20fhtnq1   select /*+ connect_by_filterin...
55,539 268 207.24 1.84 0.70 87.33 17.66 3am9cfkvx7gq1   CALL MGMT_ADMIN_DATA.EVALUATE_...
50,115 4,098 12.23 1.66 0.16 95.55 0.00 6qg99cfg26kwb DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(P...
48,160 7 6,880.00 1.59 469.56 105.42 0.00 0mf6d1n1zzm0z PL/SQL Developer begin -- Call the procedure oc...
47,262 34 1,390.06 1.56 1.21 99.55 4.88 32bph60y97zdc PL/SQL Developer SELECT DISTINCT DESCRIPTION, F...
46,803 15,601 3.00 1.55 0.23 106.95 0.67 7sx5p1ug5ag12 DBMS_SCHEDULER SELECT SPARE4 FROM SYS.OPTSTAT...
41,438 5 8,287.60 1.37 0.21 65.84 12.56 gjm43un5cy843   SELECT SUM(USED), SUM(TOTAL) F...
35,502 801 44.32 1.17 0.76 119.42 0.00 4y1y43113gv8f   delete from histgrm$ where obj...

Back to SQL Statistics 
Back to Top

SQL ordered by Reads

  • %Total - Physical Reads as a percentage of Total Disk Reads
  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Total Disk Reads: 6,899
  • Captured SQL account for 53.0% of Total
Physical Reads Executions Reads per Exec %Total Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
4,995 1 4,995.00 72.40 34.46 93.54 0.82 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
3,383 1 3,383.00 49.04 0.21 29.79 79.70 2gjv8dam6m03g DBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
1,718 1 1,718.00 24.90 4.05 91.25 4.35 05s9358mm6vrr   begin dbms_feature_usage_inter...
69 129 0.53 1.00 0.03 55.01 82.19 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
41 140 0.29 0.59 0.02 0.00 72.17 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
34 1 34.00 0.49 0.10 96.29 12.70 ff5spha9pjuyu SQL*Plus select * from table(dbms_xplan...
31 1 31.00 0.45 0.40 96.72 1.81 f4r4y82fnaqgy   BEGIN ctxsys.drifeat.dr$featur...
29 311 0.09 0.42 0.02 169.41 51.32 8swypbbr0m372   select order#, columns, types ...
19 1 19.00 0.28 0.54 94.99 1.13 3h1rjtcff3wy1   BEGIN ordsys.CARTRIDGE.dbms_fe...
16 316 0.05 0.23 0.03 57.81 22.36 3ktacv9r56b51   select owner#, name, namespace...

Back to SQL Statistics 
Back to Top

SQL ordered by Physical Reads (UnOptimized)

  • UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs
  • %Opt - Optimized Reads as percentage of SQL Read Requests
  • %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
  • Total Physical Read Requests: 909
  • Captured SQL account for 46.9% of Total
  • Total UnOptimized Read Requests: 909
  • Captured SQL account for 46.9% of Total
  • Total Optimized Read Requests: 1
  • Captured SQL account for 0.0% of Total
UnOptimized Read Reqs Physical Read Reqs Executions UnOptimized Reqs per Exec %Opt %Total SQL Id SQL Module SQL Text
728 728 1 728.00 0.00 80.09 05s9358mm6vrr   begin dbms_feature_usage_inter...
332 332 1 332.00 0.00 36.52 b6usrg82hwsa3 DBMS_SCHEDULER call dbms_stats.gather_databas...
120 120 264 0.45 0.00 13.20 49s332uhbnsma   declare vsn varchar2(20); beg...
106 106 1 106.00 0.00 11.66 2gjv8dam6m03g DBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
84 84 12 7.00 0.00 9.24 7mgr3uwydqq8j   select decode(open_mode, 'MOU...
70 70 299 0.23 0.00 7.70 6gvch1xu9ca3g   DECLARE job BINARY_INTEGER := ...
69 69 129 0.53 0.00 7.59 cvn54b7yz0s8u   select /*+ index(idl_ub1$ i_id...
67 67 268 0.25 0.00 7.37 3am9cfkvx7gq1   CALL MGMT_ADMIN_DATA.EVALUATE_...
41 41 140 0.29 0.00 4.51 39m4sx9k63ba2   select /*+ index(idl_ub2$ i_id...
34 34 1 34.00 0.00 3.74 ff5spha9pjuyu SQL*Plus select * from table(dbms_xplan...
30 30 5 6.00 0.00 3.30 gjm43un5cy843   SELECT SUM(USED), SUM(TOTAL) F...
24 24 8 3.00 0.00 2.64 aukf94xzh2p3g   declare policy varchar2(512);...
24 24 1 24.00 0.00 2.64 f4r4y82fnaqgy   BEGIN ctxsys.drifeat.dr$featur...
15 15 1 15.00 0.00 1.65 3h1rjtcff3wy1   BEGIN ordsys.CARTRIDGE.dbms_fe...
15 15 1 15.00 0.00 1.65 fa16465c7pqmd PL/SQL Developer select table_name, constraint_...
14 14 142 0.10 0.00 1.54 c6awqs517jpj0   select /*+ index(idl_char$ i_i...
13 13 129 0.10 0.00 1.43 ga9j9xk5cy9s0   select /*+ index(idl_sb4$ i_id...

Back to SQL Statistics 
Back to Top

SQL ordered by Executions

  • %CPU - CPU Time as a percentage of Elapsed Time
  • %IO - User I/O Time as a percentage of Elapsed Time
  • Total Executions: 3,369,093
  • Captured SQL account for 12.4% of Total
Executions Rows Processed Rows per Exec Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text
1,431,218 1,431,218 1.00 326.43 104.15 0.00 g0unxwd406yu0 PL/SQL Developer declare ret binary_integer; be...
1,431,072 1,431,072 1.00 347.51 99.66 0.00 du7nyv586fquh PL/SQL Developer declare runtime_info sys.dbms_...
176,608 176,608 1.00 14.75 100.45 0.05 dr4mnnwqwm3rq PL/SQL Developer insert into shuju1 (SUBJID, V...
153,748 153,748 1.00 13.65 106.62 0.04 845hn917wsu63 PL/SQL Developer insert into shuju1 (SUBJID, V...
16,240 28,567 1.76 0.43 97.39 0.00 cm5vu20fhtnq1   select /*+ connect_by_filterin...
15,601 15,601 1.00 0.23 106.95 0.67 7sx5p1ug5ag12 DBMS_SCHEDULER SELECT SPARE4 FROM SYS.OPTSTAT...
14,306 0 0.00 0.12 134.66 0.00 cn39cg7kr98v6 DBMS_SCHEDULER SELECT P.VALCHAR FROM SYS.OPTS...
4,098 4,098 1.00 0.16 95.55 0.00 6qg99cfg26kwb DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(P...
2,699 2,699 1.00 0.71 116.81 0.00 2q7x9yavq27zy PL/SQL Developer declare i integer; fr integer;...
2,663 13,566 5.09 1.03 99.98 0.00 43ruajmwv9fus   insert into histgrm$(obj#, int...

Back to SQL Statistics 
Back to Top

SQL ordered by Parse Calls

  • Total Parse Calls: 3,283,212
  • Captured SQL account for 11.1% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
1,431,217 1,431,218 43.59 g0unxwd406yu0 PL/SQL Developer declare ret binary_integer; be...
1,431,072 1,431,072 43.59 du7nyv586fquh PL/SQL Developer declare runtime_info sys.dbms_...
176,608 176,608 5.38 dr4mnnwqwm3rq PL/SQL Developer insert into shuju1 (SUBJID, V...
153,748 153,748 4.68 845hn917wsu63 PL/SQL Developer insert into shuju1 (SUBJID, V...
16,240 16,240 0.49 cm5vu20fhtnq1   select /*+ connect_by_filterin...
2,699 2,699 0.08 2q7x9yavq27zy PL/SQL Developer declare i integer; fr integer;...
970 970 0.03 350f5yrnnmshs   lock table sys.mon_mods$ in ex...
970 970 0.03 g00cj285jmgsw   update sys.mon_mods$ set inser...
942 942 0.03 8wg0nmja0pfac PL/SQL Developer declare -- Probe 2.1 backtrace...
938 938 0.03 9gkq7rruycsjp   select parttype, partcnt, part...

Back to SQL Statistics 
Back to Top

SQL ordered by Sharable Memory

  • Only Statements with Sharable Memory greater than 1048576 are displayed
Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text
1,253,899 5 0.30 gjm43un5cy843   SELECT SUM(USED), SUM(TOTAL) F...
1,244,533 19 0.30 20kv8785mj5sh PL/SQL Developer select * from sys.all_tab_part...
1,128,018 13 0.27 ab3swhv5g138y   select OBJOID, CLSOID, RUNTIME...

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 Id SQL Text
04p6yvxfsv7cy INSERT INTO 体格检查 SELECT SUBJID, VISIT, FORM, GOURPNAME , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌肉骨骼系统', ITEM_VALUE, ''))) 肌肉骨骼系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌肉骨骼系统异常情况描述', ITEM_VALUE, ''))) 肌肉骨骼系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '神经系统异常情况描述', ITEM_VALUE, ''))) 神经系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '其他', ITEM_VALUE, ''))) 其他 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '泌尿生殖系统', ITEM_VALUE, ''))) 泌尿生殖系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '泌尿生殖系统异常情况描述', ITEM_VALUE, ''))) 泌尿生殖系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '循环系统', ITEM_VALUE, ''))) 循环系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '皮肤异常情况描述', ITEM_VALUE, ''))) 皮肤异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '其他异常情况描述', ITEM_VALUE, ''))) 其他异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '呼吸系统异常情况描述', ITEM_VALUE, ''))) 呼吸系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '内分泌和代谢系统异常情况描述', ITEM_VALUE, ''))) 内分泌和代谢系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '皮肤', ITEM_VALUE, ''))) 皮肤 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '内分泌和代谢系统', ITEM_VALUE, ''))) 内分泌和代谢系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '五官', ITEM_VALUE, ''))) 五官 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '五官异常情 况描述', ITEM_VALUE, ''))) 五官异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '呼吸系统', ITEM_VALUE, ''))) 呼吸系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '消化系统', ITEM_VALUE, ''))) 消化系统 , wmsys.wm_concat(to_char(DECOD E(DESCRIPTION, '神经系统', ITEM_VALUE, ''))) 神经系统 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '循环系统异常情况描述', ITEM_VALUE, ''))) 循环系统异常情况描述 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '消化系统异常情况描述', ITEM_VALUE, ''))) 消化系统异常情况描述 from shuju1 group by SUBJID, VISIT, FORM, GOURPNAME
05s9358mm6vrr begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
0mf6d1n1zzm0z begin -- Call the procedure oc; end;
20kv8785mj5sh select * from sys.all_tab_partitions where table_name = :object_name and table_owner = :object_owner and trim(composite) = 'NO' order by partition_position
25986tpfa5wc3 INSERT INTO 理化检查 SELECT SUBJID, VISIT, FORM , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素临床判断', ITEM_VALUE, ''))) 血清总胆红素临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素异常注释', ITEM_VALUE, ''))) 血清总胆红素异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数检测值', ITEM_VALUE, ''))) 红细胞数检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血红蛋白检测值', ITEM_VALUE, ''))) 血红蛋白检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖异常注释', ITEM_VALUE, ''))) 葡萄糖异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞检测值', ITEM_VALUE, ''))) 红细胞检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞临床判断', ITEM_VALUE, ''))) 白细胞临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞异常注释', ITEM_VALUE, ''))) 白细胞异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶异常注释', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '碱性磷酸酶异常注释', ITEM_VALUE, ''))) 碱性磷酸酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐临床判断', ITEM_VALUE, ''))) 肌酐临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '检查日期', ITEM_VALUE, ''))) 检查日期 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数临床判断', ITEM_VALUE, ''))) 红细胞数临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖检测值', ITEM_VALUE, ''))) 葡萄糖检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质 临床判断', ITEM_VALUE, ''))) 蛋白质临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞临床判断', ITEM_VALUE, ''))) 红细胞临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞异常注释', ITEM_VALUE, ''))) 红细胞异常注释 , wmsys.wm_concat(to_cha r(DECODE(DESCRIPTION, '碱性磷酸酶临床判断', ITEM_VALUE, ''))) 碱性磷酸酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数检测值', ITEM_VALUE, ''))) 白细胞数检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血小板检测值', ITEM_VALUE, ''))) 血小板检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血小板异常注释', ITEM_VALUE, ''))) 血小板异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖临床判断', ITEM_VALUE, ''))) 葡萄糖临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶临床判断', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图结果', ITEM_VALUE, ''))) 心电图结果 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图结论', ITEM_VALUE, ''))) 心电图结论 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质异常注释', ITEM_VALUE, ''))) 蛋白质异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞检测值', ITEM_VALUE, ''))) 白细胞检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶临床判断', ITEM_VALUE, ''))) 谷氨酰转肽酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐异常注释', ITEM_VALUE, ''))) 肌酐异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图异常注释', ITEM_VALUE, ''))) 心电图异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血小板临床判断', ITEM_VALUE, ''))) 血小板临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质检测值', ITEM_VALUE, ''))) 蛋白质检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸 氨基转移酶检测值', ITEM_VALUE, ''))) 丙氨酸氨基转移酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数异常注释', ITEM_VALUE, ''))) 红细胞数异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数异常注释', ITEM_VALUE, ''))) 白细胞数异常注释 , wmsys.wm _concat(to_char(DECODE(DESCRIPTION, '血红蛋白异常注释', ITEM_VALUE, ''))) 血红蛋白异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸氨基转移酶异常注释', ITEM_VALUE, ''))) 丙氨酸氨基转移酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶检测值', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '碱性磷酸酶检测值', ITEM_VALUE, ''))) 碱性磷酸酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素检测值', ITEM_VALUE, ''))) 血清总胆红素检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶检测值', ITEM_VALUE, ''))) 谷氨酰转肽酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮检测值', ITEM_VALUE, ''))) 尿素氮检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮临床判断', ITEM_VALUE, ''))) 尿素氮临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮异常注释', ITEM_VALUE, ''))) 尿素氮异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图临床判断', ITEM_VALUE, ''))) 心电图临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血红蛋白临床判断', ITEM_VALUE, ''))) 血红蛋白临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '单位', ITEM_VALUE, ''))) 单位 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶异常注释', ITEM_VALUE, ''))) 谷氨酰转肽酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐检测值', ITEM_VALUE, ''))) 肌酐检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数临床判断', ITEM_VALUE, ''))) 白细胞数临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸氨基转移酶临床判断', ITEM_VALUE, ''))) 丙氨酸氨基转移酶临床判断 from shuju1 group by SUBJID, VISIT, FORM
2gjv8dam6m03g /* SQL Analyze(1) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("OBJ#")), to_char(substrb(dump(min("OBJ#"), 16, 0, 32), 1, 120)), to_char(substrb(dump(max("OBJ#"), 16, 0, 32), 1, 120)), to_char(count("PART")), to_char(substrb(dump(min("PART"), 16, 0, 32), 1, 120)), to_char(substrb(dump(max("PART"), 16, 0, 32), 1, 120)), to_char(count("VERSION")), to_char(substrb(dump(min("VERSION"), 16, 0, 32), 1, 120)), to_char(substrb(dump(max("VERSION"), 16, 0, 32), 1, 120)), to_char(count("PIECE#")), to_char(substrb(dump(min("PIECE#"), 16, 0, 32), 1, 120)), to_char(substrb(dump(max("PIECE#"), 16, 0, 32), 1, 120)), to_char(count("LENGTH")), to_char(substrb(dump(min("LENGTH"), 16, 0, 32), 1, 120)), to_char(substrb(dump(max("LENGTH"), 16, 0, 32), 1, 120)) from "SYS"."IDL_UB2$" t /* NDV, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL, NDV, NIL, NIL*/
2q7x9yavq27zy declare i integer; fr integer; begin i := 0; loop :result := sys.dbms_debug.get_value(variable_name => :variable_name, frame# => :frame#, scalar_value => :scalar_value, format => :format); if i = 0 then fr := :result; end if; exit when (:result in (0, 1, 32)) or (i = 9); i := i + 1; :frame# := :frame# + 1; end loop; if :result <> 0 then :result := fr; end if; end;
2tr12b1b8uj71 MERGE /*+ dynamic_sampling(ST 4) dynamic_sampling_est_cdn(ST) */ INTO STATS_TARGET$ ST USING (SELECT STALENESS, OSIZE, OBJ#, TYPE#, CASE WHEN STALENESS > LOG(0.01, NVL(LOC_STALE_PCT, :B1 )/100) THEN 128 ELSE 0 END + AFLAGS AFLAGS, STATUS, SID, SERIAL#, PART#, BO# FROM ( SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(BITAND(T.FLAGS, 16), 16, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, CASE WHEN T.FILE# = 0 THEN DBMS_STATS_INTERNAL.GET_TABLE_BLOCK_COUNT(U.NAME, O.NAME, NULL, NULL, 'TRUE') WHEN S.TYPE# = 5 THEN DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), O.DATAOBJ#, S.BLOCKS, 'TRUE') ELSE NULL END * NVL(TS.BLOCKSIZE, :B6 ) OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#, 32 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, NULL PART#, NULL BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.SEG$ S, SYS.TS$ TS, SYS.USER$ U, SYS.OBJ$ O, SYS.TAB$ T, SYS.MON_MODS_ALL$ M, OPTSTAT_USER_PREFS$ P WHERE (BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(T.OBJ#, :B3 ), :B2 ) <> 0) AND O.TYPE# = 2 AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND M.OBJ#(+) = T.OBJ# AND T.FILE# = S.FILE#(+) AND T.BLOCK# = S.BLOCK#(+) AND T.TS# = S.TS#(+) AND TS.TS#( +) = S.TS# AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND BITAND(T.PROPERTY, 64+512) = 0 AND BITAND(T.FLAGS, 536870912) != 536870912 AND BITAND(T.FLAGS, 2097152) != 2097152 AND BITAND(O.FLAGS, 128) != 128 AND BITAND(T.PROPERTY, 2147483648) = 0 AND (BITAND(T.PROPERTY, 4194304) != 4194304 AND BITAND(T.PROPERTY, 8388608) != 8388608) AND NOT (BITAND(O.FLAGS, 16) = 16 AND (O.NAME LIKE 'DR$%' OR O.NAME LIKE 'DR#%')) AND NOT (U.NAME = 'SYS' AND O.NAME IN ('SUMDELTA$', 'SNAP_XCMT$')) AND O.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND (U.NAME, O.NAME) NOT IN (SELECT /*+ unnest */ * FROM (SELECT MOWNER, LOG FROM MLOG$ UNION ALL SELECT MOWNER, TEMP_LOG FROM MLOG$ WHERE TEMP_LOG IS NOT NULL)) AND ((BITAND(T.FLAGS, 16) = 16 AND ((BITAND(M.FLAGS, 5) != 0) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * T.ROWCNT))) OR (BITAND(T.FLAGS, 16) != 16)) UNION ALL SELECT STALENESS, OSIZE, OBJ#, TYPE#, CASE WHEN ROW_NUMBER() OVER (PARTITION BY BO# ORDER BY STALENESS, OSIZE, OBJ#) = 1 THEN 32 ELSE 0 END + 64 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, PART#, BO#, LOC_STALE_PCT FROM ( SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(BITAND(T.FLAGS, 2), 2, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1), -100 .0) STALENESS, DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), O.DATAOBJ#, S.BLOCKS, 'TRUE') * TS.BLOCKSIZE OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#, T.PART# PART#, T.BO# BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.SEG$ S, SYS.TS$ TS, SYS.OBJ$ O, SYS.TABPART$ T, SYS.TAB$ TAB, SYS.OBJ$ OT, SYS.MON_MODS_ALL$ M, SYS.OPTSTAT_USER_PREFS$ P WHERE O.TYPE# = 19 AND S.TYPE# = 5 AND T.OBJ# = O.OBJ# AND TAB.OBJ# = T.BO# AND M.OBJ#(+) = T.OBJ# AND T.FILE# = S.FILE# AND T.BLOCK# = S.BLOCK# AND T.TS# = S.TS# AND TS.TS# = S.TS# AND BITAND(TAB.TRIGFLAG, 67108864) != 67108864 AND BITAND(TAB.PROPERTY, 512) != 512 AND BITAND(TAB.FLAGS, 536870912) != 536870912 AND BITAND(T.FLAGS, 2097152) != 2097152 AND BITAND(T.FLAGS, 32) != 32 AND TAB.OBJ# = OT.OBJ# AND BITAND(OT.FLAGS, 128) != 128 AND NOT (BITAND(O.FLAGS, 16) = 16 AND (O.NAME LIKE 'DR$%' OR O.NAME LIKE 'DR#%')) AND O.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND ((BITAND(T.FLAGS, 2) = 2 AND ((BITAND(M.FLAGS, 5) != 0) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * T.ROWCNT))) OR (BITAND(T.FLAGS, 2) != 2)) UNION ALL SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(BITAND(T.FLAGS, 2), 2, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M .DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, DBMS_STATS_INTERNAL.GET_TABLE_BLOCK_COUNT(U.NAME, O.NAME, O.SUBNAME, NULL, 'TRUE') * :B6 OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#, T.PART# PART#, T.BO# BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.OBJ$ O, SYS.USER$ U, SYS.TABCOMPART$ T, SYS.TAB$ TAB, SYS.OBJ$ OT, SYS.MON_MODS_ALL$ M, SYS.OPTSTAT_USER_PREFS$ P WHERE O.TYPE# = 19 AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND TAB.OBJ# = T.BO# AND M.OBJ#(+) = T.OBJ# AND BITAND(TAB.TRIGFLAG, 67108864) != 67108864 AND BITAND(T.FLAGS, 32) != 32 AND TAB.OBJ# = OT.OBJ# AND BITAND(OT.FLAGS, 128) != 128 AND NOT (BITAND(O.FLAGS, 16) = 16 AND (O.NAME LIKE 'DR$%' OR O.NAME LIKE 'DR#%')) AND O.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND ((BITAND(T.FLAGS, 2) = 2 AND ((BITAND(M.FLAGS, 5) != 0) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * T.ROWCNT))) OR (BITAND(T.FLAGS, 2) != 2)) ) WHERE BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(BO#, :B3 ), :B7 ) <> 0 UNION ALL SELECT STALENESS, OSIZE, OBJ#, TYPE#, CASE WHEN ROW_NUMBER() OVER (PARTITION BY BO# ORDER BY STALENESS, OSIZE, OBJ#) = 1 THEN 64 ELSE 0 END + CASE WHEN ROW_NUMBER() OVER (PARTITION BY (SELECT TCP0.BO# FROM TABCOMPART$ TCP0 WHERE TCP0.OBJ#=ST0.BO#) ORDER BY STALENESS, OSIZE, OBJ#) = 1 THEN 32 ELSE 0 END AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, PART#, BO#, LOC_STALE_PCT FROM (SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_ est_cdn */ DECODE(BITAND(T.FLAGS, 2), 2, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#, T.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), O.DATAOBJ#, S.BLOCKS, 'TRUE') * TS.BLOCKSIZE OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#, T.SUBPART# PART#, T.POBJ# BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.SEG$ S, SYS.TS$ TS, SYS.OBJ$ O, SYS.TABSUBPART$ T, SYS.TABCOMPART$ TCP, SYS.TAB$ TAB, SYS.OBJ$ OT, SYS.MON_MODS_ALL$ M, SYS.PARTOBJ$ PO, SYS.OPTSTAT_USER_PREFS$ P WHERE O.TYPE# = 34 AND S.TYPE# = 5 AND T.OBJ# = O.OBJ# AND M.OBJ#(+) = T.OBJ# AND T.FILE# = S.FILE# AND T.BLOCK# = S.BLOCK# AND T.TS# = S.TS# AND TS.TS# = S.TS# AND TCP.OBJ# = T.POBJ# AND TCP.BO# = TAB.OBJ# AND PO.OBJ# = TAB.OBJ# AND (((BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(T.OBJ#, :B3 ), :B9 ) <> 0) AND BITAND(PO.SPARE2, 255) = 2) OR ((BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(T.OBJ#, :B3 ), :B8 ) <> 0) AND BITAND(PO.SPARE2, 255) NOT IN (0, 2))) AND BITAND(TAB.TRIGFLAG, 67108864) != 67108864 AND TAB.OBJ# = OT.OBJ# AND BITAND(OT.FLAGS, 128) != 128 AND NOT (BITAND(O.FLAGS, 16) = 16 AND (O.NAME LIKE 'DR$%' OR O.NAME LIKE 'DR#%')) AND BITAND(TCP.FLAGS, 32) != 32 AND O.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND ((BITAND(T.FLAGS, 2) = 2 AND ((BITAND(M.FLAGS, 5) != 0) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * T.ROWCNT))) OR (BITAND(T.FLAGS, 2) != 2))) ST0 UNION ALL SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(BITAND(I.FLAGS, 2), 2, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(CM.TRUNC, 1, GREATEST(CM.ROWCNT, CM.INSERTS), CM.TOT_MODS/(CM.ROWCNT + 0.01))))), 0.01)), 1), -100.0) STALENESS, DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(I.TS#, I.FILE#, I.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), O.DATAOBJ#, S.BLOCKS, 'TRUE') * TS.BLOCKSIZE OSIZE, O.OBJ# OBJ#, O.TYPE# TYPE#, 32 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, NULL PART#, I.BO# BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.SEG$ S, SYS.TS$ TS, SYS.OBJ$ O, SYS.IND$ I, SYS.OPTSTAT_USER_PREFS$ P, (SELECT BOBJ#, SUM(BITAND(T.TRIGFLAG, 67108864)/67108864) LOCKED, SUM(BITAND(M.FLAGS, 1))/COUNT(M.FLAGS) TRUNC, SUM(M.INSERTS+M.DELETES) TOT_MODS, SUM(M.INSERTS) INSERTS, SUM(T.ROWCNT) ROWCNT FROM MON_MODS_ALL$ M, TAB$ T WHERE M.OBJ#(+) = T.OBJ# AND T.CLUCOLS IS NOT NULL GROUP BY BOBJ#) CM WHERE (DBMS_STATS_INTERNAL.GATHER_INDEX(CM.BOBJ#, :B10 ) IS NOT NULL) AND O.TYPE# = 1 AND S.TYPE# = 6 AND I.TYPE# = 3 AND I.OBJ# = O.OBJ# AND I.BO# = CM.BOBJ#(+) AND I.FILE# = S.FILE# AND I.BLOCK# = S.BLOCK# AND I.TS# = S.TS# AND TS.TS# = S.TS# AND BITAND(O.FLAGS, 128) != 128 AND O.OBJ# = P.OBJ# (+) AND P.PNAME(+) = 'STALE_PERCENT' AND (BITAND(I.FLAGS, 2) = 0 OR (BITAND(I.FLAGS, 2) != 0 AND (CM.TRUNC > 0 OR CM.TOT_MODS > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * CM.ROWCNT))) AND CM.LOCKED = 0 AND BITAND(I.FLAGS, 1+8+16+1024+4096) = 0 UNION ALL SELECT /*+ no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ DECODE(BITAND(T.FLAGS, 16), 16, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT + M.INSERTS - M.DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, CASE WHEN I.FILE# = 0 THEN DBMS_STATS_INTERNAL.GET_INDEX_BLOCK_COUNT(U.NAME, OI.NAME, NULL, NULL, 'TRUE') WHEN S.TYPE# = 6 THEN DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(I.TS#, I.FILE#, I.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), I.DATAOBJ#, S.BLOCKS, 'TRUE') ELSE NULL END * NVL(TS.BLOCKSIZE, :B6 ) OSIZE, OT.OBJ# OBJ#, OT.TYPE# TYPE#, 32 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, NULL PART#, NULL BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.IND$ I, SYS.SEG$ S, SYS.MON_MODS_ALL$ M, SYS.TS$ TS, SYS.TAB$ T, SYS.OBJ$ OI, OPTSTAT_USER_PREFS$ P WHERE (BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(T.OBJ#, :B3 ), :B2 ) <> 0) AND I.TYPE# = 4 AND I.BO# = OT.OBJ# AND BITAND(OT.FLAGS, 128) != 128 AND OT.OWNER# = U.USER# AND M.OBJ#(+) = OT.OBJ# AND I.FILE# = S.FILE#(+) AND I.BLOCK# = S.BLO CK#(+) AND I.TS# = S.TS#(+) AND TS.TS#(+) = S.TS# AND T.OBJ# = OT.OBJ# AND I.OBJ# = OI.OBJ# AND NOT (BITAND(OT.FLAGS, 16) = 16 AND (OT.NAME LIKE 'DR$%' OR OT.NAME LIKE 'DR#%')) AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND OT.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND ((BITAND(T.FLAGS, 16) = 16 AND ((BITAND(M.FLAGS, 1) = 1) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * T.ROWCNT))) OR (BITAND(M.FLAGS, 4) = 4) OR (BITAND(T.FLAGS, 16) != 16)) UNION ALL SELECT /*+ leading(i t) no_expand dynamic_sampling(4) dynamic_sampling_est_cdn */ STALENESS, OSIZE, OBJ#, TYPE#, CASE WHEN ROW_NUMBER() OVER (PARTITION BY BO# ORDER BY STALENESS, OSIZE, OBJ#) = 1 THEN 32 ELSE 0 END + 64 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, PART#, BO#, LOC_STALE_PCT FROM (SELECT DECODE(BITAND(TP.FLAGS, 2), 2, ROUND( LOG(0.01, NVL( LEAST( 100, GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(TP.ROWCNT, M.INSERTS), LEAST((M.INSERTS + M.DELETES + M.UPDATES), GREATEST(TP.ROWCNT, (TP.ROWCNT + M.INSERTS - M.DELETES)))) / (TP.ROWCNT + 0.01)))), 0.01)), 1), -100.0) STALENESS, DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(IP.TS#, IP.FILE#, IP.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1, 0), IP.DATAOBJ#, S.BLOCKS, 'TRUE') * TS.BLOCKSIZE OSIZE, OT.OBJ# OBJ#, OT.TYPE# TYPE#, TP.PART# PART#, TP.BO# BO#, TO_NUMBER(P.VALCHAR) LOC_STALE_PCT FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.IND$ I, SYS.INDPART$ IP, SYS.TABPART$ TP, SYS.SEG$ S , SYS.MON_MODS_ALL$ M, SYS.TS$ TS, SYS.TAB$ T, SYS.OPTSTAT_USER_PREFS$ P WHERE I.TYPE# = 4 AND S.TYPE# = 6 AND OT.OWNER# = U.USER# AND I.OBJ# = IP.BO# AND IP.PART# = TP.PART# AND TP.OBJ# = OT.OBJ# AND I.BO# = TP.BO# AND BITAND(OT.FLAGS, 128) != 128 AND M.OBJ#(+) = OT.OBJ# AND IP.FILE# = S.FILE# AND IP.BLOCK# = S.BLOCK# AND IP.TS# = S.TS# AND TS.TS# = S.TS# AND I.BO# = T.OBJ# AND NOT (BITAND(OT.FLAGS, 16) = 16 AND (OT.NAME LIKE 'DR$%' OR OT.NAME LIKE 'DR#%')) AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND BITAND(TP.FLAGS, 32) != 32 AND OT.OBJ# = P.OBJ#(+) AND P.PNAME(+) = 'STALE_PERCENT' AND ((BITAND(TP.FLAGS, 2) = 2 AND ((BITAND(M.FLAGS, 1) = 1) OR ((M.INSERTS + M.UPDATES + M.DELETES) > NVL(TO_NUMBER(P.VALCHAR), :B1 )/100 * TP.ROWCNT))) OR (BITAND(M.FLAGS, 4) = 4) OR (BITAND(TP.FLAGS, 2) != 2))) WHERE BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(BO#, :B3 ), :B7 ) <> 0 UNION ALL SELECT /*+ ordered */ -99 STALENESS, 100*:B6 OSIZE, I.OBJ# OBJ#, 1 TYPE#, 32 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, NULL PART#, I.BO# BO#, NULL LOC_STALE_PCT FROM SYS.IND$ I, SYS.TAB$ T, SYS.OBJ$ O, USER$ U WHERE (DBMS_STATS_INTERNAL.GATHER_INDEX(I.BO#, :B10 ) IS NOT NULL) AND (BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(I.BO#, :B3 ), :B2 ) <> 0) AND BITAND(I.PROPERTY, 32) != 32 AND BITAND(I.PROPERTY, 64) != 64 AND BITAND(I.FLAGS, 2) != 2 AND I.BO# = T.OBJ# AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND BITAND(T.FLAGS, 16) = 16 AND I.OBJ# = O.OBJ# AND I.TYPE# != 4 AND I.TYPE# != 8 AND BITAND(I.FLAGS, 1+8+16+1024+4096) = 0 AND BITAND(O.FLAGS, 128) != 128 AND O.OWNER# = U.USER# AND NOT (U.NAME = 'SYS' AND O.NAME = 'I_SUMDELTA$') UNION ALL SELECT /*+ ordered */ -99 STALENESS, 100*:B6 OSIZE, I.OBJ# OBJ#, 20 TYPE#, 64 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, I.PART# PART#, I.BO# BO#, NULL LOC_STALE_PCT FROM (SELECT OBJ#, BO#, PART#, FLAGS FROM SYS.INDPART$ WHERE BITAND(FLAGS, 2) != 2 UNION ALL SELECT OBJ#, BO#, PART#, FLAGS FROM SYS.INDCOMPART$ WHERE BITAND(FLAGS, 2) != 2 ) I, SYS.IND$ IND, SYS.TAB$ T, SYS.OBJ$ OI WHERE (DBMS_STATS_INTERNAL.GATHER_INDEX(I.BO#, :B10 ) IS NOT NULL) AND (BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(I.BO#, :B3 ), :B7 ) <> 0) AND I.BO# = IND.OBJ# AND IND.TYPE# != 4 AND IND.TYPE# != 8 AND BITAND(IND.FLAGS, 1+8+16+1024+4096) = 0 AND IND.BO# = T.OBJ# AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND IND.OBJ# = OI.OBJ# AND BITAND(OI.FLAGS, 128) != 128 UNION ALL SELECT /*+ ordered */ -99 STALENESS, 100*:B6 OSIZE, I.OBJ# OBJ#, 35 TYPE#, 0 AFLAGS, 0 STATUS, :B5 SID, :B4 SERIAL#, I.SUBPART# PART#, I.POBJ# BO#, NULL LOC_STALE_PCT FROM SYS.INDSUBPART$ I, SYS.INDCOMPART$ ICP, SYS.IND$ IND, SYS.TAB$ T, SYS.PARTOBJ$ PO, SYS.OBJ$ OI WHERE (DBMS_STATS_INTERNAL.GATHER_INDEX(IND.BO#, :B10 ) IS NOT NULL) AND BITAND(I.FLAGS, 2) != 2 AND I.POBJ# = ICP.OBJ# AND ICP.BO# = IND.OBJ# AND IND.TYPE# != 4 AND IND.TYPE# != 8 AND BITAND(IND.FLAGS, 1+8+16+1024+4096) = 0 AND IND.BO# = T.OBJ# AND BITAND(T.TRIGFLAG, 67108864) != 67108864 AND IND.OBJ# = PO.OBJ# AND (((BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(IND.BO#, :B3 ), :B9 ) <> 0) AND BITAND(PO.SPARE2, 255) = 2) OR ((BITAND(DBMS_STATS_INTERNAL.DECODE_GRANULARITY(IND.BO#, :B3 ), :B8 ) <> 0) AND BITAND(PO.SPARE2, 255) NOT IN (0, 2))) AND IND.OBJ# = OI.OBJ# AND BITAND(OI.FLAGS, 128) != 128 ) WHERE OSIZE IS NOT NULL ) NEW ON (ST.OBJ# = NEW.OBJ#) WHEN MATCHED THEN UPDATE SET ST.STALENESS = CASE WHEN BITAND(ST.FLAGS, 7) > 1 THEN GREATEST(NEW.STALENESS, -0.5) + ROUND(BITAND(ST.FLAGS, 7)*0.15, 1) ELSE NEW.STALENESS END, ST.OSIZE = NEW.OSIZE * DECODE(BITAND(ST.FLAGS, 7), 0, 1, 2), ST.FLAGS = ST.FLAGS - BITAND(ST.FLAGS, 32+64+128) + NEW.AFLAGS, ST.STATUS = 0, ST.TYPE# = NEW.TYPE#, ST.PART# = NEW.PART#, ST.BO# = NEW.BO#, ST.SID = NEW.SID, ST.SERIAL# = NEW.SERIAL# WHEN NOT MATCHED THEN INSERT (ST.STALENESS, ST.OSIZE, ST.OBJ#, ST.TYPE#, ST.FLAGS, ST.STATUS, ST.SID, ST.SERIAL#, ST.PART#, ST.BO#) VALUES (NEW.STALENESS, NEW.OSIZE, NEW.OBJ#, NEW.TYPE#, NEW.AFLAGS, NEW.STATUS, :B5 , :B4 , NEW.PART#, NEW.BO#)
32bph60y97zdc SELECT DISTINCT DESCRIPTION, FORM FROM SHUJU1
350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait
39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
3am9cfkvx7gq1 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid, :metric_guid, :metric_values)
3h1rjtcff3wy1 BEGIN ordsys.CARTRIDGE.dbms_feature_multimedia(:feature_boolean, :aux_cnt, :feature_info); END;
3ktacv9r56b51 select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
43ruajmwv9fus insert into histgrm$(obj#, intcol#, row#, bucket, endpoint, col#, epvalue)values(:1, :2, :3, :4, :5, :6, :7)
459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'
49s332uhbnsma declare vsn varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn:pkg_vsn_i := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn, 1, 2) || substr(vsn, 4, 2) || substr(vsn, 7, 2) || nvl(substr(vsn, 10, 2), '00')); end if; end;
4y1y43113gv8f delete from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3
6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
6qg99cfg26kwb SELECT 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 )
7mdacxfm37utk SELECT COUNT(*) FROM MGMT_FAILOVER_TABLE WHERE SYS_EXTRACT_UTC(SYSTIMESTAMP)-LAST_TIME_STAMP_UTC > NUMTODSINTERVAL(HEARTBEAT_INTERVAL*:B1 , 'SECOND')
7mgr3uwydqq8j select decode(open_mode, 'MOUNTED', 0, 'READ WRITE', 1, 'READ ONLY', 1, 'READ ONLY WITH APPLY', 1, 2) dbstate into :dbstate from v$database
7sx5p1ug5ag12 SELECT SPARE4 FROM SYS.OPTSTAT_HIST_CONTROL$ WHERE SNAME = :B1
845hn917wsu63 insert into shuju1 (SUBJID, VISIT, FORM, GOURPNAME, DESCRIPTION, ITEM_VALUE) values (:v0, :v1, :v2, :v3, :v4, :v5)
8swypbbr0m372 select order#, columns, types from access$ where d_obj#=:1
8wg0nmja0pfac declare -- Probe 2.1 backtrace sys.dbms_debug.backtrace_table; program_info sys.dbms_debug.program_info; i integer; j integer; begin sys.dbms_debug.print_backtrace(listing => :listing); begin sys.dbms_debug.print_backtrace(backtrace => backtrace); exception when others then backtrace.delete; end; :names := ''; :namespaces := ''; :owners := ''; :lines := ''; i := backtrace.first; j := 0; while (i is not null) and (j < 100) loop program_info := backtrace(i); :names := :names || program_info.name || chr(10); :namespaces := :namespaces || to_char(program_info.namespace) || chr(10); :owners := :owners || program_info.owner || chr(10); :lines := :lines || to_char(program_info.line#) || chr(10); i := backtrace.next(i); j := j + 1; end loop; end;
977mq4aw9jc36 INSERT INTO 理化检查 SELECT SUBJID, VISIT, FORM, GOURPNAME , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数检测值', ITEM_VALUE, ''))) 红细胞数检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血红蛋白检测值', ITEM_VALUE, ''))) 血红蛋白检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖异常注释', ITEM_VALUE, ''))) 葡萄糖异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞检测值', ITEM_VALUE, ''))) 红细胞检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞临床判断', ITEM_VALUE, ''))) 白细胞临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶异常注释', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '碱性磷酸酶临床判断', ITEM_VALUE, ''))) 碱性磷酸酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数检测值', ITEM_VALUE, ''))) 白细胞数检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血小板检测值', ITEM_VALUE, ''))) 血小板检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图结果', ITEM_VALUE, ''))) 心电图结果 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞检测值', ITEM_VALUE, ''))) 白细胞检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸氨基转移酶检测值', ITEM_VALUE, ''))) 丙氨酸氨基转移酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶临床判断', ITEM_VALUE, ''))) 谷氨酰转肽酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图异常注释', ITEM_VALUE, ''))) 心电图异常注释 , wmsys.wm_concat(to_char(DECODE(DESCR IPTION, '血小板临床判断', ITEM_VALUE, ''))) 血小板临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸氨基转移酶异常注释', ITEM_VALUE, ''))) 丙氨酸氨基转移酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数异常注释', ITEM_VALUE, ''))) 红细胞数异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血红蛋白临床判断', ITEM_VALUE, ''))) 血红蛋白临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '单位', ITEM_VALUE, ''))) 单位 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数临床判断', ITEM_VALUE, ''))) 白细胞数临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素临床判断', ITEM_VALUE, ''))) 血清总胆红素临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素异常注释', ITEM_VALUE, ''))) 血清总胆红素异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞异常注释', ITEM_VALUE, ''))) 白细胞异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '碱性磷酸酶异常注释', ITEM_VALUE, ''))) 碱性磷酸酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐临床判断', ITEM_VALUE, ''))) 肌酐临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '检查日期', ITEM_VALUE, ''))) 检查日期 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞数临床判断', ITEM_VALUE, ''))) 红细胞数临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖检测值', ITEM_VALUE, ''))) 葡萄糖检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质临床判断', ITEM_VALUE, ''))) 蛋白质临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞临床判断', ITEM_VALUE, ''))) 红细胞临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '红细胞异常注释', ITEM_VALUE, ''))) 红细胞异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶临床判断', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶临床判断 , wmsys.wm_c oncat(to_char(DECODE(DESCRIPTION, '血小板异常注释', ITEM_VALUE, ''))) 血小板异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '葡萄糖临床判断', ITEM_VALUE, ''))) 葡萄糖临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图结论', ITEM_VALUE, ''))) 心电图结论 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质异常注释', ITEM_VALUE, ''))) 蛋白质异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐异常注释', ITEM_VALUE, ''))) 肌酐异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '蛋白质检测值', ITEM_VALUE, ''))) 蛋白质检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '天门冬氨酸氨基转移酶检测值', ITEM_VALUE, ''))) 天门冬氨酸氨基转移酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '白细胞数异常注释', ITEM_VALUE, ''))) 白细胞数异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血红蛋白异常注释', ITEM_VALUE, ''))) 血红蛋白异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '碱性磷酸酶检测值', ITEM_VALUE, ''))) 碱性磷酸酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '血清总胆红素检测值', ITEM_VALUE, ''))) 血清总胆红素检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶检测值', ITEM_VALUE, ''))) 谷氨酰转肽酶检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮检测值', ITEM_VALUE, ''))) 尿素氮检测值 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮临床判断', ITEM_VALUE, ''))) 尿素氮临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '尿素氮异常注释', ITEM_VALUE, ''))) 尿素氮异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '心电图临床判断', ITEM_VALUE, ''))) 心电图临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '丙氨酸氨基转移酶临床判断', ITEM_VALUE, ''))) 丙氨酸氨基转移酶临床判断 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '谷氨酰转肽酶异常注释', ITEM_VALUE, ''))) 谷氨酰转肽酶异常注释 , wmsys.wm_concat(to_char(DECODE(DESCRIPTION, '肌酐检测值', ITEM_VALUE, ''))) 肌酐检测值 from shuju1 group by SUBJID, VISIT, FORM, GOURPNAME
9gkq7rruycsjp select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296), 256) defhscflags, mod(spare3, 256) interval_dty, rowid, defmaxsize from partobj$ where obj# = :1
ab3swhv5g138y select OBJOID, CLSOID, RUNTIME, PRI, JOBTYPE, SCHLIM, WT, INST, RUNNOW, ENQ_SCHLIM from ( select a.obj# OBJOID, a.class_oid CLSOID, decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) RUNTIME, (2*a.priority + decode(bitand(a.job_status, 4), 0, 0, decode(a.running_instance, :1, -1, 1))) PRI, 1 JOBTYPE, decode(a.schedule_limit, NULL, decode(bitand(a.flags, 4194304), 4194304, p.schedule_limit, NULL), a.schedule_limit) SCHLIM, a.job_weight WT, decode(a.running_instance, NULL, 0, a.running_instance) INST, decode(bitand(a.flags, 16384), 0, 0, 1) RUNNOW, decode(bitand(a.job_status, 8388608), 0, 0, 1) ENQ_SCHLIM from sys.scheduler$_job a, sys.scheduler$_program p, v$database v, v$instance i where a.program_oid = p.obj#(+) and bitand(a.job_status, 515) = 1 and bitand(a.flags, 1048576) = 0 and ((bitand(a.flags, 134217728 + 268435456) = 0) or (bitand(a.job_status, 1024) <> 0)) and bitand(a.flags, 4096) = 0 and (a.next_run_date <= :2 or bitand(a.flags, 16384) <> 0) and a.instance_id is null and (a.class_oid is null or (a.class_oid is not null and a.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) and (a.database_role = v.database_role or (a.database_role is null and v.database_role = 'PRIMARY' )) and ( i.logins = 'ALLOWED' or bitand(a.flags, 17179869184) <> 0 ) union all select l.obj#, l.class_oid, decode(bitand(l.flags, 16384), 0, l.next_run_date, l.last_enabled_time), (2*decode(bitand(l.flags, 8589934592), 0, q.priority, pj.priority) + decode(bitand(l.job_status, 4), 0, 0, decode(l.running_instance, :3, -1, 1))), 1, decode(bitand(l.flags, 8589934592), 0, q.schedule_limit, decode(pj.schedule_limit, NULL, q.schedule_limit, pj.schedule_limit)), decode(bitand(l.flags, 8589934592), 0, q.job_weight, pj.job_weight), decode(l.running_instance, NULL, 0, l.running_instance), decode(bitand(l.flags, 16384), 0, 0, 1), decode(bitand(l.job_status, 8388608), 0, 0, 1) from sys.scheduler$_lightweight_job l, sys.scheduler$_program q, (select sl.obj# obj#, decode(bitand(sl.flags, 8589934592), 0, sl.program_oid, spj.program_oid) program_oid, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.priority) priority, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.job_weight) job_weight, decode(bitand(sl.flags, 8589934592), 0, NULL, spj.schedule_limit) schedule_limit from sys.scheduler$_lightweight_job sl, scheduler$_job spj where sl.program_oid = spj.obj#(+)) pj , v$instance i where pj.obj# = l.ob j# and pj.program_oid = q.obj#(+) and (:4 = 0 or l.running_instance = :5) and bitand(l.job_status, 515) = 1 and ((bitand(l.flags, 134217728 + 268435456) = 0) or (bitand(l.job_status, 1024) <> 0)) and bitand(l.flags, 4096) = 0 and (l.next_run_date <= :6 or bitand(l.flags, 16384) <> 0) and l.instance_id is null and (l.class_oid is null or (l.class_oid is not null and l.class_oid in (select w.obj# from sys.scheduler$_class w where w.affinity is null))) and ( i.logins = 'ALLOWED' or bitand(l.flags, 17179869184) <> 0 ) union all select c.obj#, 0, c.next_start_date, 0, 2, c.duration, 1, 0, 0, 0 from sys.scheduler$_window c , v$instance i where bitand(c.flags, 1) <> 0 and bitand(c.flags, 2) = 0 and bitand(c.flags, 64) = 0 and c.next_start_date <= :7 and i.logins = 'ALLOWED' union all select d.obj#, 0, d.next_start_date + d.duration, 0, 4, numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_window d , v$instance i where bitand(d.flags, 1) <> 0 and bitand(d.flags, 2) = 0 and bitand(d.flags, 64) = 0 and d.next_start_date <= :8 and i.logins = 'ALLOWED' union all select f.obj#, 0, e.attr_tstamp, 0, decode(bitand(e.flags, 131072), 0, 2, 3), e.attr_intv, 1, 0, 0, 0 from sys.scheduler$_global_attribute e, sys.obj$ f, sys.obj$ g, v$instance i where e.obj# = g.obj# and g.name = 'CURRENT_OPEN_WINDOW' and e.value = f.name and f.type# = 69 and e.attr_tstamp is not null and e.attr_intv is not null and i.logins = 'ALLOWED' union all select i.obj#, 0, h.attr_tstamp + h.attr_intv, 0, decode(bitand(h.flags, 131072), 0, 4, 5), numtodsinterval(0, 'minute'), 1, 0, 0, 0 from sys.scheduler$_global_attribute h, sys.obj$ i, sys.obj$ j, v$instance ik where h.obj# = j.obj# and j.name = 'CURRENT_OPEN_WINDOW' and h.value = i.name and i.type# = 69 and h.attr_tstamp is not null and h.attr_intv is not null and ik.logins = 'ALLOWED') order by RUNTIME, JOBTYPE, CLSOID, PRI, WT DESC, OBJOID
aukf94xzh2p3g declare policy varchar2(512); alldest number := dbms_rcvman.FALSE#; rlgscn number; begin if (:alldest > 0) then alldest := dbms_rcvman.TRUE#; end if; dbms_rcvman.getRedoLogDeletionPolicy(policy => policy); dbms_rcvman.setRedoLogDeletionPolicy(policy => policy, alldest => alldest); if ((policy like '%APPLIED%' OR policy like '%SHIPPED%') AND policy not like '%BACKED UP%') then dbms_rcvman.getSCNForAppliedPolicy( minscn => :applied_scn , rlgscn => rlgscn); else :applied_scn := 0; end if; end;
b6usrg82hwsa3 call dbms_stats.gather_database_stats_job_proc ( )
c0j6cx9kzjf7g SELECT EXTRACTVALUE(VALUE(T), '/select_list_item/pos') + 1 POS, EXTRACTVALUE(VALUE(T), '/select_list_item/value') RES, EXTRACTVALUE(VALUE(T), '/select_list_item/nonnulls') NONNULLS, EXTRACTVALUE(VALUE(T), '/select_list_item/ndv') NDV, EXTRACTVALUE(VALUE(T), '/select_list_item/split') SPLIT, EXTRACTVALUE(VALUE(T), '/select_list_item/rsize') RSIZE, EXTRACTVALUE(VALUE(T), '/select_list_item/rowcnt') ROWCNT, EXTRACT(VALUE(T), '/select_list_item/hash_val').GETCLOBVAL() HASHVAL FROM TABLE(XMLSEQUENCE(EXTRACT(:B1 , '/process_result/select_list_item'))) T
c6awqs517jpj0 select /*+ index(idl_char$ i_idl_char1) +*/ piece#, length, piece from idl_char$ where obj#=:1 and part=:2 and version=:3 order by piece#
cm5vu20fhtnq1 select /*+ connect_by_filtering */ privilege#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0
cn39cg7kr98v6 SELECT P.VALCHAR FROM SYS.OPTSTAT_USER_PREFS$ P WHERE P.OBJ#=:B2 AND P.PNAME=:B1
cvhk2j2gymzhd SELECT 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
cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
dr4mnnwqwm3rq insert into shuju1 (SUBJID, VISIT, FORM, DESCRIPTION, ITEM_VALUE) values (:v0, :v1, :v2, :v3, :v4)
du7nyv586fquh declare runtime_info sys.dbms_debug.runtime_info; sync_result integer; begin sys.dbms_debug.default_timeout := :default_timeout; runtime_info.program.namespace := :program_namespace; runtime_info.program.name := :program_name; runtime_info.program.owner := :program_owner; runtime_info.program.dblink := :program_dblink; runtime_info.line# := :line#; runtime_info.terminated := :terminated; runtime_info.breakpoint := :breakpoint; runtime_info.stackdepth := :stackdepth; runtime_info.interpreterdepth := :interpreterdepth; runtime_info.reason := :reason; if :do_sync = 1 then sync_result := sys.dbms_debug.synchronize(run_info => runtime_info, info_requested => sys.dbms_debug.info_getStackDepth + sys.dbms_debug.info_getLineInfo + sys.dbms_debug.info_getBreakpoint); end if; :cont_result := sys.dbms_debug.continue(run_info => runtime_info, breakflags => :breakflags, info_requested => sys.dbms_debug.info_getStackDepth + sys.dbms_debug.info_getLineInfo + sys.dbms_debug.info_getBreakpoint); :program_namespace := runtime_info.program.namespace; :program_name := runtime_info.program.name; :program_owner := runtime_info.program.owner; :program_dblink := runtime_info.program.dblink; :line# := runtime_info.line#; :terminated := runtime_info.terminated; :breakpoint := runtime_info.breakpoint; :stackdepth := runtime_info.stackdepth; :interpreterdepth := runtime_info.interpreterdepth; :reason := runtime_info.reason; sys.dbms_debug.default_timeout := 3600; end;
f4r4y82fnaqgy BEGIN ctxsys.drifeat.dr$feature_track(:feature_boolean, :aux_cnt, :feature_info); END;
fa16465c7pqmd select table_name, constraint_name, status, owner from sys.all_constraints where r_owner = :r_owner and constraint_type = 'R' and r_constraint_name in ( select constraint_name from sys.all_constraints where constraint_type in ('P', 'U') and table_name = :r_table_name and owner = :r_owner ) order by table_name, constraint_name
ff5spha9pjuyu select * from table(dbms_xplan.display)
fqmpmkfr6pqyk select s.synonym_name object_name, o.object_type from sys.all_synonyms s, sys.all_objects o where s.owner in ('PUBLIC', user) and o.owner = s.table_owner and o.object_name = s.table_name and o.object_type in ('TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
g0unxwd406yu0 declare ret binary_integer; begin ret := PBSDE.DEBUG_LOOP; end;
ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#, length, piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#
gjm43un5cy843 SELECT SUM(USED), SUM(TOTAL) FROM (SELECT /*+ ORDERED */ SUM(D.BYTES)/(1024*1024)-MAX(S.BYTES) USED, SUM(D.BYTES)/(1024*1024) TOTAL FROM (SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) BYTES FROM (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT TS.NAME FROM SYS.OBJ$ OBJ, SYS.TAB$ TAB, SYS.TS$ TS WHERE OBJ.OWNER# = USERENV('SCHEMAID') AND OBJ.OBJ# = TAB.OBJ# AND TAB.TS# = TS.TS# AND BITAND(TAB.PROPERTY, 1) = 0 AND BITAND(TAB.PROPERTY, 4194400) = 0) TN, DBA_FREE_SPACE SP WHERE SP.TABLESPACE_NAME = TN.NAME GROUP BY SP.TABLESPACE_NAME) S, DBA_DATA_FILES D WHERE D.TABLESPACE_NAME = S.TABLESPACE_NAME GROUP BY D.TABLESPACE_NAME)

Back to SQL Statistics 
Back to Top

 

Instance Activity Statistics

Back to Top

Instance Activity Stats

  • Ordered by statistic name
Statistic Total per Second per Trans
Batched IO (bound) vector count 12 0.00 0.01
Batched IO (full) vector count 0 0.00 0.00
Batched IO block miss count 38 0.00 0.02
Batched IO double miss count 0 0.00 0.00
Batched IO same unit count 25 0.00 0.01
Batched IO single block count 11 0.00 0.00
Batched IO vector block count 2 0.00 0.00
Batched IO vector read count 1 0.00 0.00
Block Cleanout Optim referenced 21 0.00 0.01
CCursor + sql area evicted 25 0.00 0.01
CPU used by this session 107,587 5.98 47.71
CPU used when call started 103,454 5.75 45.88
CR blocks created 506 0.03 0.22
Cached Commit SCN referenced 258 0.01 0.11
Commit SCN cached 3 0.00 0.00
DB time 2,682,801 149.09 1,189.71
DBWR checkpoint buffers written 19,234 1.07 8.53
DBWR checkpoints 16 0.00 0.01
DBWR object drop buffers written 393 0.02 0.17
DBWR transaction table writes 449 0.02 0.20
DBWR undo block writes 6,988 0.39 3.10
HSC Heap Segment Block Changes 412,896 22.95 183.10
HSC IDL Compressed Blocks 0 0.00 0.00
Heap Segment Array Inserts 1,412 0.08 0.63
Heap Segment Array Updates 661 0.04 0.29
IMU CR rollbacks 586 0.03 0.26
IMU Flushes 404 0.02 0.18
IMU Redo allocation size 638,248 35.47 283.04
IMU commits 1,987 0.11 0.88
IMU contention 1 0.00 0.00
IMU ktichg flush 5 0.00 0.00
IMU pool not allocated 2 0.00 0.00
IMU recursive-transaction flush 2 0.00 0.00
IMU undo allocation size 9,168,872 509.55 4,066.02
IMU- failed to get a private strand 2 0.00 0.00
LOB table id lookup cache misses 1 0.00 0.00
Number of read IOs issued 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 attempts 207 0.01 0.09
RowCR hits 207 0.01 0.09
SMON posted for undo segment shrink 0 0.00 0.00
SQL*Net roundtrips to/from client 1,797,404 99.89 797.07
TBS Extension: bytes extended 3,932,160 218.53 1,743.75
TBS Extension: files extended 3 0.00 0.00
TBS Extension: tasks created 22 0.00 0.01
TBS Extension: tasks executed 22 0.00 0.01
active txn count during cleanout 4,077 0.23 1.81
application wait time 10 0.00 0.00
background checkpoints completed 7 0.00 0.00
background checkpoints started 5 0.00 0.00
background timeouts 75,398 4.19 33.44
buffer is not pinned count 606,512 33.71 268.96
buffer is pinned count 803,206 44.64 356.19
bytes received via SQL*Net from client 3,718,946,383 206,676.35 1,649,200.17
bytes sent via SQL*Net to client 309,034,396 17,174.25 137,044.08
calls to get snapshot scn: kcmgss 196,268 10.91 87.04
calls to kcmgas 15,479 0.86 6.86
calls to kcmgcs 431,261 23.97 191.25
cell physical IO interconnect bytes 927,578,624 51,549.16 411,343.07
change write time 575 0.03 0.25
cleanout - number of ktugct calls 4,187 0.23 1.86
cleanouts and rollbacks - consistent read gets 134 0.01 0.06
cleanouts only - consistent read gets 72 0.00 0.03
cluster key scan block gets 133,619 7.43 59.25
cluster key scans 113,507 6.31 50.34
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 1 0.00 0.00
commit batch/immediate requested 1 0.00 0.00
commit cleanout failures: block lost 27 0.00 0.01
commit cleanout failures: callback failure 74 0.00 0.03
commit cleanouts 23,745 1.32 10.53
commit cleanouts successfully completed 23,644 1.31 10.49
commit immediate performed 1 0.00 0.00
commit immediate requested 1 0.00 0.00
commit txn count during cleanout 337 0.02 0.15
concurrency wait time 2,727 0.15 1.21
consistent changes 11,185 0.62 4.96
consistent gets 1,678,334 93.27 744.27
consistent gets - examination 445,589 24.76 197.60
consistent gets direct 14 0.00 0.01
consistent gets from cache 1,678,320 93.27 744.27
consistent gets from cache (fastpath) 1,187,964 66.02 526.81
cursor authentications 942 0.05 0.42
data blocks consistent reads - undo records applied 948 0.05 0.42
db block changes 1,020,320 56.70 452.47
db block gets 1,347,154 74.87 597.41
db block gets direct 140 0.01 0.06
db block gets from cache 1,347,014 74.86 597.35
db block gets from cache (fastpath) 777,352 43.20 344.72
deferred (CURRENT) block cleanout applications 11,534 0.64 5.11
enqueue conversions 3,702 0.21 1.64
enqueue releases 172,787 9.60 76.62
enqueue requests 172,789 9.60 76.62
enqueue timeouts 1 0.00 0.00
enqueue waits 12 0.00 0.01
execute count 3,369,093 187.23 1,494.05
failed probes on index block reclamation 1 0.00 0.00
file io wait time 3,481,194 193.46 1,543.77
free buffer requested 20,188 1.12 8.95
heap block compress 537 0.03 0.24
immediate (CR) block cleanout applications 206 0.01 0.09
immediate (CURRENT) block cleanout applications 6,031 0.34 2.67
index crx upgrade (positioned) 51 0.00 0.02
index crx upgrade (prefetch) 0 0.00 0.00
index fast full scans (full) 455 0.03 0.20
index fetch by key 583,505 32.43 258.76
index scans kdiixs1 374,084 20.79 165.89
leaf node 90-10 splits 112 0.01 0.05
leaf node splits 220 0.01 0.10
lob reads 4,295 0.24 1.90
lob writes 3,471 0.19 1.54
lob writes unaligned 3,471 0.19 1.54
logons cumulative 1,145 0.06 0.51
max cf enq hold time 16 0.00 0.01
messages received 11,471 0.64 5.09
messages sent 11,471 0.64 5.09
min active SCN optimization applied on CR 3 0.00 0.00
no work - consistent read gets 781,197 43.41 346.43
non-idle wait count 1,898,896 105.53 842.08
non-idle wait time 7,628 0.42 3.38
opened cursors cumulative 3,356,781 186.55 1,488.59
parse count (describe) 36 0.00 0.02
parse count (failures) 152 0.01 0.07
parse count (hard) 2,058 0.11 0.91
parse count (total) 3,283,212 182.46 1,455.97
parse time cpu 7,545 0.42 3.35
parse time elapsed 7,729 0.43 3.43
physical read IO requests 909 0.05 0.40
physical read bytes 56,516,608 3,140.85 25,062.80
physical read total IO requests 20,902 1.16 9.27
physical read total bytes 384,717,824 21,380.27 170,606.57
physical read total multi block requests 93 0.01 0.04
physical reads 6,899 0.38 3.06
physical reads cache 6,850 0.38 3.04
physical reads cache prefetch 5,990 0.33 2.66
physical reads direct 49 0.00 0.02
physical reads direct (lob) 0 0.00 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 3 0.00 0.00
physical write IO requests 5,875 0.33 2.61
physical write bytes 162,316,288 9,020.55 71,980.62
physical write total IO requests 24,833 1.38 11.01
physical write total bytes 542,860,800 30,168.89 240,736.50
physical write total multi block requests 911 0.05 0.40
physical writes 19,814 1.10 8.79
physical writes direct 175 0.01 0.08
physical writes direct (lob) 39 0.00 0.02
physical writes direct temporary tablespace 101 0.01 0.04
physical writes from cache 19,639 1.09 8.71
physical writes non checkpoint 11,760 0.65 5.22
process last non-idle time 14,856 0.83 6.59
recovery blocks read 0 0.00 0.00
recursive calls 6,735,351 374.31 2,986.85
recursive cpu usage 56,954 3.17 25.26
redo blocks checksummed by FG (exclusive) 26,315 1.46 11.67
redo blocks read for recovery 0 0.00 0.00
redo blocks written 317,145 17.62 140.64
redo buffer allocation retries 3 0.00 0.00
redo entries 507,508 28.20 225.06
redo k-bytes read for recovery 0 0.00 0.00
redo k-bytes read total 0 0.00 0.00
redo log space requests 3 0.00 0.00
redo log space wait time 2 0.00 0.00
redo ordering marks 5,643 0.31 2.50
redo size 155,363,524 8,634.16 68,897.35
redo size for direct writes 1,768 0.10 0.78
redo subscn max counts 11,492 0.64 5.10
redo synch time 82 0.00 0.04
redo synch writes 690 0.04 0.31
redo wastage 1,526,604 84.84 676.99
redo write time 667 0.04 0.30
redo writes 5,847 0.32 2.59
rollback changes - undo records applied 1 0.00 0.00
rollbacks only - consistent read gets 372 0.02 0.16
rows fetched via callback 90,508 5.03 40.14
session connect time 5,468 0.30 2.42
session cursor cache hits 635,789 35.33 281.95
session logical reads 3,025,488 168.14 1,341.68
shared hash latch upgrades - no wait 21,982 1.22 9.75
sorts (memory) 60,736 3.38 26.93
sorts (rows) 3,766,001 209.29 1,670.07
sql area evicted 645 0.04 0.29
sql area purged 203 0.01 0.09
switch current to new buffer 2,534 0.14 1.12
table fetch by rowid 213,230 11.85 94.56
table fetch continued row 593 0.03 0.26
table scan blocks gotten 284,169 15.79 126.02
table scan rows gotten 20,711,742 1,151.03 9,184.81
table scans (long tables) 1 0.00 0.00
table scans (short tables) 30,249 1.68 13.41
temp space allocated (bytes) 8,388,608 466.19 3,720.00
total cf enq hold time 407 0.02 0.18
total number of cf enq holders 183 0.01 0.08
total number of times SMON posted 22 0.00 0.01
transaction rollbacks 1 0.00 0.00
undo change vector size 39,553,260 2,198.13 17,540.25
user I/O wait time 223 0.01 0.10
user calls 3,592,993 199.68 1,593.35
user commits 2,192 0.12 0.97
user rollbacks 63 0.00 0.03
workarea executions - onepass 0 0.00 0.00
workarea executions - optimal 27,350 1.52 12.13
write clones created in background 12 0.00 0.01

Back to Instance Activity Statistics 
Back to Top

Instance Activity Stats - Absolute Values

  • Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
session uga memory max 6,629,559,024 12,083,207,472
session pga memory 1,932,356,096 2,677,229,464
session pga memory max 2,227,261,360 3,104,013,288
session cursor cache count 51,748 73,149
session uga memory 3,749,663,380,184 5,231,482,835,472
opened cursors current 48 52
logons current 31 29

Back to Instance Activity Statistics 
Back to Top

Instance Activity Stats - Thread Activity

  • Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 5 1.00

Back to Instance Activity Statistics 
Back to Top

 

IO Stats

Back to Top

IOStat by Function summary

  • 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
  • ordered by (Data Read + Write) desc
Function Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
Others 311M 1.11 .017283 208M 0.72 .011559 26.4K 0.61
LGWR 2M 0.01 .000111 157M 0.33 .008725 166 0.00
DBWR 0M 0.00 0M 154M 0.32 .008558 15 1.07
Buffer Cache Reads 54M 0.05 .003000 0M 0.00 0M 860 0.67
Direct Reads 1M 0.00 .000055 0M 0.00 0M 0  
Direct Writes 0M 0.00 0M 1M 0.00 .000055 0  
TOTAL: 368M 1.16 .020451 520M 1.38 .028898 27.4K 0.61

Back to IO Stats 
Back to Top

IOStat by Filetype summary

  • 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
  • Small Read and Large Read are average service times, in milliseconds
  • Ordered by (Data Read + Write) desc
Filetype Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Small Read Large Read
Control File 312M 1.11 .017339 205M 0.73 .011392 0.45  
Data File 53M 0.05 .002945 152M 0.33 .008447 0.39 2.60
Log File 0M 0.00 0M 155M 0.33 .008613 0.00  
Temp File 1M 0.00 .000055 2M 0.00 .000111 1.25 0.00
TOTAL: 366M 1.16 .020340 514M 1.38 .028564 0.45 2.52

Back to IO Stats 
Back to Top

IOStat by Function/Filetype summary

  • 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000
  • Ordered by (Data Read + Write) desc for each function
Function/File Name Reads: Data Reqs per sec Data per sec Writes: Data Reqs per sec Data per sec Waits: Count Avg Tm(ms)
Others 311M 1.11 .017283 208M 0.72 .011559 19.9K 0.45
Others (Control File) 311M 1.10 .017283 203M 0.72 .011281 19.8K 0.45
Others (Data File) 0M 0.00 0M 5M 0.00 .000277 48 0.00
LGWR 2M 0.01 .000111 157M 0.33 .008725 116 0.00
LGWR (Log File) 0M 0.00 0M 155M 0.33 .008613 20 0.00
LGWR (Control File) 2M 0.01 .000111 2M 0.01 .000111 96 0.00
DBWR 0M 0.00 0M 154M 0.32 .008558 15 1.07
DBWR (Data File) 0M 0.00 0M 154M 0.32 .008558 0  
DBWR (Control File) 0M 0.00 0M 0M 0.00 0M 15 1.07
Buffer Cache Reads 54M 0.05 .003000 0M 0.00 0M 860 0.67
Buffer Cache Reads (Data File) 54M 0.05 .003000 0M 0.00 0M 860 0.67
Direct Reads 1M 0.00 .000055 0M 0.00 0M 0  
Direct Reads (Data File) 1M 0.00 .000055 0M 0.00 0M 0  
Direct Writes 0M 0.00 0M 1M 0.00 .000055 0  
Direct Writes (Data File) 0M 0.00 0M 1M 0.00 .000055 0  
TOTAL: 368M 1.16 .020451 520M 1.38 .028898 20.9K 0.46

Back to IO Stats 
Back to Top

Tablespace IO Stats

  • ordered by IOs (Reads + Writes) desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
SYSAUX 115 0 0.52 7.30 2,745 0 0 0.00
SYSTEM 729 0 0.58 8.11 1,328 0 0 0.00
UNDOTBS1 7 0 0.00 1.00 1,327 0 0 0.00
USERS 37 0 0.27 1.00 452 0 0 0.00
TEMP 15 0 0.67 6.73 16 0 0 0.00
EXAMPLE 7 0 0.00 1.00 7 0 0 0.00

Back to IO Stats 
Back to Top

File IO Stats

  • ordered by Tablespace, File
Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
EXAMPLE C:\APP\ADMINISTRATOR\ORADATA\WXH\EXAMPLE01.DBF 7 0 0.00 1.00 7 0 0 0.00
SYSAUX C:\APP\ADMINISTRATOR\ORADATA\WXH\SYSAUX01.DBF 115 0 0.52 7.30 2,745 0 0 0.00
SYSTEM C:\APP\ADMINISTRATOR\ORADATA\WXH\SYSTEM01.DBF 729 0 0.58 8.11 1,328 0 0 0.00
TEMP C:\APP\ADMINISTRATOR\ORADATA\WXH\TEMP01.DBF 15 0 0.67 6.73 16 0 0  
UNDOTBS1 C:\APP\ADMINISTRATOR\ORADATA\WXH\UNDOTBS01.DBF 7 0 0.00 1.00 1,327 0 0 0.00
USERS C:\APP\ADMINISTRATOR\ORADATA\WXH\USERS01.DBF 37 0 0.27 1.00 452 0 0 0.00

Back to IO Stats 
Back to Top

 

Buffer Pool Statistics

Back to Top

Buffer Pool Statistics

  • Standard block size Pools D: default, K: keep, R: recycle
  • Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 169,420 100 3,025,140 6,850 19,639 0 0 0

Back to Buffer Pool Statistics 
Back to Top

Checkpoint Activity

  • Total Physical Writes: 19,814
MTTR Writes Log Size Writes Log Ckpt Writes Other Settings Writes Autotune Ckpt Writes Thread Ckpt Writes
0 0 0 0 19,234 0

Back to Buffer Pool Statistics 
Back to Top

 

Advisory Statistics

Back to Top

Instance Recovery Stats

  • B: Begin Snapshot, E: End Snapshot
  Targt MTTR (s) Estd MTTR (s) Recovery Estd IOs Actual RedoBlks Target RedoBlks Log Sz RedoBlks Log Ckpt Timeout RedoBlks Log Ckpt Interval RedoBlks Opt Log Sz(M) Estd RAC Avail Time
B 0 12 3005 95118 95315 165888 95315      
E 0 6 275 1347 1546 165888 1546      

Back to Advisory Statistics 
Back to Top

MTTR Advisory

No data exists for this section of the report.

Back to Advisory Statistics 
Back to Top

Buffer Pool Advisory

  • Only rows with estimated physical reads >0 are displayed
  • ordered by Block Size, Buffers For Estimate
P Size for Est (M) Size Factor Buffers (thousands) Est Phys Read Factor Estimated Phys Reads (thousands) Est Phys Read Time Est %DBtime for Rds
D 128 0.09 16 1.37 58 1 10.00
D 256 0.19 32 1.02 43 1 5.00
D 384 0.28 47 1.00 42 1 5.00
D 512 0.37 63 1.00 42 1 5.00
D 640 0.47 79 1.00 42 1 5.00
D 768 0.56 95 1.00 42 1 5.00
D 896 0.65 110 1.00 42 1 5.00
D 1,024 0.74 126 1.00 42 1 5.00
D 1,152 0.84 142 1.00 42 1 5.00
D 1,280 0.93 158 1.00 42 1 5.00
D 1,376 1.00 169 1.00 42 1 5.00
D 1,408 1.02 173 1.00 42 1 5.00
D 1,536 1.12 189 1.00 42 1 5.00
D 1,664 1.21 205 1.00 42 1 5.00
D 1,792 1.30 221 1.00 42 1 5.00
D 1,920 1.40 236 1.00 42 1 5.00
D 2,048 1.49 252 1.00 42 1 5.00
D 2,176 1.58 268 1.00 42 1 5.00
D 2,304 1.67 284 1.00 42 1 5.00
D 2,432 1.77 299 1.00 42 1 5.00
D 2,560 1.86 315 1.00 42 1 5.00

Back to Advisory Statistics 
Back to Top

PGA Aggr Summary

  • PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
100.00 754 0

Back to Advisory Statistics 
Back to Top

PGA Aggr Target Stats

No data exists for this section of the report.

Back to Advisory Statistics 
Back to Top

PGA Aggr Target Histogram

  • Optimal Executions are purely in-memory operations
Low Optimal High Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
2K 4K 26,033 26,033 0 0
64K 128K 702 702 0 0
128K 256K 22 22 0 0
256K 512K 50 50 0 0
512K 1024K 436 436 0 0
1M 2M 73 73 0 0
2M 4M 16 16 0 0
4M 8M 11 11 0 0
8M 16M 7 7 0 0

Back to Advisory Statistics 
Back to Top

PGA Memory Advisory

  • When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB) Size Factr W/A MB Processed Estd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit % Estd PGA Overalloc Count Estd Time
154 0.13 3,005.82 0.00 100.00 0 529,663
308 0.25 3,005.82 0.00 100.00 0 529,663
616 0.50 3,005.82 0.00 100.00 0 529,663
924 0.75 3,005.82 0.00 100.00 0 529,663
1,232 1.00 3,005.82 0.00 100.00 0 529,663
1,478 1.20 3,005.82 0.00 100.00 0 529,663
1,725 1.40 3,005.82 0.00 100.00 0 529,663
1,971 1.60 3,005.82 0.00 100.00 0 529,663
2,218 1.80 3,005.82 0.00 100.00 0 529,663
2,464 2.00 3,005.82 0.00 100.00 0 529,663
3,696 3.00 3,005.82 0.00 100.00 0 529,663
4,928 4.00 3,005.82 0.00 100.00 0 529,663
7,392 6.00 3,005.82 0.00 100.00 0 529,663
9,856 8.00 3,005.82 0.00 100.00 0 529,663

Back to Advisory Statistics 
Back to Top

Shared Pool Advisory

  • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
  • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
Shared Pool Size(M) SP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits (K)
160 0.40 15 1,061 52,218 1.00 219 1.24 6,600
208 0.52 64 3,840 52,228 1.00 209 1.18 20,528
256 0.64 111 5,434 52,251 1.00 186 1.05 20,534
304 0.76 158 7,108 52,258 1.00 179 1.01 20,536
352 0.88 206 9,303 52,259 1.00 178 1.01 20,536
400 1.00 243 10,942 52,260 1.00 177 1.00 20,536
448 1.12 251 11,436 52,260 1.00 177 1.00 20,536
496 1.24 251 11,436 52,260 1.00 177 1.00 20,536
544 1.36 251 11,436 52,260 1.00 177 1.00 20,536
592 1.48 251 11,436 52,260 1.00 177 1.00 20,536
640 1.60 251 11,436 52,260 1.00 177 1.00 20,536
688 1.72 251 11,436 52,260 1.00 177 1.00 20,536
736 1.84 251 11,436 52,260 1.00 177 1.00 20,536
784 1.96 251 11,436 52,260 1.00 177 1.00 20,536
832 2.08 251 11,436 52,260 1.00 177 1.00 20,536

Back to Advisory Statistics 
Back to Top

SGA Target Advisory

SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
460 0.25 1,999 58,026
920 0.50 1,985 42,361
1,380 0.75 1,985 42,361
1,840 1.00 1,985 42,361
2,300 1.25 1,985 42,361
2,760 1.50 1,985 42,361
3,220 1.75 1,985 42,361
3,680 2.00 1,985 42,361

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

No data exists for this section of the report.

Back to Wait Statistics 
Back to Top

Enqueue Activity

  • only enqueues with waits are shown
  • Enqueue stats gathered prior to 10g should not be compared with 10g data
  • ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
RO-Multiple Object Reuse (fast object reuse) 99 99 0 11 0 1.45
JS-Job Scheduler (queue lock) 65,364 65,364 0 1 0 0.00

Back to Wait Statistics 
Back to Top

 

Undo Statistics

Back to Top

Undo Segment Summary

  • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
  • STO - Snapshot Too Old count, OOS - Out of Space count
  • Undo segment block stats:
  • uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
  • eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
2 6.52 6,245 2,344 4 24.8/54.1 0/0 0/0/0/0/0/0

Back to Undo Statistics 
Back to Top

Undo Segment Stats

  • Most recent 35 Undostat rows, ordered by Time desc
End Time Num Undo Blocks Number of Transactions Max Qry Len (s) Max Tx Concy Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
01-9月 14:54 7 54 586 3 25 0/0 0/0/0/0/0/0
01-9月 14:44 14 91 1,188 3 35 0/0 0/0/0/0/0/0
01-9月 14:34 4 80 587 3 25 0/0 0/0/0/0/0/0
01-9月 14:24 6 59 1,188 2 35 0/0 0/0/0/0/0/0
01-9月 14:14 17 234 586 4 25 0/0 0/0/0/0/0/0
01-9月 14:04 87 193 2,344 4 54 0/0 0/0/0/0/0/0
01-9月 13:54 8 57 1,743 4 44 0/0 0/0/0/0/0/0
01-9月 13:44 9 88 1,191 4 35 0/0 0/0/0/0/0/0
01-9月 13:34 10 136 589 2 25 0/0 0/0/0/0/0/0
01-9月 13:24 1,270 121 1,192 3 35 0/0 0/0/0/0/0/0
01-9月 13:14 216 312 589 3 25 0/0 0/0/0/0/0/0
01-9月 13:04 73 265 1,193 3 35 0/0 0/0/0/0/0/0
01-9月 12:54 1,072 1,356 1,666 3 43 0/0 0/0/0/0/0/0
01-9月 12:44 13 111 1,064 1 33 0/0 0/0/0/0/0/0
01-9月 12:34 24 120 592 3 25 0/0 0/0/0/0/0/0
01-9月 12:24 26 84 1,194 3 35 0/0 0/0/0/0/0/0
01-9月 12:14 17 188 592 2 25 0/0 0/0/0/0/0/0
01-9月 12:04 1,369 264 1,196 3 35 0/0 0/0/0/0/0/0
01-9月 11:54 20 73 594 2 25 0/0 0/0/0/0/0/0
01-9月 11:44 211 203 1,196 3 35 0/0 0/0/0/0/0/0
01-9月 11:34 55 152 594 3 25 0/0 0/0/0/0/0/0
01-9月 11:24 22 80 1,197 2 35 0/0 0/0/0/0/0/0
01-9月 11:14 41 235 594 1 25 0/0 0/0/0/0/0/0
01-9月 11:04 91 189 1,197 3 35 0/0 0/0/0/0/0/0
01-9月 10:54 42 84 595 3 25 0/0 0/0/0/0/0/0
01-9月 10:44 17 300 1,199 2 35 0/0 0/0/0/0/0/0
01-9月 10:34 21 122 597 1 25 0/0 0/0/0/0/0/0
01-9月 10:24 7 54 1,200 2 35 0/0 0/0/0/0/0/0
01-9月 10:14 391 659 598 3 25 0/0 0/0/0/0/0/0
01-9月 10:04 1,364 281 1,200 3 35 0/0 0/0/0/0/0/0

Back to Undo Statistics 
Back to Top

 

Latch Statistics

Back to Top

Latch Activity

  • "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
  • "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
  • "Pct Misses" for both should be very close to 0.0
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
AQ deq hash table latch 5 0.00   0 0  
ASM db client latch 14,633 0.00   0 0  
ASM map operation hash table 5 0.00   0 0  
ASM network state latch 316 0.00   0 0  
AWR Alerted Metric Element list 126,081 0.00   0 0  
Change Notification Hash table latch 5,995 0.00   0 0  
Consistent RBA 5,853 0.00   0 0  
DML lock allocation 25,645 0.00   0 0  
Event Group Locks 805 0.00   0 0  
FIB s.o chain latch 6 0.00   0 0  
FOB s.o list latch 1,904 0.00   0 0  
File State Object Pool Parent Latch 5 0.00   0 0  
IPC stats buffer allocation latch 5 0.00   0 0  
In memory undo latch 30,273 0.00   0 6,367 0.00
JS Sh mem access 45 2.22 1.00 0 0  
JS broadcast autostart latch 3 0.00   0 0  
JS mem alloc latch 94 0.00   0 2 0.00
JS queue access latch 99 0.00   0 0  
JS queue state obj latch 130,728 0.00   0 0  
JS slv state obj latch 3,603 0.08 0.00 0 0  
KFC FX Hash Latch 5 0.00   0 0  
KFC Hash Latch 5 0.00   0 0  
KFCL LE Freelist 5 0.00   0 0  
KGNFS-NFS:SHM structure 5 0.00   0 0  
KGNFS-NFS:SVR LIST 5 0.00   0 0  
KJC message pool free list 5 0.00   0 0  
KJCT flow control latch 5 0.00   0 0  
KMG MMAN ready and startup request latch 5,980 0.00   0 0  
KTF sga latch 104 0.00   0 5,477 0.00
KWQMN job cache list latch 29 0.00   0 0  
KWQS pqsubs latch 19 0.00   0 0  
KWQS pqueue ctx latch 331 0.00   0 0  
Locator state objects pool parent latch 5 0.00   0 0  
Lsod array latch 5 0.00   0 0  
MQL Tracking Latch 0     0 359 0.00
Managed Standby Recovery State 1 0.00   0 0  
Memory Management Latch 5 0.00   0 5,980 0.00
Memory Queue 5 0.00   0 0  
Memory Queue Message Subscriber #1 5 0.00   0 0  
Memory Queue Message Subscriber #2 5 0.00   0 0  
Memory Queue Message Subscriber #3 5 0.00   0 0  
Memory Queue Message Subscriber #4 5 0.00   0 0  
Memory Queue Subscriber 5 0.00   0 0  
MinActiveScn Latch 64 0.00   0 0  
Mutex 5 0.00   0 0  
Mutex Stats 5 0.00   0 0  
OS process 6,497 0.00   0 0  
OS process allocation 10,306 0.01 1.00 0 0  
OS process: request allocation 1,602 0.00   0 0  
PL/SQL warning settings 2,871,313 0.00 0.00 0 0  
PX hash array latch 5 0.00   0 0  
QMT 5 0.00   0 0  
Real-time plan statistics latch 1,931 0.00   0 2 0.00
Result Cache: RC Latch 2 0.00   0 0  
SGA IO buffer pool latch 3 0.00   0 3 0.00
SGA blob parent 5 0.00   0 0  
SGA bucket locks 5 0.00   0 0  
SGA heap locks 5 0.00   0 0  
SGA pool locks 5 0.00   0 0  
SQL memory manager latch 603 0.00   0 5,979 0.00
SQL memory manager workarea list latch 522,617 0.00   0 0  
Shared B-Tree 1,622 0.00   0 0  
Streams Generic 5 0.00   0 0  
Testing 5 0.00   0 0  
Token Manager 5 0.00   0 0  
WCR: sync 5 0.00   0 0  
Write State Object Pool Parent Latch 5 0.00   0 0  
X$KSFQP 5 0.00   0 0  
XDB NFS Security Latch 5 0.00   0 0  
XDB unused session pool 5 0.00   0 0  
XDB used session pool 5 0.00   0 0  
active checkpoint queue latch 11,884 0.00   0 2 0.00
active service list 40,997 0.01 0.00 0 18,467 0.00
alert log latch 0     0 2 0.00
archive control 64 0.00   0 0  
begin backup scn array 34 0.00   0 0  
buffer pool 5 0.00   0 0  
business card 5 0.00   0 0  
cache buffer handles 23,082 0.00   0 0  
cache buffers chains 7,370,180 0.00 0.00 0 56,106 0.00
cache buffers lru chain 25,721 0.00   0 80,465 0.00
cache table scan latch 312 0.00   0 312 0.00
call allocation 7,820 0.10 0.00 0 2 0.00
cas latch 5 0.00   0 0  
change notification client cache latch 5 0.00   0 0  
channel handle pool latch 1,686 0.00   0 0  
channel operations parent latch 113,264 0.00   0 0  
checkpoint queue latch 569,511 0.00   0 16,740 0.00
client/application info 10,024 0.00   0 0  
compile environment latch 1,145 0.00   0 0  
corrupted undo seg latch 4 0.00   0 0  
cp cmon/server latch 5 0.00   0 0  
cp pool latch 5 0.00   0 0  
cp server hash latch 5 0.00   0 0  
cp sga latch 316 0.00   0 0  
cvmap freelist lock 5 0.00   0 0  
database property service latch 8 0.00   0 0  
deferred cleanup latch 316 0.00   0 0  
dml lock allocation 404 0.00   0 0  
done queue latch 5 0.00   0 0  
dummy allocation 2,297 0.04 0.00 0 0  
enqueue hash chains 349,320 0.00 0.00 0 0  
enqueues 305,205 0.00 0.00 0 0  
fifth spare latch 5 0.00   0 0  
file cache latch 926 0.00   0 0  
flashback copy 5 0.00   0 0  
gc element 5 0.00   0 0  
gcs commit scn state 5 0.00   0 0  
gcs partitioned table hash 5 0.00   0 0  
gcs pcm hashed value bucket hash 5 0.00   0 0  
gcs resource freelist 5 0.00   0 0  
gcs resource hash 5 0.00   0 0  
gcs resource scan list 5 0.00   0 0  
gcs shadows freelist 5 0.00   0 0  
ges domain table 5 0.00   0 0  
ges enqueue table freelist 5 0.00   0 0  
ges group table 5 0.00   0 0  
ges process hash list 5 0.00   0 0  
ges process parent latch 5 0.00   0 0  
ges resource hash list 5 0.00   0 0  
ges resource scan list 5 0.00   0 0  
ges resource table freelist 5 0.00   0 0  
ges value block free list 5 0.00   0 0  
global KZLD latch for mem in SGA 119 0.00   0 0  
global tx hash mapping 5 0.00   0 0  
granule operation 5 0.00   0 0  
hash table column usage latch 1,383 0.00   0 687,044 0.00
hash table modification latch 496 0.00   0 2 0.00
heartbeat check 5 0.00   0 0  
i/o slave adaptor 0     0 2 0.00
internal temp table object number allocation latch 50 0.00   0 0  
intra txn parallel recovery 5 0.00   0 0  
io pool granule metadata list 5 0.00   0 0  
job workq parent latch 350 0.00   0 345 1.16
job_queue_processes free list latch 722 0.00   0 2 0.00
job_queue_processes parameter latch 4,252 0.00   0 0  
k2q lock allocation 5 0.00   0 0  
kcbtsemkid latch 5 0.00   0 0  
kdlx hb parent latch 5 0.00   0 0  
kgb parent 5 0.00   0 0  
kks stats 5,199 0.00   0 0  
kokc descriptor allocation latch 3,122 0.00   0 0  
ksfv messages 5 0.00   0 0  
kss move lock 171 0.00   0 0  
ksuosstats global area 1,215 0.00   0 0  
ksv allocation latch 648 0.00   0 0  
ksv class latch 337 0.00   0 0  
ksv msg queue latch 5 0.00   0 0  
ksz_so allocation latch 1,602 0.00   0 0  
ktm global data 135 0.00   0 0  
kwqbsn:qsga 642 0.00   0 0  
lgwr LWN SCN 10,189 0.00   0 0  
list of block allocation 1,585 0.00   0 0  
loader state object freelist 242 0.00   0 0  
lob segment dispenser latch 5 0.00   0 0  
lob segment hash table latch 65 0.00   0 0  
lob segment query latch 5 0.00   0 0  
lock DBA buffer during media recovery 5 0.00   0 0  
logical standby cache 5 0.00   0 0  
logminer context allocation 5 0.00   0 0  
logminer work area 5 0.00   0 0  
longop free list parent 152 0.00   0 150 0.00
mapped buffers lru chain 5 0.00   0 0  
message pool operations parent latch 54 0.00   0 0  
messages 275,629 0.03 0.00 0 0  
mostly latch-free SCN 10,200 0.09 0.11 0 0  
msg queue latch 5 0.00   0 0  
multiblock read objects 790 0.00   0 2 0.00
name-service namespace bucket 5 0.00   0 0  
ncodef allocation latch 316 0.00   0 0  
object queue header heap 48,024 0.00   0 494 0.00
object queue header operation 130,991 0.00   0 0  
object stats modification 587 0.00   0 0  
parallel query alloc buffer 2,397 0.00   0 0  
parallel query stats 5 0.00   0 0  
parameter list 2,490 0.00   0 0  
parameter table management 2,562 0.00   0 0  
peshm 5 0.00   0 0  
pesom_free_list 5 0.00   0 0  
pesom_hash_node 5 0.00   0 0  
post/wait queue 1,007 0.00   0 612 0.00
process allocation 2,283 0.00   0 800 0.00
process group creation 1,602 0.00   0 0  
process queue 5 0.00   0 0  
process queue reference 5 0.00   0 0  
qmn task queue latch 2,620 8.05 0.91 0 0  
query server freelists 5 0.00   0 0  
queued dump request 60 0.00   0 0  
queuing load statistics 5 0.00   0 0  
recovery domain hash list 5 0.00   0 0  
redo allocation 39,423 0.05 0.00 0 507,562 0.00
redo copy 5 0.00   0 507,564 0.03
redo writing 41,941 0.00   0 0  
resmgr group change latch 2,508 0.00   0 0  
resmgr:active threads 6,269 0.00   0 3,009 0.00
resmgr:actses change group 793 0.00   0 0  
resmgr:actses change state 97 0.00   0 0  
resmgr:free threads list 2,450 0.20 0.00 0 0  
resmgr:plan CPU method 5 0.00   0 0  
resmgr:resource group CPU method 10 0.00   0 0  
resmgr:schema config 3,908 0.00   0 300 0.00
resmgr:session queuing 5 0.00   0 0  
rm cas latch 5 0.00   0 0  
row cache objects 1,784,826 0.00 0.01 0 713 0.00
rules engine aggregate statistics 19 0.00   0 0  
rules engine evaluation context statistics 0     0 2 0.00
rules engine rule set statistics 38 0.00   0 0  
second spare latch 5 0.00   0 0  
sequence cache 1,925 0.00   0 0  
session allocation 7,269 0.00   0 2,420 0.00
session idle bit 7,187,267 0.00 0.00 0 0  
session queue latch 5 0.00   0 0  
session state list latch 2,834 0.00   0 0  
session switching 4,331 0.00   0 0  
session timer 6,636 0.00   0 0  
shared pool 8,922,553 0.00 0.03 0 0  
shared pool sim alloc 111 0.00   0 0  
shared pool simulator 19,960 0.00   0 0  
sim partition latch 5 0.00   0 0  
simulator hash latch 165,619 0.00   0 0  
simulator lru latch 10,284 0.00   0 153,449 0.00
sort extent pool 686 0.00   0 0  
space background state object latch 6 0.00   0 0  
space background task latch 13,463 0.04 0.50 0 11,962 0.00
state object free list 12 0.00   0 0  
statistics aggregation 2,800 0.00   0 0  
tablespace key chain 101 0.00   0 0  
temp lob duration state obj allocation 17 0.00   0 0  
temporary table state object allocation 16 0.00   0 0  
test excl. parent l0 5 0.00   0 0  
test excl. parent2 l0 5 0.00   0 0  
third spare latch 5 0.00   0 0  
threshold alerts latch 673 0.00   0 0  
transaction allocation 4,656 0.00   0 0  
undo global data 34,869 0.00   0 0  
virtual circuit buffers 5 0.00   0 0  
virtual circuit holder 5 0.00   0 0  
virtual circuit queues 5 0.00   0 0  

Back to Latch Statistics 
Back to Top

Latch Sleep Breakdown

  • ordered by misses desc
Latch Name Get Requests Misses Sleeps Spin Gets
shared pool 8,922,553 247 7 241
qmn task queue latch 2,620 211 191 20
row cache objects 1,784,826 87 1 86
mostly latch-free SCN 10,200 9 1 8
space background task latch 13,463 6 3 3
JS Sh mem access 45 1 1 0
OS process allocation 10,306 1 1 0

Back to Latch Statistics 
Back to Top

Latch Miss Sources

  • only latches with sleeps are shown
  • ordered by name, sleeps desc
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
JS Sh mem access jsksGetShMemLatch 0 1 1
OS process allocation ksoreq_submit 0 1 0
PC and Classifier lists for WLM No latch 0 2 0
lgwr LWN SCN kcs023 0 1 0
qmn task queue latch kwqmnmvtsks: delay to ready list 0 188 0
qmn task queue latch kwqmnaddtsk: add task 0 3 0
row cache objects kqreqd: reget 0 1 0
shared pool kghalo 0 5 4
shared pool kghfre 0 2 2
space background task latch ktsjCreateTask 0 1 0

Back to Latch Statistics 
Back to Top

Mutex Sleep Summary

  • ordered by number of sleeps desc
Mutex Type Location Sleeps Wait Time (ms)
Library Cache kglhdgn2 106 838 0
Library Cache kglpin1 4 719 0
Library Cache kglhdgn1 62 119 0
Library Cache kgllkdl1 85 96 -0
Library Cache kglpnal1 90 17 0
Library Cache kglpndl1 95 15 0
Library Cache kglpsl1 38 2 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

  • Total Logical Reads: 3,025,488
  • Captured Segments account for 92.1% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
WLGS USERS SHUJU1   TABLE 641,088 21.19
WLGS USERS SHUJU1   TABLE 629,520 20.81
WLGS USERS SHUJU1   TABLE 98,656 3.26
SYS SYSTEM I_OBJ1   INDEX 98,592 3.26
WLGS USERS SHUJU1   TABLE 94,240 3.11

Back to Segment Statistics 
Back to Top

Segments by Physical Reads

  • Total Physical Reads: 6,899
  • Captured Segments account for 49.6% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SYS SYSTEM IDL_UB2$   TABLE 3,383 49.04
WLGS USERS SHUJU1   TABLE 6 0.09
WLGS USERS SHUJU1   TABLE 4 0.06
SYS SYSAUX SYS_LOB0000006207C00004$$   LOB 4 0.06
SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST   INDEX 3 0.04

Back to Segment Statistics 
Back to Top

Segments by Physical Read Requests

  • Total Physical Read Requests: 909
  • Captured Segments account for 16.3% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Phys Read Requests %Total
SYS SYSTEM IDL_UB2$   TABLE 106 11.66
WLGS USERS SHUJU1   TABLE 6 0.66
WLGS USERS SHUJU1   TABLE 4 0.44
SYS SYSAUX SYS_LOB0000006207C00004$$   LOB 4 0.44
SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST   INDEX 3 0.33

Back to Segment Statistics 
Back to Top

Segments by UnOptimized Reads

  • Total UnOptimized Read Requests: 909
  • Captured Segments account for 16.3% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type UnOptimized Reads %Total
SYS SYSTEM IDL_UB2$   TABLE 106 11.66
WLGS USERS SHUJU1   TABLE 6 0.66
WLGS USERS SHUJU1   TABLE 4 0.44
SYS SYSAUX SYS_LOB0000006207C00004$$   LOB 4 0.44
SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST   INDEX 3 0.33

Back to Segment Statistics 
Back to Top

Segments by Optimized Reads

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by Direct Physical Reads

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by Physical Writes

  • Total Physical Writes: 19,814
  • Captured Segments account for 43.4% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Writes %Total
WLGS USERS SHUJU1   TABLE 1,542 7.78
WLGS USERS SHUJU1   TABLE 1,422 7.18
WLGS USERS SHUJU1   TABLE 1,410 7.12
SYS SYSTEM HISTGRM$   TABLE 897 4.53
WLGS USERS SHUJU1   TABLE 259 1.31

Back to Segment Statistics 
Back to Top

Segments by Physical Write Requests

  • Total Physical Write Requestss: 5,875
  • Captured Segments account for 32.1% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Phys Write Requests %Total
SYS SYSTEM HISTGRM$   TABLE 139 2.37
WLGS USERS SHUJU1   TABLE 112 1.91
WLGS USERS SHUJU1   TABLE 103 1.75
WLGS USERS SHUJU1   TABLE 102 1.74
SYS SYSAUX SMON_SCN_TIME   TABLE 94 1.60

Back to Segment Statistics 
Back to Top

Segments by Direct Physical Writes

  • Total Direct Physical Writes: 175
  • Captured Segments account for 22.9% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Direct Writes %Total
SYS SYSAUX SYS_LOB0000006213C00038$$   LOB 28 16.00
SYS SYSAUX SYS_LOB0000006207C00004$$   LOB 9 5.14
SYS SYSAUX SYS_LOB0000006021C00008$$   LOB 2 1.14
SYS TEMP SYS_TEMP_0FD9D6608_1C4A2C   TABLE 1 0.57

Back to Segment Statistics 
Back to Top

Segments by Table Scans

  • Total Table Scans: 456
  • Captured Segments account for 32.7% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Table Scans %Total
SYS SYSTEM I_OBJ2   INDEX 38 8.33
SYS SYSTEM I_OBJ1   INDEX 16 3.51
SYS SYSAUX WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__142723844_81 INDEX PARTITION 10 2.19
SYS SYSAUX WRH$_SERVICE_WAIT_CLASS_PK WRH$_SERVIC_142723844_81 INDEX PARTITION 10 2.19
SYS SYSAUX WRH$_LATCH_PK WRH$_LATCH_142723844_81 INDEX PARTITION 8 1.75

Back to Segment Statistics 
Back to Top

Segments by DB Blocks Changes

  • % of Capture shows % of DB Block Changes for each top segment compared
  • with total DB Block Changes for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type DB Block Changes % of Capture
WLGS USERS SHUJU1   TABLE 161,696 33.64
WLGS USERS SHUJU1   TABLE 160,896 33.48
SYS SYSTEM HISTGRM$   TABLE 28,448 5.92
SYS SYSTEM I_H_OBJ#_COL#   INDEX 25,568 5.32
WLGS USERS SHUJU1   TABLE 24,368 5.07

Back to Segment Statistics 
Back to Top

Segments by Row Lock Waits

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics 
Back to Top

 

Dictionary Cache Stats

  • "Pct Misses" should be very low (< 2% in most cases)
  • "Final Usage" is the number of cache entries being used
Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
dc_awr_control 376 0.00 0   12 1
dc_constraints 12 33.33 0   12 1
dc_files 50 0.00 0   0 5
dc_global_oids 6,458 0.06 0   0 73
dc_histogram_data 27,276 6.49 0   1,809 5,162
dc_histogram_defs 47,735 11.77 0   2,413 4,076
dc_object_grants 16,880 0.41 0   0 289
dc_objects 62,303 1.35 0   405 2,935
dc_profiles 480 0.00 0   0 1
dc_rollback_segments 4,203 0.00 0   0 22
dc_segments 8,641 2.08 0   321 964
dc_sequences 18 11.11 0   18 9
dc_table_scns 3 100.00 0   0 0
dc_tablespaces 175,762 0.00 0   0 7
dc_users 251,711 0.02 0   0 211
global database name 17,394 0.00 0   0 1
outstanding_alerts 148 6.76 0   19 5
sch_lj_objs 13 23.08 0   0 10
sch_lj_oids 37 10.81 0   0 20


Back to Top

 

Library Cache Activity

  • "Pct Misses" should be very low
Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations
BODY 9,555 0.08 10,047,841 0.00 0 0
CLUSTER 1,128 0.00 295 0.00 0 0
DBLINK 248 0.00 0   0 0
DIRECTORY 1 0.00 2 0.00 0 0
EDITION 544 0.00 1,004 0.00 0 0
INDEX 2,129 0.75 1,766 15.35 151 0
OBJECT ID 86 100.00 0   0 0
PIPE 8,662,861 0.00 13,015,407 -0.01 0 0
QUEUE 300 0.00 1,092 0.00 0 0
RULESET 19 0.00 19 0.00 0 0
SCHEMA 1,245 0.40 0   0 0
SQL AREA 22,289 12.61 7,731,451 0.06 352 600
SUBSCRIPTION 29 0.00 29 0.00 0 0
TABLE/PROCEDURE 48,722 0.64 10,073,894 0.02 627 3
TEMPORARY INDEX 165 5.45 165 100.00 156 0
TEMPORARY TABLE 460 1.52 460 100.00 453 0
TRIGGER 1,020 0.00 1,290 0.00 0 0


Back to Top

 

Memory Statistics

Back to Top

Memory Dynamic Components

  • Min/Max sizes since instance startup
  • Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
  • ordered by Component
Component Begin Snap Size (Mb) Current Size (Mb) Min Size (Mb) Max Size (Mb) Oper Count Last Op Typ/Mod
ASM Buffer Cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 16K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 2K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 32K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 4K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT 8K buffer cache 0.00 0.00 0.00 0.00 0 STA/
DEFAULT buffer cache 1,376.00 1,376.00 1,376.00 1,376.00 0 INI/
KEEP buffer cache 0.00 0.00 0.00 0.00 0 STA/
PGA Target 1,232.00 1,232.00 1,232.00 1,232.00 0 STA/
RECYCLE buffer cache 0.00 0.00 0.00 0.00 0 STA/
SGA Target 1,840.00 1,840.00 1,840.00 1,840.00 0 STA/
Shared IO Pool 0.00 0.00 0.00 0.00 0 STA/
java pool 16.00 16.00 16.00 16.00 0 STA/
large pool 16.00 16.00 16.00 16.00 0 STA/
shared pool 400.00 400.00 400.00 400.00 0 STA/
streams pool 0.00 0.00 0.00 0.00 0 STA/

Back to Memory Statistics 
Back to Top

Memory Resize Operations Summary

No data exists for this section of the report.

Back to Memory Statistics 
Back to Top

Memory Resize Ops

No data exists for this section of the report.

Back to Memory Statistics 
Back to Top

Process Memory Summary

  • B: Begin Snap E: End Snap
  • All rows below contain absolute values (i.e. not diffed over the interval)
  • Max Alloc is Maximum PGA Allocation size at snapshot time
  • Hist Max Alloc is the Historical Max Allocation for still-connected processes
  • ordered by Begin/End snapshot, Alloc (MB) desc
  Category Alloc (MB) Used (MB) Avg Alloc (MB) Std Dev Alloc (MB) Max Alloc (MB) Hist Max Alloc (MB) Num Proc Num Alloc
B Other 56.03   1.70 1.50 6 10 33 33
  Freeable 12.63 0.00 1.15 1.30 5   11 11
  SQL 1.63 1.32 0.09 0.28 1 26 19 14
  PL/SQL 1.12 0.86 0.04 0.06 0 2 31 31
E Other 54.14   1.75 1.77 9 10 31 31
  Freeable 18.94 0.00 1.72 1.52 5   11 11
  PL/SQL 0.90 0.61 0.03 0.07 0 1 29 29
  SQL 0.36 0.12 0.02 0.03 0 26 18 12

Back to Memory Statistics 
Back to Top

SGA Memory Summary

SGA regions Begin Size (Bytes) End Size (Bytes) (if different)
Database Buffers 1,442,840,576  
Fixed Size 2,180,024  
Redo Buffers 16,982,016  
Variable Size 1,744,833,608  

Back to Memory Statistics 
Back to Top

SGA breakdown difference

  • ordered by Pool, Name
  • N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
java free memory 16.00 16.00 0.00
large PX msg pool 7.50 7.50 0.00
large free memory 8.50 8.50 0.00
shared ASH buffers 8.00 8.00 0.00
shared CCUR 18.72 22.92 22.42
shared FileOpenBlock 10.50 10.50 0.00
shared KCB Table Scan Buffer 4.00 4.00 0.00
shared KGLH0 7.33 7.65 4.37
shared KGLHD 9.34 9.86 5.55
shared KGLS 12.97 8.98 -30.78
shared KGLSG 5.02 5.02 0.00
shared PCUR 12.93 16.26 25.76
shared PLDIA 4.83   -100.00
shared PLMCD 9.29 9.80 5.52
shared SQLA 102.78 129.17 25.68
shared db_block_hash_buckets 11.13 11.13 0.00
shared dbwriter coalesce buffer 4.00 4.00 0.00
shared free memory 106.57 73.32 -31.20
shared row cache 7.23 7.23 0.00
  buffer_cache 1,376.00 1,376.00 0.00
  fixed_sga 2.08 2.08 0.00
  log_buffer 16.20 16.20 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 Capture Rate

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

Streams Apply Rate

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 Queue 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

Persistent Queues

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Persistent Queues Rate

No data exists for this section of the report.

Back to Streams Statistics 
Back to Top

Persistent Queue Subscribers

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

 

Shared Server Statistics

Back to Top

Shared Servers Activity

  • Values represent averages for all samples
Avg Total Connections Avg Active Connections Avg Total Shared Srvrs Avg Active Shared Srvrs Avg Total Dispatchers Avg Active Dispatchers
0 0 1 0 1 0

Back to Shared Server Statistics 
Back to Top

Shared Servers Rates

Common Queue Per Sec Disp Queue Per Sec Server Msgs/Sec Server KB/Sec Common Queue Total Disp Queue Total Server Total Msgs Server Total(KB)
0 0 0 0.00 0 0 0 0

Back to Shared Server Statistics 
Back to Top

Shared Servers Utilization

  • Statistics are combined for all servers
  • Incoming and Outgoing Net % are included in %Busy
Total Server Time (s) %Busy %Idle Incoming Net % Outgoing Net %
17,994 0.00 100.00 0.00 0.00

Back to Shared Server Statistics 
Back to Top

Shared Servers Common Queue

No data exists for this section of the report.

Back to Shared Server Statistics 
Back to Top

Shared Servers Dispatchers

  • Ordered by %Busy, descending
  • Total Queued, Total Queue Wait and Avg Queue Wait are for dispatcher queue
  • Name suffixes: "(N)" - dispatcher started between begin and end snapshots "(R)" - dispatcher re-started between begin and end snapshots
Name Avg Conns Total Disp Time (s) %Busy %Idle Total Queued Total Queue Wait (s) Avg Queue Wait (ms)
D000 0.00 17,994 0.00 100.00 0 0  

Back to Shared Server Statistics 
Back to Top

 

init.ora Parameters

  • if IP/Public/Source at End snap is different a '*' is displayed
Parameter Name Begin value End value (if different)
audit_file_dest C:\APP\ADMINISTRATOR\ADMIN\WXH\ADUMP   
audit_trail DB   
compatible 11.2.0.0.0   
control_files C:\APP\ADMINISTRATOR\ORADATA\WXH\CONTROL01.CTL, C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\WXH\CONTROL02.CTL   
db_block_size 8192   
db_domain      
db_name WXH   
db_recovery_file_dest c:\app\Administrator\flash_recovery_area   
db_recovery_file_dest_size 4102029312   
diagnostic_dest C:\APP\ADMINISTRATOR   
dispatchers (PROTOCOL=TCP) (SERVICE=WXHXDB)   
local_listener LISTENER_WXH   
memory_target 3221225472   
open_cursors 300   
processes 150   
remote_login_passwordfile EXCLUSIVE   
undo_tablespace UNDOTBS1   


Back to Top

 

 

 

 

 

 

Dynamic Remastering Stats

No data exists for this section of the report.


Back to Top

 

End of Report