Update of /cvsroot/mantisbt/mantisbt/core/adodb/perf
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv22504/perf
Modified Files:
perf-db2.inc.php perf-informix.inc.php perf-mssql.inc.php
perf-mysql.inc.php perf-oci8.inc.php perf-postgres.inc.php
Log Message:
Sync: V4.80 8 Mar 2006 of adodb
Index: perf-db2.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-db2.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-db2.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-db2.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,15 +1,15 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ Library for basic performance monitoring and tuning
+
*/
// security - hide paths
@@ -27,16 +27,16 @@
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
+ "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',
@@ -60,7 +60,7 @@
{
$this->conn =& $conn;
}
-
+
function Explain($sql,$partial=false)
{
$save = $this->conn->LogSQL(false);
@@ -85,18 +85,18 @@
$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
+ 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);
}
}
-?>
+?>
\ No newline at end of file
Index: perf-informix.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-informix.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-informix.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-informix.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,15 +1,15 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ Library for basic performance monitoring and tuning
+
*/
// security - hide paths
@@ -31,9 +31,9 @@
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',
@@ -47,20 +47,20 @@
'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;
Index: perf-mysql.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-mysql.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-mysql.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-mysql.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,24 +1,24 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ 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,
@@ -27,7 +27,7 @@
tracer text NOT NULL,
timer decimal(16,6) NOT NULL
)";
-
+
var $settings = array(
'Ratios',
'MyISAM cache hit ratio' => array('RATIO',
@@ -49,7 +49,7 @@
'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'),
@@ -70,25 +70,25 @@
'table cache' => array('CACHE',
array("show variables", 'table_cache'),
'Number of tables to keep open'),
- 'Connections',
+ '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) {
@@ -102,12 +102,12 @@
}
}
$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);
@@ -115,77 +115,95 @@
$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':
+ 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':
+ case 'Com_insert':
$val += $rs->fields[1]; break;
- case 'Com_delete':
+ case 'Com_delete':
$val += $rs->fields[1]; break;
- case 'Com_update':
+ 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();
@@ -198,38 +216,46 @@
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 ($hits*100)/$total;
+ 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 $HTTP_SESSION_VARS;
-
+ 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();
@@ -237,23 +263,53 @@
$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];
- $HTTP_SESSION_VARS['INNODB_HIT_PCT'] = $val;
- return $val;
+ $_SESSION['INNODB_HIT_PCT'] = $val;
+ return round($val,2);
} else {
- if (isset($HTTP_SESSION_VARS['INNODB_HIT_PCT'])) return
$HTTP_SESSION_VARS['INNODB_HIT_PCT'];
+ 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 ($hits/($reqs+$hits))*100;
+
+ 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
}
-?>
+?>
\ No newline at end of file
Index: perf-postgres.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-postgres.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-postgres.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-postgres.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,16 +1,16 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ Library for basic performance monitoring and tuning
+
*/
// security - hide paths
@@ -20,13 +20,13 @@
Notice that PostgreSQL has no sql query cache
*/
class perf_postgres extends adodb_perf{
-
- var $tablesSQL =
+
+ 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)
+ 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,
@@ -34,22 +34,22 @@
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
(1-blks_read::float/blks_hit)*100 end from pg_stat_database where
datname='\$DATABASE'",
+ "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 sum(n_tup_ins/4.0+n_tup_upd/8.0+n_tup_del/4.0)/16 from
pg_stat_user_tables',
+ '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',
@@ -66,8 +66,9 @@
"select setting::integer*8192 from pg_settings where
name='effective_cache_size'",
'(effective cache size)' ),
'Memory Usage',
- 'sort buffer size' => array('CACHE',
- "select setting::integer*1024 from pg_settings where
name='sort_mem'",
+ # 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',
@@ -85,16 +86,16 @@
'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");
@@ -120,4 +121,4 @@
return $s;
}
}
-?>
+?>
\ No newline at end of file
Index: perf-mssql.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-mssql.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-mssql.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-mssql.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,16 +1,16 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ Library for basic performance monitoring and tuning
+
*/
// security - hide paths
@@ -29,7 +29,7 @@
tracer varchar(500) NOT NULL,
timer decimal(16,6) NOT NULL
)";
-
+
var $settings = array(
'Ratios',
'data cache hit ratio' => array('RATIO',
@@ -46,7 +46,7 @@
"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'",
@@ -63,8 +63,8 @@
false
);
-
-
+
+
function perf_mssql(&$conn)
{
if ($conn->dataProvider == 'odbc') {
@@ -73,10 +73,10 @@
}
$this->conn =& $conn;
}
-
+
function Explain($sql,$partial=false)
{
-
+
$save = $this->conn->LogSQL(false);
if ($partial) {
$sqlq = $this->conn->qstr($sql.'%');
@@ -88,12 +88,12 @@
}
}
}
-
+
$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);
@@ -107,20 +107,20 @@
$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;
@@ -142,23 +142,23 @@
$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;
}
-
-
+
+
}
-?>
+?>
\ No newline at end of file
Index: perf-oci8.inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/adodb/perf/perf-oci8.inc.php,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -d -r1.3 -r1.4
--- perf-oci8.inc.php 12 Feb 2005 20:03:44 -0000 1.3
+++ perf-oci8.inc.php 22 Apr 2006 11:12:16 -0000 1.4
@@ -1,25 +1,25 @@
<?php
-/*
-V4.60 24 Jan 2005 (c) 2000-2005 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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.
+/*
+V4.80 8 Mar 2006 (c) 2000-2006 John Lim
(jlim-l15XFKl8ZeYZ+IcD6AW/HA@xxxxxxxxxxxxxxxx). 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
-
+
+ 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
+
+ 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,
@@ -29,32 +29,32 @@
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)
+ "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) +
+ "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),
+ "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
0,1,(a.VALUE + b.VALUE)),2)
-FROM v\$sysstat a,
+FROM v\$sysstat a,
v\$sysstat b
WHERE a.name = 'sorts (disk)'
AND b.name = 'sorts (memory)'",
@@ -63,18 +63,18 @@
'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
+ "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'",
@@ -89,11 +89,11 @@
"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',
+ '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',
@@ -102,20 +102,20 @@
"select value from v\$parameter where name='sessions'",
''),
- 'Memory Utilization',
+ '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
@@ -131,7 +131,7 @@
'rollback segments' => array('ROLLBACK',
"select count(*) from sys.v_\$rollstat",
''),
-
+
'peak transactions' => array('ROLLBACK',
"select max_utilization tx_hwm
from sys.v_\$resource_limit
@@ -140,7 +140,7 @@
'max transactions' => array('ROLLBACK',
"select value from v\$parameter where name =
'transactions'",
'max transactions / rollback segments < 3.5 (or
transactions_per_rollback_segment)'),
- 'Parameters',
+ '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>.'),
@@ -156,59 +156,59 @@
'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);
+ $savelog = $conn->LogSQL(false);
$this->version = $conn->ServerInfo();
- $conn->LogSQL($savelog);
+ $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
+
+ $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
+ 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
+ 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)
+
+ function Explain($sql,$partial=false)
{
$savelog = $this->conn->LogSQL(false);
$rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
@@ -244,10 +244,10 @@
</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");
@@ -258,11 +258,12 @@
}
}
}
-
- $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";
-
+
+ $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) {
@@ -271,38 +272,38 @@
$s .= "<p>$m</p>";
return $s;
}
- $rs = $this->conn->Execute("
- select
- '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' ||
trim(options)||'</pre>' as Operation,
+ $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'
+ 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
+ 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,
+ 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 ,
+ 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
*/
@@ -315,7 +316,7 @@
}
return $s;
}
-
+
/*
Generate html for suspicious/expensive sql
*/
@@ -338,18 +339,18 @@
$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];
+ $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>';
@@ -358,11 +359,11 @@
$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.
+
+ // code thanks to Ixora.
// http://www.ixora.com.au/scripts/query_opt.htm
// requires oracle 8.1.7 or later
function SuspiciousSQL($numsql=10)
@@ -373,7 +374,7 @@
s.executions executes,
p.sql_text
from
- (
+ (
select
address,
buffer_gets,
@@ -381,7 +382,7 @@
pct,
rank() over (order by buffer_gets desc) ranking
from
- (
+ (
select
address,
buffer_gets,
@@ -402,32 +403,37 @@
order by
1 desc, s.address, p.piece";
- global $ADODB_CACHE_MODE,$HTTP_GET_VARS;
- if (isset($HTTP_GET_VARS['expsixora']) &&
isset($HTTP_GET_VARS['sql'])) {
- $partial = empty($HTTP_GET_VARS['part']);
- echo "<a
name=explain></a>".$this->Explain($HTTP_GET_VARS['sql'],$partial)."\n";
+ 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($HTTP_GET_VARS['sql'])) return
$this->_SuspiciousSQL();
-
+ 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 .= "\n<h3>Ixora Suspicious SQL</h3>";
$s .= $this->tohtml($rs,'expsixora');
- } else
- $s = '';
-
- if ($s) $s .= '<p>';
- $s .= $this->_SuspiciousSQL();
+ }
+
return $s;
}
-
- // code thanks to Ixora.
+
+ // code thanks to Ixora.
// http://www.ixora.com.au/scripts/query_opt.htm
// requires oracle 8.1.7 or later
function ExpensiveSQL($numsql = 10)
@@ -438,7 +444,7 @@
s.executions executes,
p.sql_text
from
- (
+ (
select
address,
disk_reads,
@@ -467,33 +473,37 @@
order by
1 desc, s.address, p.piece
";
- global $ADODB_CACHE_MODE,$HTTP_GET_VARS;
- if (isset($HTTP_GET_VARS['expeixora']) &&
isset($HTTP_GET_VARS['sql'])) {
- $partial = empty($HTTP_GET_VARS['part']);
- echo "<a
name=explain></a>".$this->Explain($HTTP_GET_VARS['sql'],$partial)."\n";
+ 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($HTTP_GET_VARS['sql'])) {
- $var =& $this->_ExpensiveSQL();
+ 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 .= "\n<h3>Ixora Expensive SQL</h3>";
$s .= $this->tohtml($rs,'expeixora');
- } else
- $s = '';
-
-
- if ($s) $s .= '<p>';
- $s .= $this->_ExpensiveSQL();
+ }
+
return $s;
}
-
+
}
-?>
+?>
\ No newline at end of file
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
|
