社区 › MYSQL数据库开发 › MYSQL网页编程之Oracle诊断案例-SGA与Swap之一

只想知道 发表于 2015-1-16 22:40:13

MYSQL网页编程之Oracle诊断案例-SGA与Swap之一

不可否认，MySQL也是一个很好的关系型数据库，或许在技术上它与其他领先的关系数据库相差并不大，或不具有劣势。但是，对于一些企业环境来说，MySQL显然不具有优势。oracle




link:

http://www.eygle.com/case/sga1.htm

案例形貌:

用户呈报，服务器启动一段工夫今后，没法创建数据库毗连
从头启动几分钟今后，再次没法毗连

体系没法一般利用.



1.上岸体系

SunOS5.8

login:root
Password:
Lastlogin:TueMar2313:56:59from172.16.31.41
SunMicrosystemsInc.SunOS5.8GenericPatchOctober2001
Youhavenewmail.

2.su为Oracle用户
反省启动的Oracle历程

发明背景历程一般，有必定量的用户毗连






wapplatform:/>su-oracle
SunMicrosystemsInc.SunOS5.8GenericPatchOctober2001
Youhavenewmail.
/export/home1/oracle>ls
admincodesyndealt31exp.shlocal.cshrclocal.profileoraclebakouiv6_database
appexp.logjrelocal.loginnsmailoradataswan
export/home1/oracle>cdadmin
/export/home1/oracle/admin>ps-ef|grepora
oracle2526925258013:58:36pts/30:00grepora
oracle2525724906013:58:31pts/40:00vialert_HSWAPDB.log
oracle252671113:58:34?0:00oracleHSWAPDB(LOCAL=NO)
oracle251841013:56:57?0:00ora_p007_HSWAPDB
oracle251821013:56:57?0:00ora_p006_HSWAPDB
oracle251931013:57:03?0:01oracleHSWAPDB(LOCAL=NO)
oracle252091013:57:09?0:00oracleHSWAPDB(LOCAL=NO)
oracle251761013:56:57?0:00ora_p003_HSWAPDB
oracle251801013:56:57?0:00ora_p005_HSWAPDB
oracle251721013:56:56?0:00ora_p001_HSWAPDB
oracle251781013:56:57?0:00ora_p004_HSWAPDB
oracle251701013:56:56?0:00ora_p000_HSWAPDB
oracle2425424240012:08:25pts/20:00-ksh
oracle251741013:56:56?0:00ora_p002_HSWAPDB
oracle252441113:58:23?0:00oracleHSWAPDB(LOCAL=NO)
oracle252181013:57:23?0:00oracleHSWAPDB(LOCAL=NO)
oracle251591013:56:42?0:02ora_qmn0_HSWAPDB
oracle252301013:57:40?0:01oracleHSWAPDB(LOCAL=NO)
oracle251611013:56:42?0:00ora_s000_HSWAPDB
oracle251491013:56:41?0:01ora_lgwr_HSWAPDB
oracle251571013:56:42?0:00ora_cjq0_HSWAPDB
oracle249063698013:47:47pts/40:00-ksh
oracle251531013:56:42?0:01ora_smon_HSWAPDB
oracle250587464013:55:14pts/10:00-ksh
oracle251631013:56:42?0:00ora_d000_HSWAPDB
oracle251551013:56:42?0:00ora_reco_HSWAPDB
oracle251511013:56:41?0:00ora_ckpt_HSWAPDB
oracle251451013:56:41?0:00ora_dbw0_HSWAPDB
oracle2519911513:57:04?0:49ora_j000_HSWAPDB
oracle41494146012:05:11pts/50:00-ksh
oracle252321013:57:41?0:00oracleHSWAPDB(LOCAL=NO)
oracle251191013:56:29?0:00oraclehswapdb(LOCAL=NO)
oracle250751013:55:34?0:00/export/home1/oracle/app/bin/tnslsnrLISTENER-inherit
oracle243744149012:21:56pts/50:00sqlplus/nolog
oracle251431013:56:41?0:00ora_pmon_HSWAPDB
oracle2525825242013:58:31pts/30:00-ksh
/export/home1/oracle/admin>ps-ef|grepora_
oracle2527525258013:58:42pts/30:00grepora_
oracle251841013:56:57?0:00ora_p007_HSWAPDB
oracle251821013:56:57?0:00ora_p006_HSWAPDB
oracle251761013:56:57?0:00ora_p003_HSWAPDB
oracle251801013:56:57?0:00ora_p005_HSWAPDB
oracle251721013:56:56?0:00ora_p001_HSWAPDB
oracle251781013:56:57?0:00ora_p004_HSWAPDB
oracle251701013:56:56?0:00ora_p000_HSWAPDB
oracle251741013:56:56?0:00ora_p002_HSWAPDB
oracle251591013:56:42?0:02ora_qmn0_HSWAPDB
oracle251611013:56:42?0:00ora_s000_HSWAPDB
oracle251491013:56:41?0:01ora_lgwr_HSWAPDB
oracle251571013:56:42?0:00ora_cjq0_HSWAPDB
oracle251531013:56:42?0:01ora_smon_HSWAPDB
oracle251631013:56:42?0:00ora_d000_HSWAPDB
oracle251551013:56:42?0:00ora_reco_HSWAPDB
oracle251511013:56:41?0:00ora_ckpt_HSWAPDB
oracle251451013:56:41?0:00ora_dbw0_HSWAPDB
oracle2519911313:57:04?0:51ora_j000_HSWAPDB
oracle251431013:56:41?0:00ora_pmon_HSWAPDB

3.反省Alert.log警报日记文件






/export/home1/oracle/admin>ls
hswapdb
/export/home1/oracle/admin>cd*
/export/home1/oracle/admin/hswapdb>ls
bdumpcdumpcreatepfileudump
/export/home1/oracle/admin/hswapdb>cdbdump
/export/home1/oracle/admin/hswapdb/bdump>

/export/home1/oracle/admin/hswapdb/bdump>ls-l*.log

-rw-r--r--1oracledba813396Mar2313:57alert_HSWAPDB.log
/export/home1/oracle/admin/hswapdb/bdump>vi*.log
"alert_HSWAPDB.log"18888lines,813396characters(115null)
TueJun2421:17:142003
StartingORACLEinstance(normal)
LICENSE_MAX_SESSION=0
LICENSE_SESSIONS_WARNING=0
SCNscheme3
Usinglog_archive_destparameterdefaultvalue
LICENSE_MAX_USERS=0
SYSauditingisdisabled
StartingupORACLERDBMSVersion:9.2.0.3.0.
Systemparameterswithnon-defaultvalues:
processes=400
timed_statistics=TRUE
shared_pool_size=117440512
large_pool_size=83886080
java_pool_size=33554432
control_files=/export/home1/oracle/oradata/hswapdb/control01.ctl,

/export/home1/oracle/oradata/hswapdb/control02.ctl,
/export/home1/oracle/oradata/hswapdb/control03.ctl
db_block_size=8192
db_cache_size=352321536
compatible=9.2.0.0.0
db_file_multiblock_read_count=16
fast_start_mttr_target=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=eygle.com
instance_name=hswapdb
dispatchers=(PROTOCOL=TCP)(SERVICE=hswapdbXDB)
job_queue_processes=10
hash_join_enabled=TRUE
background_dump_dest=/export/home1/oracle/admin/hswapdb/bdump
user_dump_dest=/export/home1/oracle/admin/hswapdb/udump
core_dump_dest=/export/home1/oracle/admin/hswapdb/cdump
sort_area_size=524288
db_name=hswapdb
open_cursors=300
star_transformation_enabled=FALSE
query_rewrite_enabled=FALSE
pga_aggregate_target=154140672
aq_tm_processes=1

.................

TueMar2313:40:452004
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=11,op=fork,loc=skgpspawn5
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
TueMar2313:42:022004
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
TueMar2313:55:382004
StartingORACLEinstance(normal)
Shuttingdowninstance:furtherlogonsdisabled
TueMar2313:56:202004
Shuttingdowninstance(abort)
Licensehighwatermark=26
InstanceterminatedbyUSER,pid=25112
TueMar2313:56:372004
StartingORACLEinstance(normal)
LICENSE_MAX_SESSION=0
LICENSE_SESSIONS_WARNING=0
SCNscheme3
Usinglog_archive_destparameterdefaultvalue
LICENSE_MAX_USERS=0
SYSauditingisdisabled
StartingupORACLERDBMSVersion:9.2.0.3.0.
Systemparameterswithnon-defaultvalues:
processes=400
timed_statistics=TRUE
shared_pool_size=117440512
large_pool_size=83886080
java_pool_size=33554432
control_files=/export/home1/oracle/oradata/hswapdb/control01.ctl,

/export/home1/oracle/oradata/hswapdb/control02.ctl,
/export/home1/oracle/oradata/hswapdb/control03.ctl
db_block_size=8192
db_cache_size=352321536
compatible=9.2.0.0.0
db_file_multiblock_read_count=16
fast_start_mttr_target=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
undo_retention=10800
remote_login_passwordfile=EXCLUSIVE
db_domain=eygle.com
instance_name=hswapdb
dispatchers=(PROTOCOL=TCP)(SERVICE=hswapdbXDB)
remote_dependencies_mode=SIGNATURE
job_queue_processes=10
hash_join_enabled=TRUE
background_dump_dest=/export/home1/oracle/admin/hswapdb/bdump
user_dump_dest=/export/home1/oracle/admin/hswapdb/udump
core_dump_dest=/export/home1/oracle/admin/hswapdb/cdump
sort_area_size=524288
db_name=hswapdb
open_cursors=300
star_transformation_enabled=FALSE
parallel_automatic_tuning=TRUE
query_rewrite_enabled=FALSE
pga_aggregate_target=154140672
aq_tm_processes=1
PMONstartedwithpid=2
DBW0startedwithpid=3
LGWRstartedwithpid=4
CKPTstartedwithpid=5
SMONstartedwithpid=6
RECOstartedwithpid=7
CJQ0startedwithpid=8
QMN0startedwithpid=9
TueMar2313:56:422004
startingup1sharedserver(s)...
TueMar2313:56:422004
startingup1dispatcher(s)fornetworkaddress(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))...
TueMar2313:56:432004
ALTERDATABASEMOUNT
TueMar2313:56:472004
Successfulmountofredothread1,withmountid3253076635.
TueMar2313:56:472004
DatabasemountedinExclusiveMode.
Completed:ALTERDATABASEMOUNT
TueMar2313:56:472004
Currentlog#2seq#2136mem#0:/export/home1/oracle/oradata/hswapdb/redo02.log
Successfulopenofredothread1.
TueMar2312:24:542004
SMON:enablingcacherecovery
TueMar2312:24:562004
UndoSegment1Onlined
UndoSegment2Onlined
UndoSegment3Onlined
UndoSegment4Onlined
UndoSegment5Onlined
UndoSegment6Onlined
UndoSegment7Onlined
UndoSegment8Onlined
UndoSegment9Onlined
UndoSegment10Onlined
SuccessfullyonlinedUndoTablespace1.
TueMar2312:24:562004
SMON:enablingtxrecovery
TueMar2312:24:562004
DatabaseCharactersetisZHS16GBK
TueMar2312:25:012004
SMON:Paralleltransactionrecoverytried
TueMar2312:25:012004
replication_dependency_trackingturnedoff(noasyncmultimasterreplicationfound)
Completed:ALTERDATABASEOPEN
TueMar2312:28:262004
/*OracleOEM*/ALTERDATABASEDATAFILE/export/home1/oracle/oradata/hswapdb/users01.dbfRESIZE2501760K
TueMar2312:28:262004
ORA-3297signalledduring:/*OracleOEM*/ALTERDATABASEDATAFILE/export/h...
TueMar2312:28:322004
/*OracleOEM*/ALTERDATABASEDATAFILE/export/home1/oracle/oradata/hswapdb/users01.dbfRESIZE2501760K
ORA-3297signalledduring:/*OracleOEM*/ALTERDATABASEDATAFILE/export/h...
TueMar2312:28:532004
/*OracleOEM*/ALTERDATABASEDATAFILE/export/home1/oracle/oradata/hswapdb/users01.dbfRESIZE3501760K
TueMar2312:28:532004
ORA-3297signalledduring:/*OracleOEM*/ALTERDATABASEDATAFILE/export/h...
TueMar2313:40:452004
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=11,op=fork,loc=skgpspawn5
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
TueMar2313:42:022004
skgpspawnfailed:category=27142,depinfo=12,op=fork,loc=skgpspawn3
:q


发明数据库屡次重起，并纪录了部分毛病信息

该提醒申明数据库没法spawnanewsession.

quoteYongHuangscomment:

