一次数据库的简单性能优化:增加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 OFFLOGGINGONLINEPERMANENTEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT 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.logrm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo02.logrm /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.logrm /home/oracle/oracle/product/10.2.0/oradata/gdimp/redo05.log
=========================================================11、备份当前的最新的控制文件=========================================================
SQL> connect internalSQL> 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 modified02095, 00000, "specified initialization parameter cannot be modified"// *Cause: The specified initialization parameter is not modifiableSQL> 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 516VIRTUAL CIRCUITS 605180
POOL NAME BYTES------------ -------------------------- ----------shared pool Wait History 93800Wait event pointers 168X$KSFQP ANCHOR 52X$KSVII table 256X$KSVIS table 64X$KSVIT table 256XDB Schema Cac 4377016active checkp 944alert threshol 4116alter system errs: kspnfy 108544analytic workspace 2376
POOL NAME BYTES------------ -------------------------- ----------shared pool archive_lag_target 9620block media rcv state obj 2764block_sizes_array 24bloom filter 3532branch 96804branch so 248broker globals 112buffer handles 282004buffer_pool_desc_array 2700buffers waiting for write 12call 86120
POOL NAME BYTES------------ -------------------------- ----------shared pool change notification obj m 8200change notification regis 8200change tracking recovery 262144change tracking state cha 4168channel context areas 19712channel handle 47992channel sga anchor 172character set memory 34900character set object 674656cinfo_kfnsg 4100client/application info l 400
POOL NAME BYTES------------ -------------------------- ----------shared pool constraints 47752cross-platform compliance 1908database NCHAR language h 540database creation languag 540db_block_hash_buckets 2228224db_files 196820dbwr actual working sets 32dbwr message active flag 4dbwr outstanding ios per 64dbwr suspend/resume array 8dbwr suspend/resume ptr a 8
POOL NAME BYTES------------ -------------------------- ----------shared pool dbwr working sets kcbdbws 8dbwriter coalesce bitmap 64dbwriter coalesce buffer 1052672dbwriter coalesce struct 32dev2node map 2048dgtab_kfmdsg 8964dispatcher queue 168dispatcher rate 1312dispatcher service names 12distributed_transactions- 11256dlo fib struct 8020
POOL NAME BYTES------------ -------------------------- ----------shared pool done Q child latches 272downed inst bit vector 36dpslut_kfdsg 256dsktab_kfgsg 45816dummy 18756enqueue 403364enqueue resources 150516enqueue_hash 16920enqueue_hash_chain_latche 400error message file name 64event classes 128
POOL NAME BYTES------------ -------------------------- ----------shared pool event descriptor table 28064event statistics per sess 2682680event statistics ptr arra 1340event-class map 3496eventlist to post commits 468fdhsh_kffsg 8196fdrec_kffsg 12file # to first dba, exte 2412file # translation table 28840fixed allocation callback 244free memory 79940
POOL NAME BYTES------------ -------------------------- ----------shared pool generic process shared st 448groups_kfgbsg 4096grplut_kfgsg 256grptab_kfgsg 3592heap_kfsg 80hot latch diagnostics 80idtab_kfksg 40696incr ckpt write count arr 168instance cnxn information 12060invalid low rba queue 640java static objs 26468
POOL NAME BYTES------------ -------------------------- ----------shared pool joxs heap 4196joxs struct 80jsksncb: 2 7496jsksncb: 3 4096jsksncb: 4 4056jsksncb: 6 2808jsksncb: 7 483328jsksncb: 8 800jsksncb: 9 23752kcbl seq io throughput 16000kcbl state objects 7200
POOL NAME BYTES------------ -------------------------- ----------shared pool kcbl statistics 6144kcrfa structures 10032kcrrny 25320kea advisor definition ca 480kebm run-once actions 16kebm slave descriptors 988kebm slave message 124kebm slave reply 44kebm test replies 22528kelr other metrics table 36kelr system metrics table 248
POOL NAME BYTES------------ -------------------------- ----------shared pool kelt translation table 300kewr MMON Remote Flush Re 23552kfasga 1044kfdsga 44kffsga 48kfgbsg 28kfgsga 36kfkhsh_kfdsg 2052kfkid hash 2052kfkid hrec 12kfkrec_kfdsg 12
POOL NAME BYTES------------ -------------------------- ----------shared pool kfmdsg 72kfmsg 3088kga sga 4kghx free lists 20736kgl lock hash table state 15660kgllk hash table 178176kglsim count of pinned he 2832kglsim free heap list 72kglsim free obj list 72kglsim hash table 4104kglsim hash table bkts 2097152
POOL NAME BYTES------------ -------------------------- ----------shared pool kglsim heap 615296kglsim main lru count 75520kglsim main lru size 151040kglsim object batch 1038096kglsim pin list arr 288kglsim recovery area 1320kglsim sga 22188kglsim size of pinned mem 5664kgsk subheap descriptor 80kkj jobq wor 4104kkj jobq slav 896
POOL NAME BYTES------------ -------------------------- ----------shared pool kks sga 40kks stats 28kks stats hds 560kks stats latch 400kks stats mem 32kks stbkt 917504kksss 21504kksss-heap 38628kkzias 144kmgsb circular statistics 108544knlsg 80
POOL NAME BYTES------------ -------------------------- ----------shared pool knlu_txn_init_btree:init 28knstsg 40kodosgi kodos 16kodosgi kopfdo 400koh dur heap 188kohsg 4kolbsgi: KOLB's SGA initi 4kolfsgi: KOLF's SGA initi 4kponfy 672kpscad: kpscscon 340kpssnfy: kpsssgct 32
POOL NAME BYTES------------ -------------------------- ----------shared pool kpummst global in the SGA 992kqlpWrntoStr:string 200kqlpaac:value-1 280krvxdka 588krvxlctx 160krvxmctx 20ksb process so list 288ksbtnfy: infrequent actio 1760kscdnfyglobalflags 4kscdnfyinitflags 4kscdnfyinithead 12
POOL NAME BYTES------------ -------------------------- ----------shared pool kscdnfyinitnext 16kscdnfyinitprev 16ksfd shared pool recovery 16ksfm state object 20ksfv subheap descriptor 104ksim client list 84ksir State Object 3788ksleid alloc 112ksmd unit test 1 7576kso req alloc 4116kso req alloc heapds 152
POOL NAME BYTES------------ -------------------------- ----------shared pool kspd run-time context 12kspload:comment 20ksuloi: child latches for 400ksuloi: garbage collectio 16ksuloi: long op free list 32ksuloi: long op statistic 142000ksuloi: long op used list 32ksunfy: is parent statist 3000ksunfy: nodes of hierarch 320ksunfy: system-global sta 3000ksv reaper 4168
POOL NAME BYTES------------ -------------------------- ----------shared pool ksv slave class 8276ksws RLB SGA ctx 20ksws service events 32032ksws service object 2640ktlbk state objects 188416kwqicaqe2kc1 8200kwqmncal: allocate buffer 4088kwqmncini-slv 240kwqmncini-tbl 192kwrsnfy: kwrs 1612kxfpdp pointers 14400
POOL NAME BYTES------------ -------------------------- ----------shared pool kzekm heap descriptor 164kzsrs filename 532kzull 4960kzulsg SGA 1040kzulu 160latch classes 352latch descriptor table 1528latch hashvalue table 1528latch nowait fails or sle 113088latch recovery alignment 48latch recovery structures 468
POOL NAME BYTES------------ -------------------------- ----------shared pool latchnum to latch map 1528lckhsh_kffsg 2052lckhsr_kffsg 12lcktab_kffsg 2764library cache 11777948list 3584listener addresses 4log file size history arr 168log_checkpoint_timeout 12360log_simultaneous_copies 992max allowable # log files 253200
POOL NAME BYTES------------ -------------------------- ----------shared pool media recovery state obje 6044memory transfer history 12804message pool context area 6536message pool freequeue 698460messages 55200modification 67616monitoring co 8256msg Q child latches 272multiblock re 8240mvobj part des 21368name-service entry 3912
POOL NAME BYTES------------ -------------------------- ----------shared pool name-service request 2764name-service table 12944namhsh_kfdsg 2052namhsh_kfgsg 144namrec_kfdsg 12network connections 52700obj htab chun 387288obj stat memo 236812object level 28896object level stat table 1008object level stats hash t 256
POOL NAME BYTES------------ -------------------------- ----------shared pool object queue 294336object queue hash buckets 139264object queue hash table d 6080object stat dummy elem 28object stat dummy stat 288os statistics 48osp allocation 33444osp pool handles 4parallel_max_servers 8960param hash values 5540parameter blocks 5540
POOL NAME BYTES------------ -------------------------- ----------shared pool parameter handle 125328parameter table block 465360parameter text value 3404parameter value memory 312partitioning d 118848plis struct 80plugin datafile array 3612plwda:PLW_STR_NEW_LEN_VEC 4plwda:PLW_STR_NEW_RVAL 12plwda:PLW_STR_NEW_VAL_VEC 4plwpil:wa 4252
POOL NAME BYTES------------ -------------------------- ----------shared pool plwppwp:PLW_STR_NEW_LEN_V 16plwppwp:PLW_STR_NEW_VAL_V 28plwppwp:garbage handle 8plwshs:temphdl 28plwspv:PLW_STR_NEW_VAL 24policy hash table descrpt 152post stats 1076post/wait queues 3712primem_kfmdsg 516prirec_kfmdsg 12private strands 2396160
POOL NAME BYTES------------ -------------------------- ----------shared pool prmtzdini tz region 384988process group array 24328processes 1200procs: ksunfy 438000procs_kfgbsg 440property service SO 3528pso child tracebuf ptrs 1200pso tbs: ksunfy 116400pspool_kfsg 44ptr to sessions under idl 16qesmmaInitialize: 112
POOL NAME BYTES------------ -------------------------- ----------shared pool qesmmaInitialize: ia_qesm 264qesmmaInitialize: oa_qesm 112qesmmaInitialize: pa_qesm 11088qesmmaInitialize: ta_qesm 264qm_init_sga:oidctx 4qm_init_sga:origroot 56qm_init_sga:qmdpsg 28qm_init_sga:rootname 4qm_init_uga:qmsg 15064qm_init_uga_helper: qmkm 16qmcInitSGA:qmsga_acl_prop 32
POOL NAME BYTES------------ -------------------------- ----------shared pool qmn tasks 4128qmps connections 65280qmtb_init_data 856qmuCreatePermSubHeap:subh 80qtree_kwqbsgn 28qtree_kwqbspse 28quiesce system context 252quiescing session 2252recov_kgqbtctx 3036redo allocation latch(es) 3800replication session stats 93800
POOL NAME BYTES------------ -------------------------- ----------shared pool repository 174440reservation state object 2516reserved entries for all 3776resize operation history 28804resize request state obje 351200resumable 3272returns from metrics req 521216returns from remote ops 43008row cache 3741868row cache child latch 3400rules engine aggregate st 1412
POOL NAME BYTES------------ -------------------------- ----------shared pool rules engine context 200sched job queue 3788sched job slv 3912segmented arrays 4336service names array 28sess Q child latches 272session idle latches 400sessions 1605324set_descriptor_array 14400sga dev dict 36sga listelement 1024
POOL NAME BYTES------------ -------------------------- ----------shared pool sga node map 8sim cache nbufs 640sim cache sizes 640sim kghx free lists 4sim lru segments 1280sim segment hits 2560sim segment num bufs 1280sim state object 24sim trace buf 5140sim trace buf context 120sim_knlasg 1200
POOL NAME BYTES------------ -------------------------- ----------shared pool simulator hash buckets 131328simulator hash latch 6400simulator latch/bucket st 3328slave class sga anchor 48sort segment handle 2504spfile callback table: ks 224spfile cleanup structure 16752sql area 134368520sql area:PLSQL 206068sskgplib 1132stat hash values 1452
POOL NAME BYTES------------ -------------------------- ----------shared pool state objects 4200subheap 53700sys event stats 192280sys event stats for Other 192280system default language h 540table definiti 1448temp lob duration state o 3720temporary foreign ref 3592temporary tabl 4116temporary table lock 2504threshold ale 8212
POOL NAME BYTES------------ -------------------------- ----------shared pool time manager context 36trace buf hdr xtend 68368trace buffer 1474560trace buffer header array 34184trace events array 68000trace_knlasg 500transaction 494956trigger condition node 64trigger defini 9484trigger inform 2860txncallback 58740
POOL NAME BYTES------------ -------------------------- ----------shared pool type object de 214516where to latch num map 7068work area tab 276576writes stopped lock conte 16writes stopped lock state 20x$ksmfs table 12x$rule_set 16804xdbconf 4xscalc 3528xslongops 4040xsoqmehift 4168
POOL NAME BYTES------------ -------------------------- ----------shared pool xsoqojhift 3272xsoqophift 4168xsoqsehift 2376xssinfo 5532************ ----------sum 249604556
buffer_cache 784334848fixed_sga 1223488log_buffer 7163904************ ----------
POOL NAME BYTES------------ -------------------------- ----------sum 792722240
转载于:https://www.cnblogs.com/myitworld/archive/2008/04/21/2214890.html
