Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpdw\tdpdw_perform.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>Eliminating Performance Bottlenecks</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1" /> <meta name="date" content="2009-04-27T11:26:23Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content=" Eliminating Performance Bottlenecks" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10578-01" /> <meta name="topic-id" content="TDPDW008" /> <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="tdpdw_optimize.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpdw_bandr.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="tdpdw_optimize.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpdw_bandr.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CACJFFGI" name="CACJFFGI"></a><a id="TDPDW008" name="TDPDW008"></a></p> <h1><a name="TDPDW008|Eliminating Performance Bottlenecks"></a>Eliminating Performance Bottlenecks</h1> <p><a name="BEGIN" id="BEGIN"></a></p> <p>This section discusses how to identify and reduce performance issues, and includes the following topics:</p> <ul> <li> <p><a href="#CACCIICD">Verifying That SQL Runs Efficiently</a></p> </li> <li> <p><a href="#CIHBEBGD">Improving Performance by Minimizing Resource Consumption</a></p> </li> <li> <p><a href="#CIHDIIIA">Using Resources Optimally</a></p> </li> </ul> <p><a id="CACCIICD" name="CACCIICD"></a><a id="TDPDW0081" name="TDPDW0081"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW0081|Verifying that SQL Runs Efficiently"></a>Verifying That SQL Runs Efficiently</h2> <p>An important aspect of ensuring that your system performs well is to eliminate performance problems. This section describes some methods of finding and eliminating these bottlenecks, and contains the following topics:</p> <ul> <li> <p><a href="#CACBGEEH">Analyzing Optimizer Statistics</a></p> </li> <li> <p><a href="#CACCCEFF">Analyzing an Execution Plan</a></p> </li> <li> <p><a href="#CACCGFJE">Using Hints to Improve Data Warehouse Performance</a></p> </li> <li> <p><a href="#CACFGIHA">Using Advisors to Verify SQL Performance</a></p> </li> </ul> <a id="CACBGEEH" name="CACBGEEH"></a><a id="TDPDW00811" name="TDPDW00811"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00811|Analyzing Optimizer Statistics"></a>Analyzing Optimizer Statistics</h3> <p><span class="bold">Optimizer</span> <a id="sthref212" name="sthref212"></a><a id="sthref213" name="sthref213"></a><span class="bold">statistics</span> are a collection of data that describes more details about the database and the objects in the database. These statistics are stored in the data dictionary, and are used by the query optimizer to choose the best execution plan for each SQL statement. Optimizer statistics include the following:</p> <ul> <li> <p>Table statistics (number of rows, blocks, and the average row length)</p> </li> <li> <p>Column statistics (number of distinct values in a column, number of null values in a column, and data distribution)</p> </li> <li> <p>Index statistics (number of leaf blocks, levels, and clustering factor)</p> </li> <li> <p>System statistics (CPU and I/O performance and utilization)</p> </li> </ul> <p>The optimizer statistics are stored in the data dictionary. They can be viewed using data dictionary views similar to the following:</p> <pre xml:space="preserve" class="oac_no_warn"> SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB'; </pre> <p>Because the objects in a database can constantly change, statistics must be regularly updated so that they accurately describe these database objects. Statistics are maintained automatically by Oracle Database or you can maintain the optimizer statistics manually using the <code>DBMS_STATS</code> package.</p> </div> <!-- class="sect2" --> <a id="CACCCEFF" name="CACCCEFF"></a><a id="TDPDW00812" name="TDPDW00812"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00812|Analyzing Explain Plan"></a>Analyzing an Execution Plan</h3> <p>To execute <a id="sthref214" name="sthref214"></a><a id="sthref215" name="sthref215"></a>a SQL statement, Oracle Database might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle Database uses to execute a statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method.</p> <p>You can examine the execution plan chosen by the optimizer for a SQL statement by using the <code>EXPLAIN</code> <code>PLAN</code> statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the <code>EXPLAIN</code> <code>PLAN</code> statement and then query the output table.</p> <p>General guidelines for using the <code>EXPLAIN</code> <code>PLAN</code> statement are:</p> <ul> <li> <p>To use the SQL script <code>UTLXPLAN.SQL</code> to create a sample output table called <code>PLAN_TABLE</code> in your schema.</p> </li> <li> <p>To include the <code>EXPLAIN</code> <code>PLAN</code> <code>FOR</code> clause prior to the SQL statement.</p> </li> <li> <p>After issuing the <code>EXPLAIN</code> <code>PLAN</code> statement, to use one of the scripts or packages provided by Oracle Database to display the most recent plan table output.</p> </li> <li> <p>The execution order in <code>EXPLAIN</code> <code>PLAN</code> output begins with the line that is indented farthest to the right. If two lines are indented equally, then the top line is normally executed first.</p> </li> </ul> <a id="TDPDW00833" name="TDPDW00833"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref216" name="sthref216"></a> <h4><a name="TDPDW00833|Example: Analyzing Explain Plan"></a>Example: Analyzing Explain Plan Output</h4> <p>The following statement illustrates the output of two <code>EXPLAIN</code> <code>PLAN</code> statements, one with dynamic pruning and one with static pruning.</p> <a id="sthref217" name="sthref217"></a> <p class="subhead2">To analyze EXPLAIN PLAN output:</p> <pre xml:space="preserve" class="oac_no_warn"> EXPLAIN PLAN FOR SELECT p.prod_name , c.channel_desc , SUM(s.amount_sold) revenue FROM products p , channels c , sales s WHERE s.prod_id = p.prod_id AND s.channel_id = c.channel_id AND s.time_id BETWEEN '01-12-2001' AND '31-12-2001' GROUP BY p.prod_name , c.channel_desc; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); </pre> <pre xml:space="preserve" class="oac_no_warn"> WITHOUT TO_DATE --------------------------------------------------------------------------------------------------- | Id| Operation | Name |Rows|Bytes|Cost | Time |Pstart|Pstop| (%CPU) --------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 252|15876|305(1)|00:00:06| | | | 1| HASH GROUP BY | | 252|15876|305(1)|00:00:06| | | | *2| FILTER | | | | | | | | | *3| HASH JOIN | |2255| 138K|304(1)|00:00:06| | | | 4| TABLE ACCESS FULL | PRODUCTS | 72| 2160| 2(0)|00:00:01| | | | 5| MERGE JOIN | |2286|75438|302(1)|00:00:06| | | | 6| TABLE ACCESS BY INDEX ROWID | CHANNELS | 5| 65| 2(0)|00:00:01| | | | 7| INDEX FULL SCAN | CHANNELS_PK | 5| | 1(0)|00:00:01| | | | *8| SORT JOIN | |2286|45720|299(1)|00:00:06| | | | 9| PARTITION RANGE ITERATOR | |2286|45720|298(0)|00:00:06| KEY| KEY| | 10| TABLE ACCESS BY LOCAL INDEX ROWID| SALES |2286|45720|298(0)|00:00:06| KEY| KEY| | 11| BITMAP CONVERSION TO ROWIDS | | | | | | | | |*12| BITMAP INDEX RANGE SCAN |SALES_TIME_BIX| | | | | KEY| KEY| --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_DATE('01-12-2001')<=TO_DATE('31-12-2001')) 3 - access("S"."PROD_ID"="P"."PROD_ID") 8 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID") filter("S"."CHANNEL_ID"="C"."CHANNEL_ID") 12 - access("S"."TIME_ID">='01-12-2001' AND "S"."TIME_ID"<='31-12-2001') Note the values of KEY KEY for Pstart and Pstop. WITH TO_DATE -------------------------------------------------------------------------------------------------- |Id| Operation | Name | Rows | Bytes |Cost(%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 252 | 15876 | 31 (20)| 00:00:01 | | | | 1| HASH GROUP BY | | 252 | 15876 | 31 (20)| 00:00:01 | | | |*2| HASH JOIN | | 21717 | 1336K| 28 (11)| 00:00:01 | | | | 3| TABLE ACCESS FULL |PRODUCTS| 72 | 2160 | 2 (0)| 00:00:01 | | | |*4| HASH JOIN | | 21717 | 699K| 26 (12)| 00:00:01 | | | | 5| TABLE ACCESS FULL |CHANNELS| 5 | 65 | 3 (0)| 00:00:01 | | | | 6| PARTITION RANGE SINGLE| | 21717 | 424K| 22 (10)| 00:00:01 | 20 | 20 | |*7| TABLE ACCESS FULL |SALES | 21717 | 424K| 22 (10)| 00:00:01 | 20 | 20 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."PROD_ID"="P"."PROD_ID") 4 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID") 7 - filter("S"."TIME_ID">=TO_DATE('2001-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID"<=TO_DATE('2001-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) Note the values of 20 20 for Pstart and Pstop. </pre> <p>The first execution plan shows dynamic pruning, using the <code>KEY</code> values for <code>Pstart</code> and <code>Pstop</code> respectively. Dynamic pruning means that the database will have to figure out at execution time which partition or partitions to access. In the case of static pruning, the database knows at parse time which partition or partitions to access, which leads to more efficient execution.</p> <p>You can frequently improve the execution plan by using explicit date conversions. Using explicit date conversions is a <span class="italic">best</span> <span class="italic">practice</span> for optimal partition pruning and index usage.</p> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="CACCGFJE" name="CACCGFJE"></a><a id="TDPDW00813" name="TDPDW00813"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00813|Using Hints"></a>Using Hints to Improve Data Warehouse Performance</h3> <p>Hints enable you to ma<a id="sthref218" name="sthref218"></a><a id="sthref219" name="sthref219"></a>ke decisions usually made by the optimizer. As an application developer, you might know information about your data that the optimizer does not know. Hints provide a mechanism to instruct the optimizer to choose a certain query execution plan based on the specific criteria.</p> <p>For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to instruct the optimizer to use the optimal execution plan.</p> <p>By default, Oracle Warehouse Builder includes hints to optimize a typical data load. See <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=WBDOD','newWindow').focus()"><span class="italic">Oracle Warehouse Builder Sources and Targets Guide</span></a> for more information.</p> <a id="TDPDW00835" name="TDPDW00835"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref220" name="sthref220"></a> <h4><a name="TDPDW00835|Example: Using Hints to Improve DW Performance"></a>Example: Using Hints to Improve Data Warehouse Performance</h4> <p>Suppose you want to very quickly run a summary across the sales table for last year while the system is otherwise idle. In this case, you could issue the following statement.</p> <a id="sthref221" name="sthref221"></a> <p class="subhead2">To use a hint to improve data warehouse performance:</p> <pre xml:space="preserve" class="oac_no_warn"> SELECT /*+ PARALLEL(s,16) */ SUM(amount_sold) FROM sales s WHERE s.time_id BETWEEN TO_DATE('01-JAN-2005','DD-MON-YYYY') AND TO_DATE('31-DEC-2005','DD-MON-YYYY'); </pre> <p>Another common use for hints in data warehouses is to ensure that records are efficiently loaded using compression. For this, you use the <code>APPEND</code> hint, as shown in the following SQL:</p> <pre xml:space="preserve" class="oac_no_warn"> ... INSERT /* +APPEND */ INTO my_materialized_view ... </pre></div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="CACFGIHA" name="CACFGIHA"></a><a id="TDPDW00814" name="TDPDW00814"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00814|Using Advisors to Verify SQL Performance"></a>Using Advisors to Verify SQL Performance</h3> <p>Using the<a id="sthref222" name="sthref222"></a><a id="sthref223" name="sthref223"></a><a id="sthref224" name="sthref224"></a><a id="sthref225" name="sthref225"></a> SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a given SQL statement, or set of SQL statements, and provide recommendations to improve their efficiency. The SQL Tuning Advisor and SQL Access Advisor can make various types of recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Oracle Enterprise Manager enables you to accept and implement many of these recommendations in very few steps.</p> <p>The SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. It also recommends a partitioning strategy. The SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. In some cases where significant performance improvements can be gained by creating a new index, the SQL Tuning Advisor may recommend doing so. However, such recommendations should be verified by running the SQL Access Advisor with a SQL workload that contains a set of representative SQL statements.</p> <a id="sthref226" name="sthref226"></a> <p class="subhead2"><a name="TDPDW00836|Example: Using the SQL Tuning Advisor to Verify SQL Performance"></a>Example: Using the SQL Tuning Advisor to Verify SQL Performance</p> <p>You can use the SQL Tuning Advisor to tune a single or multiple SQL statements. When tuning multiple SQL statements, keep in the mind that the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, it is just meant to be a convenient way for you to run the SQL Tuning Advisor for a large number of SQL statements.</p> <p><span class="bold">To run the SQL Tuning Advisor to verify SQL performance:</span></p> <ol> <li> <p>Go to the Advisor Central page, then click <span class="bold">SQL</span> <span class="bold">Advisors</span>.</p> <p>The SQL Advisors page is displayed.</p> </li> <li> <p>Click <span class="bold">Schedule</span> <span class="bold">SQL</span> <span class="bold">Tuning</span> <span class="bold">Advisor</span>.</p> <p>The Schedule SQL Tuning Advisor page is displayed. A suggested name will be in the <span class="bold">Name</span> field, which you can modify. Then select <span class="bold">Comprehensive</span> to have a comprehensive analysis performed. Select <span class="bold">Immediately</span> for the Schedule. Choose a proper SQL Tuning Set, and then click <span class="bold">OK</span>.</p> </li> <li> <p>The Processing page is displayed. Then the Recommendations page shows the recommendations for improving performance. Click <span class="bold">View</span> <span class="bold">Recommendations</span>.</p> <p>The Recommendations page is displayed.</p> </li> <li> <p>The recommendation is to create an index, which you can implement by clicking <span class="bold">Implement</span>. Alternatively, you may want to run the SQL Access Advisor as well.</p> </li> </ol> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <p><a id="CIHBEBGD" name="CIHBEBGD"></a><a id="TDPDW0082" name="TDPDW0082"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW0082|Minimizing the Resources Needed"></a>Improving Performance by Minimizing Resource Consumption</h2> <p>You can minimize re<a id="sthref227" name="sthref227"></a><a id="sthref228" name="sthref228"></a>source consumption, and thus improve your data warehouse's performance through the use of the following capabilities:</p> <ul> <li> <p><a href="#CIHJDAHD">Improving Performance: Partitioning</a></p> </li> <li> <p><a href="#CIHBDGEF">Improving Performance: Query Rewrite and Materialized Views</a></p> </li> <li> <p><a href="#CIHBGHHG">Improving Performance: Indexes</a></p> </li> <li> <p><a href="#CACIGHHJ">Improving Performance: Columnar Compression</a></p> </li> </ul> <a id="CIHJDAHD" name="CIHJDAHD"></a><a id="TDPDW00821" name="TDPDW00821"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00821|Minimizing Resources: Partitioning"></a>Improving Performance: Partitioning</h3> <p>Data warehous<a id="sthref229" name="sthref229"></a>es often contain large tables and require techniques both for managing these large tables and for providing good query performance across these large tables. This section discusses partitioning, a key method for addressing these requirements. Two capabilities relevant for query performance in a data warehouse are partition pruning and partitionwise joins.</p> <a id="TDPDW00838" name="TDPDW00838"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref230" name="sthref230"></a> <h4><a name="TDPDW00838|Minimizing Resource Consumption: Partition Pruning"></a>Improving Performance: Partition Pruning</h4> <p>Partition <a id="sthref231" name="sthref231"></a>pruning is an essential performance feature for data warehouses. In partition pruning, the optimizer analyzes <code>FROM</code> and <code>WHERE</code> clauses in SQL statements to eliminate unneeded partitions when building the partition access list. This enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement. Oracle Database prunes partitions when you use range, <code>LIKE</code>, equality, and <code>IN</code>-list predicates on the range or list partitioning columns, and when you use equality and <code>IN</code>-list predicates on the hash partitioning columns.</p> <p>Partition pruning dramatically reduces the amount of data retrieved from disk and shortens the use of processing time, thus improving query performance and resource utilization. If you partition the index and table on different columns (with a global partitioned index), partition pruning also eliminates index partitions even when the partitions of the underlying table cannot be eliminated.</p> <p>Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile time, with the information about the partitions accessed beforehand while dynamic pruning occurs at run time, meaning that the exact partitions to be accessed by a statement are not known beforehand. A sample scenario for static pruning would be a SQL statement containing a <code>WHERE</code> condition with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the <code>WHERE</code> condition.</p> <p>Partition pruning affects the statistics of the objects where pruning will occur and will therefore also affect the execution plan of a statement.</p> </div> <!-- class="sect3" --> <a id="TDPDW00851" name="TDPDW00851"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref232" name="sthref232"></a> <h4><a name="TDPDW00851|Minimizing Resource Consumption: Partitionwise Joins"></a>Improving Performance: Partitionwise Joins</h4> <p>Partitionwi<a id="sthref233" name="sthref233"></a>se joins reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. This significantly reduces response time and improves the use of both CPU and memory resources. In Oracle Real Application Clusters environments, partitionwise joins also avoid or at least limit the data traffic over the interconnection, which is the key to achieving good scalability for massive join operations.</p> <p>Partitionwise joins can be full or partial. Oracle Database decides which type of join to use.</p> </div> <!-- class="sect3" --> <a id="TDPDW00839" name="TDPDW00839"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref234" name="sthref234"></a> <h4><a name="TDPDW00850|EM Example: Evaluating Partitioning with SQL Access Advisor"></a>Example: Evaluating Partitioning with the SQL Access Advisor</h4> <p>You should always consider partitioning in data warehousing environments.</p> <p><span class="bold">To evaluat<a id="sthref235" name="sthref235"></a>e partitioning:</span></p> <ol> <li> <p>In the Advisor Central page, click <span class="bold">SQL</span> <span class="bold">Advisors</span>.</p> <p>The SQL Advisors page is displayed.</p> </li> <li> <p>Click <span class="bold">SQL</span> <span class="bold">Access</span> <span class="bold">Advisor</span>.</p> <p>The SQL Access Advisor page is displayed.</p> </li> <li> <p>From the Initial Options, select <span class="bold">Use</span> <span class="bold">Default</span> <span class="bold">Options</span> and click <span class="bold">Continue</span>.</p> </li> <li> <p>From the Workload Sources, select <span class="bold">Current</span> <span class="bold">and</span> <span class="bold">Recent</span> <span class="bold">SQL</span> <span class="bold">Activity</span> and click <span class="bold">Next</span>.</p> <p>The Recommendation Options page is displayed.</p> </li> <li> <p>Select <span class="bold">Partitioning</span> and then <span class="bold">Comprehensive</span> <span class="bold">Mode</span>, then click <span class="bold">Next</span>.</p> <p>The Schedule page is displayed.</p> </li> <li> <p>Enter <code>SQLACCESStest1</code> into the <span class="bold">Task</span> <span class="bold">Name</span> field and click <span class="bold">Next</span></p> <p>The Review page is displayed. Click <span class="bold">Submit</span>.</p> </li> <li> <p>Click <span class="bold">Submit</span>.</p> <p>The Confirmation page is displayed.</p> </li> <li> <p>Select your task and click <span class="bold">View</span> <span class="bold">Result</span>. The Results for Task page is displayed, illustrating possible improvements as a result of partitioning.</p> </li> </ol> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="CIHBDGEF" name="CIHBDGEF"></a><a id="TDPDW00822" name="TDPDW00822"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW0822|Minimizing Resource Consumption: Query Rewrite and Materialized Views"></a>Improving Performance: <a id="sthref236" name="sthref236"></a>Query Rewrite and Materialized Views</h3> <p>In data <a id="sthref237" name="sthref237"></a><a id="sthref238" name="sthref238"></a><a id="sthref239" name="sthref239"></a>warehouses, you can use materialized views to compute and store aggregated data such as the sum of sales. You can also use them to compute joins with or without aggregations, and they are very useful for frequently executed expensive joins between large tables as well as expensive calculations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries because it computes and stores summarized data before processing large joins or queries. Materialized views in these environments are often referred to as summaries.</p> <p>One of the major benefits of creating and maintaining materialized views is the ability to take advantage of the query rewrite feature, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables. The transformation is transparent to the user or application, requiring no intervention and no reference to the materialized view in the SQL statement. Because the query rewrite feature is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.</p> <p>When underlying tables contain large amount of data, it is an expensive and time-consuming process to compute the required aggregates or to compute joins between these tables. In such cases, queries can take minutes or even hours to return the answer. Because materialized views contain already computed aggregates and joins, Oracle Database uses the powerful query rewrite process to quickly answer the query using materialized views.</p> </div> <!-- class="sect2" --> <a id="CIHBGHHG" name="CIHBGHHG"></a><a id="TDPDW00823" name="TDPDW00823"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW00823|Minimizing Resource Consumption: Indexes"></a>Improving Performance: Indexes</h3> <p>Bitmap inde<a id="sthref240" name="sthref240"></a>xes are widely used in data warehousing environments. The environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions. Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of disk space because the indexes can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table. For such applications, bitmap indexing provides the following:</p> <ul> <li> <p>Reduced response time for large classes of ad hoc queries</p> </li> <li> <p>Reduced storage requirements compared to other indexing techniques</p> </li> <li> <p>Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory</p> </li> <li> <p>Efficient maintenance during parallel DML and loads</p> </li> </ul> </div> <!-- class="sect2" --> <a id="CACIGHHJ" name="CACIGHHJ"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW12080" id="TDPDW12080"></a>Improving Performance: Columnar Compression</h3> <p><a id="sthref241" name="sthref241"></a>Columnar compression is introduced in Oracle Database 11<span class="italic">g</span> Release 2 (11.2). Using columnar compression, data can be compressed during bulk-load operations. Enough data must be present in the table in order for compression to be useful. For small segments with very little data, no compression will occur even if you specify it. During the load process, data is transformed into a column-oriented format and then compressed using a set of special compression algorithms. Different levels of compression can be specified; Oracle Database selects the compression algorithm according to the desired level of compression. Oracle Database handles data transformation internally and requires no application changes to use columnar compression.</p> <p>No special installation is required to configure this feature. However, in order to use this feature, the database compatibility parameter must be set to <code>11.2.0</code> or higher. In order for compression to be enabled on a table, it must be turned on at the table creation time, or the table must be altered to enable it.</p> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref242" name="sthref242"></a> <h4><a name="TDPDW12081" id="TDPDW12081"></a>Improving Performance: DBMS_COMPRESSION Package</h4> <p>The PL/SQL package <a id="sthref243" name="sthref243"></a><code>DBMS_COMPRESSION</code> has been added in this release to provide a compression advisor interface to facilitate choosing the correct compression level for an application. As part of the existing Advisor framework in Oracle Database, the compression advisor analyzes the objects in the database, discovers the possible compression ratios that could be achieved, and recommends optimal compression levels. In addition to this package, the compression advisor can also be accessed like other advisors using the existing Advisor framework package <code>DBMS_ADVISOR</code>.</p> <div class="helpinfonote"> <p><span class="bold">See Also: </span><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=APRLS','newWindow').focus()"><span class="italic">Oracle Database PL/SQL Packages and Types Reference</span></a></p> </div> </div> <!-- class="sect3" --> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref244" name="sthref244"></a> <h4><a name="TDPDW12082" id="TDPDW12082"></a>Improving Performance: COMPRESS Clause of CREATE TABLE and ALTER TABLE</h4> <p>The <a id="sthref245" name="sthref245"></a><code>COMPRESS</code> clause of <code>CREATE</code> <code>TABLE</code> and <code>ALTER</code> <code>TABLE</code> has been extended to take a variable for compression level. The SQL syntax can specify to compress the table for Archival, and can specify the compression level.</p> <div class="helpinfonotealso"> <h2>Related Topics</h2> <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a></div> </div> <!-- class="sect3" --></div> <!-- class="sect2" --></div> <!-- class="sect1" --> <p><a id="CIHDIIIA" name="CIHDIIIA"></a><a id="TDPDW0083" name="TDPDW0083"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW0083|Using Resources Optimally"></a>Using Resources Optimally</h2> <p>You can maximize how resources are used in your system by ensuring that operations run in parallel whenever possible. Any database operation would run faster if it were not constrained by a resource at any point in time. The operation may be constrained by CPU resources, I/O capacity, memory, or interconnection traffic (in a cluster). To improve the performance of database operations, you focus on the performance problem and try to eliminate it (so that the problem might shift to another resource). Oracle Database provides functions to optimize the use of available resources, but also to avoid using unnecessary resources.</p> <a id="TDPDW00831" name="TDPDW00831"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref246" name="sthref246"></a> <h3><a name="TDPDW00831|Improving Resource Performance with Parallel Query"></a>Optimizing Performance with Parallel Execution</h3> <p>Parallel execu<a id="sthref247" name="sthref247"></a><a id="sthref248" name="sthref248"></a>tion dramatically reduces response time for data-intensive operations on large databases typically associated with a decision support system (DSS) and data warehouses. You can also implement parallel execution on certain types of online transaction processing (OLTP) and hybrid systems. Parallel execution is sometimes called parallelism. Simply expressed, parallelism is the idea of breaking down a task so that, instead of one process doing all the work in a query, many processes do part of the work at the same time. An example of this is when four processes handle four different quarters in a year instead of one process handling all four quarters by itself. The improvement in performance can be quite high. Parallel execution improves processing for the following:</p> <ul> <li> <p>Queries requiring large table scans, joins, or partitioned index scans</p> </li> <li> <p>Creation of large indexes</p> </li> <li> <p>Creation of large tables (including materialized views)</p> </li> <li> <p>Bulk insert, update, merge, and delete operations</p> </li> </ul> <p>You can also use parallel execution to access object types within an Oracle database. For example, you can use parallel execution to access large objects (LOBs).</p> <p>Parallel execution benefits systems with all of the following characteristics:</p> <ul> <li> <p>Symmetric multiprocessors (SMPs), clusters, or massively parallel systems</p> </li> <li> <p>Sufficient I/O bandwidth</p> </li> <li> <p>Underutilized or intermittently used CPUs (for example, systems where CPU usage is typically less than 30 percent)</p> </li> <li> <p>Sufficient memory to support additional memory-intensive processes, such as sorts, hashing, and I/O buffers</p> </li> </ul> <p>If your system lacks any of these characteristics, then parallel execution might not significantly improve performance. In fact, parallel execution might reduce system performance on overutilized systems or systems with small I/O bandwidth.</p> <a id="TDPDW00840" name="TDPDW00840"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref249" name="sthref249"></a> <h4><a name="TDPDW00840|How Parallel Execution Works"></a>How Parallel Execution Works</h4> <p>Parallel ex<a id="sthref250" name="sthref250"></a>ecution divides the task of running a SQL statement into multiple small units, each of which is executed by a separate process. Also, the incoming data (tables, indexes, partitions) can be divided into parts called <span class="bold">granules</span>. The user shadow process takes on the role as parallel execution coordinator or query coordinator. The query coordinator performs the following tasks:</p> <ul> <li> <p>Parses the query and determines the degree of parallelism</p> </li> <li> <p>Allocates one or two sets of slaves (threads or processes)</p> </li> <li> <p>Controls the query and sends instructions to the parallel query slaves</p> </li> <li> <p>Determines which tables or indexes must be scanned by the parallel query slaves</p> </li> <li> <p>Produces the final output to the user</p> </li> </ul> </div> <!-- class="sect3" --> <a id="TDPDW00841" name="TDPDW00841"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref251" name="sthref251"></a> <h4><a name="TDPDW00841|Setting the Degree of Parallelism"></a>Setting the Degree of Parallelism</h4> <p>The par<a id="sthref252" name="sthref252"></a>allel execution coordinator may enlist two or more of the instance's parallel execution servers to process a SQL statement. The number of parallel execution servers associated with a single operation is known as the <span class="bold">degree of parallelism</span> or DOP.</p> <p>A single operation is a part of a SQL statement, such as an <code>ORDER</code> <code>BY</code> operation or a full table scan to perform a join on a non-indexed column table.</p> <p>The degree of parallelism is specified in the following ways:</p> <ul> <li> <p>At the statement level with <code>PARALLEL</code> hints</p> </li> <li> <p>At the session level by issuing the <code>ALTER</code> <code>SESSION</code> <code>FORCE</code> <code>PARALLEL</code> statement</p> </li> <li> <p>At the table level in the table's definition</p> </li> <li> <p>At the index level in the index's definition</p> </li> </ul> </div> <!-- class="sect3" --> <a id="TDPDW00842" name="TDPDW00842"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --><a id="sthref253" name="sthref253"></a> <h4><a name="TDPDW00842|Example: Setting the Degree of Parallelism"></a>Example: Setting the Degree of Parallelism</h4> <p>Suppose that you want to set the DOP to 4 on a table.</p> <a id="sthref254" name="sthref254"></a> <p class="subhead2">To set the degree of parallelism:</p> <p>Issue the following statement:</p> <pre xml:space="preserve" class="oac_no_warn"> ALTER TABLE orders PARALLEL 4; </pre></div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="TDPDW00832" name="TDPDW00832"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref255" name="sthref255"></a> <h3><a name="TDPDW00832|About Wait Events"></a>About Wait Events</h3> <p><span class="bold">Wait</span> <span class="bold">events</span><a id="sthref256" name="sthref256"></a> are statistics that are incremented by a server process to indicate that the server process had to wait for an event to complete before being able to continue processing. A session could wait for a variety of reasons, including waiting for more input, waiting for the operating system to complete a service such as a disk write operation, or it could wait for a lock or latch.</p> <p>When a session is waiting for resources, it is not doing any useful work. A large number of wait events is a source of concern. Wait event data reveals various symptoms of problems that might be affecting performance, such as latch contention, buffer contention, and I/O contention.</p> </div> <!-- class="sect2" --></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="tdpdw_optimize.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpdw_bandr.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