| [ Index ] |
|
Code source de Mantis 1.1.0rc3 |
1 <?php 2 /* 3 V4.94 23 Jan 2007 (c) 2000-2007 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/case when p.value=0 then sga.bytes else to_number(p.value) end)*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 'Backup', 161 'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', 'To turn on archivelog:<br> 162 <pre> 163 SQLPLUS> connect sys as sysdba; 164 SQLPLUS> shutdown immediate; 165 166 SQLPLUS> startup mount exclusive; 167 SQLPLUS> alter database archivelog; 168 SQLPLUS> archive log start; 169 SQLPLUS> alter database open; 170 </pre>'), 171 172 'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'), 173 'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value) 174 FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''), 175 176 '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.'), 177 '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.'), 178 false 179 180 ); 181 182 183 function perf_oci8(&$conn) 184 { 185 $savelog = $conn->LogSQL(false); 186 $this->version = $conn->ServerInfo(); 187 $conn->LogSQL($savelog); 188 $this->conn =& $conn; 189 } 190 191 function WarnPageCost($val) 192 { 193 if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>'; 194 else $s = ''; 195 196 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>. '; 197 } 198 199 function WarnIndexCost($val) 200 { 201 if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>'; 202 else $s = ''; 203 204 return $s.'Percentage of indexed data blocks expected in the cache. 205 Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0. 206 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.'; 207 } 208 209 function PGA() 210 { 211 if ($this->version['version'] < 9) return 'Oracle 9i or later required'; 212 213 $rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from 214 (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb, 215 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 216 from v\$pga_target_advice) a left join 217 (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb, 218 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 219 from v\$pga_target_advice) b on 220 a.r = b.r+1 where 221 b.pct < 100"); 222 if (!$rs) return "Only in 9i or later"; 223 $rs->Close(); 224 if ($rs->EOF) return "PGA could be too big"; 225 226 return reset($rs->fields); 227 } 228 229 function Explain($sql,$partial=false) 230 { 231 $savelog = $this->conn->LogSQL(false); 232 $rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE"); 233 if (!$rs) { 234 echo "<p><b>Missing PLAN_TABLE</b></p> 235 <pre> 236 CREATE TABLE PLAN_TABLE ( 237 STATEMENT_ID VARCHAR2(30), 238 TIMESTAMP DATE, 239 REMARKS VARCHAR2(80), 240 OPERATION VARCHAR2(30), 241 OPTIONS VARCHAR2(30), 242 OBJECT_NODE VARCHAR2(128), 243 OBJECT_OWNER VARCHAR2(30), 244 OBJECT_NAME VARCHAR2(30), 245 OBJECT_INSTANCE NUMBER(38), 246 OBJECT_TYPE VARCHAR2(30), 247 OPTIMIZER VARCHAR2(255), 248 SEARCH_COLUMNS NUMBER, 249 ID NUMBER(38), 250 PARENT_ID NUMBER(38), 251 POSITION NUMBER(38), 252 COST NUMBER(38), 253 CARDINALITY NUMBER(38), 254 BYTES NUMBER(38), 255 OTHER_TAG VARCHAR2(255), 256 PARTITION_START VARCHAR2(255), 257 PARTITION_STOP VARCHAR2(255), 258 PARTITION_ID NUMBER(38), 259 OTHER LONG, 260 DISTRIBUTION VARCHAR2(30) 261 ); 262 </pre>"; 263 return false; 264 } 265 266 $rs->Close(); 267 // $this->conn->debug=1; 268 269 if ($partial) { 270 $sqlq = $this->conn->qstr($sql.'%'); 271 $arr = $this->conn->GetArray("select distinct distinct sql1 from adodb_logsql where sql1 like $sqlq"); 272 if ($arr) { 273 foreach($arr as $row) { 274 $sql = reset($row); 275 if (crc32($sql) == $partial) break; 276 } 277 } 278 } 279 280 $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>"; 281 282 $this->conn->BeginTrans(); 283 $id = "ADODB ".microtime(); 284 285 $rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql"); 286 $m = $this->conn->ErrorMsg(); 287 if ($m) { 288 $this->conn->RollbackTrans(); 289 $this->conn->LogSQL($savelog); 290 $s .= "<p>$m</p>"; 291 return $s; 292 } 293 $rs =& $this->conn->Execute(" 294 select 295 '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>' as Operation, 296 object_name,COST,CARDINALITY,bytes 297 FROM plan_table 298 START WITH id = 0 and STATEMENT_ID='$id' 299 CONNECT BY prior id=parent_id and statement_id='$id'"); 300 301 $s .= rs2html($rs,false,false,false,false); 302 $this->conn->RollbackTrans(); 303 $this->conn->LogSQL($savelog); 304 $s .= $this->Tracer($sql,$partial); 305 return $s; 306 } 307 308 309 function CheckMemory() 310 { 311 if ($this->version['version'] < 9) return 'Oracle 9i or later required'; 312 313 $rs =& $this->conn->Execute(" 314 select a.size_for_estimate as cache_mb_estimate, 315 case when a.size_factor=1 then 316 '<<= current' 317 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then 318 '- BETTER - ' 319 else ' ' end as currsize, 320 a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0 321 from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) a , 322 (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1"); 323 if (!$rs) return false; 324 325 /* 326 The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size 327 */ 328 $s = "<h3>Data Cache Estimate</h3>"; 329 if ($rs->EOF) { 330 $s .= "<p>Cache that is 50% of current size is still too big</p>"; 331 } else { 332 $s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero."; 333 $s .= rs2html($rs,false,false,false,false); 334 } 335 return $s; 336 } 337 338 /* 339 Generate html for suspicious/expensive sql 340 */ 341 function tohtml(&$rs,$type) 342 { 343 $o1 = $rs->FetchField(0); 344 $o2 = $rs->FetchField(1); 345 $o3 = $rs->FetchField(2); 346 if ($rs->EOF) return '<p>None found</p>'; 347 $check = ''; 348 $sql = ''; 349 $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>'; 350 while (!$rs->EOF) { 351 if ($check != $rs->fields[0].'::'.$rs->fields[1]) { 352 if ($check) { 353 $carr = explode('::',$check); 354 $prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">'; 355 $suffix = '</a>'; 356 if (strlen($prefix)>2000) { 357 $prefix = ''; 358 $suffix = ''; 359 } 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 $sql = $rs->fields[2]; 364 $check = $rs->fields[0].'::'.$rs->fields[1]; 365 } else 366 $sql .= $rs->fields[2]; 367 if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1); 368 $rs->MoveNext(); 369 } 370 $rs->Close(); 371 372 $carr = explode('::',$check); 373 $prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">'; 374 $suffix = '</a>'; 375 if (strlen($prefix)>2000) { 376 $prefix = ''; 377 $suffix = ''; 378 } 379 $s .= "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>'; 380 381 return $s."</table>\n\n"; 382 } 383 384 // code thanks to Ixora. 385 // http://www.ixora.com.au/scripts/query_opt.htm 386 // requires oracle 8.1.7 or later 387 function SuspiciousSQL($numsql=10) 388 { 389 $sql = " 390 select 391 substr(to_char(s.pct, '99.00'), 2) || '%' load, 392 s.executions executes, 393 p.sql_text 394 from 395 ( 396 select 397 address, 398 buffer_gets, 399 executions, 400 pct, 401 rank() over (order by buffer_gets desc) ranking 402 from 403 ( 404 select 405 address, 406 buffer_gets, 407 executions, 408 100 * ratio_to_report(buffer_gets) over () pct 409 from 410 sys.v_\$sql 411 where 412 command_type != 47 and module != 'T.O.A.D.' 413 ) 414 where 415 buffer_gets > 50 * executions 416 ) s, 417 sys.v_\$sqltext p 418 where 419 s.ranking <= $numsql and 420 p.address = s.address 421 order by 422 1 desc, s.address, p.piece"; 423 424 global $ADODB_CACHE_MODE; 425 if (isset($_GET['expsixora']) && isset($_GET['sql'])) { 426 $partial = empty($_GET['part']); 427 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 428 } 429 430 if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql); 431 432 $s = ''; 433 $s .= $this->_SuspiciousSQL($numsql); 434 $s .= '<p>'; 435 436 $save = $ADODB_CACHE_MODE; 437 $ADODB_CACHE_MODE = ADODB_FETCH_NUM; 438 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 439 440 $savelog = $this->conn->LogSQL(false); 441 $rs =& $this->conn->SelectLimit($sql); 442 $this->conn->LogSQL($savelog); 443 444 if (isset($savem)) $this->conn->SetFetchMode($savem); 445 $ADODB_CACHE_MODE = $save; 446 if ($rs) { 447 $s .= "\n<h3>Ixora Suspicious SQL</h3>"; 448 $s .= $this->tohtml($rs,'expsixora'); 449 } 450 451 return $s; 452 } 453 454 // code thanks to Ixora. 455 // http://www.ixora.com.au/scripts/query_opt.htm 456 // requires oracle 8.1.7 or later 457 function ExpensiveSQL($numsql = 10) 458 { 459 $sql = " 460 select 461 substr(to_char(s.pct, '99.00'), 2) || '%' load, 462 s.executions executes, 463 p.sql_text 464 from 465 ( 466 select 467 address, 468 disk_reads, 469 executions, 470 pct, 471 rank() over (order by disk_reads desc) ranking 472 from 473 ( 474 select 475 address, 476 disk_reads, 477 executions, 478 100 * ratio_to_report(disk_reads) over () pct 479 from 480 sys.v_\$sql 481 where 482 command_type != 47 and module != 'T.O.A.D.' 483 ) 484 where 485 disk_reads > 50 * executions 486 ) s, 487 sys.v_\$sqltext p 488 where 489 s.ranking <= $numsql and 490 p.address = s.address 491 order by 492 1 desc, s.address, p.piece 493 "; 494 global $ADODB_CACHE_MODE; 495 if (isset($_GET['expeixora']) && isset($_GET['sql'])) { 496 $partial = empty($_GET['part']); 497 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 498 } 499 if (isset($_GET['sql'])) { 500 $var = $this->_ExpensiveSQL($numsql); 501 return $var; 502 } 503 504 $s = ''; 505 $s .= $this->_ExpensiveSQL($numsql); 506 $s .= '<p>'; 507 $save = $ADODB_CACHE_MODE; 508 $ADODB_CACHE_MODE = ADODB_FETCH_NUM; 509 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 510 511 $savelog = $this->conn->LogSQL(false); 512 $rs =& $this->conn->Execute($sql); 513 $this->conn->LogSQL($savelog); 514 515 if (isset($savem)) $this->conn->SetFetchMode($savem); 516 $ADODB_CACHE_MODE = $save; 517 518 if ($rs) { 519 $s .= "\n<h3>Ixora Expensive SQL</h3>"; 520 $s .= $this->tohtml($rs,'expeixora'); 521 } 522 523 return $s; 524 } 525 526 } 527 ?>
titre
Description
Corps
titre
Description
Corps
titre
Description
Corps
titre
Corps
| Généré le : Thu Nov 29 09:42:17 2007 | par Balluche grâce à PHPXref 0.7 |
|
|