Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\ratug\spa_upgrade002.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Enabling SQL Trace on the Production System</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-06-04T10:50:31Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Enabling SQL Trace on the Production System" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E12254-01" /> <link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="prev" href="spa_upgrade001.htm" title="Previous" type="text/html" /> <link rel="next" href="spa_upgrade003.htm" title="Next" type="text/html" /> <script src="./callback.js" type="text/javascript"></script> <noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript> </head> <body> <div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="spa_upgrade001.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade003.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CIAFGCBI" name="CIAFGCBI"></a><a id="RATUG212" name="RATUG212"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1>Enabling SQL Trace on the Production System</h1> <a name="BEGIN" id="BEGIN"></a> <p>Oracle9<span class="italic">i</span> uses the <a id="sthref76" name="sthref76"></a>SQL Trace facility to collect performance data on individual SQL statements. The information generated by SQL Trace is stored in <a id="sthref77" name="sthref77"></a>SQL trace files. SQL Performance Analyzer consumes the following information from these files:</p> <ul> <li> <p>SQL text and username under which parse occurred</p> </li> <li> <p>Bind values for each execution</p> </li> <li> <p>CPU and elapsed times</p> </li> <li> <p>Physical reads and logical reads</p> </li> <li> <p>Number of rows processed</p> </li> <li> <p>Execution plan for each SQL statement (only captured if the cursor for the SQL statement is closed)</p> </li> </ul> <p>Although it is possible to enable <a id="sthref78" name="sthref78"></a>SQL Trace for an instance, it is recommended that you enable SQL Trace for a subset of sessions instead. When the SQL Trace facility is enabled for an instance, performance statistics for all SQL statements executed in the instance are stored into SQL trace files. Using SQL Trace in this way can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space. It is required that <a id="sthref79" name="sthref79"></a>trace level be set to 4 to capture bind values, along with the execution plans.</p> <p>For production systems running Oracle Database 10<span class="italic">g</span> Release 1, use the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to enable SQL Trace transparently in another session. You should also enable binds explicitly by setting the <code>binds</code> procedure parameter to <code>TRUE</code> (its default value is <code>FALSE</code>).</p> <p>After enabling SQL Trace, identify the SQL trace files containing statistics for a representative set of SQL statements that you want to use with SQL Performance Analyzer. You can then copy the <a id="sthref80" name="sthref80"></a>SQL trace files to the SQL Performance Analyzer system. Once the SQL workload is captured in the SQL trace files, disable SQL Trace on the production system.</p> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=PFGRF010','newWindow').focus()"><span class="italic">Oracle Database Performance Tuning Guide</span></a> for additional considerations when using SQL Trace, such as setting initialization parameters to manage SQL trace files</p> <p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=ARPLS091','newWindow').focus()"><span class="italic">Oracle Database PL/SQL Packages and Types Reference</span></a> for information about the DBMS_MONITOR package</p> <p><a href="spa_upgrade001.htm#CIAIFDIJ">Upgrading from Oracle9<span class=italic">i</span> Database and Oracle Database 10<span class="italic">g</span> Release 1"</a></p> </div> </div> <!-- class="sect2" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2008, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="spa_upgrade001.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade003.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de