[oracle@zhongwc1 Pck]$ ls -l *.sql -rw-r--r-- 1 oracle oinstall 839 Mar 19 2012 1_grants.sql -rw-r--r-- 1 oracle oinstall 1680 Mar 17 2012 2_Types.sql -rw-r--r-- 1 oracle oinstall 82422 May 12 2012 3_pkg.sql -rw-r--r-- 1 oracle oinstall 221 Mar 17 2012 drop.sql -rw-r--r-- 1 oracle oinstall 1808 Mar 17 2012 howtoinstall.sql
[oracle@zhongwc1 Pck]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 30 14:54:18 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @1_grants.sql Enter value for _usr: zwc old 1: grant create type to &&_usr new 1: grant create type to zwc Grant succeeded. old 1: grant create procedure to &_usr new 1: grant create procedure to zwc Grant succeeded. old 1: grant execute on dbms_lock to &_usr new 1: grant execute on dbms_lock to zwc Grant succeeded. old 1: grant select on gv_$sql to &_usr new 1: grant select on gv_$sql to zwc Grant succeeded. old 1: grant select on gv_$sql_monitor to &_usr new 1: grant select on gv_$sql_monitor to zwc Grant succeeded. old 1: grant select on gv_$active_session_history to &_usr new 1: grant select on gv_$active_session_history to zwc Grant succeeded. old 1: grant select on gv_$osstat to &_usr new 1: grant select on gv_$osstat to zwc Grant succeeded. old 1: grant select on gv_$instance to &_usr new 1: grant select on gv_$instance to zwc Grant succeeded. old 1: grant select on gv_$statname to &_usr new 1: grant select on gv_$statname to zwc Grant succeeded. old 1: grant select on gv_$sysstat to &_usr new 1: grant select on gv_$sysstat to zwc Grant succeeded. old 1: grant select on gv_$segment_statistics to &_usr new 1: grant select on gv_$segment_statistics to zwc Grant succeeded. old 1: grant select on gv_$dlm_misc to &_usr new 1: grant select on gv_$dlm_misc to zwc Grant succeeded. old 1: grant select on v_$parameter to &_usr new 1: grant select on v_$parameter to zwc Grant succeeded.
SQL> conn zwc Enter password: Connected. SQL> @2_Types Type created. Type created. Type created. Type created. Type created. Type created. Type created. Type created. Type created. Type created. SQL> @3_pkg Package created. No errors. Package body created.
Script requires linesize 190, pages 0 and arraysize 45+ minimum, so make sure you keep your terminal screen as full screen.
NOTE: arraysize should be same as you pass parameter to sql.. by default arraysize value is 47 and refresh time 6 seconds, should be enough for 8 node cluster
SQL> connect zwc/zwc
SQL>set lines 190 pages 0 arraysize 47
SQL>select * from table(jss.gtop) ;
-- Screen Size and Refresh/Sample time can be passed as parameter
SQL>set lines 190 pages 0 arraysize 50
SQL>select * from table(jss.gtop(50,10)) ;
-- Above example, would use 50 arraysize and 10 second sample.
Note : Screensize and passed parameter to jss.gtop(X) should be same
: There might be some delay top of sample time based on db performance or no. of nodes it has to collect data
I observed 3 second delay on vms running on my laptop. timestamp is being used to calculate per second metrics values
+Inst-------+CPUIDL%--IO%-USR%--SYS%+--Tprse/s--+Hprse/s+--PhyWIO/s-+-PhyWMB/s-+--PhyRIO/s--+-PhyRMB/s-+-SessLIO/s--+---Exec/s-+RedoMB/s+Commit/s+-ExSSMB/s-+-ExSIMB/s+-ExFCRh/s+
|zhongwc1 | 87.9 .9 6.4 4.6| 1| 0| 1| 0| 3| 0| 16| 4| 0| 0| 0| 0| 0|
|zhongwc2 | 89.8 .9 4.6 4.6| 1| 0| 1| 0| 3| 0| 1| 1| 0| 0| 0| 0| 0|
+-----------+-----------------------+-----------+-------+-----------+----------+------------+----------+------------+----------+--------+--------+----------+---------+---------+
TOTAL : 2, 0, 2, 0, 6, 0, 17, 5, 0, 0, 0, 0, 0,
+IMPACT%-+--TOP WAIT EVENTS-----------------------+-WAIT CLASS--------+ +IMPACT%-+ TOP SQLS (child)--+-TOP SESSIONS-------INST:SID----------------------------------------+
+--------+----------------------------------------+-------------------+ +--------+-------------------+--------------------------------------------------------------------+
-------+ ACTIVE SESSIONS GRAPH +----
Active 15 | | 15
| Global | Global | Estd. | Sessions 14 | | 14
| Cache | Cache | Intercnt | | TOP Segments by GC* | 13 | | 13
Inst| Blocks | Blocks | Traffic | | Waits | 12 | | 12
ID| Sent/s | Rcvd/s | MB/s | | IMPACT% [Type:Segment] | 11 | | 11
+---+----------+----------+-----------+ +-------------------------+ 10 | | 10
1 | 0| 0| 0| 9 | | 9
2 | 0| 0| 0| 8 | | 8
7 | | 7
6 | | 6
5 | | 5
4 | | 4
3 | | 3
2 | -MAX CPUs [2]- | 2
1 | | 1
0 +------------------------------------------------------------------------------------+ 0
^ 15:01:58 15:05:58 ^ 15:09:58 ^
+--SqlID--------+--SqlText----------------------------------------------------------------------------+-LongstDur-+-InstCnt-+-Cnt-+--CPU%--+--CONC%--+--CLUS%--+-IO%-+-PhyReadMb+
+---------------+-------------------------------------------------------------------------------------+-----------+---------+-----+--------+---------+---------+-----+----------+