diff options
Diffstat (limited to 'trunk/adodb5/docs-perf.htm')
| -rw-r--r-- | trunk/adodb5/docs-perf.htm | 965 |
1 files changed, 0 insertions, 965 deletions
diff --git a/trunk/adodb5/docs-perf.htm b/trunk/adodb5/docs-perf.htm deleted file mode 100644 index 53889c1a..00000000 --- a/trunk/adodb5/docs-perf.htm +++ /dev/null @@ -1,965 +0,0 @@ -<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> -<html> -<head> - <title>ADOdb Performance Monitoring Library</title> - <style type="text/css"> -body, td { -/*font-family: Arial, Helvetica, sans-serif;*/ -font-size: 11pt; -} -pre { -font-size: 9pt; -background-color: #EEEEEE; padding: .5em; margin: 0px; -} -.toplink { -font-size: 8pt; -} - </style> -</head> -<body> -<h3>The ADOdb Performance Monitoring Library</h3> -<p>V5.18 3 Sep 2012 (c) 2000-2010 John Lim (jlim#natsoft.com)</p> -<p><font size="1">This software is dual licensed using BSD-Style and -LGPL. This means you can use it in compiled proprietary and commercial -products.</font></p> -<p>Useful ADOdb links: <a href="http://adodb.sourceforge.net/#download">Download</a> - <a href="http://adodb.sourceforge.net/#docs">Other Docs</a> -</p> -<h3>Introduction</h3> -<p>This module, part of the ADOdb package, provides both CLI and HTML -interfaces for viewing key performance indicators of your database. -This is very useful because web apps such as the popular phpMyAdmin -currently do not provide effective database health monitoring tools. -The module provides the following: </p> -<ul> - <li>A quick health check of your database server using <code>$perf->HealthCheck()</code> -or <code>$perf->HealthCheckCLI()</code>. </li> - <li>User interface for performance monitoring, <code>$perf->UI()</code>. -This UI displays: - <ul> - <li>the health check, </li> - <li>all SQL logged and their query plans, </li> - <li>a list of all tables in the current database</li> - <li>an interface to continiously poll the server for key -performance indicators such as CPU, Hit Ratio, Disk I/O</li> - <li>a form where you can enter and run SQL interactively.</li> - </ul> - </li> - <li>Gives you an API to build database monitoring tools for a server -farm, for example calling <code>$perf->DBParameter('data cache hit -ratio')</code> returns this very important statistic in a database -independant manner. </li> -</ul> -<p>ADOdb also has the ability to log all SQL executed, using <a - href="docs-adodb.htm#logsql">LogSQL</a>. All SQL logged can be -analyzed through the performance monitor <a href="#ui">UI</a>. In the <i>View -SQL</i> mode, we categorize the SQL into 3 types: -</p> -<ul> - <li><b>Suspicious SQL</b>: queries with high average execution times, -and are potential candidates for rewriting</li> - <li><b>Expensive SQL</b>: queries with high total execution times -(#executions * avg execution time). Optimizing these queries will -reduce your database server load.</li> - <li><b>Invalid SQL</b>: queries that generate errors.</li> -</ul> -<p>Each query is hyperlinked to a description of the query plan, and -every PHP script that executed that query is also shown.</p> -<p>Please note that the information presented is a very basic database -health check, and does not provide a complete overview of database -performance. Although some attempt has been made to make it work across -multiple databases in the same way, it is impossible to do so. For the -health check, we do try to display the following key database -parameters for all drivers:</p> -<ul> - <li><b>data cache size</b> - The amount of memory allocated to the -cache.</li> - <li><b>data cache hit ratio</b> - A measure of how effective the -cache is, as a percentage. The higher, the better.</li> - <li><b>current connections</b> - The number of sessions currently -connected to the database. </li> -</ul> -<p>You will need to connect to the database as an administrator to view -most of the parameters. </p> -<p>Code improvements as very welcome, particularly adding new database -parameters and automated tuning hints.</p> -<a name="usage"></a> -<h3>Usage</h3> -<p>Currently, the following drivers: <em>mysql</em>, <em>postgres</em>, -<em>oci8</em>, <em>mssql</em>, <i>informix</i> and <em>db2</em> are -supported. To create a new performance monitor, call NewPerfMonitor( ) -as demonstrated below: </p> -<pre><?php<br>include_once('adodb.inc.php');<br>session_start(); <font - color="#006600"># session variables required for monitoring</font><br>$conn = ADONewConnection($driver);<br>$conn->Connect($server,$user,$pwd,$db);<br>$perf =& NewPerfMonitor($conn);<br>$perf->UI($pollsecs=5);<br>?><br></pre> -<p>It is also possible to retrieve a single database parameter:</p> -<pre>$size = $perf->DBParameter('data cache size');<br></pre> -<p> -Thx to Fernando Ortiz for the informix module. </p> -<h3>Methods</h3> -<a name="ui"></a> -<p><font face="Courier New, Courier, mono">function <b>UI($pollsecs=5)</b></font></p> -<p>Creates a web-based user interface for performance monitoring. When -you click on Poll, server statistics will be displayed every $pollsecs -seconds. See <a href="#usage">Usage</a> above. </p> -<p>Since 4.11, we allow users to enter and run SQL interactively via -the "Run SQL" link. To disable this for security reasons, set this -constant before calling $perf->UI(). </p> -<p> </p> -<pre>define('ADODB_PERF_NO_RUN_SQL',1);</pre> -<p>Sample output follows below:</p> -<table bgcolor="lightyellow" border="1" width="100%"> - <tbody> - <tr> - <td> <b><a href="http://php.weblogs.com/adodb?perf=1">ADOdb</a> -Performance Monitor</b> for localhost, db=test<br> - <font size="-1">PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by -GCC gcc (GCC) 3.2 20020927 (prerelease)</font></td> - </tr> - <tr> - <td> <a href="#">Performance Stats</a> <a href="#">View -SQL</a> <a href="#">View Tables</a> <a href="#">Poll -Stats</a></td> - </tr> - </tbody> -</table> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>postgres7</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>statistics collector</td> - <td>TRUE</td> - <td>Value must be TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i> -and <i>stats_block_level</i> must be set to true in postgresql.conf)</td> - </tr> - <tr> - <td>data cache hit ratio</td> - <td>99.7967555299239</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data reads</td> - <td>125</td> - <td> </td> - </tr> - <tr> - <td>data writes</td> - <td>21.78125000000000000</td> - <td>Count of inserts/updates/deletes * coef</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i> </td> - </tr> - <tr> - <td>data cache buffers</td> - <td>640</td> - <td>Number of cache buffers. <a - href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td> - </tr> - <tr> - <td>cache blocksize</td> - <td>8192</td> - <td>(estimate)</td> - </tr> - <tr> - <td>data cache size</td> - <td>5M</td> - <td> </td> - </tr> - <tr> - <td>operating system cache size</td> - <td>80M</td> - <td>(effective cache size)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Memory Usage</i> </td> - </tr> - <tr> - <td>sort buffer size</td> - <td>1M</td> - <td>Size of sort buffer (per query)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> </td> - </tr> - <tr> - <td>current connections</td> - <td>0</td> - <td> </td> - </tr> - <tr> - <td>max connections</td> - <td>32</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Parameters</i> </td> - </tr> - <tr> - <td>rollback buffers</td> - <td>8</td> - <td>WAL buffers</td> - </tr> - <tr> - <td>random page cost</td> - <td>4</td> - <td>Cost of doing a seek (default=4). See <a - href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td> - </tr> - </tbody> -</table> -<p><font face="Courier New, Courier, mono">function <b>HealthCheck</b>()</font></p> -<p>Returns database health check parameters as a HTML table. You will -need to echo or print the output of this function,</p> -<p><font face="Courier New, Courier, mono">function <b>HealthCheckCLI</b>()</font></p> -<p>Returns database health check parameters formatted for a command -line interface. You will need to echo or print the output of this -function. Sample output for mysql:</p> -<pre>-- Ratios -- <br> MyISAM cache hit ratio =gt; 56.5635738832 <br> InnoDB cache hit ratio =gt; 0 <br> sql cache hit ratio =gt; 0 <br> -- IO -- <br> data reads =gt; 2622 <br> data writes =gt; 2415.5 <br> -- Data Cache -- <br> MyISAM data cache size =gt; 512K <br> BDB data cache size =gt; 8388600<br> InnoDB data cache size =gt; 8M<br> -- Memory Pools -- <br> read buffer size =gt; 131072 <br> sort buffer size =gt; 65528 <br> table cache =gt; 4 <br> -- Connections -- <br> current connections =gt; 3<br> max connections =gt; 100</pre> -<p><font face="Courier New, Courier, mono">function <b>Poll</b>($pollSecs=5) -</font> </p> -<p> Run in infinite loop, displaying the following information every -$pollSecs. This will not work properly if output buffering is enabled. -In the example below, $pollSecs=3: -</p> -<pre>Accumulating statistics...<br> Time WS-CPU% Hit% Sess Reads/s Writes/s<br>11:08:30 0.7 56.56 1 0.0000 0.0000<br>11:08:33 1.8 56.56 2 0.0000 0.0000<br>11:08:36 11.1 56.55 3 2.5000 0.0000<br>11:08:39 9.8 56.55 2 3.1121 0.0000<br>11:08:42 2.8 56.55 1 0.0000 0.0000<br>11:08:45 7.4 56.55 2 0.0000 1.5000<br></pre> -<p><b>WS-CPU%</b> is the Web Server CPU load of the server that PHP is -running from (eg. the database client), and not the database. The <b>Hit%</b> -is the data cache hit ratio. <b>Sess</b> is the current number of -sessions connected to the database. If you are using persistent -connections, this should not change much. The <b>Reads/s</b> and <b>Writes/s</b> -are synthetic values to give the viewer a rough guide to I/O, and are -not to be taken literally. </p> -<p><font face="Courier New, Courier, mono">function <b>SuspiciousSQL</b>($numsql=10)</font></p> -<p>Returns SQL which have high average execution times as a HTML table. -Each sql statement -is hyperlinked to a new window which details the execution plan and the -scripts that execute this SQL. -</p> -<p> The number of statements returned is determined by $numsql. Data is -taken from the adodb_logsql table, where the sql statements are logged -when -$connection->LogSQL(true) is enabled. The adodb_logsql table is -populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>. -</p> -<p>For Oracle, Ixora Suspicious SQL returns a list of SQL statements -that are most cache intensive as a HTML table. These are data intensive -SQL statements that could benefit most from tuning. </p> -<p><font face="Courier New, Courier, mono">function <b>ExpensiveSQL</b>($numsql=10)</font></p> -<p>Returns SQL whose total execution time (avg time * #executions) is -high as a HTML table. Each sql statement -is hyperlinked to a new window which details the execution plan and the -scripts that execute this SQL. -</p> -<p> The number of statements returned is determined by $numsql. Data is -taken from the adodb_logsql table, where the sql statements are logged -when -$connection->LogSQL(true) is enabled. The adodb_logsql table is -populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>. -</p> -<p>For Oracle, Ixora Expensive SQL returns a list of SQL statements -that are taking the most CPU load when run. -</p> -<p><font face="Courier New, Courier, mono">function <b>InvalidSQL</b>($numsql=10)</font></p> -<p>Returns a list of invalid SQL as an HTML table. -</p> -<p>Data is taken from the adodb_logsql table, where the sql statements -are logged when -$connection->LogSQL(true) is enabled. -</p> -<p><font face="Courier New, Courier, mono">function <b>Tables</b>($orderby=1)</font></p> -<p>Returns information on all tables in a database, with the first two -fields containing the table name and table size, the remaining fields -depend on the database driver. If $orderby is set to 1, it will sort by -name. If $orderby is set to 2, then it will sort by table size. Some -database drivers (mssql and mysql) will ignore the $orderby clause. For -postgresql, the information is up-to-date since the last <i>vacuum</i>. -Not supported currently for db2.</p> -<h3>Raw Functions</h3> -<p>Raw functions return values without any formatting.</p> -<p><font face="Courier New, Courier, mono">function <b>DBParameter</b>($paramname)</font></p> -<p>Returns the value of a database parameter, such as -$this->DBParameter("data cache size").</p> -<p><font face="Courier New, Courier, mono">function <b>CPULoad</b>()</font></p> -<p>Returns the CPU load of the database client (NOT THE SERVER) as a -percentage. Only works for Linux and Windows. For Windows, WMI must be -available.</p> -<h3>$ADODB_PERF_MIN</h3> -<p>New in adodb 4.97/5.03 is this global variable, which controls whether sql timings which are too small are not saved. Currently it defaults -to 0.05 (seconds). This means that all sql's which are faster than 0.05 seconds to execute are not saved. -<h3>Format of $settings Property</h3> -<p> To create new database parameters, you need to understand -$settings. The $settings data structure is an associative array. Each -element of the array defines a database parameter. The key is the name -of the database parameter. If no key is defined, then it is assumed to -be a section break, and the value is the name of the section break. If -this is too confusing, looking at the source code will help a lot!</p> -<p> Each database parameter is itself an array consisting of the -following elements:</p> -<ol start="0"> - <li> Category code, used to group related db parameters. If the -category code is 'HIDE', then -the database parameter is not shown when HTML() is called. <br> - </li> - <li> either - <ol type="a"> - <li>sql string to retrieve value, eg. "select value from -v\$parameter where name='db_block_size'", </li> - <li>array holding sql string and field to look for, e.g. -array('show variables','table_cache'); optional 3rd parameter is the -$rs->fields[$index] to use (otherwise $index=1), and optional 4th -parameter is a constant to multiply the result with (typically 100 for -percentage calculations),</li> - <li>a string prefixed by =, then a PHP method of the class is -invoked, e.g. to invoke $this->GetIndexValue(), set this array -element to '=GetIndexValue', <br> - </li> - </ol> - </li> - <li> Description of database parameter. If description begins with an -=, then it is interpreted as a method call, just as in (1c) above, -taking one parameter, the current value. E.g. '=GetIndexDescription' -will invoke $this->GetIndexDescription($val). This is useful for -generating tuning suggestions. For an example, see WarnCacheRatio().</li> -</ol> -<p>Example from MySQL, table_cache database parameter:</p> -<pre>'table cache' =gt; array('CACHE', # category code<br> array("show variables", 'table_cache'), # array (type 1b)<br> 'Number of tables to keep open'), # description</pre> -<h3>Example Health Check Output</h3> -<p><a href="#db2">db2</a> <a href="#informix">informix</a> <a - href="#mysql">mysql</a> <a href="#mssql">mssql</a> <a href="#oci8">oci8</a> -<a href="#postgres">postgres</a></p> -<p><a name="db2"></a></p> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>db2</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr bgcolor="#ffffff"> - <td>data cache hit ratio</td> - <td>0 </td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i></td> - </tr> - <tr bgcolor="#ffffff"> - <td>data cache buffers</td> - <td>250 </td> - <td>See <a - href="http://www7b.boulder.ibm.com/dmdd/library/techarticle/anshum/0107anshum.html#bufferpoolsize">tuning -reference</a>.</td> - </tr> - <tr bgcolor="#ffffff"> - <td>cache blocksize</td> - <td>4096 </td> - <td> </td> - </tr> - <tr bgcolor="#ffffff"> - <td>data cache size</td> - <td>1000K </td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i></td> - </tr> - <tr bgcolor="#ffffff"> - <td>current connections</td> - <td>2 </td> - <td> </td> - </tr> - </tbody> -</table> -<p> </p> -<p><a name="informix"></a> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>informix</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Val -ue</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>data cache hit -ratio</td> - <td>95.89</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data -reads</td> - <td>1883884</td> - <td>Page reads</td> - </tr> - <tr> - <td>data writes</td> - <td>1716724</td> - <td>Page writes</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> - </td> - </tr> - <tr> - <td>current connections</td> - <td>263.0</td> - <td>Number of -sessions</td> - </tr> - </tbody> -</table> -</p> -<p> </p> -<p><a name="mysql" id="mysql"></a></p> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>mysql</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>MyISAM cache hit ratio</td> - <td>56.5658301822</td> - <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td> - </tr> - <tr> - <td>InnoDB cache hit ratio</td> - <td>0</td> - <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td> - </tr> - <tr> - <td>sql cache hit ratio</td> - <td>0</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data reads</td> - <td>2622</td> - <td>Number of selects (Key_reads is not accurate)</td> - </tr> - <tr> - <td>data writes</td> - <td>2415.5</td> - <td>Number of inserts/updates/deletes * coef (Key_writes is not -accurate)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i> </td> - </tr> - <tr> - <td>MyISAM data cache size</td> - <td>512K</td> - <td> </td> - </tr> - <tr> - <td>BDB data cache size</td> - <td>8388600</td> - <td> </td> - </tr> - <tr> - <td>InnoDB data cache size</td> - <td>8M</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Memory Pools</i> </td> - </tr> - <tr> - <td>read buffer size</td> - <td>131072</td> - <td>(per session)</td> - </tr> - <tr> - <td>sort buffer size</td> - <td>65528</td> - <td>Size of sort buffer (per session)</td> - </tr> - <tr> - <td>table cache</td> - <td>4</td> - <td>Number of tables to keep open</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> </td> - </tr> - <tr> - <td>current connections</td> - <td>3</td> - <td> </td> - </tr> - <tr> - <td>max connections</td> - <td>100</td> - <td> </td> - </tr> - </tbody> -</table> -<p> </p> -<p><a name="mssql" id="mssql"></a></p> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>mssql</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>data cache hit ratio</td> - <td>99.9999694824</td> - <td> </td> - </tr> - <tr> - <td>prepared sql hit ratio</td> - <td>99.7738579828</td> - <td> </td> - </tr> - <tr> - <td>adhoc sql hit ratio</td> - <td>98.4540169133</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data reads</td> - <td>2858</td> - <td> </td> - </tr> - <tr> - <td>data writes</td> - <td>1438</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i> </td> - </tr> - <tr> - <td>data cache size</td> - <td>4362</td> - <td>in K</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> </td> - </tr> - <tr> - <td>current connections</td> - <td>14</td> - <td> </td> - </tr> - <tr> - <td>max connections</td> - <td>32767</td> - <td> </td> - </tr> - </tbody> -</table> -<p> </p> -<p><a name="oci8" id="oci8"></a></p> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>oci8</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>data cache hit ratio</td> - <td>96.98</td> - <td> </td> - </tr> - <tr> - <td>sql cache hit ratio</td> - <td>99.96</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data reads</td> - <td>842938</td> - <td> </td> - </tr> - <tr> - <td>data writes</td> - <td>16852</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i> </td> - </tr> - <tr> - <td>data cache buffers</td> - <td>3072</td> - <td>Number of cache buffers</td> - </tr> - <tr> - <td>data cache blocksize</td> - <td>8192</td> - <td> </td> - </tr> - <tr> - <td>data cache size</td> - <td>48M</td> - <td>shared_pool_size</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Memory Pools</i> </td> - </tr> - <tr> - <td>java pool size</td> - <td>0</td> - <td>java_pool_size</td> - </tr> - <tr> - <td>sort buffer size</td> - <td>512K</td> - <td>sort_area_size (per query)</td> - </tr> - <tr> - <td>user session buffer size</td> - <td>8M</td> - <td>large_pool_size</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> </td> - </tr> - <tr> - <td>current connections</td> - <td>1</td> - <td> </td> - </tr> - <tr> - <td>max connections</td> - <td>170</td> - <td> </td> - </tr> - <tr> - <td>data cache utilization ratio</td> - <td>88.46</td> - <td>Percentage of data cache actually in use</td> - </tr> - <tr> - <td>user cache utilization ratio</td> - <td>91.76</td> - <td>Percentage of user cache (large_pool) actually in use</td> - </tr> - <tr> - <td>rollback segments</td> - <td>11</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Transactions</i> </td> - </tr> - <tr> - <td>peak transactions</td> - <td>24</td> - <td>Taken from high-water-mark</td> - </tr> - <tr> - <td>max transactions</td> - <td>187</td> - <td>max transactions / rollback segments < 3.5 (or -transactions_per_rollback_segment)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Parameters</i> </td> - </tr> - <tr> - <td>cursor sharing</td> - <td>EXACT</td> - <td>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>.</td> - </tr> - <tr> - <td>index cache cost</td> - <td>0</td> - <td>% of indexed data blocks expected in the cache. Recommended -is 20-80. Default is 0. See <a - href="http://www.dba-oracle.com/oracle_tips_cbo_part1.htm">optimizer_index_caching</a>.</td> - </tr> - <tr> - <td>random page cost</td> - <td>100</td> - <td>Recommended is 10-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>. - </td> - </tr> - </tbody> -</table> -<h3>Suspicious SQL</h3> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td><b>LOAD</b></td> - <td><b>EXECUTES</b></td> - <td><b>SQL_TEXT</b></td> - </tr> - <tr> - <td align="right"> .73%</td> - <td align="right">89</td> - <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o, -sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) -and o.obj#=t.obj# and o.owner# = u.user# select i.obj#, i.flags, -u.name, o.name from sys.obj$ o, sys.user$ u, sys.ind$ i where -(bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) and -(not((i.type# = 9) and bitand(i.flags,8) = 8)) and o.obj#=i.obj# and -o.owner# = u.user# </td> - </tr> - <tr> - <td align="right"> .84%</td> - <td align="right">3</td> - <td>select /*+ RULE */ distinct tabs.table_name, tabs.owner , -partitioned, iot_type , TEMPORARY, table_type, table_type_owner from -DBA_ALL_TABLES tabs where tabs.owner = :own </td> - </tr> - <tr> - <td align="right"> 3.95%</td> - <td align="right">6</td> - <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM -DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE -obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = -seg.owner and obj.object_name = seg.segment_name and obj.object_type = -seg.segment_type and seg.buffer_pool = buf.name and buf.name = -'DEFAULT' </td> - </tr> - <tr> - <td align="right"> 4.50%</td> - <td align="right">6</td> - <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM -DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE -obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = -seg.owner and obj.object_name = seg.segment_name and obj.object_type = -seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td> - </tr> - <tr> - <td align="right">57.34%</td> - <td align="right">9267</td> - <td>select t.schema, t.name, t.flags, q.name from -system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft, -system.aq$_queues q where aft.table_objno = t.objno and -aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and -bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name, -aft.table_objno skip locked </td> - </tr> - </tbody> -</table> -<h3>Expensive SQL</h3> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td><b>LOAD</b></td> - <td><b>EXECUTES</b></td> - <td><b>SQL_TEXT</b></td> - </tr> - <tr> - <td align="right"> 5.24%</td> - <td align="right">1</td> - <td>select round(sum(bytes)/1048576) from dba_segments </td> - </tr> - <tr> - <td align="right"> 6.89%</td> - <td align="right">6</td> - <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM -DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE -obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = -seg.owner and obj.object_name = seg.segment_name and obj.object_type = -seg.segment_type and seg.buffer_pool = buf.name and buf.name = -'DEFAULT' </td> - </tr> - <tr> - <td align="right"> 7.85%</td> - <td align="right">6</td> - <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM -DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE -obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner = -seg.owner and obj.object_name = seg.segment_name and obj.object_type = -seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td> - </tr> - <tr> - <td align="right">33.69%</td> - <td align="right">89</td> - <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o, -sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576) -and o.obj#=t.obj# and o.owner# = u.user# </td> - </tr> - <tr> - <td align="right">36.44%</td> - <td align="right">89</td> - <td>select i.obj#, i.flags, u.name, o.name from sys.obj$ o, -sys.user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or -bitand(i.flags, 512) = 512) and (not((i.type# = 9) and -bitand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# </td> - </tr> - </tbody> -</table> -<p><a name="postgres" id="postgres"></a></p> -<table bgcolor="white" border="1"> - <tbody> - <tr> - <td colspan="3"> - <h3>postgres7</h3> - </td> - </tr> - <tr> - <td><b>Parameter</b></td> - <td><b>Value</b></td> - <td><b>Description</b></td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Ratios</i> </td> - </tr> - <tr> - <td>statistics collector</td> - <td>FALSE</td> - <td>Must be set to TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i> -and <i>stats_block_level</i> must be set to true in postgresql.conf)</td> - </tr> - <tr> - <td>data cache hit ratio</td> - <td>99.9666031916603</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>IO</i> </td> - </tr> - <tr> - <td>data reads</td> - <td>15</td> - <td> </td> - </tr> - <tr> - <td>data writes</td> - <td>0.000000000000000000</td> - <td>Count of inserts/updates/deletes * coef</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Data Cache</i> </td> - </tr> - <tr> - <td>data cache buffers</td> - <td>1280</td> - <td>Number of cache buffers. <a - href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td> - </tr> - <tr> - <td>cache blocksize</td> - <td>8192</td> - <td>(estimate)</td> - </tr> - <tr> - <td>data cache size</td> - <td>10M</td> - <td> </td> - </tr> - <tr> - <td>operating system cache size</td> - <td>80000K</td> - <td>(effective cache size)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Memory Pools</i> </td> - </tr> - <tr> - <td>sort buffer size</td> - <td>1M</td> - <td>Size of sort buffer (per query)</td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Connections</i> </td> - </tr> - <tr> - <td>current connections</td> - <td>13</td> - <td> </td> - </tr> - <tr> - <td>max connections</td> - <td>32</td> - <td> </td> - </tr> - <tr bgcolor="#f0f0f0"> - <td colspan="3"><i>Parameters</i> </td> - </tr> - <tr> - <td>rollback buffers</td> - <td>8</td> - <td>WAL buffers</td> - </tr> - <tr> - <td>random page cost</td> - <td>4</td> - <td>Cost of doing a seek (default=4). See <a - href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td> - </tr> - </tbody> -</table> -</body> -</html> |