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