16 if (!defined(
'ADODB_DIR')) die();
23 var
$tablesSQL =
"select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
24 group by segment_name,tablespace_name";
29 created date NOT NULL,
30 sql0 varchar(250) NOT NULL,
31 sql1 varchar(4000) NOT NULL,
34 timer decimal(16,6) NOT NULL
39 'data cache hit ratio' => array(
'RATIOH',
40 "select round((1-(phy.value / (cur.value + con.value)))*100,2)
41 from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
42 where cur.name = 'db block gets' and
43 con.name = 'consistent gets' and
44 phy.name = 'physical reads'",
47 'sql cache hit ratio' => array(
'RATIOH',
48 'select round(100*(sum(pins)-sum(reloads))/sum(pins),2) from v$librarycache',
49 'increase <i>shared_pool_size</i> if too ratio low'),
51 'datadict cache hit ratio' => array(
'RATIOH',
53 round((1 - (sum(getmisses) / (sum(gets) +
54 sum(getmisses))))*100,2)
56 'increase <i>shared_pool_size</i> if too ratio low'),
58 'memory sort ratio' => array(
'RATIOH',
59 "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
60 0,1,(a.VALUE + b.VALUE)),2)
63 WHERE a.name = 'sorts (disk)'
64 AND b.name = 'sorts (memory)'",
65 "% of memory sorts compared to disk sorts - should be over 95%"),
68 'data reads' => array(
'IO',
69 "select value from v\$sysstat where name='physical reads'"),
71 'data writes' => array(
'IO',
72 "select value from v\$sysstat where name='physical writes'"),
76 'data cache buffers' => array(
'DATAC',
77 "select a.value/b.value from v\$parameter a, v\$parameter b
78 where a.name = 'db_cache_size' and b.name= 'db_block_size'",
79 'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
80 'data cache blocksize' => array(
'DATAC',
81 "select value from v\$parameter where name='db_block_size'",
85 'Mem Max Target (11g+)' => array(
'DATAC',
86 "select value from v\$parameter where name = 'memory_max_target'",
87 'The memory_max_size is the maximum value to which memory_target can be set.' ),
88 'Memory target (11g+)' => array(
'DATAC',
89 "select value from v\$parameter where name = 'memory_target'",
90 'If memory_target is defined then SGA and PGA targets are consolidated into one memory_target.' ),
91 'SGA Max Size' => array(
'DATAC',
92 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_max_size'",
93 'The sga_max_size is the maximum value to which sga_target can be set.' ),
94 'SGA target' => array(
'DATAC',
95 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'sga_target'",
96 '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.' ),
97 'PGA aggr target' => array(
'DATAC',
98 "select nvl(value,0)/1024.0/1024 || 'M' from v\$parameter where name = 'pga_aggregate_target'",
99 '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.' ),
100 'data cache size' => array(
'DATAC',
101 "select value from v\$parameter where name = 'db_cache_size'",
103 'shared pool size' => array(
'DATAC',
104 "select value from v\$parameter where name = 'shared_pool_size'",
105 'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
106 'java pool size' => array(
'DATAJ',
107 "select value from v\$parameter where name = 'java_pool_size'",
109 'large pool buffer size' => array(
'CACHE',
110 "select value from v\$parameter where name='large_pool_size'",
111 '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) ' ),
113 'dynamic memory usage' => array(
'CACHE',
"select '-' from dual",
'=DynMemoryUsage'),
116 'current connections' => array(
'SESS',
117 'select count(*) from sys.v_$session where username is not null',
119 'max connections' => array(
'SESS',
120 "select value from v\$parameter where name='sessions'",
123 'Memory Utilization',
124 'data cache utilization ratio' => array(
'RATIOU',
125 "select round((1-bytes/sgasize)*100, 2)
126 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
127 where name = 'free memory' and pool = 'shared pool'",
128 'Percentage of data cache actually in use - should be over 85%'),
130 'shared pool utilization ratio' => array(
'RATIOU',
131 'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2)
132 from v$sgastat sga, v$parameter p
133 where sga.name = \'free memory\' and sga.pool = \'shared pool\'
134 and p.name = \'shared_pool_size\'',
135 'Percentage of shared pool actually used - too low is bad, too high is worse'),
137 'large pool utilization ratio' => array(
'RATIOU',
138 "select round((1-bytes/sgasize)*100, 2)
139 from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
140 where name = 'free memory' and pool = 'large pool'",
141 'Percentage of large_pool actually in use - too low is bad, too high is worse'),
142 'sort buffer size' => array(
'CACHE',
143 "select value from v\$parameter where name='sort_area_size'",
144 'max in-mem sort_area_size (per query), uses memory in pga' ),
149 'rollback segments' => array(
'ROLLBACK',
150 "select count(*) from sys.v_\$rollstat",
153 'peak transactions' => array(
'ROLLBACK',
154 "select max_utilization tx_hwm
155 from sys.v_\$resource_limit
156 where resource_name = 'transactions'",
157 'Taken from high-water-mark'),
158 'max transactions' => array(
'ROLLBACK',
159 "select value from v\$parameter where name = 'transactions'",
160 'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
162 'cursor sharing' => array(
'CURSOR',
163 "select value from v\$parameter where name = 'cursor_sharing'",
164 '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>.'),
171 'index cache cost' => array(
'COST',
172 "select value from v\$parameter where name = 'optimizer_index_caching'",
174 'random page cost' => array(
'COST',
175 "select value from v\$parameter where name = 'optimizer_index_cost_adj'",
178 'Recent wait events' => array(
'WAITS',
'select \'Top 5 events\' from dual',
'=TopRecentWaits'),
181 'Achivelog Mode' => array(
'BACKUP',
'select log_mode from v$database',
'=LogMode'),
183 'DBID' => array(
'BACKUP',
'select dbid from v$database',
'Primary key of database, used for recovery with an RMAN Recovery Catalog'),
184 'Archive Log Dest' => array(
'BACKUP',
"SELECT NVL(v1.value,v2.value)
185 FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'",
''),
187 '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.'),
189 'Flashback Usage' => array(
'BACKUP',
"select nvl('-','Flashback Area not used') from v\$parameter where name=lower('DB_RECOVERY_FILE_DEST')",
'=FlashUsage',
'Flashback area usage.'),
191 '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.'),
192 'Recent RMAN Jobs' => array(
'BACKUP',
"select '-' from dual",
"=RMAN"),
195 'Storage',
'Tablespaces' => array(
'TABLESPACE',
"select '-' from dual",
"=TableSpace"),
205 $gSQLBlockRows = 1000;
206 $savelog = $conn->LogSQL(
false);
207 $this->version = $conn->ServerInfo();
208 $conn->LogSQL($savelog);
214 $mode = $this->conn->GetOne(
"select log_mode from v\$database");
216 if ($mode ==
'ARCHIVELOG')
return 'To turn off archivelog:<br>
218 SQLPLUS> connect sys as sysdba;
219 SQLPLUS> shutdown immediate;
221 SQLPLUS> startup mount exclusive;
222 SQLPLUS> alter database noarchivelog;
223 SQLPLUS> alter database open;
226 return 'To turn on archivelog:<br>
228 SQLPLUS> connect sys as sysdba;
229 SQLPLUS> shutdown immediate;
231 SQLPLUS> startup mount exclusive;
232 SQLPLUS> alter database archivelog;
233 SQLPLUS> archive log start;
234 SQLPLUS> alter database open;
241 $rs = $this->conn->Execute(
"select * from (
242 select event, round(100*time_waited/(select sum(time_waited) from v\$system_event where wait_class <> 'Idle'),1) \"% Wait\",
243 total_waits,time_waited, average_wait,wait_class from v\$system_event where wait_class <> 'Idle' order by 2 desc
244 ) where rownum <=5");
246 $ret =
rs2html($rs,
false,
false,
false,
false);
247 return " <p>".$ret.
" </p>";
255 $rs = $this->conn->Execute(
"select * from ( SELECT
257 round(sum(wait_time+TIME_WAITED)/1000000) waitsecs,
260 FROM V\$ACTIVE_SESSION_HISTORY A
261 join V\$EVENT_NAME B on A.EVENT# = B.EVENT#
262 join V\$SQLAREA C on A.SQL_ID = C.SQL_ID
263 WHERE A.SAMPLE_TIME BETWEEN sysdate-$days and sysdate
264 and parsing_schema_name not in ('SYS','SYSMAN','DBSNMP','SYSTEM')
265 GROUP BY b.wait_class,parsing_schema_name,C.SQL_TEXT, B.NAME,A.sql_id
266 order by 3 desc) where rownum <=10");
268 $ret =
rs2html($rs,
false,
false,
false,
false);
269 return " <p>".$ret.
" </p>";
276 $rs = $this->conn->Execute(
277 "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
279 group by tablespace_name order by 2 desc");
281 $ret =
"<p><b>Tablespace</b>".rs2html($rs,
false,
false,
false,
false);
283 $rs = $this->conn->Execute(
"select * from dba_data_files order by tablespace_name, 1");
284 $ret .=
"<p><b>Datafile</b>".rs2html($rs,
false,
false,
false,
false);
286 return " <p>".$ret.
" </p>";
291 $rs = $this->conn->Execute(
"select * from (select start_time, end_time, operation, status, mbytes_processed, output_device_type
292 from V\$RMAN_STATUS order by start_time desc) where rownum <=10");
294 $ret =
rs2html($rs,
false,
false,
false,
false);
295 return " <p>".$ret.
" </p>";
301 if (@$this->version[
'version'] >= 11) {
302 $rs = $this->conn->Execute(
"select component, current_size/1024./1024 as \"CurrSize (M)\" from V\$MEMORY_DYNAMIC_COMPONENTS");
305 $rs = $this->conn->Execute(
"select name, round(bytes/1024./1024,2) as \"CurrSize (M)\" from V\$sgainfo");
308 $ret =
rs2html($rs,
false,
false,
false,
false);
309 return " <p>".$ret.
" </p>";
314 $rs = $this->conn->Execute(
"select * from V\$FLASH_RECOVERY_AREA_USAGE");
315 $ret =
rs2html($rs,
false,
false,
false,
false);
316 return " <p>".$ret.
" </p>";
321 if ($val == 100 && $this->version[
'version'] < 10) $s =
'<font color=red><b>Too High</b>. </font>';
324 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>. ';
329 if ($val == 0 && $this->version[
'version'] < 10) $s =
'<font color=red><b>Too Low</b>. </font>';
332 return $s.
'Percentage of indexed data blocks expected in the cache.
333 Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0.
334 See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
345 $t =
"<h3>PGA Advice Estimate</h3>";
346 if ($this->version[
'version'] < 9)
return $t.
'Oracle 9i or later required';
348 $rs = $this->conn->Execute(
'select a.MB,
349 case when a.targ = 1 then \'<<= Current \'
350 when a.targ < 1 or a.pct <= b.pct then null
352 \'- BETTER than Current by \'||round(a.pct/b.pct*100-100,2)||\'%\' end as "Percent Improved",
353 a.targ as "PGA Size Factor",a.pct "% Perf"
355 (select round(pga_target_for_estimate/1024.0/1024.0,0) MB,
356 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
357 from v$pga_target_advice) a left join
358 (select round(pga_target_for_estimate/1024.0/1024.0,0) MB,
359 pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
360 from v$pga_target_advice) b on
363 if (!$rs)
return $t.
"Only in 9i or later";
365 if ($rs->EOF)
return $t.
"PGA could be too big";
367 return $t.rs2html($rs,
false,
false,
true,
false);
372 $savelog = $this->conn->LogSQL(
false);
373 $rs = $this->conn->SelectLimit(
"select ID FROM PLAN_TABLE");
375 echo
"<p><b>Missing PLAN_TABLE</b></p>
377 CREATE TABLE PLAN_TABLE (
378 STATEMENT_ID VARCHAR2(30),
380 REMARKS VARCHAR2(80),
381 OPERATION VARCHAR2(30),
382 OPTIONS VARCHAR2(30),
383 OBJECT_NODE VARCHAR2(128),
384 OBJECT_OWNER VARCHAR2(30),
385 OBJECT_NAME VARCHAR2(30),
386 OBJECT_INSTANCE NUMBER(38),
387 OBJECT_TYPE VARCHAR2(30),
388 OPTIMIZER VARCHAR2(255),
389 SEARCH_COLUMNS NUMBER,
391 PARENT_ID NUMBER(38),
394 CARDINALITY NUMBER(38),
396 OTHER_TAG VARCHAR2(255),
397 PARTITION_START VARCHAR2(255),
398 PARTITION_STOP VARCHAR2(255),
399 PARTITION_ID NUMBER(38),
401 DISTRIBUTION VARCHAR2(30)
411 $sqlq = $this->conn->qstr($sql.
'%');
412 $arr = $this->conn->GetArray(
"select distinct sql1 from adodb_logsql where sql1 like $sqlq");
414 foreach($arr as $row) {
416 if (crc32($sql) == $partial)
break;
421 $s =
"<p><b>Explain</b>: ".htmlspecialchars($sql).
"</p>";
423 $this->conn->BeginTrans();
424 $id =
"ADODB ".microtime();
426 $rs = $this->conn->Execute(
"EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
427 $m = $this->conn->ErrorMsg();
429 $this->conn->RollbackTrans();
430 $this->conn->LogSQL($savelog);
434 $rs = $this->conn->Execute(
"
436 '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>' as Operation,
437 object_name,COST,CARDINALITY,bytes
439 START WITH id = 0 and STATEMENT_ID='$id'
440 CONNECT BY prior id=parent_id and statement_id='$id'");
442 $s .=
rs2html($rs,
false,
false,
false,
false);
443 $this->conn->RollbackTrans();
444 $this->conn->LogSQL($savelog);
445 $s .= $this->Tracer($sql,$partial);
451 if ($this->version[
'version'] < 9)
return 'Oracle 9i or later required';
453 $rs = $this->conn->Execute(
"
454 select a.name Buffer_Pool, b.size_for_estimate as cache_mb_estimate,
455 case when b.size_factor=1 then
457 when a.estd_physical_read_factor-b.estd_physical_read_factor > 0.001 and b.estd_physical_read_factor<1 then
458 '- BETTER than current by ' || round((1-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) || '%'
459 else ' ' end as RATING,
460 b.estd_physical_read_factor \"Phys. Reads Factor\",
461 round((a.estd_physical_read_factor-b.estd_physical_read_factor)/b.estd_physical_read_factor*100,2) as \"% Improve\"
462 from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) a ,
463 (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r,name from v\$db_cache_advice order by name,1) b
464 where a.r = b.r-1 and a.name = b.name
466 if (!$rs)
return false;
471 $s =
"<h3>Data Cache Advice Estimate</h3>";
473 $s .=
"<p>Cache that is 50% of current size is still too big</p>";
475 $s .=
"Ideal size of Data Cache is when %BETTER gets close to zero.";
476 $s .=
rs2html($rs,
false,
false,
false,
false);
478 return $s.$this->PGA_Advice();
486 $o1 = $rs->FetchField(0);
487 $o2 = $rs->FetchField(1);
488 $o3 = $rs->FetchField(2);
489 if ($rs->EOF)
return '<p>None found</p>';
492 $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>';
494 if ($check != $rs->fields[0].
'::'.$rs->fields[1]) {
496 $carr = explode(
'::',$check);
497 $prefix =
"<a href=\"?$type=1&sql=".rawurlencode($sql).
'&x#explain">';
499 if (strlen($prefix)>2000) {
504 $s .=
"\n<tr><td align=right>".$carr[0].
'</td><td align=right>'.$carr[1].
'</td><td>'.$prefix.$sql.$suffix.
'</td></tr>';
506 $sql = $rs->fields[2];
507 $check = $rs->fields[0].
'::'.$rs->fields[1];
509 $sql .= $rs->fields[2];
510 if (substr($sql,strlen($sql)-1) ==
"\0") $sql = substr($sql,0,strlen($sql)-1);
515 $carr = explode(
'::',$check);
516 $prefix =
"<a target=".rand().
" href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).
'&x#explain">';
518 if (strlen($prefix)>2000) {
522 $s .=
"\n<tr><td align=right>".$carr[0].
'</td><td align=right>'.$carr[1].
'</td><td>'.$prefix.$sql.$suffix.
'</td></tr>';
524 return $s.
"</table>\n\n";
534 substr(to_char(s.pct, '99.00'), 2) || '%' load,
535 s.executions executes,
544 rank() over (order by buffer_gets desc) ranking
551 100 * ratio_to_report(buffer_gets) over () pct
555 command_type != 47 and module != 'T.O.A.D.'
558 buffer_gets > 50 * executions
562 s.ranking <= $numsql and
563 p.address = s.address
565 1 desc, s.address, p.piece";
567 global $ADODB_CACHE_MODE;
568 if (isset($_GET[
'expsixora']) && isset($_GET[
'sql'])) {
569 $partial = empty($_GET[
'part']);
570 echo
"<a name=explain></a>".$this->Explain($_GET[
'sql'],$partial).
"\n";
573 if (isset($_GET[
'sql']))
return $this->_SuspiciousSQL($numsql);
577 $s .= $this->_SuspiciousSQL($numsql);
578 $timer = time() - $timer;
580 if ($timer > $this->noShowIxora)
return $s;
583 $save = $ADODB_CACHE_MODE;
584 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
585 if ($this->conn->fetchMode !==
false) $savem = $this->conn->SetFetchMode(
false);
587 $savelog = $this->conn->LogSQL(
false);
588 $rs = $this->conn->SelectLimit($sql);
589 $this->conn->LogSQL($savelog);
591 if (isset($savem)) $this->conn->SetFetchMode($savem);
592 $ADODB_CACHE_MODE = $save;
594 $s .=
"\n<h3>Ixora Suspicious SQL</h3>";
595 $s .= $this->
tohtml($rs,
'expsixora');
608 substr(to_char(s.pct, '99.00'), 2) || '%' load,
609 s.executions executes,
618 rank() over (order by disk_reads desc) ranking
625 100 * ratio_to_report(disk_reads) over () pct
629 command_type != 47 and module != 'T.O.A.D.'
632 disk_reads > 50 * executions
636 s.ranking <= $numsql and
637 p.address = s.address
639 1 desc, s.address, p.piece
641 global $ADODB_CACHE_MODE;
642 if (isset($_GET[
'expeixora']) && isset($_GET[
'sql'])) {
643 $partial = empty($_GET[
'part']);
644 echo
"<a name=explain></a>".$this->Explain($_GET[
'sql'],$partial).
"\n";
646 if (isset($_GET[
'sql'])) {
647 $var = $this->_ExpensiveSQL($numsql);
653 $s .= $this->_ExpensiveSQL($numsql);
654 $timer = time() - $timer;
655 if ($timer > $this->noShowIxora)
return $s;
658 $save = $ADODB_CACHE_MODE;
659 $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
660 if ($this->conn->fetchMode !==
false) $savem = $this->conn->SetFetchMode(
false);
662 $savelog = $this->conn->LogSQL(
false);
663 $rs = $this->conn->Execute($sql);
664 $this->conn->LogSQL($savelog);
666 if (isset($savem)) $this->conn->SetFetchMode($savem);
667 $ADODB_CACHE_MODE = $save;
670 $s .=
"\n<h3>Ixora Expensive SQL</h3>";
671 $s .= $this->
tohtml($rs,
'expeixora');
683 "DECLARE cnt pls_integer;
686 FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE created<SYSDATE)
689 DELETE FROM $perf_table WHERE ROWID=rec.rr;
698 $ok = $this->conn->Execute($sql);