1<?php
2/*
3V4.65 22 July 2005  (c) 2000-2005 John Lim (jlim@natsoft.com.my). All rights reserved.
4  Released under both BSD license and Lesser GPL library license.
5  Whenever there is any discrepancy between the two licenses,
6  the BSD license will take precedence. See License.txt.
7  Set tabs to 4 for best viewing.
8
9  Latest version is available at http://adodb.sourceforge.net
10
11  Library for basic performance monitoring and tuning
12
13*/
14
15// security - hide paths
16if (!defined('ADODB_DIR')) die();
17
18class perf_oci8 extends ADODB_perf{
19
20	var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
21	   group by segment_name,tablespace_name";
22
23	var $version;
24	var $createTableSQL = "CREATE TABLE adodb_logsql (
25		  created date NOT NULL,
26		  sql0 varchar(250) NOT NULL,
27		  sql1 varchar(4000) NOT NULL,
28		  params varchar(4000),
29		  tracer varchar(4000),
30		  timer decimal(16,6) NOT NULL
31		)";
32
33	var $settings = array(
34	'Ratios',
35		'data cache hit ratio' => array('RATIOH',
36			"select round((1-(phy.value / (cur.value + con.value)))*100,2)
37			from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
38			where cur.name = 'db block gets' and
39			      con.name = 'consistent gets' and
40			      phy.name = 'physical reads'",
41			'=WarnCacheRatio'),
42
43		'sql cache hit ratio' => array( 'RATIOH',
44			'select round(100*(sum(pins)-sum(reloads))/sum(pins),2)  from v$librarycache',
45			'increase <i>shared_pool_size</i> if too ratio low'),
46
47		'datadict cache hit ratio' => array('RATIOH',
48		"select
49           round((1 - (sum(getmisses) / (sum(gets) +
50   		 sum(getmisses))))*100,2)
51		from  v\$rowcache",
52		'increase <i>shared_pool_size</i> if too ratio low'),
53
54		'memory sort ratio' => array('RATIOH',
55		"SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
56       0,1,(a.VALUE + b.VALUE)),2)
57FROM   v\$sysstat a,
58       v\$sysstat b
59WHERE  a.name = 'sorts (disk)'
60AND    b.name = 'sorts (memory)'",
61	"% of memory sorts compared to disk sorts - should be over 95%"),
62
63	'IO',
64		'data reads' => array('IO',
65		"select value from v\$sysstat where name='physical reads'"),
66
67	'data writes' => array('IO',
68		"select value from v\$sysstat where name='physical writes'"),
69
70	'Data Cache',
71		'data cache buffers' => array( 'DATAC',
72		"select a.value/b.value  from v\$parameter a, v\$parameter b
73			where a.name = 'db_cache_size' and b.name= 'db_block_size'",
74			'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
75		'data cache blocksize' => array('DATAC',
76			"select value from v\$parameter where name='db_block_size'",
77			'' ),
78	'Memory Pools',
79		'data cache size' => array('DATAC',
80			"select value from v\$parameter where name = 'db_cache_size'",
81			'db_cache_size' ),
82		'shared pool size' => array('DATAC',
83			"select value from v\$parameter where name = 'shared_pool_size'",
84			'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
85		'java pool size' => array('DATAJ',
86			"select value from v\$parameter where name = 'java_pool_size'",
87			'java_pool_size' ),
88		'large pool buffer size' => array('CACHE',
89			"select value from v\$parameter where name='large_pool_size'",
90			'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) ' ),
91
92		'pga buffer size' => array('CACHE',
93			"select value from v\$parameter where name='pga_aggregate_target'",
94			'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ),
95
96
97		'Connections',
98		'current connections' => array('SESS',
99			'select count(*) from sys.v_$session where username is not null',
100			''),
101		'max connections' => array( 'SESS',
102			"select value from v\$parameter where name='sessions'",
103			''),
104
105	'Memory Utilization',
106		'data cache utilization ratio' => array('RATIOU',
107			"select round((1-bytes/sgasize)*100, 2)
108			from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
109			where name = 'free memory' and pool = 'shared pool'",
110		'Percentage of data cache actually in use - should be over 85%'),
111
112		'shared pool utilization ratio' => array('RATIOU',
113		'select round((sga.bytes/p.value)*100,2)
114		from v$sgastat sga, v$parameter p
115		where sga.name = \'free memory\' and sga.pool = \'shared pool\'
116		and p.name = \'shared_pool_size\'',
117		'Percentage of shared pool actually used - too low is bad, too high is worse'),
118
119		'large pool utilization ratio' => array('RATIOU',
120			"select round((1-bytes/sgasize)*100, 2)
121			from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
122			where name = 'free memory' and pool = 'large pool'",
123		'Percentage of large_pool actually in use - too low is bad, too high is worse'),
124		'sort buffer size' => array('CACHE',
125			"select value from v\$parameter where name='sort_area_size'",
126			'max in-mem sort_area_size (per query), uses memory in pga' ),
127
128		'pga usage at peak' => array('RATIOU',
129		'=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),
130	'Transactions',
131		'rollback segments' => array('ROLLBACK',
132			"select count(*) from sys.v_\$rollstat",
133			''),
134
135		'peak transactions' => array('ROLLBACK',
136			"select max_utilization  tx_hwm
137    		from sys.v_\$resource_limit
138    		where resource_name = 'transactions'",
139			'Taken from high-water-mark'),
140		'max transactions' => array('ROLLBACK',
141			"select value from v\$parameter where name = 'transactions'",
142			'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
143	'Parameters',
144		'cursor sharing' => array('CURSOR',
145			"select value from v\$parameter where name = 'cursor_sharing'",
146			'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>.'),
147		/*
148		'cursor reuse' => array('CURSOR',
149			"select count(*) from (select sql_text_wo_constants, count(*)
150  from t1
151 group by sql_text_wo_constants
152having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
153		'index cache cost' => array('COST',
154			"select value from v\$parameter where name = 'optimizer_index_caching'",
155			'=WarnIndexCost'),
156		'random page cost' => array('COST',
157			"select value from v\$parameter where name = 'optimizer_index_cost_adj'",
158			'=WarnPageCost'),
159
160		false
161
162	);
163
164
165	function perf_oci8(&$conn)
166	{
167		$savelog = $conn->LogSQL(false);
168		$this->version = $conn->ServerInfo();
169		$conn->LogSQL($savelog);
170		$this->conn =& $conn;
171	}
172
173	function WarnPageCost($val)
174	{
175		if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>';
176		else $s = '';
177
178		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>. ';
179	}
180
181	function WarnIndexCost($val)
182	{
183		if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>';
184		else $s = '';
185
186		return $s.'Percentage of indexed data blocks expected in the cache.
187			Recommended is 20 (fast disk array) to 50 (slower hard disks). Default is 0.
188			 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
189		}
190
191	function PGA()
192	{
193		if ($this->version['version'] < 9) return 'Oracle 9i or later required';
194
195		$rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from
196	   (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
197	   	   pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
198	   	   from v\$pga_target_advice) a left join
199	   (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
200	   	   pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
201	   	   from v\$pga_target_advice) b on
202	  a.r = b.r+1 where
203  		b.pct < 100");
204		if (!$rs) return "Only in 9i or later";
205		$rs->Close();
206		if ($rs->EOF) return "PGA could be too big";
207
208		return reset($rs->fields);
209	}
210
211	function Explain($sql,$partial=false)
212	{
213		$savelog = $this->conn->LogSQL(false);
214		$rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
215		if (!$rs) {
216			echo "<p><b>Missing PLAN_TABLE</b></p>
217<pre>
218CREATE TABLE PLAN_TABLE (
219  STATEMENT_ID                    VARCHAR2(30),
220  TIMESTAMP                       DATE,
221  REMARKS                         VARCHAR2(80),
222  OPERATION                       VARCHAR2(30),
223  OPTIONS                         VARCHAR2(30),
224  OBJECT_NODE                     VARCHAR2(128),
225  OBJECT_OWNER                    VARCHAR2(30),
226  OBJECT_NAME                     VARCHAR2(30),
227  OBJECT_INSTANCE                 NUMBER(38),
228  OBJECT_TYPE                     VARCHAR2(30),
229  OPTIMIZER                       VARCHAR2(255),
230  SEARCH_COLUMNS                  NUMBER,
231  ID                              NUMBER(38),
232  PARENT_ID                       NUMBER(38),
233  POSITION                        NUMBER(38),
234  COST                            NUMBER(38),
235  CARDINALITY                     NUMBER(38),
236  BYTES                           NUMBER(38),
237  OTHER_TAG                       VARCHAR2(255),
238  PARTITION_START                 VARCHAR2(255),
239  PARTITION_STOP                  VARCHAR2(255),
240  PARTITION_ID                    NUMBER(38),
241  OTHER                           LONG,
242  DISTRIBUTION                    VARCHAR2(30)
243);
244</pre>";
245			return false;
246		}
247
248		$rs->Close();
249	//	$this->conn->debug=1;
250
251		if ($partial) {
252			$sqlq = $this->conn->qstr($sql.'%');
253			$arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
254			if ($arr) {
255				foreach($arr as $row) {
256					$sql = reset($row);
257					if (crc32($sql) == $partial) break;
258				}
259			}
260		}
261
262		$s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";
263
264		$this->conn->BeginTrans();
265		$id = "ADODB ".microtime();
266
267		$rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
268		$m = $this->conn->ErrorMsg();
269		if ($m) {
270			$this->conn->RollbackTrans();
271			$this->conn->LogSQL($savelog);
272			$s .= "<p>$m</p>";
273			return $s;
274		}
275		$rs =& $this->conn->Execute("
276		select
277  '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation,
278  object_name,COST,CARDINALITY,bytes
279		FROM plan_table
280START WITH id = 0  and STATEMENT_ID='$id'
281CONNECT BY prior id=parent_id and statement_id='$id'");
282
283		$s .= rs2html($rs,false,false,false,false);
284		$this->conn->RollbackTrans();
285		$this->conn->LogSQL($savelog);
286		$s .= $this->Tracer($sql,$partial);
287		return $s;
288	}
289
290
291	function CheckMemory()
292	{
293		if ($this->version['version'] < 9) return 'Oracle 9i or later required';
294
295		 $rs =& $this->conn->Execute("
296select  a.size_for_estimate as cache_mb_estimate,
297	case when a.size_factor=1 then
298   		'&lt;&lt;= current'
299	 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
300		'- BETTER - '
301	else ' ' end as currsize,
302   a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
303   from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a ,
304   (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
305		if (!$rs) return false;
306
307		/*
308		The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
309		*/
310		$s = "<h3>Data Cache Estimate</h3>";
311		if ($rs->EOF) {
312			$s .= "<p>Cache that is 50% of current size is still too big</p>";
313		} else {
314			$s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
315			$s .= rs2html($rs,false,false,false,false);
316		}
317		return $s;
318	}
319
320	/*
321		Generate html for suspicious/expensive sql
322	*/
323	function tohtml(&$rs,$type)
324	{
325		$o1 = $rs->FetchField(0);
326		$o2 = $rs->FetchField(1);
327		$o3 = $rs->FetchField(2);
328		if ($rs->EOF) return '<p>None found</p>';
329		$check = '';
330		$sql = '';
331		$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>';
332		while (!$rs->EOF) {
333			if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
334				if ($check) {
335					$carr = explode('::',$check);
336					$prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
337					$suffix = '</a>';
338					if (strlen($prefix)>2000) {
339						$prefix = '';
340						$suffix = '';
341					}
342
343					$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
344				}
345				$sql = $rs->fields[2];
346				$check = $rs->fields[0].'::'.$rs->fields[1];
347			} else
348				$sql .= $rs->fields[2];
349			if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
350			$rs->MoveNext();
351		}
352		$rs->Close();
353
354		$carr = explode('::',$check);
355		$prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
356		$suffix = '</a>';
357		if (strlen($prefix)>2000) {
358			$prefix = '';
359			$suffix = '';
360		}
361		$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
362
363		return $s."</table>\n\n";
364	}
365
366	// code thanks to Ixora.
367	// http://www.ixora.com.au/scripts/query_opt.htm
368	// requires oracle 8.1.7 or later
369	function SuspiciousSQL($numsql=10)
370	{
371		$sql = "
372select
373  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
374  s.executions  executes,
375  p.sql_text
376from
377  (
378    select
379      address,
380      buffer_gets,
381      executions,
382      pct,
383      rank() over (order by buffer_gets desc)  ranking
384    from
385      (
386	select
387	  address,
388	  buffer_gets,
389	  executions,
390	  100 * ratio_to_report(buffer_gets) over ()  pct
391	from
392	  sys.v_\$sql
393	where
394	  command_type != 47 and module != 'T.O.A.D.'
395      )
396    where
397      buffer_gets > 50 * executions
398  )  s,
399  sys.v_\$sqltext  p
400where
401  s.ranking <= $numsql and
402  p.address = s.address
403order by
404  1 desc, s.address, p.piece";
405
406  		global $ADODB_CACHE_MODE;
407  		if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
408				$partial = empty($_GET['part']);
409				echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
410		}
411
412		if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
413
414		$s = '';
415		$s .= $this->_SuspiciousSQL($numsql);
416		$s .= '<p>';
417
418		$save = $ADODB_CACHE_MODE;
419		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
420		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
421
422		$savelog = $this->conn->LogSQL(false);
423		$rs =& $this->conn->SelectLimit($sql);
424		$this->conn->LogSQL($savelog);
425
426		if (isset($savem)) $this->conn->SetFetchMode($savem);
427		$ADODB_CACHE_MODE = $save;
428		if ($rs) {
429			$s .= "\n<h3>Ixora Suspicious SQL</h3>";
430			$s .= $this->tohtml($rs,'expsixora');
431		}
432
433		return $s;
434	}
435
436	// code thanks to Ixora.
437	// http://www.ixora.com.au/scripts/query_opt.htm
438	// requires oracle 8.1.7 or later
439	function ExpensiveSQL($numsql = 10)
440	{
441		$sql = "
442select
443  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
444  s.executions  executes,
445  p.sql_text
446from
447  (
448    select
449      address,
450      disk_reads,
451      executions,
452      pct,
453      rank() over (order by disk_reads desc)  ranking
454    from
455      (
456	select
457	  address,
458	  disk_reads,
459	  executions,
460	  100 * ratio_to_report(disk_reads) over ()  pct
461	from
462	  sys.v_\$sql
463	where
464	  command_type != 47 and module != 'T.O.A.D.'
465      )
466    where
467      disk_reads > 50 * executions
468  )  s,
469  sys.v_\$sqltext  p
470where
471  s.ranking <= $numsql and
472  p.address = s.address
473order by
474  1 desc, s.address, p.piece
475";
476		global $ADODB_CACHE_MODE;
477  		if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
478			$partial = empty($_GET['part']);
479			echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
480		}
481		if (isset($_GET['sql'])) {
482			 $var = $this->_ExpensiveSQL($numsql);
483			 return $var;
484		}
485
486		$s = '';
487		$s .= $this->_ExpensiveSQL($numsql);
488		$s .= '<p>';
489		$save = $ADODB_CACHE_MODE;
490		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
491		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
492
493		$savelog = $this->conn->LogSQL(false);
494		$rs =& $this->conn->Execute($sql);
495		$this->conn->LogSQL($savelog);
496
497		if (isset($savem)) $this->conn->SetFetchMode($savem);
498		$ADODB_CACHE_MODE = $save;
499
500		if ($rs) {
501			$s .= "\n<h3>Ixora Expensive SQL</h3>";
502			$s .= $this->tohtml($rs,'expeixora');
503		}
504
505		return $s;
506	}
507
508}
509?>