在线等待! 帮忙看STATSPACK!!

显示全部楼层 · 2005-10-30 17:05:33

环境参数:
ORA9.0.2/HP UNIX /8G RAM /4 CPU
应用:
与ARCSDE(GIS数据库插件)共用,因此,SQL中有很多移动地图引发的SELECT语句.
STATSPACK参数:
20MIN取样
我怀疑:
1.有参数配置不合理(注意:因为共用服务器,因此,所有的资源要求只能使用50%).服务器管理员抱怨ORA占用太多的CPU(90%)和RAM(80%),但是,数据库的速度还是慢.
2. 程序中没有使用绑定变量.
请问:
该数据库有何问题?该如何解决或查找问题?

千问 · 2005-10-30 17:05:33

千问 · 2005-10-30 17:05:33

STATSPACK report for
DB Name DB IdInstance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
GISPROP 1152282858 GISPROP
1 9.2.0.1.0 NOnmdb

Snap Id Snap TimeSessions Curs/Sess Comment

------- ------------------ -------- --------- -------------------
Begin Snap:22 24-7月 -04 10:44:016010.8
End Snap:33 24-7月 -04 14:24:046510.2
Elapsed:
220.05 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~

Buffer Cache: 1,200MStd Block Size:16K
Shared Pool Size: 384M
Log Buffer: 512K
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction

--------------- ---------------

Redo size:
26,010.71
92,615.80

Logical reads:
8,077.04
28,759.75

Block changes:
199.98
712.06

Physical reads:
1.05
3.73

Physical writes:
2.16
7.69

User calls:
62.06
220.97

Parses:
29.80
106.09

Hard parses:
1.85
6.58

Sorts:
14.27
50.82

Logons:
0.01
0.03

Executes:
44.28
157.65

Transactions:
0.28
% Blocks changed per Read:2.48Recursive Call %:54.29
Rollback per transaction %:0.32 Rows per Sort: 274.98
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %:100.00 Redo NoWait %:100.00

BufferHit %:100.00In-memory Sort %: 99.99

Library Hit %: 97.22Soft Parse %: 93.79
Execute to Parse %: 32.70 Latch Hit %:100.00
Parse CPU to Parse Elapsd %: 99.54 % Non-Parse CPU: 92.64
Shared Pool StatisticsBegin End

------------

Memory Usage %: 92.34 92.30
% SQL with executions>1: 34.66 49.26
% Memory for SQL w/exec>1: 26.85 51.86
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event
WaitsTime (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time
1,26291.72
log file parallel write
6,747
57 4.16
log file sync
2,985
19 1.36
SQL*Net more data to client
427,231
15 1.12
control file parallel write
4,322
12.84

-------------------------------------------------------------
Wait Events for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> 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)

Avg

Total Wait waitWaits
Event
Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write
6,747
0 5781.8
log file sync
2,985
0 1960.8
SQL*Net more data to client 427,231
0 150115.2
control file parallel write 4,322
0 1231.2
log buffer space
38
0
41080.0
process startup
33
0
2 730.0
SQL*Net break/reset to clien
800
0
230.2
db file sequential read
141
0
170.0
latch free
644
0
010.2
db file scattered read
18
0
0 100.0
control file sequential read2,352
0
000.6
log file switch completion
7
0
0 200.0
LGWR wait for redo copy
960
2
000.3
log file single write
12
0
000.0
local write wait
1
0
040.0
direct path read
810
0
000.2
db file parallel write
895
0
000.2
direct path write
136
0
000.0
log file sequential read
12
0
000.0
direct path read (lob)
21
0
000.0
buffer busy waits
2
0
000.0
SQL*Net message from client 510,405
0174,466342137.6
jobq slave wait
7206902,138 29700.2
SQL*Net message to client 510,409
0
00137.7
SQL*Net more data from clien 16,104
0
004.3

-------------------------------------------------------------
Background Wait Events for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait waitWaits
Event
Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write
6,747
0 5781.8
control file parallel write 4,322
0 1231.2
log buffer space
4
0
0 630.0
db file scattered read
13
0
0 100.0
control file sequential read1,776
0
000.5
LGWR wait for redo copy
960
2
000.3
db file sequential read
6
0
060.0
log file sync
25
0
010.0
log file single write
12
0
000.0
db file parallel write
895
0
000.2
latch free
3
0
000.0
direct path read
90
0
000.0
log file sequential read
12
0
000.0
direct path write
90
0
000.0
buffer busy waits
1
0
000.0
rdbms ipc message
22,812 17,108 61,110 26796.2
smon timer
43 42 12,326 ######0.0
SQL*Net message from client 150
0 10,985732320.0
SQL*Net message to client
150
0
000.0

