[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Phpgroupware-cvs] phpgwapi/inc/adodb/perf/perf-oci8.inc.php, 1.2
|
From: |
nomail |
|
Subject: |
[Phpgroupware-cvs] phpgwapi/inc/adodb/perf/perf-oci8.inc.php, 1.2 |
|
Date: |
Thu, 30 Dec 2004 07:47:33 +0100 |
Update of /phpgwapi/inc/adodb/perf
Added Files:
Branch:
perf-oci8.inc.php
date: 2004/12/30 06:47:33; author: skwashd; state: Exp; lines: +499 -0
Log Message:
new HEAD
=====================================================================
<?php
/*
V4.54 5 Nov 2004 (c) 2000-2004 John Lim (address@hidden). 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];
$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,$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";
}
if (isset($HTTP_GET_VARS['sql'])) return
$this->_SuspiciousSQL();
$save = $ADODB_CACHE_MODE;
$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
$savelog = $this->conn->LogSQL(false);
$rs =& $this->conn->SelectLimit($sql);
$this->conn->LogSQL($savelog);
$ADODB_CACHE_MODE = $save;
if ($rs) {
$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.
// 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,$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";
}
if (isset($HTTP_GET_VARS['sql'])) {
$var =& $this->_ExpensiveSQL();
return $var;
}
$save = $ADODB_CACHE_MODE;
$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
$savelog = $this->conn->LogSQL(false);
$rs =& $this->conn->Execute($sql);
$this->conn->LogSQL($savelog);
$ADODB_CACHE_MODE = $save;
if ($rs) {
$s = "\n<h3>Ixora Expensive SQL</h3>";
$s .= $this->tohtml($rs,'expeixora');
} else
$s = '';
if ($s) $s .= '<p>';
$s .= $this->_ExpensiveSQL();
return $s;
}
}
?>