it世界 mysql优化_一次数据库的简单性能优化

一次数据库的简单性能优化:

增加INDEX表空间,增大在线归档日志组文件,增大在物理内存允许范围内sag_target,增大log_buffer

=========================================================

1、建立专用Index表空间

=========================================================

CREATE TABLESPACE INDX DATAFILE

'/home/oracle/oracle/product/10.2.0/oradata/gdimp/INDEX.dbf' SIZE 5120M AUTOEXTEND OFF

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

=========================================================

2、生成控制文件的.trc并获取其中的内容

=========================================================

alter database backup controlfile to trace;

* GROUP 1 '/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo01.log' SIZE 50M,

* GROUP 2 '/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo02.log' SIZE 50M,

* GROUP 3 '/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo03.log' SIZE 50M

=========================================================

3、增加替换日志组文件

=========================================================

alter database add logfile group 4 ('/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo04.log') size 50M;

alter database add logfile group 5 ('/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo05.log') size 50M;

=========================================================

4、检查log日志组的状态

=========================================================

select * from v$log;

=========================================================

5、调整日志组的status 为inactive,并drop掉要增大日志组

=========================================================

alter system switch logfile;

alter system switch logfile;

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

=========================================================

6、操作系统下删除原日志组1、2、3中的文件

=========================================================

rm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo01.log

rm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo02.log

rm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo03.log

=========================================================

7、重建日志组1、2、3

=========================================================

alter database add logfile group 1 ('/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo01.log') size 500M;

alter database add logfile group 2 ('/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo02.log') size 500M;

alter database add logfile group 3 ('/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo03.log') size 500M;

=========================================================

8、切换日志组

=========================================================

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

=========================================================

9、删除中间过渡用的日志组4、5

=========================================================

alter database drop logfile group 4;

alter database drop logfile group 5;

=========================================================

10、到操作系统下删除原日志组4、5中的文件

=========================================================

rm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo04.log

rm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo05.log

=========================================================

11、备份当前的最新的控制文件

=========================================================

SQL> connect internal

SQL> alter database backup controlfile to trace;

=========================================================

12、保存初始化参数并调整初始化参数sga_target,log_buffer

=========================================================

create pfile from spfile;

alter system set sga_target=1024M scope=spfile

alter system set log_buffer=20480K scope=spfile

=========================================================

13、对用户模式下进行统计

=========================================================

exec dbms_stats.gather_schema_stats(ownname => 'gdimp', options => 'GATHER AUTO', estimate_percent =>

dbms_stats.auto_sample_size,method_opt => 'for all columns size repeat', degree => 15 );

exec dbms_stats.gather_schema_stats(ownname => 'imp', options => 'GATHER AUTO', estimate_percent =>

dbms_stats.auto_sample_size,method_opt => 'for all columns size repeat', degree => 15 );

=========================================================

14、错误处理1

=========================================================

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance gdimp (thread 1)

ORA-00312: online log 3 thread 1: '/home/oracle/oracle/product/10.2.0/oradata/gdimp/redo03.log'

删除在线日志组redo03时报错，连续的转化造成所有的日志组在很短

的时间内status状态都处active状态，故在删除时出现如上报错

=========================================================

14、错误处理2

=========================================================

SQL> alter system set log_buffer=20480k scope=spfile;

alter system set log_buffer=20480k scope=spfile

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

02095, 00000, "specified initialization parameter cannot be modified"

// *Cause: The specified initialization parameter is not modifiable

SQL> show parameter log_buffer;

NAME TYPE VALUE

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

log_buffer integer 7053312

在修改这个参数时会出现如上错误，应该是没有问题的；

数据库重新启动后加载，相应的参数将会调整成功

SQL> show parameter log_buffer;

NAME TYPE VALUE

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

log_buffer integer 20480000

=========================================================

15、show SGA =========================================================

User dump directory 516

VIRTUAL CIRCUITS 605180

POOL NAME BYTES

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

shared pool Wait History 93800

Wait event pointers 168

X$KSFQP ANCHOR 52

X$KSVII table 256

X$KSVIS table 64

X$KSVIT table 256

XDB Schema Cac 4377016

active checkp 944

alert threshol 4116

alter system errs: kspnfy 108544

