Home arrow Oracle arrow Page 3 - Developing and Implementing Applications, continued

Editing Stored Outlines - Oracle

This article, the second of three parts, focuses on the design and creation of applications that use the database. It is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

  1. Developing and Implementing Applications, continued
  2. Switching Consumer Groups
  3. Editing Stored Outlines
  4. The Golden Rule for Space Calculations
  5. Sizing the Objects
  6. Estimating Space Requirements for Indexes
  7. Sizing Tables That Contain Large Objects (LOBs)
  8. Using Object Views
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 5
February 16, 2006

print this article



You can use DBMS_OUTLN_EDIT to edit the stored outlines. The procedures within DBMS_ OUTLN_EDIT are detailed in the following table:





Changes the join position for the hint identified by outline name and hint number to the position specified. Inputs are name, hintno, and newpos.


Creates outline editing tables in the user’s schema.


Drops the outline editing tables in the user’s schema.


Generates a signature for the specified SQL text.


Refreshes the in-memory copy of the outline, synchronizing it with the edits made.


As of Oracle 10g, you no longer need to execute the CREATE_EDIT_ TABLES procedure because the edit tables are available as temporary tables in the SYSTEM schema. The procedure is still available, however, for backward compatibility.

You can use private outlines, which are seen only within your current session. Changes made to a private outline do not affect any other users. To enable private outline editing, set the USE_ PRIVATE_OUTLINES initialization parameter to TRUE. Use the REFRESH_PRIVATE_OUTLINE procedure to have your changes take effect for the in-memory versions of the outlines.

Using SQL Profiles

As of Oracle 10g, you can use SQL profiles to further refine the SQL execution plans chosen by the optimizer. SQL profiles are particularly useful when you are attempting to tune code that you do not have direct access to (for example, within a packaged application). The SQL profile consists of statistics that are specific to the statement, allowing the optimizer to know more about the exact selectivity and cost of the steps in the execution plan.

SQL profiling is part of the automatic tuning capability described in Chapter 8. Once you accept a SQL profile recommendation, it is stored in the data dictionary. As with stored outlines, you can use a category attribute to control its usage. See Chapter 8 for further details on the use of the automatic tools for detection and diagnosis of SQL performance issues.

Sizing Database Objects

Choosing the proper space allocation for database objects is critical. Developers should begin estimating space requirements before the first database objects are created. Afterward, the space requirements can be refined based on the actual usage statistics. In the following sections, you will see the space estimation methods for tables, indexes, and clusters. You’ll also see methods for determining the proper settings for pctfree and pctused.


You can enable Automatic Segment Space Management when you create a tablespace; you cannot enable this feature for existing tablespaces. If you are using Automatic Segment Space Management, Oracle manages the pctused, freelists, and freelist groups parameters.

Why Size Objects?

You should size your database objects for three reasons:

  1. To preallocate space in the database, thereby minimizing the amount of future work required to manage objects’ space requirements
  2. To reduce the amount of space wasted due to overallocation of space
  3. To improve the likelihood of a dropped free extent being reused by another segment

You can accomplish all these goals by following the sizing methodology shown in the following sections. This methodology is based on Oracle’s internal methods for allocating space to database objects. Rather than rely on detailed calculations, the methodology relies on approximations that will dramatically simplify the sizing process while simplifying the long-term maintainability of the database.

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: