MSSQL网页设计statspack 利用实例

“数据行级锁定”的意思是指在事务操作的执行过程中锁定正在被处理的个别记录，不让其他用户进行访问。这种锁定将影响到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。
--1.´´½¨perfstat±í¿Õ¼ä

createtablespaceperfstatdatafile/home/orapaid/oradata/prdyp/perfstat.dbfsize500m;

--2.ÒÑsysµÇ½ִÐÐÏÂÁнű¾
@/home/orapaid/product/92/rdbms/admin/catdbsyn.sql
@/home/orapaid/product/92/rdbms/admin/dbmspool.sql

--3.ÔËÐа²×°½Å±¾
--ÒÑsysÓû§µÇ¼

selectinstance_name,host_name,version,startup_timefromv$instance;

selectfile_namefromdba_data_files;

createtablespaceperfstatdatafile/home/orapaid/oradata/prdyp/perfstat.dbfsize500M;

--°²×°Ç°Òª×öµÄÊÂ
Ò».ϵͳ²ÎÊý
ΪÁËÄܹ»Ë³Àû°²×°ºÍÔËÐÐStatspackÄã¿ÉÄÜÐèÒªÉèÖÃÒÔÏÂϵͳ²ÎÊý£º
1.job_queue_processes
ΪÁËÄܹ»½¨Á¢×Ô¶¯ÈÎÎñ£¬Ö´ÐÐÊý¾ÝÊÕ¼¯£¬¸Ã²ÎÊýÐèÒª´óÓÚ0¡£Äã¿ÉÒÔÔÚ³õÊÔ»¯²ÎÊýÎļþÖÐÐ޸ĸòÎÊý¡£
Ð޸ĴËĿ¼ÏÂ/home/orapaid/admin/prdyp/pfileµÄ.oraÎļþÐèÒªÖØÆôÊý¾Ý¿â

2.timed_statistics
ÊÕ¼¯²Ù×÷ϵͳµÄ¼ÆʱÐÅÏ¢£¬ÕâЩÐÅÏ¢¿É±»ÓÃÀ´ÏÔʾʱ¼äµÈͳ¼ÆÐÅÏ¢¡¢ÓÅ»¯Êý¾Ý¿âºÍSQLÓï¾ä¡£Òª·ÀÖ¹Òò´Ó²Ù×÷ϵͳÇëÇóʱ¼ä¶øÒýÆðµÄ¿ªÏú£¬Ç뽫¸ÃÖµÉèÖÃΪFalse¡£
ʹÓÃstatspackÊÕ¼¯Í³¼ÆÐÅϢʱ½¨Ò齫¸ÃÖµÉèÖÃΪTRUE£¬·ñÔòÊÕ¼¯µÄͳ¼ÆÐÅÏ¢´óÔ¼Ö»ÄÜÆðµ½10%µÄ×÷Ó㬽«timed_statisticsÉèÖÃΪTrueËù´øÀ´µÄÐÔÄÜÓ°ÏìÓëºÃ´¦Ïà±ÈÊÇ΢²»×ãµÀµÄ¡£
¸Ã²ÎÊýʹÊÕ¼¯µÄʱ¼äÐÅÏ¢´æ´¢ÔÚÔÚV$SESSTATSºÍV$SYSSTATS¶¯Ì¬ÐÔÄÜÊÓͼÖС£

Timed_statistics²ÎÊý¿ÉÒÔÔÚʵÀý¼¶½øÐиü¸Ä

SQL>altersystemsettimed_statistics=true;
Systemaltered

altersystemsettimed_statistics=false;

--°²×°Ç°²é¿´´ËĿ¼ÏµÄÎļþ
sql>hostdirsp*


oracle8iÖ´ÐÐ$ORACLE_HOME/rdbms/admin/statscre.sql
oracle9iÖ´ÐÐ$ORACLE_HOME/rdbms/admin/spcreate.sql

@/home/newvers/product/92/rdbms/admin/spcreate

--°²×°ºó²é¿´´ËĿ¼ÏµÄÎļþ(Ó¦¸Ã¶àÁ˼¸¸ö.lisÎļþ)
sql>hostdirsp*


