Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\ratug\spa_upgrade007.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>Upgrading from Oracle Database 10g Release 2 and Newer Releases</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="Upgrading from Oracle Database 10g Release 2 and Newer Releases" /> <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_upgrade006.htm" title="Previous" type="text/html" /> <link rel="next" href="spa_upgrade008.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_upgrade006.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade008.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CIAJDJBD" name="CIAJDJBD"></a><a id="RATUG221" name="RATUG221"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h1>Upgrading from Oracle Database 10<span class="italic">g</span> Release 2 and Newer Releases</h1> <a name="BEGIN" id="BEGIN"></a> <p>You can use SQL Performance Analyzer to test the impact on SQL response time of a database upgrade from Oracle Database 10<span class="italic">g</span> Release 2 or a newer release to any later release by capturing a SQL tuning set on the production system, then executing it twice remotely over a database link on a test system—first to create a pre-change SQL trial, then again to create a post-change SQL trial.</p> <p><a id="sthref91" name="sthref91"></a>The <a id="sthref92" name="sthref92"></a>production system which you are upgrading from should be running Oracle Database 10<span class="italic">g</span> Release 2 or a newer release. Initially, the <a id="sthref93" name="sthref93"></a>test system should also be running the same release. To ensure that the analysis made by SQL Performance Analyzer is accurate, the test system should contain an exact copy of the production data found on the production system. Furthermore, the hardware configuration should also be as similar to the production system as possible.</p> <p>Next, you will need to set up a separate SQL Performance Analyzer <a id="sthref94" name="sthref94"></a>system running Oracle Database 11<span class="italic">g</span> Release 1 or a newer release. The database version should be release 11.1.0.7 or higher. You will be using this system to run SQL Performance Analyzer. Neither your production data or schema need to be available on this system, since the SQL tuning set will be built using statistics stored in the SQL trace files from the production system. SQL Performance Analyzer tasks will be executed remotely on the test system to generate the execution plan and statistics for the SQL trial over a database link that you specify. The database link must be a public database link that connects to a user with the <code>EXECUTE</code> privilege for the <code>DBMS_SQLPA</code> package and the <code>ADVISOR</code> privilege on the test system. You should also drop any existing <code>PLAN_TABLE</code> from the user's schema on the test system.</p> <p>Once the upgrade environment is configured as described, perform the steps as described in the following procedure to use SQL Performance Analyzer in a database upgrade from Oracle Database 10<span class="italic">g</span> Release 2 or a newer release to any later release.</p> <ol> <li> <p>On the production system, capture the SQL workload that you intend to analyze and store it in a SQL tuning set, as described in <a href="spa_intro001.htm#CHDFCBDC">"Capturing the SQL Workload"</a>.</p> </li> <li> <p>Set up the test system so that it matches the production environment as closely as possible, as described in <a href="spa_intro002.htm#CHDCADGG">"Setting Up the Test System"</a>.</p> </li> <li> <p>Transport the SQL tuning set to the SQL Performance Analyzer system.</p> <p>For information about transporting SQL tuning sets using:</p> <ul> <li> <p>Oracle Enterprise Manager, see <a href="topicid:TDPPT159">Transporting SQL Tuning Sets</a></p> </li> <li> <p>APIs, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=PFGRF02604','newWindow').focus()"><span class="italic">Oracle Database Performance Tuning Guide</span></a></p> </li> </ul> </li> <li> <p>On the SQL Performance Analyzer system, create a <a id="sthref95" name="sthref95"></a>SQL Performance Analyzer task using the SQL tuning set as its input source.</p> <p>Remotely test execute the SQL statements in the SQL tuning set on the test system over a database link to build a pre-upgrade <a id="sthref96" name="sthref96"></a>SQL trial that will be used as a baseline for comparison, as described in <a href="spa_upgrade008.htm#CHDBHIHH">"Running SQL Performance Analyzer to Test a Database Upgrade from Oracle Database 10<span class="italic">g</span> Release 2 and Newer Releases"</a>.</p> </li> <li> <p>Upgrade the test system.</p> </li> <li> <p>Remotely test execute the SQL statements a second time on the upgraded test system over a database link to build a post-upgrade <a id="sthref97" name="sthref97"></a>SQL trial, as described in <a href="spa_upgrade008.htm#CHDBHIHH">"Running SQL Performance Analyzer to Test a Database Upgrade from Oracle Database 10<span class="italic">g</span> Release 2 and Newer Releases"</a>.</p> </li> <li> <p>Compare <a id="sthref98" name="sthref98"></a>SQL performance and fix <a id="sthref99" name="sthref99"></a>regressed SQL.</p> <p>SQL Performance Analyzer compares the performance of SQL statements read from the SQL tuning set during the pre-upgrade SQL trial to those captured from the remote test execution during the post-upgrade SQL trial. A report is produced to identify any changes in execution plans or performance of the SQL statements.</p> <p>If the report reveals any regressed SQL statements, you can make further changes to fix the regressed SQL, as described in <a href="spa_upgrade010.htm#CIADFIIF">"Tuning Regressed SQL Statements After Testing a Database Upgrade"</a>.</p> <p>Repeat the process of executing the SQL tuning set and comparing its performance to a previous execution to test any changes made until you are satisfied with the outcome of the analysis.</p> </li> </ol> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="spa_upgrade008.htm#CHDBHIHH">Running SQL Performance Analyzer to Test a Database Upgrade from Oracle Database 10<span class=italic">g</span> Release 2 and Newer Releases"</a></p> <p><a href="spa_upgrade.htm#CIADFIDI">Testing a Database Upgrade</a></p> <p><a href="spa_task001.htm#BABDBCDH">Creating an Analysis Task Using Enterprise Manager</a></p> </div> <!-- 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_upgrade006.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="spa_upgrade008.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --></div> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de