设为首页收藏本站
开启辅助访问 切换到窄版

仓酷云

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
仓酷云»社区 › 程序开发 Program › MYSQL数据库开发 › MYSQL网页设计有用的数据库反省程序(2)
返回列表 发新帖
查看: 863|回复: 8
打印 上一主题 下一主题

[学习教程] MYSQL网页设计有用的数据库反省程序(2)

[复制链接]
海妖 该用户已被删除
跳转到指定楼层
楼主
发表于 2015-1-16 22:48:52 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有帐号？立即注册

x
“对于MySQL数据库，无论是在开发方面，还是支持方面，现在有大量强大的MySQL学习教程可以选择。每一个新手开发者可以轻松地使用MySQL数据库进行开发。程序|数据|数据库
TtitleOff



prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.0LogSwitchIntheLastDay+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



TTitleleft"***Database:"xdbname",HowOffentheLogSwitch(Asof:"xdate")***"skip1



columnarchive_nameformatA40

column"Time"formatA25



selectto_char(TO_DATE(Time,MM/DD/RRHH24:MI:SS),DD-MON-RRRRHH24:MI:SS)"Time",

ARCHIVE_NAME

fromv$log_history

whereTO_DATE(Time,MM/DD/RRHH24:MI:SS)>sysdate-1

orderbyTO_DATE(Time,MM/DD/RRHH24:MI:SS)desc;



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.0Invesigation

promptStandard:

promptDuringperiodsofhighactivity,logswitchesareoccurringevery20minutes

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.1LogBuffer-redologspacerequests:TheValue+

prompt+shouldberelativesmallpromptServeriswaitingfor+

prompt+diskspacetobeallocateforredologentries+

prompt+Spaceiscreatedbyperformingalogswitch+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-redologspacerequests***"Skip1

selectsubstr(name,1,25)"LogBuffers",

substr(value,1,15)"VALUE(Near0?)"

fromv$sysstat

wherename=redologspacerequests

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.1Investigation

promptIfthevalueisnotnear0,increaseLOGBUFFER.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.2LogBuffer-logbufferspace+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleleft"***LogBuffers-LogBufferSpaceWaits***"Skip1

selectsid,event,seconds_in_wait,state

fromv$session_wait

whereevent=logbufferspace

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.2Invesigation

promptThereshouldbenologbufferspacewaits

promptMakingthelogbufferbiggerifitissmall

promptMovingthelogfilestofasterdiskssuchasstripeddisks

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.3LogBuffer-RedoBufferAllocationRetries+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-RedoBufferAllocationRetries***"Skip1

columnnameprint

selectname,value

fromv$sysstat

wherenamein(redobufferallocationretries,redoentries)

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.3Investigation

promptRedoBufferAllocationRetriesshouldbenear0

promptthenumbershouldbelessthan1%ofRedoEntries

promptIncreasethesizeoftheredologbuffer(LOGBUFFER)

promptimprovethecheckpointingorarchivingprocess

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.4LogBuffer-LogFileSwitchCompletion+

prompt+Identifythelogfileswitchwaitsbecauseoflogswitches+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-LogFileSwithCompletion***"Skip1

selectevent,total_waits,time_waited,average_wait

fromv$system_event

whereeventlikelogfileswitchcompletion%

/

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.4Investigation

promptIncreasethesizeoftheredologfiles

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+3.5LogBuffer-CHECKPOINTIncomplete+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***LogBuffers-CheckPointIncompletion***"Skip1

selectevent,total_waits,time_waited,average_wait

fromv$system_event

whereeventlikelogfileswitch(check%

oreventlikelogfileswitch(arch%

/

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt3.5Investigation

promptcheckthefrequenceofcheckpointsandsettheappropriatevalues

promptforLOG_CHECKPOINT_INTERVALandLOG_CHECKPOINT_TIMEOUT

promptcheckthesizeandnumberofredologgroups

promptconfirmthatthearchivedeviceisnotfull

promptaddredologgroups

promptincreasethenumberofbufferstoarchiveandreducingthesizeofbuffersbysetting

promptLOG_ARCHIVE_BUFFERSandLOG_ARCHIVE_BUFFER_SIZE

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+4.0SharePoolSize-GetsandMisses(LibraryCache)+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



column"Executions"format9,999,999,990

column"CacheMissesExecuting"format9,999,999,990

column"DataDictionaryGets"format9,999,999,999

column"GetMisses"format9,999,999,999

column"%Ratio"format999.99



ttitleleftskip1-

left"***SharedPoolSize(ExecutionMisses)***"skip1



selectsum(pins)"Executions",

sum(reloads)"CacheMissesExecuting",

(sum(reloads)/sum(pins)*100)"%Ratio"

fromv$librarycache

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt4.0Investigation

promptIf%Ratioisabove1%,increaseSHARE_POOL_SIZE.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+4.1SharePoolSize-GetsandMisses(DataDictionary)+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt



ttitleleft"***************SharedPoolSize(DictionaryGets)***********"skip1

selectsum(gets)"DataDictionaryGets",

sum(getmisses)"GetMisses",

100*(sum(getmisses)/sum(gets))"Ratio"

fromv$rowcache

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt4.1Investigation

promptIf%Ratioisabove12%,increaseSHARED_POOL_SIZE.

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+5.0CheckWhichSQListhemostcostSQL+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleoff



ttitleleft"***V$SQLCheck***"Skip1

columnSQL_TEXTFormatA50

selectSql_TEXT,EXECUTIONS,ROWS_PROCESSED,BUFFER_GETS/100,DISK_READS/100

FROMV$sqlV,(SELECTSUM(BUFFER_GETS)TOT_GETSFROMV$SQL)S

WHEREBUFFER_GETS>TOT_GETS*0.1

ORDERBYBUFFER_GETSDESC

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt5.0Invesigation

promptSelectedSQListhemostcostSQL(>10%oftotalgets)

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt

ttitleoff



prompt

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt+6.0HowMuchCPUisusedforeachsession+

prompt++++++++++++++++++++++++++++++++++Item++++++++++++++++++++++++++++++++++

prompt

ttitleleft"***CPUUsage***"Skip1

columnUSERNAMEFORMATA10

columnMACHINEFORMATA15

columnOSUSERFORMATA15

columnTERMINALFORMATA15

columnPROGRAMFORMATA20

selects.sid,v.SERIAL#,v.USERNAME,v.OSUSER,v.MACHINE,v.TERMINAL,v.PROGRAM,s.value"CPUUsed"

fromv$sesstats,v$statnamen,v$sessionv

wheres.statistic#=n.statistic#andn.name=CPUusedbythissession

ands.sid=v.sid

/



prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt6.0Invesigation

prompt

prompt<<<<<<<<<<<<<<<<<<<NOTE:>>>>>>>>>>>>>>>>>>>>>>

prompt
这是无法比较的，因为基于云的数据库提供了不同的模式。关键是要通过围绕云计算产品来包装其他增值服务以适应不断变化的市场条件：这就是DBaaS。
数据库, 程序

相关帖子
回复

使用道具 举报

灵魂腐蚀 该用户已被删除
沙发
发表于 2015-1-20 05:03:07 | 只看该作者
一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。
回复 支持 反对

使用道具 举报

莫相离 该用户已被删除
板凳
发表于 2015-1-28 16:04:40 | 只看该作者
可以动态传入参数，省却了动态SQL的拼写。
回复 支持 反对

使用道具 举报

冷月葬花魂 该用户已被删除
地板
发表于 2015-2-5 15:29:25 | 只看该作者
XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）
回复 支持 反对

使用道具 举报

兰色精灵 该用户已被删除
5#
发表于 2015-2-12 16:40:05 | 只看该作者
无法深入到数据库系统层面去了解和探究
回复 支持 反对

使用道具 举报

第二个灵魂 该用户已被删除
6#
发表于 2015-3-3 03:48:09 | 只看该作者
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。
回复 支持 反对

使用道具 举报

不帅 该用户已被删除
7#
发表于 2015-3-11 09:10:58 | 只看该作者
可以动态传入参数，省却了动态SQL的拼写。
回复 支持 反对

使用道具 举报

若相依 该用户已被删除
8#
发表于 2015-3-18 02:22:31 | 只看该作者
从项目平台的选择上讲，我们关心的，应该是一款产品能不能满足任务需求，而不是网上怎么说。
回复 支持 反对

使用道具 举报

愤怒的大鸟 该用户已被删除
9#
发表于 2015-3-25 09:31:50 | 只看该作者
我个人认为就是孜孜不懈的学习
回复 支持 反对

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|仓酷云 鄂ICP备14007578号-2

GMT+8, 2024-5-22 13:48

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表