[Sommaire] [Imprimer]

   1  <?php
   2  /* 
   3  V4.90 8 June 2006  (c) 2000-2006 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/p.value)*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  ?>