Contents

---

Programming in Lua

This is an online version of the book

Programming in
Lua

by Roberto Ierusalimschy

Lua.org, December 2003

ISBN 85-903798-1-7

---

Contents

Preface

Audience

Other Resources

A Few Typographical Conventions

About the Book

Acknowledgments

 

 

Part I. The Language

 

1 Getting Started

1.1 Chunks

1.2 Global Variables

1.3 Some Lexical Conventions

1.4 The Stand-Alone Interpreter

2 Types and Values

2.1 Nil

2.2 Booleans

2.3 Numbers

2.4 Strings

2.5 Tables

2.6 Functions

2.7 Userdata and Threads

3 Expressions

3.1 Arithmetic Operators

3.2 Relational Operators

3.3 Logical Operators

3.4 Concatenation

3.5 Precedence

3.6 Table Constructors

4 Statements

4.1 Assignment

4.2 Local Variables and Blocks

4.3 Control Structures

4.3.1 if then else

4.3.2 while

4.3.3 repeat

4.3.4 Numeric for

4.3.5 Generic for

4.4 break and return

5 Functions

5.1 Multiple Results

5.2 Variable Number of Arguments

5.3 Named Arguments

6 More about Functions

6.1 Closures

6.2 Non-Global Functions

6.3 Proper Tail Calls

7 Iterators and the Generic for

7.1 Iterators and Closures

7.2 The Semantics of the Generic for

7.3 Stateless Iterators

7.4 Iterators with Complex State

7.5 True Iterators

8 Compilation, Execution, and Errors

8.1 The require Function

8.2 C Packages

8.3 Errors

8.4 Error Handling and Exceptions

8.5 Error Messages and Tracebacks

9 Coroutines

9.1 Coroutine Basics

9.2 Pipes and Filters

9.3 Coroutines as Iterators

9.4 Non-Preemptive Multithreading

10 Complete Examples

10.1 Data Description

10.2 Markov Chain Algorithm

 

 

Part II. Tables and Objects

 

11 Data Structures

11.1 Arrays

11.2 Matrices and Multi-Dimensional Arrays

11.3 Linked Lists

11.4 Queues and Double Queues

11.5 Sets and Bags

11.6 String Buffers

12 Data Files and Persistence

12.1 Serialization

12.1.1 Saving Tables without Cycles

12.1.2 Saving Tables with Cycles

13 Metatables and Metamethods

13.1 Arithmetic Metamethods

13.2 Relational Metamethods

13.3 Library-Defined Metamethods

13.4 Table-Access Metamethods

13.4.1 The __index Metamethod

13.4.2 The __newindex Metamethod

13.4.3 Tables with Default Values

13.4.4 Tracking Table Accesses

13.4.5 Read-Only Tables




14 The Environment

14.1 Accessing Global Variables with Dynamic Names

14.2 Declaring Global Variables

14.3 Non-Global Environments

15 Packages

15.1 The Basic Approach

15.2 Privacy

15.3 Packages and Files

15.4 Using the Global Table

15.5 Other Facilities

16 Object-Oriented Programming

16.1 Classes

16.2 Inheritance

16.3 Multiple Inheritance

16.4 Privacy

16.5 The Single-Method Approach

17 Weak Tables

17.1 Memoize Functions

17.2 Object Attributes

17.3 Revisiting Tables with Default Values

 

 

Part III. The Standard Libraries

 

18 The Mathematical Library

19 The Table Library

19.1 Array Size

19.2 Insert and Remove

19.3 Sort

20 The String Library

20.1 Pattern-Matching Functions

20.2 Patterns

20.3 Captures

20.4 Tricks of the Trade

21 The I/O Library

21.1 The Simple I/O Model

21.2 The Complete I/O Model

21.2.1 A Small Performance Trick

21.2.2 Binary Files

21.3 Other Operations on Files

22 The Operating System Library

22.1 Date and Time

22.2 Other System Calls

23 The Debug Library

23.1 Introspective Facilities

23.1.1 Accessing Local Variables

23.1.2 Accessing Upvalues

23.2 Hooks

23.3 Profiles

 

 

Part IV. The C API

 

24 An Overview of the C API

24.1 A First Example

24.2 The Stack

24.2.1 Pushing Elements

24.2.2 Querying Elements

24.2.3 Other Stack Operations

24.3 Error Handling with the C API

24.3.1 Error Handling in Application Code

24.3.2 Error Handling in Library Code

25 Extending your Application

25.1 Table Manipulation

25.2 Calling Lua Functions

25.3 A Generic Call Function

26 Calling C from Lua

26.1 C Functions

26.2 C Libraries

27 Techniques for Writing C Functions

27.1 Array Manipulation

27.2 String Manipulation

27.3 Storing State in C Functions

27.3.1 The Registry

27.3.2 References

27.3.3 Upvalues

28 User-Defined Types in C

28.1 Userdata

28.2 Metatables

28.3 Object-Oriented Access

28.4 Array Access

28.5 Light Userdata

29 Managing Resources

29.1 A Directory Iterator

29.2 An XML Parser

---

Last update:
Tue Jul 6 19:23:30 BRT 2004

Specific Practices by Goal

[ Team LiB ]

Specific Practices by Goal SG 1 Establish an Organizational Training Capability A training capability that supports the organization's management and technical roles is established and maintained. Practice-to-Goal Relationship Table Continuous Representation Staged Representation SG 1 Establish an Organizational Training Capability SG 1 Establish an Organizational Training Capability SP 1.1-1 Establish the Strategic Training Needs	SP 1.1-1 Establish the Strategic Training Needs
SP 1.2-1 Determine Which Training Needs Are the Responsibility of the Organization	SP 1.2-1 Determine Which Training Needs Are the Responsibility of the Organization
SP 1.3-1 Establish an Organizational Training Tactical Plan	SP 1.3-1 Establish an Organizational Training Tactical Plan
SP 1.4-1 Establish Training Capability	SP 1.4-1 Establish Training Capability
SG 2 Provide Necessary Training	SG 2 Provide Necessary Training
SP 2.1-1 Deliver Training	SP 2.1-1 Deliver Training
SP 2.2-1 Establish Training Records	SP 2.2-1 Establish Training Records
SP 2.3-1 Assess Training Effectiveness	SP 2.3-1 Assess Training Effectiveness
GG 1 Achieve Specific Goals
GP 1.1 Perform Base Practices
GG 2 Institutionalize a Managed Process	GG 3 Institutionalize a Defined Process
GP 2.1 Establish an Organizational Policy	GP 2.1 Establish an Organizational Policy
GP 2.2 Plan the Process	GP 2.2 Plan the Process
GP 2.3 Provide Resources	GP 2.3 Provide Resources
GP 2.4 Assign Responsibility	GP 2.4 Assign Responsibility
GP 2.5 Train People	GP 2.5 Train People
GP 2.6 Manage Configurations	GP 2.6 Manage Configurations
GP 2.7 Identify and Involve Relevant Stakeholders	GP 2.7 Identify and Involve Relevant Stakeholders
GP 2.8 Monitor and Control the Process	GP 2.8 Monitor and Control the Process
GP 2.9 Objectively Evaluate Adherence	GP 2.9 Objectively Evaluate Adherence
GP 2.10 Review Status with Higher Level Management	GP 2.10 Review Status with Higher Level Management
GG 3 Institutionalize a Defined Process
GP 3.1 Establish a Defined Process	GP 3.1 Establish a Defined Process
GP 3.2 Collect Improvement Information	GP 3.2 Collect Improvement Information
GG 4 Institutionalize a Quantitatively Managed Process
GP 4.1 Establish Quantitative Objectives for the Process
GP 4.2 Stabilize Subprocess Performance
GG 5 Institutionalize an Optimizing Process
GP 5.1 Ensure Continuous Process Improvement
GP 5.2 Correct Root Causes of Problems

