perf-oci8.inc.php

这是一个嵌入式linux系统下的命令工具包

					$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 
   		'&lt;&lt;= current'
	 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
		'- BETTER - '
	else ' ' end as currsize, 
   a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
   from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a , 
   (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
		if (!$rs) return false;
		
		/*
		The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
		*/
		$s = "<h3>Data Cache Estimate</h3>";
		if ($rs->EOF) {
			$s .= "<p>Cache that is 50% of current size is still too big</p>";
		} else {
			$s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
			$s .= rs2html($rs,false,false,false,false);
		}
		return $s;
	}
	
	/*
		Generate html for suspicious/expensive sql
	*/
	function tohtml(&$rs,$type)
	{
		$o1 = $rs->FetchField(0);
		$o2 = $rs->FetchField(1);
		$o3 = $rs->FetchField(2);
		if ($rs->EOF) return '<p>None found</p>';
		$check = '';
		$sql = '';
		$s = "\n\n<table border=1 bgcolor=white><tr><td><b>".$o1->name.'</b></td><td><b>'.$o2->name.'</b></td><td><b>'.$o3->name.'</b></td></tr>';
		while (!$rs->EOF) {
			if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
				if ($check) {
					$carr = explode('::',$check);
					$prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
					$suffix = '</a>';
					if (strlen($prefix)>2000) {
						$prefix = '';
						$suffix = '';
					}
					
					$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
				}
				$sql = $rs->fields[2];
				$check = $rs->fields[0].'::'.$rs->fields[1];			
			} else
				$sql .= $rs->fields[2];
			if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
			$rs->MoveNext();
		}
		$rs->Close();
		
		$carr = explode('::',$check);
		$prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
		$suffix = '</a>';
		if (strlen($prefix)>2000) {
			$prefix = '';
			$suffix = '';
		}
		$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
					
		return $s."</table>\n\n";
	}
	
	// code thanks to Ixora. 
	// http://www.ixora.com.au/scripts/query_opt.htm
	// requires oracle 8.1.7 or later
	function SuspiciousSQL($numsql=10)
	{
		$sql = "
select
  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
  s.executions  executes,
  p.sql_text
from
  ( 
    select
      address,
      buffer_gets,
      executions,
      pct,
      rank() over (order by buffer_gets desc)  ranking
    from
      ( 
	select
	  address,
	  buffer_gets,
	  executions,
	  100 * ratio_to_report(buffer_gets) over ()  pct
	from
	  sys.v_\$sql
	where
	  command_type != 47 and module != 'T.O.A.D.'
      )
    where
      buffer_gets > 50 * executions
  )  s,
  sys.v_\$sqltext  p
where
  s.ranking <= $numsql and
  p.address = s.address
order by
  1 desc, s.address, p.piece";

  		global $ADODB_CACHE_MODE;
  		if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
				$partial = empty($_GET['part']);
				echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
		}

		if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
		
		$s = '';
		$s .= $this->_SuspiciousSQL($numsql);
		$s .= '<p>';
		
		$save = $ADODB_CACHE_MODE;
		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
		
		$savelog = $this->conn->LogSQL(false);
		$rs =& $this->conn->SelectLimit($sql);
		$this->conn->LogSQL($savelog);
		
		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_CACHE_MODE = $save;
		if ($rs) {
			$s .= "\n<h3>Ixora Suspicious SQL</h3>";
			$s .= $this->tohtml($rs,'expsixora');
		}
		
		return $s;
	}
	
	// code thanks to Ixora. 
	// http://www.ixora.com.au/scripts/query_opt.htm
	// requires oracle 8.1.7 or later
	function ExpensiveSQL($numsql = 10)
	{
		$sql = "
select
  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
  s.executions  executes,
  p.sql_text
from
  ( 
    select
      address,
      disk_reads,
      executions,
      pct,
      rank() over (order by disk_reads desc)  ranking
    from
      (
	select
	  address,
	  disk_reads,
	  executions,
	  100 * ratio_to_report(disk_reads) over ()  pct
	from
	  sys.v_\$sql
	where
	  command_type != 47 and module != 'T.O.A.D.'
      )
    where
      disk_reads > 50 * executions
  )  s,
  sys.v_\$sqltext  p
where
  s.ranking <= $numsql and
  p.address = s.address
order by
  1 desc, s.address, p.piece
";
		global $ADODB_CACHE_MODE;
  		if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
			$partial = empty($_GET['part']);	
			echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
		}
		if (isset($_GET['sql'])) {
			 $var = $this->_ExpensiveSQL($numsql);
			 return $var;
		}
		
		$s = '';		
		$s .= $this->_ExpensiveSQL($numsql);
		$s .= '<p>';
		$save = $ADODB_CACHE_MODE;
		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
		
		$savelog = $this->conn->LogSQL(false);
		$rs =& $this->conn->Execute($sql);
		$this->conn->LogSQL($savelog);
		
		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_CACHE_MODE = $save;
		
		if ($rs) {
			$s .= "\n<h3>Ixora Expensive SQL</h3>";
			$s .= $this->tohtml($rs,'expeixora');
		}
	
		return $s;
	}
	
}
?>