环境：db：10.2.0.4.0 system：hp-ux 11.31
近期系统在访问高峰时，性能严重下降，分析awr得到信息为
Elapsed: 60.20 (mins)
DB Time: 1,916.95 (mins) 系统负担非常严重
Library Hit %: 99.60 Soft Parse %: 99.44
Execute to Parse %: -22.89 重解析现象很严重
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 26,661 23.2
cursor: pin S 5,346,339 11,727 2 10.2 Other
latch: cache buffers chains 25,963 5,205 200 4.5 Concurrency
db file scattered read 710,601 2,698 4 2.3 User I/O
latch: library cache 6,743 2,087 309 1.8 Concurrenc
cursor: pin S事件为等待时间第一， 一般判读为系统并发sql语句过多，由于没有之前正常状态的awr报告作为对比，所以对于这个系统问题的根源和解决办法不是很清楚，对于应用方面，减少并发sql语句，绑定变量等，在数据库方面调整ession_cached_cursors参数，增加sharepool空间，由于水平有限只能知道这么多，希望得到更加深入的分析。谢谢
附部分awr（如何上传完整报告？）
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
ORCL 1183953527 orcl 1 10.2.0.4.0 NO HP-UX-1
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 36825 14-8ÔÂ -12 09:00:16 1426 1.5
End Snap: 36826 14-8ÔÂ -12 10:00:29 1630 1.4
Elapsed: 60.20 (mins)
DB Time: 1,916.95 (mins)
Report Summary
Cache Sizes
Begin End
Buffer Cache: 9,216M 9,216M Std Block Size: 8K
Shared Pool Size: 6,144M 6,144M Log Buffer: 14,348K
Load Profile
Per Second Per Transaction
Redo size: 85,077.91 5,632.74
Logical reads: 323,798.90 21,437.69
Block changes: 489.98 32.44
Physical reads: 4,190.38 277.43
Physical writes: 36.21 2.40
User calls: 7,273.13 481.53
Parses: 1,499.45 99.27
Hard parses: 8.39 0.56
Sorts: 150.73 9.98
Logons: 1.72 0.11
Executes: 1,220.19 80.78
Transactions: 15.10
% Blocks changed per Read: 0.15 Recursive Call %: 9.01
Rollback per transaction %: 12.68 Rows per Sort: 118.78
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.99 Redo NoWait %: 99.99
Buffer Hit %: 98.71 In-memory Sort %: 100.00
Library Hit %: 99.60 Soft Parse %: 99.44
Execute to Parse %: -22.89 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 6.61 % Non-Parse CPU: 94.79
Shared Pool Statistics
Begin End
Memory Usage %: 92.12 92.19
% SQL with executions>1: 90.33 87.07
% Memory for SQL w/exec>1: 90.37 88.32
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 26,661 23.2
cursor: pin S 5,346,339 11,727 2 10.2 Other
latch: cache buffers chains 25,963 5,205 200 4.5 Concurrency
db file scattered read 710,601 2,698 4 2.3 User I/O
latch: library cache 6,743 2,087 309 1.8 Concurrency
Time Model Statistics
Total time in database user-calls (DB Time): 115017.1s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 89,472.10 77.79
DB CPU 26,660.94 23.18
parse time elapsed 20,759.09 18.05
PL/SQL execution elapsed time 322.11 0.28
hard parse elapsed time 256.97 0.22
connection management call elapsed time 90.04 0.08
failed parse elapsed time 44.23 0.04
hard parse (sharing criteria) elapsed time 27.70 0.02
sequence load elapsed time 13.43 0.01
PL/SQL compilation elapsed time 10.03 0.01
hard parse (bind mismatch) elapsed time 2.82 0.00
repeated bind elapsed time 0.10 0.00
inbound PL/SQL rpc elapsed time 0.05 0.00
DB time 115,017.09
background elapsed time 451.46
background cpu time 47.64
Back to Wait Events Statistics
Back to Top
Wait Class
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
Other 5,359,517 0.06 12,985 2 98.23
Concurrency 41,787 7.97 7,899 189 0.77
User I/O 1,541,553 0.00 4,385 3 28.25
Network 28,263,892 0.00 515 0 518.03
Commit 31,974 1.02 489 15 0.59
Application 7,028 1.04 232 33 0.13
System I/O 41,367 0.00 118 3 0.76
Configuration 48 0.00 8 177 0.00
Back to Wait Events Statistics
Back to Top
Wait Events
s - second
cs - centisecond - 100th of a second
ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
cursor: pin S 5,346,339 0.00 11,727 2 97.99
latch: cache buffers chains 25,963 0.00 5,205 200 0.48
db file scattered read 710,601 0.00 2,698 4 13.02
latch: library cache 6,743 0.00 2,087 309 0.12
db file sequential read 657,512 0.00 1,191 2 12.05
latch free 7,839 0.10 973 124 0.14
read by other session 153,818 0.04 489 3 2.82
log file sync 31,974 1.02 489 15 0.59
SQL*Net more data to client 2,454,333 0.00 308 0 44.98
enq: TX - row lock contention 82 89.02 227 2762 0.00
latch: library cache lock 968 0.00 211 218 0.02
latch: session allocation 1,369 0.00 196 143 0.03
latch: shared pool 1,801 0.00 177 98 0.03
SQL*Net message to client 25,709,776 0.00 130 0 471.22
latch: row cache objects 2,772 0.00 113 41 0.05
log file parallel write 33,654 0.00 105 3 0.62
SQL*Net more data from client 99,783 0.00 77 1 1.83
buffer busy waits 198 24.24 64 325 0.00
latch: cache buffers lru chain 204 0.00 35 173 0.00
cursor: pin S wait on X 3,284 99.97 32 10 0.06
LGWR wait for redo copy 3,493 91.24 32 9 0.06
latch: object queue header operation 91 0.00 8 91 0.00
latch: In memory undo latch 43 0.00 8 191 0.00
db file parallel read 530 0.00 7 14 0.01
enq: SQ - contention 8 0.00 5 670 0.00
log file sequential read 400 0.00 5 13 0.01
SQL*Net break/reset to client 6,942 0.00 5 1 0.13
Streams AQ: qmn coordinator waiting for slave to start 1 100.00 5 4889 0.00
control file parallel write 1,844 0.00 5 2 0.03
latch: enqueue hash chains 55 0.00 4 76 0.00
Log archive I/O 344 0.00 3 9 0.01
wait list latch free 104 0.00 2 20 0.00
latch: undo global data 9 0.00 2 191 0.00
log file switch (checkpoint incomplete) 11 0.00 2 150 0.00
log file switch completion 28 0.00 1 53 0.00
os thread startup 11 0.00 1 132 0.00
control file sequential read 5,093 0.00 0 0 0.09
latch: redo allocation 4 0.00 0 68 0.00
enq: RO - fast object reuse 4 0.00 0 59 0.00
local write wait 50 0.00 0 2 0.00
latch: library cache pin 1 0.00 0 95 0.00
latch: cache buffer handles 1 0.00 0 80 0.00
enq: CF - contention 2 0.00 0 19 0.00
direct path read temp 18,314 0.00 0 0 0.34
log file single write 32 0.00 0 1 0.00
enq: TX - index contention 3 0.00 0 5 0.00
direct path write temp 25 0.00 0 0 0.00
reliable message 4 0.00 0 0 0.00
direct path read 350 0.00 0 0 0.01
Background Wait Events
ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
events in waitclass Other 2,441 130.93 140 57 0.04
log file parallel write 33,654 0.00 105 3 0.62
log file sequential read 400 0.00 5 13 0.01
control file parallel write 1,844 0.00 5 2 0.03
buffer busy waits 4 75.00 3 832 0.00
Log archive I/O 344 0.00 3 9 0.01
os thread startup 11 0.00 1 132 0.00
latch: library cache 3 0.00 1 220 0.00
control file sequential read 4,390 0.00 0 0 0.08
latch: shared pool 1 0.00 0 309 0.00
db file sequential read 82 0.00 0 4 0.00
db file scattered read 56 0.00 0 4 0.00
log file single write 32 0.00 0 1 0.00
latch: library cache lock 1 0.00 0 26 0.00
direct path read 344 0.00 0 0 0.01
direct path write 343 0.00 0 0 0.01
latch: In memory undo latch 1 0.00 0 0 0.00
Back to Wait Events Statistics
Back to Top
Operating System Statistics
Statistic Total
AVG_BUSY_TIME 355,404
AVG_IDLE_TIME 5,624
AVG_IOWAIT_TIME 1,350
AVG_SYS_TIME 24,626
AVG_USER_TIME 330,677
BUSY_TIME 2,844,143
IDLE_TIME 45,639
IOWAIT_TIME 11,363
SYS_TIME 197,893
USER_TIME 2,646,250
LOAD 8
OS_CPU_WAIT_TIME ###############
RSRC_MGR_CPU_WAIT_TIME 0
VM_IN_BYTES 0
VM_OUT_BYTES 0
PHYSICAL_MEMORY_BYTES 51,504,857,088
NUM_CPUS 8
NUM_CPU_SOCKETS 8
Back to Wait Events Statistics
Back to Top
Service Statistics
ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
orcl 108,264.60 24,956.90 12,796,071 1,110,466,840
SYS$USERS 6,748.20 1,707.20 2,350,206 59,196,825
SYS$BACKGROUND 0.00 0.00 1,188 41,361
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 centisecond (100th of a second)
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
orcl 1354220 373545 40097 757402 0 0 26777629 50042
SYS$USERS 186115 64780 1563 27129 0 0 1456144 1238
SYS$BACKGROUND 1221 183 23 584 0 0 0 0
Back to Wait Events 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
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
17,352 1,151 282,032 0.06 15.09 58nqcgq93qdpp w3wp.exe SELECT sysdate FROM dual
13,412 3,344 3,516 3.81 11.66 gpz6zsfjvvgx7 obilling.exe select sum ( costs ) , sum ( ...
9,176 2,771 393 23.35 7.98 ajp712h079d57 dosage.exe select DIAG_DESC from outp_mr ...
8,351 2,411 3,163 2.64 7.26 9skkhx9qfmzjy outpdoct.exe SELECT "OUTP_TREAT_REC"."VIS...
6,278 2,042 2,598 2.42 5.46 f9nfgywd1dzbc outpdoct.exe SELECT :"SYS_B_0" del_indica...
3,406 1,070 729 4.67 2.96 3fgaurmna0hx9 obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
3,228 867 9,221 0.35 2.81 3vy04kpknkqgk w3wp.exe select T1.REMIND_DATETIME, T1....
2,802 514 214 13.09 2.44 f4q13jvt6n8r8 mzcx.exe SELECT DISTINCT "OUTPBILL"."...
2,406 533 725 3.32 2.09 87mazvgqfas1z obilling.exe SELECT "OUTP_TREAT_REC_T"."V...
2,340 535 724 3.23 2.03 f4xrxypn2cpkm obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
2,303 781 15,143 0.15 2.00 89yw129xmhchc w3wp.exe select a.PATIENT_ID, a.VISIT_I...
1,572 449 4,748 0.33 1.37 0nwg8cxxzcb0m w3wp.exe select T1.REMIND_DATETIME, T1....
1,258 135 44 28.58 1.09 cnuj5dstf2559 outpdoct.exe SELECT "DOCT_DRUG_PRESC_DE...
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 DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
3,344 13,412 3,516 0.95 11.66 gpz6zsfjvvgx7 obilling.exe select sum ( costs ) , sum ( ...
2,771 9,176 393 7.05 7.98 ajp712h079d57 dosage.exe select DIAG_DESC from outp_mr ...
2,411 8,351 3,163 0.76 7.26 9skkhx9qfmzjy outpdoct.exe SELECT "OUTP_TREAT_REC"."VIS...
2,042 6,278 2,598 0.79 5.46 f9nfgywd1dzbc outpdoct.exe SELECT :"SYS_B_0" del_indica...
1,151 17,352 282,032 0.00 15.09 58nqcgq93qdpp w3wp.exe SELECT sysdate FROM dual
1,070 3,406 729 1.47 2.96 3fgaurmna0hx9 obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
867 3,228 9,221 0.09 2.81 3vy04kpknkqgk w3wp.exe select T1.REMIND_DATETIME, T1....
781 2,303 15,143 0.05 2.00 89yw129xmhchc w3wp.exe select a.PATIENT_ID, a.VISIT_I...
540 825 1,790 0.30 0.72 cw74b4gm99xd5 ORACLE.EXE SELECT "A4"."PATIENT_ID", TO_C...
535 2,340 724 0.74 2.03 f4xrxypn2cpkm obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
533 2,406 725 0.73 2.09 87mazvgqfas1z obilling.exe SELECT "OUTP_TREAT_REC_T"."V...
514 2,802 214 2.40 2.44 f4q13jvt6n8r8 mzcx.exe SELECT DISTINCT "OUTPBILL"."...
449 1,572 4,748 0.09 1.37 0nwg8cxxzcb0m w3wp.exe select T1.REMIND_DATETIME, T1....
135 1,258 44 3.06 1.09 cnuj5dstf2559 outpdoct.exe SELECT "DOCT_DRUG_PRESC_DE...
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: 1,169,640,302
Captured SQL account for 71.4% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
158,325,351 3,516 45,029.96 13.54 3344.37 13412.17 gpz6zsfjvvgx7 obilling.exe select sum ( costs ) , sum ( ...
142,486,001 3,163 45,047.74 12.18 2410.79 8351.24 9skkhx9qfmzjy outpdoct.exe SELECT "OUTP_TREAT_REC"."VIS...
107,954,245 2,598 41,552.83 9.23 2041.61 6278.17 f9nfgywd1dzbc outpdoct.exe SELECT :"SYS_B_0" del_indica...
54,640,906 729 74,953.23 4.67 1069.81 3405.85 3fgaurmna0hx9 obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
54,464,191 15,143 3,596.66 4.66 781.43 2302.60 89yw129xmhchc w3wp.exe select a.PATIENT_ID, a.VISIT_I...
52,300,799 9,221 5,671.92 4.47 866.73 3228.09 3vy04kpknkqgk w3wp.exe select T1.REMIND_DATETIME, T1....
36,350,381 1,790 20,307.48 3.11 539.85 825.10 cw74b4gm99xd5 ORACLE.EXE SELECT "A4"."PATIENT_ID", TO_C...
28,097,571 1,132 24,821.18 2.40 105.62 304.33 44cpsg5hgwtv3 outpdoct.exe SELECT "CLINIC_MASTER"."NAME...
27,329,179 724 37,747.48 2.34 534.54 2339.79 f4xrxypn2cpkm obilling.exe SELECT "OUTP_PRESC_T"."VISIT...
27,276,384 725 37,622.60 2.33 532.84 2405.61 87mazvgqfas1z obilling.exe SELECT "OUTP_TREAT_REC_T"."V...
26,913,555 4,748 5,668.40 2.30 449.02 1571.69 0nwg8cxxzcb0m w3wp.exe select T1.REMIND_DATETIME, T1....
25,335,084 214 118,388.24 2.17 514.25 2802.12 f4q13jvt6n8r8 mzcx.exe SELECT DISTINCT "OUTPBILL"."...
12,756,154 4 3,189,038.50 1.09 73.99 592.57 4v3jxnrj4duq0 ºǫ́ÅäÒ©ÐÂ2012.07.31.exe SELECT "DRUG_PRESC_MASTER"....
Back to SQL Statistics
Back to Top
SQL ordered by Reads
Total Disk Reads: 15,136,657
Captured SQL account for 81.4% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
6,716,909 214 31,387.43 44.38 514.25 2802.12 f4q13jvt6n8r8 mzcx.exe SELECT DISTINCT "OUTPBILL"."...
3,642,877 44 82,792.66 24.07 134.64 1257.56 cnuj5dstf2559 outpdoct.exe SELECT "DOCT_DRUG_PRESC_DE...
1,707,395 12 142,282.92 11.28 56.42 470.82 bkkm26gd9ydjp prescent.exe SELECT "DRUG_PRESC_MASTER"....
208,221 4 52,055.25 1.38 73.99 592.57 4v3jxnrj4duq0 ºǫ́ÅäÒ©ÐÂ2012.07.31.exe SELECT "DRUG_PRESC_MASTER"....
137,856 0 0.91 383.09 1041.12 b1y7hxw5s572b ORACLE.EXE SELECT /*+ OPAQUE_TRANSFORM */...
45,210 1 45,210.00 0.30 136.24 617.92 9k2mtm738ffbk ORACLE.EXE SELECT "A1"."PATIENT_ID", "A1"...
21,692 0 0.14 44.82 288.67 5w6u29xnk192r ORACLE.EXE SELECT "A1"."TEST_NO", "A1"."I...
2,078 17,010 0.12 0.01 6.78 13.35 3ts97u1my91n4 testprn.exe SELECT "LAB"."LAB_TEST_ITEM...
539 1,732 0.31 0.00 124.85 289.44 0a96nnbzvb78j sjcj.exe select diag_desc , doctor fro...
224 14,499 0.02 0.00 1.75 2.81 adr24vcznhjhz C:\Documents and Settings\yj.ÐÄÄÚʵÑéÊÒ1.000\×À? SELECT COSTS , BILLING_INDICA...
Back to SQL Statistics
Back to Top
SQL ordered by Executions
Total Executions: 4,407,615
Captured SQL account for 43.0% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
324,063 324,042 1.00 0.00 0.00 1jzhjx524wyfm presdisp.exe select TRADE_PRICE , retail_p...
282,032 281,745 1.00 0.00 0.06 58nqcgq93qdpp w3wp.exe SELECT sysdate FROM dual
197,067 197,006 1.00 0.00 0.00 f0wzs9nc663bn obilling.exe select sysdate from dual
162,033 112,966 0.70 0.00 0.00 ak8wcxnbv0mq7 presdisp.exe select location from drug_stoc...
162,032 162,023 1.00 0.00 0.00 g982zswg09n71 presdisp.exe SELECT sum ( DRUG_STOCK.QUANTI...
152,651 1,296 0.01 0.00 0.00 ctc7t080mcrpr PACSVR.exe Select exam_master.*, exam_ma...
89,187 89,186 1.00 0.00 0.00 054n8y7sfgsjc anesmgr.exe select 1 from dual
53,088 24,076 0.45 0.00 0.00 fhzdwv4sgvurz ORACLE.EXE SELECT "A1"."END_DATE_TIME", S...
43,074 991 0.02 0.00 0.00 8kj11spfu1w5k ORACLE.EXE SELECT DISTINCT "A1"."LOG_DATE...
40,962 4,803 0.12 0.00 0.00 664crkjw8n47y doctws.exe select dept_code , ward_code ...
Back to SQL Statistics
Back to Top
SQL ordered by Parse Calls
Total Parse Calls: 5,416,394
Captured SQL account for 41.3% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
457,941 152,651 8.45 ctc7t080mcrpr PACSVR.exe Select exam_master.*, exam_ma...
324,064 324,063 5.98 1jzhjx524wyfm presdisp.exe select TRADE_PRICE , retail_p...
281,992 282,032 5.21 58nqcgq93qdpp w3wp.exe SELECT sysdate FROM dual
197,067 197,067 3.64 f0wzs9nc663bn obilling.exe select sysdate from dual
162,033 162,033 2.99 ak8wcxnbv0mq7 presdisp.exe select location from drug_stoc...
162,032 162,032 2.99 g982zswg09n71 presdisp.exe SELECT sum ( DRUG_STOCK.QUANTI...
113,451 0 2.09 gbztx1wqkp1zw ORACLE.EXE SELECT * FROM "OPERATION_MASTE...
109,344 0 2.02 1gk864rffswjx ORACLE.EXE SELECT * FROM "PAT_VISIT"
104,957 0 1.94 dw9ggd0h86cw4 ORACLE.EXE SELECT * FROM "ADT_LOG"
89,187 89,187 1.65 054n8y7sfgsjc anesmgr.exe select 1 from dual
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
14,285,976 0.22 998p6b7d1q9bz ** SQL Text Not Available **
14,265,528 0.22 dsxd21s5bw5h3 ** SQL Text Not Available **
4,621,517 45 0.07 18h8s79av5v45 w3wp.exe select T1.REMIND_DATETIME, T1....
3,949,845 20 0.06 bn69cjprha24w w3wp.exe select T1.REMIND_DATETIME, T1....
3,407,101 41 0.05 5syt1sugfkg7c w3wp.exe select T1.REMIND_DATETIME, T1....
2,623,013 65 0.04 gh6391dujc994 w3wp.exe select T1.REMIND_DATETIME, T1....
2,314,741 30 0.04 49husy8txxw6h w3wp.exe select T1.REMIND_DATETIME, T1....
2,184,421 87 0.03 5a77mhab8fmq4 w3wp.exe select T1.REMIND_DATETIME, T1....
2,029,197 24 0.03 57wp7yk0ajprt w3wp.exe select T1.REMIND_DATETIME, T1....
2,028,837 5 0.03 csd4pjvrfpxry w3wp.exe select T1.REMIND_DATETIME, T1....
2,024,581 44 0.03 890zsd9cju097 w3wp.exe select T1.REMIND_DATETIME, T1....
1,925,949 39 0.03 cw4y41z3vnz8y w3wp.exe select T1.REMIND_DATETIME, T1....
1,793,389 71 0.03 gwx893aphkzw8 w3wp.exe select T1.REMIND_DATETIME, T1....
1,747,565 19 0.03 4p0vbj8qba0sj w3wp.exe select T1.REMIND_DATETIME, T1....
1,747,253 75 0.03 6hhka0kpch5bq w3wp.exe select T1.REMIND_DATETIME, T1....
1,746,061 60 0.03 cqbyt6m5m9f6g w3wp.exe select T1.REMIND_DATETIME, T1....
1,745,909 47 0.03 aatwks74mtqkx w3wp.exe select T1.REMIND_DATETIME, T1....
1,718,005 20 0.03 cmws54r2xv332 w3wp.exe select T1.REMIND_DATETIME, T1....
1,709,493 24 0.03 71zxrhv747290 w3wp.exe select T1.REMIND_DATETIME, T1....
1,265,905 1,585 0.02 a7c5z25vb0kvz obilling.exe SELECT COLUMN_NAME, DATA_TYPE...
1,132,605 30 0.02 9s6btksh172dz w3wp.exe select T1.REMIND_DATETIME, T1....
1,115,157 30 0.02 9s6btksh172dz w3wp.exe select T1.REMIND_DATETIME, T1....
1,055,029 105 0.02 da0pcbq6qxxqx w3wp.exe select T1.REMIND_DATETIME, T1....
Back to SQL Statistics
Back to Top
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
698 998p6b7d1q9bz ** SQL Text Not Available **
697 dsxd21s5bw5h3 ** SQL Text Not Available **
57 192 9mrhw217pc809 DOCTWS.EXE select MR_CODE , mr_attr , m...
43 1,307 53mkjqn5tdptj examapt.exe SELECT to_char ( last_updt_dat...
32 125 399071urkdx7n admit.exe SELECT to_char ( last_updt_dat...
26 564 14fz7s749tx24 prescent.exe Select drug_spec , batch_no , ...
Back to SQL Statistics
Back to Top
Complete List of SQL Text
SQL Id SQL Text
054n8y7sfgsjc select 1 from dual
0a96nnbzvb78j select diag_desc , doctor from outp_mr where patient_id =:1 and visit_date like visit_date and visit_no =:2
0nwg8cxxzcb0m select T1.REMIND_DATETIME, T1.START_DATETIME, T1.VISIT_DATE, T1.REMIND_TYPE, T1.REMIND_ID, T1.DELETE_FLAG, T1.REPEAT_INTERVAL, T1.VISIT_ID, T1.VISIT_NO, T1.DELETE_USER_ID, T1.DELETE_USER_NAME, T1.PATIENT_ID, T1.REMIND_DEPICT, T1.REMIND_INDEX, T2.REMIND_TYPE_NAME, T1.REMIND_USER_ID, T1.REMIND_USER_NAME from REMIND T1, REMIND_TYPE_DICT T2 where T1.REMIND_TYPE=T2.REMIND_TYPE_CODE(+) and T1.Remind_Type in (:"SYS_B_0") and T1.Delete_Flag=:"SYS_B_1" and T1.Remind_User_Name=:"SYS_B_2" order by T1.Remind_Id
14fz7s749tx24 Select drug_spec , batch_no , quantity , supply_indicator from drug_stock where drug_code =:1 and firm_id =:2 and package_spec =:3 and package_units =:4 and storage =:5 order by batch_no DESC
18h8s79av5v45
sql--省略
Back to SQL 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 26.47 26.47 0.00
java joxlod exec hp 5.34 5.34 0.00
large free memory 32.00 32.00 0.00
shared CCursor 870.37 871.80 0.16
shared Cursor Stats 65.30 65.30 0.00
shared PCursor 506.48 505.60 -0.17
shared db_block_hash_buckets 90.00 90.00 0.00
shared free memory 484.07 480.10 -0.82
shared kglsim object batch 90.30 90.30 0.00
shared library cache 352.64 351.46 -0.33
shared sql area 3,290.12 3,291.57 0.04
streams free memory 16.00 16.00 0.00
buffer_cache 9,216.00 9,216.00 0.00
fixed_sga 1.99 1.99 0.00
log_buffer 14.01 14.01 0.00
Back to Memory Statistics
Back to Top
Resource Limit Stats
only rows with Current or Maximum Utilization > 80% of Limit are shown
ordered by resource name
Resource Name Current Utilization Maximum Utilization Initial Allocation Limit
processes 1,631 1,824 2000 2000
sessions 1,639 1,884 2205 2205
Back to Top
init.ora Parameters
Parameter Name Begin value End value (if different)
audit_file_dest /oracle/product/10.2.0/admin/orcl/adump
background_dump_dest /oracle/product/10.2.0/admin/orcl/bdump
compatible 10.2.0.1.0
control_files /oradata/orcl/control01.ctl, /oradata/orcl/control02.ctl, /oradata/orcl/control03.ctl, /oralog/orcl/control04.ctl
core_dump_dest /oracle/product/10.2.0/admin/orcl/cdump
cursor_sharing FORCE
db_block_size 8192
db_cache_size 9663676416
db_domain
db_file_multiblock_read_count 32
db_name orcl
db_recovery_file_dest /oracle/product/10.2.0/flash_recovery_area
db_recovery_file_dest_size 2147483648
java_pool_size 33554432
job_queue_processes 10
large_pool_size 33554432
log_archive_dest_1 LOCATION=/oralog/arch
nls_language SIMPLIFIED CHINESE
nls_length_semantics CHAR
nls_territory CHINA
open_cursors 300
pga_aggregate_target 2147483648
processes 2000
remote_login_passwordfile EXCLUSIVE
sessions 2205
shared_pool_size 6442450944
streams_pool_size 16777216
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest /oracle/product/10.2.0/admin/orcl/udump
Back to Top
End of Report