The organization identifies the training required to develop the skills and the knowledge necessary to perform enterprise activities. Once the needs are identified, a training program addressing those needs is developed.

For Integrated Product and Process Development Cross-functional training, leadership training, interpersonal skills training, and training in the skills needed to integrate appropriate business and technical functions is needed by integrated team members. The potentially wider range of re-quirements and participant backgrounds may require relevant stakeholders who were not involved in requirements development to take cross training in the disciplines involved in product design in order to commit to requirements with a full understanding of the range of requirements and their interrelationships.

SP 1.1-1 Establish the Strategic Training Needs

Establish and maintain the strategic training needs of the organization.

Examples of sources of strategic training needs include the following: Organization's standard processes Organization's strategic business plan Organization's process improvement plan Enterprise-level initiatives Skill appraisals Risk analyses

Typical Work Products

1. Training needs




2. Assessment analysis

Subpractices

1. Analyze the organization's strategic business objectives and process improvement plan to identify potential future training needs.




2. Document the strategic training needs of the organization.

   Examples of categories of training needs include (but are not limited to) the following: Process analysis and documentation Engineering (e.g., requirements analysis, design, testing, configuration management, and quality assurance) Selection and management of suppliers Management (e.g., estimating, tracking, and risk management)




3. Determine the roles and skills needed to perform the organization's set of standard processes.




4. Document the training needed to perform the roles in the organization's set of standard processes.




5. Revise the organization's strategic needs and required training as necessary.

SP 1.2-1 Determine Which Training Needs Are the Responsibility of the Organization

Determine which training needs are the responsibility of the organization and which will be left to the individual project or support group.

Refer to the Project Planning process area for more information about project-and support-group-specific plans for training.

In addition to strategic training needs, organizational training addresses training requirements that are common across projects and support groups. Projects and support groups have the primary responsibility for identifying and addressing their specific training needs. The organization's training staff is only responsible for addressing common cross-project and support group training needs. In some cases, however, the organization's training staff may address additional training needs of projects and support groups, as negotiated with them, within the context of the training resources available and the organization's training priorities.

Typical Work Products

1. Common project and support group training needs




2. Training commitments

Subpractices

1. Analyze the training needs identified by the various projects and support groups.

   
   

   Analysis of project and support group needs is intended to identify common training needs that can be most efficiently addressed organization-wide. These needs-analysis activities are used to anticipate future training needs that are first visible at the project and support group level.

   
   




2. Negotiate with the various projects and support groups on how their specific training needs will be satisfied.

   
   

   The support provided by the organization's training staff depends on the training resources available and the organization's training priorities.

   
   

   Examples of training appropriately performed by the project or support group include the following: Training in the application domain of the project Training in the unique tools and methods used by the project or support group




3. Document the commitments for providing training support to the projects and support groups.

SP 1.3-1 Establish an Organizational Training Tactical Plan

Establish and maintain an organizational training tactical plan.

The organizational training tactical plan is the plan to deliver the training that is the responsibility of the organization. This plan is adjusted periodically in response to changes (e.g., in needs or resources) and to evaluations of effectiveness.

Typical Work Products

1. Organizational training tactical plan

Subpractices

1. Establish plan content.

   
   

   Organizational training tactical plans typically contain the following:

   
   

   
   

   • Training needs

   
   

   • Training topics

   
   

   • Schedules based on training activities and their dependencies

   
   

   • Methods used for training

   
   

   • Requirements and quality standards for training materials

   
   

   • Training tasks, roles, and responsibilities

   
   

   • Required resources including tools, facilities, environments, staffing, and skills and knowledge

   
   



2. Establish commitments to the plan.

   
   

   Documented commitments by those responsible for implementing and supporting the plan are essential for the plan to be effective.

   
   




3. Revise plan and commitments as necessary.

SP 1.4-1 Establish Training Capability

Establish and maintain training capability to address organizational training needs.

Refer to the Decision Analysis and Resolution process area for how to apply decision-making criteria when selecting training approaches and developing training materials.

Typical Work Products

1. Training materials and supporting artifacts

Subpractices

1. Select the appropriate approaches to satisfy specific organizational training needs.

   
   

   Many factors may affect the selection of training approaches, including audience-specific knowledge, costs and schedule, work environment, and so on. Selection of an approach requires consideration of the means to provide skills and knowledge in the most effective way possible given the constraints.

   
   

   Examples of training approaches include the following: Classroom training Computer-aided instruction Guided self-study Formal apprenticeship and mentoring programs Facilitated videos Chalk talks Brown-bag lunch seminars Structured on-the-job training




2. Determine whether to develop training materials internally or acquire them externally.

   
   

   Determine the costs and benefits of internal training development or of obtaining training externally.

   
   

   Example criteria that can be used to determine the most effective mode of knowledge or skill acquisition include the following: Performance objectives Time available to prepare for project execution Business objectives Availability of in-house expertise Availability of training from external sources

   
   

   Examples of external sources of training include the following: Customer-provided training Commercially available training courses Academic programs Professional conferences Seminars




3. Develop or obtain training materials.

   
   

   Training may be provided by the project, by support groups, by the organization, or by an external organization. The organization's training staff coordinates the acquisition and delivery of training regardless of its source.

   
   

   Examples of training materials include the following: Courses Computer-aided instruction Videos




4. Develop or obtain qualified instructors.

   
   

   To ensure that internally provided training instructors have the necessary knowledge and training skills, criteria can be defined to identify, develop, and qualify them. In the case of externally provided training, the organization's training staff can investigate how the training provider determines which instructors will deliver the training. This can also be a factor in selecting or continuing to use a specific training provider.

   
   




5. Describe the training in the organization's training curriculum.

   Examples of the information provided in the training descriptions for each course include the following: Topics covered in the training Intended audience Prerequisites and preparation for participating Training objectives Length of the training Lesson plans Completion criteria for the course Criteria for granting training waivers




6. Revise the training materials and supporting artifacts as necessary.

   Examples of situations in which the training materials and supporting artifacts may need to be revised include the following: Training needs change (e.g., when new technology associated with the training topic is available) An evaluation of the training identifies the need for change (e.g., evaluations of training effectiveness surveys, training program performance assessments, or instructor evaluation forms)

SG 2 Provide Necessary Training

Training necessary for individuals to perform their roles effectively is provided.

In selecting people to be trained, the following should be taken into consideration:

• Background of the target population of training participants




• Prerequisite background to receive training




• Skills and abilities needed by people to perform their roles




• Need for cross-discipline technical management training for all disciplines, including project management




• Need for managers to have training in appropriate organizational processes




• Need for training in the basic principles of discipline-specific engineering to support personnel in quality management, configuration management, and other related support functions




• Need to provide competency development for critical functional areas

SP 2.1-1 Deliver Training

Deliver the training following the organizational training tactical plan.

Typical Work Products

1. Delivered training course

Subpractices

1. Select the people who will receive the training.

   
   

   Training is intended to impart knowledge and skills to people performing various roles within the organization. Some people already possess the knowledge and skills required to perform well in their designated roles. Training can be waived for these people, but care should be taken that training waivers are not abused.

   
   




2. Schedule the training, including any resources, as necessary (e.g., facilities and instructors).

   
   

   Training should be planned and scheduled. Training is provided that has a direct bearing on the expectations of work performance. Therefore, optimal training occurs in a timely manner with regard to imminent job-performance expectations. These expectations often include the following:

   
   

   
   

   • Training in the use of specialized tools

   
   

   • Training in procedures that are new to the individual who will perform them

   
   



3. Conduct the training.

   
   

   Experienced instructors should perform training. When possible, training is conducted in settings that closely resemble actual performance conditions and includes activities to simulate actual work situations. This approach includes integration of tools, methods, and procedures for competency development. Training is tied to work responsibilities so that on-the-job activities or other outside experiences will reinforce the training within a reasonable time after the training.

   
   




