perf-oci8.inc.php

一个bug追踪工具的PHP编写的源代码

		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
   		'&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];

			$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;
	}

}
?>