Re: tunning help needed...

Have you run DBMS_STATS on the schema(s)?

Did you run it on SYS or SYSTEM ... and if you haven't ... don't! But if you have that is a major source of such problems.

Daniel Morgan

Stan wrote:

> 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 0
> .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
> -------------------------------------------------------------
> 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
> ---------------------------- ------------ ---------- ----------- ------
> --------
> 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 Second
> per 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
> -> ordered by name, sleeps desc
>
> 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 - 15:10:28 CST