From 6854cb3f4d8219cf1829e32122eb2502a916eae9 Mon Sep 17 00:00:00 2001 From: Andreas Baumann Date: Sat, 1 Feb 2020 09:05:48 +0100 Subject: initial checkin --- vendor/adodb/adodb-php/perf/perf-db2.inc.php | 108 ++++ vendor/adodb/adodb-php/perf/perf-informix.inc.php | 71 +++ vendor/adodb/adodb-php/perf/perf-mssql.inc.php | 164 +++++ .../adodb/adodb-php/perf/perf-mssqlnative.inc.php | 164 +++++ vendor/adodb/adodb-php/perf/perf-mysql.inc.php | 316 +++++++++ vendor/adodb/adodb-php/perf/perf-oci8.inc.php | 703 +++++++++++++++++++++ vendor/adodb/adodb-php/perf/perf-postgres.inc.php | 154 +++++ 7 files changed, 1680 insertions(+) create mode 100644 vendor/adodb/adodb-php/perf/perf-db2.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-informix.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-mssql.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-mssqlnative.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-mysql.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-oci8.inc.php create mode 100644 vendor/adodb/adodb-php/perf/perf-postgres.inc.php (limited to 'vendor/adodb/adodb-php/perf') diff --git a/vendor/adodb/adodb-php/perf/perf-db2.inc.php b/vendor/adodb/adodb-php/perf/perf-db2.inc.php new file mode 100644 index 0000000..b0d5c7a --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-db2.inc.php @@ -0,0 +1,108 @@ + 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); + } +} diff --git a/vendor/adodb/adodb-php/perf/perf-informix.inc.php b/vendor/adodb/adodb-php/perf/perf-informix.inc.php new file mode 100644 index 0000000..50eab39 --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-informix.inc.php @@ -0,0 +1,71 @@ + 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; + } + +} diff --git a/vendor/adodb/adodb-php/perf/perf-mssql.inc.php b/vendor/adodb/adodb-php/perf/perf-mssql.inc.php new file mode 100644 index 0000000..c9b2fff --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-mssql.inc.php @@ -0,0 +1,164 @@ + 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; + } + + +} diff --git a/vendor/adodb/adodb-php/perf/perf-mssqlnative.inc.php b/vendor/adodb/adodb-php/perf/perf-mssqlnative.inc.php new file mode 100644 index 0000000..56cd2fd --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-mssqlnative.inc.php @@ -0,0 +1,164 @@ + 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; + } + + +} diff --git a/vendor/adodb/adodb-php/perf/perf-mysql.inc.php b/vendor/adodb/adodb-php/perf/perf-mysql.inc.php new file mode 100644 index 0000000..fe0f8b0 --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-mysql.inc.php @@ -0,0 +1,316 @@ + 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; + } + + 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 dont 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 +} diff --git a/vendor/adodb/adodb-php/perf/perf-oci8.inc.php b/vendor/adodb/adodb-php/perf/perf-oci8.inc.php new file mode 100644 index 0000000..8830e6d --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-oci8.inc.php @@ -0,0 +1,703 @@ + 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); + } + +} diff --git a/vendor/adodb/adodb-php/perf/perf-postgres.inc.php b/vendor/adodb/adodb-php/perf/perf-postgres.inc.php new file mode 100644 index 0000000..f136c35 --- /dev/null +++ b/vendor/adodb/adodb-php/perf/perf-postgres.inc.php @@ -0,0 +1,154 @@ + 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 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; + } +} -- cgit v1.2.3-70-g09d2