From: Mike J. <mrj...@mi...> - 2008-09-20 17:40:36
|
Does anybody actually have a oracle 7 database to confirm these tuning queries work? If not I might have to leave the original sql there and use the new queries or 8i and up. There's got to be a reason it was done like that. :-) On Mon, 2008-09-15 at 08:13 -0500, Neulinger, Nathan wrote: > We need to ditch the custom SQL file entirely in my view... should fix the queries they refer to though - especially if we can make them work equivalently between different oracle versions. > > -- Nathan > > ------------------------------------------------------------ > Nathan Neulinger nn...@ms... > Missouri S&T Information Technology (573) 341-6679 > System Administrator - Principal KD0DMH > > > > -----Original Message----- > > From: SourceForge.net [mailto:no...@so...] > > Sent: Monday, September 15, 2008 8:08 AM > > To: nn...@ne... > > Subject: [tora - Help] Server Tuning on Oracle 10g > > > > > > Read and respond to this message at: > > https://sourceforge.net/forum/message.php?msg_id=5275807 > > By: ghp > > > > The Overview, Charts and Indicator windows of "Server Tuning" obtain > > their data > > by querying v$sysstat using the statistic# column. Now, the statistic > > numbers > > changed a little with Oracle 9i, and more with 10g. As a result, the > > graphs > > and indicators are useless. If you drop the queries below into your > > Custom > > SQL file, you can have a working Server Tuning with Oracle 8, 9 and 10. > > This > > does not work with Tora 1.4, which seems to ignore the Custom SQL file, > > and > > creates an empty one if you try to save any SQL you have edited. So, > > stick > > to 1.3. > > > > > > > > toOracleExtract:ListSource[8.1][Oracle]=SELECT text\n FROM > > sys.dba_source\n > > WHERE type = :typ<char[100]>\n AND name = :nam<char[100]>\n AND > > owner = > > :own<char[100]>\n ORDER BY line > > toTuning:Charts:1BBPhysical I/O[0900][Oracle]=select SYSDATE,\n > > sum(decode(name,'physical reads',value,0)) "Physical reads",\n > > sum(decode(name,'physical writes',value,0)) "Physical writes",\n > > sum(decode(name,'redo writes',value,0)) "Redo writes"\n from v$sysstat > > where > > name in ('physical reads','physical writes','redo writes') > > toTuning:Charts:2BBLogical I/O[0900][Oracle]=select SYSDATE,\n > > sum(decode(name,'db block gets',value,0)) "Block gets",\n > > sum(decode(name,'consistent gets',value,0)) "Consistent gets",\n > > sum(decode(name,'db block changes',value,0)) "Block changes",\n > > sum(decode(name,'consistent changes',value,0)) "Consistent changes"\n > > from > > v$sysstat where name in ('db block gets','consistent gets','db block > > changes','consistent changes') > > toTuning:Charts:5CPCache misses[0900][Oracle]=select sysdate,\n > > pread > > "Data buffer cache",read "Dictionary row cache",\n getmiss > > "Library cache",gets > > " ",\n reloads " ",pins " "\n from (select > > 100*SUM(DECODE(name,'physical > > reads',value,0)) pread,SUM(DECODE(name,'db block > > gets',value,'consistent > > gets',value,41,value,0)) read\n from v$sysstat where name IN > > ('db block > > gets','consistent gets','physical reads')) "Data buffer cache",\n > > (select > > 100*sum(getmisses) getmiss,sum(gets) gets from v$rowcache) "Dictionary > > row cache",\n > > (select 100*sum(reloads) reloads,sum(pins) pins from v$librarycache) > > "Library > > cache"\n > > toTuning:Charts:7BSRedo log I/O[0900][Oracle]=select SYSDATE,\n > > sum(decode(name,'redo size',value,0))/:unit<int> "Redo size",\n > > sum(decode(name,'redo wastage',value,0))/:unit<int> "Redo wastage"\n > > from v$sysstat > > where name in ('redo size','redo wastage') > > toTuning:Indicators:Important ratios:3DataCache[0900][Oracle]=SELECT > > (1-SUM(DECODE(name,'physical reads',value,0))/SUM(DECODE(name,'db block > > gets',value,'consistent gets',value,0)))*100,' %'\n FROM v$sysstat\n > > WHERE > > name in ('db block gets','consistent gets','physical reads') > > toTuning:Indicators:RBS contention:1SystemHeadUndo[0900][Oracle]=SELECT > > count/blocks*100,' %'\n FROM (SELECT MAX(count) count FROM v$waitstat > > WHERE > > class = 'system undo header') a,\n (SELECT SUM(value) blocks FROM > > v$sysstat > > WHERE name in ('db block gets','consistent gets')) b > > toTuning:Indicators:RBS > > contention:2SystemBlockUndo[0900][Oracle]=SELECT > > count/blocks*100,' %'\n FROM (SELECT MAX(count) count FROM v$waitstat > > WHERE > > class = 'system undo block') a,\n (SELECT SUM(value) blocks FROM > > v$sysstat > > WHERE name in ('db block gets','consistent gets')) b > > toTuning:Indicators:RBS contention:3HeadUndo[0900][Oracle]=SELECT > > count/blocks*100,' > > %'\n FROM (SELECT MAX(count) count FROM v$waitstat WHERE class = 'undo > > header') > > a,\n (SELECT SUM(value) blocks FROM v$sysstat WHERE name in ('db > > block > > gets','consistent gets')) b > > toTuning:Indicators:RBS contention:4BlockUndo[0900][Oracle]=SELECT > > count/blocks*100,' > > %'\n FROM (SELECT MAX(count) count FROM v$waitstat WHERE class = 'undo > > block') > > a,\n (SELECT SUM(value) blocks FROM v$sysstat WHERE > > \n name in ('db block gets','consistent gets')) b > > toTuning:Indicators:Redo log contention:1LogSpace[0900][Oracle]=select > > value > > from v$sysstat where name = 'redo log space requests' > > toTuning:Overview:BufferHit[0900][Oracle]=SELECT SYSDATE,(1- > > SUM(DECODE(name,'physical > > reads',value,0))/SUM(DECODE(name,'db block gets',value,'consistent > > gets',value,0)))*100\n FROM v$sysstat\n WHERE name IN ('db block > > gets','consistent > > gets','physical reads') > > toTuning:Overview:ClientInput[0900][Oracle]=select > > sysdate,value/:f1<int>\n > > from v$sysstat where name = 'bytes sent via SQL*Net to client' > > toTuning:Overview:ClientOutput[0900][Oracle]=select > > sysdate,value/:f1<int>\n > > from v$sysstat where name = 'bytes received via SQL*Net from client' > > toTuning:Overview:Execute[0900][Oracle]=select sysdate,value\n from > > v$sysstat > > where name = 'execute count' > > toTuning:Overview:LogicalRead[0900][Oracle]=select sysdate,sum(value)\n > > from > > v$sysstat where name in ('db block gets','consistent gets') > > toTuning:Overview:LogicalWrite[0900][Oracle]=select > > sysdate,sum(value)\n from > > v$sysstat > > \n where name in ('db block changes','consistent changes') > > \n > > toTuning:Overview:Parse[0900][Oracle]=select sysdate,value\n from > > v$sysstat > > where name = 'parse count (total)' > > toTuning:Overview:PhysicalRead[0900][Oracle]=select sysdate,value\n > > from v$sysstat > > where name = 'physical reads' > > toTuning:Overview:PhysicalWrite[0900][Oracle]=select sysdate,value\n > > from v$sysstat > > where name = 'physical writes' > > toTuning:Overview:RedoBlocks[0900][Oracle]=select sysdate,value\n from > > v$sysstat > > where name = 'redo blocks written' > > toTuning:Overview:RedoEntries[0900][Oracle]=select sysdate,value\n > > from v$sysstat > > where name = 'redo entries' > > > > > > ______________________________________________________________________ > > You are receiving this email because you elected to monitor this forum. > > To stop monitoring this forum, login to SourceForge.net and visit: > > https://sourceforge.net/forum/unmonitor.php?forum_id=52737 > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Tora-develop mailing list > Tor...@li... > https://lists.sourceforge.net/lists/listinfo/tora-develop |