Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\ratug\spa_upgrade004.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>Building a SQL Tuning Set</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="Building a SQL Tuning Set" /> <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_upgrade003.htm" title="Previous" type="text/html" /> <link rel="next" href="spa_upgrade005.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_upgrade003.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade005.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CIAIFAFC" name="CIAIFAFC"></a><a id="RATUG214" name="RATUG214"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1>Building a SQL Tuning Set</h1> <a name="BEGIN" id="BEGIN"></a> <p>Once the SQL trace files and mapping table are moved to the SQL Performance Analyzer system, you can build a SQL tuning set using the <code>DBMS_SQLTUNE</code> package.</p> <p>To build a <a id="sthref84" name="sthref84"></a>SQL tuning set:</p> <ol> <li> <p>Copy the SQL trace files to a directory on the SQL Performance Analyzer system.</p> </li> <li> <p>Create a directory object for this directory.</p> </li> <li> <p>Use the <code>DBMS_SQLTUNE</code>.<a id="sthref85" name="sthref85"></a><code>SELECT_SQL_TRACE</code> function to read the SQL statements from the SQL trace files.</p> <p>For each SQL statement, only information for a single execution is collected. The execution frequency of each SQL statement is not captured. Therefore, when performing a comparison analysis for a production system running Oracle Database 10<span class="italic">g</span> Release 1 and older releases, you should ignore the workload-level statistics in the SQL Performance Analyzer report and only evaluate performance changes on an execution level.</p> <p>The following example reads the contents of SQL trace files stored in the <code>sql_trace_prod</code> directory object and loads them into a SQL tuning set.</p> <pre xml:space="preserve" class="oac_no_warn"> DECLARE cur sys_refcursor; BEGIN DBMS_SQLTUNE.CREATE_SQLSET('my_sts_9i'); OPEN cur FOR SELECT VALUE (P) FROM table(DBMS_SQLTUNE.SELECT_SQL_TRACE('sql_trace_prod', '%ora%')) P; DBMS_SQLTUNE.LOAD_SQLSET('my_sts_9i', cur); CLOSE cur; END; / </pre></li> </ol> <p>The syntax for the <a id="sthref86" name="sthref86"></a><code>SELECT_SQL_TRACE</code> function is as follows:</p> <pre xml:space="preserve" class="oac_no_warn"> DBMS_SQLTUNE.SELECT_SQL_TRACE ( directory IN VARCHAR2, file_name IN VARCHAR2 := NULL, mapping_table_name IN VARCHAR2 := NULL, mapping_table_owner IN VARCHAR2 := NULL, select_mode IN POSITIVE := SINGLE_EXECUTION, options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE, pattern_start IN VARCHAR2 := NULL, parttern_end IN VARCHAR2 := NULL, result_limit IN POSITIVE := NULL) RETURN sys.sqlset PIPELINED; </pre> <p><a href="#CIAHBDDF">Table: DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters</a> describes the available parameters for the <a id="sthref87" name="sthref87"></a><code>SELECT_SQL_TRACE</code> function.</p> <div class="tblformal"><a id="RATUG250" name="RATUG250"></a><a id="sthref88" name="sthref88"></a><a id="CIAHBDDF" name="CIAHBDDF"></a> <p class="titleintable">DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters</p> <table class="Formal" title="DBMS_SQLTUNE.SELECT_SQL_TRACE Function Parameters" summary="Lists the parameters and descriptions of each parameter for the DBMS_SQLTUNE.SELECT_SQL_TRACE function" dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="31%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t7">Parameter</th> <th align="left" valign="bottom" id="r1c2-t7">Description</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t7" headers="r1c1-t7"> <p><code>directory</code></p> </td> <td align="left" headers="r2c1-t7 r1c2-t7"> <p>Specifies the directory object pointing to the directory where the SQL trace files are stored.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t7" headers="r1c1-t7"> <p><code>file_name</code></p> </td> <td align="left" headers="r3c1-t7 r1c2-t7"> <p>Specifies all or part of the name of the SQL trace files to process. If unspecified, the current or most recent trace file in the specified directory will be used. % wildcards are supported for matching trace file names.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t7" headers="r1c1-t7"> <p><code>mapping_table_name</code></p> </td> <td align="left" headers="r4c1-t7 r1c2-t7"> <p>Specifies the name of the mapping table. If set to the default value of <code>NULL</code>, mappings from the current database will be used. Note that the mapping table name is not case-sensitive.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t7" headers="r1c1-t7"> <p><code>mapping_table_owner</code></p> </td> <td align="left" headers="r5c1-t7 r1c2-t7"> <p>Specifies the schema where the mapping table resides. If set to <code>NULL</code>, the current schema will be used.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r6c1-t7" headers="r1c1-t7"> <p><code>select_mode</code></p> </td> <td align="left" headers="r6c1-t7 r1c2-t7"> <p>Specifies the mode for selecting SQL statements from the trace files. The default value is <code>SINGLE_EXECUTION</code>. In this mode, only statistics for a single execution per SQL statement will be loaded into the SQL tuning set. The statistics are not cumulative, as is the case with other SQL tuning set data source table functions.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r7c1-t7" headers="r1c1-t7"> <p><code>options</code></p> </td> <td align="left" headers="r7c1-t7 r1c2-t7"> <p>Specifies the options for the operation. The default value is <code>LIMITED_COMMAND_TYPE</code>, only SQL types that are meaningful to SQL Performance Analyzer (such as <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code>) are returned from the SQL trace files.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r8c1-t7" headers="r1c1-t7"> <p><code>pattern_start</code></p> </td> <td align="left" headers="r8c1-t7 r1c2-t7"> <p>Specifies the opening delimiting pattern of the trace file sections to consider. This parameter is currently not used.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r9c1-t7" headers="r1c1-t7"> <p><code>pattern_end</code></p> </td> <td align="left" headers="r9c1-t7 r1c2-t7"> <p>Specifies the closing delimiting pattern of the trace file sections to process. This parameter is currently not used.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r10c1-t7" headers="r1c1-t7"> <p><code>result_limit</code></p> </td> <td align="left" headers="r10c1-t7 r1c2-t7"> <p>Specifies the top SQL from the (filtered) source. The default value is <code>MAXSB4</code>.</p> </td> </tr> </tbody> </table> <br /></div> <!-- class="tblformal" --> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=ARPLS220','newWindow').focus()"><span class="italic">Oracle Database PL/SQL Packages and Types Reference</span></a> for information about the <code>DBMS_SQLTUNE</code> 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_upgrade003.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade005.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