1<?php
2/*
3@version   v5.20.20  01-Feb-2021
4@copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
5@copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
6  Released under both BSD license and Lesser GPL library license.
7  Whenever there is any discrepancy between the two licenses,
8  the BSD license will take precedence. See License.txt.
9  Set tabs to 4 for best viewing.
10
11  Latest version is available at http://adodb.org/
12
13  Library for basic performance monitoring and tuning
14
15*/
16
17// security - hide paths
18if (!defined('ADODB_DIR')) die();
19
20
21class perf_oci8 extends ADODB_perf{
22
23	var $noShowIxora = 15; // if the sql for suspicious sql is taking too long, then disable ixora
24
25	var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
26	   group by segment_name,tablespace_name";
27
28	var $version;
29
30	var $createTableSQL = "CREATE TABLE adodb_logsql (
31		  created date NOT NULL,
32		  sql0 varchar(250) NOT NULL,
33		  sql1 varchar(4000) NOT NULL,
34		  params varchar(4000),
35		  tracer varchar(4000),
36		  timer decimal(16,6) NOT NULL
37		)";
38
39	var $settings = array(
40	'Ratios',
41		'data cache hit ratio' => array('RATIOH',
42			"select round((1-(phy.value / (cur.value + con.value)))*100,2)
43			from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
44			where cur.name = 'db block gets' and
45			      con.name = 'consistent gets' and
46			      phy.name = 'physical reads'",
47			'=WarnCacheRatio'),
48
49		'sql cache hit ratio' => array( 'RATIOH',
50			'select round(100*(sum(pins)-sum(reloads))/sum(pins),2)  from v$librarycache',
51			'increase <i>shared_pool_size</i> if too ratio low'),
52
53		'datadict cache hit ratio' => array('RATIOH',
54		"select
55           round((1 - (sum(getmisses) / (sum(gets) +
56   		 sum(getmisses))))*100,2)
57		from  v\$rowcache",
58		'increase <i>shared_pool_size</i> if too ratio low'),
59
60		'memory sort ratio' => array('RATIOH',
61		"SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
62       0,1,(a.VALUE + b.VALUE)),2)
63FROM   v\$sysstat a,
64       v\$sysstat b
65WHERE  a.name = 'sorts (disk)'
66AND    b.name = 'sorts (memory)'",
67	"% of memory sorts compared to disk sorts - should be over 95%"),
68
69	'IO',
70		'data reads' => array('IO',
71		"select value from v\$sysstat where name='physical reads'"),
72
73	'data writes' => array('IO',
74		"select value from v\$sysstat where name='physical writes'"),
75
76	'Data Cache',
77
78		'data cache buffers' => array( 'DATAC',
79		"select a.value/b.value  from v\$parameter a, v\$parameter b
80			where a.name = 'db_cache_size' and b.name= 'db_block_size'",
81			'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
82		'data cache blocksize' => array('DATAC',
83			"select value from v\$parameter where name='db_block_size'",
84			'' ),
85
86	'Memory Pools',
87		'Mem Max Target (11g+)' => array( 'DATAC',
88		"select value from v\$parameter where name = 'memory_max_target'",
89			'The memory_max_size is the maximum value to which memory_target can be set.' ),
90	'Memory target (11g+)' => array( 'DATAC',
91		"select value from v\$parameter where name = 'memory_target'",
92			'If memory_target is defined then SGA and PGA targets are consolidated into one memory_target.' ),
93		'SGA Max Size' => array( 'DATAC',
94		"select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_max_size'",
95			'The sga_max_size is the maximum value to which sga_target can be set.' ),
96	'SGA target' => array( 'DATAC',
97		"select nvl(value,0)/1024.0/1024 || 'M'  from v\$parameter where name = 'sga_target'",
98			'If sga_target is defined then data cache, shared, java and large pool size can be 0. This is because all these pools are consolidated into one sga_target.' ),
99	'PGA aggr target' => array( 'DATAC',
100		"select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'pga_aggregate_target'",
101			'If pga_aggregate_target is defined then this is the maximum memory that can be allocated for cursor operations such as sorts, group by, joins, merges. When in doubt, set it to 20% of sga_target.' ),
102	'data cache size' => array('DATAC',
103			"select value from v\$parameter where name = 'db_cache_size'",
104			'db_cache_size' ),
105		'shared pool size' => array('DATAC',
106			"select value from v\$parameter where name = 'shared_pool_size'",
107			'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
108		'java pool size' => array('DATAJ',
109			"select value from v\$parameter where name = 'java_pool_size'",
110			'java_pool_size' ),
111		'large pool buffer size' => array('CACHE',
112			"select value from v\$parameter where name='large_pool_size'",
113			'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) ' ),
114
115		'dynamic memory usage' => array('CACHE', "select '-' from dual", '=DynMemoryUsage'),
116
117		'Connections',
118		'current connections' => array('SESS',
119			'select count(*) from sys.v_$session where username is not null',
120			''),
121		'max connections' => array( 'SESS',
122			"select value from v\$parameter where name='sessions'",
123			''),
124
125	'Memory Utilization',
126		'data cache utilization ratio' => array('RATIOU',
127			"select round((1-bytes/sgasize)*100, 2)
128			from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
129			where name = 'free memory' and pool = 'shared pool'",
130		'Percentage of data cache actually in use - should be over 85%'),
131
132		'shared pool utilization ratio' => array('RATIOU',
133		'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2)
134		from v$sgastat sga, v$parameter p
135		where sga.name = \'free memory\' and sga.pool = \'shared pool\'
136		and p.name = \'shared_pool_size\'',
137		'Percentage of shared pool actually used - too low is bad, too high is worse'),
138
139		'large pool utilization ratio' => array('RATIOU',
140			"select round((1-bytes/sgasize)*100, 2)
141			from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
142			where name = 'free memory' and pool = 'large pool'",
143		'Percentage of large_pool actually in use - too low is bad, too high is worse'),
144		'sort buffer size' => array('CACHE',
145			"select value from v\$parameter where name='sort_area_size'",
146			'max in-mem sort_area_size (per query), uses memory in pga' ),
147
148		/*'pga usage at peak' => array('RATIOU',
149		'=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),*/
150	'Transactions',
151		'rollback segments' => array('ROLLBACK',
152			"select count(*) from sys.v_\$rollstat",
153			''),
154
155		'peak transactions' => array('ROLLBACK',
156			"select max_utilization  tx_hwm
157    		from sys.v_\$resource_limit
158    		where resource_name = 'transactions'",
159			'Taken from high-water-mark'),
160		'max transactions' => array('ROLLBACK',
161			"select value from v\$parameter where name = 'transactions'",
162			'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
163	'Parameters',
164		'cursor sharing' => array('CURSOR',
165			"select value from v\$parameter where name = 'cursor_sharing'",
166			'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>.'),
167		/*
168		'cursor reuse' => array('CURSOR',
169			"select count(*) from (select sql_text_wo_constants, count(*)
170  from t1
171 group by sql_text_wo_constants
172having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
173		'index cache cost' => array('COST',
174			"select value from v\$parameter where name = 'optimizer_index_caching'",
175			'=WarnIndexCost'),
176		'random page cost' => array('COST',
177			"select value from v\$parameter where name = 'optimizer_index_cost_adj'",
178			'=WarnPageCost'),
179	'Waits',
180		'Recent wait events' => array('WAITS','select \'Top 5 events\' from dual','=TopRecentWaits'),
181//		'Historical wait SQL' => array('WAITS','select \'Last 2 days\' from dual','=TopHistoricalWaits'), -- requires AWR license
182	'Backup',
183		'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', '=LogMode'),
184
185		'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'),
186		'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value)
187FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''),
188
189		'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.'),
190
191		'Flashback Usage' => array('BACKUP', "select nvl('-','Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')", '=FlashUsage', 'Flashback area usage.'),
192
193		'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.  Recommended set to x2 or x3 times the frequency of your full backup.'),
194		'Recent RMAN Jobs' => array('BACKUP', "select '-' from dual", "=RMAN"),
195
196		//		'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.'),
197      'Storage', 'Tablespaces' => array('TABLESPACE', "select '-' from dual", "=TableSpace"),
198		false
199
200	);
201
202
203	function __construct(&$conn)
204	{
205	global $gSQLBlockRows;
206
207		$gSQLBlockRows = 1000;
208		$savelog = $conn->LogSQL(false);
209		$this->version = $conn->ServerInfo();
210		$conn->LogSQL($savelog);
211		$this->conn = $conn;
212	}
213
214	function LogMode()
215	{
216		$mode = $this->conn->GetOne("select log_mode from v\$database");
217
218		if ($mode == 'ARCHIVELOG') return 'To turn off archivelog:<br>
219	<pre><font size=-2>
220        SQLPLUS> connect sys as sysdba;
221        SQLPLUS> shutdown immediate;
222
223        SQLPLUS> startup mount exclusive;
224        SQLPLUS> alter database noarchivelog;
225        SQLPLUS> alter database open;
226</font></pre>';
227
228		return 'To turn on archivelog:<br>
229	<pre><font size=-2>
230        SQLPLUS> connect sys as sysdba;
231        SQLPLUS> shutdown immediate;
232
233        SQLPLUS> startup mount exclusive;
234        SQLPLUS> alter database archivelog;
235        SQLPLUS> archive log start;
236        SQLPLUS> alter database open;
237</font></pre>';
238	}
239
240	function TopRecentWaits()
241	{
242
243		$rs = $this->conn->Execute("select * from (
244		select event, round(100*time_waited/(select sum(time_waited) from v\$system_event where wait_class <> 'Idle'),1) \"% Wait\",
245    total_waits,time_waited, average_wait,wait_class from v\$system_event where wait_class <> 'Idle' order by 2 desc
246	) where rownum <=5");
247
248		$ret = rs2html($rs,false,false,false,false);
249		return "&nbsp;<p>".$ret."&nbsp;</p>";
250
251	}
252
253	function TopHistoricalWaits()
254	{
255		$days = 2;
256
257		$rs = $this->conn->Execute("select * from (   SELECT
258         b.wait_class,B.NAME,
259        round(sum(wait_time+TIME_WAITED)/1000000) waitsecs,
260        parsing_schema_name,
261        C.SQL_TEXT, a.sql_id
262FROM    V\$ACTIVE_SESSION_HISTORY A
263        join V\$EVENT_NAME B  on  A.EVENT# = B.EVENT#
264       join V\$SQLAREA C  on  A.SQL_ID = C.SQL_ID
265WHERE   A.SAMPLE_TIME BETWEEN sysdate-$days and sysdate
266       and parsing_schema_name not in ('SYS','SYSMAN','DBSNMP','SYSTEM')
267GROUP BY b.wait_class,parsing_schema_name,C.SQL_TEXT, B.NAME,A.sql_id
268order by 3 desc) where rownum <=10");
269
270		$ret = rs2html($rs,false,false,false,false);
271		return "&nbsp;<p>".$ret."&nbsp;</p>";
272
273	}
274
275	function TableSpace()
276	{
277
278		$rs = $this->conn->Execute(
279	"select tablespace_name,round(sum(bytes)/1024/1024) as Used_MB,round(sum(maxbytes)/1024/1024) as Max_MB, round(sum(bytes)/sum(maxbytes),4) * 100 as PCT
280	from dba_data_files
281   group by tablespace_name order by 2 desc");
282
283		$ret = "<p><b>Tablespace</b>".rs2html($rs,false,false,false,false);
284
285		$rs = $this->conn->Execute("select * from dba_data_files order by tablespace_name, 1");
286		$ret .= "<p><b>Datafile</b>".rs2html($rs,false,false,false,false);
287
288		return "&nbsp;<p>".$ret."&nbsp;</p>";
289	}
290
291	function RMAN()
292	{
293		$rs = $this->conn->Execute("select * from (select start_time, end_time, operation, status, mbytes_processed, output_device_type
294			from V\$RMAN_STATUS order by start_time desc) where rownum <=10");
295
296		$ret = rs2html($rs,false,false,false,false);
297		return "&nbsp;<p>".$ret."&nbsp;</p>";
298
299	}
300
301	function DynMemoryUsage()
302	{
303		if (@$this->version['version'] >= 11) {
304			$rs = $this->conn->Execute("select component, current_size/1024./1024 as \"CurrSize (M)\" from  V\$MEMORY_DYNAMIC_COMPONENTS");
305
306		} else
307			$rs = $this->conn->Execute("select name, round(bytes/1024./1024,2) as \"CurrSize (M)\" from  V\$sgainfo");
308
309
310		$ret = rs2html($rs,false,false,false,false);
311		return "&nbsp;<p>".$ret."&nbsp;</p>";
312	}
313
314	function FlashUsage()
315	{
316        $rs = $this->conn->Execute("select * from  V\$FLASH_RECOVERY_AREA_USAGE");
317		$ret = rs2html($rs,false,false,false,false);
318		return "&nbsp;<p>".$ret."&nbsp;</p>";
319	}
320
321	function WarnPageCost($val)
322	{
323		if ($val == 100 && $this->version['version'] < 10) $s = '<font color=red><b>Too High</b>. </font>';
324		else $s = '';
325
326		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>. ';
327	}
328
329	function WarnIndexCost($val)
330	{
331		if ($val == 0 && $this->version['version'] < 10) $s = '<font color=red><b>Too Low</b>. </font>';
332		else $s = '';
333
334		return $s.'Percentage of indexed data blocks expected in the cache.
335			Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0.
336			 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
337		}
338
339	function PGA()
340	{
341
342		//if ($this->version['version'] < 9) return 'Oracle 9i or later required';
343	}
344
345	function PGA_Advice()
346	{
347		$t = "<h3>PGA Advice Estimate</h3>";
348		if ($this->version['version'] < 9) return $t.'Oracle 9i or later required';
349
350		$rs = $this->conn->Execute('select a.MB,
351			case when a.targ = 1 then \'<<= Current \'
352			when a.targ < 1  or a.pct <= b.pct then null
353			else
354			\'- BETTER than Current by \'||round(a.pct/b.pct*100-100,2)||\'%\' end as "Percent Improved",
355	a.targ as  "PGA Size Factor",a.pct "% Perf"
356	from
357       (select round(pga_target_for_estimate/1024.0/1024.0,0) MB,
358              pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
359              from v$pga_target_advice) a left join
360       (select round(pga_target_for_estimate/1024.0/1024.0,0) MB,
361              pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
362              from v$pga_target_advice) b on
363      a.r = b.r+1 where
364          b.pct < 100');
365		if (!$rs) return $t."Only in 9i or later";
366	//	$rs->Close();
367		if ($rs->EOF) return $t."PGA could be too big";
368
369		return $t.rs2html($rs,false,false,true,false);
370	}
371
372	function Explain($sql,$partial=false)
373	{
374		$savelog = $this->conn->LogSQL(false);
375		$rs = $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
376		if (!$rs) {
377			echo "<p><b>Missing PLAN_TABLE</b></p>
378<pre>
379CREATE TABLE PLAN_TABLE (
380  STATEMENT_ID                    VARCHAR2(30),
381  TIMESTAMP                       DATE,
382  REMARKS                         VARCHAR2(80),
383  OPERATION                       VARCHAR2(30),
384  OPTIONS                         VARCHAR2(30),
385  OBJECT_NODE                     VARCHAR2(128),
386  OBJECT_OWNER                    VARCHAR2(30),
387  OBJECT_NAME                     VARCHAR2(30),
388  OBJECT_INSTANCE                 NUMBER(38),
389  OBJECT_TYPE                     VARCHAR2(30),
390  OPTIMIZER                       VARCHAR2(255),
391  SEARCH_COLUMNS                  NUMBER,
392  ID                              NUMBER(38),
393  PARENT_ID                       NUMBER(38),
394  POSITION                        NUMBER(38),
395  COST                            NUMBER(38),
396  CARDINALITY                     NUMBER(38),
397  BYTES                           NUMBER(38),
398  OTHER_TAG                       VARCHAR2(255),
399  PARTITION_START                 VARCHAR2(255),
400  PARTITION_STOP                  VARCHAR2(255),
401  PARTITION_ID                    NUMBER(38),
402  OTHER                           LONG,
403  DISTRIBUTION                    VARCHAR2(30)
404);
405</pre>";
406			return false;
407		}
408
409		$rs->Close();
410	//	$this->conn->debug=1;
411
412		if ($partial) {
413			$sqlq = $this->conn->qstr($sql.'%');
414			$arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
415			if ($arr) {
416				foreach($arr as $row) {
417					$sql = reset($row);
418					if (crc32($sql) == $partial) break;
419				}
420			}
421		}
422
423		$s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";
424
425		$this->conn->BeginTrans();
426		$id = "ADODB ".microtime();
427
428		$rs = $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
429		$m = $this->conn->ErrorMsg();
430		if ($m) {
431			$this->conn->RollbackTrans();
432			$this->conn->LogSQL($savelog);
433			$s .= "<p>$m</p>";
434			return $s;
435		}
436		$rs = $this->conn->Execute("
437		select
438  '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation,
439  object_name,COST,CARDINALITY,bytes
440		FROM plan_table
441START WITH id = 0  and STATEMENT_ID='$id'
442CONNECT BY prior id=parent_id and statement_id='$id'");
443
444		$s .= rs2html($rs,false,false,false,false);
445		$this->conn->RollbackTrans();
446		$this->conn->LogSQL($savelog);
447		$s .= $this->Tracer($sql,$partial);
448		return $s;
449	}
450
451	function CheckMemory()
452	{
453		if ($this->version['version'] < 9) return 'Oracle 9i or later required';
454
455		 $rs = $this->conn->Execute("
456select  a.name Buffer_Pool, b.size_for_estimate as cache_mb_estimate,
457	case when b.size_factor=1 then
458   		'&lt;&lt;= Current'
459	 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0.001 and b.estd_physical_read_factor<1 then
460		'- BETTER than current by ' || round((1-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) || '%'
461	else ' ' end as RATING,
462   b.estd_physical_read_factor \"Phys. Reads Factor\",
463   round((a.estd_physical_read_factor-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) as \"% Improve\"
464   from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r,name from v\$db_cache_advice order by name,1) a ,
465   (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) b
466   where a.r = b.r-1 and a.name = b.name
467  ");
468		if (!$rs) return false;
469
470		/*
471		The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
472		*/
473		$s = "<h3>Data Cache Advice Estimate</h3>";
474		if ($rs->EOF) {
475			$s .= "<p>Cache that is 50% of current size is still too big</p>";
476		} else {
477			$s .= "Ideal size of Data Cache is when %BETTER gets close to zero.";
478			$s .= rs2html($rs,false,false,false,false);
479		}
480		return $s.$this->PGA_Advice();
481	}
482
483	/*
484		Generate html for suspicious/expensive sql
485	*/
486	function tohtml(&$rs,$type)
487	{
488		$o1 = $rs->FetchField(0);
489		$o2 = $rs->FetchField(1);
490		$o3 = $rs->FetchField(2);
491		if ($rs->EOF) return '<p>None found</p>';
492		$check = '';
493		$sql = '';
494		$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>';
495		while (!$rs->EOF) {
496			if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
497				if ($check) {
498					$carr = explode('::',$check);
499					$prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
500					$suffix = '</a>';
501					if (strlen($prefix)>2000) {
502						$prefix = '';
503						$suffix = '';
504					}
505
506					$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
507				}
508				$sql = $rs->fields[2];
509				$check = $rs->fields[0].'::'.$rs->fields[1];
510			} else
511				$sql .= $rs->fields[2];
512			if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
513			$rs->MoveNext();
514		}
515		$rs->Close();
516
517		$carr = explode('::',$check);
518		$prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
519		$suffix = '</a>';
520		if (strlen($prefix)>2000) {
521			$prefix = '';
522			$suffix = '';
523		}
524		$s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
525
526		return $s."</table>\n\n";
527	}
528
529	// code thanks to Ixora.
530	// http://www.ixora.com.au/scripts/query_opt.htm
531	// requires oracle 8.1.7 or later
532	function SuspiciousSQL($numsql=10)
533	{
534		$sql = "
535select
536  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
537  s.executions  executes,
538  p.sql_text
539from
540  (
541    select
542      address,
543      buffer_gets,
544      executions,
545      pct,
546      rank() over (order by buffer_gets desc)  ranking
547    from
548      (
549	select
550	  address,
551	  buffer_gets,
552	  executions,
553	  100 * ratio_to_report(buffer_gets) over ()  pct
554	from
555	  sys.v_\$sql
556	where
557	  command_type != 47 and module != 'T.O.A.D.'
558      )
559    where
560      buffer_gets > 50 * executions
561  )  s,
562  sys.v_\$sqltext  p
563where
564  s.ranking <= $numsql and
565  p.address = s.address
566order by
567  1 desc, s.address, p.piece";
568
569  		global $ADODB_CACHE_MODE;
570  		if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
571				$partial = empty($_GET['part']);
572				echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
573		}
574
575		if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
576
577		$s = '';
578		$timer = time();
579		$s .= $this->_SuspiciousSQL($numsql);
580		$timer = time() - $timer;
581
582		if ($timer > $this->noShowIxora) return $s;
583		$s .= '<p>';
584
585		$save = $ADODB_CACHE_MODE;
586		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
587		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
588
589		$savelog = $this->conn->LogSQL(false);
590		$rs = $this->conn->SelectLimit($sql);
591		$this->conn->LogSQL($savelog);
592
593		if (isset($savem)) $this->conn->SetFetchMode($savem);
594		$ADODB_CACHE_MODE = $save;
595		if ($rs) {
596			$s .= "\n<h3>Ixora Suspicious SQL</h3>";
597			$s .= $this->tohtml($rs,'expsixora');
598		}
599
600		return $s;
601	}
602
603	// code thanks to Ixora.
604	// http://www.ixora.com.au/scripts/query_opt.htm
605	// requires oracle 8.1.7 or later
606	function ExpensiveSQL($numsql = 10)
607	{
608		$sql = "
609select
610  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
611  s.executions  executes,
612  p.sql_text
613from
614  (
615    select
616      address,
617      disk_reads,
618      executions,
619      pct,
620      rank() over (order by disk_reads desc)  ranking
621    from
622      (
623	select
624	  address,
625	  disk_reads,
626	  executions,
627	  100 * ratio_to_report(disk_reads) over ()  pct
628	from
629	  sys.v_\$sql
630	where
631	  command_type != 47 and module != 'T.O.A.D.'
632      )
633    where
634      disk_reads > 50 * executions
635  )  s,
636  sys.v_\$sqltext  p
637where
638  s.ranking <= $numsql and
639  p.address = s.address
640order by
641  1 desc, s.address, p.piece
642";
643		global $ADODB_CACHE_MODE;
644  		if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
645			$partial = empty($_GET['part']);
646			echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
647		}
648		if (isset($_GET['sql'])) {
649			 $var = $this->_ExpensiveSQL($numsql);
650			 return $var;
651		}
652
653		$s = '';
654		$timer = time();
655		$s .= $this->_ExpensiveSQL($numsql);
656		$timer = time() - $timer;
657		if ($timer > $this->noShowIxora) return $s;
658
659		$s .= '<p>';
660		$save = $ADODB_CACHE_MODE;
661		$ADODB_CACHE_MODE = ADODB_FETCH_NUM;
662		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
663
664		$savelog = $this->conn->LogSQL(false);
665		$rs = $this->conn->Execute($sql);
666		$this->conn->LogSQL($savelog);
667
668		if (isset($savem)) $this->conn->SetFetchMode($savem);
669		$ADODB_CACHE_MODE = $save;
670
671		if ($rs) {
672			$s .= "\n<h3>Ixora Expensive SQL</h3>";
673			$s .= $this->tohtml($rs,'expeixora');
674		}
675
676		return $s;
677	}
678
679	function clearsql()
680	{
681		$perf_table = adodb_perf::table();
682	// using the naive "delete from $perf_table where created<".$this->conn->sysTimeStamp will cause the table to lock, possibly
683	// for a long time
684		$sql =
685"DECLARE cnt pls_integer;
686BEGIN
687	cnt := 0;
688	FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE created<SYSDATE)
689	LOOP
690	  cnt := cnt + 1;
691	  DELETE FROM $perf_table WHERE ROWID=rec.rr;
692	  IF cnt = 1000 THEN
693	  	COMMIT;
694		cnt := 0;
695	  END IF;
696	END LOOP;
697	commit;
698END;";
699
700		$ok = $this->conn->Execute($sql);
701	}
702
703}
704