You have an awful lot of FTS going on by the looks of it. Are all the
indexes in place?
Also are the optimiser modes the same?
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Stan" <stan0074_at_yahoo.com> wrote in message
news:3C9D63AC.7020300_at_yahoo.com...
> couple of things,
>
> after import, i re-analyzed the necessary application schemas and this
> time i have imported into an lmt with uniform sizes, which were not in
> the old environment, it use to be dict. managed. I dont think lmt is
> going to be a cause for this slowness for sure, as i have sized up
> extents accordingly to be in few hundreds. As far as the comparison of
> boxes goes, both were in raid 5 and infact sun box, sol8 is more
> powerful then hp box.
>
> Gadgets wrote:
>
> > Hi,
> >
> > Do you have the stats from the previous 8.1.6 instance? Do you still
have
> > it up and running on the HP box? A couple of ideas -
> >
> > * You may not be picking up on an index.
> > * After the export did you re-analyze the database? You could be
running on
> > the default stats.
> > * How is the SUN box set up as compared to the HP-UX one? Need some
more
> > info. Silly example, but did you move from a RAID 0+1 to a RAID 5 unit
on
> > the SUN and your DB is write intensive?
> >
> > I'd start from the OS and work up to the database. OS tools give you an
> > overall picture of what is happening and then you can zero in. Start
with:
> >
> > vmstat, mpstat & iostat
> >
> > Regards,
> > Kyle
> > "Stan" <stan0074_at_yahoo.com> wrote in message
> > news:3C9D49FA.5050901_at_yahoo.com...
> >
> >>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 Mon Mar 25 2002 - 04:38:48 CST
