oracle查效能,请教各位班主oracle 效能问题

最新推荐文章于 2021-04-08 10:13:17 发布

蜜桃厨房

最新推荐文章于 2021-04-08 10:13:17 发布

阅读量248

点赞数

文章标签： oracle查效能

教各位班主oracle 效能问题

謝謝

statpack 如下分3次(3)

Latch Activity for DB: VHGP Instance: vhgp Snaps: 81 -92

->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for

willing-to-wait latch get requests

->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests

->"Pct Misses" for both should be very close to 0.0

Pct Avg Pct

Get Get Slps NoWait NoWait

Latch Name Requests Miss /Miss Requests Miss

----------------------------- -------------- ------ ------ ------------ ------

Token Manager 536 0.0 636 0.0

active checkpoint queue latch 2,044 0.0 0

cache buffer handles 25,152,946 1.2 0.0 0

cache buffers chains 33,480,734 0.0 0.1 11,083,364 0.0

cache buffers lru chain 6,106,158 0.3 0.1 6,051,725 0.3

channel handle pool latch 143 0.0 139 0.0

channel operations parent lat 286 0.0 139 0.0

checkpoint queue latch 31,980 0.0 1.0 0

dml lock allocation 2,410 0.0 0

enqueue hash chains 8,637 0.0 0.0 0

enqueues 27,649 0.0 0.0 0

event group latch 143 0.0 0

file number translation table 41 0.0 0

job_queue_processes parameter 107 0.0 0

ktm global data 6 0.0 0

latch wait list 3,632 0.1 0.0 3,695 0.1

library cache 428,042 0.7 2.5 10,914 5.1

library cache load lock 998 0.0 0

list of block allocation 2,210 0.0 3.0 0

loader state object freelist 200 0.0 0

longop free list 136,105 0.0 0.9 0

messages 26,226 0.1 0.7 0

multiblock read objects 1,984,432 0.1 0.1 1 0.0

ncodef allocation latch 107 0.0 0

process allocation 143 0.0 143 0.0

process group creation 282 0.0 0

redo allocation 23,309 0.0 1.0 0

redo copy 4 0.0 21,212 0.0

redo writing 7,314 0.1 0.0 0

row cache objects 1,308,160 0.1 0.1 664 0.2

sequence cache 475 0.0 0

session allocation 31,728 0.0 4.5 0

session idle bit 186,465 0.0 0.5 0

session switching 107 0.0 0

shared pool 210,526 0.6 3.0 0

sort extent pool 851 0.0 0

transaction allocation 3,148 0.0 0

transaction branch allocation 107 0.0 0

undo global data 5,132 0.0 0

user lock 1,664 0.1 1.0 0

-------------------------------------------------------------

Latch Sleep breakdown for DB: VHGP Instance: vhgp Snaps: 81 -92

-> ordered by misses desc

Get Spin &

Latch Name Requests Misses Sleeps Sleeps 1->4

-------------------------- -------------- ----------- ----------- ------------

cache buffer handles 25,152,946 295,217 2,768 292503/2673/ 35/6/0

cache buffers lru chain 6,106,158 19,085 1,070 18030/1046/5

/4/0

cache buffers chains 33,480,734 10,634 1,036 9945/550/56/

83/0

library cache 428,042 2,941 7,310 597/350/895/

1099/0

multiblock read objects 1,984,432 1,557 185 1457/85/3/12

shared pool 210,526 1,318 3,948 376/35/279/6

28/0

row cache objects 1,308,160 835 54 810/14/3/8/0

messages 26,226 35 26 11/22/2/0/0

longop free list 136,105 7 6 1/6/0/0/0

session allocation 31,728 6 27 2/0/0/4/0

redo allocation 23,309 5 5 3/1/0/1/0

session idle bit 186,465 2 1 1/1/0/0/0

checkpoint queue latch 31,980 1 1 0/1/0/0/0

list of block allocation 2,210 1 3 0/0/0/1/0

user lock 1,664 1 1 0/1/0/0/0

-------------------------------------------------------------

Latch Miss Sources for DB: VHGP Instance: vhgp Snaps: 81 -92

-> only latches with sleeps are shown

-> ordered by name, sleeps desc

NoWait Waiter

Latch Name Where Misses Sleeps Sleeps

------------------------ -------------------------- ------- ---------- -------

cache buffer handles kcbzgs 0 1,575 690

cache buffer handles kcbzfs 0 1,193 2,078

cache buffers chains kcbgtcr: kslbegin 0 709 913

cache buffers chains kcbrls: kslbegin 0 221 50

cache buffers chains kcbzib: multi-block read: 0 50 0

cache buffers chains kcbbxsv 0 35 0

cache buffers chains kcbzwb 0 9 14

cache buffers chains kcbgcur: kslbegin 0 5 1

cache buffers chains kcbzgb: scan from tail. no 0 4 0

cache buffers chains kcbzib: finish free bufs 0 3 55

cache buffers lru chain kcbzgb: multiple sets nowa 0 737 0

cache buffers lru chain kcbzar: KSLNBEGIN 0 178 962

cache buffers lru chain kcbbiop: lru scan 0 126 0

cache buffers lru chain kcbbwlru 0 15 0

cache buffers lru chain kcbzgm 0 14 107

checkpoint queue latch kcbk0rrd: update recovery 0 1 0

cost function kzulgt: find user res 0 1 0

library cache kglpnal: child: alloc spac 0 1,230 1,438

library cache kglupc: child 0 1,180 1,094

library cache kglhdgc: child: 0 600 47

library cache kglic 0 573 51

library cache kgllkdl: child: cleanup 0 532 26

library cache kglhdgn: child: 0 481 1,024

library cache kglobpn: child: 0 254 110

library cache kglpnal: child: before pro 0 149 546

library cache kgldti: 2child 0 58 103

library cache kgllkdl: child: free pin 0 34 542

library cache kglati 0 30 4

library cache kglpin 0 6 27

library cache kglpnc: child 0 6 56

library cache kglpnp: child 0 3 2

list of block allocation ktlabl 0 3 3

longop free list ksuloget 0 6 6

messages ksaamb: after wakeup 0 26 26

multiblock read objects kcbzib: MBRGET 0 99 92

multiblock read objects kcbzib: MBRFRE 0 40 93

redo allocation kcrfwr: redo allocation 0 5 0

row cache objects kqrpre: find obj 0 52 46

session allocation kspallmod 0 27 0

session idle bit ksupuc: clear busy 0 1 0

shared pool kghfrunp: alloc: clatch no 0 3,440 0

shared pool kghfrunp: clatch: nowait 0 2,061 0

shared pool kghalo 0 282 1,116

shared pool kghfrunp: clatch: wait 0 159 828

shared pool kghupr1 0 80 2,371

shared pool kghfrunp: alloc: wait 0 59 26

shared pool kghfre 0 47 13

shared pool kghfen: not perm alloc cla 0 16 7

shared pool kghfnd: req scan 0 11 0

shared pool kghfnd: get next extent 0 10 0

shared pool kghfnd: min scan 0 2 0

shared pool kghfrunp: no latch 0 1 0

Latch Miss Sources for DB: VHGP Instance: vhgp Snaps: 81 -92

-> only latches with sleeps are shown

-> ordered by name, sleeps desc

NoWait Waiter

Latch Name Where Misses Sleeps Sleeps

------------------------ -------------------------- ------- ---------- -------

Dictionary Cache Stats for DB: VHGP Instance: vhgp Snaps: 81 -92

->"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 0 0 0 25 81

dc_free_extents 57 5.3 3 0.0 9 24 89

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 62,532 2.3 0 0 2,225 100

dc_object_ids 61,547 0.1 0 0 440 99

dc_objects 5,979 2.7 0 0 407 100

dc_outlines 0 0 0 0 0

dc_profiles 142 0.0 0 0 1 25

dc_rollback_segments 399 0.0 0 18 20 83

dc_segments 20,566 0.5 0 3 485 99

dc_sequence_grants 0 0 0 0 0

dc_sequences 8 0.0 0 8 4 57

dc_synonyms 115 7.8 0 0 69 99

dc_tablespace_quotas 12 8.3 0 3 5 83

dc_tablespaces 106 0.0 0 0 14 67

dc_used_extents 3 100.0 0 3 792 100

dc_user_grants 211,066 0.0 0 0 13 93

dc_usernames 635 0.0 0 0 4 33

dc_users 213,360 0.0 0 0 18 82

ifs_acl_cache_entries 0 0 0 0 0

-------------------------------------------------------------

Library Cache Activity for DB: VHGP Instance: vhgp Snaps: 81 -92

->"Pct Misses" should be very low

Get Pct Pin Pct Invali-

