| 1 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
|
|---|
| 2 | <html>
|
|---|
| 3 | <head>
|
|---|
| 4 | <title>ADOdb Performance Monitoring Library</title>
|
|---|
| 5 | <style type="text/css">
|
|---|
| 6 | body, td {
|
|---|
| 7 | /*font-family: Arial, Helvetica, sans-serif;*/
|
|---|
| 8 | font-size: 11pt;
|
|---|
| 9 | }
|
|---|
| 10 | pre {
|
|---|
| 11 | font-size: 9pt;
|
|---|
| 12 | background-color: #EEEEEE; padding: .5em; margin: 0px;
|
|---|
| 13 | }
|
|---|
| 14 | .toplink {
|
|---|
| 15 | font-size: 8pt;
|
|---|
| 16 | }
|
|---|
| 17 | </style>
|
|---|
| 18 | </head>
|
|---|
| 19 | <body>
|
|---|
| 20 | <h3>The ADOdb Performance Monitoring Library</h3>
|
|---|
| 21 | <p>V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my)</p>
|
|---|
| 22 | <p><font size="1">This software is dual licensed using BSD-Style and
|
|---|
| 23 | LGPL. This means you can use it in compiled proprietary and commercial
|
|---|
| 24 | products.</font></p>
|
|---|
| 25 | <p>Useful ADOdb links: <a href="http://adodb.sourceforge.net/#download">Download</a>
|
|---|
| 26 | <a href="http://adodb.sourceforge.net/#docs">Other Docs</a>
|
|---|
| 27 | </p>
|
|---|
| 28 | <h3>Introduction</h3>
|
|---|
| 29 | <p>This module, part of the ADOdb package, provides both CLI and HTML
|
|---|
| 30 | interfaces for viewing key performance indicators of your database.
|
|---|
| 31 | This is very useful because web apps such as the popular phpMyAdmin
|
|---|
| 32 | currently do not provide effective database health monitoring tools.
|
|---|
| 33 | The module provides the following: </p>
|
|---|
| 34 | <ul>
|
|---|
| 35 | <li>A quick health check of your database server using <code>$perf->HealthCheck()</code>
|
|---|
| 36 | or <code>$perf->HealthCheckCLI()</code>. </li>
|
|---|
| 37 | <li>User interface for performance monitoring, <code>$perf->UI()</code>.
|
|---|
| 38 | This UI displays:
|
|---|
| 39 | <ul>
|
|---|
| 40 | <li>the health check, </li>
|
|---|
| 41 | <li>all SQL logged and their query plans, </li>
|
|---|
| 42 | <li>a list of all tables in the current database</li>
|
|---|
| 43 | <li>an interface to continiously poll the server for key
|
|---|
| 44 | performance indicators such as CPU, Hit Ratio, Disk I/O</li>
|
|---|
| 45 | <li>a form where you can enter and run SQL interactively.</li>
|
|---|
| 46 | </ul>
|
|---|
| 47 | </li>
|
|---|
| 48 | <li>Gives you an API to build database monitoring tools for a server
|
|---|
| 49 | farm, for example calling <code>$perf->DBParameter('data cache hit
|
|---|
| 50 | ratio')</code> returns this very important statistic in a database
|
|---|
| 51 | independant manner. </li>
|
|---|
| 52 | </ul>
|
|---|
| 53 | <p>ADOdb also has the ability to log all SQL executed, using <a
|
|---|
| 54 | href="docs-adodb.htm#logsql">LogSQL</a>. All SQL logged can be
|
|---|
| 55 | analyzed through the performance monitor <a href="#ui">UI</a>. In the <i>View
|
|---|
| 56 | SQL</i> mode, we categorize the SQL into 3 types:
|
|---|
| 57 | </p>
|
|---|
| 58 | <ul>
|
|---|
| 59 | <li><b>Suspicious SQL</b>: queries with high average execution times,
|
|---|
| 60 | and are potential candidates for rewriting</li>
|
|---|
| 61 | <li><b>Expensive SQL</b>: queries with high total execution times
|
|---|
| 62 | (#executions * avg execution time). Optimizing these queries will
|
|---|
| 63 | reduce your database server load.</li>
|
|---|
| 64 | <li><b>Invalid SQL</b>: queries that generate errors.</li>
|
|---|
| 65 | </ul>
|
|---|
| 66 | <p>Each query is hyperlinked to a description of the query plan, and
|
|---|
| 67 | every PHP script that executed that query is also shown.</p>
|
|---|
| 68 | <p>Please note that the information presented is a very basic database
|
|---|
| 69 | health check, and does not provide a complete overview of database
|
|---|
| 70 | performance. Although some attempt has been made to make it work across
|
|---|
| 71 | multiple databases in the same way, it is impossible to do so. For the
|
|---|
| 72 | health check, we do try to display the following key database
|
|---|
| 73 | parameters for all drivers:</p>
|
|---|
| 74 | <ul>
|
|---|
| 75 | <li><b>data cache size</b> - The amount of memory allocated to the
|
|---|
| 76 | cache.</li>
|
|---|
| 77 | <li><b>data cache hit ratio</b> - A measure of how effective the
|
|---|
| 78 | cache is, as a percentage. The higher, the better.</li>
|
|---|
| 79 | <li><b>current connections</b> - The number of sessions currently
|
|---|
| 80 | connected to the database. </li>
|
|---|
| 81 | </ul>
|
|---|
| 82 | <p>You will need to connect to the database as an administrator to view
|
|---|
| 83 | most of the parameters. </p>
|
|---|
| 84 | <p>Code improvements as very welcome, particularly adding new database
|
|---|
| 85 | parameters and automated tuning hints.</p>
|
|---|
| 86 | <a name="usage"></a>
|
|---|
| 87 | <h3>Usage</h3>
|
|---|
| 88 | <p>Currently, the following drivers: <em>mysql</em>, <em>postgres</em>,
|
|---|
| 89 | <em>oci8</em>, <em>mssql</em>, <i>informix</i> and <em>db2</em> are
|
|---|
| 90 | supported. To create a new performance monitor, call NewPerfMonitor( )
|
|---|
| 91 | as demonstrated below: </p>
|
|---|
| 92 | <pre><?php<br>include_once('adodb.inc.php');<br>session_start(); <font
|
|---|
| 93 | 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>
|
|---|
| 94 | <p>It is also possible to retrieve a single database parameter:</p>
|
|---|
| 95 | <pre>$size = $perf->DBParameter('data cache size');<br></pre>
|
|---|
| 96 | <p>
|
|---|
| 97 | Thx to Fernando Ortiz for the informix module. </p>
|
|---|
| 98 | <h3>Methods</h3>
|
|---|
| 99 | <a name="ui"></a>
|
|---|
| 100 | <p><font face="Courier New, Courier, mono">function <b>UI($pollsecs=5)</b></font></p>
|
|---|
| 101 | <p>Creates a web-based user interface for performance monitoring. When
|
|---|
| 102 | you click on Poll, server statistics will be displayed every $pollsecs
|
|---|
| 103 | seconds. See <a href="#usage">Usage</a> above. </p>
|
|---|
| 104 | <p>Since 4.11, we allow users to enter and run SQL interactively via
|
|---|
| 105 | the "Run SQL" link. To disable this for security reasons, set this
|
|---|
| 106 | constant before calling $perf->UI(). </p>
|
|---|
| 107 | <p> </p>
|
|---|
| 108 | <pre>define('ADODB_PERF_NO_RUN_SQL',1);</pre>
|
|---|
| 109 | <p>Sample output follows below:</p>
|
|---|
| 110 | <table bgcolor="lightyellow" border="1" width="100%">
|
|---|
| 111 | <tbody>
|
|---|
| 112 | <tr>
|
|---|
| 113 | <td> <b><a href="http://php.weblogs.com/adodb?perf=1">ADOdb</a>
|
|---|
| 114 | Performance Monitor</b> for localhost, db=test<br>
|
|---|
| 115 | <font size="-1">PostgreSQL 7.3.2 on i686-pc-cygwin, compiled by
|
|---|
| 116 | GCC gcc (GCC) 3.2 20020927 (prerelease)</font></td>
|
|---|
| 117 | </tr>
|
|---|
| 118 | <tr>
|
|---|
| 119 | <td> <a href="#">Performance Stats</a> <a href="#">View
|
|---|
| 120 | SQL</a> <a href="#">View Tables</a> <a href="#">Poll
|
|---|
| 121 | Stats</a></td>
|
|---|
| 122 | </tr>
|
|---|
| 123 | </tbody>
|
|---|
| 124 | </table>
|
|---|
| 125 | <table bgcolor="white" border="1">
|
|---|
| 126 | <tbody>
|
|---|
| 127 | <tr>
|
|---|
| 128 | <td colspan="3">
|
|---|
| 129 | <h3>postgres7</h3>
|
|---|
| 130 | </td>
|
|---|
| 131 | </tr>
|
|---|
| 132 | <tr>
|
|---|
| 133 | <td><b>Parameter</b></td>
|
|---|
| 134 | <td><b>Value</b></td>
|
|---|
| 135 | <td><b>Description</b></td>
|
|---|
| 136 | </tr>
|
|---|
| 137 | <tr bgcolor="#f0f0f0">
|
|---|
| 138 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 139 | </tr>
|
|---|
| 140 | <tr>
|
|---|
| 141 | <td>statistics collector</td>
|
|---|
| 142 | <td>TRUE</td>
|
|---|
| 143 | <td>Value must be TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i>
|
|---|
| 144 | and <i>stats_block_level</i> must be set to true in postgresql.conf)</td>
|
|---|
| 145 | </tr>
|
|---|
| 146 | <tr>
|
|---|
| 147 | <td>data cache hit ratio</td>
|
|---|
| 148 | <td>99.7967555299239</td>
|
|---|
| 149 | <td> </td>
|
|---|
| 150 | </tr>
|
|---|
| 151 | <tr bgcolor="#f0f0f0">
|
|---|
| 152 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 153 | </tr>
|
|---|
| 154 | <tr>
|
|---|
| 155 | <td>data reads</td>
|
|---|
| 156 | <td>125</td>
|
|---|
| 157 | <td> </td>
|
|---|
| 158 | </tr>
|
|---|
| 159 | <tr>
|
|---|
| 160 | <td>data writes</td>
|
|---|
| 161 | <td>21.78125000000000000</td>
|
|---|
| 162 | <td>Count of inserts/updates/deletes * coef</td>
|
|---|
| 163 | </tr>
|
|---|
| 164 | <tr bgcolor="#f0f0f0">
|
|---|
| 165 | <td colspan="3"><i>Data Cache</i> </td>
|
|---|
| 166 | </tr>
|
|---|
| 167 | <tr>
|
|---|
| 168 | <td>data cache buffers</td>
|
|---|
| 169 | <td>640</td>
|
|---|
| 170 | <td>Number of cache buffers. <a
|
|---|
| 171 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td>
|
|---|
| 172 | </tr>
|
|---|
| 173 | <tr>
|
|---|
| 174 | <td>cache blocksize</td>
|
|---|
| 175 | <td>8192</td>
|
|---|
| 176 | <td>(estimate)</td>
|
|---|
| 177 | </tr>
|
|---|
| 178 | <tr>
|
|---|
| 179 | <td>data cache size</td>
|
|---|
| 180 | <td>5M</td>
|
|---|
| 181 | <td> </td>
|
|---|
| 182 | </tr>
|
|---|
| 183 | <tr>
|
|---|
| 184 | <td>operating system cache size</td>
|
|---|
| 185 | <td>80M</td>
|
|---|
| 186 | <td>(effective cache size)</td>
|
|---|
| 187 | </tr>
|
|---|
| 188 | <tr bgcolor="#f0f0f0">
|
|---|
| 189 | <td colspan="3"><i>Memory Usage</i> </td>
|
|---|
| 190 | </tr>
|
|---|
| 191 | <tr>
|
|---|
| 192 | <td>sort buffer size</td>
|
|---|
| 193 | <td>1M</td>
|
|---|
| 194 | <td>Size of sort buffer (per query)</td>
|
|---|
| 195 | </tr>
|
|---|
| 196 | <tr bgcolor="#f0f0f0">
|
|---|
| 197 | <td colspan="3"><i>Connections</i> </td>
|
|---|
| 198 | </tr>
|
|---|
| 199 | <tr>
|
|---|
| 200 | <td>current connections</td>
|
|---|
| 201 | <td>0</td>
|
|---|
| 202 | <td> </td>
|
|---|
| 203 | </tr>
|
|---|
| 204 | <tr>
|
|---|
| 205 | <td>max connections</td>
|
|---|
| 206 | <td>32</td>
|
|---|
| 207 | <td> </td>
|
|---|
| 208 | </tr>
|
|---|
| 209 | <tr bgcolor="#f0f0f0">
|
|---|
| 210 | <td colspan="3"><i>Parameters</i> </td>
|
|---|
| 211 | </tr>
|
|---|
| 212 | <tr>
|
|---|
| 213 | <td>rollback buffers</td>
|
|---|
| 214 | <td>8</td>
|
|---|
| 215 | <td>WAL buffers</td>
|
|---|
| 216 | </tr>
|
|---|
| 217 | <tr>
|
|---|
| 218 | <td>random page cost</td>
|
|---|
| 219 | <td>4</td>
|
|---|
| 220 | <td>Cost of doing a seek (default=4). See <a
|
|---|
| 221 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td>
|
|---|
| 222 | </tr>
|
|---|
| 223 | </tbody>
|
|---|
| 224 | </table>
|
|---|
| 225 | <p><font face="Courier New, Courier, mono">function <b>HealthCheck</b>()</font></p>
|
|---|
| 226 | <p>Returns database health check parameters as a HTML table. You will
|
|---|
| 227 | need to echo or print the output of this function,</p>
|
|---|
| 228 | <p><font face="Courier New, Courier, mono">function <b>HealthCheckCLI</b>()</font></p>
|
|---|
| 229 | <p>Returns database health check parameters formatted for a command
|
|---|
| 230 | line interface. You will need to echo or print the output of this
|
|---|
| 231 | function. Sample output for mysql:</p>
|
|---|
| 232 | <pre>-- Ratios -- <br> MyISAM cache hit ratio => 56.5635738832 <br> InnoDB cache hit ratio => 0 <br> sql cache hit ratio => 0 <br> -- IO -- <br> data reads => 2622 <br> data writes => 2415.5 <br> -- Data Cache -- <br> MyISAM data cache size => 512K <br> BDB data cache size => 8388600<br> InnoDB data cache size => 8M<br> -- Memory Pools -- <br> read buffer size => 131072 <br> sort buffer size => 65528 <br> table cache => 4 <br> -- Connections -- <br> current connections => 3<br> max connections => 100</pre>
|
|---|
| 233 | <p><font face="Courier New, Courier, mono">function <b>Poll</b>($pollSecs=5)
|
|---|
| 234 | </font> </p>
|
|---|
| 235 | <p> Run in infinite loop, displaying the following information every
|
|---|
| 236 | $pollSecs. This will not work properly if output buffering is enabled.
|
|---|
| 237 | In the example below, $pollSecs=3:
|
|---|
| 238 | </p>
|
|---|
| 239 | <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>
|
|---|
| 240 | <p><b>WS-CPU%</b> is the Web Server CPU load of the server that PHP is
|
|---|
| 241 | running from (eg. the database client), and not the database. The <b>Hit%</b>
|
|---|
| 242 | is the data cache hit ratio. <b>Sess</b> is the current number of
|
|---|
| 243 | sessions connected to the database. If you are using persistent
|
|---|
| 244 | connections, this should not change much. The <b>Reads/s</b> and <b>Writes/s</b>
|
|---|
| 245 | are synthetic values to give the viewer a rough guide to I/O, and are
|
|---|
| 246 | not to be taken literally. </p>
|
|---|
| 247 | <p><font face="Courier New, Courier, mono">function <b>SuspiciousSQL</b>($numsql=10)</font></p>
|
|---|
| 248 | <p>Returns SQL which have high average execution times as a HTML table.
|
|---|
| 249 | Each sql statement
|
|---|
| 250 | is hyperlinked to a new window which details the execution plan and the
|
|---|
| 251 | scripts that execute this SQL.
|
|---|
| 252 | </p>
|
|---|
| 253 | <p> The number of statements returned is determined by $numsql. Data is
|
|---|
| 254 | taken from the adodb_logsql table, where the sql statements are logged
|
|---|
| 255 | when
|
|---|
| 256 | $connection->LogSQL(true) is enabled. The adodb_logsql table is
|
|---|
| 257 | populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>.
|
|---|
| 258 | </p>
|
|---|
| 259 | <p>For Oracle, Ixora Suspicious SQL returns a list of SQL statements
|
|---|
| 260 | that are most cache intensive as a HTML table. These are data intensive
|
|---|
| 261 | SQL statements that could benefit most from tuning. </p>
|
|---|
| 262 | <p><font face="Courier New, Courier, mono">function <b>ExpensiveSQL</b>($numsql=10)</font></p>
|
|---|
| 263 | <p>Returns SQL whose total execution time (avg time * #executions) is
|
|---|
| 264 | high as a HTML table. Each sql statement
|
|---|
| 265 | is hyperlinked to a new window which details the execution plan and the
|
|---|
| 266 | scripts that execute this SQL.
|
|---|
| 267 | </p>
|
|---|
| 268 | <p> The number of statements returned is determined by $numsql. Data is
|
|---|
| 269 | taken from the adodb_logsql table, where the sql statements are logged
|
|---|
| 270 | when
|
|---|
| 271 | $connection->LogSQL(true) is enabled. The adodb_logsql table is
|
|---|
| 272 | populated using <a href="docs-adodb.htm#logsql">$conn->LogSQL</a>.
|
|---|
| 273 | </p>
|
|---|
| 274 | <p>For Oracle, Ixora Expensive SQL returns a list of SQL statements
|
|---|
| 275 | that are taking the most CPU load when run.
|
|---|
| 276 | </p>
|
|---|
| 277 | <p><font face="Courier New, Courier, mono">function <b>InvalidSQL</b>($numsql=10)</font></p>
|
|---|
| 278 | <p>Returns a list of invalid SQL as an HTML table.
|
|---|
| 279 | </p>
|
|---|
| 280 | <p>Data is taken from the adodb_logsql table, where the sql statements
|
|---|
| 281 | are logged when
|
|---|
| 282 | $connection->LogSQL(true) is enabled.
|
|---|
| 283 | </p>
|
|---|
| 284 | <p><font face="Courier New, Courier, mono">function <b>Tables</b>($orderby=1)</font></p>
|
|---|
| 285 | <p>Returns information on all tables in a database, with the first two
|
|---|
| 286 | fields containing the table name and table size, the remaining fields
|
|---|
| 287 | depend on the database driver. If $orderby is set to 1, it will sort by
|
|---|
| 288 | name. If $orderby is set to 2, then it will sort by table size. Some
|
|---|
| 289 | database drivers (mssql and mysql) will ignore the $orderby clause. For
|
|---|
| 290 | postgresql, the information is up-to-date since the last <i>vacuum</i>.
|
|---|
| 291 | Not supported currently for db2.</p>
|
|---|
| 292 | <h3>Raw Functions</h3>
|
|---|
| 293 | <p>Raw functions return values without any formatting.</p>
|
|---|
| 294 | <p><font face="Courier New, Courier, mono">function <b>DBParameter</b>($paramname)</font></p>
|
|---|
| 295 | <p>Returns the value of a database parameter, such as
|
|---|
| 296 | $this->DBParameter("data cache size").</p>
|
|---|
| 297 | <p><font face="Courier New, Courier, mono">function <b>CPULoad</b>()</font></p>
|
|---|
| 298 | <p>Returns the CPU load of the database client (NOT THE SERVER) as a
|
|---|
| 299 | percentage. Only works for Linux and Windows. For Windows, WMI must be
|
|---|
| 300 | available.</p>
|
|---|
| 301 | <h3>Format of $settings Property</h3>
|
|---|
| 302 | <p> To create new database parameters, you need to understand
|
|---|
| 303 | $settings. The $settings data structure is an associative array. Each
|
|---|
| 304 | element of the array defines a database parameter. The key is the name
|
|---|
| 305 | of the database parameter. If no key is defined, then it is assumed to
|
|---|
| 306 | be a section break, and the value is the name of the section break. If
|
|---|
| 307 | this is too confusing, looking at the source code will help a lot!</p>
|
|---|
| 308 | <p> Each database parameter is itself an array consisting of the
|
|---|
| 309 | following elements:</p>
|
|---|
| 310 | <ol start="0">
|
|---|
| 311 | <li> Category code, used to group related db parameters. If the
|
|---|
| 312 | category code is 'HIDE', then
|
|---|
| 313 | the database parameter is not shown when HTML() is called. <br>
|
|---|
| 314 | </li>
|
|---|
| 315 | <li> either
|
|---|
| 316 | <ol type="a">
|
|---|
| 317 | <li>sql string to retrieve value, eg. "select value from
|
|---|
| 318 | v\$parameter where name='db_block_size'", </li>
|
|---|
| 319 | <li>array holding sql string and field to look for, e.g.
|
|---|
| 320 | array('show variables','table_cache'); optional 3rd parameter is the
|
|---|
| 321 | $rs->fields[$index] to use (otherwise $index=1), and optional 4th
|
|---|
| 322 | parameter is a constant to multiply the result with (typically 100 for
|
|---|
| 323 | percentage calculations),</li>
|
|---|
| 324 | <li>a string prefixed by =, then a PHP method of the class is
|
|---|
| 325 | invoked, e.g. to invoke $this->GetIndexValue(), set this array
|
|---|
| 326 | element to '=GetIndexValue', <br>
|
|---|
| 327 | </li>
|
|---|
| 328 | </ol>
|
|---|
| 329 | </li>
|
|---|
| 330 | <li> Description of database parameter. If description begins with an
|
|---|
| 331 | =, then it is interpreted as a method call, just as in (1c) above,
|
|---|
| 332 | taking one parameter, the current value. E.g. '=GetIndexDescription'
|
|---|
| 333 | will invoke $this->GetIndexDescription($val). This is useful for
|
|---|
| 334 | generating tuning suggestions. For an example, see WarnCacheRatio().</li>
|
|---|
| 335 | </ol>
|
|---|
| 336 | <p>Example from MySQL, table_cache database parameter:</p>
|
|---|
| 337 | <pre>'table cache' => array('CACHE', # category code<br> array("show variables", 'table_cache'), # array (type 1b)<br> 'Number of tables to keep open'), # description</pre>
|
|---|
| 338 | <h3>Example Health Check Output</h3>
|
|---|
| 339 | <p><a href="#db2">db2</a> <a href="#informix">informix</a> <a
|
|---|
| 340 | href="#mysql">mysql</a> <a href="#mssql">mssql</a> <a href="#oci8">oci8</a>
|
|---|
| 341 | <a href="#postgres">postgres</a></p>
|
|---|
| 342 | <p><a name="db2"></a></p>
|
|---|
| 343 | <table bgcolor="white" border="1">
|
|---|
| 344 | <tbody>
|
|---|
| 345 | <tr>
|
|---|
| 346 | <td colspan="3">
|
|---|
| 347 | <h3>db2</h3>
|
|---|
| 348 | </td>
|
|---|
| 349 | </tr>
|
|---|
| 350 | <tr>
|
|---|
| 351 | <td><b>Parameter</b></td>
|
|---|
| 352 | <td><b>Value</b></td>
|
|---|
| 353 | <td><b>Description</b></td>
|
|---|
| 354 | </tr>
|
|---|
| 355 | <tr bgcolor="#f0f0f0">
|
|---|
| 356 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 357 | </tr>
|
|---|
| 358 | <tr bgcolor="#ffffff">
|
|---|
| 359 | <td>data cache hit ratio</td>
|
|---|
| 360 | <td>0 </td>
|
|---|
| 361 | <td> </td>
|
|---|
| 362 | </tr>
|
|---|
| 363 | <tr bgcolor="#f0f0f0">
|
|---|
| 364 | <td colspan="3"><i>Data Cache</i></td>
|
|---|
| 365 | </tr>
|
|---|
| 366 | <tr bgcolor="#ffffff">
|
|---|
| 367 | <td>data cache buffers</td>
|
|---|
| 368 | <td>250 </td>
|
|---|
| 369 | <td>See <a
|
|---|
| 370 | href="http://www7b.boulder.ibm.com/dmdd/library/techarticle/anshum/0107anshum.html#bufferpoolsize">tuning
|
|---|
| 371 | reference</a>.</td>
|
|---|
| 372 | </tr>
|
|---|
| 373 | <tr bgcolor="#ffffff">
|
|---|
| 374 | <td>cache blocksize</td>
|
|---|
| 375 | <td>4096 </td>
|
|---|
| 376 | <td> </td>
|
|---|
| 377 | </tr>
|
|---|
| 378 | <tr bgcolor="#ffffff">
|
|---|
| 379 | <td>data cache size</td>
|
|---|
| 380 | <td>1000K </td>
|
|---|
| 381 | <td> </td>
|
|---|
| 382 | </tr>
|
|---|
| 383 | <tr bgcolor="#f0f0f0">
|
|---|
| 384 | <td colspan="3"><i>Connections</i></td>
|
|---|
| 385 | </tr>
|
|---|
| 386 | <tr bgcolor="#ffffff">
|
|---|
| 387 | <td>current connections</td>
|
|---|
| 388 | <td>2 </td>
|
|---|
| 389 | <td> </td>
|
|---|
| 390 | </tr>
|
|---|
| 391 | </tbody>
|
|---|
| 392 | </table>
|
|---|
| 393 | <p> </p>
|
|---|
| 394 | <p><a name="informix"></a>
|
|---|
| 395 | <table bgcolor="white" border="1">
|
|---|
| 396 | <tbody>
|
|---|
| 397 | <tr>
|
|---|
| 398 | <td colspan="3">
|
|---|
| 399 | <h3>informix</h3>
|
|---|
| 400 | </td>
|
|---|
| 401 | </tr>
|
|---|
| 402 | <tr>
|
|---|
| 403 | <td><b>Parameter</b></td>
|
|---|
| 404 | <td><b>Val
|
|---|
| 405 | ue</b></td>
|
|---|
| 406 | <td><b>Description</b></td>
|
|---|
| 407 | </tr>
|
|---|
| 408 | <tr bgcolor="#f0f0f0">
|
|---|
| 409 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 410 | </tr>
|
|---|
| 411 | <tr>
|
|---|
| 412 | <td>data cache hit
|
|---|
| 413 | ratio</td>
|
|---|
| 414 | <td>95.89</td>
|
|---|
| 415 | <td> </td>
|
|---|
| 416 | </tr>
|
|---|
| 417 | <tr bgcolor="#f0f0f0">
|
|---|
| 418 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 419 | </tr>
|
|---|
| 420 | <tr>
|
|---|
| 421 | <td>data
|
|---|
| 422 | reads</td>
|
|---|
| 423 | <td>1883884</td>
|
|---|
| 424 | <td>Page reads</td>
|
|---|
| 425 | </tr>
|
|---|
| 426 | <tr>
|
|---|
| 427 | <td>data writes</td>
|
|---|
| 428 | <td>1716724</td>
|
|---|
| 429 | <td>Page writes</td>
|
|---|
| 430 | </tr>
|
|---|
| 431 | <tr bgcolor="#f0f0f0">
|
|---|
| 432 | <td colspan="3"><i>Connections</i>
|
|---|
| 433 | </td>
|
|---|
| 434 | </tr>
|
|---|
| 435 | <tr>
|
|---|
| 436 | <td>current connections</td>
|
|---|
| 437 | <td>263.0</td>
|
|---|
| 438 | <td>Number of
|
|---|
| 439 | sessions</td>
|
|---|
| 440 | </tr>
|
|---|
| 441 | </tbody>
|
|---|
| 442 | </table>
|
|---|
| 443 | </p>
|
|---|
| 444 | <p> </p>
|
|---|
| 445 | <p><a name="mysql" id="mysql"></a></p>
|
|---|
| 446 | <table bgcolor="white" border="1">
|
|---|
| 447 | <tbody>
|
|---|
| 448 | <tr>
|
|---|
| 449 | <td colspan="3">
|
|---|
| 450 | <h3>mysql</h3>
|
|---|
| 451 | </td>
|
|---|
| 452 | </tr>
|
|---|
| 453 | <tr>
|
|---|
| 454 | <td><b>Parameter</b></td>
|
|---|
| 455 | <td><b>Value</b></td>
|
|---|
| 456 | <td><b>Description</b></td>
|
|---|
| 457 | </tr>
|
|---|
| 458 | <tr bgcolor="#f0f0f0">
|
|---|
| 459 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 460 | </tr>
|
|---|
| 461 | <tr>
|
|---|
| 462 | <td>MyISAM cache hit ratio</td>
|
|---|
| 463 | <td>56.5658301822</td>
|
|---|
| 464 | <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td>
|
|---|
| 465 | </tr>
|
|---|
| 466 | <tr>
|
|---|
| 467 | <td>InnoDB cache hit ratio</td>
|
|---|
| 468 | <td>0</td>
|
|---|
| 469 | <td><font color="red"><b>Cache ratio should be at least 90%</b></font></td>
|
|---|
| 470 | </tr>
|
|---|
| 471 | <tr>
|
|---|
| 472 | <td>sql cache hit ratio</td>
|
|---|
| 473 | <td>0</td>
|
|---|
| 474 | <td> </td>
|
|---|
| 475 | </tr>
|
|---|
| 476 | <tr bgcolor="#f0f0f0">
|
|---|
| 477 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 478 | </tr>
|
|---|
| 479 | <tr>
|
|---|
| 480 | <td>data reads</td>
|
|---|
| 481 | <td>2622</td>
|
|---|
| 482 | <td>Number of selects (Key_reads is not accurate)</td>
|
|---|
| 483 | </tr>
|
|---|
| 484 | <tr>
|
|---|
| 485 | <td>data writes</td>
|
|---|
| 486 | <td>2415.5</td>
|
|---|
| 487 | <td>Number of inserts/updates/deletes * coef (Key_writes is not
|
|---|
| 488 | accurate)</td>
|
|---|
| 489 | </tr>
|
|---|
| 490 | <tr bgcolor="#f0f0f0">
|
|---|
| 491 | <td colspan="3"><i>Data Cache</i> </td>
|
|---|
| 492 | </tr>
|
|---|
| 493 | <tr>
|
|---|
| 494 | <td>MyISAM data cache size</td>
|
|---|
| 495 | <td>512K</td>
|
|---|
| 496 | <td> </td>
|
|---|
| 497 | </tr>
|
|---|
| 498 | <tr>
|
|---|
| 499 | <td>BDB data cache size</td>
|
|---|
| 500 | <td>8388600</td>
|
|---|
| 501 | <td> </td>
|
|---|
| 502 | </tr>
|
|---|
| 503 | <tr>
|
|---|
| 504 | <td>InnoDB data cache size</td>
|
|---|
| 505 | <td>8M</td>
|
|---|
| 506 | <td> </td>
|
|---|
| 507 | </tr>
|
|---|
| 508 | <tr bgcolor="#f0f0f0">
|
|---|
| 509 | <td colspan="3"><i>Memory Pools</i> </td>
|
|---|
| 510 | </tr>
|
|---|
| 511 | <tr>
|
|---|
| 512 | <td>read buffer size</td>
|
|---|
| 513 | <td>131072</td>
|
|---|
| 514 | <td>(per session)</td>
|
|---|
| 515 | </tr>
|
|---|
| 516 | <tr>
|
|---|
| 517 | <td>sort buffer size</td>
|
|---|
| 518 | <td>65528</td>
|
|---|
| 519 | <td>Size of sort buffer (per session)</td>
|
|---|
| 520 | </tr>
|
|---|
| 521 | <tr>
|
|---|
| 522 | <td>table cache</td>
|
|---|
| 523 | <td>4</td>
|
|---|
| 524 | <td>Number of tables to keep open</td>
|
|---|
| 525 | </tr>
|
|---|
| 526 | <tr bgcolor="#f0f0f0">
|
|---|
| 527 | <td colspan="3"><i>Connections</i> </td>
|
|---|
| 528 | </tr>
|
|---|
| 529 | <tr>
|
|---|
| 530 | <td>current connections</td>
|
|---|
| 531 | <td>3</td>
|
|---|
| 532 | <td> </td>
|
|---|
| 533 | </tr>
|
|---|
| 534 | <tr>
|
|---|
| 535 | <td>max connections</td>
|
|---|
| 536 | <td>100</td>
|
|---|
| 537 | <td> </td>
|
|---|
| 538 | </tr>
|
|---|
| 539 | </tbody>
|
|---|
| 540 | </table>
|
|---|
| 541 | <p> </p>
|
|---|
| 542 | <p><a name="mssql" id="mssql"></a></p>
|
|---|
| 543 | <table bgcolor="white" border="1">
|
|---|
| 544 | <tbody>
|
|---|
| 545 | <tr>
|
|---|
| 546 | <td colspan="3">
|
|---|
| 547 | <h3>mssql</h3>
|
|---|
| 548 | </td>
|
|---|
| 549 | </tr>
|
|---|
| 550 | <tr>
|
|---|
| 551 | <td><b>Parameter</b></td>
|
|---|
| 552 | <td><b>Value</b></td>
|
|---|
| 553 | <td><b>Description</b></td>
|
|---|
| 554 | </tr>
|
|---|
| 555 | <tr bgcolor="#f0f0f0">
|
|---|
| 556 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 557 | </tr>
|
|---|
| 558 | <tr>
|
|---|
| 559 | <td>data cache hit ratio</td>
|
|---|
| 560 | <td>99.9999694824</td>
|
|---|
| 561 | <td> </td>
|
|---|
| 562 | </tr>
|
|---|
| 563 | <tr>
|
|---|
| 564 | <td>prepared sql hit ratio</td>
|
|---|
| 565 | <td>99.7738579828</td>
|
|---|
| 566 | <td> </td>
|
|---|
| 567 | </tr>
|
|---|
| 568 | <tr>
|
|---|
| 569 | <td>adhoc sql hit ratio</td>
|
|---|
| 570 | <td>98.4540169133</td>
|
|---|
| 571 | <td> </td>
|
|---|
| 572 | </tr>
|
|---|
| 573 | <tr bgcolor="#f0f0f0">
|
|---|
| 574 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 575 | </tr>
|
|---|
| 576 | <tr>
|
|---|
| 577 | <td>data reads</td>
|
|---|
| 578 | <td>2858</td>
|
|---|
| 579 | <td> </td>
|
|---|
| 580 | </tr>
|
|---|
| 581 | <tr>
|
|---|
| 582 | <td>data writes</td>
|
|---|
| 583 | <td>1438</td>
|
|---|
| 584 | <td> </td>
|
|---|
| 585 | </tr>
|
|---|
| 586 | <tr bgcolor="#f0f0f0">
|
|---|
| 587 | <td colspan="3"><i>Data Cache</i> </td>
|
|---|
| 588 | </tr>
|
|---|
| 589 | <tr>
|
|---|
| 590 | <td>data cache size</td>
|
|---|
| 591 | <td>4362</td>
|
|---|
| 592 | <td>in K</td>
|
|---|
| 593 | </tr>
|
|---|
| 594 | <tr bgcolor="#f0f0f0">
|
|---|
| 595 | <td colspan="3"><i>Connections</i> </td>
|
|---|
| 596 | </tr>
|
|---|
| 597 | <tr>
|
|---|
| 598 | <td>current connections</td>
|
|---|
| 599 | <td>14</td>
|
|---|
| 600 | <td> </td>
|
|---|
| 601 | </tr>
|
|---|
| 602 | <tr>
|
|---|
| 603 | <td>max connections</td>
|
|---|
| 604 | <td>32767</td>
|
|---|
| 605 | <td> </td>
|
|---|
| 606 | </tr>
|
|---|
| 607 | </tbody>
|
|---|
| 608 | </table>
|
|---|
| 609 | <p> </p>
|
|---|
| 610 | <p><a name="oci8" id="oci8"></a></p>
|
|---|
| 611 | <table bgcolor="white" border="1">
|
|---|
| 612 | <tbody>
|
|---|
| 613 | <tr>
|
|---|
| 614 | <td colspan="3">
|
|---|
| 615 | <h3>oci8</h3>
|
|---|
| 616 | </td>
|
|---|
| 617 | </tr>
|
|---|
| 618 | <tr>
|
|---|
| 619 | <td><b>Parameter</b></td>
|
|---|
| 620 | <td><b>Value</b></td>
|
|---|
| 621 | <td><b>Description</b></td>
|
|---|
| 622 | </tr>
|
|---|
| 623 | <tr bgcolor="#f0f0f0">
|
|---|
| 624 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 625 | </tr>
|
|---|
| 626 | <tr>
|
|---|
| 627 | <td>data cache hit ratio</td>
|
|---|
| 628 | <td>96.98</td>
|
|---|
| 629 | <td> </td>
|
|---|
| 630 | </tr>
|
|---|
| 631 | <tr>
|
|---|
| 632 | <td>sql cache hit ratio</td>
|
|---|
| 633 | <td>99.96</td>
|
|---|
| 634 | <td> </td>
|
|---|
| 635 | </tr>
|
|---|
| 636 | <tr bgcolor="#f0f0f0">
|
|---|
| 637 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 638 | </tr>
|
|---|
| 639 | <tr>
|
|---|
| 640 | <td>data reads</td>
|
|---|
| 641 | <td>842938</td>
|
|---|
| 642 | <td> </td>
|
|---|
| 643 | </tr>
|
|---|
| 644 | <tr>
|
|---|
| 645 | <td>data writes</td>
|
|---|
| 646 | <td>16852</td>
|
|---|
| 647 | <td> </td>
|
|---|
| 648 | </tr>
|
|---|
| 649 | <tr bgcolor="#f0f0f0">
|
|---|
| 650 | <td colspan="3"><i>Data Cache</i> </td>
|
|---|
| 651 | </tr>
|
|---|
| 652 | <tr>
|
|---|
| 653 | <td>data cache buffers</td>
|
|---|
| 654 | <td>3072</td>
|
|---|
| 655 | <td>Number of cache buffers</td>
|
|---|
| 656 | </tr>
|
|---|
| 657 | <tr>
|
|---|
| 658 | <td>data cache blocksize</td>
|
|---|
| 659 | <td>8192</td>
|
|---|
| 660 | <td> </td>
|
|---|
| 661 | </tr>
|
|---|
| 662 | <tr>
|
|---|
| 663 | <td>data cache size</td>
|
|---|
| 664 | <td>48M</td>
|
|---|
| 665 | <td>shared_pool_size</td>
|
|---|
| 666 | </tr>
|
|---|
| 667 | <tr bgcolor="#f0f0f0">
|
|---|
| 668 | <td colspan="3"><i>Memory Pools</i> </td>
|
|---|
| 669 | </tr>
|
|---|
| 670 | <tr>
|
|---|
| 671 | <td>java pool size</td>
|
|---|
| 672 | <td>0</td>
|
|---|
| 673 | <td>java_pool_size</td>
|
|---|
| 674 | </tr>
|
|---|
| 675 | <tr>
|
|---|
| 676 | <td>sort buffer size</td>
|
|---|
| 677 | <td>512K</td>
|
|---|
| 678 | <td>sort_area_size (per query)</td>
|
|---|
| 679 | </tr>
|
|---|
| 680 | <tr>
|
|---|
| 681 | <td>user session buffer size</td>
|
|---|
| 682 | <td>8M</td>
|
|---|
| 683 | <td>large_pool_size</td>
|
|---|
| 684 | </tr>
|
|---|
| 685 | <tr bgcolor="#f0f0f0">
|
|---|
| 686 | <td colspan="3"><i>Connections</i> </td>
|
|---|
| 687 | </tr>
|
|---|
| 688 | <tr>
|
|---|
| 689 | <td>current connections</td>
|
|---|
| 690 | <td>1</td>
|
|---|
| 691 | <td> </td>
|
|---|
| 692 | </tr>
|
|---|
| 693 | <tr>
|
|---|
| 694 | <td>max connections</td>
|
|---|
| 695 | <td>170</td>
|
|---|
| 696 | <td> </td>
|
|---|
| 697 | </tr>
|
|---|
| 698 | <tr>
|
|---|
| 699 | <td>data cache utilization ratio</td>
|
|---|
| 700 | <td>88.46</td>
|
|---|
| 701 | <td>Percentage of data cache actually in use</td>
|
|---|
| 702 | </tr>
|
|---|
| 703 | <tr>
|
|---|
| 704 | <td>user cache utilization ratio</td>
|
|---|
| 705 | <td>91.76</td>
|
|---|
| 706 | <td>Percentage of user cache (large_pool) actually in use</td>
|
|---|
| 707 | </tr>
|
|---|
| 708 | <tr>
|
|---|
| 709 | <td>rollback segments</td>
|
|---|
| 710 | <td>11</td>
|
|---|
| 711 | <td> </td>
|
|---|
| 712 | </tr>
|
|---|
| 713 | <tr bgcolor="#f0f0f0">
|
|---|
| 714 | <td colspan="3"><i>Transactions</i> </td>
|
|---|
| 715 | </tr>
|
|---|
| 716 | <tr>
|
|---|
| 717 | <td>peak transactions</td>
|
|---|
| 718 | <td>24</td>
|
|---|
| 719 | <td>Taken from high-water-mark</td>
|
|---|
| 720 | </tr>
|
|---|
| 721 | <tr>
|
|---|
| 722 | <td>max transactions</td>
|
|---|
| 723 | <td>187</td>
|
|---|
| 724 | <td>max transactions / rollback segments < 3.5 (or
|
|---|
| 725 | transactions_per_rollback_segment)</td>
|
|---|
| 726 | </tr>
|
|---|
| 727 | <tr bgcolor="#f0f0f0">
|
|---|
| 728 | <td colspan="3"><i>Parameters</i> </td>
|
|---|
| 729 | </tr>
|
|---|
| 730 | <tr>
|
|---|
| 731 | <td>cursor sharing</td>
|
|---|
| 732 | <td>EXACT</td>
|
|---|
| 733 | <td>Cursor reuse strategy. Recommended is FORCE (8i+) or SIMILAR
|
|---|
| 734 | (9i+). See <a
|
|---|
| 735 | href="http://www.praetoriate.com/oracle_tips_cursor_sharing.htm">cursor_sharing</a>.</td>
|
|---|
| 736 | </tr>
|
|---|
| 737 | <tr>
|
|---|
| 738 | <td>index cache cost</td>
|
|---|
| 739 | <td>0</td>
|
|---|
| 740 | <td>% of indexed data blocks expected in the cache. Recommended
|
|---|
| 741 | is 20-80. Default is 0. See <a
|
|---|
| 742 | href="http://www.dba-oracle.com/oracle_tips_cbo_part1.htm">optimizer_index_caching</a>.</td>
|
|---|
| 743 | </tr>
|
|---|
| 744 | <tr>
|
|---|
| 745 | <td>random page cost</td>
|
|---|
| 746 | <td>100</td>
|
|---|
| 747 | <td>Recommended is 10-50 for TP, and 50 for data warehouses.
|
|---|
| 748 | Default is 100. See <a
|
|---|
| 749 | href="http://www.dba-oracle.com/oracle_tips_cost_adj.htm">optimizer_index_cost_adj</a>.
|
|---|
| 750 | </td>
|
|---|
| 751 | </tr>
|
|---|
| 752 | </tbody>
|
|---|
| 753 | </table>
|
|---|
| 754 | <h3>Suspicious SQL</h3>
|
|---|
| 755 | <table bgcolor="white" border="1">
|
|---|
| 756 | <tbody>
|
|---|
| 757 | <tr>
|
|---|
| 758 | <td><b>LOAD</b></td>
|
|---|
| 759 | <td><b>EXECUTES</b></td>
|
|---|
| 760 | <td><b>SQL_TEXT</b></td>
|
|---|
| 761 | </tr>
|
|---|
| 762 | <tr>
|
|---|
| 763 | <td align="right"> .73%</td>
|
|---|
| 764 | <td align="right">89</td>
|
|---|
| 765 | <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
|
|---|
| 766 | sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576)
|
|---|
| 767 | and o.obj#=t.obj# and o.owner# = u.user# select i.obj#, i.flags,
|
|---|
| 768 | u.name, o.name from sys.obj$ o, sys.user$ u, sys.ind$ i where
|
|---|
| 769 | (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512) and
|
|---|
| 770 | (not((i.type# = 9) and bitand(i.flags,8) = 8)) and o.obj#=i.obj# and
|
|---|
| 771 | o.owner# = u.user# </td>
|
|---|
| 772 | </tr>
|
|---|
| 773 | <tr>
|
|---|
| 774 | <td align="right"> .84%</td>
|
|---|
| 775 | <td align="right">3</td>
|
|---|
| 776 | <td>select /*+ RULE */ distinct tabs.table_name, tabs.owner ,
|
|---|
| 777 | partitioned, iot_type , TEMPORARY, table_type, table_type_owner from
|
|---|
| 778 | DBA_ALL_TABLES tabs where tabs.owner = :own </td>
|
|---|
| 779 | </tr>
|
|---|
| 780 | <tr>
|
|---|
| 781 | <td align="right"> 3.95%</td>
|
|---|
| 782 | <td align="right">6</td>
|
|---|
| 783 | <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM
|
|---|
| 784 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE
|
|---|
| 785 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner =
|
|---|
| 786 | seg.owner and obj.object_name = seg.segment_name and obj.object_type =
|
|---|
| 787 | seg.segment_type and seg.buffer_pool = buf.name and buf.name =
|
|---|
| 788 | 'DEFAULT' </td>
|
|---|
| 789 | </tr>
|
|---|
| 790 | <tr>
|
|---|
| 791 | <td align="right"> 4.50%</td>
|
|---|
| 792 | <td align="right">6</td>
|
|---|
| 793 | <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM
|
|---|
| 794 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE
|
|---|
| 795 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner =
|
|---|
| 796 | seg.owner and obj.object_name = seg.segment_name and obj.object_type =
|
|---|
| 797 | seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td>
|
|---|
| 798 | </tr>
|
|---|
| 799 | <tr>
|
|---|
| 800 | <td align="right">57.34%</td>
|
|---|
| 801 | <td align="right">9267</td>
|
|---|
| 802 | <td>select t.schema, t.name, t.flags, q.name from
|
|---|
| 803 | system.aq$_queue_tables t, sys.aq$_queue_table_affinities aft,
|
|---|
| 804 | system.aq$_queues q where aft.table_objno = t.objno and
|
|---|
| 805 | aft.owner_instance = :1 and q.table_objno = t.objno and q.usage = 0 and
|
|---|
| 806 | bitand(t.flags, 4+16+32+64+128+256) = 0 for update of t.name,
|
|---|
| 807 | aft.table_objno skip locked </td>
|
|---|
| 808 | </tr>
|
|---|
| 809 | </tbody>
|
|---|
| 810 | </table>
|
|---|
| 811 | <h3>Expensive SQL</h3>
|
|---|
| 812 | <table bgcolor="white" border="1">
|
|---|
| 813 | <tbody>
|
|---|
| 814 | <tr>
|
|---|
| 815 | <td><b>LOAD</b></td>
|
|---|
| 816 | <td><b>EXECUTES</b></td>
|
|---|
| 817 | <td><b>SQL_TEXT</b></td>
|
|---|
| 818 | </tr>
|
|---|
| 819 | <tr>
|
|---|
| 820 | <td align="right"> 5.24%</td>
|
|---|
| 821 | <td align="right">1</td>
|
|---|
| 822 | <td>select round(sum(bytes)/1048576) from dba_segments </td>
|
|---|
| 823 | </tr>
|
|---|
| 824 | <tr>
|
|---|
| 825 | <td align="right"> 6.89%</td>
|
|---|
| 826 | <td align="right">6</td>
|
|---|
| 827 | <td>SELECT round(count(1)*avg(buf.block_size)/1048576) FROM
|
|---|
| 828 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, v$buffer_pool buf WHERE
|
|---|
| 829 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner =
|
|---|
| 830 | seg.owner and obj.object_name = seg.segment_name and obj.object_type =
|
|---|
| 831 | seg.segment_type and seg.buffer_pool = buf.name and buf.name =
|
|---|
| 832 | 'DEFAULT' </td>
|
|---|
| 833 | </tr>
|
|---|
| 834 | <tr>
|
|---|
| 835 | <td align="right"> 7.85%</td>
|
|---|
| 836 | <td align="right">6</td>
|
|---|
| 837 | <td>SELECT round(count(1)*avg(tsp.block_size)/1048576) FROM
|
|---|
| 838 | DBA_OBJECTS obj, V$BH bh, dba_segments seg, dba_tablespaces tsp WHERE
|
|---|
| 839 | obj.object_id = bh.objd AND obj.owner != 'SYS' and obj.owner =
|
|---|
| 840 | seg.owner and obj.object_name = seg.segment_name and obj.object_type =
|
|---|
| 841 | seg.segment_type and seg.tablespace_name = tsp.tablespace_name </td>
|
|---|
| 842 | </tr>
|
|---|
| 843 | <tr>
|
|---|
| 844 | <td align="right">33.69%</td>
|
|---|
| 845 | <td align="right">89</td>
|
|---|
| 846 | <td>select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
|
|---|
| 847 | sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) = 1048576)
|
|---|
| 848 | and o.obj#=t.obj# and o.owner# = u.user# </td>
|
|---|
| 849 | </tr>
|
|---|
| 850 | <tr>
|
|---|
| 851 | <td align="right">36.44%</td>
|
|---|
| 852 | <td align="right">89</td>
|
|---|
| 853 | <td>select i.obj#, i.flags, u.name, o.name from sys.obj$ o,
|
|---|
| 854 | sys.user$ u, sys.ind$ i where (bitand(i.flags, 256) = 256 or
|
|---|
| 855 | bitand(i.flags, 512) = 512) and (not((i.type# = 9) and
|
|---|
| 856 | bitand(i.flags,8) = 8)) and o.obj#=i.obj# and o.owner# = u.user# </td>
|
|---|
| 857 | </tr>
|
|---|
| 858 | </tbody>
|
|---|
| 859 | </table>
|
|---|
| 860 | <p><a name="postgres" id="postgres"></a></p>
|
|---|
| 861 | <table bgcolor="white" border="1">
|
|---|
| 862 | <tbody>
|
|---|
| 863 | <tr>
|
|---|
| 864 | <td colspan="3">
|
|---|
| 865 | <h3>postgres7</h3>
|
|---|
| 866 | </td>
|
|---|
| 867 | </tr>
|
|---|
| 868 | <tr>
|
|---|
| 869 | <td><b>Parameter</b></td>
|
|---|
| 870 | <td><b>Value</b></td>
|
|---|
| 871 | <td><b>Description</b></td>
|
|---|
| 872 | </tr>
|
|---|
| 873 | <tr bgcolor="#f0f0f0">
|
|---|
| 874 | <td colspan="3"><i>Ratios</i> </td>
|
|---|
| 875 | </tr>
|
|---|
| 876 | <tr>
|
|---|
| 877 | <td>statistics collector</td>
|
|---|
| 878 | <td>FALSE</td>
|
|---|
| 879 | <td>Must be set to TRUE to enable hit ratio statistics (<i>stats_start_collector</i>,<i>stats_row_level</i>
|
|---|
| 880 | and <i>stats_block_level</i> must be set to true in postgresql.conf)</td>
|
|---|
| 881 | </tr>
|
|---|
| 882 | <tr>
|
|---|
| 883 | <td>data cache hit ratio</td>
|
|---|
| 884 | <td>99.9666031916603</td>
|
|---|
| 885 | <td> </td>
|
|---|
| 886 | </tr>
|
|---|
| 887 | <tr bgcolor="#f0f0f0">
|
|---|
| 888 | <td colspan="3"><i>IO</i> </td>
|
|---|
| 889 | </tr>
|
|---|
| 890 | <tr>
|
|---|
| 891 | <td>data reads</td>
|
|---|
| 892 | <td>15</td>
|
|---|
| 893 | <td> </td>
|
|---|
| 894 | </tr>
|
|---|
| 895 | <tr>
|
|---|
| 896 | <td>data writes</td>
|
|---|
| 897 | <td>0.000000000000000000</td>
|
|---|
| 898 | <td>Count of inserts/updates/deletes * coef</td>
|
|---|
| 899 | </tr>
|
|---|
| 900 | <tr bgcolor="#f0f0f0">
|
|---|
| 901 | <td colspan="3"><i>Data Cache</i> </td>
|
|---|
| 902 | </tr>
|
|---|
| 903 | <tr>
|
|---|
| 904 | <td>data cache buffers</td>
|
|---|
| 905 | <td>1280</td>
|
|---|
| 906 | <td>Number of cache buffers. <a
|
|---|
| 907 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#basic">Tuning</a></td>
|
|---|
| 908 | </tr>
|
|---|
| 909 | <tr>
|
|---|
| 910 | <td>cache blocksize</td>
|
|---|
| 911 | <td>8192</td>
|
|---|
| 912 | <td>(estimate)</td>
|
|---|
| 913 | </tr>
|
|---|
| 914 | <tr>
|
|---|
| 915 | <td>data cache size</td>
|
|---|
| 916 | <td>10M</td>
|
|---|
| 917 | <td> </td>
|
|---|
| 918 | </tr>
|
|---|
| 919 | <tr>
|
|---|
| 920 | <td>operating system cache size</td>
|
|---|
| 921 | <td>80000K</td>
|
|---|
| 922 | <td>(effective cache size)</td>
|
|---|
| 923 | </tr>
|
|---|
| 924 | <tr bgcolor="#f0f0f0">
|
|---|
| 925 | <td colspan="3"><i>Memory Pools</i> </td>
|
|---|
| 926 | </tr>
|
|---|
| 927 | <tr>
|
|---|
| 928 | <td>sort buffer size</td>
|
|---|
| 929 | <td>1M</td>
|
|---|
| 930 | <td>Size of sort buffer (per query)</td>
|
|---|
| 931 | </tr>
|
|---|
| 932 | <tr bgcolor="#f0f0f0">
|
|---|
| 933 | <td colspan="3"><i>Connections</i> </td>
|
|---|
| 934 | </tr>
|
|---|
| 935 | <tr>
|
|---|
| 936 | <td>current connections</td>
|
|---|
| 937 | <td>13</td>
|
|---|
| 938 | <td> </td>
|
|---|
| 939 | </tr>
|
|---|
| 940 | <tr>
|
|---|
| 941 | <td>max connections</td>
|
|---|
| 942 | <td>32</td>
|
|---|
| 943 | <td> </td>
|
|---|
| 944 | </tr>
|
|---|
| 945 | <tr bgcolor="#f0f0f0">
|
|---|
| 946 | <td colspan="3"><i>Parameters</i> </td>
|
|---|
| 947 | </tr>
|
|---|
| 948 | <tr>
|
|---|
| 949 | <td>rollback buffers</td>
|
|---|
| 950 | <td>8</td>
|
|---|
| 951 | <td>WAL buffers</td>
|
|---|
| 952 | </tr>
|
|---|
| 953 | <tr>
|
|---|
| 954 | <td>random page cost</td>
|
|---|
| 955 | <td>4</td>
|
|---|
| 956 | <td>Cost of doing a seek (default=4). See <a
|
|---|
| 957 | href="http://www.varlena.com/GeneralBits/Tidbits/perf.html#less">random_page_cost</a></td>
|
|---|
| 958 | </tr>
|
|---|
| 959 | </tbody>
|
|---|
| 960 | </table>
|
|---|
| 961 | </body>
|
|---|
| 962 | </html>
|
|---|
