--1.´´½¨ perfstat ±í¿Õ¼ä
create tablespace perfstat datafile '/home/orapaid/oradata/prdyp/perfstat.dbf' size 500m;
--2.ÒÑsysµÇ½ִÐÐÏÂÁнű¾
@/home/orapaid/product/92/rdbms/admin/catdbsyn.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 altered
alter 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-¡° *.lis
sql>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/spreport
SQL>@/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 hour
variable 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 jobno
prompt
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
prompt
prompt
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
Rem
undefine anystring
set showmode off echo off;
whenever sqlerror exit;
spool sptrunc.lis
/* ------------------------------------------------------------------------- */
prompt
prompt Warning
prompt ~~~~~~~
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
1<return>
2prompt
3prompt
4prompt &return Entered - starting truncate operation
5
6truncate table STATS$FILESTATXS;
7truncate table STATS$TEMPSTATXS;
8truncate table STATS$LATCH;
9truncate table STATS$LATCH_CHILDREN;
10truncate table STATS$LATCH_MISSES_SUMMARY;
11truncate table STATS$LATCH_PARENT;
12truncate table STATS$LIBRARYCACHE;
13truncate table STATS$BUFFER_POOL_STATISTICS;
14truncate table STATS$ROLLSTAT;
15truncate table STATS$ROWCACHE_SUMMARY;
16truncate table STATS$SGA;
17truncate table STATS$SGASTAT;
18truncate table STATS$SYSSTAT;
19truncate table STATS$SESSTAT;
20truncate table STATS$SYSTEM_EVENT;
21truncate table STATS$SESSION_EVENT;
22truncate table STATS$BG_EVENT_SUMMARY;
23truncate table STATS$WAITSTAT;
24truncate table STATS$ENQUEUE_STAT;
25truncate table STATS$SQL_SUMMARY;
26truncate table STATS$SQL_STATISTICS;
27truncate table STATS$SQLTEXT;
28truncate table STATS$PARAMETER;
29truncate table STATS$RESOURCE_LIMIT;
30truncate table STATS$DLM_MISC;
31truncate table STATS$UNDOSTAT;
32truncate table STATS$SQL_PLAN;
33truncate table STATS$SQL_PLAN_USAGE;
34truncate table STATS$SEG_STAT;
35truncate table STATS$SEG_STAT_OBJ;
36truncate table STATS$DB_CACHE_ADVICE;
37truncate table STATS$PGASTAT;
38truncate table STATS$INSTANCE_RECOVERY;
39
40delete from STATS$SNAPSHOT;
41delete from STATS$DATABASE_INSTANCE;
42
43commit;
44
45Rem This is required to allow further snapshots to work without
46Rem recreating package or restarting the instance
47alter package statspack compile;
48
49prompt
50prompt Truncate operation complete
51prompt
52
53
54/* ------------------------------------------------------------------------- */
55
56spool off;
57
58whenever sqlerror continue;
59set echo on;
60[orapaid@bj37 admin]$
61
62\--¾Åµ÷ÕûstatspackµÄÊÕ¼¯ÃÅÏÞ
63
64
65SQL>execute statspack.snap(i_snap_level=>0 ,i_modify_parameter=>'true');
66
67
68SQL>execute statspack.snap(i_snap_level=>10);
69
70
71SQL>execute statspack.snap(i_snap_level=>5);
72
73\--ͨ¹ýÏÂÁÐÓï¾äÐÞ¸ÄÃÅÏÞµÄĬÈÏÖµ
74
75
76SQL>execute statspack.modify_statspack_parameter(i_buffer_gets_th=>100000,i_disk_reads_th=>100000);
77
78
79\--10 ÕûÀí·ÖÎö±¨¸æ
80
81
82\--11ÓÃshell Éú³ÉÐÔÄÜ·ÖÎö±¨¸æ(Ò²¿ÉÒÔÊÖ¹¤Éú³É)
83
84ORACLE_SID=$ORACLE_SID
85EXPORT ORACLE_SID
86ORACLE_HOME='cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'
87export ORACLE_HOME
88PATH=$ORACLE_HOME/bin:$PATH
89EXPORT PATH
90
91echo "please enter the number of seconds between snapshots."
92read elapsed
93
94$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!--
95execute statspack.snap;
96exit
97!
98
99sleep $elapsed
100$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!
101execute statspack.snap;
102
103select name,snap_id,to_char(start_time,'yyyymmdd:hh24:mi:ss')
104from
105stats\$snapshot ,v\$database
106
107where snap_id-->(select max(snap_id)-2 from stats\$snapshot);
108
109
110\--stats$sql_statistics ÊÓͼ
111\--Õâ¸öÊÓͼÓÃÓÚͳ¼Æ ϵͳSQLµÄ×Ü¿ªÏúÒÔ¼°SQL±»ÖØÓõİٷֱÈÌرðÓÐÓÃ
112
113set lines 80;
114set pages 999;
115column mydate heading 'Yr.Mo Dy Hr' format a16;
116column c1 heading 'Tot SQL' format 999,999,999;
117column c2 heading 'SINGLE USE SQL' format 999,999;
118column c3 heading 'Percent re_used SQL' format 999,999;
119column c4 heading 'TOTAL SQL RAM' format 999,999,999;
120
121
122break on mydate skip 2;
123
124
125select to_char(snap_time,'yyyy-mm-dd hh24) mydate,
126total_sql c1,
127single_use_sql c2,
128(single_use_sql/total_sql)*100 c3,
129total_sql_mem c4
130from stats$sql_statistics sq,
131stats$snapshot sn
132where sn.snap_id=sq.snap_id;
133
134\--oracle 8i stats$sqltextÓÃÀ´É¾³ýstats_sql_summary±íÏà¹ØµÄ¾Þ´ó´æ´¢¿ªÏú.
135\--oracle 9i stats$sqltext(Ö»´æ´¢sqlÔ´´úÂë)
136
137select * from stats$sql_text
138
139\--stats$latch_misses_summary ¼Ç¼ÁËoracleÊý¾Ý¿âµÄËø´æʧ°Ü
140
141select * from stats$latch_misses_summary
142\--Éú³ÉËø´æ±¨¸æ
143
144select sn.snap_time ,sl.parent_name,sl.where_in_code,sum(nwfail_count) sum_nwfail, sum(sleep_count) sum_sleep
145from stats$latch_misses_summary sl ,stats$snapshot sn
146where sn.snap_id=sl.snap_id group by sn.snap_time,sl.parent_name,sl.where_in_code ;
147
148\--stats$sql_summary ±í
149
150\--sqlͳ¼Æ»ã×ÜÊÇstatspack ¹¤¾ßÖÐ×îÖØÒªµÄ±íÖ®Ò».
151\--sqlµ÷Õû¾³£¿ÉÒÔ¼«´óµÄÓ°ÏìoracleϵͳµÄÐÔÄÜ
152
153\--stats$parameter
154
155select * from stats$parameter where name like '%optimizer_mode%'
156
157****************************************************************
158STATSPACKϵͳ±í
159
160stats$rollstat
161
162stats$latch
163
164select * from stats$latch_children
165
166
167select * from stats$librarycache(¿â¸ßËÙ»º´æ±íÏîµÄÃüÖÐÂʶ¼Òª±£³ÖÔÚ90%ÒÔÉÏ ·ñÔòÒª¶ÔSGA oracle ¹²Ïí³Ø ½øÐе÷Õû)
168
169select * from stats$waitstat where wait_count>0
170Åж϶ÔÏóÊÇ·ñ²»ÕýÈ·µÄ´æ´¢²ÎÊýÉèÖà ,×îºÃµÄ;¾¶Ö®Ò»¾ÍÊǹ۲ì×ÔÓɱíµÈ´ý
171Èç¹û×ÔÓɱíµÈ´ý·Ç³£¸ß,¾Í˵Ã÷ËùÓµÓеıí´æÔÚ¾ºÕùÐÔµÄinert»òÕßupdateÈÎÎñ
172ÕâЩ±íûÓж¨Òå×ã¹»µÄ×ÔÓɱí
173
174select * from stats$enqueue_stat
175select * from stats$enqueue_stat where failed_req#>0
176·ÖÎöstats$enqueue_stat ±íµÄʱºò ºÜÖØÒªµÄÒ»µã¾ÍÊÇÒª¼Çס¶ÓÁеȴýÊÇoracle´¦ÀíµÄÕý³£²¿·Ö
177
178
179select * from stats$sysstat
180
181select * from stats$sesstat
182
183select * from v$statname
184
185select * from stats$sgastat
186***************************************************************************
187statspackÊÂÎñ±í
188stats$buffer_pool_statistics
189
190select * from stats$buffer_pool_statistics
191Õâ¸ö±íÊÇ»º³å³ØЧÂʵÄÒ»°ã¶ÈÁ¿
192
193
194select * from stats$filestatxs
195stats$filestatxs ÊǹØÓÚoracle µ÷ÕûµÄ×îÖØÒªµÄ±íÖ®Ò»
196°üÀ¨oracleÊý¾ÝÎļþµÄÏêϸÐÅÏ¢,°üÀ¨¶ÁÈëIOÊýÁ¿ ,дÈëIOÊýÁ¿ ÒÔ¼°´¦Àí¹ý³Ì¾ÀúµÄµÈ´ýÕùÓÃ
197
198I/O×Óϵͳ¸ºÔØƽºâ
199ÕÒµ½"Èȵã"ÎļþÒÔ¼°Èȵã±í
200ÕÒµ½Êý¾Ý¿â¶ÁÈëºÍдÈë»î¶¯µÄ·åֵʼþ
201
202****************************************************
203statspackʼþ±í
204
205select * from stats$system_event
206
207select * from stats$session_event
208
209select * from stats$idle_event
210
211
212select * from stats$bg_event_summary
213stats$bg_event_summary »ã×ÜÁËËùÓÐÊý¾Ý¿âʵÀýµÄºǫ́ʼþ
214ºÍstats$system_event
215
216******************************************************************************************************************
217
218\--À©Õ¹statspack ÊÕ¼¯·þÎñÆ÷ͳ¼Æ
219\--ÿ¸ô2Ãë ¹²ÊÕ¼¯5´Î
220
221[newvers@bj37 newvers]$ vmstat 2 5
222procs memory swap io system cpu
223r b w swpd free buff cache si so bi bo in cs us sy id
2240 0 0 118136 49908 98268 1668004 0 0 1 0 0 0 0 0 1
2250 0 0 118136 51472 98276 1668012 0 0 4 88 177 790 2 0 97
2260 0 0 118136 51468 98276 1668012 0 0 0 24 116 39 0 0 100
2270 0 0 118136 49608 98276 1668092 0 0 30 138 286 1144 4 1 95
2280 0 0 118136 51340 98276 1668116 0 0 12 92 183 263 0 0 100
229
230r ÔËÐжÓÁÐ µ±Õâ¸öÖµ³¬¹ý·þÎñÆ÷ÉÏcpuµÄÊýÁ¿ ¾Í»á´æÔÚcpuÆ¿¾±
231pi ÊÇÒ³»»Èë Ò³»»Èë²Ù×÷±íʾ·þÎñÆ÷³öÏÖÁËÄÚ´æ¶Ìȱ
232us ÊÇÓû§cpu
233sy ÊÇϵͳcpu
234id ÊÇ¿ÕÏÐ
235wa Êǵȴý
236ËùÓеÄcpuÖµ(us+sy+id+wa)×ÜÊǵÈÓÚ100%
237
238\--ʹÓÃvmstat ʶ±ðcpuÆ¿¾±
239
240linux ²é¿´cpuÊýÁ¿
241/proc/Ŀ¼ÏÂÓг£¼ûµÄϵͳÐÅÏ¢
242cat /proc/cpuinfo|grep processor|wc -l
243
244ibm aix ºÍhp_ux
245lsdev -C|grep Processor|wc -l
246
247SolarisÖÐÏÔʾcpuµÄÊýÁ¿
248psrinfo -v|grep "Status of processor"|wc -l
249
250\--vmstat ʶ±ðƵ·±Ê¹ÓõÄcpu
251
252ÒªÊÇusÓësyµÄºÍ±Æ½ü100 ¾Í±íʾcpuÔÚÂú¸ººÉÔËÐÐ
253ΨһÄÜÖ¸³öcpuÆ¿¾±µÄ¶ÈÁ¿ÊÇÔËÐжÓÁÐ"r"µÄÖµ
254³¬¹ýÁËcpuµÄÊýÁ¿
255
256ʶ±ðRAM ÄÚ´æÆ¿¾±
257
258hp/ux ÏÔʾÄÚ´æ´óС
259
260dmesg
261
262ÏÔʾ dec-unix ÄÚ´æ´óС
263
264uerf -r 300|grep -i mem
265
266aix ÉÏÏÔʾÄÚ´æ´óС
267
268µÚÒ»²½ : lsdev -C|grep mem
269µÚ¶þ²½ : lsattr -El mem0
270
271ÏÔʾsqlarisÉÏÄÚ´æ´óС
272
273prtconf|grep -i mem
274
275\--ʹÓÃtop¹¤¾ßÏÔʾRAM
276
277top -d 2
278ÿ¸ô2ÃëË¢ÐÂÒ»´Î
279
280\--hpºÍsolaris¿ÉÊÇÓÃglance²é¿´ÄÚ´æ
281
282RAMÄÚ´æºÍ½»»»´ÅÅÌ
283ÒòΪ½»»»(pi)»á»¨·Ñ´óÁ¿µÄʱ¼ä´Ó½»»»´ÅÅÌÉϽ«ÄÚ´æ¶Î¸´ÖÆ»ØRAM,ËùÒÔËû»á¼õÂý·þÎñÆ÷µÄËÙ¶È,ÔÚ
284oracleÊý¾Ý¿â·þÎñÆ÷ÉÏ,½â¾öÒ³»»ÈëÎÊÌâµÄ·½°¸:
285¸üСµÄSGA
286¸ü¶àµÄRAM
287¼õÉÙRAMÐèÇó(¼õÉÙ¶Ô³ÌÐòÈ«¾ÖÇøµÄ(PGA)ÄÚ´æµÄÐèÇóÀ´¼õÉÙÊý¾Ý¿â·þÎñÆ÷µÄRAMÏûºÄ.)
288
289\--ÔÚaixÖмì²âDZÔÚµÄI/OÆ¿¾±
290
291waÁбíʾÏÖÔڵȴýÍⲿos·þÎñµÄcpu°Ù·Ö±È ¸ß²»Ò»¶¨¾ÍÊÇÓÐI/OÆ¿¾±
292
293»ñÈ¡vmstatÐÅÏ¢µÄ½Å±¾
294
295connect perfstat/perfstat
296
297drop table stats$vmstat
298
299create table stats$vmstat
300(
301start_date date,
302duration number,
303server_name varchar2(20) ,
304runque_waits number,
305page_in number,
306page_out number,
307user_cpu number,
308system_cpu number,
309idle_cpu number,
310wait_cpu number
311
312)
313tablespace perfstat
314storage(initial 10m
315next 1m
316pctincrease 0);
317
318
319\--linux²Ù×÷ϵͳµÄVMSTAT »ñÈ¡¹¤¾ß½Å±¾
320
3211.±ØÐ뽫ORACLE_HOMEÉèÖõ½ÄãµÄĿ¼:
322
323ORACLE_HOME=/home/newvers/product/92
3242.±ØÐëÔÚsqlplusÃüÁîÖÐÉèÖÃORACLE_SID
325$ORACLE_HOME/bin/sqlplus perfstat/perfstat@prdyp<<eof $2}'="" ${sample_time}="" '{print="" -a|awk="" -u="" 2="" 3.±øðëí¨¹ýéèöãsample_time="" \--get_vmstat.ksh="" do="" export="" oracle_home="" path="" sample_time="300" server_name="" true="" typeset="" vmstat="" while="" ¸ä±ä²éñùê±¼ä:="">/tmp/msg$$
326
327# run vmstat and direct the output into the Oracle table
328
329cat /tmp/msg$$|sed 1,3d| awk '{printf("%s %s %s %s %s %s\n",$1,$8,$9,$14,$15,$16)}' |while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU
330do
331$ORACLE_HOME/bin/sqlplus -a perfstat/perfstat@prdyp<<eof $idle_cpu,="" $page_in,="" $page_out,="" $runque,="" $sample_time,="" $system_cpu,="" $user_cpu,="" '$server_name',="" (local="NO)" );="" *="" ******************************************************************************************************************="" ***********************************************************************************************************************="" --="" ---pid="" --ni="" --pri="" --size="" --user="" --°´ðð´¦àíååðò="" --±¾µøᬽó="" --²¢ðð²éñ¯½ø³ì1(òòîªéèöãáë±íµä²¢ðð¶è,ëùòôºóì¨æô¶¯áë)="" --²¢ðð²éñ¯½ø³ì2="" --²¢ðð²éñ¯½ø³ì3="" --²¢ðð²éñ¯½ø³ì4="" --²¢ðð²éñ¯½ø³ì5="" --·ö²¼ê½»ö¸´="" --·þîñæ÷="" --¹éµµ½ø³ì1="" --¹éµµ½ø³ì2="" --¼ì²éµã½ø³ì="" --½ø³ì¼àêóæ÷½ø³ì="" --èõö¾ð´èëæ÷½ø³ì="" --êý¾ý¿âð´èëæ÷½ø³ì="" --ïµí³¼àêóæ÷½ø³ì="" -elf|more="" 0="" 00:00:00="" 00:00:05="" 00:00:09="" 00:00:28="" 00:00:34="" 00:01:08="" 00:01:38="" 00:02:06="" 00:02:17="" 00:02:21="" 00:06:28="" 00:12:53="" 00:17:15="" 00:17:18="" 00:17:33="" 1="" 1.µ÷õû·þîñæ÷»·¾³êçµ÷õûoracleêý¾ý¿âµäïè¾öìõ¼þ="" 1.µúò»¸öêç¹ýè¥1·ööóäúµä¼´ê±¸ºôø="" 10195="" 10197="" 10199="" 11849="" 11851="" 11853="" 11855="" 11857="" 1382="" 1384="" 1386="" 1388="" 1390="" 1392="" 1394="" 1398="" 1400="" 1402="" 1404="" 1410="" 14:20="" 17:22="" 2.µú¶þ¸öêç¹ýè¥5·ööóµä¸ºôøæ½¾ùöµ="" 2.½øðð×î´ó»¯àûóãêçò»¸öéñꥵä俱ê="" 3.µúèý¸öêç¹ýè¥15·ööóµä¸ºôøæ½¾ùöµ="" 7180="" 8672="" ;="" <="" ?="" \--rpt_top_sql.sql="" \--±¨¸æ·þîñæ÷í³¼æ="" \--²é¿´ºó쨽ø³ì="" \--²é¿´óåï輶="" \--²éõò10µãºíïâîç3µã×îïûºäcpuµäsql_text="" \--µ÷õûcpuïûºä="" \--µ÷õû·þîñæ÷»·¾³="" \--µ÷õûäú´æïûºä="" \--·þîñæ÷äú´æéèöã="" \--·þîñæ÷èîîñ¸ºôø潺â="" \--èç¹ûäú´æ·¢éúò³»»èë,¿éòôê¹óã="" \--ôúïß·þîñæ÷¼àê󹤾ß="" a,="" a.snap_id="sn.snap_id" and="" averages="" by="" cpu="" db_block_buffersµäöµà´¼õéùäú´æµäðèòª="" dba="" desc="" dnnn="" done="" eof="" exit="" feb16="" feb26="" from="" grep="" hh24'),="" insert="" into="" load="" msg$$="" newvers="" ni="" or="" ora="" ora_arc0_prdyp="" ora_arc1_prdyp="" ora_cjq0_prdyp="" ora_ckpt_prdyp="" ora_d000_prdyp="" ora_dbw0_prdyp="" ora_lgwr_prdyp="" ora_p000_prdyp="" ora_p001_prdyp="" ora_p002_prdyp="" ora_p003_prdyp="" ora_p004_prdyp="" ora_pmon_prdyp="" ora_qmn0_prdyp="" ora_reco_prdyp="" ora_s000_prdyp="" ora_smon_prdyp="" oracle="" oracleprdyp="" order="" paddr="" perfstat.stats\$vmstat="" ps="" pts="" rm="" rows_processed="" select="" shared_pool»òõß="" sn="" stats$snapshot="" stats$sql_summary="" substr(sql_text,1,50)="" sysdate,="" tmp="" to_char(snap_time,'hh24')="15" to_char(snap_time,'yyyy-mm-dd="" top="" topìṩáëºü¶àðåï¢="" v$bgprocess="" values(="" where="" ²é¿´cpuµäïêï¸çé¿ö="" ´ó¶àêý·þîñæ÷½¨ò齫½»»»´ååìµä´óð¡éèöãîªîïàíramµäò»±¶´óð¡="" µ÷¶è½ø³ì="" ¶¼¿éòôèï¦àíæ÷´¦óú¹ýôø×´ì¬="" ¶àóàµä´¦àíäüᦺíram½«ºüäñôùàûóã="" ¶ô½»»»à´½²×¼ûµä²¹¾è·½ê½¾íê罫éùsgaµä´óð¡»òõßîªêý¾ý¿â·þîñæ÷ôö¼óäú´æ="" ·öåéóåï輶="" ·þîñæ÷³öïöáë¹ýôø="" ¸ºôøæ½¾ù="" ¸ºôøæ½¾ùöµ="" ¸ä±äniceöµ="" ¸÷èîîñµääú´æ´óð¡="" ¹²óð3¸ööµ="" ¼õéùsort_area_size="" ½ø³ìid="" ¾í²»¿éäüóãèîºîoracleµ÷õûà´½â¾öðôäüîêìâ="" ×´ì¬="" è·¶¨cpu·åöµê±¼ä,±üãâôú·åöµê±¼äö´ððåú´¦àí³ìðòºí¶ôcpuê¹óã¹ý¸ßµä³ìðò(¾¡á¿½«æäתòæµ½ò¹ííö´ðð)="" èç¹û·þîñæ÷éïµäcpu»òõßäú´æ´æôú¹ýôø»òõß濾±="" êµïö¶àïß³ì·þîñæ÷òô¼°¼õéù="" ê¹óãtopºísar¹¤¾ß²é¿´cpuºíäú´æ»î¶¯µä︽ú="" ê¹óãtop¼àêó·þîñæ÷="" êç»ñµãóðð§oracleðôäüµä¹ø¼ü,ó¦¸ã·´¸´¼ì²éëùóðäúºë²îêý,òôè·±£·þîñæ÷äú´æòñ¾õýè·åäöã="" êçèîîñµä·öåéóåï輶="" îþâûèîºî걺ò,¸ºôøæ½¾ùöµ³¬¹ý1="" ñ𾿻ù±¾µääú´æ¹üàí,·þîñæ÷äú´æ·ö¸î¼¼çé="" ò²±øðëñéö¤½»»»´ååìµäåäöã,½»»»´ååìóãóú½óêü´óîïàíramò³»»³ö²âäú´æö¡="" ò³»»èë±íê¾oracle·þîñæ÷µäðèòª³¬¹ýáëramäú´æµäêýá¿="" òô²é¿´ôëðð¶óáðöµ="" ó¦¸ãᢿìôëððvmstata="" ó㻧ãû="" óåïèöµ="" óð¹øäú´æê¹óãµääúºëéèöã(shmmax,shmmni,db_max_pct)="" õ¹ê¾áë¸÷cpuµä¸ºôø="" õâ¸ö½ø³ì»áéú³éëùóððèòªµäêý¾ý¿âµ÷óã,îªó㻧²éñ¯·þîñ.ëü»á½«½á¹û·µ»ø¸øµ÷óãëüµä½ø³ìdnnn="" õâ¸ööµ³¬¹ý1±íê¾="" ö´ððê±¼ä="" ö»ðèòªáë½âæäöð¼¸áð=""> '00';
332
333\--³£¼û¹éµµ¹ÒÆðÎÊÌâµÄ´¦Àí
334ÓÉARCHÒýÆðµÄÊý¾Ý¿â¹ÒÆð?
335Êý¾Ý¿âȱʡ°²×°Ê±£¬Ò»°ã´¦Óڷǹ鵵ģʽ£¬Óû§¿É¸ù¾ÝÐèÒª¸ÄΪ¹éµµÄ£Ê½¡£
336ÔÚÐí¶àÇé¿öÏÂÓû§Ö»ÐÞ¸ÄÁËÊý ¾Ý¿âµÄģʽ£¬Ã»ÓÐÆô¶¯ARCH½ø³Ì£¬
337ÕâÖÖÇé¿öÏ£¬¾¹ýÒ»¶ÎÔËÐÐºó £¬Õû¸öÊý¾Ý¿â¹ÒÆ𣬲éѯv$session_waitÊÓͼ£¬
338»á·¢ÏÖ archive required µÄʼþ¡£ÊÖ¹¤¹éµµ»ò×Ô¶¯Æô¶¯ARCH ¼´¿É½â¾ö¡£
339
340»¹ÓÐÒ»ÖÖÇé¿öÊÇÊý¾Ý¿âÔËÐÐÔڹ鵵״̬£¬µ«Êǹ鵵ÈÕÖ¾ËùÐè¿Õ¼ä²»×㣬
341ÕâʱÊý¾Ý¿â¹ÒÆ𣬽â¾ö·½·¨Êǽâ¾ö¹éµµ¿Õ¼ä²»×ãÎÊÌ⣬ȻºóÖØÐÂÆô¶¯¹éµµ½ø³Ì¡£
342
343***************************************************************************************************************
344\--Æß.µ÷ÕûÍøÂç»·¾³
345¶Ôoracle net ×î³£¼ûµÄÎó½âÊÇ:ͨ¹ýµ÷Õûoracle ÍøÂç²ÎÊýʵÏÖÍøÂçÐÔÄܵÄÌá¸ß!
346³ýÁËÉÙÊýµÄÀýÍâÇé¿öÖ®Íâ,ËùÓеÄÍøÂçͨÐŶ¼ÊÇÔÚoracleµÄ·¶Î§Ö®Íâ,²»Äܹ»ÔÚoracle»·¾³ÄÚ²¿½øÐе÷Õû
347oracle netÖ»ÊÇOSIģʽÖÖµÄÒ»²ã ËüλÓÚÌض¨µÄÍøÂçÐÒéÕ»Ö®ÉÏ
348ʵ¼ÊÉÏËùÓеÄÍøÂçµ÷Õû¶¼ÊÇÔÚoracle »·¾³Ö®Íâ
349
350ʵ¼ÊÉ϶ÔÓÚ¸ÄÉÆÐÔÄÜÀ´½²oracle net ¿ÉÒÔ×÷µÄ¹¤×÷ºÜÉÙ
351
352DBA ¿ÉÒÔ¿ØÖÆÍøÂç°üµÄ´óСºÍƵÂÊ
353
354¿ÉÒԸıä¿ìÕյĸüмä¸ô ,ÒÔ¸üСµÄƵÂʼä¸ôÔÚÍøÂçÉÏ´«µÝ¸ü¶àµÄÊý¾Ý
355
356±¾Õ°üÀ¨ÏÂÁÐÉæ¼°ÍøÂçµ÷ÕûÎÊÌâµÄ²¿·Ö:
357ÓÅ»¯ORACLE NETÅäÖÃ
358Ó°ÏìÍøÂçÐÔÄܵÄÆäËûoracle ÌØÐÔ
359ʹÓÃSTATPACK ¼àÊÓÍøÂçÐÔÄÜ
360µ÷Õû·Ö²¼ÍøÂç
361
362\--ÓÅ»¯ORACLE Net ÅäÖÃ
363
364Óм¸¸öµ÷Õû²ÎÊý¿ÉÒÔÓ°Ïì·þÎñÆ÷¼äORACLE NETÁ¬½ÓÐÔÄÜ
365Ó¦¸ÃÔںϸñµÄÍøÂç¹ÜÀíÔ±µÄ°ïÖúϵ÷ÕûÍøÂç
366
367ÏÂÁвÎÊýÎļþ°üº¬µÄÉèÖÿÉÒÔÓ°ÏìÍøÂçÉÏ°ü´«µÝµÄ´óСºÍƵÂÊ
368
369sqlnet.ora ·þÎñÆ÷Îļþ
370.automatic_ipc
371sqlnet.ora ¿Í»§Îļþ
372.break_poll_skip
373tnsnames.ora
374.SDU TDU
375listener.ora
376.SDU TDU
377ptotocol.ora
378.tcp.nodelay
379
3801.--ptotocol.ora ÖÐµÄ .tcp.nodelay
381oracle ½¨ÒéÖ»Óе±Óöµ½TCP³¬Ê±µÄʱºò,²ÅʹÓÃtcp.nodelay
382µ±Êý¾Ý¿â·þÎñÆ÷Ö®¼äÓдóÁ¿Í¨ÐÅÇé¿öÏÂ,ÉèÖÃtcp.nodelayÄܹ»¼«´óµÄ¸ÄÉÆÐÔÄÜ
3832.--sqlnet.oraµÄautomatic_ipc
384automatic_ipc²ÎÊý»á¼ÓËÙµ½±¾µØµÄÁ¬½Ó,ÕâÊÇÒòΪËü¿ÉÒÔÔ½¹ýÍøÂç²ã.
385Èç¹ûautomatic_ipc=on ORACLE NET ¾Í»áÊ×Ïȼì²éÊÇ·ñ´æÔÚ¾ßÓÐÏàͬ±ðÃû¶¨ÒåµÄ±¾µØÊý¾Ý¿â
386Èç¹û´æÔھͻὫÁ¬½Ó½âÊÍΪһ¸ö±¾µØÁ¬½Ó,ÕâÑù¾ÍÈƹýÁËÍøÂç²ã
387
3883.ËùÓÐoracle net ¶¼Ó¦¸ÃʹÓÃÕâ¸öÉèÖÃÀ´¸ÄÉÆÐÔÄÜ
389
390\--oracle ½¨Òé¸ù¾Ý(mtu×î´ó´«Êäµ¥Ôª,Õâ¸öÖµÊǹ̶¨µÄ)ÉèÖÃSDU
391tnsnames.ora
392.SDU TDU
393listener.ora
394.SDU TDU
395
396
397\--sqlnet.ora µÄ break_poll_skip ²ÎÊý
398
399\--sqlnet.ora µÄ disable_oob ²ÎÊý
400
401epc_disabled »·¾³±äÁ¿
402Ç¿ÁÒÍƼöDBA½ûÓÃotrace
403
4041.¹Ø±ÕÊý¾Ý¿âºÍÕìÌý³ÌÐò
4052.´Ó$ORACLE_HOME/otrace/adminĿ¼ÖÐÒÆ×ß*.datÎļþ
4063.ʹÓÃUNIXµÄtouchÃüÁîÖØн¨Á¢datÎļþ
4074.ÔÚUNIX OracleµÄ .profile .login »òÕß .cshrc ×¢²áÎļþµÄÔËÐл·¾³Öй涨 "epc_disabled=true".
408Õ⽫»á½ûÓÃotrace¹¦ÄÜ
4095.ÐÞ¸Älistener.oraÎļþ,ÒÔ±ãΪËùÓеÄÊý¾Ý¿âÔÚsid_descÖй涨epc_disabled=true
4106.ÖØÐÂÆô¶¯Êý¾Ý¿âºÍÕìÌý³ÌÐò
4117.´Ó$ORACLE_HOME/binÖÐÔËÐÐotrccrefÃüÁî
412
413\--ÆäËûÓ°ÏìÍøÂçÐÐΪµÄoracleÌØÐÔ
414
415¿ÉÒÔʹÓÃÕâЩ¼¼Êõ¹ÜÀíÍøÂç»î¶¯
416ͨ³£Óм¸¸öÑ¡Ïî
417
418ʹÓÃ×é»ñÈ¡(array fetch)
419ʹÓöàÏ̷߳þÎñÆ÷(MTS)
420ʹÓÃÁ¬½Ó³Ø
421ʹÓÃODBC
422ʹÓÃOracle¸´ÖÆ
423
424\--ʹÓÃÕóÁлñÈ¡À´Ìá¸ßÍøÂçÍÌÍÂÁ¿
425
426\--ʹÓöàÏ̷߳þÎñÆ÷
427
428³ý·Ç·þÎñÆ÷ÉϵÄÁ¬½Óƽ¾ù³¬¹ý300,·ñÔòOracle ²»ÍƼöʹÓà MTS
429
430select * from v$QUEUE
431select * from v$dispatcher
432Õâ2¸öÊÓͼ½«»áÖ¸³öMTS·ÖÅÉÆ÷µÄÊýÁ¿ÊÇ·ñÌ«µÍ
433ËäÈ»·ÖÅÉÆ÷µÄÊýÁ¿ÊÇÔÚinit.oraÎļþÖй涨µÄ,µ«ÊÇÒ²¿ÉÒÔÔÚSQL*DBA
434ÖÐʹÓÃ
435
436ALTER SYSTEM SET MTS_DISPATCHERS='TCPIP,4'
437ÔÚÏ߸ıä
438
439Èç¹ûÄãÓöµ½ÓëMTSÓйصÄÎÊÌâ,¿ÉÒÔͨ¹ýµ÷ÓÃ
440SVRMGRL>ALTER SYSTEM SET MTS_DISPATCHERS=0;
441ÃüÁî¿ìËÙÍ˻ص½×¨Ó÷þÎñÆ÷
442
443\--Á¬½Ó¹²ÏíºÍÍøÂçÐÔÄÜ
444
445ʹÓÃodbcµÄÊý¾Ý¿âÁ¬½ÓÔÚÐí¶àoracleÓ¦ÓÃÖж¼»á²úÉú´óÁ¿¸ºÔØ
446
447
448\--µ÷Õûoracle ¸´ÖÆ
449
450\--´ÓOracle statpack ÖмàÊÓÍøÂçÐÔÄÜ
451
452select * from stats$system_event where event like 'SQL%';
453
454ÕâÊÇÒ»¸öÊä³ö±¨¸æʾÀý,ËüչʾÁËʼþÒÔ¼°¸÷¸öʱ¼äµÄµÈ´ýÊ®¼Ñµ±ÍøÂç´æÔÚÊý¾Ý°ü´«ÊäÁ¿¹ýÔصÄʱºò,Õâ¸ö±¨¸æ·Ç³£ÊÊÓÃÓÚչʾÌض¨Ê±¼ä
455\--rpt_event.sql
456
457select to_char(snap_time,'yyyy-mm-dd HH24') mydate,
458e.event,
459e.total_waits-nvl(b.total_waits,0) waits,
460((e.time_waited_micro-nvl(b.time_waited_micro,0))/100)/nvl((e.total_waits-nvl(b.total_waits,0)),.01) avg_wait_secs
461
462from stats$system_event b ,
463stats$system_event e,
464stats$snapshot sn
465where
466e.snap_id=sn.snap_id
467and
468b.snap_id=e.snap_id-1
469and
470b.event=e.event
471and
472e.event like 'SQL*Net%'
473and
474e.total_waits-b.total_waits >100
475and
476e.time_waited_micro-b.time_waited_micro >100;
477
478
479\--µ÷Õû·Ö²¼Ê½ÍøÂç
480
481ʹÓÃnetstat¼àÊÓÍøÂç»î¶¯
482
483netstat -sp tcp
484
485
486\--ʹÓÃstatspack µ÷Õû´ÅÅÌI/O×Óϵͳ
487
488Ó°Ïì´ÅÅÌioµÄoracleµ÷ÕûÒòËØ
489
490Àí½âʵÀý²ÎÊýÔõÑùÓ°Ïì´ÅÅÌIO.
491
492oracleÉèÖÃÖеÄÈý¸öÁìÓò¿ÉÒÔÖ±½ÓÓ°Ïì´ÅÅÌioÊýÁ¿
493
4941.oracleʵÀý (init.ora)ÉèÖÃÄܹ»Ó°Ïì´ÅÅÌio
4952.oracle¶ÔÏó(±íºÍË÷Òý)ÉèÖÃÒ²»áÓ°Ïì´ÅÅÌIO
4963.oracle sqlÖ´Ðмƻ®»¹»áÖ±½ÓÓ°Ïì´ÅÅÌio
497
498oracle Ê&#181</eof></eof></return>