Documentation TYPO3 par Ameos

perf-oci8.inc.php

00001 <?php
00002 /* 
00003 V4.80 8 Mar 2006  (c) 2000-2006 John Lim (jlim@natsoft.com.my). All rights reserved.
00004   Released under both BSD license and Lesser GPL library license. 
00005   Whenever there is any discrepancy between the two licenses, 
00006   the BSD license will take precedence. See License.txt. 
00007   Set tabs to 4 for best viewing.
00008   
00009   Latest version is available at http://adodb.sourceforge.net
00010   
00011   Library for basic performance monitoring and tuning 
00012   
00013 */
00014 
00015 // security - hide paths
00016 if (!defined('ADODB_DIR')) die();
00017 
00018 class perf_oci8 extends ADODB_perf{
00019         
00020         var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents 
00021            group by segment_name,tablespace_name";
00022          
00023         var $version;
00024         var $createTableSQL = "CREATE TABLE adodb_logsql (
00025                   created date NOT NULL,
00026                   sql0 varchar(250) NOT NULL,
00027                   sql1 varchar(4000) NOT NULL,
00028                   params varchar(4000),
00029                   tracer varchar(4000),
00030                   timer decimal(16,6) NOT NULL
00031                 )";
00032         
00033         var $settings = array(
00034         'Ratios',
00035                 'data cache hit ratio' => array('RATIOH',
00036                         "select round((1-(phy.value / (cur.value + con.value)))*100,2) 
00037                         from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
00038                         where cur.name = 'db block gets' and
00039                               con.name = 'consistent gets' and
00040                               phy.name = 'physical reads'",
00041                         '=WarnCacheRatio'),
00042                 
00043                 'sql cache hit ratio' => array( 'RATIOH',
00044                         'select round(100*(sum(pins)-sum(reloads))/sum(pins),2)  from v$librarycache',
00045                         'increase <i>shared_pool_size</i> if too ratio low'),
00046                         
00047                 'datadict cache hit ratio' => array('RATIOH',
00048                 "select      
00049            round((1 - (sum(getmisses) / (sum(gets) +          
00050                  sum(getmisses))))*100,2)
00051                 from  v\$rowcache",
00052                 'increase <i>shared_pool_size</i> if too ratio low'),
00053                 
00054                 'memory sort ratio' => array('RATIOH',
00055                 "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 
00056        0,1,(a.VALUE + b.VALUE)),2)
00057 FROM   v\$sysstat a, 
00058        v\$sysstat b
00059 WHERE  a.name = 'sorts (disk)'
00060 AND    b.name = 'sorts (memory)'",
00061         "% of memory sorts compared to disk sorts - should be over 95%"),
00062 
00063         'IO',
00064                 'data reads' => array('IO',
00065                 "select value from v\$sysstat where name='physical reads'"),
00066         
00067         'data writes' => array('IO',
00068                 "select value from v\$sysstat where name='physical writes'"),
00069         
00070         'Data Cache',
00071                 'data cache buffers' => array( 'DATAC',
00072                 "select a.value/b.value  from v\$parameter a, v\$parameter b 
00073                         where a.name = 'db_cache_size' and b.name= 'db_block_size'",
00074                         'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
00075                 'data cache blocksize' => array('DATAC',
00076                         "select value from v\$parameter where name='db_block_size'",
00077                         '' ),                   
00078         'Memory Pools',
00079                 'data cache size' => array('DATAC',
00080                         "select value from v\$parameter where name = 'db_cache_size'",
00081                         'db_cache_size' ),
00082                 'shared pool size' => array('DATAC',
00083                         "select value from v\$parameter where name = 'shared_pool_size'",
00084                         'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
00085                 'java pool size' => array('DATAJ',
00086                         "select value from v\$parameter where name = 'java_pool_size'",
00087                         'java_pool_size' ),
00088                 'large pool buffer size' => array('CACHE',
00089                         "select value from v\$parameter where name='large_pool_size'",
00090                         '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) ' ),
00091 
00092                 'pga buffer size' => array('CACHE',                     
00093                         "select value from v\$parameter where name='pga_aggregate_target'",
00094                         'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ),
00095 
00096                 
00097                 'Connections',
00098                 'current connections' => array('SESS',
00099                         'select count(*) from sys.v_$session where username is not null',
00100                         ''),
00101                 'max connections' => array( 'SESS',
00102                         "select value from v\$parameter where name='sessions'",
00103                         ''),
00104 
00105         'Memory Utilization',           
00106                 'data cache utilization ratio' => array('RATIOU',
00107                         "select round((1-bytes/sgasize)*100, 2)
00108                         from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
00109                         where name = 'free memory' and pool = 'shared pool'",
00110                 'Percentage of data cache actually in use - should be over 85%'),
00111                 
00112                 'shared pool utilization ratio' => array('RATIOU',
00113                 'select round((sga.bytes/p.value)*100,2)
00114                 from v$sgastat sga, v$parameter p
00115                 where sga.name = \'free memory\' and sga.pool = \'shared pool\'
00116                 and p.name = \'shared_pool_size\'',
00117                 'Percentage of shared pool actually used - too low is bad, too high is worse'),
00118                 
00119                 'large pool utilization ratio' => array('RATIOU',
00120                         "select round((1-bytes/sgasize)*100, 2)
00121                         from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
00122                         where name = 'free memory' and pool = 'large pool'",
00123                 'Percentage of large_pool actually in use - too low is bad, too high is worse'),
00124                 'sort buffer size' => array('CACHE',
00125                         "select value from v\$parameter where name='sort_area_size'",
00126                         'max in-mem sort_area_size (per query), uses memory in pga' ),
00127 
00128                 'pga usage at peak' => array('RATIOU',
00129                 '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),
00130         'Transactions',
00131                 'rollback segments' => array('ROLLBACK',
00132                         "select count(*) from sys.v_\$rollstat",
00133                         ''),
00134         
00135                 'peak transactions' => array('ROLLBACK',
00136                         "select max_utilization  tx_hwm
00137                 from sys.v_\$resource_limit
00138                 where resource_name = 'transactions'",
00139                         'Taken from high-water-mark'),
00140                 'max transactions' => array('ROLLBACK',
00141                         "select value from v\$parameter where name = 'transactions'",
00142                         'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
00143         'Parameters',   
00144                 'cursor sharing' => array('CURSOR',
00145                         "select value from v\$parameter where name = 'cursor_sharing'",
00146                         '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>.'),
00147                 /*
00148                 'cursor reuse' => array('CURSOR',
00149                         "select count(*) from (select sql_text_wo_constants, count(*)
00150   from t1
00151  group by sql_text_wo_constants
00152 having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
00153                 'index cache cost' => array('COST',
00154                         "select value from v\$parameter where name = 'optimizer_index_caching'",
00155                         '=WarnIndexCost'),
00156                 'random page cost' => array('COST',
00157                         "select value from v\$parameter where name = 'optimizer_index_cost_adj'",
00158                         '=WarnPageCost'),
00159                 
00160                 false
00161                 
00162         );
00163         
00164         
00165         function perf_oci8(&$conn)
00166         {
00167                 $savelog = $conn->LogSQL(false);        
00168                 $this->version = $conn->ServerInfo();
00169                 $conn->LogSQL($savelog);        
00170                 $this->conn =& $conn;
00171         }
00172         
00173         function WarnPageCost($val)
00174         {
00175                 if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>';
00176                 else $s = '';
00177                 
00178                 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>. ';
00179         }
00180         
00181         function WarnIndexCost($val)
00182         {
00183                 if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>';
00184                 else $s = '';
00185                 
00186                 return $s.'Percentage of indexed data blocks expected in the cache.
00187                         Recommended is 20 (fast disk array) to 50 (slower hard disks). Default is 0.
00188                          See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
00189                 }
00190         
00191         function PGA()
00192         {
00193                 if ($this->version['version'] < 9) return 'Oracle 9i or later required';
00194                 
00195                 $rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from 
00196            (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
00197                    pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 
00198                    from v\$pga_target_advice) a left join
00199            (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
00200                    pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 
00201                    from v\$pga_target_advice) b on 
00202           a.r = b.r+1 where 
00203                 b.pct < 100");
00204                 if (!$rs) return "Only in 9i or later";
00205                 $rs->Close();
00206                 if ($rs->EOF) return "PGA could be too big";
00207                 
00208                 return reset($rs->fields);
00209         }
00210         
00211         function Explain($sql,$partial=false) 
00212         {
00213                 $savelog = $this->conn->LogSQL(false);
00214                 $rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
00215                 if (!$rs) {
00216                         echo "<p><b>Missing PLAN_TABLE</b></p>
00217 <pre>
00218 CREATE TABLE PLAN_TABLE (
00219   STATEMENT_ID                    VARCHAR2(30),
00220   TIMESTAMP                       DATE,
00221   REMARKS                         VARCHAR2(80),
00222   OPERATION                       VARCHAR2(30),
00223   OPTIONS                         VARCHAR2(30),
00224   OBJECT_NODE                     VARCHAR2(128),
00225   OBJECT_OWNER                    VARCHAR2(30),
00226   OBJECT_NAME                     VARCHAR2(30),
00227   OBJECT_INSTANCE                 NUMBER(38),
00228   OBJECT_TYPE                     VARCHAR2(30),
00229   OPTIMIZER                       VARCHAR2(255),
00230   SEARCH_COLUMNS                  NUMBER,
00231   ID                              NUMBER(38),
00232   PARENT_ID                       NUMBER(38),
00233   POSITION                        NUMBER(38),
00234   COST                            NUMBER(38),
00235   CARDINALITY                     NUMBER(38),
00236   BYTES                           NUMBER(38),
00237   OTHER_TAG                       VARCHAR2(255),
00238   PARTITION_START                 VARCHAR2(255),
00239   PARTITION_STOP                  VARCHAR2(255),
00240   PARTITION_ID                    NUMBER(38),
00241   OTHER                           LONG,
00242   DISTRIBUTION                    VARCHAR2(30)
00243 );
00244 </pre>";
00245                         return false;
00246                 }
00247                 
00248                 $rs->Close();
00249         //      $this->conn->debug=1;
00250         
00251                 if ($partial) {
00252                         $sqlq = $this->conn->qstr($sql.'%');
00253                         $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq");
00254                         if ($arr) {
00255                                 foreach($arr as $row) {
00256                                         $sql = reset($row);
00257                                         if (crc32($sql) == $partial) break;
00258                                 }
00259                         }
00260                 }
00261                 
00262                 $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";       
00263                 
00264                 $this->conn->BeginTrans();
00265                 $id = "ADODB ".microtime();
00266 
00267                 $rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
00268                 $m = $this->conn->ErrorMsg();
00269                 if ($m) {
00270                         $this->conn->RollbackTrans();
00271                         $this->conn->LogSQL($savelog);
00272                         $s .= "<p>$m</p>";
00273                         return $s;
00274                 }
00275                 $rs =& $this->conn->Execute("
00276                 select 
00277   '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation, 
00278   object_name,COST,CARDINALITY,bytes
00279                 FROM plan_table 
00280 START WITH id = 0  and STATEMENT_ID='$id'  
00281 CONNECT BY prior id=parent_id and statement_id='$id'");
00282                 
00283                 $s .= rs2html($rs,false,false,false,false);
00284                 $this->conn->RollbackTrans();
00285                 $this->conn->LogSQL($savelog);
00286                 $s .= $this->Tracer($sql,$partial);
00287                 return $s;
00288         }
00289         
00290         
00291         function CheckMemory()
00292         {
00293                 if ($this->version['version'] < 9) return 'Oracle 9i or later required';
00294                 
00295                  $rs =& $this->conn->Execute("
00296 select  a.size_for_estimate as cache_mb_estimate,
00297         case when a.size_factor=1 then 
00298                 '&lt;&lt;= current'
00299          when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
00300                 '- BETTER - '
00301         else ' ' end as currsize, 
00302    a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
00303    from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a , 
00304    (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
00305                 if (!$rs) return false;
00306                 
00307                 /*
00308                 The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
00309                 */
00310                 $s = "<h3>Data Cache Estimate</h3>";
00311                 if ($rs->EOF) {
00312                         $s .= "<p>Cache that is 50% of current size is still too big</p>";
00313                 } else {
00314                         $s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
00315                         $s .= rs2html($rs,false,false,false,false);
00316                 }
00317                 return $s;
00318         }
00319         
00320         /*
00321                 Generate html for suspicious/expensive sql
00322         */
00323         function tohtml(&$rs,$type)
00324         {
00325                 $o1 = $rs->FetchField(0);
00326                 $o2 = $rs->FetchField(1);
00327                 $o3 = $rs->FetchField(2);
00328                 if ($rs->EOF) return '<p>None found</p>';
00329                 $check = '';
00330                 $sql = '';
00331                 $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>';
00332                 while (!$rs->EOF) {
00333                         if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
00334                                 if ($check) {
00335                                         $carr = explode('::',$check);
00336                                         $prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
00337                                         $suffix = '</a>';
00338                                         if (strlen($prefix)>2000) {
00339                                                 $prefix = '';
00340                                                 $suffix = '';
00341                                         }
00342                                         
00343                                         $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
00344                                 }
00345                                 $sql = $rs->fields[2];
00346                                 $check = $rs->fields[0].'::'.$rs->fields[1];                    
00347                         } else
00348                                 $sql .= $rs->fields[2];
00349                         if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
00350                         $rs->MoveNext();
00351                 }
00352                 $rs->Close();
00353                 
00354                 $carr = explode('::',$check);
00355                 $prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
00356                 $suffix = '</a>';
00357                 if (strlen($prefix)>2000) {
00358                         $prefix = '';
00359                         $suffix = '';
00360                 }
00361                 $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
00362                                         
00363                 return $s."</table>\n\n";
00364         }
00365         
00366         // code thanks to Ixora. 
00367         // http://www.ixora.com.au/scripts/query_opt.htm
00368         // requires oracle 8.1.7 or later
00369         function SuspiciousSQL($numsql=10)
00370         {
00371                 $sql = "
00372 select
00373   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
00374   s.executions  executes,
00375   p.sql_text
00376 from
00377   ( 
00378     select
00379       address,
00380       buffer_gets,
00381       executions,
00382       pct,
00383       rank() over (order by buffer_gets desc)  ranking
00384     from
00385       ( 
00386         select
00387           address,
00388           buffer_gets,
00389           executions,
00390           100 * ratio_to_report(buffer_gets) over ()  pct
00391         from
00392           sys.v_\$sql
00393         where
00394           command_type != 47 and module != 'T.O.A.D.'
00395       )
00396     where
00397       buffer_gets > 50 * executions
00398   )  s,
00399   sys.v_\$sqltext  p
00400 where
00401   s.ranking <= $numsql and
00402   p.address = s.address
00403 order by
00404   1 desc, s.address, p.piece";
00405 
00406                 global $ADODB_CACHE_MODE;
00407                 if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
00408                                 $partial = empty($_GET['part']);
00409                                 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00410                 }
00411 
00412                 if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
00413                 
00414                 $s = '';
00415                 $s .= $this->_SuspiciousSQL($numsql);
00416                 $s .= '<p>';
00417                 
00418                 $save = $ADODB_CACHE_MODE;
00419                 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
00420                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00421                 
00422                 $savelog = $this->conn->LogSQL(false);
00423                 $rs =& $this->conn->SelectLimit($sql);
00424                 $this->conn->LogSQL($savelog);
00425                 
00426                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00427                 $ADODB_CACHE_MODE = $save;
00428                 if ($rs) {
00429                         $s .= "\n<h3>Ixora Suspicious SQL</h3>";
00430                         $s .= $this->tohtml($rs,'expsixora');
00431                 }
00432                 
00433                 return $s;
00434         }
00435         
00436         // code thanks to Ixora. 
00437         // http://www.ixora.com.au/scripts/query_opt.htm
00438         // requires oracle 8.1.7 or later
00439         function ExpensiveSQL($numsql = 10)
00440         {
00441                 $sql = "
00442 select
00443   substr(to_char(s.pct, '99.00'), 2) || '%'  load,
00444   s.executions  executes,
00445   p.sql_text
00446 from
00447   ( 
00448     select
00449       address,
00450       disk_reads,
00451       executions,
00452       pct,
00453       rank() over (order by disk_reads desc)  ranking
00454     from
00455       (
00456         select
00457           address,
00458           disk_reads,
00459           executions,
00460           100 * ratio_to_report(disk_reads) over ()  pct
00461         from
00462           sys.v_\$sql
00463         where
00464           command_type != 47 and module != 'T.O.A.D.'
00465       )
00466     where
00467       disk_reads > 50 * executions
00468   )  s,
00469   sys.v_\$sqltext  p
00470 where
00471   s.ranking <= $numsql and
00472   p.address = s.address
00473 order by
00474   1 desc, s.address, p.piece
00475 ";
00476                 global $ADODB_CACHE_MODE;
00477                 if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
00478                         $partial = empty($_GET['part']);        
00479                         echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
00480                 }
00481                 if (isset($_GET['sql'])) {
00482                          $var = $this->_ExpensiveSQL($numsql);
00483                          return $var;
00484                 }
00485                 
00486                 $s = '';                
00487                 $s .= $this->_ExpensiveSQL($numsql);
00488                 $s .= '<p>';
00489                 $save = $ADODB_CACHE_MODE;
00490                 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
00491                 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
00492                 
00493                 $savelog = $this->conn->LogSQL(false);
00494                 $rs =& $this->conn->Execute($sql);
00495                 $this->conn->LogSQL($savelog);
00496                 
00497                 if (isset($savem)) $this->conn->SetFetchMode($savem);
00498                 $ADODB_CACHE_MODE = $save;
00499                 
00500                 if ($rs) {
00501                         $s .= "\n<h3>Ixora Expensive SQL</h3>";
00502                         $s .= $this->tohtml($rs,'expeixora');
00503                 }
00504         
00505                 return $s;
00506         }
00507         
00508 }
00509 ?>


Généré par L'expert TYPO3 avec  doxygen 1.4.6