4. Track the delivery of training against the plan.

SP 2.2-1 Establish Training Records

Establish and maintain records of the organizational training.

Refer to the Project Monitoring and Control process area for information about how project- or support group training records are maintained.

The scope of this practice is for the training performed at the organizational level. Establishment and maintenance of training records for project-or support-group-sponsored training is the responsibility of each individual project or support group.

Typical Work Products

1. Training records




2. Training updates to the organizational repository

Subpractices

1. Keep records of all students who successfully complete each training course or other approved training activity as well as those who are unsuccessful.




2. Keep records of all staff who have been waived from specific training.

   
   

   The rationale for granting a waiver should be documented, and both the manager responsible and the manager of the excepted individual should approve the waiver for organizational training.

   
   




3. Keep records of all students who successfully complete their designated required training.




4. Make training records available to the appropriate people for consideration in assignments.

   
   

   Training records may be part of a skills matrix developed by the training organization to provide a summary of the experience and education of people, as well as training sponsored by the organization.

   
   

SP 2.3-1 Assess Training Effectiveness

Assess the effectiveness of the organization's training program.

A process should exist to determine the effectiveness of training (i.e., how well the training is meeting the organization's needs).

Examples of methods used to assess training effectiveness include the following: Testing in the training context Post-training surveys of training participants Surveys of managers' satisfaction with post-training effects Assessment mechanisms embedded in courseware

Measures may be taken to assess the added value of the training against both the project's and organization's objectives. Particular attention should be paid to the need for various training methods, such as training teams as integral work units. When used, performance objectives should be shared with course participants, and should be unambiguous, observable, and verifiable. The results of the training-effectiveness assessment should be used to revise training materials as described in the Establish Training Capability specific practice.

Typical Work Products

1. Training-effectiveness surveys




2. Training program performance assessments




3. Instructor evaluation forms




4. Training examinations

Subpractices

1. Assess in-progress or completed projects to determine whether staff knowledge is adequate for performing project tasks.




2. Provide a mechanism for assessing the effectiveness of each training course with respect to established organizational, project, or individual learning (or performance) objectives.




3. Obtain student evaluations of how well training activities met their needs.

[ Team LiB ]

14.4 Instance tuning

< Day Day Up >

---

14.4 Instance tuning

Though the approach to these discussions is divided into several areas, performance tuning is an overall strategy. Many times a change to a specific area can affect other areas of the database. For example, tuning the SQL queries and ensuring the query is using bind variables may require that you reduce the size of the shared pool, because there would be more shared queries, which would mean fewer queries to be cached and hence a smaller shared pool. On the other hand, adding an index could improve the query performance, but could reduce the performance of a DML operation, because Oracle has to perform an extra I/O to update this new index.

Under this section, let us look at tuning some of the instance-specific areas in a RAC instance. Once the instance-specific parameters have been tuned, these changes can be applied to the other instances.

14.4.1 Memory management

Each instance uses the memory of the node and operating system in which it resides. The normal practice is to allocate about 80% of the system's memory for use by Oracle. The memory at the O/S level is allocated by setting certain kernel parameters that configure the shared memory and semaphores. Oracle uses certain parameters defined in the initialization file to utilize the memory allocated by the O/S kernel. The following parameters and dynamic views directly or indirectly influence the memory management of an instance.

SHARED_POOL

The shared pool contains the library cache and dictionary cache areas. The library cache is where Oracle will store the shared SQL queries and the dictionary cache contains the data-dictionary-related information. Sizing the SHARED_POOL depends on the SQL queries of the application, how many SQL queries are present and how large each query would be. Normally, a large shared pool may be required when the SQL queries do not use bind variables and instead use hard-coded values or literals while accessing information from the database.

Oracle provides advice on the shared pool size through the V$SHARED_POOL_ADVICE dynamic view. The statistics or advice is only provided if the STATISTICS_LEVEL parameter has been enabled and has been set to TYPICAL.

SQL> show parameter shared 
/

NAME                         TYPE          VALUE
--------------------------   -----------   ----------
hi_shared_memory_address     integer       0
max_shared_servers           integer       20
shared_memory_address        integer       0
shared_pool_reserved_size    big integer   107374182
shared_pool_size             big integer   2147483648
shared_server_sessions       integer       0
shared_servers               integer       0

V$SHARED_POOL_ADVICE

As mentioned above, the shared pool contains SQL statements; sizing of the shared pool is directly related to the quality of these SQL statements. The higher the size of the shared pool, the lower will be number of parses, because if sufficient memory is available then these SQL statements will not be aged out of the cache. The V$SHARED_POOL_ADVICE view provides information about library cache memory and provides additional help by predicting the effect of increasing or decreasing the amount of shared pool.

SELECT
      SHARED_POOL_SIZE_FOR_ESTIMATE SPSE,
      SHARED_POOL_SIZE_FACTOR SPSF, ESTD_LC_SIZE 
      ELS, ESTD_LC_MEMORY_OBJECTS ELMO, 
      ESTD_LC_TIME_SAVED ELTS, 
      ESTD_LC_TIME_SAVED_FACTOR ELTSF, 
      ESTD_LC_MEMORY_OBJECT_HITS ELMOH
FROM V$SHARED_POOL_ADVICE /

SPSE    SPSF  ELS   ELMO    ELTS  ELTSF     ELMOH
----  ------ ---- ------  ------  -----   -------
1216   .5938  422  66163  109519      1   9175821
1424   .6953  422  66163  109519      1   9175821
1632   .7969  422  66163  109519      1   9175821
1840   .8984  422  66163  109519      1   9175821
2048       1  422  66163  109519      1   9175821
2256  1.1016  422  66163  109519      1   9175821
2464  1.2031  422  66163  109519      1   9175821
2672  1.3047  422  66163  109519      1   9175821
2880  1.4063  422  66163  109519      1   9175821
3088  1.5078  422  66163  109519      1   9175821
3296  1.6094  422  66163  109519      1   9175821
SPSE    SPSF  ELS   ELMO    ELTS  ELTSF     ELMOH
----  ------ ---- ------  ------  -----   -------
3504  1.7109  422  66163  109519      1   9175821
3712  1.8125  422  66163  109519      1   9175821
3920  1.9141  422  66163  109519      1   9175821
4128  2.0156  422  66163  109519      1   9175821
15 rows selected.

The value of 1 in the SHARED_POOL_SIZE_FACTOR column indicates the current shared pool size. The rows above this value indicate that the number of parse counts would be significantly high if the value was less than the current SHARED_POOL size and the rows below this number indicate that the parse counts would reduce when this value is increased.

The data dictionary view also reports on the library cache information. Through the ESTD_LC_TIME_SAVED (ELTS) column it provides the amount of parse time that is saved because the library cache had already cached the information. Conversely, this is the amount of time (in seconds) that Oracle would have spent reloading the query into the library cache if the shared pool was not set to the current value.

The query below against the V$DB_OBJECT_CACHE view provides an indication on the number of database objects that have been cached. The output from the query provides the number of various objects by type that is currently in a KEEP state and how many are not. For example, there are four INDEX objects retained and 111 objects not retained. Similarly, none of the cursors has been kept. This view also provides information such as the number of times the object had to be loaded back because of space restrictions.

SELECT
     TYPE, KEPT,
     COUNT (*),
     SUM (SHARABLE_MEM) 
FROM V$DB_OBJECT_CACHE 
GROUP BY 
TYPE,
    KEPT
/

TYPE       KEP   COUNT(*)  SUM(SHARABLE_MEM)
---------- ----  --------  -----------------
TYPE       NO         288            4768575
VIEW       NO         434            1103864
INDEX      NO         111             657241
INDEX      YES          4               5066
TABLE      NO         387            1467592
TABLE      YES         21              53696
CURSOR     NO        9120          123875555
CLUSTER    NO           4               8131
CLUSTER    YES          6              12741
LIBRARY    NO           8              10378
PACKAGE    NO          40            1213688

TYPE       KEP   COUNT(*)  SUM(SHARABLE_MEM)
---------- ----  --------   -----------------
PACKAGE    YES          2             483702
PUB_SUB    NO          11              16950
SYNONYM    NO         350            1631810
TRIGGER    NO          49             271407
FUNCTION   NO          62            1101355
OPERATOR   NO           2               6762
SEQUENCE   NO          21              54017
PROCEDURE  NO           1              20480

V$LIBRARY_CACHE_MEMORY

Continuing with the options of tuning the shared pool, the V$LIBRARY_CACHE_MEMORY dynamic view provides the amount of memory allocated to the library cache memory objects in different namespaces.

SELECT
      LC_NAMESPACE NAMESPACE,
      LC_INUSE_MEMORY_OBJECTS USED_BY_OBJ,
      LC_INUSE_MEMORY_SIZE USED_SIZE,
      LC_FREEABLE_MEMORY_OBJECTS AVL_FOR_OBJ,
      LC_FREEABLE_MEMORY_SIZE FREE_SIZE
FROM V$LIBRARY_CACHE_MEMORY
/

NAMESPACE        USED_BY_OBJ USED_SIZE AVL_FOR_OBJ FREE_SIZE
---------------- ----------- --------- ----------- ---------
BODY                       6         0          76         1
CLUSTER                   13         0           8         0
INDEX                     14         0         220         0
JAVA DATA                  0         0           0         0
JAVA RESOURCE              0         0           0         0
JAVA SOURCE                0         0           0         0
OBJECT                     0         0           0         0
OTHER/SYSTEM               0         0          22         0
PIPE                       0         0           0         0
SQL AREA                  31         0       12803       105
TABLE/PROCEDURE          139         0        4197        16

NAMESPACE        USED_BY_OBJ USED_SIZE AVL_FOR_OBJ FREE_SIZE
---------------- ----------- --------- ----------- ---------
TRIGGER                   46         0         152         0
12 rows selected.

V$LIBRARYCACHE

The query below will provide statistics that will help determine the performance of the library cache. Two of the key columns that should be monitored are RELOADS and INVALIDATIONS. RELOADS indicates the number of times the objects had to be reloaded into the library cache because they were not kept in memory. The reasons for this would be because of objects aging out of the library cache, objects not being used for a long time, or being flushed out because there was insufficient memory. Since RELOADS also contains the count for the load that occurred the very first time before the object was pinned, it should be observed if this value is growing. If the reloads were due to objects being flushed out, increasing the shared pool would be a potential option.

INVALIDATIONS occur when the objects become invalid because the underlying dependent objects may have been modified or may have changed the status to invalid. Invalidations of objects cause the objects to be reparsed after the underlying dependent objects have become valid again.

COL PINS FORMAT 99999999
COL RELOADS FORMAT 9999999
COL GETHITS FORMAT 9999999
COL PINHITS FORMAT 999999999
COL INVAL FORMAT 9999999
SELECT NAMESPACE,
       GETS,
       GETHITS,
       PINS,
       PINHITS,
       RELOADS,
       INVALIDATIONS INVAL
FROM V$LIBRARYCACHE
/

NAMESPACE           GETS  GETHITS     PINS  PINHITS RELOADS INVAL
---------------  ------- -------- -------- -------- ------- -----
SQL AREA          399001   382726 20997285 20972663    1084  8154
TABLE/PROCEDURE  7381256  7373683 10108219 10087081    4937     0
BODY               26869    26823    26869    26815       8     0
TRIGGER          1021324  1021012  1021591  1020945      27     0
INDEX             104208   103909    54768    54408       8     0
CLUSTER             6235     6221     5365     5343       0     0
OBJECT                 0        0        0        0       0     0
PIPE                   0        0        0        0       0     0
JAVA SOURCE            0        0        0        0       0     0
JAVA RESOURCE          0        0        0        0       0     0
JAVA DATA              0        0        0        0       0     0
11 rows selected.

DB_CACHE_SIZE

DB_CACHE_SIZE is a new parameter introduced in Version 9i that replaces the DB_BLOCK_BUFFERS parameter and defines the default buffer pool size. In situations where multiple block sizes are defined, normally the default cache size corresponds to the default database block size.

For every block size defined, a corresponding DB_CACHE_SIZE is to be created. This is to ensure that the data retrieved using a specific block size has a corresponding memory area and all rows are of the same block size.

Similar to the advice available for the shared pool, Oracle also provides advice for the DB_CACHE_SIZE. In order to enable the advice feature, the following two parameters should be enabled, the DB_CACHE_ADVICE parameter should be set to ON and the STATISTICS_LEVEL should be set to TYPICAL.

DB_CACHE_ADVICE

Turning this parameter ON enables statistics collection for the different cache sizes and Oracle populates the V$DB_CACHE_ADVICE view. This parameter is automatically enabled when the STATISTICS_LEVEL is set to TYPICAL or ALL.

V$DB_CACHE_ADVICE

Below is a query^[3] that provides information from this view. Similar to V$SHARED_POOL_ADVICE, this query also provides advice on the optimal sizing of the DB_CACHE_SIZE parameter. In the output below, where the column EST_PHYSICAL_READ_FACTOR has a value of 1, the DB_CACHE_SIZE has been set to 512 MB. At this value the number of the estimated number of physical reads is 2,266,628. Setting this value lower than the current value would increase the number of physical reads. Increasing the value of DB_CACHE_SIZE to say 720 MB would reduce the physical reads, though not significantly, as indicated below.

column size_for_estimate       format 999,999,999 heading
                                'Cache Size (m)'
column buffers_for_estimate    format 999,999,999 heading 'Buffers'
column estd_physical_          format 999.90 heading
 read_factor                    'Estd Phys|Read Factor'
column estd_physical_reads     format 999,999,999,999 heading
Phys|Reads'
SELECT    
      SIZE_FOR_ESTIMATE,    
      BUFFERS_FOR_ESTIMATE,    
      ESTD_PHYSICAL_READ_FACTOR,    
      ESTD_PHYSICAL_READS    
FROM V$DB_CACHE_ADVICE
WHERE NAME='DEFAULT' AND
       BLOCK_SIZE =
         ( SELECT VALUE
         FROM V$PARAMETER
         WHERE NAME ='DB_BLOCK_SIZE' )
AND ADVICE_STATUS ='ON'
/

   Cache              Estd Phys     Estd Phys
Size (m)   Buffers  Read Factor         Reads
--------  --------  -----------  ------------
      48     5,739        89.43   202,695,043
      96    11,478        84.00   190,395,258
     144    17,217        80.78   183,093,708
     192    22,956        77.80   176,354,748
     240    28,695        75.91   172,069,835
     288    34,434        51.73   117,244,628
     336    40,173        10.25    23,238,498
     384    45,912         4.77    10,812,383
     432    51,651         1.81     4,100,150
     480    57,390         1.21     2,732,271

   Cache              Estd Phys     Estd Phys
Size (m)   Buffers  Read Factor         Reads
--------  --------  -----------  ------------
    512     61,216         1.00     2,266,628 
    528     63,129          .90     2,040,032
    576     68,868          .70     1,586,507
    624     74,607          .62     1,409,380
    672     80,346          .56     1,268,110
    720     86,085          .53     1,195,732
    768     91,824          .51     1,145,432
    816     97,563          .50     1,135,638
    864    103,302          .50     1,133,148
    912    109,041          .50     1,132,152

   Cache              Estd Phys     Estd Phys
Size (m)   Buffers  Read Factor         Reads
--------  --------  -----------  ------------ 
    960    114,780          .50     1,132,152
21 rows selected.

DB_KEEP_CACHE_SIZE

This parameter is used to specify the size of the keep buffer pool. Oracle does not support multiple cache sizes for the keep cache. DB_KEEP_CACHE_SIZE is based on the default block size. If a value of this parameter is not specified in the parameter, Oracle assigns a value of ZERO. This parameter replaces the BUFFER_POOL_KEEP parameter.

DB_RECYCLE_CACHE_SIZE

Similar to the DB_KEEP_CACHE_SIZE parameter, this parameter is used to specify the size of the recycle buffer pool. DB_RECYCLE_CACHE_SIZE is also based on the default block size. Multiple block sizes do not change the size of this parameter. This parameter replaces the BUFFER_POOL_RECYCLE parameter.

V$BUFFER_POOL

This view and the query below provide information about all buffer pools available for the instance. The output indicates that the instance is currently only configured with a single DB_CACHE_SIZE or buffer pool.

SELECT
      NAME,
      BLOCK_SIZE,
      BUFFERS,
      SET_COUNT,
      LO_SETID,
      HI_SETID
FROM V$BUFFER_POOL;

NAME    BLOCK_SIZE  BUFFERS  SET_COUNT  LO_SETID HI_SETID
----    ---------- --------  --------- --------- --------
DEFAULT       8192   122432          2         5        6

V$SGA_DYNAMIC_COMPONENTS

This view and the query below display information about the last 100 completed SGA resize operations. However, this does not include any work-in-progress operations.

SELECT
    COMPONENT,
    CURRENT_SIZE,
    OPER_COUNT,
    GRANULE_SIZE
FROM V$SGA_DYNAMIC_COMPONENTS;

COMPONENT     CURRENT_SIZE  OPER_COUNT  GRANULE_SIZE
------------  ------------  ----------  ------------
shared pool     2147483648           0      16777216
large pool       117440512           0      16777216
buffer cache    1073741824           0      16777216

PGA_AGGREGATE_TARGET

This parameter replaces the SORT_AREA and SORT_AREA_RETAINED_ SIZE parameter available in the previous versions of Oracle. This parameter specifies the target aggregate PGA memory available to all server processes attached to the instance. This parameter enables the automatic setting of the SQL work area used for sort/merge operations during SQL query execution.

Similar to the DB_CACHE_SIZE and the SHARED_POOL parameters, Oracle also provides advice on the PGA_AGGREGATE_TARGET provided the WORKAREA_SIZE_POLICY parameter has been defined.

V$PGASTAT

This view provides the PGA memory usage statistics and is a cumulative value since instance startup. The value from this view provides a great detail of information in sizing the PGA_AGGREGATE_TARGET.

COL NAME FORMAT A45 
SELECT * FROM V$PGASTAT;

NAME                                 VALUE  UNIT
--------------------------------- --------- -------
aggregate PGA target parameter    524288000 bytes
aggregate PGA auto target         442920960 bytes
global memory bound                26214400 bytes
total PGA inuse                    32146432 bytes
total PGA allocated                48112640 bytes
maximum PGA allocated              49292288 bytes
total freeable PGA memory           3997696 bytes
PGA memory freed back to OS         1179648 bytes
total PGA used for auto workareas         0 bytes

NAME                                  VALUE  UNIT
---------------------------------     ------ ------
maximum PGA used for auto workareas   376832 bytes
total PGA used for manual workareas        0 bytes
maximum PGA used for manual workareas      0 bytes
over allocation count                      0 bytes
processed                            5899264 bytes
extra bytes read/written                   0 bytes
cache hit percentage                     100 percent
16 rows selected.

V$PGA_TARGET_ADVICE

This dynamic view provides statistics on the impact on the PGA when the PGA_AGGREGATE_TARGET parameter is changed.

SELECT
      PGA_TARGET_FOR_ESTIMATE PTFE,
      PGA_TARGET_FACTOR PTF,
      ADVICE_STATUS ADS,
      BYTES_PROCESSED BP,
      ESTD_EXTRA_BYTES_RW EEBR,
      ESTD_PGA_CACHE_HIT_PERCENTAGE EPCHP,
      ESTD_OVERALLOC_COUNT EOC
FROM V$PGA_TARGET_ADVICE
/

PTFE        PTF  ADS         BP     EEBR EPCHP   EOC
---------- ---- ----  --------- -------- ------ ----
26214400   .125   ON   35991552 30892032    54    10
52428800    .25   ON   35991552  7723008    82     0
104857600    .5   ON   35991552        0   100     0
157286400   .75   ON   35991552        0   100     0
209715200     1   ON   35991552        0   100     0
251658240   1.2   ON   35991552        0   100     0
293601280   1.4   ON   35991552        0   100     0
335544320   1.6   ON   35991552        0   100     0
377487360   1.8   ON   35991552        0   100     0
419430400     2   ON   35991552        0   100     0
629145600     3   ON   35991552        0   100     0
838860800     4   ON   35991552        0   100     0
1258291200    6   ON   35991552        0   100     0
1677721600    8   ON   35991552        0   100     0
14 rows selected.

SGA_MAX_SIZE

Setting this parameter allows dynamic allocation of memory configuration parameters such as the SHARED_POOL, DB_CACHE_SIZE, etc. If this parameter is not set, Oracle calculates this value based on the combined values of the other memory configuration parameters and this becomes the upper bound value of the SGA_MAX_SIZE. In this case, changing the values of the other parameters is not possible.

OPTIMIZER_INDX_COST_ADJ

This parameter affects the cost calculation of an index. If the default value of this parameter is set to 100%, then it causes the optimizer to evaluate index-based access at regular cost. Setting the value of this parameter to 50% computes the cost of index-based access to half the normal access method. If this value is set very low, say around 5%, the optimizer will prefer index scans over full table scans thus improving overall performance of queries.

OPTIMIZER_INDEX_CACHING

Setting this parameter higher than its default value of 0 helps adjust the behavior of cost-based optimizer to favor nested loop joins over in-list iterators.

Setting this parameter to a higher value, say between 90 and 95, makes nested loop joins and in-list iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loop joins over hash or sort-merge joins and to pick indexes using in-list iterators over other indexes or full table scans.

The following query lists all long-running operations on the database. The output from this query indicates a high number of hash joins and sort- merge operations on the database.

COL IID FORMAT 99
COL OPNAME FORMAT A11
COL SOFAR FORMAT 9999999
COL TOTAL FORMAT 9999999
COL UNITS FORMAT A8
COL TR FORMAT 999999
COL ES FORMAT 999999
   
SELECT
      INST_ID IID,
      OPNAME,
      SOFAR,
      TOTALWORK TOTAL,
      UNITS,
      START_TIME,
      TIME_REMAINING TR,
      ELAPSED_SECONDS ES
FROM GV$SESSION_LONGOPS
WHERE TIME_REMAINING> 0;
/
INST_ID OPNAME       SOFAR    TW    UNITS   START_TIME              TR    ES
------- -----------  -----  ----  -------   --------------------  ----  ----
      2 Hash Join      915   945   Blocks   04-DEC-2002 14:58:07    43  1299
      2 Hash Join      885   945   Blocks   04-DEC-2002 14:59:58    82  1203
      2 Hash Join      930   945   Blocks   04-DEC-2002 15:00:17    19  1190
      2 Hash Join      810   945   Blocks   04-DEC-2002 15:00:20   193  1159
      2 Hash Join      915   945   Blocks   04-DEC-2002 15:00:57    38  1144
      2 Hash Join      810   945   Blocks   04-DEC-2002 15:02:38   170  1018
      1 Sort Output    157  2047   Blocks   04-DEC-2002 15:13:24  5008    41
      1 Sort Output    436  2047   Blocks   04-DEC-2002 15:12:28  1696   459
      1 Sort Output    162  2047   Blocks   04-DEC-2002 15:12:56  4945   425
      1 Sort Output    157  2047   Blocks   04-DEC-2002 15:13:24  5044   419
      1 Sort Output    148  2047   Blocks   04-DEC-2002 15:13:39  4978   388
      1 Sort Output    148  2047   Blocks   04-DEC-2002 15:13:48  4991   389
      2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:23  8879  1627
      2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:23  8896  1630
      2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:38  8983  1646
      2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:41  8999  1649
      2 Sort Output    317  2047   Blocks   04-DEC-2002 15:22:50  8983  1646

Setting OPTIMIZER_INDX_COST_ADJ to 5 and OPTIMIZER_INDEX_CACHING to 95 changes the optimizer behavior, and the new execution plans generated by Oracle for the same query eliminates these high latencies during query operations.

SELECT
      OPNAME,
      SOFAR,
      START_TIME,
      TIME_REMAINING,
      ELAPSED_SECONDS
FROM V$SESSION_LONGOPS;
/

OPNAME             SOFAR START_TIME           TIME_REMAINING ELAPSED_SECONDS
---------------- ------- -------------------- -------------- ---------------
Instance Recove  1638400 06-DEC-2002 17:03:43              0              31
Instance Recove        1 06-DEC-2002 17:03:43              0              39
Sort Output         2444 06-DEC-2002 17:08:34              0              12
Sort Output         2444 06-DEC-2002 17:08:34              0              12

The GV$SESSION_LONGOPS view provides visibility to the time taken to execute long-running operations under Oracle, providing the estimated total time and the time taken so far. The output from this query helps determine what kind of operations in the system take a long time to complete.

14.4.2 Log management

All the files and tablespaces, with the exception of redo log files, undo tablespaces, and archive log files, are shared across all instances. Redo log files, undo tablespaces, and archive log files store instance- specific information and hence there are multiple copies of these files, actually a set per instance. Having stated this, tuning of these files and their corresponding parameter falls under the category of tuning an instance.

In a RAC implementation that uses raw partitions to store data files, redo log files are also placed on these shared raw partitions. All tuning parameters and conditions also apply to tuning redo log files on a RAC instance.

Tuning this area of the instance has a corresponding effect on tuning the recovery management of the instance. This is because the redo log files, undo tablespaces, and archive log files play an important part in recovery management of both the instance and the database during instance failure or database failure.

Some of the parameters and dynamic views that help in tuning the log and recovery management of an instance will be discussed below.

FAST_START_MTTR_TARGET

This parameter is new in Oracle 9i and replaces the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters available in the previous versions of Oracle. This parameter enables specification of the number of seconds that the database should take to perform crash recovery of a single instance.

It allows the administrator to establish a distance between LGWR and the checkpoint position based on the estimated time to apply redo information (in seconds). After instance startup, Oracle 9i will use estimates of redo read and data block read/write to determine how best to reach the FAST_START_MTTR_TARGET and translate that into dynamic settings for FAST_START_IO_TARGET and LOG_CHECKPOINT_ INTERVAL. As the instance remains active/up, and actual read/write time statistics accumulate and the initial ''best guess'' estimates are replaced by real data. The dynamic settings for LOG_CHECKPOINT_ INTERVAL and FAST_START_IO_TARGET adapt to reflect changes in the average I/O times. This effectively makes instance recovery time self-tuning and responsive to changes in the operating environment.

The maximum value for FAST_START_MTTR_TARGET is 3600 (1 hour). When values are set that exceed this value, they are rounded down. There is no predefined minimum value; however, when values are set too low it may not be possible to achieve the required MTTR target, as this is limited by the low limit of the target number of dirty buffers, which is 1000. Added to this value is the time taken to mount the database.

If the value is set too low, then the effective MTTR target will be the best MTTR target the system can achieve. If the value is set high, the effective MTTR is estimated based on the whole buffer cache being dirty. The ESTIMATED_MTTR column in the V$INSTANCE_RECOVERY view can be used to view the effective MTTR. If the parameter setting, shown by the TARGET_MTTR column, is consistently different to the effective MTTR it should be adjusted, since this means it is set at an unrealistic value.

If FAST_START_MTTR_TARGET is set to a low value, then the database will be writing to disk very frequently and this could easily result in system slowdown. The parameter should therefore be adjusted such that the system is not doing excessive checkpoints but, at the same time, the recovery time is still within acceptable limits.

The output below is an extract from the alert log file and indicates that the FAST_START_MTTR_TARGET has been set to 100 and has enabled LOG_CHECKPOINT_INTERVAL:

Wed Dec 25 11:17:57 2002
ALTER SYSTEM SET fast_start_mttr_target= 100 
SCOPE= MEMORY; 
Wed Dec 25 11:18:26 2002
LOG_CHECKPOINT_INTERVAL was set when MTTR advisory was switched on.
Wed Dec 25 11:20:37 2002

V$INSTANCE_RECOVERY

This view provides estimates of the amount of time it would take for a recovery to complete. The two columns ESTIMATED_MTTR and TARGET_MTTR provide an insight as to the actual amount of time it would take for crash recovery to complete. While the TARGET_MTTR is the value defined by the parameter FAST_START_MTTR_TARGET, the ESTIMATED_MTTR is the actual value based on the number of redo blocks being generated.

For example, an update operation to update one column of a 605,000 row table increased the ESTIMATED_MTTR value from 77 to 112, showing that it requires more MTTR to recover compared to the actual value specified by the FAST_START_MTTR_TARGET parameter. In order to maintain this recovery time, Oracle performs more frequent checkpoints that force writing of changes to the redo log files.

SELECT
      RECOVERY_ESTIMATED_IOS RECEIOS,
      ACTUAL_REDO_BLKS,
      TARGET_REDO_BLKS,
      TARGET_MTTR TMTTR,
      ESTIMATED_MTTR EMTTR,
      CKPT_BLOCK_WRITES CKPTBLKWRITS
FROM
V$INSTANCE_RECOVERY
/

   RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS
---------- ----------------- ---------------- ------- ------- ------------
      4836             77860           200000     100      77            0
SQL> /

   RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS
---------- ----------------- ---------------- ------- ------- ------------
     11220            221258           200000     100      96        35193
SQL> /

   RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS
---------- ----------------- ---------------- ------- ------- ------------
     12081            200023           200000     100     101        40762
SQL> /

   RECEIOS  ACTUAL_REDO_BLKS TARGET_REDO_BLKS   TMTTR   EMTTR CKPTBLKWRITS
---------- ----------------- ---------------- ------- ------- ------------
     12118            185539           200000     100     112        43465

While MTTR is important to maintain service level agreements, setting the FAST_START_MTTR_TARGET to a small value could hinder performance due to frequent checkpoints.

V$MTTR_TARGET_ADVICE

Oracle also provides another dynamic view for tuning the MTTR value. This view is populated only if the FAST_START_MTTR_TARGET parameter is enabled. This advisor contains estimates on the number of physical I/Os for the MTTR corresponding to each row.

Like all other advisors, this advisor also provides estimates for various values of the MTTR_TARGET. For example, setting the FAST_START_MTTR_TARGET value to 150 would support a dirty buffer limit of 28,298 blocks, compared to 9875 blocks if the parameter were set to 100.

SELECT
      MTTR_TARGET_FOR_ESTIMATE MTE,
      DIRTY_LIMIT DL,
      ESTD_CACHE_WRITES ECW,
      ESTD_TOTAL_WRITES ETW,
      ESTD_TOTAL_IOS ETI
FROM
V$MTTR_TARGET_ADVICE
/
SQL>/
MTE        DL    ECW    ETW     ETI
------ ------ ------ ------ -------
 76      1000 103686 103686  126144
100      9875  91355  91355  113813
150     28298  91355  91355  113813
200     46721  91355  91355  113813

14.4.3 Undo management

Under Oracle 9i, with the new undo management architecture, every transaction that starts is allocated an undo segment of its own. If there are no undo segments available, a new undo segment is created automatically. However, if there is no space available for a new undo segment to be created, then Oracle assigns the transaction to a segment that is currently in use by another transaction.

When an instance is started, 10 undo segments are created; subsequently, for every five sessions, Oracle creates one undo segment. As mentioned earlier, when a new transaction is unable to identify an undo segment, it creates one. This way, on busy systems that started with 10 undo segments, this number of segments would grow very large, depending on the number of concurrent transactions and the duration of these transactions. A transaction retains an undo segment through the life of the transaction.

After undo segments are created, and as the database warms up and more and more users and transactions get started, a shutdown of the instance will reduce the number of undo segments back to the original 10. Subsequently, when the instance is started, only 10 segments are available and Oracle once again starts building the undo segments.

This is an expensive operation and shutting down instances frequently is discouraged. Since undo segments are automatically created, there is no significant maintenance involved. Undo segments could be monitored using the following dynamic performance views.

V$UNDOSTAT

This dynamic view helps in the determination of the amount of undo space required for the current workload. The values in the underlying fixed table (X$KTUSMST) are used by Oracle for automatic undo management. The view and the underlying X$ table retain the data spanning a 7-day cycle storing a maximum of 1008 rows. The view provides information such as the query length, transaction concurrency, and the amount of space consumed.

COL QUERYLEN FORMAT 9999999
COL STEALCNT FORMAT 9999999
SELECT
      END_TIME,
      UNDOBLKS,
      TXNCOUNT,
      MAXQUERYLEN QUERYLEN,
      MAXCONCURRENCY TXNCONCURRENCY,
      EXPSTEALCNT STEALCNT
FROM V$UNDOSTAT;

END_TIME             UNDOBLKS TXNCOUNT QUERYLEN TXNCONCURRENCY STEALCNT
-------------------- -------- -------- -------- -------------- --------
19-DEC-2002 05:40:35        0        0       0         0             0
19-DEC-2002 04:00:35        5   558434     236        10             0
19-DEC-2002 03:50:35       15   558380     227         5             0
19-DEC-2002 03:40:35       16   558309     226        11             0
19-DEC-2002 02:10:35      164   555686     231         2             0
19-DEC-2002 02:00:35       26   554496     227         2             0
19-DEC-2002 01:50:35      152   554381     225         1             0
19-DEC-2002 01:40:35        1   554196     224         1             0
19-DEC-2002 01:30:35        1   554185     225         1             0
19-DEC-2002 01:20:35       11   554172     228         1             0
19-DEC-2002 01:10:35        3   554085     224         1             0
19-DEC-2002 01:00:35       13   554048     228         1             0
19-DEC-2002 00:50:35       13   553953     228         1             0
19-DEC-2002 00:40:35        8   553870     225         1             0

END_TIME             UNDOBLKS TXNCOUNT QUERYLEN TXNCONCURRENCY STEALCNT
-------------------- -------- -------- -------- -------------- --------
19-DEC-2002 00:30:35       24   553817      225             1         0
19-DEC-2002 00:20:35        2   553697      228             1         0

DBA_UNDO_EXTENTS

The data in this view provides details on the number of extents for the undo segments across all the undo tablespaces. Please note that in the clustered database each instance is assigned an undo tablespace and this view reports on all undo tablespaces.

The view provides information on the currently active segments, blocks allocated to these active unexpired segments, and the tablespaces they belong to.

COL TABLESPACE_NAME FORMAT A20
COL SEGMENT_NAME FORMAT A20
SELECT
      SEGMENT_NAME,
      TABLESPACE_NAME,
      BLOCKS,
      STATUS
FROM DBA_UNDO_EXTENTS
/

SEGMENT_NAME TABLESPACE_NAME BLOCKS     STATUS
------------ --------------- ------  ---------
  _SYSSMU10$       UNDO_RAC1      8    EXPIRED
  _SYSSMU10$       UNDO_RAC1    128  UNEXPIRED
  _SYSSMU11$       UNDO_RAC2      7    EXPIRED
  _SYSSMU11$       UNDO_RAC2      8    EXPIRED
  _SYSSMU11$       UNDO_RAC2    128  UNEXPIRED
  _SYSSMU12$       UNDO_RAC2      7    EXPIRED
  _SYSSMU12$       UNDO_RAC2      8    EXPIRED
  _SYSSMU13$       UNDO_RAC2      7    EXPIRED
  _SYSSMU13$       UNDO_RAC2      8    EXPIRED
  _SYSSMU14$       UNDO_RAC2      7    EXPIRED
  _SYSSMU14$       UNDO_RAC2      8    EXPIRED

SEGMENT_NAME TABLESPACE_NAME BLOCKS     STATUS
------------ --------------- ------  ---------
  _SYSSMU20$       UNDO_RAC2      8    EXPIRED

V$ROLLNAME

This view acts as a cross-reference between the undo segment number and the actual undo segment.

SELECT USN,NAME FROM V$ROLLNAME;
USN  NAME
---  -----------
  0  SYSTEM
 11  _SYSSMU11$
 12  _SYSSMU12$
 13  _SYSSMU13$
 14  _SYSSMU14$ 
 15  _SYSSMU15$
 16  _SYSSMU16$
 17  _SYSSMU17$
 18  _SYSSMU18$
 19  _SYSSMU19$
 20  _SYSSMU20$

UNDO$

In its internal tables, Oracle maintains various status values for the undo segments, as it passes through various stages of its operation. The status column has the following values:

1. 
   

   Invalid

   
   


2. 
   

   Defined but not in use

   
   


3. 
   

   Online

   
   


4. 
   

   Offline

   
   


5. 
   

   Needs recovery

   
   


6. 
   

   Partially available

   
   

COL INST# FORMAT 9999
COL NAME FORMAT A15
SELECT
     INST#,
     US#,
     NAME,
     STATUS$,
     XACTSQN,
     UNDOSQN,
     OPTIMAL
FROM UNDO$
/

~INST#  US#       NAME   STATUS$ XACTSQN UNDOSQN OPTIMAL
--------------------------------------------------------
     0    0     SYSTEM         3       0       0
     1    1  _SYSSMU1$         3   15046    3235
     1    2  _SYSSMU2$         3   17047    3399
     1    3  _SYSSMU3$         3   13821    2866
     1    4  _SYSSMU4$         3   13653    3803
     1    5  _SYSSMU5$         3   14259    2741
     1    6  _SYSSMU6$         3   14222    2781
     1    7  _SYSSMU7$         3   15171    3050
     1    8  _SYSSMU8$         3   13846    2446

~INST#  US#     NAME     STATUS$ XACTSQN UNDOSQN OPTIMAL
-----------------------------------------------------
     1   33  _SYSSMU33$        1    2665     590
     1   34   SYSSMU34$        1    2725     600
     1   35   SYSSMU35$        1    1879     524

~INST#  US#     NAME     STATUS$ XACTSQN UNDOSQN OPTIMAL
-----------------------------------------------------
     2   36   SYSSMU36$        1    1867     474
     2   77   SYSSMU77$        1     247      67
     2   78   SYSSMU78$        1     253      72
     2   79   SYSSMU79$        1     265      75
     2   80   SYSSMU80$        1     273      72
     2   81   SYSSMU81$        1     264      74
     2   82   SYSSMU82$        1     240      68
     2   83   SYSSMU83$        1     237      64
     2   84   SYSSMU84$        1     215      59

Undo and rollback activity is also reported by the STATSPACK reports:

Rollback Segment Stats for DB: PRODDB instance: RAC1 Snaps:

       Trans Table   Pct Undo Bytes
RBS No        Gets Waits Written    Wraps Shrinks Extends
------ ----------- ---------------- ----- ------- -------
     0        65.0  0.00      1,638     0       0       0
     1     7,735.0  0.00  1,465,096     3       1       1
     2     6,850.0  0.03  1,246,388     6       2       2
     3     7,566.0  0.01  1,486,626    30       2       8
     4     7,225.0  0.03  1,501,720    31       3       8
     5     8,098.0  0.00  1,310,916     3       1       1
     6     7,426.0  0.01  1,797,526     4       1       0
     7     6,906.0  0.00  1,135,420    21       1       1
     8     6,813.0  0.01  1,220,012     5       2       2
     9     8,093.0  0.01  1,633,094     4       1       1
    10     7,986.0  0.00  1,406,398     5       1       1
------  ---------- ----- ---------- ------ ------ -------

In the output above, a high value for ''Pct Waits'' suggests more rollback segments may be required. RBS stats may not be accurate between begin and end snaps when using auto undo management, as RBS may be dynamically created and dropped as needed.

^[3]This query has been taken from the Oracle PerformanceTuning documentation Version 9i Release 2.

Section 12.34. Time for the absolute test drive

12.34. Time for the absolute test drive Make sure you've saved the new CSS and then reload "index.html" in your browser. Let's check out the results: Wow, this looks amazingly like the float version; however, you know that the sidebar is being positioned absolutely. As you resize the browser, the sidebar always sits 128 pixels from the top, and sticks to the right of the page. The main content area has a margin that is exactly the width of the sidebar, and the sidebar sits on top of that space. And the sidebar has a 10 pixel right margin, so it has spacing between it and the edge of the page. And we've still got a nice gutter between the two columns. But we are now back to having a problem with the footer. When the browser gets wide enough, the absolutely positioned sidebar comes down over the top of the footer. Unfortunately, we can't fall back on the clear property this time, because flowed elements ignore the presence of absolutely positioned elements. When the browser is wide, the vertical space of the main content area is reduced, and the sidebar can come down over the footer.

Section 1.10.  The Different Roots of Commercial Software

1.10. The Different Roots of Commercial Software Commercial software comes into existence in a completely different way. At the core of the creative process for commercial software is a vision that many people will pay for the software being created. For open source software, the intended audience and the developer are usually the same. So there is no mystery about what the audience wants. The requirements process consists of developers deciding among themselves what they want the software to do. Commercial software companies must somehow determine what the intended customers want. This introduces a large amount of risk into the process, because determining requirements means making lots of assumptions about whom the audience is. The economics of commercial software resemble those of a health club. Customers pay to join the club, because they can get access to a much better facility than if they built it themselves. But the club has to have the exercise machines the customers want. In commercial software, costs are shared across all customers and the company must create software that is powerful and configurable enough to solve business problems in all sorts of different environments. Figuring out the needs of the market is a key skill for a commercial software company, and many people are involved in the process. Investors in the company, or the product marketing department, might conduct research to understand what customers want. Prototypes might be built and put in front of the target audience. The problem for most commercial software companies is that it is fiendishly difficult to tell if they are getting it right. The ultimate test is if the software sells, and the sales staff frequently plays a key role in requirements gathering. But even if they get the first version right, the same issues of what the customers want must be revisited with every new version. 1.10.1. The Commercial Software Life Cycle What generally happens with a successful software company is that the first version of the product is released, a few initial sales are made, and these customers start providing more and more information about what they like about the product. 1.10.1.1 Requirements gathering and the product roadmap At most software companies the product management department keeps the list of potential features to be added to the product and crafts the definition of what each version will include. The product marketing department focuses on understanding what customers need (inbound requirements gathering) and then sending the message about why the product is of value to customers (outbound marketing). The challenge in this process is that 10 or 20 segments of customers and potential customers might be providing information. The potential feature list is gathered from the developers, sales staff, product management, and product marketing. In most cases, even before the first release, the feature list contains years of work and hundreds of potential features. Each customer has his own opinion about what is most important. The process of deciding what to do next involves several factors, among them balancing the features desired by the customers currently buying the software; adding features that might be attractive to new buyers; adding features that allow integration with other software in the marketplace; and adding features desired by other software companies that are using the software as part of their product or helping sell the software in combination with their product. In performing this balancing act, a company must ensure backward compatibility, which means that new features must not break old ones or force customers to redo work to configure or customize the product. One way companies communicate their decisions to potential customers is through a product roadmap, which shows which features are coming along in the next version and what can be planned for future versions. Notice how different this is from the open source requirements process. In the commercial process a group of peopleengineers, product managers, product marketers, sales staff, senior managersis trying to figure out what another group of peoplecustomers and partnerswant from the software. In the open source process there is only one group, the open source community. The community decides which features to include in each release of an open source project. And the developers decide what each individual feature should do based on their understanding of the need. The odd shape of the commercial software feature set is caused by that attempt to balance the perceived needs of the current and future customer base, requests from important customers, recommendations from the sales staff, and features announced by competing companies. Open source features sometimes have an odd shape, because the competing and conflicting needs of developers are being balanced in a strange compromise. 1.10.1.2 Productization One of the most challenging aspects of creating commercial software is taking a program that provides certain features and functionality, and turning it into a product. Many promising software companies fail, because they underestimate the difficulty and importance of this step. Productization means making software work for the general case and making it as easy as possible to use. For a custom program written by an IT department, it might be fine to have an XML properties file that controls the program. For a commercial product, users will probably expect a simple administrative interface to help set the parameters. For an open source product, installation might mean unzipping the source code, compiling the program, and then figuring out how to fit it into your production or development environment. Commercial products generally have an installation program that does a lot of this automatically. Productization requires a huge amount of work. It can take double or triple the amount of work it took to complete the original features and turn a program into a product. Here is some of the work that takes place during productization: Creating administrative interfaces Writing installation scripts Testing features Testing on different platforms Performance tuning Runtime monitoring through SNMP Creating engineering documentation Creating end-user documentation Developing adapters to other programs, such as reporting software Developing support for different databases and operating systems Creating graphical configuration tools Developing APIs Developing web services It is not crazy to think of an original development team of 5 requiring a productization team of 50 or more with various specialized skills to complete a product. What happens in most start-up software companies is that the original development team becomes the productization team, which causes two problems. First, the engineering team doesn't like the work of productization and is not good at it. Second, it slows down development of later features. Early in a company's life, highly skilled customers oriented toward innovation will do without many aspects of productization as long as the functionality provided by the software is compelling (these people fall into the innovators category of customers we will discuss when we talk about the Open Source Skills and Risk Tolerance model in Chapter 3). This happens because the companies that are most likely to buy new versions of commercial products are composed of innovators and early adopters, and they have the skills to overcome the lack of productization. But if a company does not learn how to productize its software, it is doomed when it comes time to sell to a broader market. 1.10.1.3 Maintenance and support Once a commercial software product has been released, it is supported with patches and bug fixes as needed. It is also supported by a technical support department that answers questions, and perhaps a training and education staff. Support services can be delivered through online resources such as email, or through discussion forums or telephone support. Customers must usually pay between 15% and 25% of the original licensing fee in annual support costs. The presence of a support team to help solve problems is one of the most popular aspects of commercial software. It is also required, because users don't have access to enough information about how the product works to solve problems on their own. But even if all such information were available down to the source code, IT users of software still want support. Later in this book, we will analyze the emerging category of companies that provide support services for open source software. 1.10.1.4 End-of-life When a commercial product goes into end-of-life, it means it will no longer be improved. Bug fixes and support might be provided for a limited period, but after that, commercial software companies will stop fixing bugs and answering questions about the software. End-of-life happens for many reasons. Perhaps the product did not succeed, or it was superseded by a newer product. Companies can afford to support only a few versions of a software product. If Version 4 is just coming out, Version 1 that was released five years ago is no longer as important to customers or to the company. Version 1 might go into end-of-life so that resources can be focused on recent versions. Sometimes, companies put versions into practical end-of-life by raising support and maintenance fees.