Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Annotate
Revision Log

perf-oci8.inc.php @ 8222

Revision 8222, 23.9 KB checked in by angelo, 11 years ago (diff)
Ticket #3491 - Compatibilizar Expresso com novas versoes do PHP
Property svn:eol-style set to `native` Property svn:executable set to ``*

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

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sandbox/2.5.1-evolucao/phpgwapi/inc/adodb/perf/perf-oci8.inc.php @ 8222

Download in other formats: