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

   1 <?php
   2 /*
   3 V4.94 23 Jan 2007  (c) 2000-2007 John Lim (jlim#natsoft.com.my). 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 class perf_oci8 extends ADODB_perf{
  19
  20         var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
  21            group by segment_name,tablespace_name";
  22
  23         var $version;
  24         var $createTableSQL = "CREATE TABLE adodb_logsql (
  25                   created date NOT NULL,
  26                   sql0 varchar(250) NOT NULL,
  27                   sql1 varchar(4000) NOT NULL,
  28                   params varchar(4000),
  29                   tracer varchar(4000),
  30                   timer decimal(16,6) NOT NULL
  31                 )";
  32
  33         var $settings = array(
  34         'Ratios',
  35                 'data cache hit ratio' => array('RATIOH',
  36                         "select round((1-(phy.value / (cur.value + con.value)))*100,2)
  37                         from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
  38                         where cur.name = 'db block gets' and
  39                               con.name = 'consistent gets' and
  40                               phy.name = 'physical reads'",
  41                         '=WarnCacheRatio'),
  42
  43                 'sql cache hit ratio' => array( 'RATIOH',
  44                         'select round(100*(sum(pins)-sum(reloads))/sum(pins),2)  from v$librarycache',
  45                         'increase <i>shared_pool_size</i> if too ratio low'),
  46
  47                 'datadict cache hit ratio' => array('RATIOH',
  48                 "select
  49            round((1 - (sum(getmisses) / (sum(gets) +
  50                  sum(getmisses))))*100,2)
  51                 from  v\$rowcache",
  52                 'increase <i>shared_pool_size</i> if too ratio low'),
  53
  54                 'memory sort ratio' => array('RATIOH',
  55                 "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
  56        0,1,(a.VALUE + b.VALUE)),2)
  57 FROM   v\$sysstat a,
  58        v\$sysstat b
  59 WHERE  a.name = 'sorts (disk)'
  60 AND    b.name = 'sorts (memory)'",
  61         "% of memory sorts compared to disk sorts - should be over 95%"),
  62
  63         'IO',
  64                 'data reads' => array('IO',
  65                 "select value from v\$sysstat where name='physical reads'"),
  66
  67         'data writes' => array('IO',
  68                 "select value from v\$sysstat where name='physical writes'"),
  69
  70         'Data Cache',
  71                 'data cache buffers' => array( 'DATAC',
  72                 "select a.value/b.value  from v\$parameter a, v\$parameter b
  73                         where a.name = 'db_cache_size' and b.name= 'db_block_size'",
  74                         'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
  75                 'data cache blocksize' => array('DATAC',
  76                         "select value from v\$parameter where name='db_block_size'",
  77                         '' ),
  78         'Memory Pools',
  79                 'data cache size' => array('DATAC',
  80                         "select value from v\$parameter where name = 'db_cache_size'",
  81                         'db_cache_size' ),
  82                 'shared pool size' => array('DATAC',
  83                         "select value from v\$parameter where name = 'shared_pool_size'",
  84                         'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
  85                 'java pool size' => array('DATAJ',
  86                         "select value from v\$parameter where name = 'java_pool_size'",
  87                         'java_pool_size' ),
  88                 'large pool buffer size' => array('CACHE',
  89                         "select value from v\$parameter where name='large_pool_size'",
  90                         '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) ' ),
  91
  92                 'pga buffer size' => array('CACHE',
  93                         "select value from v\$parameter where name='pga_aggregate_target'",
  94                         'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ),
  95
  96
  97                 'Connections',
  98                 'current connections' => array('SESS',
  99                         'select count(*) from sys.v_$session where username is not null',
 100                         ''),
 101                 'max connections' => array( 'SESS',
 102                         "select value from v\$parameter where name='sessions'",
 103                         ''),
 104
 105         'Memory Utilization',
 106                 'data cache utilization ratio' => array('RATIOU',
 107                         "select round((1-bytes/sgasize)*100, 2)
 108                         from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
 109                         where name = 'free memory' and pool = 'shared pool'",
 110                 'Percentage of data cache actually in use - should be over 85%'),
 111
 112                                 'shared pool utilization ratio' => array('RATIOU',
 113                 'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2)
 114                 from v$sgastat sga, v$parameter p
 115                 where sga.name = \'free memory\' and sga.pool = \'shared pool\'
 116                 and p.name = \'shared_pool_size\'',
 117                 'Percentage of shared pool actually used - too low is bad, too high is worse'),
 118
 119                 'large pool utilization ratio' => array('RATIOU',
 120                         "select round((1-bytes/sgasize)*100, 2)
 121                         from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
 122                         where name = 'free memory' and pool = 'large pool'",
 123                 'Percentage of large_pool actually in use - too low is bad, too high is worse'),
 124                 'sort buffer size' => array('CACHE',
 125                         "select value from v\$parameter where name='sort_area_size'",
 126                         'max in-mem sort_area_size (per query), uses memory in pga' ),
 127
 128                 'pga usage at peak' => array('RATIOU',
 129                 '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),
 130         'Transactions',
 131                 'rollback segments' => array('ROLLBACK',
 132                         "select count(*) from sys.v_\$rollstat",
 133                         ''),
 134
 135                 'peak transactions' => array('ROLLBACK',
 136                         "select max_utilization  tx_hwm
 137                 from sys.v_\$resource_limit
 138                 where resource_name = 'transactions'",
 139                         'Taken from high-water-mark'),
 140                 'max transactions' => array('ROLLBACK',
 141                         "select value from v\$parameter where name = 'transactions'",
 142                         'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
 143         'Parameters',
 144                 'cursor sharing' => array('CURSOR',
 145                         "select value from v\$parameter where name = 'cursor_sharing'",
 146                         '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>.'),
 147                 /*
 148                 'cursor reuse' => array('CURSOR',
 149                         "select count(*) from (select sql_text_wo_constants, count(*)
 150   from t1
 151  group by sql_text_wo_constants
 152 having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
 153                 'index cache cost' => array('COST',
 154                         "select value from v\$parameter where name = 'optimizer_index_caching'",
 155                         '=WarnIndexCost'),
 156                 'random page cost' => array('COST',
 157                         "select value from v\$parameter where name = 'optimizer_index_cost_adj'",
 158                         '=WarnPageCost'),
 159
 160                 false
 161
 162         );
 163
 164
 165         function perf_oci8(&$conn)
 166         {
 167                 $savelog = $conn->LogSQL(false);
 168                 $this->version = $conn->ServerInfo();
 169                 $conn->LogSQL($savelog);
 170                 $this->conn =& $conn;
 171         }
 172
 173         function WarnPageCost($val)
 174         {
 175                 if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>';
 176                 else $s = '';
 177
 178                 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>. ';
 179         }
 180
 181         function WarnIndexCost($val)
 182         {
 183                 if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>';
 184                 else $s = '';
 185
 186                 return $s.'Percentage of indexed data blocks expected in the cache.
 187                         Recommended is 20 (fast disk array) to 50 (slower hard disks). Default is 0.
 188                          See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
 189                 }
 190
 191         function PGA()
 192         {
 193                 if ($this->version['version'] < 9) return 'Oracle 9i or later required';
 194
 195                 $rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from
 196            (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
 197                    pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
 198                    from v\$pga_target_advice) a left join
 199            (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
 200                    pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
 201                    from v\$pga_target_advice) b on
 202           a.r = b.r+1 where
 203                 b.pct < 100");
 204                 if (!$rs) return "Only in 9i or later";
 205                 $rs->Close();
 206                 if ($rs->EOF) return "PGA could be too big";
 207
 208                 return reset($rs->fields);
 209         }
 210
 211         function Explain($sql,$partial=false)
 212         {
 213                 $savelog = $this->conn->LogSQL(false);
 214                 $rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
 215                 if (!$rs) {
 216                         echo "<p><b>Missing PLAN_TABLE</b></p>
 217 <pre>
 218 CREATE TABLE PLAN_TABLE (
 219   STATEMENT_ID                    VARCHAR2(30),
 220   TIMESTAMP                       DATE,
 221   REMARKS                         VARCHAR2(80),
 222   OPERATION                       VARCHAR2(30),
 223   OPTIONS                         VARCHAR2(30),
 224   OBJECT_NODE                     VARCHAR2(128),
 225   OBJECT_OWNER                    VARCHAR2(30),
 226   OBJECT_NAME                     VARCHAR2(30),
 227   OBJECT_INSTANCE                 NUMBER(38),
 228   OBJECT_TYPE                     VARCHAR2(30),
 229   OPTIMIZER                       VARCHAR2(255),
 230   SEARCH_COLUMNS                  NUMBER,
 231   ID                              NUMBER(38),
 232   PARENT_ID                       NUMBER(38),
 233   POSITION                        NUMBER(38),
 234   COST                            NUMBER(38),
 235   CARDINALITY                     NUMBER(38),
 236   BYTES                           NUMBER(38),
 237   OTHER_TAG                       VARCHAR2(255),
 238   PARTITION_START                 VARCHAR2(255),
 239   PARTITION_STOP                  VARCHAR2(255),
 240   PARTITION_ID                    NUMBER(38),
 241   OTHER                           LONG,
 242   DISTRIBUTION                    VARCHAR2(30)
 243 );
 244 </pre>";
 245                         return false;
 246                 }
 247
 248                 $rs->Close();
 249         //      $this->conn->debug=1;
 250
 251                 if ($partial) {
 252                         $sqlq = $this->conn->qstr($sql.'%');
 253                         $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
 254                         if ($arr) {
 255                                 foreach($arr as $row) {
 256                                         $sql = reset($row);
 257                                         if (crc32($sql) == $partial) break;
 258                                 }
 259                         }
 260                 }
 261
 262                 $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";
 263
 264                 $this->conn->BeginTrans();
 265                 $id = "ADODB ".microtime();
 266
 267                 $rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
 268                 $m = $this->conn->ErrorMsg();
 269                 if ($m) {
 270                         $this->conn->RollbackTrans();
 271                         $this->conn->LogSQL($savelog);
 272                         $s .= "<p>$m</p>";
 273                         return $s;
 274                 }
 275                 $rs =& $this->conn->Execute("
 276                 select
 277   '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation,
 278   object_name,COST,CARDINALITY,bytes
 279                 FROM plan_table
 280 START WITH id = 0  and STATEMENT_ID='$id'
 281 CONNECT BY prior id=parent_id and statement_id='$id'");
 282
 283                 $s .= rs2html($rs,false,false,false,false);
 284                 $this->conn->RollbackTrans();
 285                 $this->conn->LogSQL($savelog);
 286                 $s .= $this->Tracer($sql,$partial);
 287                 return $s;
 288         }
 289
 290
 291         function CheckMemory()
 292         {
 293                 if ($this->version['version'] < 9) return 'Oracle 9i or later required';
 294
 295                  $rs =& $this->conn->Execute("
 296 select  a.size_for_estimate as cache_mb_estimate,
 297         case when a.size_factor=1 then
 298                 '&lt;&lt;= current'
 299          when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
 300                 '- BETTER - '
 301         else ' ' end as currsize,
 302    a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
 303    from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a ,
 304    (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
 305                 if (!$rs) return false;
 306
 307                 /*
 308                 The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
 309                 */
 310                 $s = "<h3>Data Cache Estimate</h3>";
 311                 if ($rs->EOF) {
 312                         $s .= "<p>Cache that is 50% of current size is still too big</p>";
 313                 } else {
 314                         $s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
 315                         $s .= rs2html($rs,false,false,false,false);
 316                 }
 317                 return $s;
 318         }
 319
 320         /*
 321                 Generate html for suspicious/expensive sql
 322         */
 323         function tohtml(&$rs,$type)
 324         {
 325                 $o1 = $rs->FetchField(0);
 326                 $o2 = $rs->FetchField(1);
 327                 $o3 = $rs->FetchField(2);
 328                 if ($rs->EOF) return '<p>None found</p>';
 329                 $check = '';
 330                 $sql = '';
 331                 $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>';
 332                 while (!$rs->EOF) {
 333                         if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
 334                                 if ($check) {
 335                                         $carr = explode('::',$check);
 336                                         $prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
 337                                         $suffix = '</a>';
 338                                         if (strlen($prefix)>2000) {
 339                                                 $prefix = '';
 340                                                 $suffix = '';
 341                                         }
 342
 343                                         $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
 344                                 }
 345                                 $sql = $rs->fields[2];
 346                                 $check = $rs->fields[0].'::'.$rs->fields[1];
 347                         } else
 348                                 $sql .= $rs->fields[2];
 349                         if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
 350                         $rs->MoveNext();
 351                 }
 352                 $rs->Close();
 353
 354                 $carr = explode('::',$check);
 355                 $prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
 356                 $suffix = '</a>';
 357                 if (strlen($prefix)>2000) {
 358                         $prefix = '';
 359                         $suffix = '';
 360                 }
 361                 $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
 362
 363                 return $s."</table>\n\n";
 364         }
 365
 366         // code thanks to Ixora.
 367         // http://www.ixora.com.au/scripts/query_opt.htm
 368         // requires oracle 8.1.7 or later
 369         function SuspiciousSQL($numsql=10)
 370         {
 371                 $sql = "
 372 select
 373   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
 374   s.executions  executes,
 375   p.sql_text
 376 from
 377   (
 378     select
 379       address,
 380       buffer_gets,
 381       executions,
 382       pct,
 383       rank() over (order by buffer_gets desc)  ranking
 384     from
 385       (
 386         select
 387           address,
 388           buffer_gets,
 389           executions,
 390           100 * ratio_to_report(buffer_gets) over ()  pct
 391         from
 392           sys.v_\$sql
 393         where
 394           command_type != 47 and module != 'T.O.A.D.'
 395       )
 396     where
 397       buffer_gets > 50 * executions
 398   )  s,
 399   sys.v_\$sqltext  p
 400 where
 401   s.ranking <= $numsql and
 402   p.address = s.address
 403 order by
 404   1 desc, s.address, p.piece";
 405
 406                 global $ADODB_CACHE_MODE;
 407                 if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
 408                                 $partial = empty($_GET['part']);
 409                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
 410                 }
 411
 412                 if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
 413
 414                 $s = '';
 415                 $s .= $this->_SuspiciousSQL($numsql);
 416                 $s .= '<p>';
 417
 418                 $save = $ADODB_CACHE_MODE;
 419                 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
 420                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 421
 422                 $savelog = $this->conn->LogSQL(false);
 423                 $rs =& $this->conn->SelectLimit($sql);
 424                 $this->conn->LogSQL($savelog);
 425
 426                 if (isset($savem)) $this->conn->SetFetchMode($savem);
 427                 $ADODB_CACHE_MODE = $save;
 428                 if ($rs) {
 429                         $s .= "\n<h3>Ixora Suspicious SQL</h3>";
 430                         $s .= $this->tohtml($rs,'expsixora');
 431                 }
 432
 433                 return $s;
 434         }
 435
 436         // code thanks to Ixora.
 437         // http://www.ixora.com.au/scripts/query_opt.htm
 438         // requires oracle 8.1.7 or later
 439         function ExpensiveSQL($numsql = 10)
 440         {
 441                 $sql = "
 442 select
 443   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
 444   s.executions  executes,
 445   p.sql_text
 446 from
 447   (
 448     select
 449       address,
 450       disk_reads,
 451       executions,
 452       pct,
 453       rank() over (order by disk_reads desc)  ranking
 454     from
 455       (
 456         select
 457           address,
 458           disk_reads,
 459           executions,
 460           100 * ratio_to_report(disk_reads) over ()  pct
 461         from
 462           sys.v_\$sql
 463         where
 464           command_type != 47 and module != 'T.O.A.D.'
 465       )
 466     where
 467       disk_reads > 50 * executions
 468   )  s,
 469   sys.v_\$sqltext  p
 470 where
 471   s.ranking <= $numsql and
 472   p.address = s.address
 473 order by
 474   1 desc, s.address, p.piece
 475 ";
 476                 global $ADODB_CACHE_MODE;
 477                 if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
 478                         $partial = empty($_GET['part']);
 479                         echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
 480                 }
 481                 if (isset($_GET['sql'])) {
 482                          $var = $this->_ExpensiveSQL($numsql);
 483                          return $var;
 484                 }
 485
 486                 $s = '';
 487                 $s .= $this->_ExpensiveSQL($numsql);
 488                 $s .= '<p>';
 489                 $save = $ADODB_CACHE_MODE;
 490                 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
 491                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
 492
 493                 $savelog = $this->conn->LogSQL(false);
 494                 $rs =& $this->conn->Execute($sql);
 495                 $this->conn->LogSQL($savelog);
 496
 497                 if (isset($savem)) $this->conn->SetFetchMode($savem);
 498                 $ADODB_CACHE_MODE = $save;
 499
 500                 if ($rs) {
 501                         $s .= "\n<h3>Ixora Expensive SQL</h3>";
 502                         $s .= $this->tohtml($rs,'expeixora');
 503                 }
 504
 505                 return $s;
 506         }
 507
 508 }
 509 ?>