vendor/adodb/adodb-php/perf/perf-oci8.inc.php

   1 <?php
   2 /*
   3 @version   v5.20.9  21-Dec-2016
   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.sourceforge.net
  12
  13   Library for basic performance monitoring and tuning
  14
  15 */
  16
  17 // security - hide paths
  18 if (!defined('ADODB_DIR')) die();
  19
  20
  21 class 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)
  63 FROM   v\$sysstat a,
  64        v\$sysstat b
  65 WHERE  a.name = 'sorts (disk)'
  66 AND    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
 172 having 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)
 187 FROM 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
 262 FROM    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
 265 WHERE   A.SAMPLE_TIME BETWEEN sysdate-$days and sysdate
 266        and parsing_schema_name not in ('SYS','SYSMAN','DBSNMP','SYSTEM')
 267 GROUP BY b.wait_class,parsing_schema_name,C.SQL_TEXT, B.NAME,A.sql_id
 268 order 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>
 379 CREATE 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
 441 START WITH id = 0  and STATEMENT_ID='$id'
 442 CONNECT 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("
 456 select  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 = "
 535 select
 536   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
 537   s.executions  executes,
 538   p.sql_text
 539 from
 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
 563 where
 564   s.ranking <= $numsql and
 565   p.address = s.address
 566 order 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 = "
 609 select
 610   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
 611   s.executions  executes,
 612   p.sql_text
 613 from
 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
 637 where
 638   s.ranking <= $numsql and
 639   p.address = s.address
 640 order 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;
 686 BEGIN
 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;
 698 END;";
 699
 700                 $ok = $this->conn->Execute($sql);
 701         }
 702
 703 }