lib/adodb/adodb-perf.inc.php

   1 <?php
   2 /*
   3 @version   v5.20.1  06-Dec-2015
   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   My apologies if you see code mixed with presentation. The presentation suits
  16   my needs. If you want to separate code from presentation, be my guest. Patches
  17   are welcome.
  18
  19 */
  20
  21 if (!defined('ADODB_DIR')) include_once(dirname(__FILE__).'/adodb.inc.php');
  22 include_once(ADODB_DIR.'/tohtml.inc.php');
  23
  24 define( 'ADODB_OPT_HIGH', 2);
  25 define( 'ADODB_OPT_LOW', 1);
  26
  27 global $ADODB_PERF_MIN;
  28 $ADODB_PERF_MIN = 0.05; // log only if >= minimum number of secs to run
  29
  30
  31 // returns in K the memory of current process, or 0 if not known
  32 function adodb_getmem()
  33 {
  34         if (function_exists('memory_get_usage'))
  35                 return (integer) ((memory_get_usage()+512)/1024);
  36
  37         $pid = getmypid();
  38
  39         if ( strncmp(strtoupper(PHP_OS),'WIN',3)==0) {
  40                 $output = array();
  41
  42                 exec('tasklist /FI "PID eq ' . $pid. '" /FO LIST', $output);
  43                 return substr($output[5], strpos($output[5], ':') + 1);
  44         }
  45
  46         /* Hopefully UNIX */
  47         exec("ps --pid $pid --no-headers -o%mem,size", $output);
  48         if (sizeof($output) == 0) return 0;
  49
  50         $memarr = explode(' ',$output[0]);
  51         if (sizeof($memarr)>=2) return (integer) $memarr[1];
  52
  53         return 0;
  54 }
  55
  56 // avoids localization problems where , is used instead of .
  57 function adodb_round($n,$prec)
  58 {
  59         return number_format($n, $prec, '.', '');
  60 }
  61
  62 /* obsolete: return microtime value as a float. Retained for backward compat */
  63 function adodb_microtime()
  64 {
  65         return microtime(true);
  66 }
  67
  68 /* sql code timing */
  69 function adodb_log_sql(&$connx,$sql,$inputarr)
  70 {
  71     $perf_table = adodb_perf::table();
  72         $connx->fnExecute = false;
  73         $a0 = microtime(true);
  74         $rs = $connx->Execute($sql,$inputarr);
  75         $a1 = microtime(true);
  76
  77         if (!empty($connx->_logsql) && (empty($connx->_logsqlErrors) || !$rs)) {
  78         global $ADODB_LOG_CONN;
  79
  80                 if (!empty($ADODB_LOG_CONN)) {
  81                         $conn = $ADODB_LOG_CONN;
  82                         if ($conn->databaseType != $connx->databaseType)
  83                                 $prefix = '/*dbx='.$connx->databaseType .'*/ ';
  84                         else
  85                                 $prefix = '';
  86                 } else {
  87                         $conn = $connx;
  88                         $prefix = '';
  89                 }
  90
  91                 $conn->_logsql = false; // disable logsql error simulation
  92                 $dbT = $conn->databaseType;
  93
  94                 $time = $a1 - $a0;
  95
  96                 if (!$rs) {
  97                         $errM = $connx->ErrorMsg();
  98                         $errN = $connx->ErrorNo();
  99                         $conn->lastInsID = 0;
 100                         $tracer = substr('ERROR: '.htmlspecialchars($errM),0,250);
 101                 } else {
 102                         $tracer = '';
 103                         $errM = '';
 104                         $errN = 0;
 105                         $dbg = $conn->debug;
 106                         $conn->debug = false;
 107                         if (!is_object($rs) || $rs->dataProvider == 'empty')
 108                                 $conn->_affected = $conn->affected_rows(true);
 109                         $conn->lastInsID = @$conn->Insert_ID();
 110                         $conn->debug = $dbg;
 111                 }
 112                 if (isset($_SERVER['HTTP_HOST'])) {
 113                         $tracer .= '<br>'.$_SERVER['HTTP_HOST'];
 114                         if (isset($_SERVER['PHP_SELF'])) $tracer .= htmlspecialchars($_SERVER['PHP_SELF']);
 115                 } else
 116                         if (isset($_SERVER['PHP_SELF'])) $tracer .= '<br>'.htmlspecialchars($_SERVER['PHP_SELF']);
 117                 //$tracer .= (string) adodb_backtrace(false);
 118
 119                 $tracer = (string) substr($tracer,0,500);
 120
 121                 if (is_array($inputarr)) {
 122                         if (is_array(reset($inputarr))) $params = 'Array sizeof='.sizeof($inputarr);
 123                         else {
 124                                 // Quote string parameters so we can see them in the
 125                                 // performance stats. This helps spot disabled indexes.
 126                                 $xar_params = $inputarr;
 127                                 foreach ($xar_params as $xar_param_key => $xar_param) {
 128                                         if (gettype($xar_param) == 'string')
 129                                         $xar_params[$xar_param_key] = '"' . $xar_param . '"';
 130                                 }
 131                                 $params = implode(', ', $xar_params);
 132                                 if (strlen($params) >= 3000) $params = substr($params, 0, 3000);
 133                         }
 134                 } else {
 135                         $params = '';
 136                 }
 137
 138                 if (is_array($sql)) $sql = $sql[0];
 139                 if ($prefix) $sql = $prefix.$sql;
 140                 $arr = array('b'=>strlen($sql).'.'.crc32($sql),
 141                                         'c'=>substr($sql,0,3900), 'd'=>$params,'e'=>$tracer,'f'=>adodb_round($time,6));
 142                 //var_dump($arr);
 143                 $saved = $conn->debug;
 144                 $conn->debug = 0;
 145
 146                 $d = $conn->sysTimeStamp;
 147                 if (empty($d)) $d = date("'Y-m-d H:i:s'");
 148                 if ($conn->dataProvider == 'oci8' && $dbT != 'oci8po') {
 149                         $isql = "insert into $perf_table values($d,:b,:c,:d,:e,:f)";
 150                 } else if ($dbT == 'odbc_mssql' || $dbT == 'informix' || strncmp($dbT,'odbtp',4)==0) {
 151                         $timer = $arr['f'];
 152                         if ($dbT == 'informix') $sql2 = substr($sql2,0,230);
 153
 154                         $sql1 = $conn->qstr($arr['b']);
 155                         $sql2 = $conn->qstr($arr['c']);
 156                         $params = $conn->qstr($arr['d']);
 157                         $tracer = $conn->qstr($arr['e']);
 158
 159                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values($d,$sql1,$sql2,$params,$tracer,$timer)";
 160                         if ($dbT == 'informix') $isql = str_replace(chr(10),' ',$isql);
 161                         $arr = false;
 162                 } else {
 163                         if ($dbT == 'db2') $arr['f'] = (float) $arr['f'];
 164                         $isql = "insert into $perf_table (created,sql0,sql1,params,tracer,timer) values( $d,?,?,?,?,?)";
 165                 }
 166
 167                 global $ADODB_PERF_MIN;
 168                 if ($errN != 0 || $time >= $ADODB_PERF_MIN) {
 169                         $ok = $conn->Execute($isql,$arr);
 170                 } else
 171                         $ok = true;
 172
 173                 $conn->debug = $saved;
 174
 175                 if ($ok) {
 176                         $conn->_logsql = true;
 177                 } else {
 178                         $err2 = $conn->ErrorMsg();
 179                         $conn->_logsql = true; // enable logsql error simulation
 180                         $perf = NewPerfMonitor($conn);
 181                         if ($perf) {
 182                                 if ($perf->CreateLogTable()) $ok = $conn->Execute($isql,$arr);
 183                         } else {
 184                                 $ok = $conn->Execute("create table $perf_table (
 185                                 created varchar(50),
 186                                 sql0 varchar(250),
 187                                 sql1 varchar(4000),
 188                                 params varchar(3000),
 189                                 tracer varchar(500),
 190                                 timer decimal(16,6))");
 191                         }
 192                         if (!$ok) {
 193                                 ADOConnection::outp( "<p><b>LOGSQL Insert Failed</b>: $isql<br>$err2</p>");
 194                                 $conn->_logsql = false;
 195                         }
 196                 }
 197                 $connx->_errorMsg = $errM;
 198                 $connx->_errorCode = $errN;
 199         }
 200         $connx->fnExecute = 'adodb_log_sql';
 201         return $rs;
 202 }
 203
 204
 205 /*
 206 The settings data structure is an associative array that database parameter per element.
 207
 208 Each database parameter element in the array is itself an array consisting of:
 209
 210 0: category code, used to group related db parameters
 211 1: either
 212         a. sql string to retrieve value, eg. "select value from v\$parameter where name='db_block_size'",
 213         b. array holding sql string and field to look for, e.g. array('show variables','table_cache'),
 214         c. a string prefixed by =, then a PHP method of the class is invoked,
 215                 e.g. to invoke $this->GetIndexValue(), set this array element to '=GetIndexValue',
 216 2: description of the database parameter
 217 */
 218
 219 class adodb_perf {
 220         var $conn;
 221         var $color = '#F0F0F0';
 222         var $table = '<table border=1 bgcolor=white>';
 223         var $titles = '<tr><td><b>Parameter</b></td><td><b>Value</b></td><td><b>Description</b></td></tr>';
 224         var $warnRatio = 90;
 225         var $tablesSQL = false;
 226         var $cliFormat = "%32s => %s \r\n";
 227         var $sql1 = 'sql1';  // used for casting sql1 to text for mssql
 228         var $explain = true;
 229         var $helpurl = '<a href="http://adodb.sourceforge.net/docs-adodb.htm#logsql">LogSQL help</a>';
 230         var $createTableSQL = false;
 231         var $maxLength = 2000;
 232
 233     // Sets the tablename to be used
 234     static function table($newtable = false)
 235     {
 236         static $_table;
 237
 238         if (!empty($newtable))  $_table = $newtable;
 239                 if (empty($_table)) $_table = 'adodb_logsql';
 240         return $_table;
 241     }
 242
 243         // returns array with info to calculate CPU Load
 244         function _CPULoad()
 245         {
 246 /*
 247
 248 cpu  524152 2662 2515228 336057010
 249 cpu0 264339 1408 1257951 168025827
 250 cpu1 259813 1254 1257277 168031181
 251 page 622307 25475680
 252 swap 24 1891
 253 intr 890153570 868093576 6 0 4 4 0 6 1 2 0 0 0 124 0 8098760 2 13961053 0 0 0 0 0 0 0 0 0 0 0 0 0 16 16 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
 254 disk_io: (3,0):(3144904,54369,610378,3090535,50936192) (3,1):(3630212,54097,633016,3576115,50951320)
 255 ctxt 66155838
 256 btime 1062315585
 257 processes 69293
 258
 259 */
 260                 // Algorithm is taken from
 261                 // http://social.technet.microsoft.com/Forums/en-US/winservergen/thread/414b0e1b-499c-411e-8a02-6a12e339c0f1/
 262                 if (strncmp(PHP_OS,'WIN',3)==0) {
 263                         if (PHP_VERSION == '5.0.0') return false;
 264                         if (PHP_VERSION == '5.0.1') return false;
 265                         if (PHP_VERSION == '5.0.2') return false;
 266                         if (PHP_VERSION == '5.0.3') return false;
 267                         if (PHP_VERSION == '4.3.10') return false; # see http://bugs.php.net/bug.php?id=31737
 268
 269                         static $FAIL = false;
 270                         if ($FAIL) return false;
 271
 272                         $objName = "winmgmts:{impersonationLevel=impersonate}!\\\\.\\root\\CIMV2";
 273                         $myQuery = "SELECT * FROM Win32_PerfFormattedData_PerfOS_Processor WHERE Name = '_Total'";
 274
 275                         try {
 276                                 @$objWMIService = new COM($objName);
 277                                 if (!$objWMIService) {
 278                                         $FAIL = true;
 279                                         return false;
 280                                 }
 281
 282                                 $info[0] = -1;
 283                                 $info[1] = 0;
 284                                 $info[2] = 0;
 285                                 $info[3] = 0;
 286                                 foreach($objWMIService->ExecQuery($myQuery) as $objItem)  {
 287                                                 $info[0] = $objItem->PercentProcessorTime();
 288                                 }
 289
 290                         } catch(Exception $e) {
 291                                 $FAIL = true;
 292                                 echo $e->getMessage();
 293                                 return false;
 294                         }
 295
 296                         return $info;
 297                 }
 298
 299                 // Algorithm - Steve Blinch (BlitzAffe Online, http://www.blitzaffe.com)
 300                 $statfile = '/proc/stat';
 301                 if (!file_exists($statfile)) return false;
 302
 303                 $fd = fopen($statfile,"r");
 304                 if (!$fd) return false;
 305
 306                 $statinfo = explode("\n",fgets($fd, 1024));
 307                 fclose($fd);
 308                 foreach($statinfo as $line) {
 309                         $info = explode(" ",$line);
 310                         if($info[0]=="cpu") {
 311                                 array_shift($info);  // pop off "cpu"
 312                                 if(!$info[0]) array_shift($info); // pop off blank space (if any)
 313                                 return $info;
 314                         }
 315                 }
 316
 317                 return false;
 318
 319         }
 320
 321         /* NOT IMPLEMENTED */
 322         function MemInfo()
 323         {
 324                 /*
 325
 326         total:    used:    free:  shared: buffers:  cached:
 327 Mem:  1055289344 917299200 137990144        0 165437440 599773184
 328 Swap: 2146775040 11055104 2135719936
 329 MemTotal:      1030556 kB
 330 MemFree:        134756 kB
 331 MemShared:           0 kB
 332 Buffers:        161560 kB
 333 Cached:         581384 kB
 334 SwapCached:       4332 kB
 335 Active:         494468 kB
 336 Inact_dirty:    322856 kB
 337 Inact_clean:     24256 kB
 338 Inact_target:   168316 kB
 339 HighTotal:      131064 kB
 340 HighFree:         1024 kB
 341 LowTotal:       899492 kB
 342 LowFree:        133732 kB
 343 SwapTotal:     2096460 kB
 344 SwapFree:      2085664 kB
 345 Committed_AS:   348732 kB
 346                 */
 347         }
 348
 349
 350         /*
 351                 Remember that this is client load, not db server load!
 352         */
 353         var $_lastLoad;
 354         function CPULoad()
 355         {
 356                 $info = $this->_CPULoad();
 357                 if (!$info) return false;
 358
 359                 if (strncmp(PHP_OS,'WIN',3)==0) {
 360                         return (integer) $info[0];
 361                 }else {
 362                         if (empty($this->_lastLoad)) {
 363                                 sleep(1);
 364                                 $this->_lastLoad = $info;
 365                                 $info = $this->_CPULoad();
 366                         }
 367
 368                         $last = $this->_lastLoad;
 369                         $this->_lastLoad = $info;
 370
 371                         $d_user = $info[0] - $last[0];
 372                         $d_nice = $info[1] - $last[1];
 373                         $d_system = $info[2] - $last[2];
 374                         $d_idle = $info[3] - $last[3];
 375
 376                         //printf("Delta - User: %f  Nice: %f  System: %f  Idle: %f<br>",$d_user,$d_nice,$d_system,$d_idle);
 377
 378                         $total=$d_user+$d_nice+$d_system+$d_idle;
 379                         if ($total<1) $total=1;
 380                         return 100*($d_user+$d_nice+$d_system)/$total;
 381                 }
 382         }
 383
 384         function Tracer($sql)
 385         {
 386         $perf_table = adodb_perf::table();
 387                 $saveE = $this->conn->fnExecute;
 388                 $this->conn->fnExecute = false;
 389
 390                 global $ADODB_FETCH_MODE;
 391                 $save = $ADODB_FETCH_MODE;
 392                 $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 393                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 394
 395                 $sqlq = $this->conn->qstr($sql);
 396                 $arr = $this->conn->GetArray(
 397 "select count(*),tracer
 398         from $perf_table where sql1=$sqlq
 399         group by tracer
 400         order by 1 desc");
 401                 $s = '';
 402                 if ($arr) {
 403                         $s .= '<h3>Scripts Affected</h3>';
 404                         foreach($arr as $k) {
 405                                 $s .= sprintf("%4d",$k[0]).' &nbsp; '.strip_tags($k[1]).'<br>';
 406                         }
 407                 }
 408
 409                 if (isset($savem)) $this->conn->SetFetchMode($savem);
 410                 $ADODB_CACHE_MODE = $save;
 411                 $this->conn->fnExecute = $saveE;
 412                 return $s;
 413         }
 414
 415         /*
 416                 Explain Plan for $sql.
 417                 If only a snippet of the $sql is passed in, then $partial will hold the crc32 of the
 418                         actual sql.
 419         */
 420         function Explain($sql,$partial=false)
 421         {
 422                 return false;
 423         }
 424
 425         function InvalidSQL($numsql = 10)
 426         {
 427
 428                 if (isset($_GET['sql'])) return;
 429                 $s = '<h3>Invalid SQL</h3>';
 430                 $saveE = $this->conn->fnExecute;
 431                 $this->conn->fnExecute = false;
 432         $perf_table = adodb_perf::table();
 433                 $rs = $this->conn->SelectLimit("select distinct count(*),sql1,tracer as error_msg from $perf_table where tracer like 'ERROR:%' group by sql1,tracer order by 1 desc",$numsql);//,$numsql);
 434                 $this->conn->fnExecute = $saveE;
 435                 if ($rs) {
 436                         $s .= rs2html($rs,false,false,false,false);
 437                 } else
 438                         return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
 439
 440                 return $s;
 441         }
 442
 443
 444         /*
 445                 This script identifies the longest running SQL
 446         */
 447         function _SuspiciousSQL($numsql = 10)
 448         {
 449                 global $ADODB_FETCH_MODE;
 450
 451             $perf_table = adodb_perf::table();
 452                         $saveE = $this->conn->fnExecute;
 453                         $this->conn->fnExecute = false;
 454
 455                         if (isset($_GET['exps']) && isset($_GET['sql'])) {
 456                                 $partial = !empty($_GET['part']);
 457                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
 458                         }
 459
 460                         if (isset($_GET['sql'])) return;
 461                         $sql1 = $this->sql1;
 462
 463                         $save = $ADODB_FETCH_MODE;
 464                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 465                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 466                         //$this->conn->debug=1;
 467                         $rs = $this->conn->SelectLimit(
 468                         "select avg(timer) as avg_timer,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
 469                                 from $perf_table
 470                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5)) not in ('DROP ','INSER','COMMI','CREAT')
 471                                 and (tracer is null or tracer not like 'ERROR:%')
 472                                 group by sql1
 473                                 order by 1 desc",$numsql);
 474                         if (isset($savem)) $this->conn->SetFetchMode($savem);
 475                         $ADODB_FETCH_MODE = $save;
 476                         $this->conn->fnExecute = $saveE;
 477
 478                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
 479                         $s = "<h3>Suspicious SQL</h3>
 480 <font size=1>The following SQL have high average execution times</font><br>
 481 <table border=1 bgcolor=white><tr><td><b>Avg Time</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n";
 482                         $max = $this->maxLength;
 483                         while (!$rs->EOF) {
 484                                 $sql = $rs->fields[1];
 485                                 $raw = urlencode($sql);
 486                                 if (strlen($raw)>$max-100) {
 487                                         $sql2 = substr($sql,0,$max-500);
 488                                         $raw = urlencode($sql2).'&part='.crc32($sql);
 489                                 }
 490                                 $prefix = "<a target=sql".rand()." href=\"?hidem=1&exps=1&sql=".$raw."&x#explain\">";
 491                                 $suffix = "</a>";
 492                                 if ($this->explain == false || strlen($prefix)>$max) {
 493                                         $suffix = ' ... <i>String too long for GET parameter: '.strlen($prefix).'</i>';
 494                                         $prefix = '';
 495                                 }
 496                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
 497                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
 498                                 $rs->MoveNext();
 499                         }
 500                         return $s."</table>";
 501
 502         }
 503
 504         function CheckMemory()
 505         {
 506                 return '';
 507         }
 508
 509
 510         function SuspiciousSQL($numsql=10)
 511         {
 512                 return adodb_perf::_SuspiciousSQL($numsql);
 513         }
 514
 515         function ExpensiveSQL($numsql=10)
 516         {
 517                 return adodb_perf::_ExpensiveSQL($numsql);
 518         }
 519
 520
 521         /*
 522                 This reports the percentage of load on the instance due to the most
 523                 expensive few SQL statements. Tuning these statements can often
 524                 make huge improvements in overall system performance.
 525         */
 526         function _ExpensiveSQL($numsql = 10)
 527         {
 528                 global $ADODB_FETCH_MODE;
 529
 530             $perf_table = adodb_perf::table();
 531                         $saveE = $this->conn->fnExecute;
 532                         $this->conn->fnExecute = false;
 533
 534                         if (isset($_GET['expe']) && isset($_GET['sql'])) {
 535                                 $partial = !empty($_GET['part']);
 536                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
 537                         }
 538
 539                         if (isset($_GET['sql'])) return;
 540
 541                         $sql1 = $this->sql1;
 542                         $save = $ADODB_FETCH_MODE;
 543                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 544                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 545
 546                         $rs = $this->conn->SelectLimit(
 547                         "select sum(timer) as total,$sql1,count(*),max(timer) as max_timer,min(timer) as min_timer
 548                                 from $perf_table
 549                                 where {$this->conn->upperCase}({$this->conn->substr}(sql0,1,5))  not in ('DROP ','INSER','COMMI','CREAT')
 550                                 and (tracer is null or tracer not like 'ERROR:%')
 551                                 group by sql1
 552                                 having count(*)>1
 553                                 order by 1 desc",$numsql);
 554                         if (isset($savem)) $this->conn->SetFetchMode($savem);
 555                         $this->conn->fnExecute = $saveE;
 556                         $ADODB_FETCH_MODE = $save;
 557                         if (!$rs) return "<p>$this->helpurl. ".$this->conn->ErrorMsg()."</p>";
 558                         $s = "<h3>Expensive SQL</h3>
 559 <font size=1>Tuning the following SQL could reduce the server load substantially</font><br>
 560 <table border=1 bgcolor=white><tr><td><b>Load</b><td><b>Count</b><td><b>SQL</b><td><b>Max</b><td><b>Min</b></tr>\n";
 561                         $max = $this->maxLength;
 562                         while (!$rs->EOF) {
 563                                 $sql = $rs->fields[1];
 564                                 $raw = urlencode($sql);
 565                                 if (strlen($raw)>$max-100) {
 566                                         $sql2 = substr($sql,0,$max-500);
 567                                         $raw = urlencode($sql2).'&part='.crc32($sql);
 568                                 }
 569                                 $prefix = "<a target=sqle".rand()." href=\"?hidem=1&expe=1&sql=".$raw."&x#explain\">";
 570                                 $suffix = "</a>";
 571                                 if($this->explain == false || strlen($prefix>$max)) {
 572                                         $prefix = '';
 573                                         $suffix = '';
 574                                 }
 575                                 $s .= "<tr><td>".adodb_round($rs->fields[0],6)."<td align=right>".$rs->fields[2]."<td><font size=-1>".$prefix.htmlspecialchars($sql).$suffix."</font>".
 576                                         "<td>".$rs->fields[3]."<td>".$rs->fields[4]."</tr>";
 577                                 $rs->MoveNext();
 578                         }
 579                         return $s."</table>";
 580         }
 581
 582         /*
 583                 Raw function to return parameter value from $settings.
 584         */
 585         function DBParameter($param)
 586         {
 587                 if (empty($this->settings[$param])) return false;
 588                 $sql = $this->settings[$param][1];
 589                 return $this->_DBParameter($sql);
 590         }
 591
 592         /*
 593                 Raw function returning array of poll paramters
 594         */
 595         function PollParameters()
 596         {
 597                 $arr[0] = (float)$this->DBParameter('data cache hit ratio');
 598                 $arr[1] = (float)$this->DBParameter('data reads');
 599                 $arr[2] = (float)$this->DBParameter('data writes');
 600                 $arr[3] = (integer) $this->DBParameter('current connections');
 601                 return $arr;
 602         }
 603
 604         /*
 605                 Low-level Get Database Parameter
 606         */
 607         function _DBParameter($sql)
 608         {
 609                 $savelog = $this->conn->LogSQL(false);
 610                 if (is_array($sql)) {
 611                 global $ADODB_FETCH_MODE;
 612
 613                         $sql1 = $sql[0];
 614                         $key = $sql[1];
 615                         if (sizeof($sql)>2) $pos = $sql[2];
 616                         else $pos = 1;
 617                         if (sizeof($sql)>3) $coef = $sql[3];
 618                         else $coef = false;
 619                         $ret = false;
 620                         $save = $ADODB_FETCH_MODE;
 621                         $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 622                         if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 623
 624                         $rs = $this->conn->Execute($sql1);
 625
 626                         if (isset($savem)) $this->conn->SetFetchMode($savem);
 627                         $ADODB_FETCH_MODE = $save;
 628                         if ($rs) {
 629                                 while (!$rs->EOF) {
 630                                         $keyf = reset($rs->fields);
 631                                         if (trim($keyf) == $key) {
 632                                                 $ret = $rs->fields[$pos];
 633                                                 if ($coef) $ret *= $coef;
 634                                                 break;
 635                                         }
 636                                         $rs->MoveNext();
 637                                 }
 638                                 $rs->Close();
 639                         }
 640                         $this->conn->LogSQL($savelog);
 641                         return $ret;
 642                 } else {
 643                         if (strncmp($sql,'=',1) == 0) {
 644                                 $fn = substr($sql,1);
 645                                 return $this->$fn();
 646                         }
 647                         $sql = str_replace('$DATABASE',$this->conn->database,$sql);
 648                         $ret = $this->conn->GetOne($sql);
 649                         $this->conn->LogSQL($savelog);
 650
 651                         return $ret;
 652                 }
 653         }
 654
 655         /*
 656                 Warn if cache ratio falls below threshold. Displayed in "Description" column.
 657         */
 658         function WarnCacheRatio($val)
 659         {
 660                 if ($val < $this->warnRatio)
 661                          return '<font color=red><b>Cache ratio should be at least '.$this->warnRatio.'%</b></font>';
 662                 else return '';
 663         }
 664
 665         function clearsql()
 666         {
 667                 $perf_table = adodb_perf::table();
 668                 $this->conn->Execute("delete from $perf_table where created<".$this->conn->sysTimeStamp);
 669         }
 670         /***********************************************************************************************/
 671         //                                    HIGH LEVEL UI FUNCTIONS
 672         /***********************************************************************************************/
 673
 674
 675         function UI($pollsecs=5)
 676         {
 677         global $ADODB_LOG_CONN;
 678
 679     $perf_table = adodb_perf::table();
 680         $conn = $this->conn;
 681
 682         $app = $conn->host;
 683         if ($conn->host && $conn->database) $app .= ', db=';
 684         $app .= $conn->database;
 685
 686         if ($app) $app .= ', ';
 687         $savelog = $this->conn->LogSQL(false);
 688         $info = $conn->ServerInfo();
 689         if (isset($_GET['clearsql'])) {
 690                 $this->clearsql();
 691         }
 692         $this->conn->LogSQL($savelog);
 693
 694         // magic quotes
 695
 696         if (isset($_GET['sql']) && get_magic_quotes_gpc()) {
 697                 $_GET['sql'] = $_GET['sql'] = str_replace(array("\\'",'\"'),array("'",'"'),$_GET['sql']);
 698         }
 699
 700         if (!isset($_SESSION['ADODB_PERF_SQL'])) $nsql = $_SESSION['ADODB_PERF_SQL'] = 10;
 701         else  $nsql = $_SESSION['ADODB_PERF_SQL'];
 702
 703         $app .= $info['description'];
 704
 705
 706         if (isset($_GET['do'])) $do = $_GET['do'];
 707         else if (isset($_POST['do'])) $do = $_POST['do'];
 708          else if (isset($_GET['sql'])) $do = 'viewsql';
 709          else $do = 'stats';
 710
 711         if (isset($_GET['nsql'])) {
 712                 if ($_GET['nsql'] > 0) $nsql = $_SESSION['ADODB_PERF_SQL'] = (integer) $_GET['nsql'];
 713         }
 714         echo "<title>ADOdb Performance Monitor on $app</title><body bgcolor=white>";
 715         if ($do == 'viewsql') $form = "<td><form># SQL:<input type=hidden value=viewsql name=do> <input type=text size=4 name=nsql value=$nsql><input type=submit value=Go></td></form>";
 716         else $form = "<td>&nbsp;</td>";
 717
 718         $allowsql = !defined('ADODB_PERF_NO_RUN_SQL');
 719         global $ADODB_PERF_MIN;
 720         $app .= " (Min sql timing \$ADODB_PERF_MIN=$ADODB_PERF_MIN secs)";
 721
 722         if  (empty($_GET['hidem']))
 723         echo "<table border=1 width=100% bgcolor=lightyellow><tr><td colspan=2>
 724         <b><a href=http://adodb.sourceforge.net/?perf=1>ADOdb</a> Performance Monitor</b> <font size=1>for $app</font></tr><tr><td>
 725         <a href=?do=stats><b>Performance Stats</b></a> &nbsp; <a href=?do=viewsql><b>View SQL</b></a>
 726          &nbsp; <a href=?do=tables><b>View Tables</b></a> &nbsp; <a href=?do=poll><b>Poll Stats</b></a>",
 727          $allowsql ? ' &nbsp; <a href=?do=dosql><b>Run SQL</b></a>' : '',
 728          "$form",
 729          "</tr></table>";
 730
 731
 732                 switch ($do) {
 733                 default:
 734                 case 'stats':
 735                         if (empty($ADODB_LOG_CONN))
 736                                 echo "<p>&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
 737                         echo $this->HealthCheck();
 738                         //$this->conn->debug=1;
 739                         echo $this->CheckMemory();
 740                         break;
 741                 case 'poll':
 742                         $self = htmlspecialchars($_SERVER['PHP_SELF']);
 743                         echo "<iframe width=720 height=80%
 744                                 src=\"{$self}?do=poll2&hidem=1\"></iframe>";
 745                         break;
 746                 case 'poll2':
 747                         echo "<pre>";
 748                         $this->Poll($pollsecs);
 749                         break;
 750
 751                 case 'dosql':
 752                         if (!$allowsql) break;
 753
 754                         $this->DoSQLForm();
 755                         break;
 756                 case 'viewsql':
 757                         if (empty($_GET['hidem']))
 758                                 echo "&nbsp; <a href=\"?do=viewsql&clearsql=1\">Clear SQL Log</a><br>";
 759                         echo($this->SuspiciousSQL($nsql));
 760                         echo($this->ExpensiveSQL($nsql));
 761                         echo($this->InvalidSQL($nsql));
 762                         break;
 763                 case 'tables':
 764                         echo $this->Tables(); break;
 765                 }
 766                 global $ADODB_vers;
 767                 echo "<p><div align=center><font size=1>$ADODB_vers Sponsored by <a href=http://phplens.com/>phpLens</a></font></div>";
 768         }
 769
 770         /*
 771                 Runs in infinite loop, returning real-time statistics
 772         */
 773         function Poll($secs=5)
 774         {
 775                 $this->conn->fnExecute = false;
 776                 //$this->conn->debug=1;
 777                 if ($secs <= 1) $secs = 1;
 778                 echo "Accumulating statistics, every $secs seconds...\n";flush();
 779                 $arro = $this->PollParameters();
 780                 $cnt = 0;
 781                 set_time_limit(0);
 782                 sleep($secs);
 783                 while (1) {
 784
 785                         $arr = $this->PollParameters();
 786
 787                         $hits   = sprintf('%2.2f',$arr[0]);
 788                         $reads  = sprintf('%12.4f',($arr[1]-$arro[1])/$secs);
 789                         $writes = sprintf('%12.4f',($arr[2]-$arro[2])/$secs);
 790                         $sess = sprintf('%5d',$arr[3]);
 791
 792                         $load = $this->CPULoad();
 793                         if ($load !== false) {
 794                                 $oslabel = 'WS-CPU%';
 795                                 $osval = sprintf(" %2.1f  ",(float) $load);
 796                         }else {
 797                                 $oslabel = '';
 798                                 $osval = '';
 799                         }
 800                         if ($cnt % 10 == 0) echo " Time   ".$oslabel."   Hit%   Sess           Reads/s          Writes/s\n";
 801                         $cnt += 1;
 802                         echo date('H:i:s').'  '.$osval."$hits  $sess $reads $writes\n";
 803                         flush();
 804
 805                         if (connection_aborted()) return;
 806
 807                         sleep($secs);
 808                         $arro = $arr;
 809                 }
 810         }
 811
 812         /*
 813                 Returns basic health check in a command line interface
 814         */
 815         function HealthCheckCLI()
 816         {
 817                 return $this->HealthCheck(true);
 818         }
 819
 820
 821         /*
 822                 Returns basic health check as HTML
 823         */
 824         function HealthCheck($cli=false)
 825         {
 826                 $saveE = $this->conn->fnExecute;
 827                 $this->conn->fnExecute = false;
 828                 if ($cli) $html = '';
 829                 else $html = $this->table.'<tr><td colspan=3><h3>'.$this->conn->databaseType.'</h3></td></tr>'.$this->titles;
 830
 831                 $oldc = false;
 832                 $bgc = '';
 833                 foreach($this->settings as $name => $arr) {
 834                         if ($arr === false) break;
 835
 836                         if (!is_string($name)) {
 837                                 if ($cli) $html .= " -- $arr -- \n";
 838                                 else $html .= "<tr bgcolor=$this->color><td colspan=3><i>$arr</i> &nbsp;</td></tr>";
 839                                 continue;
 840                         }
 841
 842                         if (!is_array($arr)) break;
 843                         $category = $arr[0];
 844                         $how = $arr[1];
 845                         if (sizeof($arr)>2) $desc = $arr[2];
 846                         else $desc = ' &nbsp; ';
 847
 848
 849                         if ($category == 'HIDE') continue;
 850
 851                         $val = $this->_DBParameter($how);
 852
 853                         if ($desc && strncmp($desc,"=",1) === 0) {
 854                                 $fn = substr($desc,1);
 855                                 $desc = $this->$fn($val);
 856                         }
 857
 858                         if ($val === false) {
 859                                 $m = $this->conn->ErrorMsg();
 860                                 $val = "Error: $m";
 861                         } else {
 862                                 if (is_numeric($val) && $val >= 256*1024) {
 863                                         if ($val % (1024*1024) == 0) {
 864                                                 $val /= (1024*1024);
 865                                                 $val .= 'M';
 866                                         } else if ($val % 1024 == 0) {
 867                                                 $val /= 1024;
 868                                                 $val .= 'K';
 869                                         }
 870                                         //$val = htmlspecialchars($val);
 871                                 }
 872                         }
 873                         if ($category != $oldc) {
 874                                 $oldc = $category;
 875                                 //$bgc = ($bgc == ' bgcolor='.$this->color) ? ' bgcolor=white' : ' bgcolor='.$this->color;
 876                         }
 877                         if (strlen($desc)==0) $desc = '&nbsp;';
 878                         if (strlen($val)==0) $val = '&nbsp;';
 879                         if ($cli) {
 880                                 $html  .= str_replace('&nbsp;','',sprintf($this->cliFormat,strip_tags($name),strip_tags($val),strip_tags($desc)));
 881
 882                         }else {
 883                                 $html .= "<tr$bgc><td>".$name.'</td><td>'.$val.'</td><td>'.$desc."</td></tr>\n";
 884                         }
 885                 }
 886
 887                 if (!$cli) $html .= "</table>\n";
 888                 $this->conn->fnExecute = $saveE;
 889
 890                 return $html;
 891         }
 892
 893         function Tables($orderby='1')
 894         {
 895                 if (!$this->tablesSQL) return false;
 896
 897                 $savelog = $this->conn->LogSQL(false);
 898                 $rs = $this->conn->Execute($this->tablesSQL.' order by '.$orderby);
 899                 $this->conn->LogSQL($savelog);
 900                 $html = rs2html($rs,false,false,false,false);
 901                 return $html;
 902         }
 903
 904
 905         function CreateLogTable()
 906         {
 907                 if (!$this->createTableSQL) return false;
 908
 909                 $table = $this->table();
 910                 $sql = str_replace('adodb_logsql',$table,$this->createTableSQL);
 911                 $savelog = $this->conn->LogSQL(false);
 912                 $ok = $this->conn->Execute($sql);
 913                 $this->conn->LogSQL($savelog);
 914                 return ($ok) ? true : false;
 915         }
 916
 917         function DoSQLForm()
 918         {
 919
 920
 921                 $PHP_SELF = htmlspecialchars($_SERVER['PHP_SELF']);
 922                 $sql = isset($_REQUEST['sql']) ? $_REQUEST['sql'] : '';
 923
 924                 if (isset($_SESSION['phplens_sqlrows'])) $rows = $_SESSION['phplens_sqlrows'];
 925                 else $rows = 3;
 926
 927                 if (isset($_REQUEST['SMALLER'])) {
 928                         $rows /= 2;
 929                         if ($rows < 3) $rows = 3;
 930                         $_SESSION['phplens_sqlrows'] = $rows;
 931                 }
 932                 if (isset($_REQUEST['BIGGER'])) {
 933                         $rows *= 2;
 934                         $_SESSION['phplens_sqlrows'] = $rows;
 935                 }
 936
 937 ?>
 938
 939 <form method="POST" action="<?php echo $PHP_SELF ?>">
 940 <table><tr>
 941 <td> Form size: <input type="submit" value=" &lt; " name="SMALLER"><input type="submit" value=" &gt; &gt; " name="BIGGER">
 942 </td>
 943 <td align=right>
 944 <input type="submit" value=" Run SQL Below " name="RUN"><input type=hidden name=do value=dosql>
 945 </td></tr>
 946   <tr>
 947   <td colspan=2><textarea rows=<?php print $rows; ?> name="sql" cols="80"><?php print htmlspecialchars($sql) ?></textarea>
 948   </td>
 949   </tr>
 950  </table>
 951 </form>
 952
 953 <?php
 954                 if (!isset($_REQUEST['sql'])) return;
 955
 956                 $sql = $this->undomq(trim($sql));
 957                 if (substr($sql,strlen($sql)-1) === ';') {
 958                         $print = true;
 959                         $sqla = $this->SplitSQL($sql);
 960                 } else  {
 961                         $print = false;
 962                         $sqla = array($sql);
 963                 }
 964                 foreach($sqla as $sqls) {
 965
 966                         if (!$sqls) continue;
 967
 968                         if ($print) {
 969                                 print "<p>".htmlspecialchars($sqls)."</p>";
 970                                 flush();
 971                         }
 972                         $savelog = $this->conn->LogSQL(false);
 973                         $rs = $this->conn->Execute($sqls);
 974                         $this->conn->LogSQL($savelog);
 975                         if ($rs && is_object($rs) && !$rs->EOF) {
 976                                 rs2html($rs);
 977                                 while ($rs->NextRecordSet()) {
 978                                         print "<table width=98% bgcolor=#C0C0FF><tr><td>&nbsp;</td></tr></table>";
 979                                         rs2html($rs);
 980                                 }
 981                         } else {
 982                                 $e1 = (integer) $this->conn->ErrorNo();
 983                                 $e2 = $this->conn->ErrorMsg();
 984                                 if (($e1) || ($e2)) {
 985                                         if (empty($e1)) $e1 = '-1'; // postgresql fix
 986                                         print ' &nbsp; '.$e1.': '.$e2;
 987                                 } else {
 988                                         print "<p>No Recordset returned<br></p>";
 989                                 }
 990                         }
 991                 } // foreach
 992         }
 993
 994         function SplitSQL($sql)
 995         {
 996                 $arr = explode(';',$sql);
 997                 return $arr;
 998         }
 999
1000         function undomq($m)
1001         {
1002         if (get_magic_quotes_gpc()) {
1003                 // undo the damage
1004                 $m = str_replace('\\\\','\\',$m);
1005                 $m = str_replace('\"','"',$m);
1006                 $m = str_replace('\\\'','\'',$m);
1007         }
1008         return $m;
1009 }
1010
1011
1012    /************************************************************************/
1013
1014     /**
1015      * Reorganise multiple table-indices/statistics/..
1016      * OptimizeMode could be given by last Parameter
1017      *
1018      * @example
1019      *      <pre>
1020      *          optimizeTables( 'tableA');
1021      *      </pre>
1022      *      <pre>
1023      *          optimizeTables( 'tableA', 'tableB', 'tableC');
1024      *      </pre>
1025      *      <pre>
1026      *          optimizeTables( 'tableA', 'tableB', ADODB_OPT_LOW);
1027      *      </pre>
1028      *
1029      * @param string table name of the table to optimize
1030      * @param int mode optimization-mode
1031      *      <code>ADODB_OPT_HIGH</code> for full optimization
1032      *      <code>ADODB_OPT_LOW</code> for CPU-less optimization
1033      *      Default is LOW <code>ADODB_OPT_LOW</code>
1034      * @author Markus Staab
1035      * @return Returns <code>true</code> on success and <code>false</code> on error
1036      */
1037     function OptimizeTables()
1038     {
1039         $args = func_get_args();
1040         $numArgs = func_num_args();
1041
1042         if ( $numArgs == 0) return false;
1043
1044         $mode = ADODB_OPT_LOW;
1045         $lastArg = $args[ $numArgs - 1];
1046         if ( !is_string($lastArg)) {
1047             $mode = $lastArg;
1048             unset( $args[ $numArgs - 1]);
1049         }
1050
1051         foreach( $args as $table) {
1052             $this->optimizeTable( $table, $mode);
1053         }
1054         }
1055
1056     /**
1057      * Reorganise the table-indices/statistics/.. depending on the given mode.
1058      * Default Implementation throws an error.
1059      *
1060      * @param string table name of the table to optimize
1061      * @param int mode optimization-mode
1062      *      <code>ADODB_OPT_HIGH</code> for full optimization
1063      *      <code>ADODB_OPT_LOW</code> for CPU-less optimization
1064      *      Default is LOW <code>ADODB_OPT_LOW</code>
1065      * @author Markus Staab
1066      * @return Returns <code>true</code> on success and <code>false</code> on error
1067      */
1068     function OptimizeTable( $table, $mode = ADODB_OPT_LOW)
1069     {
1070         ADOConnection::outp( sprintf( "<p>%s: '%s' not implemented for driver '%s'</p>", __CLASS__, __FUNCTION__, $this->conn->databaseType));
1071         return false;
1072     }
1073
1074     /**
1075      * Reorganise current database.
1076      * Default implementation loops over all <code>MetaTables()</code> and
1077      * optimize each using <code>optmizeTable()</code>
1078      *
1079      * @author Markus Staab
1080      * @return Returns <code>true</code> on success and <code>false</code> on error
1081      */
1082     function optimizeDatabase()
1083     {
1084         $conn = $this->conn;
1085         if ( !$conn) return false;
1086
1087         $tables = $conn->MetaTables( 'TABLES');
1088         if ( !$tables ) return false;
1089
1090         foreach( $tables as $table) {
1091             if ( !$this->optimizeTable( $table)) {
1092                 return false;
1093             }
1094         }
1095
1096         return true;
1097     }
1098     // end hack
1099 }