＋设为首页 ＋添加到收藏夹
prompt
prompt warning 【程序编程相关:Java网络编程之URI、URL研究(下】 【推荐阅读:Java网络编程之URI、URL研究(上】prompt ~~~~~~~ 【扩展信息:MY SQL 常用命令】 prompt running sptrunc.sql removes all data from statspack tables. you may prompt wish to export the data before continuing. prompt prompt prompt about to truncate statspack tables prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt if you would like to continue, press <return> prompt prompt prompt &return entered - starting truncate operationtruncate table stats$filestatxs;
truncate table stats$tempstatxs; truncate table stats$latch; truncate table stats$latch_children; truncate table stats$latch_misses_summary; truncate table stats$latch_parent; truncate table stats$librarycache; truncate table stats$buffer_pool_statistics; truncate table stats$rollstat; truncate table stats$rowcache_summary; truncate table stats$sga; truncate table stats$sgastat; truncate table stats$sysstat; truncate table stats$sesstat; truncate table stats$system_event; truncate table stats$session_event; truncate table stats$bg_event_summary; truncate table stats$waitstat; truncate table stats$enqueue_stat; truncate table stats$sql_summary; truncate table stats$sql_statistics; truncate table stats$sqltext; truncate table stats$parameter; truncate table stats$resource_limit; truncate table stats$dlm_misc; truncate table stats$undostat; truncate table stats$sql_plan; truncate table stats$sql_plan_usage; truncate table stats$seg_stat; truncate table stats$seg_stat_obj; truncate table stats$db_cache_advice; truncate table stats$pgastat; truncate table stats$instance_recovery;delete from stats$snapshot;
delete from stats$database_instance;commit;
rem this is required to allow further snapshots to work without
rem recreating package or restarting the instance alter package statspack compile;prompt
prompt truncate operation complete prompt /* ------------------------------------------------------------------------- */spool off;
whenever sqlerror continue;
set echo on; [orapaid@bj37 admin]$--¾Åµ÷ÕûstatspackµÄÊÕ¼¯ÃÅÏÞ
sql>execute statspack.snap(i_snap_level=>0 ,i_modify_parameter=>´true´); sql>execute statspack.snap(i_snap_level=>10); sql>execute statspack.snap(i_snap_level=>5);
--ͨ¹ýÏÂÁÐÓï¾äÐÞ¸ÄÃÅÏÞµÄĬÈÏÖµ
sql>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000); --10 ÕûÀí·ÖÎö±¨¸æ --11ÓÃshell Éú³ÉÐÔÄÜ·ÖÎö±¨¸æ(Ò²¿ÉÒÔÊÖ¹¤Éú³É)oracle_sid=$oracle_sid
export oracle_sid oracle_home=´cat /etc/oratab|grep ^$oracle_sid:|cut -f2 -d´:´ export oracle_home path=$oracle_home/bin:$path export pathecho "please enter the number of seconds between snapshots."
read elapsed$oracle_home/bin/sqlplus -s perfstat/perfstat<<!
execute statspack.snap; exit !sleep $elapsed
$oracle_home/bin/sqlplus -s perfstat/perfstat<<! execute statspack.snap;select name,snap_id,to_char(start_time,´yyyymmdd:hh24:mi:ss´)
from stats\$snapshot ,v\$databasewhere snap_id>(select max(snap_id)-2 from stats\$snapshot);
--stats$sql_statistics ÊÓͼ --Õâ¸öÊÓͼÓÃÓÚͳ¼Æ ϵͳsqlµÄ×Ü¿ªÏúÒÔ¼°sql±»ÖØÓõİٷֱÈÌرðÓÐÓÃset lines 80;
set pages 999; column mydate heading ´yr.mo dy hr´ format a16; column c1 heading ´tot sql´ format 999,999,999; column c2 heading ´single use sql´ format 999,999; column c3 heading ´percent re_used sql´ format 999,999; column c4 heading ´total sql ram´ format 999,999,999; break on mydate skip 2; select to_char(snap_time,´yyyy-mm-dd hh24) mydate, total_sql c1, single_use_sql c2, (single_use_sql/total_sql)*100 c3, total_sql_mem c4 from stats$sql_statistics sq, stats$snapshot sn where sn.snap_id=sq.snap_id;--oracle 8i stats$sqltextÓÃÀ´É¾³ýstats_sql_summary±íÏà¹ØµÄ¾Þ´ó´æ´¢¿ªÏú.
--oracle 9i stats$sqltext(Ö»´æ´¢sqlÔ´´úÂë)select * from stats$sql_text
--stats$latch_misses_summary ¼Ç¼ÁËoracleÊý¾Ý¿âµÄËø´æʧ°Ü
select * from stats$latch_misses_summary
--Éú³ÉËø´æ±¨¸æselect sn.snap_time ,sl.parent_name,sl.where_in_code,sum(nwfail_count) sum_nwfail, sum(sleep_count) sum_sleep
from stats$latch_misses_summary sl ,stats$snapshot sn where sn.snap_id=sl.snap_id group by sn.snap_time,sl.parent_name,sl.where_in_code ;--stats$sql_summary ±í
--sqlͳ¼Æ»ã×ÜÊÇstatspack ¹¤¾ßÖÐ×îÖØÒªµÄ±íÖ®Ò».
--sqlµ÷Õû¾³£¿ÉÒÔ¼«´óµÄÓ°ÏìoracleϵͳµÄÐÔÄÜ
--stats$parameter
select * from stats$parameter where name like ´%optimizer_mode%´
****************************************************************
statspackϵͳ±ístats$rollstat
stats$latch
select * from stats$latch_children
select * from stats$librarycache(¿â¸ßËÙ»º´æ±íÏîµÄÃüÖÐÂʶ¼Òª±£³ÖÔÚ90%ÒÔÉÏ ·ñÔòÒª¶Ôsga oracle ¹²Ïí³Ø ½øÐе÷Õû)select * from stats$waitstat where wait_count>0
Åж϶ÔÏóÊÇ·ñ²»ÕýÈ·µÄ´æ´¢²ÎÊýÉèÖà ,×îºÃµÄ;¾¶Ö®Ò»¾ÍÊǹ۲ì×ÔÓɱíµÈ´ý Èç¹û×ÔÓɱíµÈ´ý·Ç³£¸ß,¾Í˵Ã÷ËùÓµÓеıí´æÔÚ¾ºÕùÐÔµÄinert»òÕßupdateÈÎÎñ ÕâЩ±íûÓж¨Òå×ã¹»µÄ×ÔÓɱíselect * from stats$enqueue_stat
select * from stats$enqueue_stat where failed_req#>0 ·ÖÎöstats$enqueue_stat ±íµÄʱºò ºÜÖØÒªµÄÒ»µã¾ÍÊÇÒª¼Çס¶ÓÁеȴýÊÇoracle´¦ÀíµÄÕý³£²¿·Ö select * from stats$sysstatselect * from stats$sesstat
select * from v$statname
select * from stats$sgastat
*************************************************************************** statspackÊÂÎñ±í stats$buffer_pool_statisticsselect * from stats$buffer_pool_statistics
Õâ¸ö±íÊÇ»º³å³ØЧÂʵÄÒ»°ã¶ÈÁ¿ select * from stats$filestatxs stats$filestatxs ÊǹØÓÚoracle µ÷ÕûµÄ×îÖØÒªµÄ±íÖ®Ò» °üÀ¨oracleÊý¾ÝÎļþµÄÏêϸÐÅÏ¢,°üÀ¨¶ÁÈëioÊýÁ¿ ,дÈëioÊýÁ¿ ÒÔ¼°´¦Àí¹ý³Ì¾ÀúµÄµÈ´ýÕùÓÃi/o×Óϵͳ¸ºÔØƽºâ
ÕÒµ½"Èȵã"ÎļþÒÔ¼°Èȵã±í ÕÒµ½Êý¾Ý¿â¶ÁÈëºÍдÈë»î¶¯µÄ·åֵʼþ****************************************************
statspackʼþ±íselect * from stats$system_event
select * from stats$session_event
select * from stats$idle_event
select * from stats$bg_event_summary stats$bg_event_summary »ã×ÜÁËËùÓÐÊý¾Ý¿âʵÀýµÄºǫ́ʼþ ºÍstats$system_event
******************************************************************************************************************
--À©Õ¹statspack ÊÕ¼¯·þÎñÆ÷ͳ¼Æ
--ÿ¸ô2Ãë ¹²ÊÕ¼¯5´Î[newvers@bj37 newvers]$ vmstat 2 5
procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 0 0 0 118136 49908 98268 1668004 0 0 1 0 0 0 0 0 1 0 0 0 118136 51472 98276 1668012 0 0 4 88 177 790 2 0 97 0 0 0 118136 51468 98276 1668012 0 0 0 24 116 39 0 0 100 ... 下一页