00001 <?php
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
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
00149
00150
00151
00152
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:
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:
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
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 '<<= 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
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
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
00437
00438
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 ?>