Yes, i used dbms_utility.analyze_schema to gather compute statistics of
all application schemas and I never gather stats for system or sys, as
am very much aware of the performance problems that'd arise.
-Stan
any better reasons in using dbms_stats than analyze compute stats ?
Daniel A. Morgan wrote:
> 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 Sun Mar 24 2002 - 13:02:21 CST
