/web/kaklik's_web/torrentflux/adodb/perf/perf-db2.inc.php |
---|
0,0 → 1,102 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
// Simple guide to configuring db2: so-so http://www.devx.com/gethelpon/10MinuteSolution/16575 |
// SELECT * FROM TABLE(SNAPSHOT_APPL('SAMPLE', -1)) as t |
class perf_db2 extends adodb_perf{ |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created TIMESTAMP NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 varchar(4000) NOT NULL, |
params varchar(3000) NOT NULL, |
tracer varchar(500) NOT NULL, |
timer decimal(16,6) NOT NULL |
)"; |
var $settings = array( |
'Ratios', |
'data cache hit ratio' => 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 <a href=http://www7b.boulder.ibm.com/dmdd/library/techarticle/anshum/0107anshum.html#bufferpoolsize>tuning reference</a>.' ), |
'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 perf_db2(&$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 "<p>Have EXPLAIN tables been created?</p>"; |
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; |
} |
function Tables() |
{ |
$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); |
} |
} |
?> |
/web/kaklik's_web/torrentflux/adodb/perf/perf-informix.inc.php |
---|
0,0 → 1,70 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
// |
// Thx to Fernando Ortiz, mailto:fortiz#lacorona.com.mx |
// With info taken from http://www.oninit.com/oninit/sysmaster/index.html |
// |
class perf_informix extends adodb_perf{ |
// Maximum size on varchar upto 9.30 255 chars |
// better truncate varchar to 255 than char(4000) ? |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created datetime year to second NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 varchar(255) NOT NULL, |
params varchar(255) NOT NULL, |
tracer varchar(255) NOT NULL, |
timer decimal(16,6) NOT NULL |
)"; |
var $tablesSQL = "select a.tabname tablename, ti_nptotal*2 size_in_k, ti_nextns extents, ti_nrows records from systables c, sysmaster:systabnames a, sysmaster:systabinfo b where c.tabname not matches 'sys*' and c.partnum = a.partnum and c.partnum = b.ti_partnum"; |
var $settings = array( |
'Ratios', |
'data cache hit ratio' => 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 perf_informix(&$conn) |
{ |
$this->conn =& $conn; |
} |
} |
?> |
/web/kaklik's_web/torrentflux/adodb/perf/perf-mssql.inc.php |
---|
0,0 → 1,164 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
/* |
MSSQL has moved most performance info to Performance Monitor |
*/ |
class perf_mssql extends adodb_perf{ |
var $sql1 = 'cast(sql1 as text)'; |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created datetime NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 varchar(4000) NOT NULL, |
params varchar(3000) NOT NULL, |
tracer varchar(500) NOT NULL, |
timer decimal(16,6) NOT NULL |
)"; |
var $settings = array( |
'Ratios', |
'data cache hit ratio' => 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 perf_mssql(&$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 = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>'; |
$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 .= '<table bgcolor=white border=0 cellpadding="1" callspacing=0><tr><td nowrap align=center> Rows<td nowrap align=center> IO<td nowrap align=center> CPU<td align=left> Plan</tr>'; |
while (!$rs->EOF) { |
$s .= '<tr><td>'.round($rs->fields[8],1).'<td>'.round($rs->fields[9],3).'<td align=right>'.round($rs->fields[10],3).'<td nowrap><pre>'.htmlspecialchars($rs->fields[0])."</td></pre></tr>\n"; ## NOTE CORRUPT </td></pre> tag is intentional!!!! |
$rs->MoveNext(); |
} |
$s .= '</table>'; |
$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 = '<table border=1 bgcolor=white><tr><td><b>tablename</b></td><td><b>size_in_k</b></td><td><b>index size</b></td><td><b>reserved size</b></td></tr>'; |
$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 .= '<tr><td>'.$tab.'</td><td align=right>'.$rs2->fields[3].'</td><td align=right>'.$rs2->fields[4].'</td><td align=right>'.$rs2->fields[2].'</td></tr>'; |
$rs2->Close(); |
} |
$rs1->MoveNext(); |
} |
$rs1->Close(); |
} |
$ADODB_FETCH_MODE = $save; |
return $s.'</table>'; |
} |
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; |
} |
} |
?> |
/web/kaklik's_web/torrentflux/adodb/perf/perf-mysql.inc.php |
---|
0,0 → 1,315 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
class perf_mysql extends adodb_perf{ |
var $tablesSQL = 'show table status'; |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created datetime NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 text NOT NULL, |
params text NOT NULL, |
tracer text NOT NULL, |
timer decimal(16,6) NOT NULL |
)"; |
var $settings = array( |
'Ratios', |
'MyISAM cache hit ratio' => 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 perf_mysql(&$conn) |
{ |
$this->conn =& $conn; |
} |
function Explain($sql,$partial=false) |
{ |
if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '<p>Unable to EXPLAIN non-select statement</p>'; |
$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 = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>'; |
$rs = $this->conn->Execute('EXPLAIN '.$sql); |
$s .= rs2html($rs,false,false,false,false); |
$this->conn->LogSQL($save); |
$s .= $this->Tracer($sql); |
return $s; |
} |
function Tables() |
{ |
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 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 dont use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0) |
ADOConnection::outp( sprintf( "<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, __FUNCTION__, $mode)); |
return false; |
} |
} |
$sql = sprintf( $sql, $table); |
return $conn->Execute( $sql) !== false; |
} |
// end hack |
} |
?> |
/web/kaklik's_web/torrentflux/adodb/perf/perf-oci8.inc.php |
---|
0,0 → 1,509 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
class perf_oci8 extends ADODB_perf{ |
var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents |
group by segment_name,tablespace_name"; |
var $version; |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created date NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 varchar(4000) NOT NULL, |
params varchar(4000), |
tracer varchar(4000), |
timer decimal(16,6) NOT NULL |
)"; |
var $settings = array( |
'Ratios', |
'data cache hit ratio' => 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 <i>shared_pool_size</i> 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 <i>shared_pool_size</i> 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 <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'), |
'data cache blocksize' => array('DATAC', |
"select value from v\$parameter where name='db_block_size'", |
'' ), |
'Memory Pools', |
'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) ' ), |
'pga buffer size' => array('CACHE', |
"select value from v\$parameter where name='pga_aggregate_target'", |
'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ), |
'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/p.value)*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 <a href=http://www.praetoriate.com/oracle_tips_cursor_sharing.htm>cursor_sharing</a>.'), |
/* |
'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'), |
false |
); |
function perf_oci8(&$conn) |
{ |
$savelog = $conn->LogSQL(false); |
$this->version = $conn->ServerInfo(); |
$conn->LogSQL($savelog); |
$this->conn =& $conn; |
} |
function WarnPageCost($val) |
{ |
if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>'; |
else $s = ''; |
return $s.'Recommended is 20-50 for TP, and 50 for data warehouses. Default is 100. See <a href=http://www.dba-oracle.com/oracle_tips_cost_adj.htm>optimizer_index_cost_adj</a>. '; |
} |
function WarnIndexCost($val) |
{ |
if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>'; |
else $s = ''; |
return $s.'Percentage of indexed data blocks expected in the cache. |
Recommended is 20 (fast disk array) to 50 (slower hard disks). Default is 0. |
See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.'; |
} |
function PGA() |
{ |
if ($this->version['version'] < 9) return 'Oracle 9i or later required'; |
$rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct 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 "Only in 9i or later"; |
$rs->Close(); |
if ($rs->EOF) return "PGA could be too big"; |
return reset($rs->fields); |
} |
function Explain($sql,$partial=false) |
{ |
$savelog = $this->conn->LogSQL(false); |
$rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE"); |
if (!$rs) { |
echo "<p><b>Missing PLAN_TABLE</b></p> |
<pre> |
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) |
); |
</pre>"; |
return false; |
} |
$rs->Close(); |
// $this->conn->debug=1; |
if ($partial) { |
$sqlq = $this->conn->qstr($sql.'%'); |
$arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq"); |
if ($arr) { |
foreach($arr as $row) { |
$sql = reset($row); |
if (crc32($sql) == $partial) break; |
} |
} |
} |
$s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>"; |
$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 .= "<p>$m</p>"; |
return $s; |
} |
$rs =& $this->conn->Execute(" |
select |
'<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>' 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.size_for_estimate as cache_mb_estimate, |
case when a.size_factor=1 then |
'<<= current' |
when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then |
'- BETTER - ' |
else ' ' end as currsize, |
a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0 |
from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) a , |
(select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1"); |
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 = "<h3>Data Cache Estimate</h3>"; |
if ($rs->EOF) { |
$s .= "<p>Cache that is 50% of current size is still too big</p>"; |
} else { |
$s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero."; |
$s .= rs2html($rs,false,false,false,false); |
} |
return $s; |
} |
/* |
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 '<p>None found</p>'; |
$check = ''; |
$sql = ''; |
$s = "\n\n<table border=1 bgcolor=white><tr><td><b>".$o1->name.'</b></td><td><b>'.$o2->name.'</b></td><td><b>'.$o3->name.'</b></td></tr>'; |
while (!$rs->EOF) { |
if ($check != $rs->fields[0].'::'.$rs->fields[1]) { |
if ($check) { |
$carr = explode('::',$check); |
$prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">'; |
$suffix = '</a>'; |
if (strlen($prefix)>2000) { |
$prefix = ''; |
$suffix = ''; |
} |
$s .= "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>'; |
} |
$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 = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">'; |
$suffix = '</a>'; |
if (strlen($prefix)>2000) { |
$prefix = ''; |
$suffix = ''; |
} |
$s .= "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>'; |
return $s."</table>\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 "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; |
} |
if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql); |
$s = ''; |
$s .= $this->_SuspiciousSQL($numsql); |
$s .= '<p>'; |
$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<h3>Ixora Suspicious SQL</h3>"; |
$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 "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; |
} |
if (isset($_GET['sql'])) { |
$var = $this->_ExpensiveSQL($numsql); |
return $var; |
} |
$s = ''; |
$s .= $this->_ExpensiveSQL($numsql); |
$s .= '<p>'; |
$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<h3>Ixora Expensive SQL</h3>"; |
$s .= $this->tohtml($rs,'expeixora'); |
} |
return $s; |
} |
} |
?> |
/web/kaklik's_web/torrentflux/adodb/perf/perf-postgres.inc.php |
---|
0,0 → 1,124 |
<?php |
/* |
V4.80 8 Mar 2006 (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved. |
Released under both BSD license and Lesser GPL library license. |
Whenever there is any discrepancy between the two licenses, |
the BSD license will take precedence. See License.txt. |
Set tabs to 4 for best viewing. |
Latest version is available at http://adodb.sourceforge.net |
Library for basic performance monitoring and tuning |
*/ |
// security - hide paths |
if (!defined('ADODB_DIR')) die(); |
/* |
Notice that PostgreSQL has no sql query cache |
*/ |
class perf_postgres extends adodb_perf{ |
var $tablesSQL = |
"select a.relname as tablename,(a.relpages+CASE WHEN b.relpages is null THEN 0 ELSE b.relpages END+CASE WHEN c.relpages is null THEN 0 ELSE c.relpages END)*8 as size_in_K,a.relfilenode as \"OID\" from pg_class a left join pg_class b |
on b.relname = 'pg_toast_'||trim(a.relfilenode) |
left join pg_class c on c.relname = 'pg_toast_'||trim(a.relfilenode)||'_index' |
where a.relname in (select tablename from pg_tables where tablename not like 'pg_%')"; |
var $createTableSQL = "CREATE TABLE adodb_logsql ( |
created timestamp NOT NULL, |
sql0 varchar(250) NOT NULL, |
sql1 text NOT NULL, |
params text NOT NULL, |
tracer text NOT NULL, |
timer decimal(16,6) NOT NULL |
)"; |
var $settings = array( |
'Ratios', |
'statistics collector' => 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 (<i>stats_start_collector</i>,<i>stats_row_level</i> and <i>stats_block_level</i> 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. <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic>Tuning</a>'), |
'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 <a href=http://www.varlena.com/GeneralBits/Tidbits/perf.html#less>random_page_cost</a>'), |
false |
); |
function perf_postgres(&$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 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 = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>'; |
$rs = $this->conn->Execute('EXPLAIN '.$sql); |
$this->conn->LogSQL($save); |
$s .= '<pre>'; |
if ($rs) |
while (!$rs->EOF) { |
$s .= reset($rs->fields)."\n"; |
$rs->MoveNext(); |
} |
$s .= '</pre>'; |
$s .= $this->Tracer($sql,$partial); |
return $s; |
} |
} |
?> |