Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdppt\tdppt_sqltune002.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>Tuning SQL Manually Using SQL Tuning Advisor</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-05-27T15:13:2Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Tuning SQL Manually Using SQL Tuning Advisor" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10822-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="tdppt_sqltune001.htm" title="Previous" type="text/html" /> <link rel="next" href="tdppt_sqltune003.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="tdppt_sqltune001.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqltune003.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CACFDJHE" name="CACFDJHE"></a><a id="TDPPT310" name="TDPPT310"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1>Tuning SQL Manually Using SQL Tuning Advisor</h1> <a name="BEGIN" id="BEGIN"></a> <p>As described in <a href="tdppt_sqlid.htm#BABFHBFJ">Identifying High-Load SQL Statements</a>, Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click <span class="bold">Schedule/Run SQL Tuning Advisor</span> on the Recommendation Detail page to run SQL Tuning Advisor.</p> <p class="orderedlisttitle">To tune SQL statements manually using SQL Tuning Advisor<a id="sthref265" name="sthref265"></a><a id="sthref266" name="sthref266"></a>: </p> <ol> <li> <p>On the Database Home page, under Related Links, click <span class="bold">Advisor Central</span>.</p> <p>The Advisor Central page appears.</p> </li> <li> <p>Under Advisors, click <a href="callback:SQL_ADVISORS"><span class="bold">SQL Advisors</span></a>.</p> <p>The SQL Advisors page appears.</p> </li> <li> <p>Under SQL Tuning Advisor, click <span class="bold">SQL Tuning Advisor</span>.</p> <p>The Schedule SQL Tuning Advisor page appears.</p> </li> <li> <p>In the <span class="bold">Name</span> field, enter a name for the SQL tuning task.</p> <p>If unspecified, then SQL Tuning Advisor uses a system-generated name.</p> </li> <li> <p>Do one of the following:</p> <ul> <li> <p>To run a SQL tuning task for one or more high-load SQL statements, under SQL Tuning Advisor Data Source Links, click <a href="callback:TOP_ACTIVITY"><span class="bold">Top Activity</span></a>.</p> <p>The Top Activity page appears.</p> <p>Under Top SQL, select the SQL statement you want to tune and click <a href="callback:SCHEDULE_SQL_TUNING_ADVISOR"><span class="bold">Schedule SQL Tuning Advisor</span></a>. See <a href="tdppt_sqlid002.htm#BABECDJG">"Identifying High-Load SQL Statements Using Top SQL"</a> to learn how to identify high-load SQL statements using the Top Activity page.</p> </li> <li> <p>To run a SQL tuning task for historical SQL statements from the Automatic Workload Repository (AWR), under SQL Tuning Advisor Data Source Links, click <a href="callback:AWR_HISTORICAL_SQL"><span class="bold">Historical SQL (AWR)</span></a>.</p> <p>The Historical SQL (AWR) page appears.</p> <p>Under Historical SQL (AWR), click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that ran on the database. Under Detail for Selected 24 Hour Interval, select the SQL statement you want to tune, and click <span class="bold">Schedule SQL Tuning Advisor</span>.</p> </li> <li> <p>To run a SQL tuning task for a SQL Tuning Set, click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('SQL_TUNING_SETS');"><span class="bold">SQL Tuning Sets</span></a>.</p> <p>The SQL Tuning Sets page appears.</p> <p>Select the SQL Tuning Set that contains the SQL statements you want to tune and then click <span class="bold">Schedule SQL Tuning Advisor</span>. See <a href="tdppt_sqltune005.htm#CHDFABIG">"Creating a SQL Tuning Set"</a> to learn how to create SQL Tuning Sets.</p> </li> </ul> <p>The Schedule SQL Tuning Advisor page reappears.</p> </li> <li> <p>To display the SQL text of the selected statement, expand <span class="bold">SQL Statements</span>.</p> </li> <li> <p>Under Scope, select the scope of tuning to perform. Do one of the following:</p> <ul> <li> <p>Select <span class="bold">Limited</span>.</p> <p>A limited scope<a id="sthref267" name="sthref267"></a> takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile.</p> </li> <li> <p>Select <span class="bold">Comprehensive</span>, and then set a time limit (in minutes) for each SQL statement in the <span class="bold">Time Limit per Statement</span> field, and a total time limit (in minutes) in the <span class="bold">Total Time Limit</span> field. Note that setting the time limit too small may affect the quality of the recommendations.</p> <p>Comprehensive mode may take several minutes to tune a single SQL statement. This mode is both time and resource intensive because each query must be hard-parsed. Thus, you should only use comprehensive scope for high-load SQL statements that have a significant impact on the entire system.</p> </li> </ul> <p>See <a href="tdppt_sqltune019.htm#CHDDAGBE">"Managing SQL Profiles"</a> to learn more about SQL profiles.</p> </li> <li> <p>Under Schedule, do one of the following:</p> <ul> <li> <p>Select <span class="bold">Immediately</span> and then click <span class="bold">Submit</span> to run the SQL tuning task immediately.</p> <p>The Processing: SQL Tuning Advisor Task page appears.</p> </li> <li> <p>Select <span class="bold">Later</span> to schedule a specific time in the future, and then click <span class="bold">OK</span>.</p> </li> </ul> </li> <li> <p>On the Database Home page, under Related Links, click <span class="bold">Advisor Central</span>.</p> <p>The Advisor Central page appears.</p> <p>Under Advisor Tasks, the Results sections lists the result of advisors.</p> </li> <li> <p>Select a result from the table and then click <span class="bold">View Result</span>.</p> <p>The Recommendations for SQL ID page appears.</p> <p>If you used a SQL Tuning Set, then multiple recommendations may be shown. To help you decide whether to implement a recommendation, an estimated benefit of implementing the recommendation is displayed in the Benefit (%) column. The Rationale column displays an explanation of why the recommendation is made.</p> </li> <li> <p>To implement the recommendation, do one of the following:<a id="sthref268" name="sthref268"></a></p> <ul> <li> <p>If an automated solution is recommended, then click <span class="bold">Implement</span>.</p> <p><a id="sthref269" name="sthref269"></a>A confirmation page appears. Click <span class="bold">Yes</span> to confirm the change.</p> </li> <li> <p>If a manual solution is recommended, then consider implementing the recommendation.</p> </li> </ul> </li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdppt_sqltune001.htm#CACHECBD">Tuning SQL Statements Using SQL Tuning Advisor</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 © 2007, 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="tdppt_sqltune001.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqltune003.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