source: branches/comu-ver2/data/module/adodb/perf/perf-oci8.inc.php @ 18701

Revision 18701, 18.1 KB checked in by nanasess, 14 years ago (diff)

Copyright の更新(#601)
  • Property svn:eol-style set to LF
  • Property svn:keywords set to Id Revision Date
  • Property svn:mime-type set to text/x-httpd-php
Line 
1<?php
2/*
3v4.992 10 Nov 2009  (c) 2000-2010 John Lim (jlim#natsoft.com). 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
16if (!defined('ADODB_DIR')) die();
17
18class perf_oci8 extends ADODB_perf{
19   
20    var $noShowIxora = 15;
21   
22    var $tablesSQL = "select segment_name as \"tablename\", sum(bytes)/1024 as \"size_in_k\",tablespace_name as \"tablespace\",count(*) \"extents\" from sys.user_extents
23       group by segment_name,tablespace_name";
24     
25    var $version;
26    var $createTableSQL = "CREATE TABLE adodb_logsql (
27          created date NOT NULL,
28          sql0 varchar(250) NOT NULL,
29          sql1 varchar(4000) NOT NULL,
30          params varchar(4000),
31          tracer varchar(4000),
32          timer decimal(16,6) NOT NULL
33        )";
34   
35    var $settings = array(
36    'Ratios',
37        'data cache hit ratio' => array('RATIOH',
38            "select round((1-(phy.value / (cur.value + con.value)))*100,2)
39            from v\$sysstat cur, v\$sysstat con, v\$sysstat phy
40            where cur.name = 'db block gets' and
41                  con.name = 'consistent gets' and
42                  phy.name = 'physical reads'",
43            '=WarnCacheRatio'),
44       
45        'sql cache hit ratio' => array( 'RATIOH',
46            'select round(100*(sum(pins)-sum(reloads))/sum(pins),2)  from v$librarycache',
47            'increase <i>shared_pool_size</i> if too ratio low'),
48           
49        'datadict cache hit ratio' => array('RATIOH',
50        "select     
51           round((1 - (sum(getmisses) / (sum(gets) +         
52         sum(getmisses))))*100,2)
53        from  v\$rowcache",
54        'increase <i>shared_pool_size</i> if too ratio low'),
55       
56        'memory sort ratio' => array('RATIOH',
57        "SELECT ROUND((100 * b.VALUE) /DECODE ((a.VALUE + b.VALUE),
58       0,1,(a.VALUE + b.VALUE)),2)
59FROM   v\$sysstat a,
60       v\$sysstat b
61WHERE  a.name = 'sorts (disk)'
62AND    b.name = 'sorts (memory)'",
63    "% of memory sorts compared to disk sorts - should be over 95%"),
64
65    'IO',
66        'data reads' => array('IO',
67        "select value from v\$sysstat where name='physical reads'"),
68   
69    'data writes' => array('IO',
70        "select value from v\$sysstat where name='physical writes'"),
71   
72    'Data Cache',
73        'data cache buffers' => array( 'DATAC',
74        "select a.value/b.value  from v\$parameter a, v\$parameter b
75            where a.name = 'db_cache_size' and b.name= 'db_block_size'",
76            'Number of cache buffers. Tune <i>db_cache_size</i> if the <i>data cache hit ratio</i> is too low.'),
77        'data cache blocksize' => array('DATAC',
78            "select value from v\$parameter where name='db_block_size'",
79            '' ),           
80    'Memory Pools',
81        'data cache size' => array('DATAC',
82            "select value from v\$parameter where name = 'db_cache_size'",
83            'db_cache_size' ),
84        'shared pool size' => array('DATAC',
85            "select value from v\$parameter where name = 'shared_pool_size'",
86            'shared_pool_size, which holds shared sql, stored procedures, dict cache and similar shared structs' ),
87        'java pool size' => array('DATAJ',
88            "select value from v\$parameter where name = 'java_pool_size'",
89            'java_pool_size' ),
90        'large pool buffer size' => array('CACHE',
91            "select value from v\$parameter where name='large_pool_size'",
92            '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) ' ),
93
94        'pga buffer size' => array('CACHE',         
95            "select value from v\$parameter where name='pga_aggregate_target'",
96            'program global area is private memory for sorting, and hash and bitmap merges - since oracle 9i (pga_aggregate_target)' ),
97
98       
99        'Connections',
100        'current connections' => array('SESS',
101            'select count(*) from sys.v_$session where username is not null',
102            ''),
103        'max connections' => array( 'SESS',
104            "select value from v\$parameter where name='sessions'",
105            ''),
106
107    'Memory Utilization',       
108        'data cache utilization ratio' => array('RATIOU',
109            "select round((1-bytes/sgasize)*100, 2)
110            from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
111            where name = 'free memory' and pool = 'shared pool'",
112        'Percentage of data cache actually in use - should be over 85%'),
113       
114                'shared pool utilization ratio' => array('RATIOU',
115        'select round((sga.bytes/case when p.value=0 then sga.bytes else to_number(p.value) end)*100,2)
116        from v$sgastat sga, v$parameter p
117        where sga.name = \'free memory\' and sga.pool = \'shared pool\'
118        and p.name = \'shared_pool_size\'',
119        'Percentage of shared pool actually used - too low is bad, too high is worse'),
120       
121        'large pool utilization ratio' => array('RATIOU',
122            "select round((1-bytes/sgasize)*100, 2)
123            from (select sum(bytes) sgasize from sys.v_\$sgastat) s, sys.v_\$sgastat f
124            where name = 'free memory' and pool = 'large pool'",
125        'Percentage of large_pool actually in use - too low is bad, too high is worse'),
126        'sort buffer size' => array('CACHE',
127            "select value from v\$parameter where name='sort_area_size'",
128            'max in-mem sort_area_size (per query), uses memory in pga' ),
129
130        'pga usage at peak' => array('RATIOU',
131        '=PGA','Mb utilization at peak transactions (requires Oracle 9i+)'),
132    'Transactions',
133        'rollback segments' => array('ROLLBACK',
134            "select count(*) from sys.v_\$rollstat",
135            ''),
136   
137        'peak transactions' => array('ROLLBACK',
138            "select max_utilization  tx_hwm
139            from sys.v_\$resource_limit
140            where resource_name = 'transactions'",
141            'Taken from high-water-mark'),
142        'max transactions' => array('ROLLBACK',
143            "select value from v\$parameter where name = 'transactions'",
144            'max transactions / rollback segments < 3.5 (or transactions_per_rollback_segment)'),
145    'Parameters',   
146        'cursor sharing' => array('CURSOR',
147            "select value from v\$parameter where name = 'cursor_sharing'",
148            '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>.'),
149        /*
150        'cursor reuse' => array('CURSOR',
151            "select count(*) from (select sql_text_wo_constants, count(*)
152  from t1
153 group by sql_text_wo_constants
154having count(*) > 100)",'These are sql statements that should be using bind variables'),*/
155        'index cache cost' => array('COST',
156            "select value from v\$parameter where name = 'optimizer_index_caching'",
157            '=WarnIndexCost'),
158        'random page cost' => array('COST',
159            "select value from v\$parameter where name = 'optimizer_index_cost_adj'",
160            '=WarnPageCost'),
161   
162    'Backup',
163        'Achivelog Mode' => array('BACKUP', 'select log_mode from v$database', 'To turn on archivelog:<br>
164    <pre>
165        SQLPLUS> connect sys as sysdba;
166        SQLPLUS> shutdown immediate;
167
168        SQLPLUS> startup mount exclusive;
169        SQLPLUS> alter database archivelog;
170        SQLPLUS> archive log start;
171        SQLPLUS> alter database open;
172</pre>'),
173   
174        'DBID' => array('BACKUP','select dbid from v$database','Primary key of database, used for recovery with an RMAN Recovery Catalog'),
175        'Archive Log Dest' => array('BACKUP', "SELECT NVL(v1.value,v2.value)
176FROM v\$parameter v1, v\$parameter v2 WHERE v1.name='log_archive_dest' AND v2.name='log_archive_dest_10'", ''),
177   
178    '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.'),
179        '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.'),
180        false
181       
182    );
183   
184   
185    function perf_oci8(&$conn)
186    {
187        $savelog = $conn->LogSQL(false);   
188        $this->version = $conn->ServerInfo();
189        $conn->LogSQL($savelog);   
190        $this->conn =& $conn;
191    }
192   
193    function WarnPageCost($val)
194    {
195        if ($val == 100) $s = '<font color=red><b>Too High</b>. </font>';
196        else $s = '';
197       
198        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>. ';
199    }
200   
201    function WarnIndexCost($val)
202    {
203        if ($val == 0) $s = '<font color=red><b>Too Low</b>. </font>';
204        else $s = '';
205       
206        return $s.'Percentage of indexed data blocks expected in the cache.
207            Recommended is 20 (fast disk array) to 30 (slower hard disks). Default is 0.
208             See <a href=http://www.dba-oracle.com/oracle_tips_cbo_part1.htm>optimizer_index_caching</a>.';
209        }
210   
211    function PGA()
212    {
213        if ($this->version['version'] < 9) return 'Oracle 9i or later required';
214       
215        $rs = $this->conn->Execute("select a.mb,a.targ as pga_size_pct,a.pct from
216       (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
217           pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
218           from v\$pga_target_advice) a left join
219       (select round(pga_target_for_estimate/1024.0/1024.0,0) Mb,
220           pga_target_factor targ,estd_pga_cache_hit_percentage pct,rownum as r
221           from v\$pga_target_advice) b on
222      a.r = b.r+1 where
223        b.pct < 100");
224        if (!$rs) return "Only in 9i or later";
225        $rs->Close();
226        if ($rs->EOF) return "PGA could be too big";
227       
228        return reset($rs->fields);
229    }
230   
231    function Explain($sql,$partial=false)
232    {
233        $savelog = $this->conn->LogSQL(false);
234        $rs =& $this->conn->SelectLimit("select ID FROM PLAN_TABLE");
235        if (!$rs) {
236            echo "<p><b>Missing PLAN_TABLE</b></p>
237<pre>
238CREATE TABLE PLAN_TABLE (
239  STATEMENT_ID                    VARCHAR2(30),
240  TIMESTAMP                       DATE,
241  REMARKS                         VARCHAR2(80),
242  OPERATION                       VARCHAR2(30),
243  OPTIONS                         VARCHAR2(30),
244  OBJECT_NODE                     VARCHAR2(128),
245  OBJECT_OWNER                    VARCHAR2(30),
246  OBJECT_NAME                     VARCHAR2(30),
247  OBJECT_INSTANCE                 NUMBER(38),
248  OBJECT_TYPE                     VARCHAR2(30),
249  OPTIMIZER                       VARCHAR2(255),
250  SEARCH_COLUMNS                  NUMBER,
251  ID                              NUMBER(38),
252  PARENT_ID                       NUMBER(38),
253  POSITION                        NUMBER(38),
254  COST                            NUMBER(38),
255  CARDINALITY                     NUMBER(38),
256  BYTES                           NUMBER(38),
257  OTHER_TAG                       VARCHAR2(255),
258  PARTITION_START                 VARCHAR2(255),
259  PARTITION_STOP                  VARCHAR2(255),
260  PARTITION_ID                    NUMBER(38),
261  OTHER                           LONG,
262  DISTRIBUTION                    VARCHAR2(30)
263);
264</pre>";
265            return false;
266        }
267       
268        $rs->Close();
269    //  $this->conn->debug=1;
270   
271        if ($partial) {
272            $sqlq = $this->conn->qstr($sql.'%');
273            $arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
274            if ($arr) {
275                foreach($arr as $row) {
276                    $sql = reset($row);
277                    if (crc32($sql) == $partial) break;
278                }
279            }
280        }
281       
282        $s = "<p><b>Explain</b>: ".htmlspecialchars($sql)."</p>";   
283       
284        $this->conn->BeginTrans();
285        $id = "ADODB ".microtime();
286
287        $rs =& $this->conn->Execute("EXPLAIN PLAN SET STATEMENT_ID='$id' FOR $sql");
288        $m = $this->conn->ErrorMsg();
289        if ($m) {
290            $this->conn->RollbackTrans();
291            $this->conn->LogSQL($savelog);
292            $s .= "<p>$m</p>";
293            return $s;
294        }
295        $rs =& $this->conn->Execute("
296        select
297  '<pre>'||lpad('--', (level-1)*2,'-') || trim(operation) || ' ' || trim(options)||'</pre>'  as Operation,
298  object_name,COST,CARDINALITY,bytes
299        FROM plan_table
300START WITH id = 0  and STATEMENT_ID='$id' 
301CONNECT BY prior id=parent_id and statement_id='$id'");
302       
303        $s .= rs2html($rs,false,false,false,false);
304        $this->conn->RollbackTrans();
305        $this->conn->LogSQL($savelog);
306        $s .= $this->Tracer($sql,$partial);
307        return $s;
308    }
309   
310   
311    function CheckMemory()
312    {
313        if ($this->version['version'] < 9) return 'Oracle 9i or later required';
314       
315         $rs =& $this->conn->Execute("
316select  a.size_for_estimate as cache_mb_estimate,
317    case when a.size_factor=1 then
318        '&lt;&lt;= current'
319     when a.estd_physical_read_factor-b.estd_physical_read_factor > 0 and a.estd_physical_read_factor<1 then
320        '- BETTER - '
321    else ' ' end as currsize,
322   a.estd_physical_read_factor-b.estd_physical_read_factor as best_when_0
323   from (select size_for_estimate,size_factor,estd_physical_read_factor,rownum  r from v\$db_cache_advice) a ,
324   (select size_for_estimate,size_factor,estd_physical_read_factor,rownum r from v\$db_cache_advice) b where a.r = b.r-1");
325        if (!$rs) return false;
326       
327        /*
328        The v$db_cache_advice utility show the marginal changes in physical data block reads for different sizes of db_cache_size
329        */
330        $s = "<h3>Data Cache Estimate</h3>";
331        if ($rs->EOF) {
332            $s .= "<p>Cache that is 50% of current size is still too big</p>";
333        } else {
334            $s .= "Ideal size of Data Cache is when \"best_when_0\" changes from a positive number and becomes zero.";
335            $s .= rs2html($rs,false,false,false,false);
336        }
337        return $s;
338    }
339   
340    /*
341        Generate html for suspicious/expensive sql
342    */
343    function tohtml(&$rs,$type)
344    {
345        $o1 = $rs->FetchField(0);
346        $o2 = $rs->FetchField(1);
347        $o3 = $rs->FetchField(2);
348        if ($rs->EOF) return '<p>None found</p>';
349        $check = '';
350        $sql = '';
351        $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>';
352        while (!$rs->EOF) {
353            if ($check != $rs->fields[0].'::'.$rs->fields[1]) {
354                if ($check) {
355                    $carr = explode('::',$check);
356                    $prefix = "<a href=\"?$type=1&sql=".rawurlencode($sql).'&x#explain">';
357                    $suffix = '</a>';
358                    if (strlen($prefix)>2000) {
359                        $prefix = '';
360                        $suffix = '';
361                    }
362                   
363                    $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
364                }
365                $sql = $rs->fields[2];
366                $check = $rs->fields[0].'::'.$rs->fields[1];           
367            } else
368                $sql .= $rs->fields[2];
369            if (substr($sql,strlen($sql)-1) == "\0") $sql = substr($sql,0,strlen($sql)-1);
370            $rs->MoveNext();
371        }
372        $rs->Close();
373       
374        $carr = explode('::',$check);
375        $prefix = "<a target=".rand()." href=\"?&hidem=1&$type=1&sql=".rawurlencode($sql).'&x#explain">';
376        $suffix = '</a>';
377        if (strlen($prefix)>2000) {
378            $prefix = '';
379            $suffix = '';
380        }
381        $s .=  "\n<tr><td align=right>".$carr[0].'</td><td align=right>'.$carr[1].'</td><td>'.$prefix.$sql.$suffix.'</td></tr>';
382                   
383        return $s."</table>\n\n";
384    }
385   
386    // code thanks to Ixora.
387    // http://www.ixora.com.au/scripts/query_opt.htm
388    // requires oracle 8.1.7 or later
389    function SuspiciousSQL($numsql=10)
390    {
391        $sql = "
392select
393  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
394  s.executions  executes,
395  p.sql_text
396from
397  (
398    select
399      address,
400      buffer_gets,
401      executions,
402      pct,
403      rank() over (order by buffer_gets desc)  ranking
404    from
405      (
406    select
407      address,
408      buffer_gets,
409      executions,
410      100 * ratio_to_report(buffer_gets) over ()  pct
411    from
412      sys.v_\$sql
413    where
414      command_type != 47 and module != 'T.O.A.D.'
415      )
416    where
417      buffer_gets > 50 * executions
418  )  s,
419  sys.v_\$sqltext  p
420where
421  s.ranking <= $numsql and
422  p.address = s.address
423order by
424  1 desc, s.address, p.piece";
425
426        global $ADODB_CACHE_MODE;
427        if (isset($_GET['expsixora']) && isset($_GET['sql'])) {
428                $partial = empty($_GET['part']);
429                echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
430        }
431
432        if (isset($_GET['sql'])) return $this->_SuspiciousSQL($numsql);
433       
434        $s = '';
435        $timer = time();
436        $s .= $this->_SuspiciousSQL($numsql);
437        $timer = time() - $timer;
438        if ($timer > $this->noShowIxora) return $s;
439        $s .= '<p>';
440       
441        $save = $ADODB_CACHE_MODE;
442        $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
443        if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
444       
445        $savelog = $this->conn->LogSQL(false);
446        $rs =& $this->conn->SelectLimit($sql);
447        $this->conn->LogSQL($savelog);
448       
449        if (isset($savem)) $this->conn->SetFetchMode($savem);
450        $ADODB_CACHE_MODE = $save;
451        if ($rs) {
452            $s .= "\n<h3>Ixora Suspicious SQL</h3>";
453            $s .= $this->tohtml($rs,'expsixora');
454        }
455       
456        return $s;
457    }
458   
459    // code thanks to Ixora.
460    // http://www.ixora.com.au/scripts/query_opt.htm
461    // requires oracle 8.1.7 or later
462    function ExpensiveSQL($numsql = 10)
463    {
464        $sql = "
465select
466  substr(to_char(s.pct, '99.00'), 2) || '%'  load,
467  s.executions  executes,
468  p.sql_text
469from
470  (
471    select
472      address,
473      disk_reads,
474      executions,
475      pct,
476      rank() over (order by disk_reads desc)  ranking
477    from
478      (
479    select
480      address,
481      disk_reads,
482      executions,
483      100 * ratio_to_report(disk_reads) over ()  pct
484    from
485      sys.v_\$sql
486    where
487      command_type != 47 and module != 'T.O.A.D.'
488      )
489    where
490      disk_reads > 50 * executions
491  )  s,
492  sys.v_\$sqltext  p
493where
494  s.ranking <= $numsql and
495  p.address = s.address
496order by
497  1 desc, s.address, p.piece
498";
499        global $ADODB_CACHE_MODE;
500        if (isset($_GET['expeixora']) && isset($_GET['sql'])) {
501            $partial = empty($_GET['part']);   
502            echo "<a name=explain></a>".$this->Explain($_GET['sql'],$partial)."\n";
503        }
504        if (isset($_GET['sql'])) {
505             $var = $this->_ExpensiveSQL($numsql);
506             return $var;
507        }
508       
509        $s = '';
510        $timer = time();
511        $s .= $this->_ExpensiveSQL($numsql);
512        $timer = time() - $timer;
513       
514        if ($timer > $this->noShowIxora) return $s;
515       
516        $s .= '<p>';
517        $save = $ADODB_CACHE_MODE;
518        $ADODB_CACHE_MODE = ADODB_FETCH_NUM;
519        if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);
520       
521        $savelog = $this->conn->LogSQL(false);
522        $rs =& $this->conn->Execute($sql);
523        $this->conn->LogSQL($savelog);
524       
525        if (isset($savem)) $this->conn->SetFetchMode($savem);
526        $ADODB_CACHE_MODE = $save;
527       
528        if ($rs) {
529            $s .= "\n<h3>Ixora Expensive SQL</h3>";
530            $s .= $this->tohtml($rs,'expeixora');
531        }
532   
533        return $s;
534    }
535   
536    function clearsql()
537    {
538    $this->conn->debug=1;
539        $perf_table = adodb_perf::table();
540    // using the naive "delete from $perf_table where created<".$this->conn->sysTimeStamp will cause the table to lock, possibly
541    // for a long time
542        $sql =
543"DECLARE cnt pls_integer;
544BEGIN
545    cnt := 0;
546    FOR rec IN (SELECT ROWID AS rr FROM $perf_table WHERE created<SYSDATE)
547    LOOP
548      cnt := cnt + 1;
549      DELETE FROM $perf_table WHERE ROWID=rec.rr;
550      IF cnt = 10000 THEN
551        COMMIT;
552        cnt := 0;
553      END IF;
554    END LOOP;
555END;";
556
557        $ok = $this->conn->Execute($sql);
558    }
559}
560?>
Note: See TracBrowser for help on using the repository browser.