As Published In
Oracle Magazine
January/February 2005

TECHNOLOGY: Inside OCP


More New Features for OCPs

By Aradhana Puri

Concepts and sample questions for Oracle Database 10g OCP upgrade certification

The Oracle Database 10g: New Features for Administrators exam enables Oracle Certified Professionals (OCPs) certified on Oracle9i Database to upgrade their certifications to Oracle Database 10g. This exam is the only one that OCPs certified on Oracle9i Database need to take to upgrade their certification credentials to Oracle Database 10g.

This is the second column addressing the new feature topics that you may encounter when taking the Oracle Database 10g: New Features for Administrators exam.

This column focuses on the Automatic Shared Memory Management and Online Segment Shrink features of Oracle Database and presents sample questions of the type you may encounter when taking the Oracle Database 10g: New Features for Administrators exam. Note that the sample question format has been adjusted for presentation in this article.

Automatic Shared Memory Management

The system global area (SGA) of an Oracle Database instance consists of several memory pools, including the buffer cache, the shared pool, the Java pool, the large pool, and the redo log buffers. As a DBA, you must ensure that sufficient memory is allocated to these pools. Undersizing these pools can lead to poor performance and out-of-memory errors (ORA-4031), and oversizing can waste memory.

Which five memory pools are automatically sized when you are using Automatic Shared Memory Management?

A. Database buffer cache
B. Streams pool
C. Keep buffer cache
D. Java pool
E. Recycle buffer cache
F. Shared pool
G. Log buffer
H. Large pool

The correct answers are A, B, D, F, and H.

When you configure Automatic Shared Memory Management, five memory pools—the database buffer cache, the streams pool, the Java pool, the shared pool, and the large pool—are automatically sized. The initialization parameters that set the minimum levels for these automatically tuned memory pools are DB_CACHE_SIZE, STREAMS_POOL_SIZE, JAVA_POOL_SIZE, SHARED_POOL_SIZE , and LARGE_POOL_SIZE . The redo log buffer, the keep/recycle buffer caches, fixed SGA, and other internal allocations are referred to as manually sized components.

Which background process does Automatic Shared Memory Management use to coordinate the sizing of memory components?

A. Memory Manager (MMAN)
B. System Monitor (SMON)
C. Memory Monitor (MMON)
D. Memory Monitor Light (MMNL)

The correct answer is A.

The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations. The SGA Memory Broker also observes the system and workload in order to determine the ideal distribution of memory. The SGA Memory Broker checks to ensure that memory can always be present where needed.

You are working in Automatic Shared Memory Management mode, and some of the parameters are set as shown below: 

SGA_MAX_SIZE = 12G
SGA_TARGET = 10
                               g
DB_RECYCLE_CACHE_SIZE = 1G
SHARED_POOL = 2G
                            

Which statement is correct in this scenario?

A. The shared pool will not shrink below 2GB.
B. 12GB of memory will be available for autotuned memory components.
C. The value specified for the shared pool will be ignored, because Automatic Shared Memory Management is enabled.
D. If you increase the value of SGA_TARGET , then it will affect the value of DB_RECYCLE_CACHE_SIZE.

The correct answer is A.

When you specify a nonzero value for SGA_TARGET and do not specify a value for an autotuned SGA parameter, the values of the autotuned SGA parameters in the V$PARAMETER view are 0 and the value of the ISDEFAULT column is TRUE . Automatic Shared Memory Management automatically sizes these components. However, if the autotuned SGA parameters are set to nonzero values, then the specified values are not ignored—rather, they are used as lower limits by the autotuning algorithm. For example, in this case, SHARED_POOL_SIZE is set to 2GB, which tells the Automatic Shared Memory Management algorithm never to shrink the shared pool below 2GB, although larger values are fine.

Online Segment Shrink

It is common for segment space to become fragmented or for a segment to acquire a large amount of free space as a result of update and delete operations. The resulting segments are sparsely populated and can suffer from performance degradation during queries and data manipulation language (DML) operations.

In Oracle9i and earlier database versions, you can reclaim the free space by dropping the table, re-creating it, and then reloading the data or by moving the table to a different tablespace, using the ALTER TABLE MOVE command. Both of these processes must occur with the table offline. Alternatively, you can use online table reorganization, but that requires at least double the space of the existing table.

With Oracle Database 10g, you can now shrink segments. The process of shrinking a segment includes the following phases:

  • Segment data is compacted.

  • High-water mark (HWM) is pushed down.

  • Unused space is released back to the tablespace containing the segment.

Which two statements are correct regarding the segment shrink operation in Oracle Database 10g?

A. You can perform a shrink operation on a table segment only.
B. You need to re-create the indexes after shrinking the corresponding table, because the indexes always become unusable after a shrink operation.
C. A shrink operation is an online, in-place operation and does not need extra database space to be executed.
D. You must enable row movement for heap-organized segments before performing shrink operations on them.

The correct answers are C and D.

Segment shrink is an online, in-place operation. It does not require extra disk space to be allocated. Because a shrink operation may cause ROWIDs to change in heap-organized segments, you must enable row movement on the corresponding segment before executing a shrink operation on that segment.

To shrink segments, use Oracle Enterprise Manager 10g or the SHRINK SPACE clause of the ALTER command.

You need to compact the hr.emp table segment while ensuring that the recovered space is returned to the tablespace in which the segment is stored. Which two actions can you perform?

A. Choose the Compact Segments option in the Shrink Segment: Options page of Oracle Enterprise Manager 10g.
B. Execute the ALTER TABLE hr.emp SHRINK SPACE command.
C. Choose the Compact Segments and Release Space option in the Shrink Segment: Options page of Oracle Enterprise Manager 10g.
D. Execute the ALTER TABLE hr.emp SHRINK SPACE COMPACT command.

The correct answers are B and C.

To shrink a table segment using Oracle Enterprise Manager 10g: from the database home page, click on the Tables link in the Schema section. On the Tables page, select the table and then select Shrink Segment in the Actions field. This brings you to the Shrink Segment: Options page.

You have two shrink options on the Shrink Segment: Options page:

  • Compact the segment and release the free space.

  • Compact the segment and keep the release of free space pending.

When you choose the Compact Segments and Release Space option, the segment space is compacted, the HWM is adjusted, and the free space is returned to the tablespace. Alternatively, you can use the ALTER TABLE <segment_name> SHRINK SPACE command to shrink a table segment. For example, as a result of the following command, the table segment is compacted and all the unused space inside the table is returned to the tablespace: 

ALTER TABLE hr.emp SHRINK SPACE;


The CUSTOMERS table is stored in the SALES_TBS tablespace, which has the following properties:

  • It is read/write and online.

  • It is not autoextensible.

  • Its segment space management is set to manual.


You executed the following command: 

ALTER TABLE customers SHRINK SPACE;


You received the following error:

Next Steps


GET Certified
 Visit oracle.com/education/certification for more information about the Oracle Certification program and to download a free exam candidate guide.

 READ more Inside OCP

 

ORA-10635: Invalid segment or tablespace type


What could be the reason for the error?

A. The SALES_TBS tablespace is read/write and not read-only.
B. The SALES_TBS tablespace is online and not offline.
C. The segment-space management for the SALES_TBS tablespace is manual and not automatic.
D. The SALES_TBS tablespace is not autoextensible to an unlimited size.

The correct answer is C.

Shrink operations can be performed only on segments in tablespaces that use automatic segment-space management. You need not make a tablespace read-only, offline, or autoextensible before shrinking any of the segments in that tablespace.

Conclusion

This column has discussed topics associated with upgrading from an Oracle9i DBA Certified Professional credential to an Oracle 10g DBA Certified Professional credential. Automatic Shared Memory Management significantly simplifies Oracle database administration and helps make more-effective use of available memory. Online segment shrink can improve the performance of full table scans and index access. Also, by shrinking sparsely populated segments, online segment shrink can enhance the efficiency of space utilization inside your database.


Aradhana Puri (ocpexam_ww@oracle.com) is a principal Oracle Certification Exam developer. She has been with Oracle for more than four years.


Send us your comments