[TASK] Update ADOdb to 5.18
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / perf / perf-oci8.inc.php
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 "&nbsp;<p>".$ret."&nbsp;</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 "&nbsp;<p>".$ret."&nbsp;</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 "&nbsp;<p>".$ret."&nbsp;</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 "&nbsp;<p>".$ret."&nbsp;</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 "&nbsp;<p>".$ret."&nbsp;</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 "&nbsp;<p>".$ret."&nbsp;</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 '&lt;&lt;= 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 ?>