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