1<?php
2/*
3V4.98 13 Feb 2008  (c) 2000-2008 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/case when p.value=0 then sga.bytes else to_number(p.value) end)*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	'Backup',
161		'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', 'To turn on archivelog:<br>
162	<pre>
163        SQLPLUS> connect sys as sysdba;
164        SQLPLUS> shutdown immediate;
165
166        SQLPLUS> startup mount exclusive;
167        SQLPLUS> alter database archivelog;
168        SQLPLUS> archive log start;
169        SQLPLUS> alter database open;
170</pre>'),
171
172		'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'),
173		'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value)
174FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''),
175
176	'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.'),
177		'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.'),
178		false
179
180	);
181
182
183	function perf_oci8(&$conn)
184	{
185		$savelog = $conn->LogSQL(false);
186		$this->version = $conn->ServerInfo();
187		$conn->LogSQL($savelog);
188		$this->conn =& $conn;
189	}
190
191	function WarnPageCost($val)
192	{
193		if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>';
194		else $s = '';
195
196		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>. ';
197	}
198
199	function WarnIndexCost($val)
200	{
201		if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>';
202		else $s = '';
203
204		return $s.'Percentage of indexed data blocks expected in the cache.
205			Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0.
206			 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
207		}
208
209	function PGA()
210	{
211		if ($this->version['version'] < 9) return 'Oracle 9i or later required';
212
213		$rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from
214	   (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
215	   	   pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
216	   	   from v\$pga_target_advice) a left join
217	   (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
218	   	   pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
219	   	   from v\$pga_target_advice) b on
220	  a.r = b.r+1 where
221  		b.pct < 100");
222		if (!$rs) return "Only in 9i or later";
223		$rs->Close();
224		if ($rs->EOF) return "PGA could be too big";
225
226		return reset($rs->fields);
227	}
228
229	function Explain($sql,$partial=false)
230	{
231		$savelog = $this->conn->LogSQL(false);
232		$rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
233		if (!$rs) {
234			echo "<p><b>Missing PLAN_TABLE</b></p>
235<pre>
236CREATE TABLE PLAN_TABLE (
237  STATEMENT_ID                    VARCHAR2(30),
238  TIMESTAMP                       DATE,
239  REMARKS                         VARCHAR2(80),
240  OPERATION                       VARCHAR2(30),
241  OPTIONS                         VARCHAR2(30),
242  OBJECT_NODE                     VARCHAR2(128),
243  OBJECT_OWNER                    VARCHAR2(30),
244  OBJECT_NAME                     VARCHAR2(30),
245  OBJECT_INSTANCE                 NUMBER(38),
246  OBJECT_TYPE                     VARCHAR2(30),
247  OPTIMIZER                       VARCHAR2(255),
248  SEARCH_COLUMNS                  NUMBER,
249  ID                              NUMBER(38),
250  PARENT_ID                       NUMBER(38),
251  POSITION                        NUMBER(38),
252  COST                            NUMBER(38),
253  CARDINALITY                     NUMBER(38),
254  BYTES                           NUMBER(38),
255  OTHER_TAG                       VARCHAR2(255),
256  PARTITION_START                 VARCHAR2(255),
257  PARTITION_STOP                  VARCHAR2(255),
258  PARTITION_ID                    NUMBER(38),
259  OTHER                           LONG,
260  DISTRIBUTION                    VARCHAR2(30)
261);
262</pre>";
263			return false;
264		}
265
266		$rs->Close();
267	//	$this->conn->debug=1;
268
269		if ($partial) {
270			$sqlq = $this->conn->qstr($sql.'%');
271			$arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
272			if ($arr) {
273				foreach($arr as $row) {
274					$sql = reset($row);
275					if (crc32($sql) == $partial) break;
276				}
277			}
278		}
279
280		$s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";
281
282		$this->conn->BeginTrans();
283		$id = "ADODB ".microtime();
284
285		$rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
286		$m = $this->conn->ErrorMsg();
287		if ($m) {
288			$this->conn->RollbackTrans();
289			$this->conn->LogSQL($savelog);
290			$s .= "<p>$m</p>";
291			return $s;
292		}
293		$rs =& $this->conn->Execute("
294		select
295  '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation,
296  object_name,COST,CARDINALITY,bytes
297		FROM plan_table
298START WITH id = 0  and STATEMENT_ID='$id'
299CONNECT BY prior id=parent_id and statement_id='$id'");
300
301		$s .= rs2html($rs,false,false,false,false);
302		$this->conn->RollbackTrans();
303		$this->conn->LogSQL($savelog);
304		$s .= $this->Tracer($sql,$partial);
305		return $s;
306	}
307
308
309	function CheckMemory()
310	{
311		if ($this->version['version'] < 9) return 'Oracle 9i or later required';
312
313		 $rs =& $this->conn->Execute("
314select  a.size_for_estimate as cache_mb_estimate,
315	case when a.size_factor=1 then
316   		'&lt;&lt;= current'
317	 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
318		'- BETTER - '
319	else ' ' end as currsize,
320   a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
321   from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a ,
322   (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
323		if (!$rs) return false;
324
325		/*
326		The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
327		*/
328		$s = "<h3>Data Cache Estimate</h3>";
329		if ($rs->EOF) {
330			$s .= "<p>Cache that is 50% of current size is still too big</p>";
331		} else {
332			$s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
333			$s .= rs2html($rs,false,false,false,false);
334		}
335		return $s;
336	}
337
338	/*
339		Generate html for suspicious/expensive sql
340	*/
341	function tohtml(&$rs,$type)
342	{
343		$o1 = $rs->FetchField(0);
344		$o2 = $rs->FetchField(1);
345		$o3 = $rs->FetchField(2);
346		if ($rs->EOF) return '<p>None found</p>';
347		$check = '';
348		$sql = '';
349		$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>';
350		while (!$rs->EOF) {
351			if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
352				if ($check) {
353					$carr = explode('::',$check);
354					$prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
355					$suffix = '</a>';
356					if (strlen($prefix)>2000) {
357						$prefix = '';
358						$suffix = '';
359					}
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				$sql = $rs->fields[2];
364				$check = $rs->fields[0].'::'.$rs->fields[1];
365			} else
366				$sql .= $rs->fields[2];
367			if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
368			$rs->MoveNext();
369		}
370		$rs->Close();
371
372		$carr = explode('::',$check);
373		$prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
374		$suffix = '</a>';
375		if (strlen($prefix)>2000) {
376			$prefix = '';
377			$suffix = '';
378		}
379		$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
380
381		return $s."</table>\n\n";
382	}
383
384	// code thanks to Ixora.
385	// http://www.ixora.com.au/scripts/query_opt.htm
386	// requires oracle 8.1.7 or later
387	function SuspiciousSQL($numsql=10)
388	{
389		$sql = "
390select
391  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
392  s.executions  executes,
393  p.sql_text
394from
395  (
396    select
397      address,
398      buffer_gets,
399      executions,
400      pct,
401      rank() over (order by buffer_gets desc)  ranking
402    from
403      (
404	select
405	  address,
406	  buffer_gets,
407	  executions,
408	  100 * ratio_to_report(buffer_gets) over ()  pct
409	from
410	  sys.v_\$sql
411	where
412	  command_type != 47 and module != 'T.O.A.D.'
413      )
414    where
415      buffer_gets > 50 * executions
416  )  s,
417  sys.v_\$sqltext  p
418where
419  s.ranking <= $numsql and
420  p.address = s.address
421order by
422  1 desc, s.address, p.piece";
423
424  		global $ADODB_CACHE_MODE;
425  		if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
426				$partial = empty($_GET['part']);
427				echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
428		}
429
430		if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
431
432		$s = '';
433		$s .= $this->_SuspiciousSQL($numsql);
434		$s .= '<p>';
435
436		$save = $ADODB_CACHE_MODE;
437		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
438		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
439
440		$savelog = $this->conn->LogSQL(false);
441		$rs =& $this->conn->SelectLimit($sql);
442		$this->conn->LogSQL($savelog);
443
444		if (isset($savem)) $this->conn->SetFetchMode($savem);
445		$ADODB_CACHE_MODE = $save;
446		if ($rs) {
447			$s .= "\n<h3>Ixora Suspicious SQL</h3>";
448			$s .= $this->tohtml($rs,'expsixora');
449		}
450
451		return $s;
452	}
453
454	// code thanks to Ixora.
455	// http://www.ixora.com.au/scripts/query_opt.htm
456	// requires oracle 8.1.7 or later
457	function ExpensiveSQL($numsql = 10)
458	{
459		$sql = "
460select
461  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
462  s.executions  executes,
463  p.sql_text
464from
465  (
466    select
467      address,
468      disk_reads,
469      executions,
470      pct,
471      rank() over (order by disk_reads desc)  ranking
472    from
473      (
474	select
475	  address,
476	  disk_reads,
477	  executions,
478	  100 * ratio_to_report(disk_reads) over ()  pct
479	from
480	  sys.v_\$sql
481	where
482	  command_type != 47 and module != 'T.O.A.D.'
483      )
484    where
485      disk_reads > 50 * executions
486  )  s,
487  sys.v_\$sqltext  p
488where
489  s.ranking <= $numsql and
490  p.address = s.address
491order by
492  1 desc, s.address, p.piece
493";
494		global $ADODB_CACHE_MODE;
495  		if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
496			$partial = empty($_GET['part']);
497			echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
498		}
499		if (isset($_GET['sql'])) {
500			 $var = $this->_ExpensiveSQL($numsql);
501			 return $var;
502		}
503
504		$s = '';
505		$s .= $this->_ExpensiveSQL($numsql);
506		$s .= '<p>';
507		$save = $ADODB_CACHE_MODE;
508		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
509		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
510
511		$savelog = $this->conn->LogSQL(false);
512		$rs =& $this->conn->Execute($sql);
513		$this->conn->LogSQL($savelog);
514
515		if (isset($savem)) $this->conn->SetFetchMode($savem);
516		$ADODB_CACHE_MODE = $save;
517
518		if ($rs) {
519			$s .= "\n<h3>Ixora Expensive SQL</h3>";
520			$s .= $this->tohtml($rs,'expeixora');
521		}
522
523		return $s;
524	}
525
526	function clearsql()
527	{
528	$this->conn->debug=1;
529		$perf_table = adodb_perf::table();
530	// using the naive "delete from $perf_table where created<".$this->conn->sysTimeStamp will cause the table to lock, possibly
531	// for a long time
532		$sql =
533"DECLARE cnt pls_integer;
534BEGIN
535	cnt := 0;
536	FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE created<SYSDATE)
537	LOOP
538	  cnt := cnt + 1;
539	  DELETE FROM $perf_table WHERE ROWID=rec.rr;
540	  IF cnt = 10000 THEN
541	  	COMMIT;
542		cnt := 0;
543	  END IF;
544	END LOOP;
545END;";
546
547		$ok = $this->conn->Execute($sql);
548	}
549}
550?>
551