Oracle Internals

Tips, Tricks, and Techniques for DBAs

Donald K. Burleson

• Publisher: Auerbach Publications
• Year: 2017
• ISBN:
  9781135514655
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

If you are a typical Oracle professional, you don't have the luxury of time to keep up with new technology and read all the new manuals to understand each new feature of the latest release from Oracle. You need a comprehensive source of information and in-depth tips and techniques for using the new technology. You need Oracle Internals: Tips, Tricks, and Techniques for DBAs. Oracle has evolved from a simple relational database into one of the most complex e-commerce platforms ever devised. It's not enough for you to understand just the Oracle database. You must also understand the components of the Web server technology, XML, Oracle Security, Oracle and Java, and a host of other issues in order to do your job properly. This book is a compendium of the best and most useful articles from Oracle Internals, Auerbach Publications' newsletter for Oracle database administrators and other Oracle professionals. Edited by Oracle guru Don Burleson, it provides the type of in-depth, highly technical information not found in any other book, information only available from peers and consultants. The chapters focus on the truly tough stuff - proven techniques learned in the trenches. You could get this information from other sources, but you'd have to hunt and peck for it. Can you afford that kind of time? Oracle Internals: Tips, Tricks, and Techniques for DBAs gives you knowledge and advice directly applicable to your work in one easy-to-use resource.

XIII

ORACLE DATABASE INTERNALS MANAGEMENT

Chapters 59 through 69 derive from numerous and sundry articles submitted by Oracle Internals experts to provide an in-depth treatment of the internal workings of various Oracle components. These techniques can dramatically improve the performance and manageability of your Oracle systems.

These include discussions by Brad Brown, chief architect at The Ultimate Software Consultants (TUSC), who reveals some internal hardware operations and shows how you can tune your hardware for optimal Oracle performance. We also see some great chapters that discuss Oracle concurrency management, metadata schema usage, the use of triggers in high-volume applications, as well as discussions on the new features of Oracle8i using the DBMS_LOCK package to improve the performance of the Oracle8i database.

Chapter 59

Getting a Quick Start with Oracle8i

Roman Kab

This chapter focuses on a few very powerful Oracle8i features. Oracle8i is packed with new features and enhancements to the existing, but not fully developed, features introduced in Oracle8. If you are a veteran database administrator who transitioned from Oracle version 6 to Oracle 7.0 and then to 7.1, you will quickly see a trend in feature and functionality introductions. Oracle 7.0 was dramatically different from version 6, but 7.1 really brought Oracle into a new level of relational database management systems (RDBMS). This chapter does not attempt to address new Java Virtual Machine (JVM) support in Oracle8i.

Transportable Tablespaces

The first feature is TRANSPORTABLE TABLESPACES. This option enables tablespace(s) to be copied and plugged into another database. This eliminates long-running, error-prone exports/imports to move operational data to the data warehouse. It is possible to archive large tablespaces faster and with much easier restore. The restore process could be applied to any database, providing more flexibility.

Here are some steps to follow to unplug/transport/plug tablespace(s):

1. Identify tablespaces that will not introduce orphans when moved (i.e., indexes without tables). Run:

    execute dbms_tts.transport_set_check(‘ts1,ts2’, TRUE); select * from transport_set_violations; 

   
   
2. Make the target tablespace READ ONLY.
3. EXP sys/change_on_install TRANSPORT_TABLESPACE=y TABLESPACES=t1,t2 TRIGGERS=y CONSTRAINTS=y GRANTS=y FILE=tt.dmp
4. Copy corresponding data files.
5. Import entries for the transported tablespaces — a very quick import.

    IMP TRANSPORT_TABLESPACE=y DATAFILES=‘/db/t1_jan’,‘/db/t2_feb’ TABLESPACES=t1,t2,... TTS_OWNERS=scott FROMUSER=scott TOUSER=scott FILE=tt.dmp 

Transportable tablespaces can be used to distribute data to other databases as well as to consolidate data (see Exhibit 59.1).

Optimizer Plan Stability

If you work at a vendor that builds applications on Oracle databases or an information technology (IT) development shop that needs the application to perform as well at the client site as during system testing, you need to use stored outlines.

Oracle8i offers stored outlines to ensure that the optimizer generates the same execution plan regardless of changes to the system configuration, init parameters, or object statistics.

This feature also benefits high-end Online Transaction Processing (OLTP) sites by having Structured Query Language (SQL) execute without having to invoke the cost-based optimizer at each SQL invocation.

Using CREATE OUTLINE, the command user will create a stored outline that contains an execution plan for each SQL statement. The execution plan remains the same for all SQL statements associated with this outline name. There may be a need to create multiple outlines for the same SQL statements, depending on the time of day they execute (i.e., batch versus daily OLTP reports). This dynamic switching between outlines provides great flexibility and ensures proper application executions. See Appendix 59.1 for an example.

Exhibit 59.1 Data Distribution with Portable Tablespaces

Exhibit 59.2 Oracle Transparently Rewrites SQL Statements to Utilize Materialized Views

Materialized View

Oracle8i introduces many powerful features designed especially for large data warehouses. One of them is summary management using materialized views.

A materialized view is implemented by existing Oracle8 replication technologies. If you have worked with replication, the concept of materialized views will seem familiar. It features a snapshot-like setup with tables storing precomputed summary results. The power of the materialized views comes from transparent access to the views. The Oracle8i database server automatically rewrites queries to use the summary data, rather than retrieving data from detail tables by doing expensive joins and aggregate operations. As shown in Exhibit 59.2, this query rewrite facility is totally transparent to the application, which is not aware of the materialized view. The rewrite capability will appear in other features of the Oracle8i engine.

The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a subquery, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view. Once created, the view is maintained much like snapshots. The refresh process either completely or incrementally refreshes the data.

Like the replication set of Application Program Interface (API), Oracle provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. These are packaged in the DBMS_OLAP package. See Appendix 59.2 for an example.

Logminer

The ability to analyze data in an online redo or archived redo log file may not appear at the top of a DBA’s list. But when logical data corruption occurs, UNDO SQL statements can be constructed to remedy the problem.

Logminer can enhance application auditing by tracking specific changes to the tables based on username, table name, time, or transaction. Logminer can analyze log files from other databases, as long as they were created on the same operating system and use the same version. See Appendix 59.3 for an example.

Virtual Private Database

Today’s applications and databases require server-enforced and granular access control because clients are connecting from local desktops and external Internet sessions. This new, powerful feature provides the means to access application data from many sources. The virtual private database feature has two components: fine-grained access control and application context. Here is how they work together to implement a safe and consistent security policy across applications.

Fine-Grained Access Control

Access control is implemented by attaching security policies to individual tables. These policies are created using PL/SQL stored packages or functions. The Oracle-supplied DBMS_RLS package is used to associate the security policy functions with the table or view. O...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• Contributors
• Introduction
• I: ORACLE APPLICATIONS DEVELOPMENT TECHNIQUES
• II: ORACLE WEB DEVELOPMENT
• III: JAVA AND ORACLE
• IV: ORACLE AND UNIX
• V: ORACLE BACKUP AND RECOVERY
• VI: ORACLE SQL TUNING
• VII: ORACLE DISK MANAGEMENT
• VIII: DISTRIBUTED ORACLE SYSTEMS
• IX: ORACLE BUFFER AND SGA MANAGEMENT
• X: ORACLE TABLE MANAGEMENT
• XI: ORACLE SECURITY
• XII: ORACLE DATA WAREHOUSING
• XIII: ORACLE DATABASE INTERNALS MANAGEMENT