Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\xml\orarep\sqlpa\sqlpaHtml.xsl
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:include href="../xplan/xplHtml.xsl"/> <!-- exadata cell simulation mode ? --> <xsl:variable name="noCellSim" select="/report/head/task/execution[@label='current']/ property[@name='cell_simulation_enabled'] = 'FALSE' and /report/head/compare/execution[@label='before']/ property[@name='cell_simulation_enabled'] = 'FALSE' and /report/head/compare/execution[@label='after']/ property[@name='cell_simulation_enabled'] = 'FALSE'"/> <!-- compare sts 2 sts ? --> <xsl:variable name="sts2sts" select="/report/head/compare/execution[@label='before']/ property[@name='type'] = 'CONVERT SQLSET' and /report/head/compare/execution[@label='after']/ property[@name='type'] = 'CONVERT SQLSET'"/> <!-- advanced mode ? --> <xsl:variable name="level" select="/report/head/@level"/> <!-- plan format --> <xsl:variable name="plan_format" select="/report/head/@plan_format"/> <xsl:template match="/"> <html> <head> <title> SQL Performance Impact Analyzer Report <xsl:if test="report/summary/statements/@category"> - SQL with <xsl:value-of select="report/summary/statements/@category"/> </xsl:if> </title> <style type="text/css"> body, table, input, select, textarea {font:normal normal 8pt Verdana,Arial;text-decoration:none;color:#000000;} .s8 {font-size:8pt;color:#006699} .s9 {font-size:10pt;color:#006699} .s10 {font-size:14pt;color:#006699;} .s16 {border-width : 1px; border-color : #CCCC99; border-style: solid;color:#006699;font-size:8pt;} .s17 {border-width : 1px; border-color : #CCCC99; border-style: solid;font-size:8pt;} .s27 {border-width : 1px; border-color : #CCCC99; border-style: solid;} <!-- A { text-decoration:none } --> </style> </head> <body> <xsl:apply-templates select="report/head"/> <xsl:apply-templates select="report/summary"/> <xsl:apply-templates select="report/body"/> </body> </html> </xsl:template> <!-- +++++++++++++++++++++++++++ report header ++++++++++++++++++++++++++ --> <xsl:template match="head"> <a name="general"/> <a href="#general" style="text-decoration:none"> <font class="s10"> General Information </font> </a> <hr size="3" width="650" align="left"/> <br/> <xsl:apply-templates select="task"/> <xsl:apply-templates select="compare"/> </xsl:template> <!-- +++++++++++++++++++++++++++ report summary ++++++++++++++++++++++++++ --> <xsl:template match="summary"> <br/> <a name="summary"/> <a href="#summary" style="text-decoration:none"> <font class="s10"> Report Summary </font> </a> <hr size="3" width="650" align="left"/> <br/> <!-- produce a global error message if the current execution hash been --> <!-- interrupted, cancelled, timedout, or errored out whith a fatal error --> <xsl:if test="/report/head/task /execution[@label='current']/property[@name='err_msg']"> <xsl:value-of select="/report/head/task /execution[@label='current']/property[@name='err_msg']"/> <hr size="3" width="650" align="left"/> <br/> </xsl:if> <xsl:choose> <xsl:when test="impacts"> <xsl:apply-templates select="impacts"/> <xsl:apply-templates select="histograms"/> <xsl:apply-templates select="statements[@type='compare']"/> </xsl:when> <xsl:when test="statements[@type='sts2trial']"> <xsl:apply-templates select="statements[@type='sts2trial']"/> </xsl:when> <xsl:when test="statements[@type='explain plan']"> <xsl:apply-templates select="statements[@type='explain plan']"/> </xsl:when> <xsl:otherwise> <xsl:apply-templates select="statements[@type='execute']"/> </xsl:otherwise> </xsl:choose> <!-- common templates --> <xsl:apply-templates select="error_groups"/> <xsl:apply-templates select="errors"/> <xsl:apply-templates select="timeouts"/> <xsl:apply-templates select="unsupported"/> <hr size="3" width="650" align="left"/> </xsl:template> <!-- ++++++++++++++++++++++++++ report details ++++++++++++++++++++++++++ --> <xsl:template match="body"> <xsl:if test="object"> <a name="details"/> <a href="#details" style="text-decoration:none"> <font class="s10"><br/> Report Details </font> </a> <hr size="3" width="650" align="left"/> <xsl:for-each select="object"> <xsl:apply-templates select="sql"/> <!-- sql details compare or test_execute --> <xsl:choose> <xsl:when test="error"> <xsl:apply-templates select="error"/> </xsl:when> <xsl:when test="stats[@type='compare']"> <xsl:apply-templates select="stats[@type='compare']"/> <xsl:apply-templates select="findings"/> <xsl:apply-templates select="plans[@type='compare']"/> </xsl:when> <xsl:when test="stats[@type='execute']"> <xsl:apply-templates select="stats[@type='execute']"/> <xsl:apply-templates select="findings"/> <xsl:apply-templates select="plans[@type='execute']"/> </xsl:when> <xsl:when test="stats[@type='sts2trial']"> <xsl:apply-templates select="stats[@type='sts2trial']"/> <xsl:apply-templates select="plans[@type='sts2trial']"/> </xsl:when> </xsl:choose> <hr size="1" width="650" align="left"/> </xsl:for-each> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++++++ task information ++++++++++++++++++++++++++ --> <xsl:template match="task"> <table class="ex" cellspacing="0" border="0"> <tr> <td> <font class="s9"><b>Task Information:</b></font> <hr size="1" width="312" align="left"/> </td> <xsl:if test="../sqlset"> <td> <font class="s9"><b>Workload Information:</b></font> <hr size="1" width="315" align="left"/> </td> </xsl:if> </tr> <tr align="left" valign="top"> <td> <table class="ex" cellspacing="0" border="0"> <tr><th align="left">Task Name</th> <td>: <xsl:value-of select="property[@name='name']"/></td></tr> <tr><th align="left">Task Owner</th> <td>: <xsl:value-of select="property[@name='owner']"/></td></tr> <tr><th align="left">Description</th> <td>: <xsl:value-of select="property[@name='desc']"/></td></tr> </table> </td> <td> <xsl:apply-templates select="../sqlset"/> </td> </tr> </table> <!-- display current task execution details --> <xsl:apply-templates select="execution[@label='current']"/> </xsl:template> <!-- +++++++++++++++++++++++++ template execution +++++++++++++++++++++++++ --> <xsl:template match="execution[@label='current']"> <!-- display current execution information --> <br/> <font class="s9"><b>Execution Information:</b></font> <hr size="1" width="650" align="left"/> <table class="ex" cellspacing="0" border="0"> <tr> <td> <!-- table on the left --> <table class="ex" cellspacing="0" border="0"> <tr> <th align="left">Execution Name</th> <td>: <xsl:value-of select="./property[@name='name']"/> </td> </tr> <tr> <th align="left">Execution Type</th> <td>: <xsl:value-of select="./property[@name='type']"/> </td> </tr> <tr> <th align="left">Description</th> <td>: <xsl:value-of select="./property[@name='desc']"/> </td> </tr> <tr> <th align="left">Scope</th> <td>: <xsl:value-of select="./property[@name='mode']"/> </td> </tr> <tr> <th align="left">Status</th> <td>: <xsl:value-of select="./property[@name='status']"/> </td> </tr> <xsl:if test="(./property[@name='timeout'])"> <tr> <th align="left">Number of Timeouts</th> <td>: <xsl:value-of select="./property[@name='timeout']"/> </td> </tr> </xsl:if> <xsl:if test="(./property[@name='unsupported'])"> <tr> <th align="left">Number of Unsupported SQL</th> <td>: <xsl:value-of select="./property[@name='unsupported']"/> </td> </tr> </xsl:if> </table> </td> <!-- table on the right --> <td> <table class="ex" cellspacing="0" border="0"> <tr> <th align="left">Started </th> <td>: <xsl:value-of select="./property[@name='start']"/> </td> </tr> <tr><th align="left">Last Updated </th> <td>: <xsl:value-of select="./property[@name='modif']"/> </td> </tr> <xsl:if test="(./property[@name='time_limit'])"> <tr> <th align="left">Global Time Limit</th> <td>: <xsl:value-of select="./property[@name='time_limit']"/> </td> </tr> </xsl:if> <xsl:if test="(./property[@name='local_time_limit'])"> <tr> <th align="left">Per-SQL Time Limit</th> <td>: <xsl:value-of select="./property[@name='local_time_limit']"/> </td> </tr> </xsl:if> <tr> <th align="left">Number of Errors</th> <td>: <xsl:value-of select="./property[@name='err_count']"/> </td> </tr> </table> </td> </tr> </table> </xsl:template> <!-- +++++++++++++++ template for two executions in Compare ++++++++++++++ --> <xsl:template match="execution"> <!-- common execution information --> <table class="ex" cellspacing="0" border="0"> <tr><th align="left">Execution Name</th> <td>: <xsl:value-of select="./property[@name='name']"/></td></tr> <tr><th align="left">Execution Type</th> <td>: <xsl:value-of select="./property[@name='type']"/> <xsl:if test="(./property[@name='dblink'])"> REMOTE </xsl:if> </td> </tr> <xsl:if test="(./property[@name='dblink'])"> <tr><th align="left">Database Link</th> <td>: <xsl:value-of select="./property[@name='dblink']"/></td></tr> </xsl:if> <tr><th align="left">Scope</th> <td>: <xsl:value-of select="./property[@name='mode']"/></td></tr> <tr><th align="left">Status</th> <td>: <xsl:value-of select="./property[@name='status']"/></td></tr> <tr><th align="left">Started </th> <td>: <xsl:value-of select="./property[@name='start']"/></td></tr> <tr><th align="left">Last Updated </th> <td>: <xsl:value-of select="./property[@name='modif']"/></td></tr> <tr><th align="left">Global Time Limit</th> <td>: <xsl:value-of select="./property[@name='time_limit']"/></td></tr> <tr><th align="left">Per-SQL Time Limit</th> <td>: <xsl:value-of select="./property[@name='local_time_limit']"/></td> </tr> <xsl:if test="(./property[@name='err_count'])"> <tr><th align="left">Number of Errors</th> <td>: <xsl:value-of select="./property[@name='err_count']"/> </td> </tr> </xsl:if> </table> </xsl:template> <!-- ++++++++++++++++++++++++++ sts information ++++++++++++++++++++++++++ --> <xsl:template match="sqlset"> <table class="ex" cellspacing="0" border="0"> <tr><th align="left">SQL Tuning Set Name</th> <td>: <xsl:value-of select="property[@name='name']"/></td></tr> <tr><th align="left">SQL Tuning Set Owner</th> <td>: <xsl:value-of select="property[@name='owner']"/></td></tr> <tr><th align="left">Total SQL Statement Count</th> <td>: <xsl:value-of select="property[@name='sql_count']"/></td></tr> </table> </xsl:template> <!-- ++++++++++++++++++++++++ compare information ++++++++++++++++++++++++ --> <xsl:template match="compare"> <br/> <font class="s9"> <b> Analysis Information: </b> <hr size="1" width="650" align="left"/> </font> <table border="0"> <tr> <td> <font class="s8"><b>Before Change Execution:</b></font> <hr size="1" width="312" align="left"/> </td> <td> <font class="s8"><b>After Change Execution:</b></font> <hr size="1" width="315" align="left"/> </td> </tr> <tr valign="top"> <td><xsl:apply-templates select="execution[@label='before']"/></td> <td><xsl:apply-templates select="execution[@label='after']"/></td> </tr> <!-- workload information for compare sts --> <xsl:if test="$sts2sts"> <tr><td></td></tr> <tr> <td> <font class="s8"><b>Before Change Workload:</b></font> <hr size="1" width="312" align="left"/> </td> <td> <font class="s8"><b>After Change Workload:</b></font> <hr size="1" width="315" align="left"/> </td> </tr> <tr valign="top"> <td><xsl:apply-templates select="execution[@label='before']/sqlset"/></td> <td><xsl:apply-templates select="execution[@label='after']/sqlset"/></td> </tr> </xsl:if> <!-- empty rows --> <tr><td><hr size="1" width="312" align="left"/></td></tr> <tr> <td><font class="s8"> <b>Comparison Metric:</b> </font> <b><xsl:value-of select="metric"/></b> <hr size="1" width="125" align="left"/> </td> </tr> <tr> <td><font class="s8"> <b>Workload Impact Threshold:</b> </font> <b><xsl:value-of select="threshold[@type='sts']"/>%</b> <hr size="1" width="182" align="left"/> </td> </tr> <tr> <td><font class="s8"> <b>SQL Impact Threshold:</b> </font> <b><xsl:value-of select="threshold[@type='sql']"/>%</b> <hr size="1" width="148" align="left"/> </td> </tr> <tr/> <!-- empty row --> </table> </xsl:template> <!-- +++++++++++++++++++++++++ impact information ++++++++++++++++++++++++ --> <xsl:template match="impacts"> <font class="s9"><b>Projected Workload Change Impact:</b></font> <hr size="1" width="300" align="left"/> <table border="0"> <tr align="left"> <th >Overall Impact</th> <td>:</td> <!-- process the overall impact --> <xsl:choose> <xsl:when test="./impact[@type='overall'] > 0"> <td bgcolor="lime"> <b><xsl:value-of select="impact[@type='overall']"/>%</b> </td> </xsl:when> <xsl:when test="./impact[@type='overall'] < 0"> <td bgcolor="#ff6600"> <b><xsl:value-of select="impact[@type='overall']"/>%</b> </td> </xsl:when> <xsl:otherwise> <td> <b>0%</b> </td> </xsl:otherwise> </xsl:choose> </tr> <tr><th align="left">Improvement Impact</th> <td>:</td> <!-- process improvement impact --> <xsl:choose> <xsl:when test="./impact[@type='improve'] > 0"> <td bgcolor="lime"> <b><xsl:value-of select="impact[@type='improve']"/>%</b> </td> </xsl:when> <xsl:when test="./impact[@type='improve'] < 0"> <td bgcolor="#ff6600"> <b><xsl:value-of select="impact[@type='improve']"/>%</b> </td> </xsl:when> <xsl:otherwise> <td> <b>0%</b> </td> </xsl:otherwise> </xsl:choose> </tr> <tr><th align="left">Regression Impact</th> <td>:</td> <!-- process the regression impact --> <xsl:choose> <xsl:when test="./impact[@type='regress'] > 0"> <td bgcolor="lime"> <b><xsl:value-of select="impact[@type='regress']"/>%</b> </td> </xsl:when> <xsl:when test="./impact[@type='regress'] < 0"> <td bgcolor="#ff6600"> <b><xsl:value-of select="impact[@type='regress']"/>%</b> </td> </xsl:when> <xsl:otherwise> <td> <b>0%</b> </td> </xsl:otherwise> </xsl:choose> </tr> <xsl:if test="$sts2sts"> <!-- process the missing SQL impact --> <tr> <th align="left">Missing-SQL Impact</th> <td>:</td> <td> <xsl:choose> <xsl:when test="./impact[@type='missing']"> <b><xsl:value-of select="impact[@type='missing']"/>%</b> </xsl:when> <xsl:otherwise> <b>0%</b> </xsl:otherwise> </xsl:choose> </td> </tr> <!-- process the new SQL impact --> <tr> <th align="left">New-SQL Impact</th> <td>:</td> <td> <xsl:choose> <xsl:when test="./impact[@type='newsql']"> <b><xsl:value-of select="impact[@type='newsql']"/>%</b> </xsl:when> <xsl:otherwise> <b>0%</b> </xsl:otherwise> </xsl:choose> </td> </tr> </xsl:if> </table> <br/> <!-- statement count --> <font class="s9"><b>SQL Statement Count</b></font> <hr size="1" width="300" align="left"/> <table border="0" style="border-width:0px;" class="rs" bordercolor="#000000" cellspacing="1"> <tr> <th bgcolor="#CCCC99" class="s16">SQL Category</th> <th bgcolor="#CCCC99" class="s16">SQL Count</th> <th bgcolor="#CCCC99" class="s16">Plan Change Count</th> </tr> <!-- overall SQL count --> <tr> <td class="s17">Overall</td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='overall']/@sql_count"> <xsl:value-of select="impact[@type='overall']/@sql_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='overall']/@plan_change_count"> <xsl:value-of select="impact[@type='overall']/@plan_change_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> </tr> <xsl:choose> <xsl:when test="$sts2sts"> <tr> <td class="s17">Common</td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='common']/@sql_count"> <xsl:value-of select="impact[@type='common']/@sql_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='common']/@plan_change_count"> <xsl:value-of select="impact[@type='common']/@plan_change_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> </tr> <!-- improved sql count --> <xsl:if test="impact[@type='improve']"> <tr> <xsl:if test="(impact[@type='improve']/@sql_count > 0)"> <xsl:attribute name="bgcolor">lime</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>Improved</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='improve']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='improve']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- regressed sql count --> <xsl:if test="impact[@type='regress']"> <tr> <xsl:if test="(impact[@type='regress']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#ff6600</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>Regressed</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='regress']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='regress']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- unchanged sql count --> <xsl:if test="impact[@type='unchange']"> <tr> <td class="s17"> <table border="0"><tr><td>Unchanged</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unchange']/@sql_count"/></td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unchange']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- sql with timeout --> <xsl:if test="impact[@type='timeout']"> <tr> <xsl:if test="(impact[@type='timeout']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>with Timeout</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='timeout']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- sql with errors --> <xsl:if test="impact[@type='errors']"> <tr> <xsl:if test="(impact[@type='errors']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>with Errors</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='errors']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- unsupported SQL --> <xsl:if test="impact[@type='unsupport']"> <tr> <xsl:if test="(impact[@type='unsupport']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>Unsupported</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unsupport']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- unkonwn category because of null metric and impact --> <xsl:if test="impact[@type='unknown']"> <tr> <xsl:if test="(impact[@type='unknown']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>Unknown</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unknown']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unknown']/@plan_change_count"/> </td> </tr> </xsl:if> </xsl:when> <xsl:otherwise> <!-- improved sql count --> <xsl:if test="impact[@type='improve']"> <tr> <xsl:if test="(impact[@type='improve']/@sql_count > 0)"> <xsl:attribute name="bgcolor">lime</xsl:attribute> </xsl:if> <td class="s17">Improved</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='improve']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='improve']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- regressed sql count --> <xsl:if test="impact[@type='regress']"> <tr> <xsl:if test="(impact[@type='regress']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#ff6600</xsl:attribute> </xsl:if> <td class="s17">Regressed</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='regress']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='regress']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- unchanged sql count --> <xsl:if test="impact[@type='unchange']"> <tr> <td class="s17">Unchanged</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unchange']/@sql_count"/></td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unchange']/@plan_change_count"/> </td> </tr> </xsl:if> <!-- sql with timeout --> <xsl:if test="impact[@type='timeout']"> <tr> <xsl:if test="(impact[@type='timeout']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17">with Timeout</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='timeout']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- sql with errors --> <xsl:if test="impact[@type='errors']"> <tr> <xsl:if test="(impact[@type='errors']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17">with Errors</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='errors']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- unsupported SQL --> <xsl:if test="impact[@type='unsupport']"> <tr> <xsl:if test="(impact[@type='unsupport']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17">Unsupported</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unsupport']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- unkonwn category because of null metric and impact --> <xsl:if test="impact[@type='unknown']"> <tr> <xsl:if test="(impact[@type='unknown']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17">Unknown</td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unknown']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='unknown']/@plan_change_count"/> </td> </tr> </xsl:if> </xsl:otherwise> </xsl:choose> <xsl:if test="$sts2sts"> <tr> <td class="s17">Different</td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='different']/@sql_count"> <xsl:value-of select="impact[@type='different']/@sql_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> <td class="s17" align="right"> <xsl:choose> <xsl:when test="impact[@type='different']/@plan_change_count"> <xsl:value-of select="impact[@type='different']/@plan_change_count"/> </xsl:when> <xsl:otherwise>0</xsl:otherwise> </xsl:choose> </td> </tr> <!-- missing SQL --> <xsl:if test="impact[@type='missing']"> <tr> <xsl:if test="(impact[@type='missing']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>Missing SQL</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='missing']/@sql_count"/> </td> <td class="s17" align="right"> 0 </td> </tr> </xsl:if> <!-- new sql statements --> <xsl:if test="impact[@type='newsql']"> <tr> <xsl:if test="(impact[@type='newsql']/@sql_count > 0)"> <xsl:attribute name="bgcolor">#F7F777</xsl:attribute> </xsl:if> <td class="s17"> <table border="0"><tr><td>New SQL</td></tr></table> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='newsql']/@sql_count"/> </td> <td class="s17" align="right"> <xsl:value-of select="impact[@type='newsql']/@plan_change_count"/> </td> </tr> </xsl:if> </xsl:if> </table> <br/> </xsl:template> <!-- +++++++++++++++++++++++ histogram information +++++++++++++++++++++++ --> <xsl:template match="histograms"> <xsl:if test="histogram/bucket"> <font class="s9"> <b>Projected Workload Performance Distribution</b> <hr size="1" width="350" align="left"/> </font> <xsl:apply-templates select="histogram[@type='performance']"/> <br/> <font class="s9"> <b>Single SQL Statement Execution Count Distribution</b> <hr size="1" width="380" align="left"/> </font> <xsl:apply-templates select="histogram[@type='sql_count']"/> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++ performance distribution +++++++++++++++++++++ --> <xsl:template match="histogram[@type='performance']"> <table border="1" ora_borderstyle="headeronly"> <tr> <th bgcolor="#CCCC99" class="s16"><br/>Bucket</th> <th bgcolor="#CCCC99" class="s16">Cumulative Perf.<br/>Before Change</th> <th bgcolor="#CCCC99" class="s16"><br/>(%)</th> <th bgcolor="#CCCC99" class="s16">Cumulative Perf.<br/>After Change</th> <th bgcolor="#CCCC99" class="s16"><br/>(%)</th> </tr> <xsl:for-each select="bucket"> <tr> <td class="s17"> < = <xsl:value-of select="./@range"/></td> <td class="s17" align="right"><xsl:value-of select="b"/></td> <td class="s17" align="right"><xsl:value-of select="b/@wprc"/>%</td> <td class="s17" align="right"><xsl:value-of select="a"/></td> <td class="s17" align="right"><xsl:value-of select="a/@wprc"/>%</td> </tr> </xsl:for-each> </table> <br/> </xsl:template> <!-- ++++++++++++++++++++++++ sql count distribution ++++++++++++++++++++++ --> <xsl:template match="histogram[@type='sql_count']"> <table border="1" ora_borderstyle="headeronly"> <tr> <th bgcolor="#CCCC99" class="s16"><br/>Bucket</th> <th bgcolor="#CCCC99" class="s16">SQL Count <br/> Before Change</th> <th bgcolor="#CCCC99" class="s16"><br/>(%)</th> <th bgcolor="#CCCC99" class="s16">SQL Count <br/> After Change</th> <th bgcolor="#CCCC99" class="s16"><br/>(%)</th> </tr> <xsl:for-each select="bucket"> <tr> <td class="s17"> < = <xsl:value-of select="./@range"/></td> <td class="s17" align="right"><xsl:value-of select="b"/></td> <td class="s17" align="right"><xsl:value-of select="b/@wprc"/>%</td> <td class="s17" align="right"><xsl:value-of select="a"/></td> <td class="s17" align="right"><xsl:value-of select="a/@wprc"/>%</td> </tr> </xsl:for-each> </table> <br/> </xsl:template> <!-- ++++++++ SQL statements execution statistics for SQLPI compare ++++++ --> <xsl:template match="statements[@type='compare']"> <xsl:if test="object"> <font class="s9"> <b> Top <xsl:value-of select="./@sql_count"/> SQL <xsl:if test="./@category"> <xsl:value-of select="./@category"/> </xsl:if> Sorted by <xsl:value-of select="./@order_by"/> </b> <hr size="1" width="650" align="left"/> </font> <table border="1" ora_borderstyle="headeronly"> <tr> <th align="left" bgcolor="#CCCC99" class="s16"><br/>object_id</th> <th align="left" bgcolor="#CCCC99" class="s16"><br/>sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">Impact on<br/>Workload</th> <!-- only if not comparing sts 2 sts --> <xsl:if test="not ($sts2sts)"> <th align="left" bgcolor="#CCCC99" class="s16">Execution<br/>Frequency </th> </xsl:if> <xsl:choose> <xsl:when test="$sts2sts"> <th align="center" bgcolor="#CCCC99" class="s16">Total Metric<br/>Before </th> <th align="center" bgcolor="#CCCC99" class="s16">Total Metric<br/>After </th> </xsl:when> <xsl:otherwise> <th align="left" bgcolor="#CCCC99" class="s16">Metric<br/>Before</th> <th align="left" bgcolor="#CCCC99" class="s16">Metric<br/>After</th> </xsl:otherwise> </xsl:choose> <th align="left" bgcolor="#CCCC99" class="s16">Impact<br/>on SQL</th> <!-- only if all or advanced level --> <xsl:if test="$level='ADVANCED'"> <th align="left" bgcolor="#CCCC99" class="s16">% Workload<br/> Before</th> <th align="left" bgcolor="#CCCC99" class="s16">% Workload<br/> After</th> </xsl:if> <!-- only if not exadata simulation --> <xsl:if test="$noCellSim"> <th align="left" bgcolor="#CCCC99" class="s16">Plan<br/>Change</th> </xsl:if> </tr> <xsl:for-each select="object"> <tr> <xsl:choose> <xsl:when test="./@perf = '+'"> <!-- improvement --> <xsl:attribute name="bgcolor">lime</xsl:attribute> </xsl:when> <xsl:when test="./@perf = '-'"> <!-- regression --> <xsl:attribute name="bgcolor">#ff6600</xsl:attribute> </xsl:when> </xsl:choose> <td class="s17" align="right"> <xsl:value-of select="./@id"/> </td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17" align="right"> <xsl:if test="./stat/@wImp"> <xsl:value-of select="./stat/@wImp"/>% </xsl:if> </td> <xsl:if test="not ($sts2sts)"> <td class="s17" align="right"> <xsl:if test="./@frequency"> <xsl:value-of select="./@frequency"/> </xsl:if> </td> </xsl:if> <td class="s17" align="right"><xsl:value-of select="./stat/b"/></td> <td class="s17" align="right"><xsl:value-of select="./stat/a"/></td> <td class="s17" align="right"> <xsl:if test="./stat/@sImp"> <xsl:value-of select="./stat/@sImp"/>% </xsl:if> </td> <!-- only if all or advanced level --> <xsl:if test="$level='ADVANCED'"> <td class="s17" align="right"> <xsl:if test="./stat/b/@wprc"> <xsl:value-of select="./stat/b/@wprc"/>% </xsl:if> </td> <td class="s17" align="right"> <xsl:if test="./stat/a/@wprc"> <xsl:value-of select="./stat/a/@wprc"/>% </xsl:if> </td> </xsl:if> <!-- only if not exadata simulation --> <xsl:if test="$noCellSim"> <td class="s17"><xsl:value-of select="./@plan_change"/></td> </xsl:if> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL statements execution statistics for SQLPI test-execute +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --> <xsl:template match="statements[@type='execute']"> <xsl:if test="object"> <font class="s9"> <b> Top <xsl:value-of select="./@sql_count"/> SQL Sorted by <xsl:value-of select="./@order_by"/> </b> </font> <table border="1" ora_borderstyle="headeronly"> <tr> <th align="left" bgcolor="#CCCC99" class="s16"><br/>object_id</th> <th align="left" bgcolor="#CCCC99" class="s16"><br/>sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16"><br/>Executions</th> <th align="left" bgcolor="#CCCC99" class="s16">Parse<br/>time(s)</th> <th align="left" bgcolor="#CCCC99" class="s16">Elapsed<br/>time(s)</th> <th align="left" bgcolor="#CCCC99" class="s16">CPU<br/>time(s)</th> <th align="left" bgcolor="#CCCC99" class="s16">Buffer<br/>gets</th> </tr> <xsl:for-each select="object"> <tr> <td class="s17" align="right"><xsl:value-of select="./@id"/></td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17" align="right"><xsl:value-of select="./@frequency"/></td> <xsl:choose> <xsl:when test="$level = 'TEST'"> <td class="s17" align="right"> <xsl:if test="stat[@name='parse_time']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stat[@name='elapsed_time']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stat[@name='cpu_time']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stat[@name='buffer_gets']">xxx</xsl:if> </td> </xsl:when> <xsl:otherwise> <td class="s17" align="right"> <xsl:value-of select="stat[@name='parse_time']"/></td> <td class="s17" align="right"> <xsl:value-of select="stat[@name='elapsed_time']"/></td> <td class="s17" align="right"> <xsl:value-of select="stat[@name='cpu_time']"/></td> <td class="s17" align="right"> <xsl:value-of select="stat[@name='buffer_gets']"/></td> </xsl:otherwise> </xsl:choose> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL statements execution statistics for SQLPI explain plan +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --> <xsl:template match="statements[@type='explain plan']"> <xsl:if test="object"> <font class="s9"> <b> Top <xsl:value-of select="./@sql_count"/> SQL Sorted by <xsl:value-of select="./@order_by"/> </b> </font> <table border="1" ora_borderstyle="headeronly"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">object_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">Executions</th> <th align="left" bgcolor="#CCCC99" class="s16">Optimizer cost</th> <th align="left" bgcolor="#CCCC99" class="s16">Parse time(s)</th> </tr> <xsl:for-each select="object"> <tr> <td class="s17" align="right"><xsl:value-of select="./@id"/></td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17" align="right"><xsl:value-of select="./@frequency"/></td> <xsl:choose> <xsl:when test="$level = 'TEST'"> <td class="s17" align="right"> <xsl:if test="stat[@name='cost']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stat[@name='parse_time']">xxx</xsl:if> </td> </xsl:when> <xsl:otherwise> <td class="s17" align="right"> <xsl:value-of select="stat[@name='cost']"/></td> <td class="s17" align="right"> <xsl:value-of select="stat[@name='parse_time']"/></td> </xsl:otherwise> </xsl:choose> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ SQL statements execution statistics for SQLPA convert SQLSET +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --> <xsl:template match="statements[@type='sts2trial']"> <xsl:if test="object"> <font class="s9"> <b> Top <xsl:value-of select="./@sql_count"/> SQL Sorted by <xsl:value-of select="./@order_by"/> (Cumulative Statistics) </b> </font> <table border="1" ora_borderstyle="headeronly"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">object_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">Execs.</th> <th align="left" bgcolor="#CCCC99" class="s16">Elapsed Time(s)</th> <th align="left" bgcolor="#CCCC99" class="s16">CPU Time(s)</th> <th align="left" bgcolor="#CCCC99" class="s16">Buffer Gets</th> <th align="left" bgcolor="#CCCC99" class="s16">Plans</th> </tr> <xsl:for-each select="object"> <tr> <td class="s17" align="right"><xsl:value-of select="./@id"/></td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17" align="right"><xsl:value-of select="./@frequency"/></td> <xsl:choose> <xsl:when test="$level = 'TEST'"> <td class="s17" align="right"> <xsl:if test="stats/stat[@name='elapsed_time']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stats/stat[@name='cpu_time']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:if test="stats/stat[@name='buffer_gets']">xxx</xsl:if> </td> <td class="s17" align="right"> <xsl:value-of select="./@plan_count"/></td> </xsl:when> <xsl:otherwise> <td class="s17" align="right"> <xsl:value-of select="stats/stat[@name='elapsed_time']"/></td> <td class="s17" align="right"> <xsl:value-of select="stats/stat[@name='cpu_time']"/></td> <td class="s17" align="right"> <xsl:value-of select="stats/stat[@name='buffer_gets']"/></td> <td class="s17" align="right"> <xsl:value-of select="./@plan_count"/></td> </xsl:otherwise> </xsl:choose> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++++ SQL statements errors +++++++++++++++++++++ --> <!-- errors by error code --> <xsl:template match="error_groups"> <xsl:if test="./@count > 0"> <font class="s9"> <b> Top <xsl:value-of select="./@count"/> Errors (Grouped by Error Code) Sorted by SQL Count </b> <hr size="1" width="650" align="left"/> </font> <table border="1" ora_borderstyle="headeronly" ora_widthc="80"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">Error Code</th> <th align="left" bgcolor="#CCCC99" class="s16">SQL Count</th> <th align="left" bgcolor="#CCCC99" class="s16">Error Message (sample)</th> </tr> <xsl:for-each select="./error_group"> <tr> <td class="s17"><xsl:value-of select="./@code"/></td> <td class="s17" align="right"> <xsl:value-of select="./@sql_count"/> </td> <td class="s17"><xsl:value-of select="."/></td> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- error by sql_id/objet_id--> <xsl:template match="errors"> <xsl:if test="./@count > 0"> <font class="s9"> <b> Top <xsl:value-of select="./@count"/> SQL with Error <xsl:value-of select="./@code"/> Sorted by object_id </b> <hr size="1" width="650" align="left"/> </font> <table border="1" ora_borderstyle="headeronly" ora_widthc="80"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">object_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">Error Message</th> </tr> <xsl:for-each select="error"> <tr> <td class="s17" align="right"> <xsl:value-of select="./@object_id"/> </td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@object_id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17"><xsl:value-of select="."/></td> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++ Unsupported SQL statements ++++++++++++++++++ --> <xsl:template match="unsupported"> <xsl:if test="./@count > 0"> <font class="s9"> <b> Top <xsl:value-of select="./@count"/> Unsupported SQL Statements Sorted by object_id </b> <hr size="1" width="650" align="left"/> </font> <table border="1" ora_borderstyle="headeronly" ora_widthc="80"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">object_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_text</th> </tr> <xsl:for-each select="sql_text"> <tr> <td class="s17" align="right"> <xsl:value-of select="./@object_id"/> </td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@object_id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17"><xsl:value-of select="."/></td> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++ SQL statements with timeout ++++++++++++++++++ --> <xsl:template match="timeouts"> <xsl:if test="./@count > 0"> <font class="s9"> <b> Top <xsl:value-of select="./@count"/> SQL Statements with Timeout Sorted by object_id </b> <hr size="1" width="650" align="left"/> </font> <table border="1" ora_borderstyle="headeronly" ora_widthc="80"> <tr> <th align="left" bgcolor="#CCCC99" class="s16">object_id</th> <th align="left" bgcolor="#CCCC99" class="s16">sql_id</th> <th align="left" bgcolor="#CCCC99" class="s16">Message</th> </tr> <xsl:for-each select="timeout"> <tr> <td class="s17" align="right"> <xsl:value-of select="./@object_id"/> </td> <td class="s17"> <a> <xsl:attribute name="href"> #<xsl:value-of select="./@object_id"/> </xsl:attribute> <xsl:value-of select="./@sql_id"/> </a> </td> <td class="s17"><xsl:value-of select="."/></td> </tr> </xsl:for-each> </table> <br/> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ templates for body of the report ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ --> <!-- ++++++++++++++++++++++++++++ SQL information ++++++++++++++++++++++++ --> <xsl:template match="sql"> <font class="s8"> <a> <xsl:attribute name="name"> <xsl:value-of select="../@id"/> </xsl:attribute> </a> <br/> <b> SQL Details: </b> </font> <hr size="1" width="200" align="left"/> <table border="0" ora_widthc="80"> <tr> <th align="left">Object ID</th> <td>: <xsl:value-of select="../@id"/></td> </tr> <xsl:if test="not ($sts2sts)"> <tr> <th align="left">Schema Name</th> <td>: <xsl:value-of select="property[@name='schema']"/></td> </tr> </xsl:if> <tr> <th align="left">SQL ID</th> <td>: <xsl:value-of select="./@id"/></td> </tr> <xsl:if test="not ($sts2sts)"> <tr> <th align="left">Execution Frequency</th> <td>: <xsl:value-of select="property[@name='frequency']"/></td> </tr> </xsl:if> <tr> <th align="left" valign="top">SQL Text</th> <td>: <xsl:value-of select="property[@name='text']"/></td> </tr> </table> <xsl:if test="binds/bind"> <font class="s7"> <b> SQL Text binds: </b> </font> <hr size="1" width="200" align="left"/> <table border="0" ora_widthc="80"> <xsl:for-each select="binds/bind"> <xsl:if test="./@pos and ./@type and current()"> <tr> <td><xsl:value-of select="./@pos"/></td><td>-</td> <td>(<xsl:value-of select="./@type"/>):</td> <td><xsl:value-of select="current()"/></td> </tr> </xsl:if> </xsl:for-each> </table> </xsl:if> </xsl:template> <!-- ++++++++++++++++++++++++++++ SQL errors +++++++++++++++++++++++++++++ --> <xsl:template match="error"> <br/> <font class="s8"><b> Errors: </b></font> <hr size="1" width="100" align="left"/> <table border="0" ora_widthc="80"> <tr> <td> <ol> <li><xsl:value-of select="."/></li> </ol> </td> </tr> </table> </xsl:template> <!-- ++++++++++++++++ SQL statistics for Compare Performance +++++++++++++ --> <xsl:template match="stats[@type='compare']"> <br/> <font class="s8"> <b> <xsl:choose> <xsl:when test="$sts2sts"> SQL Execution Statistics (average): <hr size="1" width="270" align="left"/> </xsl:when> <xsl:otherwise> Execution Statistics: <hr size="1" width="200" align="left"/> </xsl:otherwise> </xsl:choose> </b> </font> <table border="1" ora_borderstyle="headeronly"> <tr> <th align="left" bgcolor="#CCCC99" class="s16"><br/>Stat Name</th> <th align="left" bgcolor="#CCCC99" class="s16">Impact on<br/>Workload</th> <th align="left" bgcolor="#CCCC99" class="s16">Value<br/>Before</th> <th align="left" bgcolor="#CCCC99" class="s16">Value<br/>After</th> <th align="left" bgcolor="#CCCC99" class="s16">Impact<br/>on SQL</th> <!-- only if all or advanced level --> <xsl:if test="$level='ADVANCED'"> <th align="left" bgcolor="#CCCC99" class="s16">% Workload<br/>Before</th> <th align="left" bgcolor="#CCCC99" class="s16">% Workload<br/>After</th> </xsl:if> </tr> <xsl:for-each select="stat"> <tr> <td class="s17"><xsl:value-of select="./@name"/></td> <td class="s17" align="right"> <xsl:if test="./@wImp"> <xsl:value-of select="./@wImp"/>% </xsl:if> </td> <td class="s17" align="right"><xsl:value-of select="b"/></td> <td class="s17" align="right"><xsl:value-of select="a"/></td> <td class="s17" align="right"> <xsl:if test="./@sImp"> <xsl:value-of select="./@sImp"/>% </xsl:if> </td> <!-- only if all or advanced level --> <xsl:if test="$level='ADVANCED'"> <td class="s17" align="right"> <xsl:if test="b/@wprc"> <xsl:value-of select="b/@wprc"/>% </xsl:if> </td> <td class="s17" align="right"> <xsl:if test="a/@wprc"> <xsl:value-of select="a/@wprc"/>% </xsl:if> </td> </xsl:if> </tr> </xsl:for-each> </table> <!-- any notes on execution stats --> <xsl:apply-templates select="notes[@type='compare']"/> </xsl:template> <!-- ++++++++++ SQL statistics for Test-execute and Explain Plan ++++++++++ --> <xsl:template match="stats[@type='execute']"> <br/> <font class="s8"><b>Execution Statistics: </b></font> <hr size="1" width="200" align="left"/> <table border="0"> <xsl:choose> <xsl:when test="$level = 'TEST'"> <xsl:for-each select="stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> <td>: <xsl:if test=".">xxx</xsl:if> </td> </tr> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:for-each select="stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> <td>: <xsl:value-of select="."/></td> </tr> </xsl:for-each> </xsl:otherwise> </xsl:choose> <tr> </tr> </table> <!-- add not section if any --> <xsl:apply-templates select="notes[@type='execute']"/> </xsl:template> <!-- +++++++++++++++++ SQL statistics for Convert SQLSET +++++++++++++++++ --> <xsl:template match="stats[@type='sts2trial']"> <br/> <font class="s8"><b>SQL Execution Statistics (average): </b></font> <hr size="1" width="270" align="left"/> <table border="0"> <xsl:choose> <xsl:when test="$level = 'TEST'"> <xsl:for-each select="stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> <td>: <xsl:if test=".">xxx</xsl:if> </td> </tr> </xsl:for-each> </xsl:when> <xsl:otherwise> <xsl:for-each select="stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> <td>: <xsl:value-of select="."/></td> </tr> </xsl:for-each> </xsl:otherwise> </xsl:choose> <tr> </tr> </table> </xsl:template> <!-- ++++++++++++++++++++++++++++ SQL findings +++++++++++++++++++++++++++ --> <xsl:template match="findings"> <xsl:if test="./@count > 0"> <br/> <font class="s8"> <b> Findings (<xsl:value-of select="./@count"/>): </b> </font> <hr size="1" width="200" align="left"/> <table border="0" ora_widthc="80"> <tr> <td> <ol> <xsl:for-each select="fnd"> <li><xsl:value-of select="."/></li> </xsl:for-each> </ol> </td> </tr> </table> <br/> </xsl:if> </xsl:template> <!-- +++++++++++++++++++ SQL Plans for Compare Performance +++++++++++++++ --> <xsl:template match="plans[@type='compare']"> <xsl:choose> <xsl:when test="$sts2sts"> <!-- display plan statistics --> <br/> <font class="s8"><b> Plan Execution Statistics (average): </b></font> <hr size="1" width="270" align="left"/> <table ora_borderstyle="headeronly"> <tr> <td> Statistic Name </td> <xsl:if test="plan[@type='before']"> <td> Plans Before Change </td> </xsl:if> <xsl:if test="plan[@type='after']"> <td> Plans After Change </td> </xsl:if> </tr> <tr> <td> <table border="0"> <!-- a nested table/column for every plan --> <tr> <td> <table border="0"> <tr> <th align="left"> plan hash value <hr size="1" width="100" align="right"/> </th> </tr> <tr> <th align="left">schema name </th> </tr> <xsl:for-each select="plan[1]/stats[1]/stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> </tr> </xsl:for-each> </table> </td> </tr> </table> </td> <xsl:if test="plan[@type='before']"> <td> <table border="0"> <!-- a nested table/column for every plan --> <tr> <xsl:for-each select="plan[@type='before']"> <td> <table border="0"> <tr> <td align="left"> <xsl:value-of select="./@hash"/> <hr size="1" width="80" align="right"/> </td> </tr> <tr> <td align="left"> <xsl:value-of select="./@schema"/> </td> </tr> <xsl:for-each select="stats/stat"> <tr> <td align="left"> <xsl:value-of select="."/> </td> </tr> </xsl:for-each> </table> </td> </xsl:for-each> </tr> </table> </td> </xsl:if> <xsl:if test="plan[@type='after']"> <td> <table border="0"> <!-- a nested table/column for every plan --> <tr> <xsl:for-each select="plan[@type='after']"> <td> <table border="0"> <tr> <td align="left"> <xsl:value-of select="./@hash"/> <hr size="1" width="80" align="right"/> </td> </tr> <tr> <td align="left"> <xsl:value-of select="./@schema"/> </td> </tr> <xsl:for-each select="stats/stat"> <tr> <td align="left"> <xsl:value-of select="."/> </td> </tr> </xsl:for-each> </table> </td> </xsl:for-each> </tr> </table> </td> </xsl:if> </tr> </table> <!-- plans before the change --> <xsl:if test="plan/@type = 'before'"> <br/> <font class="s8"><b> Execution Plans Before Change: </b></font> <hr size="1" width="200" align="left"/> <xsl:for-each select="plan[@type='before']"> <xsl:apply-templates select="."> <xsl:with-param name="format" select="$plan_format" /> </xsl:apply-templates> </xsl:for-each> </xsl:if> <!-- plans after the change --> <xsl:if test="plan/@type = 'after'"> <br/> <font class="s8"><b> Executions Plan After Change: </b></font> <hr size="1" width="200" align="left"/> <xsl:for-each select="plan[@type='after']"> <xsl:apply-templates select="."> <xsl:with-param name="format" select="$plan_format" /> </xsl:apply-templates> </xsl:for-each> </xsl:if> </xsl:when> <xsl:otherwise> <xsl:for-each select="plan"> <xsl:if test="./@type = 'before'"> <br/> <font class="s8"><b> Execution Plan Before Change: </b></font> <hr size="1" width="200" align="left"/> </xsl:if> <xsl:if test="./@type = 'after'"> <br/> <font class="s8"><b> Execution Plan After Change: </b></font> <hr size="1" width="200" align="left"/> </xsl:if> <xsl:apply-templates select="."> <xsl:with-param name="format" select="$plan_format" /> </xsl:apply-templates> </xsl:for-each> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- +++++++++++++ SQL Plans for Test execute and Explain Plan +++++++++++ --> <xsl:template match="plans[@type='execute']"> <br/> <font class="s8"><b> Execution Plan: </b></font> <hr size="1" width="200" align="left"/> <xsl:apply-templates select="plan"> <xsl:with-param name="format" select="$plan_format" /> </xsl:apply-templates> </xsl:template> <!-- +++++++++++++++++++++ SQL Plans for convert slqset +++++++++++++++++ --> <xsl:template match="plans[@type='sts2trial']"> <xsl:if test="./@plan_count > 1"> <!-- display plan statistics --> <br/> <font class="s8"><b> Plan Execution Statistics (average): </b></font> <hr size="1" width="270" align="left"/> <table ora_borderstyle="headeronly"> <tr> <td> Name </td> <td> Value </td> </tr> <tr> <td> <table border="0"> <!-- a nested table/column for every plan --> <tr> <td> <table border="0"> <tr> <th align="left"> plan hash value <hr size="1" width="100" align="right"/> </th> </tr> <tr> <th align="left">schema name </th> </tr> <xsl:for-each select="plan[1]/stats[1]/stat"> <tr> <th align="left"><xsl:value-of select="./@name"/></th> </tr> </xsl:for-each> </table> </td> </tr> </table> </td> <td> <table border="0"> <!-- a nested table/column for every plan --> <tr> <xsl:for-each select="plan"> <td> <table border="0"> <tr> <td align="left"> <xsl:value-of select="./@hash"/> <hr size="1" width="80" align="right"/> </td> </tr> <tr> <td align="left"> <xsl:value-of select="./@schema"/> </td> </tr> <xsl:for-each select="stats/stat"> <tr> <td align="left"> <xsl:value-of select="."/> </td> </tr> </xsl:for-each> </table> </td> </xsl:for-each> </tr> </table> </td> </tr> </table> </xsl:if> <!-- display plans --> <br/> <font class="s8"> <b> Execution Plans (<xsl:value-of select="./@plan_count"/>): </b></font> <hr size="1" width="270" align="left"/> <xsl:for-each select="plan"> <xsl:apply-templates select="."> <xsl:with-param name="format" select="$plan_format" /> </xsl:apply-templates> </xsl:for-each> </xsl:template> <!-- +++++++++++++ SQL multiple execution for Test-execute +++++++++++++++ --> <xsl:template match="notes[@type='execute']"> <br/> <font class="s8"><b> Notes: </b></font> <hr size="1" width="200" align="left"/> <table border="0" ora_widthc="80"> <tr> <td align="left"> <ol> <xsl:for-each select="note"> <li><xsl:value-of select="."/></li> </xsl:for-each> </ol> </td> </tr> </table> <br/> </xsl:template> <!-- +++++++ SQL multiple execution for comparison ++++++++++++++++++++++ --> <xsl:template match="notes[@type='compare']"> <br/> <font class="s8"><b> Notes: </b></font> <hr size="1" width="200" align="left"/> <xsl:for-each select="b"> <font class="s8"> <b> Before Change: </b> </font> <table border="0" ora_widthc="80"> <tr> <td align="left"> <ol> <xsl:for-each select="note"> <li><xsl:value-of select="."/></li> </xsl:for-each> </ol> </td> </tr> </table> </xsl:for-each> <br/> <xsl:for-each select="a"> <font class="s8"> <b> After Change: </b> </font> <table border="0" ora_widthc="80"> <tr> <td align="left"> <ol> <xsl:for-each select="note"> <li><xsl:value-of select="."/></li> </xsl:for-each> </ol> </td> </tr> </table> </xsl:for-each> <br/> </xsl:template> </xsl:stylesheet>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de