Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.10.x will end 8 November 2021 (12 months).
  • Bug fixes for security issues in 3.10.x will end 9 May 2022 (18 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 310 and 311] [Versions 310 and 400] [Versions 310 and 401] [Versions 310 and 402] [Versions 310 and 403]

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