Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: tunning help needed...
It seems as the instance users almost half of it's time parsing statements.
My experience is when Oracle adds new features, the optimizer uses more time
to find a good plan.
Try to run rule based optimization on single statements(hints), that can
give instant access.
I have seen 15 minutes optimization and 10 sec. data retreval.
Regards
Kjell
"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 Sun Mar 24 2002 - 02:37:50 CST