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