TYPO3 CMS  TYPO3_6-2
•All Classes Namespaces Files Functions Variables Pages
perf-oci8.inc.php
Go to the documentation of this file.
1 <?php
2 /*
3 V5.19 23-Apr-2014 (c) 2000-2014 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 }
perf_oci8\ExpensiveSQL
ExpensiveSQL($numsql=10)
Definition: perf-oci8.inc.php:604
perf_oci8\Explain
Explain($sql, $partial=false)
Definition: perf-oci8.inc.php:370
perf_oci8\$version
$version
Definition: perf-oci8.inc.php:26
perf_oci8\PGA_Advice
PGA_Advice()
Definition: perf-oci8.inc.php:343
$sql
$sql
Definition: server.php:82
perf_oci8\TableSpace
TableSpace()
Definition: perf-oci8.inc.php:273
perf_oci8\perf_oci8
perf_oci8(&$conn)
Definition: perf-oci8.inc.php:201
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:92
adodb_perf\table
static table($newtable=false)
Definition: adodb-perf.inc.php:232
perf_oci8\LogMode
LogMode()
Definition: perf-oci8.inc.php:212
$gSQLBlockRows
GLOBAL $gSQLBlockRows
Definition: tohtml.inc.php:12
perf_oci8\RMAN
RMAN()
Definition: perf-oci8.inc.php:289
perf_oci8\$createTableSQL
$createTableSQL
Definition: perf-oci8.inc.php:28
perf_oci8\FlashUsage
FlashUsage()
Definition: perf-oci8.inc.php:312
$conn
$conn
Definition: server.php:79
perf_oci8\TopHistoricalWaits
TopHistoricalWaits()
Definition: perf-oci8.inc.php:251
die
die
Definition: index.php:6
perf_oci8\clearsql
clearsql()
Definition: perf-oci8.inc.php:677
perf_oci8\PGA
PGA()
Definition: perf-oci8.inc.php:337
perf_oci8\CheckMemory
CheckMemory()
Definition: perf-oci8.inc.php:449
perf_oci8\$noShowIxora
$noShowIxora
Definition: perf-oci8.inc.php:21
perf_oci8
Definition: perf-oci8.inc.php:19
rs2html
rs2html(&$rs, $ztabhtml=false, $zheaderarray=false, $htmlspecialchars=true, $echo=true)
Definition: tohtml.inc.php:41
perf_oci8\tohtml
tohtml(&$rs, $type)
Definition: perf-oci8.inc.php:484
perf_oci8\TopRecentWaits
TopRecentWaits()
Definition: perf-oci8.inc.php:238
perf_oci8\SuspiciousSQL
SuspiciousSQL($numsql=10)
Definition: perf-oci8.inc.php:530
perf_oci8\WarnIndexCost
WarnIndexCost($val)
Definition: perf-oci8.inc.php:327
perf_oci8\DynMemoryUsage
DynMemoryUsage()
Definition: perf-oci8.inc.php:299
perf_oci8\WarnPageCost
WarnPageCost($val)
Definition: perf-oci8.inc.php:319
perf_oci8\$tablesSQL
$tablesSQL
Definition: perf-oci8.inc.php:23
perf_oci8\$settings
$settings
Definition: perf-oci8.inc.php:37