OraclePoint - <B>D.C.B.A. Tools Collection</B>

OraclePoint - <B>D.C.B.A. Tools Collection</B> http://www.oraclepoint.com/index.php Worldwide froum on oracle and other IT products. OraclePoint http://www.oraclepoint.com/images/ITPUB_log.png http://www.oraclepoint.com Worldwide froum on oracle and other IT products. http://www.oraclepoint.com/topic.php?filename=51 Performance Tuning Utility: DCBA <OraclePoint.com> OraclePoint Fri, 20 Apr 2007 22:18:45 +0000 http://www.oraclepoint.com/topic.php?filename=51 I believe this tool is just the same powerful as STATSPACK, and some time due to Oracle's bug in shared pool management, we offten need to flush the shared pool, because this tool run quickly, you can still get useful information in this case.

1, Free Download

Windows/Linux/Solaris Binary

2, Installation & Syntax
OPMon is a small powerful utility, you must get oracle client or server software installed on your machine. If you want to connect to remote database, config the oracle network client also, finally create an user with ANYSQL/ANYSQL and grant the CREATE SESSION and SELECT_CATALOG_ROLE previledge.

C:\MYDUL\utility>dcba -2 -h
AnySQL.net OPMon for Oracle, Release 3.0.0
(c) Copyright Lou Fangxin (AnySQL.net) 2004-2006, all rights reserved.

Usage: dcba -[option][value]
-{1|2|3|4} 1:OTop, 2:OPMon, 3:OTune, 4:OLoad
-H print help message
-U Oracle user name with select_catalog_role.
-S tns alias if remote database.
-T time interval (default 30 seconds, min 5, max 900)
-C number of top SQLs displayed (min 5, max 16384)
-Q run in quiet mode, write to file
-D directory of output file in quiet mode

3, How to connect to database?
If we use SQL*Plus to connect database as "sqlplus anysql/anysql@prod", then you should run OTop as "dcba -2 -uanysql/anysql -sprod", the default time interval is 120 seconds, so you need to wait for about 2 minutes, then it will give you the following report, of cause you could use "-t" option to tune the interval, for example 30 seconds, just issue "dcba -2 -t30 -uanysql/anysql -sprod".

4, Report Sample
First part is overall information.

AnySQL OPMon (Host:*****, SID:*****, CPU:4, Time:23:01:22 - 23:03:26, Elapse:124)
-Statistics-------------------------------------------------------------------------------------
209 1 logons cumulative 27K 222 opened cursors 833 6 user commits
163K 1321 user calls 31K 253 CPU this session 0 0 enqueue waits
24K 200 db block gets 9435K 76K consistent gets 771K 6222 physical reads
16K 135 db block changes 328 2 consistent change 231 1 physical writes
13K 110 DBWR buffers scan 762K 6148 free buffer req 4650 37 free buffer ins
29 0 CR blocks created 2688K 21K redo size 5908 47 redo blks written
46M 378K Table scan rows 29M 241K Table fetch rowid 27K 223 Total parse count
83 0 Hard parse count 120K 968 execute count 177M 1432K bytes via SQL*Net
9668 77 sorts (memory) 0 0 sorts (disk)

Second part is top 5 wait event.
-Waits----W/S---Time--Tim/S--Pct----Event-------------(Top 5 Wait Event)------------------------
0 0 31K 254 28 CPU time
67K 541 26K 215 23 db file scattered read
80 0 24K 194 21 enqueue
43K 346 16K 136 15 db file sequential read
27K 223 12K 102 11 buffer busy waits

Forth part is TOP sqls.
--Exec--Exe/S--Get/S--Get/E--Dsk/S--Dsk/E--Row/E---Sort--Cpu/S-Wait/S--HASH---(Order By Exec)---
2061 62 1914 30 3 0 1 0 78K 9710 274642557 [EXEC 1]
1502 45 136 3 17 0 0 0 17K 44K 3941635528 [EXEC 2]
1180 35 630 17 0 0 1 0 32K 13K 924586018 [EXEC 3]
1031 31 832 26 1 0 1 0 29K 15K 292557380 [EXEC 4]
728 22 87 3 0 0 0 0 5151 n/a 14619818 [EXEC 5]

