Developing and Implementing Applications, continued - Editing Stored Outlines
(Page 3 of 8 )

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

 

Procedure	Description
CHANGE_JOIN_POS	Changes the join position for the hint identified by outline name and hint number to the position specified. Inputs are name, hintno, and newpos.
CREATE_EDIT_TABLES	Creates outline editing tables in the user’s schema.
DROP_EDIT_TABLES	Drops the outline editing tables in the user’s schema.
GENERATE_SIGNATURE	Generates a signature for the specified SQL text.
REFRESH_PRIVATE_OUTLINE	Refreshes the in-memory copy of the outline, synchronizing it with the edits made.

NOTE

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.

NOTE

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.

Next: The Golden Rule for Space Calculations >>
 

More Oracle Articles
More By McGraw-Hill/Osborne

Comments On: Developing and Implementing Applications, continued

· This article is an excerpt from the book "Oracle Database 10g DBA Handbook,"...   >>> Post your comment now!

Buy this book...

This article 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). Check it out today at your favorite bookstore. Buy this book now.