analytic workspace 2376

POOL NAME BYTES

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

shared pool archive_lag_target 9620

block media rcv state obj 2764

block_sizes_array 24

bloom filter 3532

branch 96804

branch so 248

broker globals 112

buffer handles 282004

buffer_pool_desc_array 2700

buffers waiting for write 12

call 86120

POOL NAME BYTES

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

shared pool change notification obj m 8200

change notification regis 8200

change tracking recovery 262144

change tracking state cha 4168

channel context areas 19712

channel handle 47992

channel sga anchor 172

character set memory 34900

character set object 674656

cinfo_kfnsg 4100

client/application info l 400

POOL NAME BYTES

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

shared pool constraints 47752

cross-platform compliance 1908

database NCHAR language h 540

database creation languag 540

db_block_hash_buckets 2228224

db_files 196820

dbwr actual working sets 32

dbwr message active flag 4

dbwr outstanding ios per 64

dbwr suspend/resume array 8

dbwr suspend/resume ptr a 8

POOL NAME BYTES

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

shared pool dbwr working sets kcbdbws 8

dbwriter coalesce bitmap 64

dbwriter coalesce buffer 1052672

dbwriter coalesce struct 32

dev2node map 2048

dgtab_kfmdsg 8964

dispatcher queue 168

dispatcher rate 1312

dispatcher service names 12

distributed_transactions- 11256

dlo fib struct 8020

POOL NAME BYTES

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

shared pool done Q child latches 272

downed inst bit vector 36

dpslut_kfdsg 256

dsktab_kfgsg 45816

dummy 18756

enqueue 403364

enqueue resources 150516

enqueue_hash 16920

enqueue_hash_chain_latche 400

error message file name 64

event classes 128

POOL NAME BYTES

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

shared pool event descriptor table 28064

event statistics per sess 2682680

event statistics ptr arra 1340

event-class map 3496

eventlist to post commits 468

fdhsh_kffsg 8196

fdrec_kffsg 12

file # to first dba, exte 2412

file # translation table 28840

fixed allocation callback 244

free memory 79940

POOL NAME BYTES

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

shared pool generic process shared st 448

groups_kfgbsg 4096

grplut_kfgsg 256

grptab_kfgsg 3592

heap_kfsg 80

hot latch diagnostics 80

idtab_kfksg 40696

incr ckpt write count arr 168

instance cnxn information 12060

invalid low rba queue 640

java static objs 26468

POOL NAME BYTES

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

shared pool joxs heap 4196

joxs struct 80

jsksncb: 2 7496

jsksncb: 3 4096

jsksncb: 4 4056

jsksncb: 6 2808

jsksncb: 7 483328

jsksncb: 8 800

jsksncb: 9 23752

kcbl seq io throughput 16000

kcbl state objects 7200

POOL NAME BYTES

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

shared pool kcbl statistics 6144

kcrfa structures 10032

kcrrny 25320

kea advisor definition ca 480

kebm run-once actions 16

kebm slave descriptors 988

kebm slave message 124

kebm slave reply 44

kebm test replies 22528

kelr other metrics table 36

kelr system metrics table 248

POOL NAME BYTES

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

shared pool kelt translation table 300

kewr MMON Remote Flush Re 23552

kfasga 1044

kfdsga 44

kffsga 48

kfgbsg 28

kfgsga 36

kfkhsh_kfdsg 2052

kfkid hash 2052

kfkid hrec 12

kfkrec_kfdsg 12

POOL NAME BYTES

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

shared pool kfmdsg 72

kfmsg 3088

kga sga 4

kghx free lists 20736

kgl lock hash table state 15660

kgllk hash table 178176

kglsim count of pinned he 2832

kglsim free heap list 72

kglsim free obj list 72

kglsim hash table 4104

kglsim hash table bkts 2097152

POOL NAME BYTES

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

shared pool kglsim heap 615296

kglsim main lru count 75520

kglsim main lru size 151040

kglsim object batch 1038096

kglsim pin list arr 288

kglsim recovery area 1320

kglsim sga 22188

kglsim size of pinned mem 5664

kgsk subheap descriptor 80

kkj jobq wor 4104

kkj jobq slav 896

POOL NAME BYTES

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

shared pool kks sga 40

kks stats 28

kks stats hds 560

kks stats latch 400

kks stats mem 32

kks stbkt 917504

kksss 21504

kksss-heap 38628

kkzias 144

kmgsb circular statistics 108544

knlsg 80

POOL NAME BYTES

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

shared pool knlu_txn_init_btree:init 28

knstsg 40

kodosgi kodos 16

kodosgi kopfdo 400

koh dur heap 188

kohsg 4

kolbsgi: KOLB's SGA initi 4

kolfsgi: KOLF's SGA initi 4

kponfy 672

kpscad: kpscscon 340

kpssnfy: kpsssgct 32

POOL NAME BYTES

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

shared pool kpummst global in the SGA 992

kqlpWrntoStr:string 200

kqlpaac:value-1 280

krvxdka 588

krvxlctx 160

krvxmctx 20

ksb process so list 288

ksbtnfy: infrequent actio 1760

kscdnfyglobalflags 4

kscdnfyinitflags 4

kscdnfyinithead 12

POOL NAME BYTES

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

shared pool kscdnfyinitnext 16

kscdnfyinitprev 16

ksfd shared pool recovery 16

ksfm state object 20

ksfv subheap descriptor 104

ksim client list 84

ksir State Object 3788

ksleid alloc 112

ksmd unit test 1 7576

kso req alloc 4116

kso req alloc heapds 152

POOL NAME BYTES

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

shared pool kspd run-time context 12

kspload:comment 20

ksuloi: child latches for 400

ksuloi: garbage collectio 16

ksuloi: long op free list 32

ksuloi: long op statistic 142000

ksuloi: long op used list 32

ksunfy: is parent statist 3000

ksunfy: nodes of hierarch 320

ksunfy: system-global sta 3000

ksv reaper 4168

POOL NAME BYTES

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

shared pool ksv slave class 8276

ksws RLB SGA ctx 20

ksws service events 32032

ksws service object 2640

ktlbk state objects 188416

kwqicaqe2kc1 8200

kwqmncal: allocate buffer 4088

kwqmncini-slv 240

kwqmncini-tbl 192

kwrsnfy: kwrs 1612

kxfpdp pointers 14400

POOL NAME BYTES

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

shared pool kzekm heap descriptor 164

kzsrs filename 532

kzull 4960

kzulsg SGA 1040

kzulu 160

latch classes 352

latch descriptor table 1528

latch hashvalue table 1528

latch nowait fails or sle 113088

latch recovery alignment 48

latch recovery structures 468

POOL NAME BYTES

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

shared pool latchnum to latch map 1528

lckhsh_kffsg 2052

lckhsr_kffsg 12

lcktab_kffsg 2764

library cache 11777948

list 3584

listener addresses 4

log file size history arr 168

log_checkpoint_timeout 12360

log_simultaneous_copies 992

max allowable # log files 253200

POOL NAME BYTES

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

shared pool media recovery state obje 6044

memory transfer history 12804

message pool context area 6536

message pool freequeue 698460

messages 55200

modification 67616

monitoring co 8256

msg Q child latches 272

multiblock re 8240

mvobj part des 21368

name-service entry 3912

POOL NAME BYTES

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

shared pool name-service request 2764

name-service table 12944

namhsh_kfdsg 2052

namhsh_kfgsg 144

namrec_kfdsg 12

network connections 52700

obj htab chun 387288

obj stat memo 236812

object level 28896

object level stat table 1008

object level stats hash t 256

POOL NAME BYTES

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

shared pool object queue 294336

object queue hash buckets 139264

object queue hash table d 6080

object stat dummy elem 28

object stat dummy stat 288

os statistics 48

osp allocation 33444

osp pool handles 4

parallel_max_servers 8960

param hash values 5540

parameter blocks 5540

POOL NAME BYTES

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

shared pool parameter handle 125328

parameter table block 465360

parameter text value 3404

parameter value memory 312

partitioning d 118848

plis struct 80

plugin datafile array 3612

plwda:PLW_STR_NEW_LEN_VEC 4

plwda:PLW_STR_NEW_RVAL 12

plwda:PLW_STR_NEW_VAL_VEC 4

plwpil:wa 4252

POOL NAME BYTES

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

shared pool plwppwp:PLW_STR_NEW_LEN_V 16

plwppwp:PLW_STR_NEW_VAL_V 28

plwppwp:garbage handle 8

plwshs:temphdl 28

plwspv:PLW_STR_NEW_VAL 24

policy hash table descrpt 152

post stats 1076

post/wait queues 3712

primem_kfmdsg 516

prirec_kfmdsg 12

private strands 2396160

POOL NAME BYTES

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

shared pool prmtzdini tz region 384988

process group array 24328

processes 1200

procs: ksunfy 438000

procs_kfgbsg 440

property service SO 3528

pso child tracebuf ptrs 1200

pso tbs: ksunfy 116400

pspool_kfsg 44

ptr to sessions under idl 16

qesmmaInitialize: 112

POOL NAME BYTES

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

shared pool qesmmaInitialize: ia_qesm 264

qesmmaInitialize: oa_qesm 112

qesmmaInitialize: pa_qesm 11088

qesmmaInitialize: ta_qesm 264

qm_init_sga:oidctx 4

qm_init_sga:origroot 56

qm_init_sga:qmdpsg 28

qm_init_sga:rootname 4

qm_init_uga:qmsg 15064

qm_init_uga_helper: qmkm 16

qmcInitSGA:qmsga_acl_prop 32

POOL NAME BYTES

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

shared pool qmn tasks 4128

qmps connections 65280

qmtb_init_data 856

qmuCreatePermSubHeap:subh 80

qtree_kwqbsgn 28

qtree_kwqbspse 28

quiesce system context 252

quiescing session 2252

recov_kgqbtctx 3036

redo allocation latch(es) 3800

replication session stats 93800

POOL NAME BYTES

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

shared pool repository 174440

reservation state object 2516

reserved entries for all 3776

resize operation history 28804

resize request state obje 351200

resumable 3272

returns from metrics req 521216

returns from remote ops 43008

row cache 3741868

row cache child latch 3400

rules engine aggregate st 1412

POOL NAME BYTES

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

shared pool rules engine context 200

sched job queue 3788

sched job slv 3912

segmented arrays 4336

service names array 28

sess Q child latches 272

session idle latches 400

sessions 1605324

set_descriptor_array 14400

sga dev dict 36

sga listelement 1024

POOL NAME BYTES

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

shared pool sga node map 8

sim cache nbufs 640

sim cache sizes 640

sim kghx free lists 4

sim lru segments 1280

sim segment hits 2560

sim segment num bufs 1280

sim state object 24

sim trace buf 5140

sim trace buf context 120

sim_knlasg 1200

POOL NAME BYTES

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

shared pool simulator hash buckets 131328

simulator hash latch 6400

simulator latch/bucket st 3328

slave class sga anchor 48

sort segment handle 2504

spfile callback table: ks 224

spfile cleanup structure 16752

sql area 134368520

sql area:PLSQL 206068

sskgplib 1132

stat hash values 1452

POOL NAME BYTES

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

shared pool state objects 4200

subheap 53700

sys event stats 192280

sys event stats for Other 192280

system default language h 540

table definiti 1448

temp lob duration state o 3720

temporary foreign ref 3592

temporary tabl 4116

temporary table lock 2504

threshold ale 8212

POOL NAME BYTES

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

shared pool time manager context 36

trace buf hdr xtend 68368

trace buffer 1474560

trace buffer header array 34184

trace events array 68000

trace_knlasg 500

transaction 494956

trigger condition node 64

trigger defini 9484

trigger inform 2860

txncallback 58740

POOL NAME BYTES

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

shared pool type object de 214516

where to latch num map 7068

work area tab 276576

writes stopped lock conte 16

writes stopped lock state 20

x$ksmfs table 12

x$rule_set 16804

xdbconf 4

xscalc 3528

xslongops 4040

xsoqmehift 4168

POOL NAME BYTES

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

shared pool xsoqojhift 3272

xsoqophift 4168

xsoqsehift 2376

xssinfo 5532

************ ----------

sum 249604556

buffer_cache 784334848

fixed_sga 1223488

log_buffer 7163904

************ ----------

POOL NAME BYTES

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

sum 792722240