Namespace Requests Miss Requests Miss Reloads dations

--------------- ------------ ------ -------------- ------ ---------- --------

BODY 17 0.0 16 12.5 2 0

CLUSTER 129 0.0 103 0.0 0 0

INDEX 0 0 0 0

OBJECT 0 0 0 0

PIPE 0 0 0 0

SQL AREA 7,843 31.9 49,096 11.2 454 0

TABLE/PROCEDURE 12,242 0.6 23,765 2.6 329 0

TRIGGER 120 0.0 120 2.5 1 0

-------------------------------------------------------------

SGA Memory Summary for DB: VHGP Instance: vhgp Snaps: 81 -92

SGA regions Size in Bytes

------------------------------ ----------------

Database Buffers 503,316,480

Fixed Size 116,860

Redo Buffers 270,336

Variable Size 180,531,200

----------------

sum 684,234,876

-------------------------------------------------------------

SGA breakdown difference for DB: VHGP Instance: vhgp Snaps: 81 -92

Pool Name Begin value End value Difference

----------- ------------------------ -------------- -------------- -----------

java pool free memory 20,000,768 20,000,768 0

shared pool Checkpoint queue 672,224 672,224 0

shared pool KGFF heap 17,424 18,024 600

shared pool KGK heap 49,896 50,496 600

shared pool KQLS heap 7,479,792 6,897,400 -582,392

shared pool PL/SQL DIANA 485,024 399,432 -85,592

shared pool PL/SQL MPCODE 463,696 232,112 -231,584

shared pool PLS non-lib hp 2,136 2,136 0

shared pool SYSTEM PARAMETERS 105,376 105,376 0

shared pool State objects 785,120 785,120 0

shared pool character set memory 130,432 130,432 0

shared pool db_block_buffers 25,559,040 25,559,040 0

shared pool db_block_hash_buckets 4,120,752 4,120,752 0

shared pool db_files 603,456 603,456 0

shared pool db_handles 264,000 264,000 0

shared pool dictionary cache 3,592,832 2,699,328 -893,504

shared pool dlo fib struct 96,032 96,032 0

shared pool enqueue_resources 229,320 229,320 0

shared pool event statistics per ses 1,163,120 1,163,120 0

shared pool file # translation table 96,056 96,056 0

shared pool fixed allocation callbac 1,344 1,344 0

shared pool free memory 30,278,440 31,240,896 962,456

shared pool library cache 47,967,752 45,147,352 -2,820,400

shared pool long op statistics array 124,000 124,000 0

shared pool message pool freequeue 191,192 191,192 0

shared pool messages 62,400 62,400 0

shared pool miscellaneous 1,660,608 1,685,760 25,152

shared pool processes 321,600 321,600 0

shared pool sessions 814,720 814,720 0

shared pool simulator trace entries 80,000 80,000 0

shared pool sql area 33,047,960 36,668,864 3,620,904

shared pool table columns 34,552 33,208 -1,344

shared pool table definiti 9,672 11,264 1,592

shared pool trigger inform 224 224 0

db_block_buffers 503,316,480 503,316,480 0

fixed_sga 116,860 116,860 0

log_buffer 262,144 262,144 0

-------------------------------------------------------------

init.ora Parameters for DB: VHGP Instance: vhgp Snaps: 81 -92

End value

Parameter Name Begin value (if different)

----------------------------- --------------------------------- --------------

_db_handles_cached 0

background_dump_dest $ORACLE_HOME/admin/vhgp/bdump

compatible 8.1.7

control_files /u01/oradata/vhgp/control1, /u01/

core_dump_dest $ORACLE_HOME/admin/vhgp/cdump

db_block_buffers 122880

db_block_lru_latches 4

db_block_size 4096

db_file_multiblock_read_count 12

db_files 1500

db_name vhgp

global_names FALSE

log_buffer 262144

log_checkpoint_interval 10000

max_dump_file_size 10240

open_cursors 300

parallel_max_servers 5

processes 300

rollback_segments rbs01, rbs02, rbs03, rbs04, rbs05

session_cached_cursors 100

shared_pool_reserved_size 12582912

shared_pool_size 125829120

sort_area_retained_size 655360

sort_area_size 2048000

timed_statistics FALSE TRUE

transactions 40

transactions_per_rollback_seg 5

user_dump_dest $ORACLE_HOME/admin/vhgp/udump

utl_file_dir /u01/vtd, /u02/vtd