Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdppt\tdppt_sqltune020.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>Managing SQL Execution Plans</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="Managing SQL Execution Plans" /> <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_sqltune019.htm" title="Previous" type="text/html" /> <link rel="next" href="tdppt_sqlopt.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_sqltune019.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqlopt.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CACCFCIG" name="CACCFCIG"></a><a id="TDPPT335" name="TDPPT335"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h1>Managing SQL Execution Plans</h1> <a name="BEGIN" id="BEGIN"></a> <p><span class="bold">SQL plan management</span> is a preventative mechanism that records and evaluates execution plans of SQL statements over time. The database builds <span class="bold">SQL plan baselines</span> consisting of a set of existing plans known to be efficient. If the same SQL statement runs repeatedly, and if the optimizer generates a new plan differing from the baseline, then the database compares the plan with the baseline and chooses the best one.</p> <p>SQL plan management avoids SQL performance regression. Events such as new optimizer statistics, changes to initialization parameters, database upgrades, and so on can cause changes to execution plans. These changes can cause SQL performance regressions that are difficult and time-consuming to fix manually. SQL plan baselines preserve performance of SQL statements, regardless of changes in the database.</p> <p class="orderedlisttitle">To load SQL execution plans: </p> <ol> <li> <p>From the Database Home page, click <span class="bold">Server</span>.</p> <p>The Server page appears.</p> </li> <li> <p>Under Query Optimizer, click <img src="shortcut.png" alt="callback" border="0" /><a href="javascript:void(0);" onclick="redirectEMpage('TOP_ACTIVITY');"><span class="bold">SQL Plan Control</span></a>.</p> <p>The SQL Profile subpage of the SQL Plan Control page appears.</p> </li> <li> <p>Click <span class="bold">SQL Plan Baseline</span>.</p> <p>The SQL Plan Baseline subpage appears.</p> </li> <li> <p>Under Settings, click the link next to <span class="bold">Capture SQL Plan Baselines</span>.</p> <p>The Initialization Parameters page appears.</p> </li> <li> <p>In the <span class="bold">Value</span> column of the table, select <span class="bold">TRUE</span> and then click <span class="bold">OK</span>.</p> <p>You are returned to the SQL Plan Baseline subpage, which now shows <span class="bold">Capture SQL Baselines</span> set to <span class="bold">TRUE</span>.</p> <p>Because you configured baselines to be captured, the database automatically keeps a history of execution plans for all SQL statements executed more than once.</p> </li> <li> <p>Click <span class="bold">Load</span>.</p> <p>The SQL Plan Control page appears.</p> </li> <li> <p>Select the SQL plan baselines to be loaded by completing the following steps:</p> <ol> <li> <p>Under Load SQL Plan Baselines, select <span class="bold">Load plans from SQL Tuning Set (STS)</span>.</p> </li> <li> <p>In <span class="bold">Job Name</span>, enter a name for the job. For example, enter <code>SPM_LOAD_TEST</code>.</p> </li> <li> <p>Under Schedule, select <span class="bold">Immediately</span>.</p> </li> <li> <p>Click <span class="bold">OK</span>.</p> </li> </ol> <p>The SQL Profile subpage of the SQL Plan Control page appears.</p> <p>The table displays a list of SQL plans that are stored as SQL plan baselines.</p> </li> <li> <p>Optionally, fix the execution plan of a baseline to prevent the database from using an alternative SQL plan baseline. Complete the following steps:</p> <ol> <li> <p>Select a SQL plan baseline that is not fixed.</p> </li> <li> <p>Select <span class="bold">Fixed - Yes</span> from the list preceding the baseline table.</p> </li> <li> <p>Click <span class="bold">Go</span>.</p> </li> </ol> <p>The table is refreshed to show the SQL execution plan with the value <code>YES</code> in the Fixed column of the table.</p> </li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=PFGRF007','newWindow').focus()"><span class="italic">Oracle Database Performance Tuning Guide</span></a> to learn how to use SQL plan management</p> <p><a href="tdppt_sqltune.htm#CAEBFCFJ">Tuning SQL Statements</a></p> </div> </div> <!-- class="sect1" --> <!-- 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_sqltune019.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqlopt.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