Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpdw\tdpdw_optimize.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>Optimizing Data Warehouse Operations</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=" Optimizing Data Warehouse Operations" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10578-01" /> <meta name="topic-id" content="TDPDW011" /> <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_refresh.htm" title="Previous" type="text/html" /> <link rel="next" href="tdpdw_perform.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_refresh.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpdw_perform.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="CACJDGBH" name="CACJDGBH"></a><a id="TDPDW011" name="TDPDW011"></a></p> <h1><a name="TDPDW011|Maintaining the Data Warehouse"></a>Optimizing Data Warehouse Operations</h1> <p><a name="BEGIN" id="BEGIN"></a></p> <p>This section discusses how to optimize your data warehouse's performance, and includes the following topics:</p> <ul> <li> <p><a href="#BABGCFHJ">Avoiding System Overload</a></p> </li> <li> <p><a href="#CFHGJGGH">Optimizing the Use of Indexes and Materialized Views</a></p> </li> <li> <p><a href="#CFHEECGJ">Optimizing Storage Requirements</a></p> </li> </ul> <p><a id="BABGCFHJ" name="BABGCFHJ"></a><a id="TDPDW00271" name="TDPDW00271"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW00271" id="TDPDW00271"></a>Avoiding System Overload</h2> <p>This section discusses how to identify and avoid system overload. In general, you should use the automatic diagnostic feature Automatic Database Diagnostic Monitor (ADDM) to identify performance problems with the database, as described in <a href="topicid:TDPPT025">Managing AWR Snapshots</a>. This section discusses additional methods for avoiding performance problems in your system, and includes the following sections:</p> <ul> <li> <p><a href="#BJEHCEBI">Monitoring System Performance</a></p> </li> <li> <p><a href="#BJEGAAFD">Using Database Resource Manager</a></p> </li> </ul> <a id="BJEHCEBI" name="BJEHCEBI"></a><a id="TDPDW0091" name="TDPDW0091"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW0091|Monitoring System Performance"></a>Monitoring System Performance</h3> <p>This section provides information on how to avoid system overload by regularly monitoring important metrics. You can monitor these metrics through the use of the Database Performance page in Oracle Enterprise Manager. This section includes the following topics:</p> <ul> <li> <p><a href="#CHDHAHEH">Monitoring Parallel Execution Performance</a></p> </li> <li> <p><a href="#CHDBIBEH">Monitoring I/O</a></p> </li> </ul> <a id="CHDHAHEH" name="CHDHAHEH"></a><a id="TDPDW00911" name="TDPDW00911"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4><a name="TDPDW00911|EM Monitoring Parallel Execution"></a>Monitoring Parallel Execution Performance</h4> <p>This section descri<a id="sthref197" name="sthref197"></a><a id="sthref198" name="sthref198"></a>bes how to monitor parallel execution performance. Suppose that you see many parallel statements are being downgraded. This may indicate a performance problem. Statements that run with a degree of parallelism lower than expected can take much longer, and users may experience different execution times depending on whether statements got downgraded. Possible causes for downgraded parallel statements include the following:</p> <ul> <li> <p>The initial degree of parallelism is higher than it should be and should be lowered.</p> </li> <li> <p>There are not enough parallel servers available, which may indicate the system is overloaded.</p> </li> </ul> <a id="sthref199" name="sthref199"></a> <p class="subhead2">To monitor parallel execution performance:</p> <ol> <li> <p>On the Database Home page, click <span class="bold">Performance</span>.</p> <p>The Performance page is displayed.</p> </li> <li> <p>Scroll down the page. Under the list of links, click <span class="bold">PQ</span>.</p> <p>The PQ page is displayed. Parallel query performance characteristics are shown for:</p> <ul> <li> <p>Parallel sessions</p> </li> <li> <p>Parallel slaves</p> </li> <li> <p>DML and DDL parallelization</p> </li> <li> <p>Serialization and statement downgrades</p> </li> </ul> </li> </ol> </div> <!-- class="sect3" --> <a id="CHDBIBEH" name="CHDBIBEH"></a><a id="TDPDW00912" name="TDPDW00912"></a> <div class="sect3"><!-- infolevel="all" infotype="General" --> <h4><a name="TDPDW00912|EM Monitoring I/O"></a>Monitoring I/O</h4> <p>This section descri<a id="sthref200" name="sthref200"></a><a id="sthref201" name="sthref201"></a>bes how to monitor I/O performance. If the throughput on your system is significantly lower than what you expect based on the system configuration (see <a href="tdpdw_system.htm#CHDBFDGI">Setting Up Your Data Warehouse System</a>), and your users complain about performance issues, then there could be a performance problem. In a well-configured system that runs a typical data warehouse workload, you would expect a large portion of large I/Os and a relatively low latency (lower than 30ms) for a single block I/O.</p> <a id="sthref202" name="sthref202"></a> <p class="subhead2">To monitor I/O performance:</p> <ol> <li> <p>On the Database Home page, click <span class="bold">Performance</span>.</p> <p>The Performance page is displayed.</p> </li> <li> <p>Scroll down the page. Under the list of links, click <span class="bold">I/O</span>.</p> <p>The I/O page is displayed, displaying I/O Megabytes per Second by Function and I/O Requests per Second by Function.</p> </li> <li> <p>For details regarding read and write operations, select <span class="bold">IO Type</span>.</p> <p>I/O details are shown for the following:</p> <ul> <li> <p>Large Writes</p> </li> <li> <p>Large Reads</p> </li> <li> <p>Small Writes</p> </li> <li> <p>Small Reads</p> </li> </ul> </li> </ol> </div> <!-- class="sect3" --></div> <!-- class="sect2" --> <a id="BJEGAAFD" name="BJEGAAFD"></a><a id="TDPDW0092" name="TDPDW0092"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW0092|Using Resource Manager"></a>Using Database Resource Manager</h3> <p>The Database <a id="sthref203" name="sthref203"></a>Resource Manager provides the ability to prioritize work within the Oracle system. Users with higher priority jobs get resources in order to minimize response time for online work, for example, while users with lower priority jobs, such as batch jobs or reports, might encounter slower response times. This priority assignment allows for more granular control over resources and provides features such as automatic consumer group switching, maximum active sessions control, query execution time estimation and undo pool quotas for consumer groups.</p> <p>You can specify the maximum number of concurrently active sessions for each consumer group. When this limit is reached, the Database Resource Manager queues all subsequent requests and runs them only after existing active sessions complete.</p> <p>The Database Resource Manager is part of the Oracle Database and can distinguish different processes inside the database. As a result, the Database Resource Manager can assign priorities to individual operations running inside the database.</p> <p>With the Database Resource Manager, you can do the following:</p> <ul> <li> <p>Guarantee certain users a minimum amount of processing resources regardless of the load on the system and the number of users.</p> </li> <li> <p>Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational online analytical processing) applications than to batch jobs.</p> </li> <li> <p>Allow automatic switching of users from one group to another based on administrator-defined criteria. If a member of a particular group of users creates a session that runs for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.</p> </li> <li> <p>Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.</p> </li> </ul> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <p><a id="CFHGJGGH" name="CFHGJGGH"></a><a id="TDPDW0101" name="TDPDW0101"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW0101|Optimizing Indexes and Materialized Views"></a>Optimizing the Use of Indexes and Materialized Views</h2> <p>You can improve <a id="sthref204" name="sthref204"></a><a id="sthref205" name="sthref205"></a><a id="sthref206" name="sthref206"></a>the performance of your data warehouse by the proper use of indexes and materialized views. A key benefit of the SQL Access Advisor is its capability to use the current workload as the basis for the recommendations.</p> <a id="BABHAJJD" name="BABHAJJD"></a><a id="TDPDW01011" name="TDPDW01011"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3><a name="TDPDW01011|EM Example of Optimizing Indexes Using the SQL Access Advisor"></a>Example: Optimizing Indexes and Materialized Views Using the SQL Access Advisor</h3> <p>For this example, assume you have a workload running on the system that may benefit from certain indexes or materialized views.</p> <a id="sthref207" name="sthref207"></a> <p class="subhead2">To optimize an index and materialized view:</p> <ol> <li> <p>From the Advisor Central page, click <span class="bold">SQL</span> <span class="bold">Advisors</span>.</p> <p>The Advisors page is displayed.</p> </li> <li> <p>From the Advisors page, 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>Select <span class="bold">Use</span> <span class="bold">Default</span> <span class="bold">Options</span> and click <span class="bold">Continue</span>.</p> <p>The Workload Source page is displayed.</p> </li> <li> <p>Select your workload source as <span class="bold">Use an Existing SQL Tuning Set</span>. Go to a SQL Tuning Set and click <span class="bold">Select</span>. Then click <span class="bold">Next</span>.</p> <p>The Recommendation Options page is displayed.</p> </li> <li> <p>Select <span class="bold">Indexes</span>, <span class="bold">Materialized</span> <span class="bold">Views</span>, and <span class="bold">Comprehensive</span> <span class="bold">Mode</span>. Click <span class="bold">Next</span>.</p> <p>The Schedule page is displayed.</p> </li> <li> <p>Click <span class="bold">Submit</span>.</p> <p>The Recommendations page is displayed.</p> </li> <li> <p>Enter a name in the <span class="bold">Name</span> field and select <span class="bold">Immediately</span> for when it should start. Then click <span class="bold">Next</span>.</p> <p>The Review page is displayed.</p> </li> <li> <p>Click <span class="bold">Submit</span>.</p> <p>The Confirmation page is displayed.</p> </li> <li> <p>Select your task name and click <span class="bold">View</span> <span class="bold">Result</span>.</p> <p>You can view additional information under Recommendations, SQL Statements, or Details.</p> </li> </ol> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <p><a id="CFHEECGJ" name="CFHEECGJ"></a><a id="TDPDW0102" name="TDPDW0102"></a></p> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2><a name="TDPDW0102|Optimizing Storage Requirements"></a>Optimizing Storage Requirements</h2> <p>You can <a id="sthref208" name="sthref208"></a>reduce your storage requirements by compressing data, which is achieved by eliminating duplicate values in a database block. Database objects that can be compressed include tables and materialized views. For partitioned tables, you can choose to compress some or all partitions. Compression attributes can be declared for a tablespace, a table, or a partition of a table. If declared at the tablespace level, then all tables created in that tablespace are compressed by default. You can alter the compression attribute for a table (or a partition or tablespace), and the change only applies only to new data going into that table. As a result, a single table or partition may contain some compressed blocks and some regular blocks. This guarantees that data size will not increase as a result of compression; in cases where compression could increase the size of a block, it is not applied to that block.</p> <a id="TDPDW01021" name="TDPDW01021"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --><a id="sthref209" name="sthref209"></a> <h3><a name="TDPDW01021|Using Data Compression"></a>Using Data Compression to Improve Storage</h3> <p>You can compr<a id="sthref210" name="sthref210"></a>ess several partitions or a complete partitioned heap-organized table. You do this either by defining a complete partitioned table as being compressed, or by defining it on a per-partition level. Partitions without a specific declaration inherit the attribute from the table definition or, if nothing is specified on table level, from the tablespace definition.</p> <p>The decision about whether or not a partition should be compressed or stay uncompressed adheres to the same rules as a nonpartitioned table. However, due to the ability of range and composite partitioning to separate data logically into distinct partitions, such a partitioned table is an ideal candidate for compressing parts of the data (partitions) that are mainly read-only. It is, for example, beneficial in all rolling window operations as a kind of intermediate stage before aging out old data. With data compression, you can keep more old data online, minimizing the burden of additional storage consumption.</p> <p>You can also change any existing uncompressed table partition later, add new compressed and uncompressed partitions, or change the compression attribute as part of any partition maintenance operation that requires data movement, such as <code>MERGE</code> <code>PARTITION</code>, <code>SPLIT</code> <code>PARTITION</code>, or <code>MOVE</code> <code>PARTITION</code>. The partitions can contain data or they can be empty.</p> <p>The access and maintenance of a partially or fully compressed partitioned table are the same as for a fully uncompressed partitioned table. All rules that apply to fully uncompressed partitioned tables are also valid for partially or fully compressed partitioned tables.</p> <a id="sthref211" name="sthref211"></a> <p class="subhead2">To use data compression:</p> <p>The following example creates a range-partitioned table with one compressed partition <code>costs_old</code>. The compression attribute for the table and all other partitions is inherited from the tablespace level.</p> <pre xml:space="preserve" class="oac_no_warn"> CREATE TABLE costs_demo ( prod_id NUMBER(6), time_id DATE, unit_cost NUMBER(10,2), unit_price NUMBER(10,2)) PARTITION BY RANGE (time_id) (PARTITION costs_old VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS, PARTITION costs_q1_2003 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')), PARTITION costs_q2_2003 VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')), PARTITION costs_recent VALUES LESS THAN (MAXVALUE)); </pre></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_refresh.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdpdw_perform.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