千问 · 2005-10-30 17:05:33

-------------------------------------------------------------
Instance Activity Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
Statistic
Total per Secondper Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session
126,231
9.6 34.0
CPU used when call started
126,230
9.6 34.0
CR blocks created
141
0.0
0.0
DBWR buffers scanned
512
0.0
0.1
DBWR checkpoint buffers written
14,389
1.1
3.9
DBWR checkpoints
3
0.0
0.0
DBWR free buffers found
512
0.0
0.1
DBWR lru scans
2
0.0
0.0
DBWR make free requests
2
0.0
0.0
DBWR summed scan depth
512
0.0
0.1
DBWR transaction table writes
90
0.0
0.0
DBWR undo block writes
7,394
0.6
2.0
PX local messages recv'd
0
0.0
0.0
PX local messages sent
0
0.0
0.0
SQL*Net roundtrips to/from client
510,284 38.7137.6
active txn count during cleanout
3,885
0.3
1.1
background checkpoints completed
3
0.0
0.0
background checkpoints started
3
0.0
0.0
background timeouts
19,733
1.5
5.3
branch node splits
1
0.0
0.0
buffer is not pinned count
106,152,5698,040.0 28,628.0
buffer is pinned count
55,645,3194,214.6 15,006.8
bytes received via SQL*Net from c374,070,438 28,332.2100,882.0
bytes sent via SQL*Net to client 1,154,474,240 87,440.3311,346.9
calls to get snapshot scn: kcmgss
1,250,999 94.8337.4
calls to kcmgas
16,049
1.2
4.3
calls to kcmgcs
540
0.0
0.2
change write time
2,295
0.2
0.6
cleanout - number of ktugct calls
4,081
0.3
1.1
cleanouts and rollbacks - consist
7
0.0
0.0
cleanouts only - consistent read
55
0.0
0.0
cluster key scan block gets
254,068 19.2 68.5
cluster key scans
54,782
4.2 14.8
commit cleanout failures: block l
18
0.0
0.0
commit cleanout failures: buffer
0
0.0
0.0
commit cleanout failures: callbac
0
0.0
0.0
commit cleanout failures: cannot
0
0.0
0.0
commit cleanouts
24,888
1.9
6.7
commit cleanouts successfully com
24,870
1.9
6.7
commit txn count during cleanout
683
0.1
0.2
consistent changes
214
0.0
0.1
consistent gets
105,036,8247,955.5 28,327.1
consistent gets - examination
98,577,3677,466.3 26,585.1
current blocks converted for CR
0
0.0
0.0
cursor authentications
8,831
0.7
2.4
data blocks consistent reads - un
151
0.0
0.0
db block changes
2,640,315
200.0712.1
db block gets
1,604,389
121.5432.7
deferred (CURRENT) block cleanout
12,444
0.9
3.4
dirty buffers inspected
618
0.1
0.2
enqueue conversions
1,050
0.1
0.3
enqueue releases
32,463
2.5
8.8
enqueue requests
33,214
2.5
9.0
enqueue timeouts
740
0.1
0.2
enqueue waits
0
0.0
0.0
execute count
584,566 44.3157.7
Instance Activity Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
Statistic
Total per Secondper Trans
--------------------------------- ------------------ -------------- ------------
free buffer inspected
618
0.1
0.2
free buffer requested
14,248
1.1
3.8
hot buffers moved to head of LRU
6,167
0.5
1.7
immediate (CR) block cleanout app
62
0.0
0.0
immediate (CURRENT) block cleanou
4,458
0.3
1.2
index fast full scans (full)
18
0.0
0.0
index fetch by key
49,565,4563,754.1 13,367.2
index scans kdiixs1
759,912 57.6204.9
leaf node 90-10 splits
90
0.0
0.0
leaf node splits
445
0.0
0.1
logons cumulative
120
0.0
0.0
messages received
7,225
0.6
2.0
messages sent
7,225
0.6
2.0
no buffer to keep pinned count
0
0.0
0.0
no work - consistent read gets
5,304,306
401.81,430.5
opened cursors cumulative
391,490 29.7105.6
parse count (failures)
324
0.0
0.1
parse count (hard)
24,414
1.9
6.6
parse count (total)
393,389 29.8106.1
parse time cpu
9,288
0.7
2.5
parse time elapsed
9,331
0.7
2.5
physical reads
13,827
1.1
3.7
physical reads direct
13,510
1.0
3.6
physical reads direct (lob)
75
0.0
0.0
physical writes
28,519
2.2
7.7
physical writes direct
13,510
1.0
3.6
physical writes direct (lob)
0
0.0
0.0
physical writes non checkpoint
24,258
1.8
6.5
prefetched blocks
83
0.0
0.0
prefetched blocks aged out before
3
0.0
0.0
process last non-idle time
94,886,193,6687,186,714.7 ############
recovery blocks read
0
0.0
0.0
recursive calls
973,197 73.7262.5
recursive cpu usage
8,074
0.6
2.2
redo blocks written
344,042 26.1 92.8
redo buffer allocation retries
43
0.0
0.0
redo entries
1,320,417
100.0356.1
redo log space requests
7
0.0
0.0
redo log space wait time
15
0.0
0.0
redo ordering marks
2,403
0.2
0.7
redo size
343,419,376 26,010.7 92,615.8
redo synch time
1,952
0.2
0.5
redo synch writes
2,999
0.2
0.8
redo wastage
3,373,008
255.5909.7
redo write time
4
0.0
0.0
redo writer latching time
14
0.0
0.0
redo writes
6,747
0.5
1.8
rollback changes - undo records a
89
0.0
0.0
rollbacks only - consistent read
134
0.0
0.0
rows fetched via callback
49,295,1263,733.6 13,294.3
session connect time
94,886,193,6687,186,714.7 ############
session logical reads
106,641,1388,077.0 28,759.8
session pga memory
13,242,0881,003.03,571.2
session pga memory max
7,322,904
554.61,974.9
session uga memory
1,750,920
132.6472.2
session uga memory max
47,287,3603,581.6 12,752.8
Instance Activity Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
Statistic
Total per Secondper Trans
--------------------------------- ------------------ -------------- ------------
shared hash latch upgrades - no w
766,080 58.0206.6
sorts (disk)
23
0.0
0.0
sorts (memory)
188,436 14.3 50.8
sorts (rows)
51,822,0523,925.0 13,975.7
summed dirty queue length
618
0.1
0.2
switch current to new buffer
5,721
0.4
1.5
table fetch by rowid
54,888,0054,157.2 14,802.6
table fetch continued row
57,656
4.4 15.6
table scan blocks gotten
1,693,924
128.3456.8
table scan rows gotten
218,892,302 16,579.0 59,032.4
table scans (long tables)
0
0.0
0.0
table scans (short tables)
285,529 21.6 77.0
transaction rollbacks
11
0.0
0.0
user calls
819,360 62.1221.0
user commits
3,696
0.3
1.0
user rollbacks
12
0.0
0.0
write clones created in foregroun
11
0.0
0.0

-------------------------------------------------------------
Tablespace IO Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->ordered by IOs (Reads + Writes) desc
Tablespace
------------------------------

AvAv Av
AvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
UNDOTBS

3 03.3 1.07,5091
20.0
MAPDB
226 06.8 1.36,2850
00.0
TEMP
1,426 00.5 9.42,4160
00.0
STAT

3 03.3 1.0
8980
00.0
SYSTEM

23 09.1 4.2
3730
00.0
SDE

12 07.5 1.0 310
00.0
TOOLS

3 06.7 1.0
30
00.0

-------------------------------------------------------------
File IO Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->ordered by Tablespace, File
Tablespace
Filename
------------------------ ----------------------------------------------------

AvAv Av
AvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
MAPDB
/oradata/mapdb/mapdb01.dbf

3 06.7 1.0 110
0

/oradata/mapdb/mapdb02.dbf

4 0 22.5 1.0 190
0

/oradata/mapdb/mapdb03.dbf

6 0 10.0 1.0 110
0

/oradata/mapdb/mapdb04.dbf

6 08.3 1.0 110
0

/oradata/mapdb/mapdb05.dbf

5 06.0 1.0 320
0

/oradata/mapdb/mapdb06.dbf

5 08.0 1.0 490
0

/oradata/mapdb/mapdb07.dbf

4 0 12.5 1.0 950
0

/oradata/mapdb/mapdb08.dbf

27 02.2 3.0
1210
0

/oradata/mapdb/mapdb09.dbf

3 06.7 1.0 170
0

/oradata/mapdb/mapdb10.dbf

3 03.3 1.0 110
0

/oradata/mapdb/mapdb11.dbf

5 0 16.0 1.0
1240
0

/oradata/mapdb/mapdb12.dbf

21 07.6 1.1
4630
0

/oradata/mapdb/mapdb13.dbf

13 09.2 1.0
5710
0

/oradata/mapdb/mapdb14.dbf

8 08.8 1.0
6720
0

/oradata/mapdb/mapdb15.dbf

12 0 14.2 1.2
3880
0

/oradata/mapdb/mapdb16.dbf

5 0 20.0 1.0
5840
0

/oradata/mapdb/mapdb17.dbf

10 06.0 1.2
5170
0

/oradata/mapdb/mapdb18.dbf

5 06.0 1.0
6450
0

/oradata/mapdb/mapdb19.dbf

8 05.0 1.3
4910
0

/oradata/mapdb/mapdb20.dbf

19 04.7 1.1
3230
0

/oradata/mapdb/mapdb21.dbf

10 08.0 1.0
3080
0

/oradata/mapdb/mapdb22.dbf

8 03.8 1.0
2880
0

/oradata/mapdb/mapdb23.dbf

3 00.0 1.0
2710
0

/oradata/mapdb/mapdb24.dbf

7 04.3 1.0
1900
0

/oradata/mapdb/mapdb25.dbf

26 01.9 1.0 730
0
SDE
/oradata/mapdb/sde.dbf
File IO Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->ordered by Tablespace, File
Tablespace
Filename
------------------------ ----------------------------------------------------

AvAv Av
AvBuffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/sWaits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------

12 07.5 1.0 310
0
STAT
/oradata/GISPROP/STAT.dbf

3 03.3 1.0
8980
0
SYSTEM
/oradata/GISPROP/system01.dbf

23 09.1 4.2
3730
0
TEMP
/oradata/GISPROP/temp01.dbf
1,426 00.5 9.42,4160
0
TOOLS
/oradata/GISPROP/tools01.dbf

3 06.7 1.0
30
0
UNDOTBS
/oradata/GISPROP/undotbs01.dbf

3 03.3 1.07,5091
20.0

-------------------------------------------------------------
Buffer Pool Statistics for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> Standard block size PoolsD: default,K: keep,R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

FreeWriteBuffer
Number of CacheBufferPhysical PhysicalBuffer CompleteBusy
PBuffers Hit %Gets Reads Writes WaitsWaits Waits
--- ---------- ----- ----------- ----------- ---------- ------- --------------
D 75,600 100.014,522,967 242 15,009 00 2

-------------------------------------------------------------
Instance Recovery Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> B: Begin snapshot,E: End snapshot
Targt Estd
Log File Log Ckpt Log Ckpt
MTTRMTTR RecoveryActual TargetSize TimeoutInterval
(s) (s) Estd IOsRedo BlksRedo BlksRedo BlksRedo BlksRedo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ----------
B5214588 6651 639392160 6393
E5221 136222838228069216022806

-------------------------------------------------------------
Buffer Pool Advisory for DB: GISPROPInstance: GISPROPEnd Snap: 33
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Size forSizeBuffers forEst Physical
Estimated
P Estimate (M) Factr Estimate Read Factor Physical Reads
--- ------------ ----- ---------------- ------------- ------------------
D
112.1
7,056
5.98
348,751
D
224.2 14,112
2.71
158,386
D
336.3 21,168
1.89
110,031
D
448.4 28,224
1.51
88,077
D
560.5 35,280
1.33
77,414
D
672.6 42,336
1.19
69,705
D
784.7 49,392
1.10
63,922
D
896.7 56,448
1.04
60,741
D
1,008.8 63,504
1.02
59,292
D
1,120.9 70,560
1.00
58,642
D
1,200 1.0 75,600
1.00
58,357
D
1,232 1.0 77,616
1.00
58,323
D
1,344 1.1 84,672
1.00
58,311
D
1,456 1.2 91,728
1.00
58,311
D
1,568 1.3 98,784
1.00
58,311
D
1,680 1.4
105,840
1.00
58,311
D
1,792 1.5
112,896
1.00
58,311
D
1,904 1.6
119,952
1.00
58,311
D
2,016 1.7
127,008
1.00
58,311
D
2,128 1.8
134,064
1.00
58,311
D
2,240 1.9
141,120
1.00
58,311

-------------------------------------------------------------

Buffer wait Statistics for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> ordered by wait time desc, waits desc

Tot WaitAvg
Class
Waits Time (s) Time (ms)
------------------ ----------- ---------- ---------
undo header
2
0 0

-------------------------------------------------------------
Rollback Segment Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->A high value for "Pct Waits" suggests more rollback segments may be required
->RBS stats may not be accurate between begin and end snaps when using Auto Undo
managment, as RBS may be dynamically created and dropped as needed
Trans Table Pct Undo Bytes
RBS NoGetsWaits WrittenWrapsShrinksExtends
------ -------------- ------- --------------- -------- -------- --------
0 54.00.00
0000
12,205.00.00 2,903,138503
22,033.00.00 3,559,260704
31,663.00.00 2,882,980502
42,073.00.00 2,747,554502
54,409.00.0043,773,074 440 41
62,604.00.0810,308,258 1209
71,601.00.00 2,229,556503
82,119.00.00 3,569,786603
92,184.00.00 4,051,540705
105,322.00.0045,140,050 462 44

-------------------------------------------------------------
Rollback Segment Storage for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->Optimal Size should be larger than Avg Active
RBS NoSegment SizeAvg ActiveOptimal SizeMaximum Size
------ --------------- --------------- --------------- ---------------
0 507,904
0
507,904
1 4,308,992 605,466
24,231,936
2 5,423,104 594,355
11,649,024
3 4,308,992 590,414
11,714,560
4 4,308,992 622,010
28,426,240
545,203,45632,657,933
54,640,640
611,649,024 3,292,481
12,697,600
7 4,308,992 1,347,705
12,697,600
8 5,357,568 712,146
11,649,024
9 6,406,144 753,507
11,649,024
1048,349,18429,192,553
48,349,184

-------------------------------------------------------------
Undo Segment Summary for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> Undo segment block stats:
-> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
-> eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo UndoNumMax Qry Max Tx Snapshot Out of uS/uR/uU/
TS# BlocksTransLen (s) ConcurcyToo OldSpace eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1
7,8498,231,536 69
200 0/0/0/0/0/0

-------------------------------------------------------------

Undo Segment Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> ordered by Time desc

UndoNum Max Qry Max TxSnap Out of uS/uR/uU/
End Time BlocksTrans Len (s)Concy Too OldSpace eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
24-7月14:2
560377,781 31 00 0/0/0/0/0/0
0
24-7月14:1 34376,809 31 00 0/0/0/0/0/0
0
24-7月14:05,554376,654211 00 0/0/0/0/0/0
0
24-7月13:5 36376,541191 00 0/0/0/0/0/0
0
24-7月13:4 24376,203161 00 0/0/0/0/0/0
0
24-7月13:3 47375,974192 00 0/0/0/0/0/0
0
24-7月13:2
6375,478241 00 0/0/0/0/0/0
0
24-7月13:1 48375,372 31 00 0/0/0/0/0/0
0
24-7月13:0 84375,238 32 00 0/0/0/0/0/0
0
24-7月12:5
110375,033 32 00 0/0/0/0/0/0
0
24-7月12:4 73374,735 31 00 0/0/0/0/0/0
0
24-7月12:3
102374,596 41 00 0/0/0/0/0/0
0
24-7月12:2
400374,398281 00 0/0/0/0/0/0
0
24-7月12:1
507373,839222 00 0/0/0/0/0/0
0
24-7月12:0
9373,020 41 00 0/0/0/0/0/0
0
24-7月11:5 23372,790242 00 0/0/0/0/0/0
0
24-7月11:4 55372,730 71 00 0/0/0/0/0/0
0
24-7月11:3 40371,746691 00 0/0/0/0/0/0
0
24-7月11:2 40371,417 42 00 0/0/0/0/0/0
0
24-7月11:1 57370,917 51 00 0/0/0/0/0/0
0
24-7月11:0
1370,158 41 00 0/0/0/0/0/0
0
24-7月10:5 39370,107 32 00 0/0/0/0/0/0
0

