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