| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> tunning help needed...
i just did a major export/import of one of production database and we
moved data from hp-ux, oracle 8.1.6 std. edition to sun box with 8.1.7.2
enterpirse edt. now i started to see slowness in the application and
there was no change in the application. infact i have increased more
db_buffers and shared_poool size in the new environment that the old one.
here is my statspack analysis report. i know i need to increase my redolog buffer and sort_area_size, can anyone point out that am missing....
any tuning advise is greatly appreicated. thanks....
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 77.69 In-memory Sort %: 99.41
Library Hit %: 86.63 Soft Parse %: 57.43
Execute to Parse %: 56.65 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: % Non-Parse CPU:
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 73.13 78.14
% SQL with executions>1: 15.21 14.83
% Memory for SQL w/exec>1: 24.97 23.97
Top 5 Wait Events
~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs)Wt Time
-------------------------------------------- ------------ ------------ ------- db file scattered read 14,880 0Wait Events for DB: prod1 Instance: prod1 Snaps: 011- 012
.00
direct path write 4,713 0
.00
db file sequential read 4,704 0
.00
direct path read 3,803 0
.00
SQL*Net more data to client 2,189 0
.00
-------------------------------------------------------------
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------
--------
db file scattered read 14,880 0 0 0
15.1
direct path write 4,713 0 0 0
4.8
db file sequential read 4,704 0 0 0
4.8
direct path read 3,803 0 0 0
3.9
SQL*Net more data to client 2,189 0 0 0
2.2
log file parallel write 1,435 0 0 0
1.5
log file sync 1,288 0 0 0
1.3
control file parallel write 162 0 0 0
0.2
db file parallel write 64 0 0 0
0.1
control file sequential read 53 0 0 0
0.1
file open 29 0 0 0
0.0
latch free 10 9 0 0
0.0
refresh controlfile command 8 0 0 0
0.0
LGWR wait for redo copy 3 0 0 0
0.0
log buffer space 3 0 0 0
0.0
SQL*Net break/reset to clien 2 0 0 0
0.0
SQL*Net message from client 18,885 0 0 0
19.2
SQL*Net message to client 18,885 0 0 0
19.2
virtual circuit status 16 16 0 0
0.0
SQL*Net more data from clien 5 0 0 0
0.0
-------------------------------------------------------------
Background Wait Events for DB: prod1 Instance: prod1 Snaps: 011- 012
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms)
/txn
---------------------------- ------------ ---------- ----------- ------
--------
log file parallel write 1,435 0 0 0
1.5
control file parallel write 162 0 0 0
0.2
db file parallel write 64 0 0 0
0.1
control file sequential read 24 0 0 0
0.0
LGWR wait for redo copy 4 0 0 0
0.0
rdbms ipc message 4,706 482 0 0
4.8
pmon timer 163 163 0 0
0.2
smon timer 1 1 0 0
0.0
Instance Activity Stats for DB: prod1 Instance: prod1 Snaps: 011- 012
Statistic Total per Secondper Trans
--------------------------------- ---------------- ------------
----------------
CR blocks created 5 0.0
0.0
DBWR buffers scanned 0 0.0
0.0
DBWR checkpoint buffers written 489 1.0
0.5
DBWR checkpoints 0 0.0
0.0
DBWR free buffers found 0 0.0
0.0
DBWR lru scans 0 0.0
0.0
DBWR make free requests 0 0.0
0.0
DBWR summed scan depth 0 0.0
0.0
DBWR transaction table writes 0 0.0
0.0
DBWR undo block writes 226 0.5
0.2
SQL*Net roundtrips to/from client 18,879 37.8
19.2
background checkpoints started 0 0.0
0.0
background timeouts 488 1.0
0.5
branch node splits 0 0.0
0.0
buffer is not pinned count 244,099 489.2
248.3
buffer is pinned count 72,696 145.7
74.0
bytes received via SQL*Net from c 1,601,637 3,209.7
1,629.3
bytes sent via SQL*Net to client 6,233,304 12,491.6
6,341.1
calls to get snapshot scn: kcmgss 11,868 23.8
12.1
calls to kcmgas 1,321 2.7
1.3
calls to kcmgcs 41 0.1
0.0
cleanouts and rollbacks - consist 0 0.0
0.0
cleanouts only - consistent read 10 0.0
0.0
cluster key scan block gets 349 0.7
0.4
cluster key scans 154 0.3
0.2
commit cleanout failures: block l 6 0.0
0.0
commit cleanout failures: callbac 1 0.0
0.0
commit cleanout failures: cannot 0 0.0
0.0
commit cleanouts 5,518 11.1
5.6
commit cleanouts successfully com 5,511 11.0
5.6
consistent changes 13 0.0
0.0
consistent gets 488,751 979.5
497.2
cursor authentications 175 0.4
0.2
data blocks consistent reads - un 13 0.0
0.0
db block changes 51,154 102.5
52.0
db block gets 59,916 120.1
61.0
deferred (CURRENT) block cleanout 3,482 7.0
3.5
dirty buffers inspected 0 0.0
0.0
enqueue releases 2,743 5.5
2.8
enqueue requests 2,743 5.5
2.8
enqueue waits 0 0.0
0.0
execute count 9,314 18.7
9.5
free buffer inspected 0 0.0
0.0
free buffer requested 110,342 221.1
112.3
hot buffers moved to head of LRU 43 0.1
0.0
immediate (CR) block cleanout app 10 0.0
0.0
immediate (CURRENT) block cleanou 94 0.2
0.1
index fast full scans (full) 21 0.0
0.0
leaf node splits 26 0.1
0.0
logons cumulative 3 0.0
0.0
logons current
messages received 1,517 3.0
1.5
messages sent 1,517 3.0
1.5
no buffer to keep pinned count 140,717 282.0
143.2
no work - consistent read gets 199,274 399.4
202.7
opened cursors cumulative 3,507 7.0
3.6
opened cursors current
parse count (hard) 1,719 3.4
1.8
parse count (total) 4,038 8.1
4.1
physical reads 122,431 245.4
124.6
physical reads direct 12,529 25.1
12.8
physical writes 13,018 26.1
13.2
physical writes direct 12,529 25.1
12.8
physical writes non checkpoint 13,014 26.1
13.2
prefetched blocks 90,319 181.0
91.9
prefetched blocks aged out before 0 0.0
0.0
recursive calls 10,383 20.8
10.6
redo blocks written 14,099 28.3
14.3
redo buffer allocation retries 3 0.0
0.0
redo entries 25,509 51.1
26.0
redo log space requests 0 0.0
0.0
redo size 6,529,240 13,084.7
6,642.2
redo synch writes 1,287 2.6
1.3
redo wastage 456,268 914.4
464.2
redo writes 1,434 2.9
1.5
rollback changes - undo records a 0 0.0
0.0
rollbacks only - consistent read 5 0.0
0.0
rows fetched via callback 12,796 25.6
13.0
session logical reads 548,667 1,099.5
558.2
session pga memory 2,044,132 4,096.5
2,079.5
session pga memory max 2,706,572 5,424.0
2,753.4
session uga memory 65,824 131.9
67.0
session uga memory max 931,680 1,867.1
947.8
sorts (disk) 19 0.0
0.0
sorts (memory) 3,179 6.4
3.2
sorts (rows) 615,835 1,234.1
626.5
summed dirty queue length 0 0.0
0.0
switch current to new buffer
table fetch by rowid 76,726 153.8
78.1
table fetch continued row 1 0.0
0.0
table scan blocks gotten 137,870 276.3
140.3
table scan rows gotten 27,222,785 54,554.7
27,693.6
table scans (long tables) 9 0.0
0.0
table scans (short tables) 846 1.7
0.9
total file opens 28 0.1
0.0
user calls 22,319 44.7
22.7
user commits 952 1.9
1.0
user rollbacks 31 0.1
0.0
write clones created in foregroun 0 0.0
0.0
-------------------------------------------------------------
Pct Avg Pct
Get Get Slps NoWait
NoWait
Latch Name Requests Miss /Miss Requests
Miss
----------------------------- -------------- ------ ------ ------------
------
Token Manager 28 0.0 0
active checkpoint queue latch 224 0.0 0
cache buffer handles 39 0.0 0
cache buffers chains 1,051,226 0.0 0.0 205,823
0.0
cache buffers lru chain 223,441 0.0 0.0 0
channel handle pool latch 5 0.0 0
channel operations parent lat 8 0.0 0
checkpoint queue latch 3,972 0.0 0.0 0
dml lock allocation 2,684 0.0 0
enqueue hash chains 5,490 0.0 0
enqueues 8,783 0.0 0.0 0
event group latch 3 0.0 0
job_queue_processes parameter 8 0.0 0
ktm global data 1 0.0 0
latch wait list 10 0.0 10
0.0
library cache 131,738 0.0 0.2 0
library cache load lock 140 0.0 0
list of block allocation 2,858 0.0 0
loader state object freelist 62 0.0 0
longop free list 11 0.0 0
messages 11,302 0.1 0.0 0
multiblock read objects 34,156 0.0 0
ncodef allocation latch 8 0.0 0
process allocation 3 0.0 3
0.0
process group creation 5 0.0 0
redo allocation 28,465 0.0 0.0 0
redo writing 7,315 0.5 0.0 0
row cache objects 178,431 0.0 0.0 0
sequence cache 696 0.0 0
session allocation 6,269 0.0 0.0 0
session idle bit 47,767 0.0 0
session switching 8 0.0 0
session timer 163 0.0 0
shared pool 60,645 0.0 0.0 0
sort extent pool 129 0.0 0
transaction allocation 3,974 0.0 0
transaction branch allocation 8 0.0 0
undo global data 4,189 0.0 0
user lock 10 0.0 0
virtual circuit queues 24 0.0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: prod1 Instance: prod1 Snaps: 011- 012
-> ordered by misses desc
Get Spin &
Latch Name Requests Misses Sleeps Sleeps
1->4
-------------------------- -------------- ----------- -----------
------------
library cache 131,738 41 10
31/10/0/0/0
-------------------------------------------------------------
Latch Miss Sources for DB: prod1 Instance: prod1 Snaps: 011- 012
-> only latches with sleeps are shown
NoWait
Waiter
Latch Name Where Misses Sleeps
Sleeps
------------------------ -------------------------- ------- ----------
-------
library cache kglic 0 9
0
library cache kglpnal: child: alloc spac 0 1
1
-------------------------------------------------------------
Dictionary Cache Stats for DB: prod1 Instance: prod1 Snaps: 011- 012
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache
Get Pct Scan Pct Mod
Final Pct
Cache Requests Miss Requests Miss Req
Usage SGA
---------------------- ------------ ------ -------- ----- --------
------ ----
dc_constraints 0 0 0
0 0
dc_database_links 0 0 0
0 0
dc_files 33 39.4 0 0
13 65
dc_free_extents 1 0.0 0 0
1 9
dc_global_oids 0 0 0
0 0
dc_histogram_data 0 0 0
0 0
dc_histogram_data_valu 0 0 0
0 0
dc_histogram_defs 10,937 0.3 0 0
738 100
dc_object_ids 29,329 0.0 0 0
271 98
dc_objects 2,579 1.0 0 0
396 99
dc_outlines 0 0 0
0 0
dc_profiles 3 0.0 0 0
1 25
dc_rollback_segments 81 0.0 0 0
19 76
dc_segments 10,628 0.0 0 0
294 95
dc_sequence_grants 22 0.0 0 0
22 96
dc_sequences 348 0.0 0 342
30 79
dc_synonyms 26 30.8 0 0
36 92
dc_tablespace_quotas 0 0 0
0 0
dc_tablespaces 54 13.0 0 0
9 45
dc_used_extents 0 0 0
158 99
dc_user_grants 2,847 0.1 0 0
12 67
dc_usernames 1,122 0.1 0 0
6 29
dc_users 2,894 0.0 0 0
14 88
ifs_acl_cache_entries 0 0 0
0 0
-------------------------------------------------------------
Library Cache Activity for DB: prod1 Instance: prod1 Snaps: 011- 012 ->"Pct Misses" should be very low
Get Pct Pin Pct
Invali-
Namespace Requests Miss Requests Miss Reloads
dations
BODY 1 0.0 1 0.0 0
0
CLUSTER 13 0.0 2 0.0 0
0
INDEX 0 0 0
0
OBJECT 0 0 0
0
PIPE 0 0 0
0
SQL AREA 4,022 42.6 15,064 22.8 7
0
TABLE/PROCEDURE 4,781 1.0 11,091 0.6 0
0
TRIGGER 21 0.0 21 0.0 0
0
-------------------------------------------------------------
Received on Sat Mar 23 2002 - 21:34:36 CST
 |
 |