| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
1 <?php 2 /* 3 V5.19 23-Apr-2014 (c) 2000-2014 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 16 if (!defined('ADODB_DIR')) die(); 17 18 19 class perf_oci8 extends ADODB_perf{ 20 21 var $noShowIxora = 15; // if the sql for suspicious sql is taking too long, then disable ixora 22 23 var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents 24 group by segment_name,tablespace_name"; 25 26 var $version; 27 28 var $createTableSQL = "CREATE TABLE adodb_logsql ( 29 created date NOT NULL, 30 sql0 varchar(250) NOT NULL, 31 sql1 varchar(4000) NOT NULL, 32 params varchar(4000), 33 tracer varchar(4000), 34 timer decimal(16,6) NOT NULL 35 )"; 36 37 var $settings = array( 38 'Ratios', 39 'data cache hit ratio' => array('RATIOH', 40 "select round((1-(phy.value / (cur.value + con.value)))*100,2) 41 from v\$sysstat cur, v\$sysstat con, v\$sysstat phy 42 where cur.name = 'db block gets' and 43 con.name = 'consistent gets' and 44 phy.name = 'physical reads'", 45 '=WarnCacheRatio'), 46 47 'sql cache hit ratio' => array( 'RATIOH', 48 'select round(100*(sum(pins)-sum(reloads))/sum(pins),2) from v$librarycache', 49 'increase <i>shared_pool_size</i> if too ratio low'), 50 51 'datadict cache hit ratio' => array('RATIOH', 52 "select 53 round((1 - (sum(getmisses) / (sum(gets) + 54 sum(getmisses))))*100,2) 55 from v\$rowcache", 56 'increase <i>shared_pool_size</i> if too ratio low'), 57 58 'memory sort ratio' => array('RATIOH', 59 "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE), 60 0,1,(a.VALUE + b.VALUE)),2) 61 FROM v\$sysstat a, 62 v\$sysstat b 63 WHERE a.name = 'sorts (disk)' 64 AND b.name = 'sorts (memory)'", 65 "% of memory sorts compared to disk sorts - should be over 95%"), 66 67 'IO', 68 'data reads' => array('IO', 69 "select value from v\$sysstat where name='physical reads'"), 70 71 'data writes' => array('IO', 72 "select value from v\$sysstat where name='physical writes'"), 73 74 'Data Cache', 75 76 'data cache buffers' => array( 'DATAC', 77 "select a.value/b.value from v\$parameter a, v\$parameter b 78 where a.name = 'db_cache_size' and b.name= 'db_block_size'", 79 'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'), 80 'data cache blocksize' => array('DATAC', 81 "select value from v\$parameter where name='db_block_size'", 82 '' ), 83 84 'Memory Pools', 85 'Mem Max Target (11g+)' => array( 'DATAC', 86 "select value from v\$parameter where name = 'memory_max_target'", 87 'The memory_max_size is the maximum value to which memory_target can be set.' ), 88 'Memory target (11g+)' => array( 'DATAC', 89 "select value from v\$parameter where name = 'memory_target'", 90 'If memory_target is defined then SGA and PGA targets are consolidated into one memory_target.' ), 91 'SGA Max Size' => array( 'DATAC', 92 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_max_size'", 93 'The sga_max_size is the maximum value to which sga_target can be set.' ), 94 'SGA target' => array( 'DATAC', 95 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_target'", 96 'If sga_target is defined then data cache, shared, java and large pool size can be 0. This is because all these pools are consolidated into one sga_target.' ), 97 'PGA aggr target' => array( 'DATAC', 98 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'pga_aggregate_target'", 99 'If pga_aggregate_target is defined then this is the maximum memory that can be allocated for cursor operations such as sorts, group by, joins, merges. When in doubt, set it to 20% of sga_target.' ), 100 'data cache size' => array('DATAC', 101 "select value from v\$parameter where name = 'db_cache_size'", 102 'db_cache_size' ), 103 'shared pool size' => array('DATAC', 104 "select value from v\$parameter where name = 'shared_pool_size'", 105 'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ), 106 'java pool size' => array('DATAJ', 107 "select value from v\$parameter where name = 'java_pool_size'", 108 'java_pool_size' ), 109 'large pool buffer size' => array('CACHE', 110 "select value from v\$parameter where name='large_pool_size'", 111 '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) ' ), 112 113 'dynamic memory usage' => array('CACHE', "select '-' from dual", '=DynMemoryUsage'), 114 115 'Connections', 116 'current connections' => array('SESS', 117 'select count(*) from sys.v_$session where username is not null', 118 ''), 119 'max connections' => array( 'SESS', 120 "select value from v\$parameter where name='sessions'", 121 ''), 122 123 'Memory Utilization', 124 'data cache utilization ratio' => array('RATIOU', 125 "select round((1-bytes/sgasize)*100, 2) 126 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f 127 where name = 'free memory' and pool = 'shared pool'", 128 'Percentage of data cache actually in use - should be over 85%'), 129 130 'shared pool utilization ratio' => array('RATIOU', 131 'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2) 132 from v$sgastat sga, v$parameter p 133 where sga.name = \'free memory\' and sga.pool = \'shared pool\' 134 and p.name = \'shared_pool_size\'', 135 'Percentage of shared pool actually used - too low is bad, too high is worse'), 136 137 'large pool utilization ratio' => array('RATIOU', 138 "select round((1-bytes/sgasize)*100, 2) 139 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f 140 where name = 'free memory' and pool = 'large pool'", 141 'Percentage of large_pool actually in use - too low is bad, too high is worse'), 142 'sort buffer size' => array('CACHE', 143 "select value from v\$parameter where name='sort_area_size'", 144 'max in-mem sort_area_size (per query), uses memory in pga' ), 145 146 /*'pga usage at peak' => array('RATIOU', 147 '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),*/ 148 'Transactions', 149 'rollback segments' => array('ROLLBACK', 150 "select count(*) from sys.v_\$rollstat", 151 ''), 152 153 'peak transactions' => array('ROLLBACK', 154 "select max_utilization tx_hwm 155 from sys.v_\$resource_limit 156 where resource_name = 'transactions'", 157 'Taken from high-water-mark'), 158 'max transactions' => array('ROLLBACK', 159 "select value from v\$parameter where name = 'transactions'", 160 'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'), 161 'Parameters', 162 'cursor sharing' => array('CURSOR', 163 "select value from v\$parameter where name = 'cursor_sharing'", 164 '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>.'), 165 /* 166 'cursor reuse' => array('CURSOR', 167 "select count(*) from (select sql_text_wo_constants, count(*) 168 from t1 169 group by sql_text_wo_constants 170 having count(*) > 100)",'These are sql statements that should be using bind variables'),*/ 171 'index cache cost' => array('COST', 172 "select value from v\$parameter where name = 'optimizer_index_caching'", 173 '=WarnIndexCost'), 174 'random page cost' => array('COST', 175 "select value from v\$parameter where name = 'optimizer_index_cost_adj'", 176 '=WarnPageCost'), 177 'Waits', 178 'Recent wait events' => array('WAITS','select \'Top 5 events\' from dual','=TopRecentWaits'), 179 // 'Historical wait SQL' => array('WAITS','select \'Last 2 days\' from dual','=TopHistoricalWaits'), -- requires AWR license 180 'Backup', 181 'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', '=LogMode'), 182 183 'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'), 184 'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value) 185 FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''), 186 187 '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.'), 188 189 'Flashback Usage' => array('BACKUP', "select nvl('-','Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')", '=FlashUsage', 'Flashback area usage.'), 190 191 '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. Recommended set to x2 or x3 times the frequency of your full backup.'), 192 'Recent RMAN Jobs' => array('BACKUP', "select '-' from dual", "=RMAN"), 193 194 // '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.'), 195 'Storage', 'Tablespaces' => array('TABLESPACE', "select '-' from dual", "=TableSpace"), 196 false 197 198 ); 199 200 201 function perf_oci8(&$conn) 202 { 203 global $gSQLBlockRows; 204 205 $gSQLBlockRows = 1000; 206 $savelog = $conn->LogSQL(false); 207 $this->version = $conn->ServerInfo(); 208 $conn->LogSQL($savelog); 209 $this->conn = $conn; 210 } 211 212 function LogMode() 213 { 214 $mode = $this->conn->GetOne("select log_mode from v\$database"); 215 216 if ($mode == 'ARCHIVELOG') return 'To turn off archivelog:<br> 217 <pre><font size=-2> 218 SQLPLUS> connect sys as sysdba; 219 SQLPLUS> shutdown immediate; 220 221 SQLPLUS> startup mount exclusive; 222 SQLPLUS> alter database noarchivelog; 223 SQLPLUS> alter database open; 224 </font></pre>'; 225 226 return 'To turn on archivelog:<br> 227 <pre><font size=-2> 228 SQLPLUS> connect sys as sysdba; 229 SQLPLUS> shutdown immediate; 230 231 SQLPLUS> startup mount exclusive; 232 SQLPLUS> alter database archivelog; 233 SQLPLUS> archive log start; 234 SQLPLUS> alter database open; 235 </font></pre>'; 236 } 237 238 function TopRecentWaits() 239 { 240 241 $rs = $this->conn->Execute("select * from ( 242 select event, round(100*time_waited/(select sum(time_waited) from v\$system_event where wait_class <> 'Idle'),1) \"% Wait\", 243 total_waits,time_waited, average_wait,wait_class from v\$system_event where wait_class <> 'Idle' order by 2 desc 244 ) where rownum <=5"); 245 246 $ret = rs2html($rs,false,false,false,false); 247 return " <p>".$ret." </p>"; 248 249 } 250 251 function TopHistoricalWaits() 252 { 253 $days = 2; 254 255 $rs = $this->conn->Execute("select * from ( SELECT 256 b.wait_class,B.NAME, 257 round(sum(wait_time+TIME_WAITED)/1000000) waitsecs, 258 parsing_schema_name, 259 C.SQL_TEXT, a.sql_id 260 FROM V\$ACTIVE_SESSION_HISTORY A 261 join V\$EVENT_NAME B on A.EVENT# = B.EVENT# 262 join V\$SQLAREA C on A.SQL_ID = C.SQL_ID 263 WHERE A.SAMPLE_TIME BETWEEN sysdate-$days and sysdate 264 and parsing_schema_name not in ('SYS','SYSMAN','DBSNMP','SYSTEM') 265 GROUP BY b.wait_class,parsing_schema_name,C.SQL_TEXT, B.NAME,A.sql_id 266 order by 3 desc) where rownum <=10"); 267 268 $ret = rs2html($rs,false,false,false,false); 269 return " <p>".$ret." </p>"; 270 271 } 272 273 function TableSpace() 274 { 275 276 $rs = $this->conn->Execute( 277 "select tablespace_name,round(sum(bytes)/1024/1024) as Used_MB,round(sum(maxbytes)/1024/1024) as Max_MB, round(sum(bytes)/sum(maxbytes),4) * 100 as PCT 278 from dba_data_files 279 group by tablespace_name order by 2 desc"); 280 281 $ret = "<p><b>Tablespace</b>".rs2html($rs,false,false,false,false); 282 283 $rs = $this->conn->Execute("select * from dba_data_files order by tablespace_name, 1"); 284 $ret .= "<p><b>Datafile</b>".rs2html($rs,false,false,false,false); 285 286 return " <p>".$ret." </p>"; 287 } 288 289 function RMAN() 290 { 291 $rs = $this->conn->Execute("select * from (select start_time, end_time, operation, status, mbytes_processed, output_device_type 292 from V\$RMAN_STATUS order by start_time desc) where rownum <=10"); 293 294 $ret = rs2html($rs,false,false,false,false); 295 return " <p>".$ret." </p>"; 296 297 } 298 299 function DynMemoryUsage() 300 { 301 if (@$this->version['version'] >= 11) { 302 $rs = $this->conn->Execute("select component, current_size/1024./1024 as \"CurrSize (M)\" from V\$MEMORY_DYNAMIC_COMPONENTS"); 303 304 } else 305 $rs = $this->conn->Execute("select name, round(bytes/1024./1024,2) as \"CurrSize (M)\" from V\$sgainfo"); 306 307 308 $ret = rs2html($rs,false,false,false,false); 309 return " <p>".$ret." </p>"; 310 } 311 312 function FlashUsage() 313 { 314 $rs = $this->conn->Execute("select * from V\$FLASH_RECOVERY_AREA_USAGE"); 315 $ret = rs2html($rs,false,false,false,false); 316 return " <p>".$ret." </p>"; 317 } 318 319 function WarnPageCost($val) 320 { 321 if ($val == 100 && $this->version['version'] < 10) $s = '<font color=red><b>Too High</b>. </font>'; 322 else $s = ''; 323 324 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>. '; 325 } 326 327 function WarnIndexCost($val) 328 { 329 if ($val == 0 && $this->version['version'] < 10) $s = '<font color=red><b>Too Low</b>. </font>'; 330 else $s = ''; 331 332 return $s.'Percentage of indexed data blocks expected in the cache. 333 Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0. 334 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.'; 335 } 336 337 function PGA() 338 { 339 340 //if ($this->version['version'] < 9) return 'Oracle 9i or later required'; 341 } 342 343 function PGA_Advice() 344 { 345 $t = "<h3>PGA Advice Estimate</h3>"; 346 if ($this->version['version'] < 9) return $t.'Oracle 9i or later required'; 347 348 $rs = $this->conn->Execute('select a.MB, 349 case when a.targ = 1 then \'<<= Current \' 350 when a.targ < 1 or a.pct <= b.pct then null 351 else 352 \'- BETTER than Current by \'||round(a.pct/b.pct*100-100,2)||\'%\' end as "Percent Improved", 353 a.targ as "PGA Size Factor",a.pct "% Perf" 354 from 355 (select round(pga_target_for_estimate/1024.0/1024.0,0) MB, 356 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 357 from v$pga_target_advice) a left join 358 (select round(pga_target_for_estimate/1024.0/1024.0,0) MB, 359 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r 360 from v$pga_target_advice) b on 361 a.r = b.r+1 where 362 b.pct < 100'); 363 if (!$rs) return $t."Only in 9i or later"; 364 // $rs->Close(); 365 if ($rs->EOF) return $t."PGA could be too big"; 366 367 return $t.rs2html($rs,false,false,true,false); 368 } 369 370 function Explain($sql,$partial=false) 371 { 372 $savelog = $this->conn->LogSQL(false); 373 $rs = $this->conn->SelectLimit("select ID FROM PLAN_TABLE"); 374 if (!$rs) { 375 echo "<p><b>Missing PLAN_TABLE</b></p> 376 <pre> 377 CREATE TABLE PLAN_TABLE ( 378 STATEMENT_ID VARCHAR2(30), 379 TIMESTAMP DATE, 380 REMARKS VARCHAR2(80), 381 OPERATION VARCHAR2(30), 382 OPTIONS VARCHAR2(30), 383 OBJECT_NODE VARCHAR2(128), 384 OBJECT_OWNER VARCHAR2(30), 385 OBJECT_NAME VARCHAR2(30), 386 OBJECT_INSTANCE NUMBER(38), 387 OBJECT_TYPE VARCHAR2(30), 388 OPTIMIZER VARCHAR2(255), 389 SEARCH_COLUMNS NUMBER, 390 ID NUMBER(38), 391 PARENT_ID NUMBER(38), 392 POSITION NUMBER(38), 393 COST NUMBER(38), 394 CARDINALITY NUMBER(38), 395 BYTES NUMBER(38), 396 OTHER_TAG VARCHAR2(255), 397 PARTITION_START VARCHAR2(255), 398 PARTITION_STOP VARCHAR2(255), 399 PARTITION_ID NUMBER(38), 400 OTHER LONG, 401 DISTRIBUTION VARCHAR2(30) 402 ); 403 </pre>"; 404 return false; 405 } 406 407 $rs->Close(); 408 // $this->conn->debug=1; 409 410 if ($partial) { 411 $sqlq = $this->conn->qstr($sql.'%'); 412 $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq"); 413 if ($arr) { 414 foreach($arr as $row) { 415 $sql = reset($row); 416 if (crc32($sql) == $partial) break; 417 } 418 } 419 } 420 421 $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>"; 422 423 $this->conn->BeginTrans(); 424 $id = "ADODB ".microtime(); 425 426 $rs = $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql"); 427 $m = $this->conn->ErrorMsg(); 428 if ($m) { 429 $this->conn->RollbackTrans(); 430 $this->conn->LogSQL($savelog); 431 $s .= "<p>$m</p>"; 432 return $s; 433 } 434 $rs = $this->conn->Execute(" 435 select 436 '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>' as Operation, 437 object_name,COST,CARDINALITY,bytes 438 FROM plan_table 439 START WITH id = 0 and STATEMENT_ID='$id' 440 CONNECT BY prior id=parent_id and statement_id='$id'"); 441 442 $s .= rs2html($rs,false,false,false,false); 443 $this->conn->RollbackTrans(); 444 $this->conn->LogSQL($savelog); 445 $s .= $this->Tracer($sql,$partial); 446 return $s; 447 } 448 449 function CheckMemory() 450 { 451 if ($this->version['version'] < 9) return 'Oracle 9i or later required'; 452 453 $rs = $this->conn->Execute(" 454 select a.name Buffer_Pool, b.size_for_estimate as cache_mb_estimate, 455 case when b.size_factor=1 then 456 '<<= Current' 457 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0.001 and b.estd_physical_read_factor<1 then 458 '- BETTER than current by ' || round((1-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) || '%' 459 else ' ' end as RATING, 460 b.estd_physical_read_factor \"Phys. Reads Factor\", 461 round((a.estd_physical_read_factor-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) as \"% Improve\" 462 from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) a , 463 (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) b 464 where a.r = b.r-1 and a.name = b.name 465 "); 466 if (!$rs) return false; 467 468 /* 469 The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size 470 */ 471 $s = "<h3>Data Cache Advice Estimate</h3>"; 472 if ($rs->EOF) { 473 $s .= "<p>Cache that is 50% of current size is still too big</p>"; 474 } else { 475 $s .= "Ideal size of Data Cache is when %BETTER gets close to zero."; 476 $s .= rs2html($rs,false,false,false,false); 477 } 478 return $s.$this->PGA_Advice(); 479 } 480 481 /* 482 Generate html for suspicious/expensive sql 483 */ 484 function tohtml(&$rs,$type) 485 { 486 $o1 = $rs->FetchField(0); 487 $o2 = $rs->FetchField(1); 488 $o3 = $rs->FetchField(2); 489 if ($rs->EOF) return '<p>None found</p>'; 490 $check = ''; 491 $sql = ''; 492 $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>'; 493 while (!$rs->EOF) { 494 if ($check != $rs->fields[0].'::'.$rs->fields[1]) { 495 if ($check) { 496 $carr = explode('::',$check); 497 $prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">'; 498 $suffix = '</a>'; 499 if (strlen($prefix)>2000) { 500 $prefix = ''; 501 $suffix = ''; 502 } 503 504 $s .= "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>'; 505 } 506 $sql = $rs->fields[2]; 507 $check = $rs->fields[0].'::'.$rs->fields[1]; 508 } else 509 $sql .= $rs->fields[2]; 510 if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1); 511 $rs->MoveNext(); 512 } 513 $rs->Close(); 514 515 $carr = explode('::',$check); 516 $prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">'; 517 $suffix = '</a>'; 518 if (strlen($prefix)>2000) { 519 $prefix = ''; 520 $suffix = ''; 521 } 522 $s .= "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>'; 523 524 return $s."</table>\n\n"; 525 } 526 527 // code thanks to Ixora. 528 // http://www.ixora.com.au/scripts/query_opt.htm 529 // requires oracle 8.1.7 or later 530 function SuspiciousSQL($numsql=10) 531 { 532 $sql = " 533 select 534 substr(to_char(s.pct, '99.00'), 2) || '%' load, 535 s.executions executes, 536 p.sql_text 537 from 538 ( 539 select 540 address, 541 buffer_gets, 542 executions, 543 pct, 544 rank() over (order by buffer_gets desc) ranking 545 from 546 ( 547 select 548 address, 549 buffer_gets, 550 executions, 551 100 * ratio_to_report(buffer_gets) over () pct 552 from 553 sys.v_\$sql 554 where 555 command_type != 47 and module != 'T.O.A.D.' 556 ) 557 where 558 buffer_gets > 50 * executions 559 ) s, 560 sys.v_\$sqltext p 561 where 562 s.ranking <= $numsql and 563 p.address = s.address 564 order by 565 1 desc, s.address, p.piece"; 566 567 global $ADODB_CACHE_MODE; 568 if (isset($_GET['expsixora']) && isset($_GET['sql'])) { 569 $partial = empty($_GET['part']); 570 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 571 } 572 573 if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql); 574 575 $s = ''; 576 $timer = time(); 577 $s .= $this->_SuspiciousSQL($numsql); 578 $timer = time() - $timer; 579 580 if ($timer > $this->noShowIxora) return $s; 581 $s .= '<p>'; 582 583 $save = $ADODB_CACHE_MODE; 584 $ADODB_CACHE_MODE = ADODB_FETCH_NUM; 585 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 586 587 $savelog = $this->conn->LogSQL(false); 588 $rs = $this->conn->SelectLimit($sql); 589 $this->conn->LogSQL($savelog); 590 591 if (isset($savem)) $this->conn->SetFetchMode($savem); 592 $ADODB_CACHE_MODE = $save; 593 if ($rs) { 594 $s .= "\n<h3>Ixora Suspicious SQL</h3>"; 595 $s .= $this->tohtml($rs,'expsixora'); 596 } 597 598 return $s; 599 } 600 601 // code thanks to Ixora. 602 // http://www.ixora.com.au/scripts/query_opt.htm 603 // requires oracle 8.1.7 or later 604 function ExpensiveSQL($numsql = 10) 605 { 606 $sql = " 607 select 608 substr(to_char(s.pct, '99.00'), 2) || '%' load, 609 s.executions executes, 610 p.sql_text 611 from 612 ( 613 select 614 address, 615 disk_reads, 616 executions, 617 pct, 618 rank() over (order by disk_reads desc) ranking 619 from 620 ( 621 select 622 address, 623 disk_reads, 624 executions, 625 100 * ratio_to_report(disk_reads) over () pct 626 from 627 sys.v_\$sql 628 where 629 command_type != 47 and module != 'T.O.A.D.' 630 ) 631 where 632 disk_reads > 50 * executions 633 ) s, 634 sys.v_\$sqltext p 635 where 636 s.ranking <= $numsql and 637 p.address = s.address 638 order by 639 1 desc, s.address, p.piece 640 "; 641 global $ADODB_CACHE_MODE; 642 if (isset($_GET['expeixora']) && isset($_GET['sql'])) { 643 $partial = empty($_GET['part']); 644 echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n"; 645 } 646 if (isset($_GET['sql'])) { 647 $var = $this->_ExpensiveSQL($numsql); 648 return $var; 649 } 650 651 $s = ''; 652 $timer = time(); 653 $s .= $this->_ExpensiveSQL($numsql); 654 $timer = time() - $timer; 655 if ($timer > $this->noShowIxora) return $s; 656 657 $s .= '<p>'; 658 $save = $ADODB_CACHE_MODE; 659 $ADODB_CACHE_MODE = ADODB_FETCH_NUM; 660 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false); 661 662 $savelog = $this->conn->LogSQL(false); 663 $rs = $this->conn->Execute($sql); 664 $this->conn->LogSQL($savelog); 665 666 if (isset($savem)) $this->conn->SetFetchMode($savem); 667 $ADODB_CACHE_MODE = $save; 668 669 if ($rs) { 670 $s .= "\n<h3>Ixora Expensive SQL</h3>"; 671 $s .= $this->tohtml($rs,'expeixora'); 672 } 673 674 return $s; 675 } 676 677 function clearsql() 678 { 679 $perf_table = adodb_perf::table(); 680 // using the naive "delete from $perf_table where created<".$this->conn->sysTimeStamp will cause the table to lock, possibly 681 // for a long time 682 $sql = 683 "DECLARE cnt pls_integer; 684 BEGIN 685 cnt := 0; 686 FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE created<SYSDATE) 687 LOOP 688 cnt := cnt + 1; 689 DELETE FROM $perf_table WHERE ROWID=rec.rr; 690 IF cnt = 1000 THEN 691 COMMIT; 692 cnt := 0; 693 END IF; 694 END LOOP; 695 commit; 696 END;"; 697 698 $ok = $this->conn->Execute($sql); 699 } 700 701 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |