HomeOracle 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).
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:
To preallocate space in the database, thereby minimizing the amount of future work required to manage objects’ space requirements
To reduce the amount of space wasted due to overallocation of space
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.