--Exec--Exe/S--Get/S--Get/E--Dsk/S--Dsk/E--Row/E---Sort--Cpu/S-Wait/S--HASH---(Order By Gets)---
1 0 6601 217K 0 17 26 1 79K 4313 1116368370 [GETS 1]
2061 62 1914 30 3 0 1 0 78K 9710 274642557 [GETS 2]
112 3 1772 522 0 0 445 0 242K 6872 3312817073 [GETS 3]
1031 31 832 26 1 0 1 0 29K 15K 292557380 [GETS 4]
301 9 758 83 0 0 20 0 15K n/a 1779246519 [GETS 5]
1180 35 630 17 0 0 1 0 32K 13K 924586018 [GETS 6]
509 15 443 28 1 0 1 0 19K 10K 680911523 [GETS 7]
421 12 366 28 0 0 1 0 20K 3619 1904510931 [GETS 8]

--Exec--Exe/S--Get/S--Get/E--Dsk/S--Dsk/E--Row/E---Sort--Cpu/S-Wait/S--HASH---(Order By Disk)---
5 0 101 669 62 415 42K 5 142K 43K 1560822338 [DISK 1]
1 0 57 1906 57 1900 3285 1 59K 42K 4043595143 [DISK 2]
1 0 50 1670 45 1507 4219 0 15K 140K 2674340009 [DISK 3]
2 0 60 990 45 743 2013 0 17K 142K 2260377061 [DISK 4]
2 0 41 678 25 416 42K 2 55K 26K 179113020 [DISK 5]
2 0 40 661 24 410 42K 2 58K 20K 3213418442 [DISK 6]
1502 45 136 3 17 0 0 0 17K 44K 3941635528 [DISK 7]
2061 62 1914 30 3 0 1 0 78K 9710 274642557 [DISK 8]

--Exec--Exe/S--Get/S--Get/E--Dsk/S--Dsk/E--Row/E---Sort--Cpu/S-Wait/S--HASH---(Order By Sort)---
144 4 8 2 0 0 0 144 2121 n/a 1926052843 [SORT 1]
47 1 27 19 0 0 9 124 606 1024 1356713530 [SORT 2]
51 1 4 3 0 0 0 51 909 608 375404960 [SORT 3]
44 1 115 86 0 0 2 44 3636 n/a 4032276548 [SORT 4]
41 1 265 213 0 0 63 41 13K 3031 1574206447 [SORT 5]

What's the mean of each column?
Exec : Total execution count in this period
Exe/S : Execution count per second
Get/S : Consistent gets per second
Get/E : Consistent gets per execution
Dsk/S : Physical reads per second
Dsk/E : Physical reads per execution
Row/E : Rows per executions
Sort : Sort count
Cpu/S : CPU time per second(cs)
Wait/S : Wait time per second (cs)
All the values are the delta between the two time points. You can find out so much useful information here, event data distribution by check the "Row/E" column,I have found one SQL with different rows at different period, and final found that there is lots of rows for one value.

5, Notes
OPMon will not caused heavy load on the database server, it's a light way tools.
]]> http://www.oraclepoint.com/topic.php?filename=124 Curriculum Vitae of D.C.B.A. <OraclePoint.com> OraclePoint Mon, 18 Jun 2007 18:28:20 +0000 http://www.oraclepoint.com/topic.php?filename=124 Fangxin.Lou
Senior DBA, eBay China Operation Center
Shanghai, China

Fangxin Lou currently works as a senior DBA for eBay, the world's largest auction web site, to help improve the site availability by performance tuning and doing maintenance work. AnySQL is active in the Chinese database technology community using the ID of "d.c.b.a" or "AnySQL". He has writen several free DBA utilities such as AnySQL, ociuldr, otop etc. where hundreds of DBAs have downloaded to use in their daily work.

Read Fangxins'Blog: English | Chinese ]]>