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