Update to upstream version 4.94
[Packages/TYPO3.CMS.git] / typo3 / sysext / adodb / adodb / perf / perf-oci8.inc.php
1 <?php
2 /*
3 V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved.
4 Released under both BSD license and Lesser GPL library license.
5 Whenever there is any discrepancy between the two licenses,
6 the BSD license will take precedence. See License.txt.
7 Set tabs to 4 for best viewing.
8
9 Latest version is available at http://adodb.sourceforge.net
10
11 Library for basic performance monitoring and tuning
12
13 */
14
15 // security - hide paths
16 if (!defined('ADODB_DIR')) die();
17
18 class perf_oci8 extends ADODB_perf{
19
20 var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
21 group by segment_name,tablespace_name";
22
23 var $version;
24 var $createTableSQL = "CREATE TABLE adodb_logsql (
25 created date NOT NULL,
26 sql0 varchar(250) NOT NULL,
27 sql1 varchar(4000) NOT NULL,
28 params varchar(4000),
29 tracer varchar(4000),
30 timer decimal(16,6) NOT NULL
31 )";
32
33 var $settings = array(
34 'Ratios',
35 'data cache hit ratio' => array('RATIOH',
36 "select round((1-(phy.value / (cur.value + con.value)))*100,2)
37 from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
38 where cur.name = 'db block gets' and
39 con.name = 'consistent gets' and
40 phy.name = 'physical reads'",
41 '=WarnCacheRatio'),
42
43 'sql cache hit ratio' => array( 'RATIOH',
44 'select round(100*(sum(pins)-sum(reloads))/sum(pins),2) from v$librarycache',
45 'increase <i>shared_pool_size</i> if too ratio low'),
46
47 'datadict cache hit ratio' => array('RATIOH',
48 "select
49 round((1 - (sum(getmisses) / (sum(gets) +
50 sum(getmisses))))*100,2)
51 from v\$rowcache",
52 'increase <i>shared_pool_size</i> if too ratio low'),
53
54 'memory sort ratio' => array('RATIOH',
55 "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
56 0,1,(a.VALUE + b.VALUE)),2)
57 FROM v\$sysstat a,
58 v\$sysstat b
59 WHERE a.name = 'sorts (disk)'
60 AND b.name = 'sorts (memory)'",
61 "% of memory sorts compared to disk sorts - should be over 95%"),
62
63 'IO',
64 'data reads' => array('IO',
65 "select value from v\$sysstat where name='physical reads'"),
66
67 'data writes' => array('IO',
68 "select value from v\$sysstat where name='physical writes'"),
69
70 'Data Cache',
71 'data cache buffers' => array( 'DATAC',
72 "select a.value/b.value from v\$parameter a, v\$parameter b
73 where a.name = 'db_cache_size' and b.name= 'db_block_size'",
74 'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
75 'data cache blocksize' => array('DATAC',
76 "select value from v\$parameter where name='db_block_size'",
77 '' ),
78 'Memory Pools',
79 'data cache size' => array('DATAC',
80 "select value from v\$parameter where name = 'db_cache_size'",
81 'db_cache_size' ),
82 'shared pool size' => array('DATAC',
83 "select value from v\$parameter where name = 'shared_pool_size'",
84 'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
85 'java pool size' => array('DATAJ',
86 "select value from v\$parameter where name = 'java_pool_size'",
87 'java_pool_size' ),
88 'large pool buffer size' => array('CACHE',
89 "select value from v\$parameter where name='large_pool_size'",
90 'this pool is for large mem allocations (not because it is larger than shared pool), for MTS sessions, parallel queries, io buffers (large_pool_size) ' ),
91
92 'pga buffer size' => array('CACHE',
93 "select value from v\$parameter where name='pga_aggregate_target'",
94 'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ),
95
96
97 'Connections',
98 'current connections' => array('SESS',
99 'select count(*) from sys.v_$session where username is not null',
100 ''),
101 'max connections' => array( 'SESS',
102 "select value from v\$parameter where name='sessions'",
103 ''),
104
105 'Memory Utilization',
106 'data cache utilization ratio' => array('RATIOU',
107 "select round((1-bytes/sgasize)*100, 2)
108 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
109 where name = 'free memory' and pool = 'shared pool'",
110 'Percentage of data cache actually in use - should be over 85%'),
111
112 'shared pool utilization ratio' => array('RATIOU',
113 'select round((sga.bytes/p.value)*100,2)
114 from v$sgastat sga, v$parameter p
115 where sga.name = \'free memory\' and sga.pool = \'shared pool\'
116 and p.name = \'shared_pool_size\'',
117 'Percentage of shared pool actually used - too low is bad, too high is worse'),
118
119 'large pool utilization ratio' => array('RATIOU',
120 "select round((1-bytes/sgasize)*100, 2)
121 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
122 where name = 'free memory' and pool = 'large pool'",
123 'Percentage of large_pool actually in use - too low is bad, too high is worse'),
124 'sort buffer size' => array('CACHE',
125 "select value from v\$parameter where name='sort_area_size'",
126 'max in-mem sort_area_size (per query), uses memory in pga' ),
127
128 'pga usage at peak' => array('RATIOU',
129 '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),
130 'Transactions',
131 'rollback segments' => array('ROLLBACK',
132 "select count(*) from sys.v_\$rollstat",
133 ''),
134
135 'peak transactions' => array('ROLLBACK',
136 "select max_utilization tx_hwm
137 from sys.v_\$resource_limit
138 where resource_name = 'transactions'",
139 'Taken from high-water-mark'),
140 'max transactions' => array('ROLLBACK',
141 "select value from v\$parameter where name = 'transactions'",
142 'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
143 'Parameters',
144 'cursor sharing' => array('CURSOR',
145 "select value from v\$parameter where name = 'cursor_sharing'",
146 'Cursor reuse strategy. Recommended is FORCE (8i+) or SIMILAR (9i+). See <a href=http://www.praetoriate.com/oracle_tips_cursor_sharing.htm>cursor_sharing</a>.'),
147 /*
148 'cursor reuse' => array('CURSOR',
149 "select count(*) from (select sql_text_wo_constants, count(*)
150 from t1
151 group by sql_text_wo_constants
152 having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
153 'index cache cost' => array('COST',
154 "select value from v\$parameter where name = 'optimizer_index_caching'",
155 '=WarnIndexCost'),
156 'random page cost' => array('COST',
157 "select value from v\$parameter where name = 'optimizer_index_cost_adj'",
158 '=WarnPageCost'),
159
160 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();
266
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 }
275 $rs =& $this->conn->Execute("
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 '&lt;&lt;= 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];
349 if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
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
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";
410 }
411
412 if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
413
414 $s = '';
415 $s .= $this->_SuspiciousSQL($numsql);
416 $s .= '<p>';
417
418 $save = $ADODB_CACHE_MODE;
419 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
420 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
421
422 $savelog = $this->conn->LogSQL(false);
423 $rs =& $this->conn->SelectLimit($sql);
424 $this->conn->LogSQL($savelog);
425
426 if (isset($savem)) $this->conn->SetFetchMode($savem);
427 $ADODB_CACHE_MODE = $save;
428 if ($rs) {
429 $s .= "\n<h3>Ixora Suspicious SQL</h3>";
430 $s .= $this->tohtml($rs,'expsixora');
431 }
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 ";
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";
480 }
481 if (isset($_GET['sql'])) {
482 $var = $this->_ExpensiveSQL($numsql);
483 return $var;
484 }
485
486 $s = '';
487 $s .= $this->_ExpensiveSQL($numsql);
488 $s .= '<p>';
489 $save = $ADODB_CACHE_MODE;
490 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
491 if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
492
493 $savelog = $this->conn->LogSQL(false);
494 $rs =& $this->conn->Execute($sql);
495 $this->conn->LogSQL($savelog);
496
497 if (isset($savem)) $this->conn->SetFetchMode($savem);
498 $ADODB_CACHE_MODE = $save;
499
500 if ($rs) {
501 $s .= "\n<h3>Ixora Expensive SQL</h3>";
502 $s .= $this->tohtml($rs,'expeixora');
503 }
504
505 return $s;
506 }
507
508 }
509 ?>