资源描述：
《greenplum常用维护脚本总结2》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、-InformationCompiledbySohailMian,---Sr.Consultant(EMC-GP).-Contributionscamefrom:-SohailMian,KrisLarson,RandyWillard,GeorgeSpears,RudyWidjaja,AndreasScherbaum.GreenPlumUsefulScriptsSeriesPart-2oRed:-Youhavetoreplacetheseparametersinthescripts.1.Howtogetqueryr
2、untimeandelapsedtime. 2.*ViewingtheLastOperationPerformed*LastAnalyzedorVacuumorCreateTABLEORETC…3.Showsegments,whicharedown.4.Howtocalculatedatabasesizeindisk?5.Howtocalculatetablesizeindisk?6.Forapartitionedtableyouneedthesqlbelowtofindcumulativetablesize7.
3、*FunctionforGrant/Revokepermissiontoobjectswithinaschema*FunctionforRevokeprivilegestoschemaobjects(tables,views,sequences)8.GreenPlumDatabaseGUItools9.Dataskewqueryyoumaylike10.*Findnoofsegments*SizeofSegment11.Determineyourcurrentprimarysegmentconfiguration
4、12.Timezonesetup:13.Re-createGPToolkitobjects,iflost1vHowtogetqueryruntime&elapsedtime.Ifgpperfmonisrunning,youcanpullthetimesoutofthequeries_historytableinthegpperfmondatabase.Thismayalsoworkwithresourcequeuesaswell–thatIhaven'ttested.{log_durationswitchtoON
5、,log_statement}Selecttstart,tfinish,(tfinish-tstart)astotal_time,trim(query_text)fromqueries_historyWheretstart>='2011-07-0711:00:00'andtstart<'2011-07-0713:00:00'anddb='yourdatabasename'Orderbytstart;TablesmaybeusefulforQuerytime.queries_historTsubmit Timet
6、hequerywassubmittedTstart Timethequerywasstarted.Tfinish Timethequeryfinished.pg_catalog.pg_stat_activityquery_startxact_startpg_catalog.pg_stat_resqueuesn_queries_waitelapsed_execelapsed_waitHowtoturnontiming,andcheckinghowmuchtimeaquerytakestoexecute?·
7、YoucanturnintimingpersessionbeforeyourunyourSQLwiththetimingcommand.·YoucanrunexplainanalyzeagainstyourSQLstatementtogetthetiming.Howtotracechildprocessesonsegmentserver?Answer:Whensessionstartinmasterandsegments, allthechildprocessesinsegmentswillbeidentifi
8、edwithmastersession_idconnectionstring(con+sess_id).Forexample:gpadmin=#select*frompg_Stat_activity;-InformationCompiledbySohailMian,---Sr.Consultant(EMC-GP).-Contributionscamefrom:-Sohai
