ÿØÿà JFIF    ÿÛ „  ( %!1"%)+...383-7(-.+  -%%--------+------------/----------------------------ÿÀ  ¶" ÿÄ     ÿÄ @    !1AQaq‘¡"±ÁÑð2’BRráS‚ñ3b¢CT²Ò #cÿÄ    ÿÄ '   1!AQ2a"‘±#ÿÚ   ? ò(/p‘—–¢·,M|¯G»R£üešT. ôŸEoCFñ‡ 4ÔkåóxR|µÁ¨ÓDà!Ç´µ˜‡p ÍQ‹§!þž^õWsŒy  åÁ§$á«%ºq˜™JyyË0y«^Ϗ -3ÃÏËÈ£Æ\P’ä„s&•‹Üs üô'àWt¹pñCp$bÚF`·7.^)¾!)ÒÙ˜–ÅÏš^h¾ø³·ìe±ŒaeÖ—Í,e=B}Jéߦ$$½—šÙÜCC¯¶è8β–Åkèwx LÉngT±æ¹£Ócœ¿Í/©Éÿ ‰ÚCÒ­mq;Tt¶–}0"zÖPcd1š­¤tˆ„ÐÓâqú[™öUCŠ2޿د­É(¸ßúçšmW,Ò ºf‹Ån™ÄLêÁ8üÁkÁ€L¥ÍuJ1œôrÂSŒÓöt!7EUíïb)AËi¬â-ÙHk8òÁiXYwÅ)O^"ÇØriÛô{»wRUKÙÏÚlÓ¬€‚’שT ] ªb—ÊîKC^×M“xùäñz¬zJ× {4[¹M'IÆeH1LCWÕ]œûº WDæà°OÅ‚¹c²ÞuA–rL`HšpCez‡V’— Ž‚Ë„–ñ­'µR0ÕÖ=À:±G»C\nÆÉ5*¢ †3šhQ4ýÒi$Õ1"Ü]¢p-q3zcQ$ªö¨¥æf›‘»´ÝÚHá„e²JÇ–iÊ:·à¬Øc5Åk»Laª*wi [0Ówk ¦a¦0Õ²ÅÅ‚T¸’³q:Æ<¹ú Ò˜Æ}2VE•Ð€p¡Ô3Úº …h³^LJºçì¥Ánó|˜ÖˆÎˆ+« ¦dø‘Mº–d\ªDðÛ„Ì×LÝ——Ð9Ç|•FèçVm™¨€™3õS–9Äå¢<̉Ìm¢±`·º˜&Z²ž Ë®D«¶»6™‰©­HÈ Àá«h­i³04æ’hxœi3ï¨(ÓL·†‹¶ËpŒ ƒÜÒ? :õdsÀ –3æÓ·Üjâ¬9—]8n¼&]I F£¼…ì–—Œ0v°LǐÅfÛ2TÖ’aµ˜N@ÖBSÏ^à´4-±°b×nÈÈœÆD=KdŒø…ÄÈK,›ÖR§fjü$^ÔÎ!„6^ s:Üun\ôkCÞò÷<—Lúl Í›G¹ÀPá¼Nc¡šÒ…ÙWͳœ±vÊáЄ×9‹P‰cEØ‘™›µŸP<×g1 º©Ø¬wiÁ^l5× I/';Ë+´Z‡ÓÄFÕp[Èð’ HÄ ó 9¬Dl4“é±Ï”V=^Hð;žLädÉa‚°Ë,#/Ž~²BÔÛ QãñQtIeórVB,ÏÂf‹4#Îœc‹É3ZIÖ Œ¢¥·üxa‡Ãçè„jŠ[5 Ä!U?,3žÎ×…ðdÓ÷háŠA‰ìÄ4÷ÃÜZÃE{‰®+W\ZÍE[‰»µk»MÝ­`¢©†˜ÃV‹-f¢¡†¢a«eŠ%«Y¨©q%jâd,Ôq`) ¤¦…šˆ ¦E>0Ùb²t–Ÿ  ÷jsÏ‚Ò’KÈcø(é›;k(’#ôŒQ ™C> õ³M9óºÆµ¤çâ;uÀ)5>RÔ¸fÓ~Ȧ—’-$fe«"FâUˆÊwµPÏu w+"åÉó¨6s™Û9(À¨ù©hE°½†n`m~“@©›p!Vkhµ Á1šbEqqÊ@ºèl›2 –Yã%‘‚hÛ{a¶‡ÅÞM²¬çL34®Çl]®‹ŽØŒm'RÜe35æöˆ­Á­ cP9šeÁköo´L³Ì=p8]”‡B¶,šº|ɏdzXˆØk*ÇÚ{#ñ‰pã(€¶‡9ý'šÜ³½¯kƒ†Â’ëSOƒ•Å®H6+a¢µˆ‹Y¨b˜b+XˆÖ!°u!¢"µˆˆn2ˆ Å0Ä`Å Ô®c(ÓÜGº•Ä»Œ âWî%q.áÐÄ÷î%umÍ£+ÜQ,Vn¦-GphU¸¢X­&,Gt ·K¢ÅÅ·¥RÄÊÉbKn N­R2g$VµÕó Qr#–ÓZUÏð0ÎtÙÀœz ëöo“Îk¯n„T°"SÞ+³/…€½"ižãO%Í%&Δâ‘ËØ´IˆÓ°Ë‰ÞM3.™yçÁzit›`4C`k¢¼á!álÄï sÂ[×!¤l¾æo"ƒIÔ'@ªu)3›ô®“@Z à¾] üœ'‡4¸PÞkÁ-qÄH‰ÓÍBd´ÿ —¹°ÄA&ÊUC„öz‚‚RæŠtf¬>˜ÑidšEçÑL÷áý¡V±h§­c¿¥¨ƒ@g¼…Ýö^ÞËc.ž„æ“I[ ª+·zÐÒÚ,½Ð»¶€C›7jk$๖€Ž¾ÅÙpp¶ÍþéΤ ½ØÎŒq ñpæžÅ¥#Øâ49ÃÂK^MYV“,¶Éz_òæ:…;Õ«¦I3âJ£¤û5$7Èxün\â]Êe¯(Ÿ&Ž‹¶2<6Ćf× æ¢ƱqWı[(ÙÂŽç‚T7¨@ÈÉÍß‚ïÙ"&3TYlGŽ†)†#5ŠA©^T2ÆÁ†)©†©†¤yGXÁ†©"†)©¼ÅV WS†#©]R–r‹q5ÕbâWþBh¯u+ªÅÄ×ü„nÑ^êkªÁj‰jeœWˆÕÕ`µDµ2Ì+Ä µDµµ5Ô{¤Þ0RF’H÷AÛ<ÊÁ§ ƈæ0Ñ£ê4 öÚµ[¬/1²€â<-¬šö²r×0o×w ´xÐÒË„ g01¬„åEM蛇Ã\6µE›‘fÙO k÷CºnÓ1`iîÒ² áÂ!ÑLÄÿ ³¬\õ*:M=Ç»c‹2[‰ØsÕLò\ŒK L^™üÄN\¶#» Ç\……ßïIœÉ$¸ÎñÍ®ÔHÇ Ûí/´>Li”„†g^$ 7NÍÑÑ2M2ªNg=k½Ð}Ÿl6‡ —JWˆ”ÞéT·+µ¦üÂFè¢Vs6ýÆ4»2+2IÝ9ô9®ÇGÙÇ1+Ä8;*Õ§ž½D*îÑWÚçá3Ma¤·ˆ¥t}š²Ü…Q+Æüµ8HíS”üxGéÁˆ¦Ël„éÈ 5 µÄƒ6äÜ(kIš¯S†fÖ&¸®×v|Äyˆ*禌Éù@7-®Ä[]ÎÖ¿ê`º…=:…žJVnݳvðÖKE¼‚ »mà ÷f®:Ë ™–4rð¡4` ܹçž~‹C=œ…¾Ïj|VDtK.ƒ"9 Ï ­–éXá³6WÒïIL-Ї Ç perf-mssqlnative.inc.php000064400000012012152267421130011324 0ustar00 array('RATIO', "select round((a.cntr_value*100.0)/b.cntr_value,2) from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b where a.counter_name = 'Buffer cache hit ratio' and b.counter_name='Buffer cache hit ratio base'", '=WarnCacheRatio'), 'prepared sql hit ratio' => array('RATIO', array('dbcc cachestats','Prepared',1,100), ''), 'adhoc sql hit ratio' => array('RATIO', array('dbcc cachestats','Adhoc',1,100), ''), 'IO', 'data reads' => array('IO', "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"), 'data writes' => array('IO', "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"), 'Data Cache', 'data cache size' => array('DATAC', "select cntr_value*8192 from master.dbo.sysperfinfo where counter_name = 'Total Pages' and object_name='SQLServer:Buffer Manager'", '' ), 'data cache blocksize' => array('DATAC', "select 8192",'page size'), 'Connections', 'current connections' => array('SESS', '=sp_who', ''), 'max connections' => array('SESS', "SELECT @@MAX_CONNECTIONS", ''), false ); function __construct(&$conn) { if ($conn->dataProvider == 'odbc') { $this->sql1 = 'sql1'; //$this->explain = false; } $this->conn =& $conn; } function Explain($sql,$partial=false) { $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $s = '

Explain: '.htmlspecialchars($sql).'

'; $this->conn->Execute("SET SHOWPLAN_ALL ON;"); $sql = str_replace('?',"''",$sql); global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $rs =& $this->conn->Execute($sql); //adodb_printr($rs); $ADODB_FETCH_MODE = $save; if ($rs) { $rs->MoveNext(); $s .= ''; while (!$rs->EOF) { $s .= '\n"; ## NOTE CORRUPT tag is intentional!!!! $rs->MoveNext(); } $s .= '
Rows IO CPU     Plan
'.round($rs->fields[8],1).''.round($rs->fields[9],3).''.round($rs->fields[10],3).'
'.htmlspecialchars($rs->fields[0])."
'; $rs->NextRecordSet(); } $this->conn->Execute("SET SHOWPLAN_ALL OFF;"); $this->conn->LogSQL($save); $s .= $this->Tracer($sql); return $s; } function Tables($orderby='1') { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; //$this->conn->debug=1; $s = ''; $rs1 = $this->conn->Execute("select distinct name from sysobjects where xtype='U'"); if ($rs1) { while (!$rs1->EOF) { $tab = $rs1->fields[0]; $tabq = $this->conn->qstr($tab); $rs2 = $this->conn->Execute("sp_spaceused $tabq"); if ($rs2) { $s .= ''; $rs2->Close(); } $rs1->MoveNext(); } $rs1->Close(); } $ADODB_FETCH_MODE = $save; return $s.'
tablenamesize_in_kindex sizereserved size
'.$tab.''.$rs2->fields[3].''.$rs2->fields[4].''.$rs2->fields[2].'
'; } function sp_who() { $arr = $this->conn->GetArray('sp_who'); return sizeof($arr); } function HealthCheck($cli=false) { $this->conn->Execute('dbcc traceon(3604)'); $html = adodb_perf::HealthCheck($cli); $this->conn->Execute('dbcc traceoff(3604)'); return $html; } } perf-db2.inc.php000064400000006631152267421130007437 0ustar00 array('RATIO', "SELECT case when sum(POOL_DATA_L_READS+POOL_INDEX_L_READS)=0 then 0 else 100*(1-sum(POOL_DATA_P_READS+POOL_INDEX_P_READS)/sum(POOL_DATA_L_READS+POOL_INDEX_L_READS)) end FROM TABLE(SNAPSHOT_APPL('',-2)) as t", '=WarnCacheRatio'), 'Data Cache', 'data cache buffers' => array('DATAC', 'select sum(npages) from SYSCAT.BUFFERPOOLS', 'See tuning reference.' ), 'cache blocksize' => array('DATAC', 'select avg(pagesize) from SYSCAT.BUFFERPOOLS', '' ), 'data cache size' => array('DATAC', 'select sum(npages*pagesize) from SYSCAT.BUFFERPOOLS', '' ), 'Connections', 'current connections' => array('SESS', "SELECT count(*) FROM TABLE(SNAPSHOT_APPL_INFO('',-2)) as t", ''), false ); function __construct(&$conn) { $this->conn = $conn; } function Explain($sql,$partial=false) { $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $qno = rand(); $ok = $this->conn->Execute("EXPLAIN PLAN SET QUERYNO=$qno FOR $sql"); ob_start(); if (!$ok) echo "

Have EXPLAIN tables been created?

"; else { $rs = $this->conn->Execute("select * from explain_statement where queryno=$qno"); if ($rs) rs2html($rs); } $s = ob_get_contents(); ob_end_clean(); $this->conn->LogSQL($save); $s .= $this->Tracer($sql); return $s; } /** * Gets a list of tables * * @param int $throwaway discarded variable to match the parent method * @return string The formatted table list */ function Tables($throwaway=0) { $rs = $this->conn->Execute("select tabschema,tabname,card as rows, npages pages_used,fpages pages_allocated, tbspace tablespace from syscat.tables where tabschema not in ('SYSCAT','SYSIBM','SYSSTAT') order by 1,2"); return rs2html($rs,false,false,false,false); } } perf-mysql.inc.php000064400000021144152267421130010131 0ustar00 array('RATIO', '=GetKeyHitRatio', '=WarnCacheRatio'), 'InnoDB cache hit ratio' => array('RATIO', '=GetInnoDBHitRatio', '=WarnCacheRatio'), 'data cache hit ratio' => array('HIDE', # only if called '=FindDBHitRatio', '=WarnCacheRatio'), 'sql cache hit ratio' => array('RATIO', '=GetQHitRatio', ''), 'IO', 'data reads' => array('IO', '=GetReads', 'Number of selects (Key_reads is not accurate)'), 'data writes' => array('IO', '=GetWrites', 'Number of inserts/updates/deletes * coef (Key_writes is not accurate)'), 'Data Cache', 'MyISAM data cache size' => array('DATAC', array("show variables", 'key_buffer_size'), '' ), 'BDB data cache size' => array('DATAC', array("show variables", 'bdb_cache_size'), '' ), 'InnoDB data cache size' => array('DATAC', array("show variables", 'innodb_buffer_pool_size'), '' ), 'Memory Usage', 'read buffer size' => array('CACHE', array("show variables", 'read_buffer_size'), '(per session)'), 'sort buffer size' => array('CACHE', array("show variables", 'sort_buffer_size'), 'Size of sort buffer (per session)' ), 'table cache' => array('CACHE', array("show variables", 'table_cache'), 'Number of tables to keep open'), 'Connections', 'current connections' => array('SESS', array('show status','Threads_connected'), ''), 'max connections' => array( 'SESS', array("show variables",'max_connections'), ''), false ); function __construct(&$conn) { $this->conn = $conn; } function Explain($sql,$partial=false) { if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '

Unable to EXPLAIN non-select statement

'; $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $sql = str_replace('?',"''",$sql); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $sql = $this->conn->GetOne("select sql1 from adodb_logsql where sql1 like $sqlq"); } $s = '

Explain: '.htmlspecialchars($sql).'

'; $rs = $this->conn->Execute('EXPLAIN '.$sql); $s .= rs2html($rs,false,false,false,false); $this->conn->LogSQL($save); $s .= $this->Tracer($sql); return $s; } /** * Returns a list of table statuses. * * @param string $orderby Unused (compatibility with parent method) * @return string A formatted set of recordsets */ function tables($orderby='1') { if (!$this->tablesSQL) return false; $rs = $this->conn->Execute($this->tablesSQL); if (!$rs) return false; $html = rs2html($rs,false,false,false,false); return $html; } function GetReads() { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $rs = $this->conn->Execute('show status'); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if (!$rs) return 0; $val = 0; while (!$rs->EOF) { switch($rs->fields[0]) { case 'Com_select': $val = $rs->fields[1]; $rs->Close(); return $val; } $rs->MoveNext(); } $rs->Close(); return $val; } function GetWrites() { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $rs = $this->conn->Execute('show status'); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if (!$rs) return 0; $val = 0.0; while (!$rs->EOF) { switch($rs->fields[0]) { case 'Com_insert': $val += $rs->fields[1]; break; case 'Com_delete': $val += $rs->fields[1]; break; case 'Com_update': $val += $rs->fields[1]/2; $rs->Close(); return $val; } $rs->MoveNext(); } $rs->Close(); return $val; } function FindDBHitRatio() { // first find out type of table //$this->conn->debug=1; global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $rs = $this->conn->Execute('show table status'); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if (!$rs) return ''; $type = strtoupper($rs->fields[1]); $rs->Close(); switch($type){ case 'MYISAM': case 'ISAM': return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)'; case 'INNODB': return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)'; default: return $type.' not supported'; } } function GetQHitRatio() { //Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached $hits = $this->_DBParameter(array("show status","Qcache_hits")); $total = $this->_DBParameter(array("show status","Qcache_inserts")); $total += $this->_DBParameter(array("show status","Qcache_not_cached")); $total += $hits; if ($total) return round(($hits*100)/$total,2); return 0; } /* Use session variable to store Hit percentage, because MySQL does not remember last value of SHOW INNODB STATUS hit ratio # 1st query to SHOW INNODB STATUS 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 # 2nd query to SHOW INNODB STATUS 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool activity since the last printout */ function GetInnoDBHitRatio() { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $rs = $this->conn->Execute('show engine innodb status'); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_FETCH_MODE = $save; if (!$rs || $rs->EOF) return 0; $stat = $rs->fields[0]; $rs->Close(); $at = strpos($stat,'Buffer pool hit rate'); $stat = substr($stat,$at,200); if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) { $val = 100*$arr[1]/$arr[2]; $_SESSION['INNODB_HIT_PCT'] = $val; return round($val,2); } else { if (isset($_SESSION['INNODB_HIT_PCT'])) return $_SESSION['INNODB_HIT_PCT']; return 0; } return 0; } function GetKeyHitRatio() { $hits = $this->_DBParameter(array("show status","Key_read_requests")); $reqs = $this->_DBParameter(array("show status","Key_reads")); if ($reqs == 0) return 0; return round(($hits/($reqs+$hits))*100,2); } // start hack var $optimizeTableLow = 'CHECK TABLE %s FAST QUICK'; var $optimizeTableHigh = 'OPTIMIZE TABLE %s'; /** * @see adodb_perf::optimizeTable() */ function optimizeTable( $table, $mode = ADODB_OPT_LOW) { if ( !is_string( $table)) return false; $conn = $this->conn; if ( !$conn) return false; $sql = ''; switch( $mode) { case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break; case ADODB_OPT_HIGH : $sql = $this->optimizeTableHigh; break; default : // May don't use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0) ADOConnection::outp( sprintf( "

%s: '%s' using of undefined mode '%s'

", __CLASS__, __FUNCTION__, $mode)); return false; } $sql = sprintf( $sql, $table); return $conn->Execute( $sql) !== false; } // end hack } perf-informix.inc.php000064400000004545152267421130010625 0ustar00 array('RATIOH', "select round((1-(wt.value / (rd.value + wr.value)))*100,2) from sysmaster:sysprofile wr, sysmaster:sysprofile rd, sysmaster:sysprofile wt where rd.name = 'pagreads' and wr.name = 'pagwrites' and wt.name = 'buffwts'", '=WarnCacheRatio'), 'IO', 'data reads' => array('IO', "select value from sysmaster:sysprofile where name='pagreads'", 'Page reads'), 'data writes' => array('IO', "select value from sysmaster:sysprofile where name='pagwrites'", 'Page writes'), 'Connections', 'current connections' => array('SESS', 'select count(*) from sysmaster:syssessions', 'Number of sessions'), false ); function __construct(&$conn) { $this->conn = $conn; } } perf-oci8.inc.php000064400000060010152267421130007621 0ustar00 array('RATIOH', "select round((1-(phy.value / (cur.value + con.value)))*100,2) from v\$sysstat cur, v\$sysstat con, v\$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'", '=WarnCacheRatio'), 'sql cache hit ratio' => array( 'RATIOH', 'select round(100*(sum(pins)-sum(reloads))/sum(pins),2) from v$librarycache', 'increase shared_pool_size if too ratio low'), 'datadict cache hit ratio' => array('RATIOH', "select round((1 - (sum(getmisses) / (sum(gets) + sum(getmisses))))*100,2) from v\$rowcache", 'increase shared_pool_size if too ratio low'), 'memory sort ratio' => array('RATIOH', "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 0,1,(a.VALUE + b.VALUE)),2) FROM v\$sysstat a, v\$sysstat b WHERE a.name = 'sorts (disk)' AND b.name = 'sorts (memory)'", "% of memory sorts compared to disk sorts - should be over 95%"), 'IO', 'data reads' => array('IO', "select value from v\$sysstat where name='physical reads'"), 'data writes' => array('IO', "select value from v\$sysstat where name='physical writes'"), 'Data Cache', 'data cache buffers' => array( 'DATAC', "select a.value/b.value from v\$parameter a, v\$parameter b where a.name = 'db_cache_size' and b.name= 'db_block_size'", 'Number of cache buffers. Tune db_cache_size if the data cache hit ratio is too low.'), 'data cache blocksize' => array('DATAC', "select value from v\$parameter where name='db_block_size'", '' ), 'Memory Pools', 'Mem Max Target (11g+)' => array( 'DATAC', "select value from v\$parameter where name = 'memory_max_target'", 'The memory_max_size is the maximum value to which memory_target can be set.' ), 'Memory target (11g+)' => array( 'DATAC', "select value from v\$parameter where name = 'memory_target'", 'If memory_target is defined then SGA and PGA targets are consolidated into one memory_target.' ), 'SGA Max Size' => array( 'DATAC', "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_max_size'", 'The sga_max_size is the maximum value to which sga_target can be set.' ), 'SGA target' => array( 'DATAC', "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_target'", 'If sga_target is defined then data cache, shared, java and large pool size can be 0. This is because all these pools are consolidated into one sga_target.' ), 'PGA aggr target' => array( 'DATAC', "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'pga_aggregate_target'", 'If pga_aggregate_target is defined then this is the maximum memory that can be allocated for cursor operations such as sorts, group by, joins, merges. When in doubt, set it to 20% of sga_target.' ), 'data cache size' => array('DATAC', "select value from v\$parameter where name = 'db_cache_size'", 'db_cache_size' ), 'shared pool size' => array('DATAC', "select value from v\$parameter where name = 'shared_pool_size'", 'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ), 'java pool size' => array('DATAJ', "select value from v\$parameter where name = 'java_pool_size'", 'java_pool_size' ), 'large pool buffer size' => array('CACHE', "select value from v\$parameter where name='large_pool_size'", 'this pool is for large mem allocations (not because it is larger than shared pool), for MTS sessions, parallel queries, io buffers (large_pool_size) ' ), 'dynamic memory usage' => array('CACHE', "select '-' from dual", '=DynMemoryUsage'), 'Connections', 'current connections' => array('SESS', 'select count(*) from sys.v_$session where username is not null', ''), 'max connections' => array( 'SESS', "select value from v\$parameter where name='sessions'", ''), 'Memory Utilization', 'data cache utilization ratio' => array('RATIOU', "select round((1-bytes/sgasize)*100, 2) from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f where name = 'free memory' and pool = 'shared pool'", 'Percentage of data cache actually in use - should be over 85%'), 'shared pool utilization ratio' => array('RATIOU', 'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2) from v$sgastat sga, v$parameter p where sga.name = \'free memory\' and sga.pool = \'shared pool\' and p.name = \'shared_pool_size\'', 'Percentage of shared pool actually used - too low is bad, too high is worse'), 'large pool utilization ratio' => array('RATIOU', "select round((1-bytes/sgasize)*100, 2) from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f where name = 'free memory' and pool = 'large pool'", 'Percentage of large_pool actually in use - too low is bad, too high is worse'), 'sort buffer size' => array('CACHE', "select value from v\$parameter where name='sort_area_size'", 'max in-mem sort_area_size (per query), uses memory in pga' ), /*'pga usage at peak' => array('RATIOU', '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),*/ 'Transactions', 'rollback segments' => array('ROLLBACK', "select count(*) from sys.v_\$rollstat", ''), 'peak transactions' => array('ROLLBACK', "select max_utilization tx_hwm from sys.v_\$resource_limit where resource_name = 'transactions'", 'Taken from high-water-mark'), 'max transactions' => array('ROLLBACK', "select value from v\$parameter where name = 'transactions'", 'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'), 'Parameters', 'cursor sharing' => array('CURSOR', "select value from v\$parameter where name = 'cursor_sharing'", 'Cursor reuse strategy. Recommended is FORCE (8i+) or SIMILAR (9i+). See cursor_sharing.'), /* 'cursor reuse' => array('CURSOR', "select count(*) from (select sql_text_wo_constants, count(*) from t1 group by sql_text_wo_constants having count(*) > 100)",'These are sql statements that should be using bind variables'),*/ 'index cache cost' => array('COST', "select value from v\$parameter where name = 'optimizer_index_caching'", '=WarnIndexCost'), 'random page cost' => array('COST', "select value from v\$parameter where name = 'optimizer_index_cost_adj'", '=WarnPageCost'), 'Waits', 'Recent wait events' => array('WAITS','select \'Top 5 events\' from dual','=TopRecentWaits'), // 'Historical wait SQL' => array('WAITS','select \'Last 2 days\' from dual','=TopHistoricalWaits'), -- requires AWR license 'Backup', 'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', '=LogMode'), 'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'), 'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value) FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''), 'Flashback Area' => array('BACKUP', "select nvl(value,'Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')", 'Flashback area is a folder where all backup data and logs can be stored and managed by Oracle. If Error: message displayed, then it is not in use.'), 'Flashback Usage' => array('BACKUP', "select nvl('-','Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')", '=FlashUsage', 'Flashback area usage.'), 'Control File Keep Time' => array('BACKUP', "select value from v\$parameter where name='control_file_record_keep_time'",'No of days to keep RMAN info in control file. Recommended set to x2 or x3 times the frequency of your full backup.'), 'Recent RMAN Jobs' => array('BACKUP', "select '-' from dual", "=RMAN"), // 'Control File Keep Time' => array('BACKUP', "select value from v\$parameter where name='control_file_record_keep_time'",'No of days to keep RMAN info in control file. I recommend it be set to x2 or x3 times the frequency of your full backup.'), 'Storage', 'Tablespaces' => array('TABLESPACE', "select '-' from dual", "=TableSpace"), false ); function __construct(&$conn) { global $gSQLBlockRows; $gSQLBlockRows = 1000; $savelog = $conn->LogSQL(false); $this->version = $conn->ServerInfo(); $conn->LogSQL($savelog); $this->conn = $conn; } function LogMode() { $mode = $this->conn->GetOne("select log_mode from v\$database"); if ($mode == 'ARCHIVELOG') return 'To turn off archivelog:

        SQLPLUS> connect sys as sysdba;
        SQLPLUS> shutdown immediate;

        SQLPLUS> startup mount exclusive;
        SQLPLUS> alter database noarchivelog;
        SQLPLUS> alter database open;
'; return 'To turn on archivelog:

        SQLPLUS> connect sys as sysdba;
        SQLPLUS> shutdown immediate;

        SQLPLUS> startup mount exclusive;
        SQLPLUS> alter database archivelog;
        SQLPLUS> archive log start;
        SQLPLUS> alter database open;
'; } function TopRecentWaits() { $rs = $this->conn->Execute("select * from ( select event, round(100*time_waited/(select sum(time_waited) from v\$system_event where wait_class <> 'Idle'),1) \"% Wait\", total_waits,time_waited, average_wait,wait_class from v\$system_event where wait_class <> 'Idle' order by 2 desc ) where rownum <=5"); $ret = rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function TopHistoricalWaits() { $days = 2; $rs = $this->conn->Execute("select * from ( SELECT b.wait_class,B.NAME, round(sum(wait_time+TIME_WAITED)/1000000) waitsecs, parsing_schema_name, C.SQL_TEXT, a.sql_id FROM V\$ACTIVE_SESSION_HISTORY A join V\$EVENT_NAME B on A.EVENT# = B.EVENT# join V\$SQLAREA C on A.SQL_ID = C.SQL_ID WHERE A.SAMPLE_TIME BETWEEN sysdate-$days and sysdate and parsing_schema_name not in ('SYS','SYSMAN','DBSNMP','SYSTEM') GROUP BY b.wait_class,parsing_schema_name,C.SQL_TEXT, B.NAME,A.sql_id order by 3 desc) where rownum <=10"); $ret = rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function TableSpace() { $rs = $this->conn->Execute( "select tablespace_name,round(sum(bytes)/1024/1024) as Used_MB,round(sum(maxbytes)/1024/1024) as Max_MB, round(sum(bytes)/sum(maxbytes),4) * 100 as PCT from dba_data_files group by tablespace_name order by 2 desc"); $ret = "

Tablespace".rs2html($rs,false,false,false,false); $rs = $this->conn->Execute("select * from dba_data_files order by tablespace_name, 1"); $ret .= "

Datafile".rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function RMAN() { $rs = $this->conn->Execute("select * from (select start_time, end_time, operation, status, mbytes_processed, output_device_type from V\$RMAN_STATUS order by start_time desc) where rownum <=10"); $ret = rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function DynMemoryUsage() { if (@$this->version['version'] >= 11) { $rs = $this->conn->Execute("select component, current_size/1024./1024 as \"CurrSize (M)\" from V\$MEMORY_DYNAMIC_COMPONENTS"); } else $rs = $this->conn->Execute("select name, round(bytes/1024./1024,2) as \"CurrSize (M)\" from V\$sgainfo"); $ret = rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function FlashUsage() { $rs = $this->conn->Execute("select * from V\$FLASH_RECOVERY_AREA_USAGE"); $ret = rs2html($rs,false,false,false,false); return " 

".$ret." 

"; } function WarnPageCost($val) { if ($val == 100 && $this->version['version'] < 10) $s = 'Too High. '; else $s = ''; return $s.'Recommended is 20-50 for TP, and 50 for data warehouses. Default is 100. See optimizer_index_cost_adj. '; } function WarnIndexCost($val) { if ($val == 0 && $this->version['version'] < 10) $s = 'Too Low. '; else $s = ''; return $s.'Percentage of indexed data blocks expected in the cache. Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0. See optimizer_index_caching.'; } function PGA() { //if ($this->version['version'] < 9) return 'Oracle 9i or later required'; } function PGA_Advice() { $t = "

PGA Advice Estimate

"; if ($this->version['version'] < 9) return $t.'Oracle 9i or later required'; $rs = $this->conn->Execute('select a.MB, case when a.targ = 1 then \'<<= Current \' when a.targ < 1 or a.pct <= b.pct then null else \'- BETTER than Current by \'||round(a.pct/b.pct*100-100,2)||\'%\' end as "Percent Improved", a.targ as "PGA Size Factor",a.pct "% Perf" from (select round(pga_target_for_estimate/1024.0/1024.0,0) MB, pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r from v$pga_target_advice) a left join (select round(pga_target_for_estimate/1024.0/1024.0,0) MB, pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r from v$pga_target_advice) b on a.r = b.r+1 where b.pct < 100'); if (!$rs) return $t."Only in 9i or later"; // $rs->Close(); if ($rs->EOF) return $t."PGA could be too big"; return $t.rs2html($rs,false,false,true,false); } function Explain($sql,$partial=false) { $savelog = $this->conn->LogSQL(false); $rs = $this->conn->SelectLimit("select ID FROM PLAN_TABLE"); if (!$rs) { echo "

Missing PLAN_TABLE

CREATE TABLE PLAN_TABLE (
  STATEMENT_ID                    VARCHAR2(30),
  TIMESTAMP                       DATE,
  REMARKS                         VARCHAR2(80),
  OPERATION                       VARCHAR2(30),
  OPTIONS                         VARCHAR2(30),
  OBJECT_NODE                     VARCHAR2(128),
  OBJECT_OWNER                    VARCHAR2(30),
  OBJECT_NAME                     VARCHAR2(30),
  OBJECT_INSTANCE                 NUMBER(38),
  OBJECT_TYPE                     VARCHAR2(30),
  OPTIMIZER                       VARCHAR2(255),
  SEARCH_COLUMNS                  NUMBER,
  ID                              NUMBER(38),
  PARENT_ID                       NUMBER(38),
  POSITION                        NUMBER(38),
  COST                            NUMBER(38),
  CARDINALITY                     NUMBER(38),
  BYTES                           NUMBER(38),
  OTHER_TAG                       VARCHAR2(255),
  PARTITION_START                 VARCHAR2(255),
  PARTITION_STOP                  VARCHAR2(255),
  PARTITION_ID                    NUMBER(38),
  OTHER                           LONG,
  DISTRIBUTION                    VARCHAR2(30)
);
"; return false; } $rs->Close(); // $this->conn->debug=1; if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $s = "

Explain: ".htmlspecialchars($sql)."

"; $this->conn->BeginTrans(); $id = "ADODB ".microtime(); $rs = $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql"); $m = $this->conn->ErrorMsg(); if ($m) { $this->conn->RollbackTrans(); $this->conn->LogSQL($savelog); $s .= "

$m

"; return $s; } $rs = $this->conn->Execute(" select '
'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'
' as Operation, object_name,COST,CARDINALITY,bytes FROM plan_table START WITH id = 0 and STATEMENT_ID='$id' CONNECT BY prior id=parent_id and statement_id='$id'"); $s .= rs2html($rs,false,false,false,false); $this->conn->RollbackTrans(); $this->conn->LogSQL($savelog); $s .= $this->Tracer($sql,$partial); return $s; } function CheckMemory() { if ($this->version['version'] < 9) return 'Oracle 9i or later required'; $rs = $this->conn->Execute(" select a.name Buffer_Pool, b.size_for_estimate as cache_mb_estimate, case when b.size_factor=1 then '<<= Current' when a.estd_physical_read_factor-b.estd_physical_read_factor > 0.001 and b.estd_physical_read_factor<1 then '- BETTER than current by ' || round((1-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) || '%' else ' ' end as RATING, b.estd_physical_read_factor \"Phys. Reads Factor\", round((a.estd_physical_read_factor-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) as \"% Improve\" from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) a , (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) b where a.r = b.r-1 and a.name = b.name "); if (!$rs) return false; /* The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size */ $s = "

Data Cache Advice Estimate

"; if ($rs->EOF) { $s .= "

Cache that is 50% of current size is still too big

"; } else { $s .= "Ideal size of Data Cache is when %BETTER gets close to zero."; $s .= rs2html($rs,false,false,false,false); } return $s.$this->PGA_Advice(); } /* Generate html for suspicious/expensive sql */ function tohtml(&$rs,$type) { $o1 = $rs->FetchField(0); $o2 = $rs->FetchField(1); $o3 = $rs->FetchField(2); if ($rs->EOF) return '

None found

'; $check = ''; $sql = ''; $s = "\n\n'; while (!$rs->EOF) { if ($check != $rs->fields[0].'::'.$rs->fields[1]) { if ($check) { $carr = explode('::',$check); $prefix = "'; $suffix = ''; if (strlen($prefix)>2000) { $prefix = ''; $suffix = ''; } $s .= "\n'; } $sql = $rs->fields[2]; $check = $rs->fields[0].'::'.$rs->fields[1]; } else $sql .= $rs->fields[2]; if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1); $rs->MoveNext(); } $rs->Close(); $carr = explode('::',$check); $prefix = "'; $suffix = ''; if (strlen($prefix)>2000) { $prefix = ''; $suffix = ''; } $s .= "\n'; return $s."
".$o1->name.''.$o2->name.''.$o3->name.'
".$carr[0].''.$carr[1].''.$prefix.$sql.$suffix.'
".$carr[0].''.$carr[1].''.$prefix.$sql.$suffix.'
\n\n"; } // code thanks to Ixora. // http://www.ixora.com.au/scripts/query_opt.htm // requires oracle 8.1.7 or later function SuspiciousSQL($numsql=10) { $sql = " select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_text from ( select address, buffer_gets, executions, pct, rank() over (order by buffer_gets desc) ranking from ( select address, buffer_gets, executions, 100 * ratio_to_report(buffer_gets) over () pct from sys.v_\$sql where command_type != 47 and module != 'T.O.A.D.' ) where buffer_gets > 50 * executions ) s, sys.v_\$sqltext p where s.ranking <= $numsql and p.address = s.address order by 1 desc, s.address, p.piece"; global $ADODB_CACHE_MODE; if (isset($_GET['expsixora']) && isset($_GET['sql'])) { $partial = empty($_GET['part']); echo "".$this->Explain($_GET['sql'],$partial)."\n"; } if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql); $s = ''; $timer = time(); $s .= $this->_SuspiciousSQL($numsql); $timer = time() - $timer; if ($timer > $this->noShowIxora) return $s; $s .= '

'; $save = $ADODB_CACHE_MODE; $ADODB_CACHE_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $savelog = $this->conn->LogSQL(false); $rs = $this->conn->SelectLimit($sql); $this->conn->LogSQL($savelog); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_CACHE_MODE = $save; if ($rs) { $s .= "\n

Ixora Suspicious SQL

"; $s .= $this->tohtml($rs,'expsixora'); } return $s; } // code thanks to Ixora. // http://www.ixora.com.au/scripts/query_opt.htm // requires oracle 8.1.7 or later function ExpensiveSQL($numsql = 10) { $sql = " select substr(to_char(s.pct, '99.00'), 2) || '%' load, s.executions executes, p.sql_text from ( select address, disk_reads, executions, pct, rank() over (order by disk_reads desc) ranking from ( select address, disk_reads, executions, 100 * ratio_to_report(disk_reads) over () pct from sys.v_\$sql where command_type != 47 and module != 'T.O.A.D.' ) where disk_reads > 50 * executions ) s, sys.v_\$sqltext p where s.ranking <= $numsql and p.address = s.address order by 1 desc, s.address, p.piece "; global $ADODB_CACHE_MODE; if (isset($_GET['expeixora']) && isset($_GET['sql'])) { $partial = empty($_GET['part']); echo "".$this->Explain($_GET['sql'],$partial)."\n"; } if (isset($_GET['sql'])) { $var = $this->_ExpensiveSQL($numsql); return $var; } $s = ''; $timer = time(); $s .= $this->_ExpensiveSQL($numsql); $timer = time() - $timer; if ($timer > $this->noShowIxora) return $s; $s .= '

'; $save = $ADODB_CACHE_MODE; $ADODB_CACHE_MODE = ADODB_FETCH_NUM; if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); $savelog = $this->conn->LogSQL(false); $rs = $this->conn->Execute($sql); $this->conn->LogSQL($savelog); if (isset($savem)) $this->conn->SetFetchMode($savem); $ADODB_CACHE_MODE = $save; if ($rs) { $s .= "\n

Ixora Expensive SQL

"; $s .= $this->tohtml($rs,'expeixora'); } return $s; } function clearsql() { $perf_table = adodb_perf::table(); // using the naive "delete from $perf_table where created<".$this->conn->sysTimeStamp will cause the table to lock, possibly // for a long time $sql = "DECLARE cnt pls_integer; BEGIN cnt := 0; FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE createdconn->Execute($sql); } } perf-sqlite3.inc.php000064400000001321152267421130010343 0ustar00conn = $conn; } function tables($orderby='1') { if (!$this->tablesSQL){ return false; } $rs = $this->conn->execute($this->tablesSQL); if (!$rs) { return false; } $html = rs2html($rs, false, false, false, false); return $html; } } perf-mssql.inc.php000064400000012003152267421130010115 0ustar00 array('RATIO', "select round((a.cntr_value*100.0)/b.cntr_value,2) from master.dbo.sysperfinfo a, master.dbo.sysperfinfo b where a.counter_name = 'Buffer cache hit ratio' and b.counter_name='Buffer cache hit ratio base'", '=WarnCacheRatio'), 'prepared sql hit ratio' => array('RATIO', array('dbcc cachestats','Prepared',1,100), ''), 'adhoc sql hit ratio' => array('RATIO', array('dbcc cachestats','Adhoc',1,100), ''), 'IO', 'data reads' => array('IO', "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page reads/sec'"), 'data writes' => array('IO', "select cntr_value from master.dbo.sysperfinfo where counter_name = 'Page writes/sec'"), 'Data Cache', 'data cache size' => array('DATAC', "select cntr_value*8192 from master.dbo.sysperfinfo where counter_name = 'Total Pages' and object_name='SQLServer:Buffer Manager'", '' ), 'data cache blocksize' => array('DATAC', "select 8192",'page size'), 'Connections', 'current connections' => array('SESS', '=sp_who', ''), 'max connections' => array('SESS', "SELECT @@MAX_CONNECTIONS", ''), false ); function __construct(&$conn) { if ($conn->dataProvider == 'odbc') { $this->sql1 = 'sql1'; //$this->explain = false; } $this->conn = $conn; } function Explain($sql,$partial=false) { $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $s = '

Explain: '.htmlspecialchars($sql).'

'; $this->conn->Execute("SET SHOWPLAN_ALL ON;"); $sql = str_replace('?',"''",$sql); global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; $rs = $this->conn->Execute($sql); //adodb_printr($rs); $ADODB_FETCH_MODE = $save; if ($rs && !$rs->EOF) { $rs->MoveNext(); $s .= ''; while (!$rs->EOF) { $s .= '\n"; ## NOTE CORRUPT tag is intentional!!!! $rs->MoveNext(); } $s .= '
Rows IO CPU     Plan
'.round($rs->fields[8],1).''.round($rs->fields[9],3).''.round($rs->fields[10],3).'
'.htmlspecialchars($rs->fields[0])."
'; $rs->NextRecordSet(); } $this->conn->Execute("SET SHOWPLAN_ALL OFF;"); $this->conn->LogSQL($save); $s .= $this->Tracer($sql); return $s; } function Tables() { global $ADODB_FETCH_MODE; $save = $ADODB_FETCH_MODE; $ADODB_FETCH_MODE = ADODB_FETCH_NUM; //$this->conn->debug=1; $s = ''; $rs1 = $this->conn->Execute("select distinct name from sysobjects where xtype='U'"); if ($rs1) { while (!$rs1->EOF) { $tab = $rs1->fields[0]; $tabq = $this->conn->qstr($tab); $rs2 = $this->conn->Execute("sp_spaceused $tabq"); if ($rs2) { $s .= ''; $rs2->Close(); } $rs1->MoveNext(); } $rs1->Close(); } $ADODB_FETCH_MODE = $save; return $s.'
tablenamesize_in_kindex sizereserved size
'.$tab.''.$rs2->fields[3].''.$rs2->fields[4].''.$rs2->fields[2].'
'; } function sp_who() { $arr = $this->conn->GetArray('sp_who'); return sizeof($arr); } function HealthCheck($cli=false) { $this->conn->Execute('dbcc traceon(3604)'); $html = adodb_perf::HealthCheck($cli); $this->conn->Execute('dbcc traceoff(3604)'); return $html; } } perf-postgres.inc.php000064400000013023152267421130010627 0ustar00 array('RATIO', "select case when count(*)=3 then 'TRUE' else 'FALSE' end from pg_settings where (name='stats_block_level' or name='stats_row_level' or name='stats_start_collector') and setting='on' ", 'Value must be TRUE to enable hit ratio statistics (stats_start_collector,stats_row_level and stats_block_level must be set to true in postgresql.conf)'), 'data cache hit ratio' => array('RATIO', "select case when blks_hit=0 then 0 else round( ((1-blks_read::float/blks_hit)*100)::numeric, 2) end from pg_stat_database where datname='\$DATABASE'", '=WarnCacheRatio'), 'IO', 'data reads' => array('IO', 'select sum(heap_blks_read+toast_blks_read) from pg_statio_user_tables', ), 'data writes' => array('IO', 'select round((sum(n_tup_ins/4.0+n_tup_upd/8.0+n_tup_del/4.0)/16)::numeric,2) from pg_stat_user_tables', 'Count of inserts/updates/deletes * coef'), 'Data Cache', 'data cache buffers' => array('DATAC', "select setting from pg_settings where name='shared_buffers'", 'Number of cache buffers. Tuning'), 'cache blocksize' => array('DATAC', 'select 8192', '(estimate)' ), 'data cache size' => array( 'DATAC', "select setting::integer*8192 from pg_settings where name='shared_buffers'", '' ), 'operating system cache size' => array( 'DATA', "select setting::integer*8192 from pg_settings where name='effective_cache_size'", '(effective cache size)' ), 'Memory Usage', # Postgres 7.5 changelog: Rename server parameters SortMem and VacuumMem to work_mem and maintenance_work_mem; 'sort/work buffer size' => array('CACHE', "select setting::integer*1024 from pg_settings where name='sort_mem' or name = 'work_mem' order by name", 'Size of sort buffer (per query)' ), 'Connections', 'current connections' => array('SESS', 'select count(*) from pg_stat_activity', ''), 'max connections' => array('SESS', "select setting from pg_settings where name='max_connections'", ''), 'Parameters', 'rollback buffers' => array('COST', "select setting from pg_settings where name='wal_buffers'", 'WAL buffers'), 'random page cost' => array('COST', "select setting from pg_settings where name='random_page_cost'", 'Cost of doing a seek (default=4). See random_page_cost'), false ); function __construct(&$conn) { $this->conn = $conn; } var $optimizeTableLow = 'VACUUM %s'; var $optimizeTableHigh = 'VACUUM ANALYZE %s'; /** * @see adodb_perf::optimizeTable() */ function optimizeTable($table, $mode = ADODB_OPT_LOW) { if(! is_string($table)) return false; $conn = $this->conn; if (! $conn) return false; $sql = ''; switch($mode) { case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break; case ADODB_OPT_HIGH: $sql = $this->optimizeTableHigh; break; default : ADOConnection::outp(sprintf("

%s: '%s' using of undefined mode '%s'

", __CLASS__, 'optimizeTable', $mode)); return false; } $sql = sprintf($sql, $table); return $conn->Execute($sql) !== false; } function Explain($sql,$partial=false) { $save = $this->conn->LogSQL(false); if ($partial) { $sqlq = $this->conn->qstr($sql.'%'); $arr = $this->conn->getArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); if ($arr) { foreach($arr as $row) { $sql = reset($row); if (crc32($sql) == $partial) break; } } } $sql = str_replace('?',"''",$sql); $s = '

Explain: '.htmlspecialchars($sql).'

'; $rs = $this->conn->Execute('EXPLAIN '.$sql); $this->conn->LogSQL($save); $s .= '
';
		if ($rs)
			while (!$rs->EOF) {
				$s .= reset($rs->fields)."\n";
				$rs->MoveNext();
			}
		$s .= '
'; $s .= $this->Tracer($sql,$partial); return $s; } }