千问 · 2005-10-30 17:05:33

-------------------------------------------------------------
Latch Activity for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->"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

PctAvg Wait
Pct

Get
Get Slps Time NoWait NoWait
Latch
RequestsMiss/Miss(s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA
6,7500.0
0
0
FIB s.o chain latch
240.0
0
0
FOB s.o list latch
4000.0
0
0
SQL memory manager worka
7370.0
0
0
active checkpoint queue 9,4950.0
0
0
archive control
130.0
0
0
cache buffer handles
4,8660.0
0
0
cache buffers chains119,548,2400.00.00 13,9240.0
cache buffers lru chain
21,4230.0
0 13,8930.0
cas latch
6,383,3960.00.00
0
channel handle pool latc
2140.0
0
0
channel operations paren
9,0220.0
0
0
checkpoint queue latch
483,1470.00.00 17,7500.0
child cursor hash table 184,1930.00.00
0
dictionary lookup
10.0
0
0
dml lock allocation
25,9300.0
0
0
dummy allocation
2350.40.00
0
enqueue hash chains
66,7210.0
0
0
enqueues
135,2340.00.00
0
event group latch
1090.0
0
0
file number translation 132,0240.00.00
0
hash table column usage 2,5080.0
0233,5640.0
job workq parent latch
0
0 229.1
job_queue_processes para
2460.0
0
0
ktm global data
450.0
0
0
lgwr LWN SCN
9,7690.10.00
0
library cache
6,595,0200.00.10 68,2800.5
library cache load lock 9,5000.0
0
0
library cache pin
3,469,0890.00.00
0
library cache pin alloca1,842,0060.00.00
0
list of block allocation
6560.0
0
0
loader state object free
1880.0
0
0
messages
60,5200.00.00
0
mostly latch-free SCN
25,8860.20.00
0
multiblock read objects
620.0
0
0
ncodef allocation latch
2100.0
0
0
object stats modificatio
130.0
0
0
post/wait queue
13,5670.0
03,0300.0
process allocation
1090.0
0
1090.0
process group creation
2140.0
0
0
redo allocation
1,336,9480.00.00
0
redo copy
0
01,320,9340.1
redo on-disk SCN
25,4690.0
0
0
redo writing
38,7510.0
0
0
row cache enqueue latch 2,823,9580.00.00
0
row cache objects
2,852,3880.00.00
5340.0
sequence cache
107,8380.0
0
0
session allocation
326,5440.00.00
0
session idle bit
1,752,2070.00.00
0
session switching
2100.0
0
0
Latch Activity for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->"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

PctAvg Wait
Pct

Get
Get Slps Time NoWait NoWait
Latch
RequestsMiss/Miss(s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
session timer
4,4220.0
0
0
shared pool
3,628,9340.10.20
0
sim partition latch
0
0
3030.0
simulator hash latch
6,343,6940.0
0
0
simulator lru latch
8160.0
0 49,5480.0
sort extent pool
5180.0
0
0
temporary table state ob
10.0
0
0
transaction allocation
2910.0
0
0
transaction branch alloc
2100.0
0
0
undo global data
32,9450.0
0
0
user lock
3560.0
0
0

-------------------------------------------------------------
Latch Sleep breakdown for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> ordered by misses desc

Get
Spin &
Latch Name
RequestsMissesSleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
library cache
6,595,020 3,150 224 2938/201/10/

1/0
shared pool
3,628,934 2,687 417 2271/415/1/0

/0
library cache pin
3,469,089
52 2 50/2/0/0/0
session allocation
326,544
24 1 23/1/0/0/0

-------------------------------------------------------------
Latch Miss Sources for DB: GISPROPInstance: GISPROPSnaps: 22 -33
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait
Waiter
Latch Name
Where
Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
library cache
kglic
0 20 42
library cache
kgllkdl: child: cleanup
0
8 14
library cache
kglupc: child
0
6 31
library cache
kglpndl: child: after proc 0
42
library cache
kglpndl: child: before pro 0
3 26
library cache
kglidp: parent
0
20
library cache
kgldti: 2child
0
11
library cache
kglpin: child: heap proces 0
12
library cache
kglhdgc: child:
0
13
library cache
kglpndl: parent: purge 0
10
library cache
kglhdgn: child:
0
1 41
library cache pinkglpndl
0
10
library cache pinkglupc
0
10
session allocation ksuxds: KSUSFCLC not set 0
11
shared pool
kghalo
0298 39
shared pool
kghfrunp: clatch: nowait 01510
shared pool
kghfrunp: alloc: wait
0 780
shared pool
kghupr1
0 39376
shared pool
kghfrunp: clatch: wait 0 252
shared pool
kghalp
0
21

-------------------------------------------------------------
Dictionary Cache Stats for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->"Pct Misses"should be very low ("Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache

GetPctScan PctModFinal
Cache
Requests MissReqsMiss ReqsUsage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_global_oids
120.0 0
0 10
dc_histogram_defs
466,8240.3 0
525,667
dc_object_ids
505,0280.1 0
03,700
dc_objects
88,2490.3 0
2053,371
dc_profiles
870.0 0
0
1
dc_rollback_segments
9480.0 0
0 12
dc_segments
147,0490.3 0
15,213
dc_sequences
1,7730.8 0
1,773 38
dc_tablespace_quotas
100.0 0
10
3
dc_tablespaces
22,4430.0 0
0
7
dc_user_grants
17,0990.0 0
0 20
dc_usernames
45,6540.0 0
0 24
dc_users
134,1980.0 0
0 33

-------------------------------------------------------------

Library Cache Activity for DB: GISPROPInstance: GISPROPSnaps: 22 -33
->"Pct Misses"should be very low

GetPctPinPct
Invali-
Namespace RequestsMiss Requests Miss Reloadsdations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY
7,4080.1
7,4090.1
20
CLUSTER
1,1630.2
1,4620.3
00
INDEX
521.9
26100.0 250
PIPE
77 22.1
77 22.1
00
SQL AREA
389,5444.81,385,3963.44,5771,353
TABLE/PROCEDURE146,1480.6472,9921.01,6180
TRIGGER
10,0050.4 10,0090.7 150

-------------------------------------------------------------
Shared Pool Advisory for DB: GISPROPInstance: GISPROPEnd Snap: 33
-> 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

Estd
Shared PoolSP Estd Estd Estd Lib LC Time
Size forSizeLib CacheLib Cache Cache Time SavedEstd Lib Cache
Estim (M) Factr Size (M)Mem ObjSaved (s) FactrMem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
192.5199 24,102107,730 1.029,512,478
240.6246 29,463107,900 1.029,579,303
288.8293 34,864108,030 1.029,646,887
336.9340 38,865108,138 1.029,730,007
384 1.0387 42,366108,214 1.029,799,120
432 1.1434 46,928108,283 1.029,851,637
480 1.3497 53,324108,329 1.029,889,880
528 1.4544 57,684108,366 1.029,922,952
576 1.5591 62,205108,400 1.029,952,197
624 1.6638 66,031108,431 1.029,978,242
672 1.8685 70,360108,452 1.029,996,613
720 1.9732 75,225108,472 1.030,015,489
768 2.0797 81,428108,501 1.030,034,751

-------------------------------------------------------------
SGA Memory Summary for DB: GISPROPInstance: GISPROPSnaps: 22 -33
SGA regions
Size in Bytes
------------------------------ ----------------
Database Buffers
1,258,291,200
Fixed Size
738,064
Redo Buffers
798,720
Variable Size
570,425,344

----------------
sum
1,830,253,328

-------------------------------------------------------------

SGA breakdown difference for DB: GISPROPInstance: GISPROPSnaps: 22 -33
Pool Name
Begin valueEnd value% Diff
------ ------------------------------ ---------------- ---------------- -------
largefree memory
134,217,728134,217,7280.00
shared 1M buffer
2,099,2002,099,2000.00
shared Checkpoint queue
1,026,5601,026,5600.00
shared FileOpenBlock
3,423,9603,423,9600.00
shared KGK heap
7,000
7,0000.00
shared KGLS heap
3,803,608 11,671,120206.84
shared KQR L PO
8,068,9848,280,4722.62
shared KQR M PO
6,031,2086,154,8722.05
shared KQR S SO
7,936
7,9360.00
shared KSXR receive buffers
1,034,0001,034,0000.00
shared MTTR advisory
167,192
167,1920.00
shared PL/SQL DIANA
911,3361,577,720 73.12
shared PL/SQL MPCODE
753,640
886,608 17.64
shared PL/SQL PPCODE
125,688
236,888 88.47
shared PL/SQL SOURCE
11,744
6,184-47.34
shared PLS non-lib hp
2,056
2,0560.00
shared PX subheap
16,784 16,7840.00
shared dictionary cache
3,221,7603,221,7600.00
shared errors
1,144
230,688 #######
shared event statistics per sess
5,084,0005,084,0000.00
shared fixed allocation callback
544
5440.00
shared free memory
33,392,760 33,573,8960.54
shared joxs heap init
4,240
4,2400.00
shared kgl simulator
22,057,952 22,057,9520.00
shared library cache
74,093,840 82,349,560 11.14
shared message pool freequeue
940,944
940,9440.00
shared miscellaneous
18,184,736 18,314,3360.71
shared parameters
18,984 50,320165.07
shared partitioning d
289,992
573,192 97.66
shared sessions
1,352,0001,352,0000.00
shared sim memory hea
602,136
602,1360.00
shared sql area
248,506,168230,237,384 -7.35
shared table definiti
17,616 24,048 36.51
shared transaction
904,992
904,9920.00
shared trigger defini
40,880 72,800 78.08
shared trigger inform
1,072
1,992 85.82
shared trigger source
168 12,280 #######
buffer_cache
1,258,291,2001,258,291,2000.00
fixed_sga
738,064
738,0640.00
log_buffer
788,480
788,4800.00

-------------------------------------------------------------
Resource Limit Stats for DB: GISPROPInstance: GISPROPEnd Snap: 33
-> only rows with Current or Maximum Utilization > 80% of Limit are shown
-> ordered by resource name

CurrentMaximum Initial
Resource Name
UtilizationUtilizationAllocationLimit
------------------------------ ------------ ------------ ---------- ----------
parallel_max_servers
0
5
6
6

-------------------------------------------------------------
init.ora Parameters for DB: GISPROPInstance: GISPROPSnaps: 22 -33

End value
Parameter Name
Begin value
(if different)
----------------------------- --------------------------------- --------------
background_dump_dest
/oradata/admin/GISPROP/bdump
compatible
9.2.0.0.0
control_files
/oradata/GISPROP/control01.ctl, /
core_dump_dest
/oradata/admin/GISPROP/cdump
db_block_size
16384
db_cache_size
1258291200
db_domain
db_file_multiblock_read_count 16
db_name
GISPROP
db_writer_processes 2
fast_start_mttr_target300
hash_join_enabled
TRUE
instance_name
GISPROP
java_pool_size
0
job_queue_processes 10
large_pool_size
134217728
open_cursors
300
pga_aggregate_target
0
processes
450
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
shared_pool_size
402653184
sort_area_size
2097152
star_transformation_enabled FALSE
timed_statistics
TRUE
undo_management
AUTO
undo_retention
10800
undo_tablespace
UNDOTBS
user_dump_dest
/oradata/admin/GISPROP/udump

-------------------------------------------------------------
End of Report

千问 · 2005-10-30 17:05:33

我看需要把LOG BUFFER调大

千问 · 2005-10-30 17:05:33

你的客户端有多少？你的网络状况如何？局域网还是广域网？

千问 · 2005-10-30 17:05:33

注意这些信息：
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,262 91.72
log file parallel write 6,747 57 4.16
log file sync 2,985 19 1.36
SQL*Net more data to client 427,231 15 1.12
control file parallel write 4,322 12 .84
参考这些等待事件都是因为什么引起的....

千问 · 2005-10-30 17:05:33

典型的CPU密集型应用
你应该优化你的sql,把statspack中的Top n sql进行优化，加快其执行
shared_pool_size可以减少到200M左右
参数设置问题不大 .

千问 · 2005-10-30 17:05:33

最初由 benny2002 发布
[B]你的客户端有多少？你的网络状况如何？局域网还是广域网？ [/B]
网络状况:
客户50
广域网,整个江苏全省啊
网络带宽::电信内部直接用2M专用电路,带宽应该不是问题!!