Thenumberin"skgpspawnfailed:category=27142"isprobablyORAerror:

$oerrora27142
27142,0000,"couldnotcreatenewprocess"
//*Cause:OSsystemcall
//*Action:checkerrnoandifpossibleincreasethenumberofprocesses

OSD(OS-dependent)errorsarealmostalwaysshownasanskg...error(probablymeans"system,kernelgeneric").

Idontknowwhat"depinfo=12"means.




4.实验毗连数据库

收到毛病信息，没法毗连数据库





$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-Productionon礼拜二3月2314:14:062004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

ERROR:
ORA-12540:TNS:超越外部限定


请输出用户名:
ERROR:
ORA-12540:TNS:超越外部限定


请输出用户名:
ERROR:
ORA-12540:TNS:超越外部限定


SP2-0157:在3次实验以后没法CONNECT到ORACLE,加入SQL*Plus




外部限定凌驾，一般申明某些体系资本不敷.



5.反省监听器

发明部分毗连被回绝






/export/home1/oracle>lsnrctlservices

LSNRCTLforSolaris:Version9.2.0.3.0-Productionon23-3月-200414:37:23

Copyright(c)1991,2002,OracleCorporation.Allrightsreserved.

正在毗连到(DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
服务择要..
服务"PLSExtProc"包括1个例程。
例程"PLSExtProc",形态UNKNOWN,包括此服务的1个处置程序...
处置程序:
"DEDICATED"已创建:0已被回绝:0
LOCALSERVER
服务"hswapdb.eygle.com"包括2个例程。
例程"hswapdb",形态UNKNOWN,包括此服务的1个处置程序...
处置程序:
"DEDICATED"已创建:6已被回绝:0
LOCALSERVER
例程"hswapdb",形态READY,包括此服务的1个处置程序...
处置程序:
"DEDICATED"已创建:21已回绝:6形态:ready
LOCALSERVER
服务"hswapdbXDB.eygle.com"包括1个例程。
例程"hswapdb",形态READY,包括此服务的1个处置程序...
处置程序:
"D000"已创建:0已被回绝:0以后:0最年夜:972形态:ready
DISPATCHER<machine:wapplatform,pid:25839>
(ADDRESS=(PROTOCOL=tcp)(HOST=wapplatform)(PORT=32869))
命令实行乐成



在listener.log中找到了相干毛病信息



23-3324302-200412:19:40*(CONNECT_DATA=(SID=hswapdb)(CID=(PROGRAM=C:WINNTMicrosoft.NETFrameworkv1.1.4322aspnet_wp.e
xe)(HOST=SWAN)(USER=SYSTEM)))*(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.30.125)(PORT=1291))*establish*hswapdb*12500
TNS-12500:TNS24327227434031437530636731626430433430636426625732725032330326530426737631636130636
7275370263314
TNS-12540:TNS243272263254263366304332262277274253317336317336326306
TNS-12560:TNS:320255322351312312305344306367264355316363
TNS-00510:263254263366304332262277274253317336317336326306
SolarisError:12:Notenoughspace
23-3324302-200412:19:50*(CONNECT_DATA=(SID=hswapdb)(CID=(PROGRAM=C:ProgramFilesPLSQLDeveloperPLSQLDev.exe)(HOST=SW
AN)(USER=Administrator)))*(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.30.125)(PORT=1292))*establish*hswapdb*12500
TNS-12500:TNS24327227434031437530636731626430433430636426625732725032330326530426737631636130636
7275370263314
TNS-12540:TNS243272263254263366304332262277274253317336317336326306
TNS-12560:TNS:320255322351312312305344306367264355316363
TNS-00510:263254263366304332262277274253317336317336326306
SolarisError:12:Notenoughspace

/export/home1/oracle/app/network/log>grep-w12/usr/include/sys/errno.h
#defineENOMEM12/*Notenoughcore



quoteYongHuangscomment:

$grep-w12/usr/include/sys/errno.h
#defineENOMEM12/*Notenoughcore*/

Here"core"meansmemory,includingrealRAMmemoryandswapspace.


6.加入Oracle用户反省

反省体系日记信息，发明大批失利的su操纵
有swap区不敷的呈报



/export/home1/oracle/admin/hswapdb/bdump>exit
wapplatform:/>dmesg

2004年03月23日礼拜二14时00分32秒CST
Mar2222:52:36wapplatformelfexec:ps:Cannotfind^?ELF^A^B^A
Mar2222:53:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2222:53:09wapplatformelfexec:w:Cannotfind^?ELF^A^B^A
Mar2222:53:53wapplatformlastmessagerepeated4times
Mar2222:56:28wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2222:58:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2222:59:54wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:02:26wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:03:00wapplatformlastmessagerepeated1time
Mar2223:08:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:08:34wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:10:27wapplatformlastmessagerepeated3times
Mar2223:11:49wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:11:52wapplatformlastmessagerepeated1time
Mar2223:13:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:18:01wapplatformlastmessagerepeated1time
Mar2223:23:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:28:01wapplatformlastmessagerepeated1time
Mar2223:33:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:38:01wapplatformlastmessagerepeated1time
Mar2223:43:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:48:01wapplatformlastmessagerepeated1time
Mar2223:53:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:58:01wapplatformlastmessagerepeated1time
Mar2300:00:00wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2300:00:00wapplatformsendmail:Myunqualifiedhostname(wapplatform)unknown;sleeping

forretry
Mar2300:01:00wapplatformsendmail:unabletoqualifymyowndomainname(wapplatform)--

usingshortname
Mar2300:02:36wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2300:03:02wapplatformlastmessagerepeated1time
Mar2300:08:02wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
....

Mar2310:18:15wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2310:20:41wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:20:47wapplatformlastmessagerepeated1time
Mar2310:23:15wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2310:24:38wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:24:43wapplatformlastmessagerepeated1time
Mar2310:24:55wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:25:06wapplatformlastmessagerepeated2times
Mar2311:09:31wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3118(su)
Mar2311:09:39wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3121(su)
Mar2311:10:48wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3137(su)
Mar2311:18:02wapplatformsshd:error:grantpt:Notenoughspace
Mar2311:18:02wapplatformsshd:error:session_pty_req:session0allocfailed
Mar2311:18:43wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3636(su)
Mar2311:19:47wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3672(su)
Mar2311:20:20wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3694(su)
Mar2311:22:23wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3736(sshd)
Mar2311:23:17wapplatformtmpfs:WARNING:/tmp:Filesystemfull,swapspacelimitexceeded
Mar2311:23:40wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3804(su)
Mar2311:23:40wapplatformlastmessagerepeated8times
Mar2311:23:56wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3806(ps)
Mar2311:23:56wapplatformlastmessagerepeated12times
Mar2311:24:01wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3808(w)
Mar2311:24:01wapplatformlastmessagerepeated8times
Mar2313:40:56wapplatformsu:surootfailedforrooton/dev/pts/2
Mar2313:46:26wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid24888

(sqlplus)
Mar2313:49:18wapplatformsu:suoraclefailedforrooton/dev/pts/6
Mar2313:54:03wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid25035(su)
Mar2313:54:08wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid25036(su)



如今基础能够判别是互换区的成绩，固然和OracleSGA设置有关.

7.反省体系内存及互换区利用




/export/home1/oracle/admin/hswapdb/bdump>exit
wapplatform:/>dmesg

2004年03月23日礼拜二14时00分32秒CST
Mar2222:52:36wapplatformelfexec:ps:Cannotfind^?ELF^A^B^A
Mar2222:53:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2222:53:09wapplatformelfexec:w:Cannotfind^?ELF^A^B^A
Mar2222:53:53wapplatformlastmessagerepeated4times
Mar2222:56:28wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2222:58:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2222:59:54wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:02:26wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:03:00wapplatformlastmessagerepeated1time
Mar2223:08:00wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:08:34wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:10:27wapplatformlastmessagerepeated3times
Mar2223:11:49wapplatformelfexec:ipnat:Cannotfind^?ELF^B^B^A
Mar2223:11:52wapplatformlastmessagerepeated1time
Mar2223:13:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:18:01wapplatformlastmessagerepeated1time
Mar2223:23:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:28:01wapplatformlastmessagerepeated1time
Mar2223:33:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:38:01wapplatformlastmessagerepeated1time
Mar2223:43:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:48:01wapplatformlastmessagerepeated1time
Mar2223:53:01wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2223:58:01wapplatformlastmessagerepeated1time
Mar2300:00:00wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2300:00:00wapplatformsendmail:Myunqualifiedhostname(wapplatform)unknown;sleeping