sql>hostfind¡°ORA-¡°*.lis

sql>hostfind"err"*.lis


--ÔÚUNIXÉÏ£¬Äã¿ÉÒÔͨ¹ýÒÔÏÂÃüÁî²é¿´ÏàÓ¦µÄ´íÎóÐÅÏ¢

$ls*.lis


$grepORA-*.lis
$greperr*.lis

ÔÚÕâÒ»²½£¬Èç¹û³öÏÖ´íÎó£¬ÄÇôÄã¿ÉÒÔÔËÐÐspdrop.sql½Å±¾À´É¾³ýÕâЩ¶ÔÏó¡£È»ºóÖØÐÂÔËÐÐspcreate.sqlÀ´´´½¨ÕâЩ¶ÔÏó¡£ÔËÐÐSQL*Plus,ÒÔ¾ßÓÐSYSDBAȨÏÞµÄÓû§µÇ½£º

/*

Èý.²âÊÔ°²×°ºÃµÄStatspack
ÔËÐÐstatspack.snap¿ÉÒÔ²úÉúϵͳ¿ìÕÕ£¬ÔËÐÐÁ½´Î£¬È»ºóÖ´ÐÐspreport.sql¾Í¿ÉÒÔÉú³ÉÒ»¸ö»ùÓÚÁ½¸öʱ¼äµãµÄ±¨¸æ¡£
Èç¹ûÒ»ÇÐÕý³££¬ËµÃ÷°²×°³É¹¦¡£

ÒÑperfstat/perfstatµÇ½
*/

SQL>executestatspack.snap

SQL>executestatspack.snap


SQL>@/home/newvers/product/92/rdbms/admin/spreport




SQL>@/home/newvers/product/92/rdbms/admin/spauto


--ËÄ.ʹstatspack×Ô¶¯ÊÕ¼¯ÏµÍ³×´¿ö

alertsystemsetjob_queue_processes=10;

alertsystemsetjob_queue_processes=10scope=both;


altersystemsetTimed_statistics=true;


[orapaid@bj37admin]$catspauto.sql
Rem
Rem$Header:spauto.sql16-feb-00.16:49:37cdialeriExp$
Rem
Remspauto.sql
Rem
RemCopyright(c)OracleCorporation1999,2000.AllRightsReserved.
Rem
RemNAME
Remspauto.sql
Rem
RemDESCRIPTION
RemSQL*PLUScommandfiletoautomatethecollectionofSTATPACK
Remstatistics.
Rem
RemNOTES
RemShouldberunastheSTATSPACKowner,PERFSTAT.
RemRequiresjob_queue_processesinit.oraparametertobe
Remsettoanumber>0beforeautomaticstatisticsgathering
Remwillrun.
Rem
RemMODIFIED(MM/DD/YY)
Remcdialeri02/16/00-1191805
Remcdialeri12/06/99-1059172,1103031
Remcdialeri08/13/99-Created
Rem


spoolspauto.lis

--
--Scheduleasnapshottoberunonthisinstanceeveryhour,onthehour

variablejobnonumber;
variableinstnonumber;
begin
selectinstance_numberinto:instnofromv$instance;
dbms_job.submit(:jobno,statspack.snap;,trunc(sysdate+1/24,HH),trunc(SYSDATE+1/24,HH),TRUE,:instno);
commit;
end;
/


prompt
promptJobnumberforautomatedstatisticscollectionforthisinstance
prompt~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
promptNotethatthisjobnumberisneededwhenmodifyingorremoving
promptthejob:
printjobno

prompt
promptJobqueueprocess
prompt~~~~~~~~~~~~~~~~~
promptBelowisthecurrentsettingofthejob_queue_processesinit.ora
promptparameter-thevalueforthisparametermustbegreater
promptthan0touseautomaticstatisticsgathering:
showparameterjob_queue_processes
prompt

prompt
promptNextscheduledrun
prompt~~~~~~~~~~~~~~~~~~
promptThenextscheduledrunforthisjobis:
selectjob,next_date,next_sec
fromuser_jobs
wherejob=:jobno;

spooloff;


--Îå.Éú³É·ÖÎö±¨¸æ
µ÷ÓÃ@/home/orapaid/product/92/rdbms/admin/spreportÉú³É

´Ë¹ý³ÌÖÐÒªÊäÈ뿪ʼ¿ìÕÕºÍÖÕÖ¹¿ìÕÕ±àºÅ

--ÁùÒƳý¶¨Ê±ÈÎÎñ
SQL>executedbms_job.remove(job_id);

--Æßɾ³ýÀúÊ·Êý¾Ý

ɾ³ýstats$snapshot±íÖÐÊý¾Ý,ÆäËû±íÖеÄÊý¾Ý»áÏàÓ¦µÄ¼¶Á¬É¾³ý

oracleÌṩÁËÓÃÓÚtruncateÕâЩͳ¼ÆÐÅÏ¢±íµÄ½Å±¾

[orapaid@bj37admin]$catsptrunc.sql
Rem
Rem$Header:sptrunc.sql19-feb-2002.11:36:28vbarrierExp$
Rem
Remsptrunc.sql
Rem
RemCopyright(c)2000,2002,OracleCorporation.Allrightsreserved.
Rem
RemNAME
Remsptrunc.sql-STATSPACK-Truncatetables
Rem
RemDESCRIPTION
RemTruncatesdatainStatspacktables
Rem
RemNOTES
RemShouldberunasSTATSPACKuser,PERFSTAT.
Rem
RemThefollowingtablesshouldNOTbetruncated
RemSTATS$LEVEL_DESCRIPTION
RemSTATS$IDLE_EVENT
RemSTATS$STATSPACK_PARAMETER
Rem
RemMODIFIED(MM/DD/YY)
Remvbarrier03/05/02-SegmentStatistics
Remcdialeri04/13/01-9.0
Remcdialeri09/12/00-sp_1404195
Remcdialeri04/11/00-1261813
Remcdialeri03/15/00-Created
Rem

undefineanystring
setshowmodeoffechooff;
wheneversqlerrorexit;

spoolsptrunc.lis

/*-------------------------------------------------------------------------*/

prompt
promptWarning
prompt~~~~~~~
promptRunningsptrunc.sqlremovesALLdatafromStatspacktables.Youmay
promptwishtoexportthedatabeforecontinuing.
prompt
prompt
promptAbouttoTruncateStatspackTables
prompt~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
promptIfyouwouldliketocontinue,press<return>
prompt
prompt
prompt&returnEntered-startingtruncateoperation

truncatetableSTATS$FILESTATXS;
truncatetableSTATS$TEMPSTATXS;
truncatetableSTATS$LATCH;
truncatetableSTATS$LATCH_CHILDREN;
truncatetableSTATS$LATCH_MISSES_SUMMARY;
truncatetableSTATS$LATCH_PARENT;
truncatetableSTATS$LIBRARYCACHE;
truncatetableSTATS$BUFFER_POOL_STATISTICS;
truncatetableSTATS$ROLLSTAT;
truncatetableSTATS$ROWCACHE_SUMMARY;
truncatetableSTATS$SGA;
truncatetableSTATS$SGASTAT;
truncatetableSTATS$SYSSTAT;
truncatetableSTATS$SESSTAT;
truncatetableSTATS$SYSTEM_EVENT;
truncatetableSTATS$SESSION_EVENT;
truncatetableSTATS$BG_EVENT_SUMMARY;
truncatetableSTATS$WAITSTAT;
truncatetableSTATS$ENQUEUE_STAT;
truncatetableSTATS$SQL_SUMMARY;
truncatetableSTATS$SQL_STATISTICS;
truncatetableSTATS$SQLTEXT;
truncatetableSTATS$PARAMETER;
truncatetableSTATS$RESOURCE_LIMIT;
truncatetableSTATS$DLM_MISC;
truncatetableSTATS$UNDOSTAT;
truncatetableSTATS$SQL_PLAN;
truncatetableSTATS$SQL_PLAN_USAGE;
truncatetableSTATS$SEG_STAT;
truncatetableSTATS$SEG_STAT_OBJ;
truncatetableSTATS$DB_CACHE_ADVICE;
truncatetableSTATS$PGASTAT;
truncatetableSTATS$INSTANCE_RECOVERY;

deletefromSTATS$SNAPSHOT;
deletefromSTATS$DATABASE_INSTANCE;

commit;

RemThisisrequiredtoallowfurthersnapshotstoworkwithout
Remrecreatingpackageorrestartingtheinstance
alterpackagestatspackcompile;

prompt
promptTruncateoperationcomplete
prompt


/*-------------------------------------------------------------------------*/

spooloff;

wheneversqlerrorcontinue;
setechoon;
[orapaid@bj37admin]$

--¾Åµ÷ÕûstatspackµÄÊÕ¼¯ÃÅÏÞ


SQL>executestatspack.snap(i_snap_level=>0,i_modify_parameter=>true);


SQL>executestatspack.snap(i_snap_level=>10);


SQL>executestatspack.snap(i_snap_level=>5);



--ͨ¹ýÏÂÁÐÓï¾äÐÞ¸ÄÃÅÏÞµÄĬÈÏÖµ


SQL>executestatspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);


--10ÕûÀí·ÖÎö±¨¸æ


--11ÓÃshellÉú³ÉÐÔÄÜ·ÖÎö±¨¸æ(Ò²¿ÉÒÔÊÖ¹¤Éú³É)

ORACLE_SID=$ORACLE_SID
EXPORTORACLE_SID
ORACLE_HOME=cat/etc/oratab|grep^$ORACLE_SID:|cut-f2-d:
exportORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
EXPORTPATH

echo"pleaseenterthenumberofsecondsbetweensnapshots."
readelapsed

$ORACLE_HOME/bin/sqlplus-sperfstat/perfstat<<!
executestatspack.snap;
exit
!

sleep$elapsed
$ORACLE_HOME/bin/sqlplus-sperfstat/perfstat<<!
executestatspack.snap;

selectname,snap_id,to_char(start_time,yyyymmdd:hh24:mi:ss)
from
stats$snapshot,v$database

wheresnap_id>(selectmax(snap_id)-2fromstats$snapshot);


--stats$sql_statisticsÊÓͼ
--Õâ¸öÊÓͼÓÃÓÚͳ¼ÆϵͳSQLµÄ×Ü¿ªÏúÒÔ¼°SQL±»ÖØÓõİٷֱÈÌرðÓÐÓÃ

setlines80;
setpages999;
columnmydateheadingYr.MoDyHrformata16;
columnc1headingTotSQLformat999,999,999;
columnc2headingSINGLEUSESQLformat999,999;
columnc3headingPercentre_usedSQLformat999,999;
columnc4headingTOTALSQLRAMformat999,999,999;


breakonmydateskip2;


selectto_char(snap_time,yyyy-mm-ddhh24)mydate,
total_sqlc1,
single_use_sqlc2,
(single_use_sql/total_sql)*100c3,
total_sql_memc4
fromstats$sql_statistiCSSq,
stats$snapshotsn
wheresn.snap_id=sq.snap_id;

--oracle8istats$sqltextÓÃÀ´É¾³ýstats_sql_summary±íÏà¹ØµÄ¾Þ´ó´æ´¢¿ªÏú.
--oracle9istats$sqltext(Ö»´æ´¢sqlÔ´´úÂë)

select*fromstats$sql_text

--stats$latch_misses_summary¼Ç¼ÁËoracleÊý¾Ý¿âµÄËø´æʧ°Ü

select*fromstats$latch_misses_summary
--Éú³ÉËø´æ±¨¸æ

selectsn.snap_time,sl.parent_name,sl.where_in_code,sum(nwfail_count)sum_nwfail,sum(sleep_count)sum_sleep
fromstats$latch_misses_summarysl,stats$snapshotsn
wheresn.snap_id=sl.snap_idgroupbysn.snap_time,sl.parent_name,sl.where_in_code;

--stats$sql_summary±í

--sqlͳ¼Æ»ã×ÜÊÇstatspack¹¤¾ßÖÐ×îÖØÒªµÄ±íÖ®Ò».
--sqlµ÷Õû¾

原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

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

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

入门没那么困难，精通没那么容易

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

总感觉自己还是不会SQL

原来公司用过MYSQL自己也只是建个表写个SQL