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

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