＋设为首页 ＋添加到收藏夹
--1.´´½¨ perfstat ±í¿Õ¼ä
--2.ÒÑsysµÇ½ִÐÐÏÂÁнű¾ 【程序编程相关:Java网络编程之URI、URL研究(下】
create tablespace perfstat datafile ´/home/orapaid/oradata/prdyp/perfstat.dbf´ size 500m; 【推荐阅读:Java网络编程之URI、URL研究(上】
@/home/orapaid/product/92/rdbms/admin/catdbsyn.sql 【扩展信息:MY SQL 常用命令】 @/home/orapaid/product/92/rdbms/admin/dbmspool.sql--3.ÔËÐа²×°½Å±¾
--ÒÑsysÓû§µÇ¼select instance_name,host_name,version,startup_time from v$instance;
select file_name from dba_data_files;
create tablespace perfstat datafile ´/home/orapaid/oradata/prdyp/perfstat.dbf´ size 500m;
--°²×°Ç°Òª×öµÄÊÂ
Ò». ϵͳ²ÎÊý ΪÁËÄܹ»Ë³Àû°²×°ºÍÔËÐÐ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> alter system set timed_statistics = true;
system alteredalter system set timed_statistics = false;
--°²×°Ç°²é¿´´ËĿ¼ÏµÄÎļþ
sql>host dir sp* oracle 8i Ö´ÐÐ $oracle_home/rdbms/admin/statscre.sql oracle 9i Ö´ÐÐ $oracle_home/rdbms/admin/spcreate.sql@/home/newvers/product/92/rdbms/admin/spcreate
--°²×°ºó²é¿´´ËĿ¼ÏµÄÎļþ(Ó¦¸Ã¶àÁ˼¸¸ö.lisÎļþ)
sql>host dir sp* sql>host find ¡°ora-¡° *.lissql>host find "err" *.lis
--ÔÚunixÉÏ£¬Äã¿ÉÒÔͨ¹ýÒÔÏÂÃüÁî²é¿´ÏàÓ¦µÄ´íÎóÐÅÏ¢$ ls *.lis
$ grep ora- *.lis $ grep err *.lisÔÚÕâÒ»²½£¬Èç¹û³öÏÖ´íÎó£¬ÄÇôÄã¿ÉÒÔÔËÐÐspdrop.sql½Å±¾À´É¾³ýÕâЩ¶ÔÏó¡£È»ºóÖØÐÂÔËÐÐspcreate.sqlÀ´´´½¨ÕâЩ¶ÔÏó¡£ÔËÐÐ sql*plus, ÒÔ¾ßÓÐsysdba ȨÏÞµÄÓû§µÇ½£º
/*
Èý. ²âÊÔ°²×°ºÃµÄstatspack
ÔËÐÐstatspack.snap¿ÉÒÔ²úÉúϵͳ¿ìÕÕ£¬ÔËÐÐÁ½´Î£¬È»ºóÖ´ÐÐspreport.sql¾Í¿ÉÒÔÉú³ÉÒ»¸ö»ùÓÚÁ½¸öʱ¼äµãµÄ±¨¸æ¡£ Èç¹ûÒ»ÇÐÕý³££¬ËµÃ÷°²×°³É¹¦¡£ÒÑ perfstat/perfstatµÇ½
*/sql>execute statspack.snap
sql>execute statspack.snap
sql>@/home/newvers/product/92/rdbms/admin/spreportsql>@/home/newvers/product/92/rdbms/admin/spauto --ËÄ.ʹstatspack×Ô¶¯ÊÕ¼¯ÏµÍ³×´¿ö
alert system set job_queue_processes = 10;
alert system set job_queue_processes = 10 scope=both;
alter system set timed_statistics=true; [orapaid@bj37 admin]$ cat spauto.sql rem rem $header: spauto.sql 16-feb-00.16:49:37 cdialeri exp $ rem rem spauto.sql rem rem copyright (c) oracle corporation 1999, 2000. all rights reserved. rem rem name rem spauto.sql rem rem description rem sql*plus command file to automate the collection of statpack rem statistics. rem rem notes rem should be run as the statspack owner, perfstat. rem requires job_queue_processes init.ora parameter to be rem set to a number >0 before automatic statistics gathering rem will run. rem rem modified (mm/dd/yy) rem cdialeri 02/16/00 - 1191805 rem cdialeri 12/06/99 - 1059172, 1103031 rem cdialeri 08/13/99 - created rem spool spauto.lis--
-- schedule a snapshot to be run on this instance every hour, on the hourvariable jobno number;
variable instno number; begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, ´statspack.snap;´, trunc(sysdate+1/24,´hh´), ´trunc(sysdate+1/24,´´hh´´)´, true, :instno); commit; end; / prompt prompt job number for automated statistics collection for this instance prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt note that this job number is needed when modifying or removing prompt the job: print jobnoprompt
prompt job queue process prompt ~~~~~~~~~~~~~~~~~ prompt below is the current setting of the job_queue_processes init.ora prompt parameter - the value for this parameter must be greater prompt than 0 to use automatic statistics gathering: show parameter job_queue_processes promptprompt
prompt next scheduled run prompt ~~~~~~~~~~~~~~~~~~ prompt the next scheduled run for this job is: select job, next_date, next_sec from user_jobs where job = :jobno;spool off;
--Îå.Éú³É·ÖÎö±¨¸æ µ÷ÓÃ@/home/orapaid/product/92/rdbms/admin/spreportÉú³É´Ë¹ý³ÌÖÐÒªÊäÈ뿪ʼ¿ìÕÕ ºÍ ÖÕÖ¹¿ìÕÕ ±àºÅ
--Áù ÒƳý¶¨Ê±ÈÎÎñ
sql>execute dbms_job.remove(´job_id´);--Æß É¾³ýÀúÊ·Êý¾Ý
ɾ³ýstats$snapshot±íÖÐÊý¾Ý ,ÆäËû±íÖеÄÊý¾Ý»áÏàÓ¦µÄ¼¶Á¬É¾³ý
oracleÌṩÁËÓÃÓÚ truncate ÕâЩͳ¼ÆÐÅÏ¢±íµÄ ½Å±¾
[orapaid@bj37 admin]$ cat sptrunc.sql
rem rem $header: sptrunc.sql 19-feb-2002.11:36:28 vbarrier exp $ rem rem sptrunc.sql rem rem copyright (c) 2000, 2002, oracle corporation. all rights reserved. rem rem name rem sptrunc.sql - statspack - truncate tables rem rem description rem truncates data in statspack tables rem rem notes rem should be run as statspack user, perfstat. rem rem the following tables should not be truncated rem stats$level_description rem stats$idle_event rem stats$statspack_parameter rem rem modified (mm/dd/yy) rem vbarrier 03/05/02 - segment statistics rem cdialeri 04/13/01 - 9.0 rem cdialeri 09/12/00 - sp_1404195 rem cdialeri 04/11/00 - 1261813 rem cdialeri 03/15/00 - created remundefine anystring
set showmode off echo off; whenever sqlerror exit;spool sptrunc.lis
/* ------------------------------------------------------------------------- */
... 下一页