forretry
Mar2300:01:00wapplatformsendmail:unabletoqualifymyowndomainname(wapplatform)--

usingshortname
Mar2300:02:36wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2300:03:02wapplatformlastmessagerepeated1time
Mar2300:08:02wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
....

Mar2310:18:15wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2310:20:41wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:20:47wapplatformlastmessagerepeated1time
Mar2310:23:15wapplatformufs:NOTICE:alloc:/export/home1:filesystemfull
Mar2310:24:38wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:24:43wapplatformlastmessagerepeated1time
Mar2310:24:55wapplatformufs:NOTICE:realloccg/export/home1:filesystemfull
Mar2310:25:06wapplatformlastmessagerepeated2times
Mar2311:09:31wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3118(su)
Mar2311:09:39wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3121(su)
Mar2311:10:48wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3137(su)
Mar2311:18:02wapplatformsshd:error:grantpt:Notenoughspace
Mar2311:18:02wapplatformsshd:error:session_pty_req:session0allocfailed
Mar2311:18:43wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3636(su)
Mar2311:19:47wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3672(su)
Mar2311:20:20wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3694(su)
Mar2311:22:23wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3736(sshd)
Mar2311:23:17wapplatformtmpfs:WARNING:/tmp:Filesystemfull,swapspacelimitexceeded
Mar2311:23:40wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3804(su)
Mar2311:23:40wapplatformlastmessagerepeated8times
Mar2311:23:56wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3806(ps)
Mar2311:23:56wapplatformlastmessagerepeated12times
Mar2311:24:01wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid3808(w)
Mar2311:24:01wapplatformlastmessagerepeated8times
Mar2313:40:56wapplatformsu:surootfailedforrooton/dev/pts/2
Mar2313:46:26wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid24888

(sqlplus)
Mar2313:49:18wapplatformsu:suoraclefailedforrooton/dev/pts/6
Mar2313:54:03wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid25035(su)
Mar2313:54:08wapplatformgenunix:WARNING:Sorry,noswapspacetogrowstackforpid25036(su)


如今基础能够判别是互换区的成绩，固然和OracleSGA设置有关.

7.反省体系内存及互换区利用





$toplastpid:25456;loadaverages:0.67,0.70,0.6914:10:0393processes:91sleeping,2oncpuCPUstates:72.7%idle,14.9%user,2.7%kernel,9.7%iowait,0.0%swapMemory:1024Mreal,34Mfree,752Mswapinuse,10MswapfreePIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND25199oracle1400674M631Mcpu/28:0316.32%oracle25209oracle1300675M630Msleep0:030.13%oracle25159oracle1480674M628Msleep0:030.06%oracle25384oracle15802632K1736Kcpu/00:010.05%top25145oracle143580682M630Msleep0:010.03%oracle25446oracle1580674M628Msleep0:000.03%oracle25149oracle15580682M626Msleep0:000.02%oracle25075oracle148017M7208Ksleep0:000.01%tnslsnr25151oracle11580676M624Msleep0:000.01%oracle25366oracle1100674M628Msleep0:000.00%oracle25356oracle1180674M628Msleep0:000.00%oracle25360oracle1200674M628Msleep0:000.00%oracle25364oracle1200674M628Msleep0:000.00%oracle25362oracle1200674M628Msleep0:000.00%oracle25330oracle1280674M628Msleep0:000.00%oracle

发明物理内存仅为1G，free部分为34M，互换区利用了752M，仅10Mfree
体系内存严峻不敷，Swap区不敷



8.反省数据库的SGA设置

发明SGA设置为:622299344bytes
靠近600M





wapplatform:/>su-oracle
SunMicrosystemsInc.SunOS5.8GenericPatchOctober2001
Youhavenewmail.
/export/home1/oracle>sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-Productionon礼拜二3月2314:02:302004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


毗连到:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>showsga

TotalSystemGlobalArea622299344bytes
FixedSize731344bytes
VariableSize268435456bytes
DatabaseBuffers352321536bytes
RedoBuffers811008bytes
SQL>



关于RAM小于1G的体系，Dedicated形式下,Oracle的SGA一样平常不该凌驾1/2物理内存.



9.第一步伐整
减小SGA，为体系保存充足的内存.

10.增添swap区






wapplatform:/>df-k
文件体系千字节用了可用容量挂接在
/dev/dsk/c0t1d0s0309909310542129316914%/
/dev/dsk/c0t2d0s0103257608359637186286682%/usr
/proc0000%/proc
fd0000%/dev/fd
mnttab0000%/etc/mnttab
/dev/dsk/c0t1d0s3101838228591467136630%/var
swap39042438801%/var/run
swap39365638802%/tmp
/dev/dsk/c0t1d0s51671823459202116246729%/opt
/dev/dsk/c0t2d0s77087473606846294813787%/export/home
/dev/dsk/c2t1d0s71741325015900222133889693%/export/home2
/dev/dsk/c0t3d0s71741325013749782348933680%/export/home1
/dev/dsk/c0t1d0s177111038241033472354%/usr/openwin
/export/home/wapgw/luke
7087473606846294813787%/home/wap

wapplatform:/var/swap>cd/export/home1
wapplatform:/export/home1>ls
TT_DBlost+foundoracleoracli9
wapplatform:/export/home1>mkdirswap
wapplatform:/export/home1>cdswap
wapplatform:/export/home1/swap>mkfile-v1gswapfile1
swapfile11073741824bytes
wapplatform:/export/home1/swap>id
uid=0(root)gid=1(other)
wapplatform:/export/home1/swap>swap-a/export/home1/swap/swapfile1
wapplatform:/export/home1/swap>swap-s
总数：分派了623160k字节+保存162704k=已利用785864k，1010936k可用




11.毗连测试

体系恢复一般，成绩办理





wapplatform:/export/home1/swap>su-oracle
SunMicrosystemsInc.SunOS5.8GenericPatchOctober2001
Youhavenewmail.
/export/home1/oracle>sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-Productionon木曜日3月2511:56:282004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


毗连到:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>exit
从Oracle9iEnterpriseEditionRelease9.2.0.3.0-64bitProduction
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production中止开
/export/home1/oracle>top

lastpid:5372;loadaverages:0.25,0.22,0.29

11:57:58
148processes:137sleeping,9zombie,2oncpu
CPUstates:98.8%idle,0.2%user,0.7%kernel,0.2%iowait,0.0%swap
Memory:1024Mreal,17Mfree,824Mswapinuse,934Mswapfree

PIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND
5363root15802680K1736Ksleep0:000.24%top
5370oracle1580514M469Msleep0:000.18%oracle
5366oracle1280514M469Msleep0:000.11%oracle
5341oracle15802680K1736Kcpu/20:000.10%top
5372oracle148061M3288Kcpu/30:000.06%oracle
1288oracle1480514M468Msleep5:330.05%oracle
607root124802768K2312Ksleep1:480.03%mibiisa
25075oracle148017M7208Ksleep0:160.02%tnslsnr
1278oracle15580522M466Msleep0:490.02%oracle
374root115303504K2888Ksleep0:160.01%nscd
1280oracle19580518M466Msleep0:280.00%oracle
5361root14601024K680Ksleep0:000.00%sleep
5362root14601024K680Ksleep0:000.00%sleep
5469root13601952K1176Ksleep30:090.00%monithttp
4167oracle1400515M471Msleep29:380.00%oracle


成绩总结:

Oracle数据库成绩的办理历来就离不开操纵体系

良多时分我们必需经由过程操纵体系一级的手腕来诊断并办理成绩.

关于操纵体系

一样平常Swap区的保举值为2XRAM
假如Ram很年夜，纷歧定非要把Swap设置为2xSwap
可是一般最少设置Swap=Ram

假如Swap区太小，在体系忙碌时代
发生大批互换没法换到磁盘，就会呈现成绩.
如本案例就是如许。

别的，假如体系Ram较小
一般设置SGA<1/2Ram

要为Serverprocess及OS保存充足的内存空间.



与其他数据库相比，MySQL易学易用。

兰色精灵 发表于 2015-1-19 21:07:52

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

精灵巫婆 发表于 2015-1-28 10:40:24

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

变相怪杰 发表于 2015-2-12 06:53:36

是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

若相依 发表于 2015-3-2 23:52:16

如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

分手快乐 发表于 2015-3-11 07:38:21

SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

简单生活 发表于 2015-3-17 23:10:30

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

仓酷云 发表于 2015-3-25 07:21:33

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

查看完整版本: MYSQL网页编程之Oracle诊断案例-SGA与Swap之一