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',
'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/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'),
'Backup',
'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', '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;
'),
'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.'),
'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.'),
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 = '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) $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';
$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 "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.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 = "Data Cache 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 \"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 'None found
';
$check = '';
$sql = '';
$s = "\n\n| ".$o1->name.' | '.$o2->name.' | '.$o3->name.' |
';
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| ".$carr[0].' | '.$carr[1].' | '.$prefix.$sql.$suffix.' |
';
}
$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| ".$carr[0].' | '.$carr[1].' | '.$prefix.$sql.$suffix.' |
';
return $s."
\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 = '';
$s .= $this->_SuspiciousSQL($numsql);
$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 = '';
$s .= $this->_ExpensiveSQL($numsql);
$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()
{
$this->conn->debug=1;
$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);
}
}
?>