| 36 |
kaklik |
1 |
<?php
|
|
|
2 |
/*
|
|
|
3 |
V4.80 8 Mar 2006 (c) 2000-2006 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 |
'<<= 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 |
?>
|