New Features for Administrators - Question 016

Which three statements are true about the privileged connection options available in Oracle9i? (Choose three.)

A. The CONNECT INTERNAL is disallowed.

B. The Server Manager tool is no longer supplied.

C. The listener must be running to make a connection.

D. The use of a remote password login file (orapw) is unchanged.

Answer: A, B, C

Explanation:

The CONNECT INTERNAL is disallowed in Oracle9i. The Server Manager tool is no longer supplied also. And the listener must be running to make a connection.

Incorrect Answers

D: The use of a remote password login file (orapw) is changed in Oracle9i.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 5-10

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

New Features for Administrators - Question 015

You can control instance recovery performance by setting parameters to define targets for _______.

A. The size of the redo buffer in the SGA

B. The instance that will perform recovery in a Real Application Clusters database

C. The number of blocks to be read during instance recovery, or the time needed to complete instance recovery

D. The number of passes through the redo logs made by the recovery process to identify blocks needing recovery

Answer: C

Explanation:

Oracle9i added the FAST_START_MTTR_TARGET parameter to define targets for the number of blocks to be read during instance recovery, or the time needed to complete instance recovery. Oracle internally uses the FAST_START_MTTR_TARGET parameter value to calculate and set the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameters. If you specified the FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL parameter values, they will override the values Oracle calculates from the FAST_START_MTTR_TARGET parameter.

Incorrect Answers

A: You cannot control instance recovery performance by setting parameters to define targets for the size of the redo buffer in the SGA

B: It’s not possible to control instance recovery performance for the instance that will perform recovery in a Real Application Clusters database.

D: The number of passes through the redo logs made by the recovery process to identify blocks needing recovery will not help to control instance recovery performance.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 42-45

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-75

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 014

Media recovery can sometimes be stopped by the inability to read past a certain point in the redo stream. This is often referred to as "stuck recovery."

Before Oracle9i, the Database Administrator had few options to deal with stuck recovery. If the corrupt redo could not be recovered from some other source, then all transactions that committed after the corrupt point in the redo stream would be lost.

Oracle9i changes that with the Trial Recovery feature. Trial Recovery is used to test the application of the redo logs to the database.

What are three other characteristics of Trial Recovery? (Choose three.)

A. Speeds up subsequent media recovery actions

B. Writes any uncorrupted blocks to disk during the test recovery

C. Can be invoked by adding the TEST option to any RECOVER command

D. Allows the Administrator to determine how many blocks are affected by corruption

E. Marks blocks as corrupt in memory, allowing the test recovery to proceed to completion

Answer: A, D, E

Explanation:

Trial Recovery feature is used to speed up subsequent media recovery actions. It allows the Administrator to determine how many blocks are affected by corruption, mark them as corrupt in memory to allow the test recovery proceed to completion.

Incorrect Answers

B: It does not write uncorrupted blocks to disk during the test recovery.

C: There is no TEST option in RECOVER command.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 186-187

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 73-86

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 013

The list below displays four steps that you need to execute in order to switch from the primary database role to the standby database role.

Choose the correct order of execution for the steps.

1. Shut down and start up the former primary instance without mounting the instance.

2. Issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY command.

3. Issue the ALTER DATABASE MOUNT STANDBY DATABASE command.

4. End read or update activity on the former primary and standby databases.

A. 1, 2, 3, 4

B. 3, 2, 4, 1

C. 4, 2, 1, 3

D. 4, 3, 2, 1

Answer: C

Explanation:

To switch from the primary database role to the standby database role you need to end to read or update activity on the former primary and standby databases, issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY command, shut down and start up the former primary instance without mounting the instance, issue the ALTER DATABASE MOUNT STANDBY DATABASE command.

Incorrect Answers

A: First you need to end to read or update activity on the former primary and standby databases.

B: You need to issue the ALTER DATABASE MOUNT STANDBY DATABASE command after the former primary database have been switched to standby mode.

D: You need to switch the former primary database to standby mode and restart instance before you mount the former primary instance in standby mode.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 013

The list below displays four steps that you need to execute in order to switch from the primary database role to the standby database role.

Choose the correct order of execution for the steps.

1. Shut down and start up the former primary instance without mounting the instance.

2. Issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY command.

3. Issue the ALTER DATABASE MOUNT STANDBY DATABASE command.

4. End read or update activity on the former primary and standby databases.

A. 1, 2, 3, 4

B. 3, 2, 4, 1

C. 4, 2, 1, 3

D. 4, 3, 2, 1

Answer: C

Explanation:

To switch from the primary database role to the standby database role you need to end to read or update activity on the former primary and standby databases, issue the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY command, shut down and start up the former primary instance without mounting the instance, issue the ALTER DATABASE MOUNT STANDBY DATABASE command.

Incorrect Answers

A: First you need to end to read or update activity on the former primary and standby databases.

B: You need to issue the ALTER DATABASE MOUNT STANDBY DATABASE command after the former primary database have been switched to standby mode.

D: You need to switch the former primary database to standby mode and restart instance before you mount the former primary instance in standby mode.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 64-72

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 86-99

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 012

When does Oracle9i flag an index as being used in the V$OBJECT_USAGE view?

A. During a query's parse time only

B. During a query's execution time only

C. During any kind of DML statement execution

D. During the execution of an INSERT statement only

Answer: A

Explanation:

During a query's parse time Oracle9i flags an index as being used in the V$OBJECT_USAGE view. You can query the V$OBJECT_USAGE view to review the index utilization data. If the index has been used within the period of time it was monitored, the USED column within this view will contain a YES value; it will contain a NO otherwise.

Incorrect Answers

B: During a query's execution time Oracle9i does not flag an index as being used in the V$OBJECT_USAGE view.

C: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during any kind of DML statement execution.

D: Oracle9i flags an index as being used in the V$OBJECT_USAGE view during a query's parse time, not during execution of an INSERT statement.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-57

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 011

Which two are true regarding external tables? (Choose two.)

A. External tables can be updated.

B. External tables are read-only tables.

C. Indexes can be created on external tables.

D. Indexes cannot be created on external tables.

Answer: B, D

Explanation:

External tables are read-only tables whose data resides in an external OS flat file, and whose definition is stored inside the database. Indexes cannot be created on external tables.

Incorrect Answers

A: External tables cannot be updated. They are read-only tables.

C: Indexes cannot be created on external tables.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 131-134

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 111-116

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

New Features for Administrators - Question 010

What is true regarding a transaction executed in Automatic Undo Management mode?

A. It is possible for the transaction to span different undo segments.

B. It is possible for the transaction to reuse inactive extents from another undo segment.

C. It is possible for the transaction to use inactive extents from another undo tablespace.

D. It is possible for the transaction to extend the undo tablespace, even if defined with AUTOEXTENSIBLE set to FALSE.

Answer: B

Explanation:

It is possible for the transaction to reuse inactive extents from another undo segment in Automatic Undo Management mode.

Incorrect Answers

A: It is not possible for the transaction to span different undo segments. It can be assigned only to the one undo segment.

C: It is not possible for the transaction to use inactive extents from another undo tablespace. It can only REUSE inactive extents from another undo segment, not from another undo tablespace.

D: It’s not possible for the transaction to extend the undo tablespace, if defined with AUTOEXTENSIBLE set to FALSE.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 160-166

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 53-54

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 009

If you create your database using Oracle Managed Files (OMF), what is true?

A. You must use Oracle predefined names to identify raw partitions that will hold OMF structures.

B. You must define directories for two online redo log groups and three copies of the control file.

C. If your CREATE DATABASE command fails, any OMF database files that have already been created will be dropped automatically.

D. You must use only OMF data files when adding new tablespaces or data files to your database in the future.

Answer: D

Explanation:

If you created your database using Oracle Managed Files (OMF), you must use only OMF data files when adding new tablespaces or data files to your database in the future.

Incorrect Answers

A: You don’t need to use Oracle predefined names to identify raw partitions that will hold OMF structures.

B: You should define directories for OMF datafiles, redo log files and control files. Oracle9i gives you two new initialization parameters, DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n, to specify the location where Oracle will create and manage OMFs; n can take a value from 1 to 5. So there is no limitation on control files amount to 3 copies.

C: If your CREATE DATABASE command fails, any OMF database files that have already been created will not be dropped automatically. This feature will work for the OMF datafiles only after successful database creation.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 008

You can use Oracle Flashback to look at past activity in your database.

What are two other characteristics of Oracle Flashback? (Choose two.)

A. Oracle Flashback uses undo information to construct consistent data.

B. You can use a cursor opened while using an Oracle Flashback image to perform DML once you deactivate Oracle Flashback in your session.

C. You can only use an Oracle Flashback view of the data if the required records are still in the online redo log files.

D. Oracle Flashback lists the DML that was executed during the period you identify when initiating your session

Answer: A, B

Explanation:

To perform DML operations while using an Oracle Flashback image you can use a cursor. Oracle Flashback uses undo information to construct consistent data. The UNDO_RETENTION parameter must be set and the UNDO tablespace must be large enough so that undo generated during the period we want to query will not be overwritten.

Incorrect Answers

C: You can use an Oracle Flashback view of the data if the required records are in the online redo log files or there is information in the UNDO tablespace about required records.

D: Oracle Flashback does not list the DML that was executed during the period you identify when initiating your session.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 45-47

Chapter 2: Availability Enhancements

Oracle 9i New Features, Robert Freeman, p. 64-68

Chapter 3: New Oracle9i Availability and Recoverability Features

New Features for Administrators - Question 007

QUESTION NO: 7

- 9 -

Automatic Consumer Group Switching is an important new feature of the Database Resource Manager.

Which three plan directive parameters are used to control this feature? (Choose three.)

A. SWITCH_TIME

B. SWITCH_GROUP

C. MAX_SESS_POOL

D. SWITCH_ESTIMATE

Answer: A, B, D

Explanation:

You can specify the maximum estimated time to complete (in seconds) for an operation by assigning a value the MAX_EST_EXEC_TIME parameter; the default value for this parameter is UNLIMITED. Oracle will estimate the time the operation with table will take. It the SWITH_ESTIMATE parameter is set to TRUE, and if Oracle’s estimate is greater than the value specified by MAX_EST_EXEC_TIME, Oracle will return an error and the operation will not be started. If the SWITCH_ESTIMATE parameter is FALSE, Oracle will start the operation even if Oracle’s estimate is greater than the value specified by MAX_EST_EXEC_TIME; Oracle will only switch groups based on other criteria being met. The SWITCH_GROUP parameter specifies the group to which the session will be switched. The SWITCH_TIME parameter specifies the amount of time a session can execute before it must be switched to another group. The default for the SWITCH_GROUP parameter is NULL; the default for the SWITCH_TIME parameter is UNLIMITED.

Incorrect Answers

C: There is no MAX_SESS_POOL directive parameter in Oracle.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 122-129

Chapter 3: Manageability Enhancements

New Features for Administrators - Question 006

What is true about version-enabled tables?

A. The unit of versioning is the schema.

B. It is possible to version enable a table pertaining to SYS.

C. There are as many segments as versions for the same base table.

D. It is possible to create workspaces if there is no version-enabled table in the database.

Answer: D

Explanation:

A workspace is a virtual database facility that enables you to maintain multiple versions of one row of data and it enables each workspace context to reference a different version of this row as compared to another workspace context. It’s possible to create a workspace if there is no version-enabled table in the database.

Incorrect Answers

A: The unit of versioning is the table, not the schema.

B: It is not possible to version enable a table in SYS schema.

C: This statement about the same amount of segments as versions for the same base table is incorrect.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 168-176

Chapter 3: Manageability Enhancements

New Features for Administrators - Question 005

The Dynamic SGA feature allows the SGA to grow and shrink dynamically according to an ALTER SYSTEM command.

This avoids the previous need of shutting down the instance in order to modify the components of the SGA, namely the buffer cache and shared pool components. Which three statements are true for the Dynamic SGA feature? (Choose three.)

A. The maximum granule size is 4 MB.

B. The minimum SGA configuration is three granules.

C. SGA memory is based on granules by SGA components.

D. The size of the SGA components is set by the SGA_MAX_SIZE parameter.

E. The size of the SGA components is limited by the setting of SGA_MAX_SIZE parameter.

Answer: B, C, E

Explanation:

Oracle9i has enhanced the nature of SGA parameters; they are now dynamic. You can change the values of the shared pool and the buffer cache without restarting the database instance. The Oracle9i dynamic SGA concept enables you to take memory from one area of the SGA and allocate it to another area as needed while the database instance is up and running. Additionally, the unit of memory allocation for SGA is a granule in Oracle9i. At a minimum, one granule allocated for the shared pool, one for the buffer cache, and one for the redo log buffer. You can confirm a total amount of granules by checking the value of the column LO_SETID in the V$BUFFER_POOLS view. Oracle9i also introduces SGA_MAX_SIZE, a new static parameter that enables the DBA to start with a smaller SGA and dynamically increase it to the maximum value specified by SGA_MAX_SIZE.

If you do not set SGA_MAX_SIZE or if you set it to a value less than initial SGA size, you cannot increase the SGA size later.

Incorrect Answers

A: The size of a granule is 4 MB if the SGA at startup is less than 128 MB; it will be 16 MB otherwise.

D: The size of the SGA components is not set by the SGA_MAX_SIZE parameter. They are only limited by SGA_MAX_SIZE value. As a DBA, you can dynamically change the value of the parameters DB_CACHE_SIZE, SHARED_POOL_SIZE, and LARGE_POOL_SIZE up to the size of SGA_MAX_SIZE.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 180-182

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 16-17

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 004

Oracle9i extends the cursor sharing functionality with the new value of SIMILAR for the CURSOR_SHARING parameter. With CURSOR_SHARING = SIMILAR, cursors are shared for safe literals only. What is meant by 'safe literals only'?

A. No literal value is substituted for a shared cursor.

B. Different execution plans are generated for substituted literal values.

C. The substitution of a literal value will produce different execution plans.

D. The substitution of any literal value will produce exactly the same execution plan.

Answer: D

Explanation:

Oracle9i has enhanced cursor sharing mode. It can use additional value, SIMILAR, in addition to the EXACT and FORCE cursor sharing modes. When you specify SIMILAR, Oracle only uses the execution plan if is certain that the execution plan does not have any association with the specific literal value. You can enable similar statements to share the same SQL execution plan by setting CURSOR_SHARING to either FORCE or SIMILAR.

Incorrect Answers

- 7 -

A: A Literal value is substituted for a shared cursor. The substitution of any literal value will produce exactly the same execution plan.

B: Exactly the same execution plans will be generated for substituted literal values.

C: The substitution of any literal value will produce exactly the same execution plan.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 215-217

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 57-59

Chapter 2: Oracle9i Architecture Changes

New Features for Administrators - Question 003

What criterion does Oracle9i use to determine whether a database file is an Oracle Managed File?

A. The filename format

B. Information stored inside a data dictionary table

C. Information stored in the ALERT.LOG file for the corresponding instance

D. Information stored inside the corresponding initialization parameter file for the instance

Answer: A

Explanation:

Oracle will use naming conventions when it creates the OMF files. In this naming convention, %t represents the tablespace name, %u is a unique 8-character string, and %g stands for the redo log group number.

Incorrect Answers

B: Oracle will create a locally managed tablespaces, so information about a datafile willnot be stored inside a data dictionary table.

C: Oracle does not use ALERT.LOG file to store information about datafiles. It just keeps a log of database events and database structure changes.

D: Initialization parameter file will not be used to store this information. Oracle reads this file to set initialization parameters to start the instance. The structure of the database is stored inside control files. Oracle uses them to mount and open the database.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 153-160

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 2-12

Chapter 1: Oracle9i Database Administration and Management Features

New Features for Administrators - Question 002

Examine the extract from a SQL*Plus session.

SQL> SELECT partition_name, high_value, tablespace_name
2 FROM user_tab_partitions
3 WHERE table_name = ‘LOCATIONS’;

PARTITION_NAME HIGH_VALUE TABLES
----------- ----------------------------------------------------- -----
REGION_EAST ‘MA’, ‘NY’, ‘CT’, ‘NH’, NULL, ‘MD’, ‘VA’, ‘PA’, ‘NJ’ tbs1
REGION_WEST ‘CA’, ‘AZ’, ‘NM’, ‘OR’, ‘WA’, ‘UT’, ‘NV’, ‘CO’, tbs2
REGION_SOUTH ‘TX’, ‘KY’, ‘TN’, ‘LA’, ‘MS’, ‘AR’, ‘AL’, ‘GA’ tbs3
REGION_CENTRAL ’OH’, ‘ND’, ‘SD’, ‘MO’, ‘IL’, ‘MI’, NULL, ‘IA’ tbs4

SQL> ALTER TABLE locations
2 SPLIT PARTITION region_east
3 VALUES(‘CT’,’MA’,’MD’)
4 INTO
5 (PARTITION region_north_east TABLESPACE tbs2
6 ,PARTITION region_south_east STORAGE (NEXT 2M));

Assuming that there is at least one row for each value shown in the HIGH VALUE column, what is the result of the ALTER TABLE statement?

A. The REGION_SOUTH_EAST partition will be created in the TBS2 tablespace.

B. The statement will fail because no values are supplied for REGION_SOUTH_EAST partition keys.

C. The REGION_SOUTH_EAST partition will contain only rows with a NULL value in the current REGION_EAST partition.

D. The REGION_SOUTH_EAST partition will be created with storage characteristics inherited from the LOCATIONS table.

E. Rows with partitioning keys in the current REGION_EAST partition not included in the VALUES clause will be stored in the REGION_SOUTH_EAST partition.

Answer: E

Explanation:

If you are splitting a partition, the list of values in the VALUES clause applies to the first partition defined. All the remaining states not included in the VALUES clause will be included in a new partition called REGION_SOUTH_EAST.

Incorrect Answers

A: The REGION_SOUTH_EAST partition will stay in the same tablespace where REGION_EAST partition is located. Only the REGION_NORTH_EAST partition will be created in the TBS2 tablespace.

B: The statement will not fail. The REGION_SOUTH_EAST partition will keep all values of REGION_EAST partition not included into the REGION_NORTH_EAST partition.

C: The REGION_SOUTH_EAST partition will keep all values of REGION_EAST partition not included into the REGION_NORTH_EAST partition, but it will not store rows with a NULL value in the current REGION_EAST partition..

D: The REGION_SOUTH_EAST partition will not use the storage characteristics inherited from the LOCATIONS table.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 134-136

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 102-108

Chapter 4: New Oracle9i Database DDS and Data-Warehouse Features

New Features for Administrators - Question 001

Examine the code.

CREATE ROLE readonly IDENTIFIED USING app.chk_readwrite ;

CREATE ROLE readwrite IDENTIFIED USING app.chk_readwrite ;

CREATE OR REPLACE PROCEDURE app.chk_readwrite
AUTHID CURRENT_USER IS
ipchk STRING(30);
BEGIN
IF says context (‘USERENV’, ‘ISDBA’=’TRUE’
THEN DBMS SESSION.SET_ROLE (‘READWRITE’) ;
ELSE DMBS_SESSION_SET_ROLE )’READONLY’) ;
END IF ;
END;
/

Which three statements correctly describe the Secure Application role definition?

(Choose three.)

(The SYS_CONTEXT calls returns whether or not the session is a true DBA session.)

A. No user or application has to remember or hide a password.

B. It prevents everyone except a true DBA session from acquiring the READWRITE role.

C. app.chk_readwrite is called whenever a user tries to access rows protected by the READONLY or READWRITE label.

D. app.chk_readwrite is called by users or applications when they want to enable the READONLY or READWRITE role.

Answer: A, B, D

Explanation:

Oracle8i introduced the Secure Application Context to enable an application to tailor access control based on using the attributes of the user’s session. Oracle prevents users from bypassing privileges validation procedure by restricting application attribute changes to the procedure that implements the context. Oracle9i takes the concept of the application context one step further; it provides a means of associating the application context with the security role.

Incorrect Answers

C: App.chk_readwrite will not be called whenever a user tries to access rows protected by the READONLY or READWRITE label. It will be called only when they want to enable the READONLY or READWRITE role.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 10-14

Chapter 1: Security Enhancements

Oracle 9i New Features, Robert Freeman, p. 132-135

Chapter 5: Miscellaneous Oracle9i Features and Enhancements

Performance Tuning - Question 128

You configured your database to use the Oracle Shared Server, but you did not configure the large pool. Where in memory will Oracle store user information?

A. PGA

B. JAVA POOL

C. LARGE POOL

D. SHARED POOL

E. SHARED SQL AREA

F. SHARED POOL RESERVED AREA

Answer: D

Explanation:

It will be stored in SHARED POOL area if it is not LARGE POOL is configured.

Incorrect Answers

A: PGA will not be used for this purpose.

B: SHARED POOL area will be used to store user information, not JAVA POOL.

C: LARGE POOL is not configured as question states.

E: There is no SHARED SQL AREA in Oracle9i.

F: SHARED POOL area will be used to store user information, not SHARED POOL RESERVED AREA.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 205-208

Chapter 4: Tuning the Shared Pool

Performance Tuning - Question 127

Which script creates views that will tell you who is holding or waiting for which tables?

A. CATPROC.sql

B. CATLOCK.sql

C. CATPERF.sql

D. CATBLOCK.sql

E. CATBPROC.sql

Answer: D

Explanation:

A CATBLOCK.sql script creates the lock views that UTLOCKT.sql script needs, so you must run it before running UTLOCKT.sql.

Incorrect Answers

A: CATPROC.sql script is required to load the initial PL/SQL environment.

B: There is no CATLOCK.sql script in Oracle9i.

C: There is no CATPERF.sql script in Oracle9i.

E: There is no CATBPROC.sql script in Oracle9i.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 468-470

Chapter 9: Tuning Contention

Performance Tuning - Question 126

Which three statements are true with respect to has clusters? (Choose three)

A. Full table scans are generally faster on cluster tables than on non-clustered tables.

B. Hash clusters may be desirable when the number of key values is predictable and key values are evenly distributes.

C. If tables in a hash cluster require more space than the initial allocation for the cluster, performance degradation can be substantial because overflow blocks are required.

D. Storing a single table in a hash cluster can be useful regardless of whether the table is joined frequently with other tables or not, provided other criteria for choosing a hash cluster are met.

Answer: A, B, C

Explanation:

These statements are true with respect to clusters.

Incorrect Answers

D: Storing a single table in a hash cluster cannot be useful in some cases. It’s better to use clusters if table is joined frequently with other tables.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 445-446

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 125

Which two statements are true regarding the use of DB_CACHE_ADVICE init.ora parameter? (Choose two)

A. Setting the parameter to READY reserves space in the buffer cache to store information about different buffer cache sizes, but no CPU overhead is incurred.

B. Setting the parameter to READY reserves space in the shared pool to store information about different buffer cache sizes, but no CPU overhead is incurred.

C. Setting the parameter to ON reserves space in the buffer cache to store information about different buffer cache sizes, and CPU overhead is incurred as statistics are collected.

D. The V$DB_CACHE_ADVICE view contains information that predicts the estimated number of physical reads for different cache sizes for each buffer cache setup in the SGA.

Answer: C, D

Explanation:

Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.

Two minor overheads are associated with this advisory:

• CPU: When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.

• Memory: The advisory requires memory to be allocated from the shared pool (about 100 bytes for each buffer).

The V$DB_CACHE_ADVICE view is populated when the DB_CACHE_ADVICE parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.

Incorrect Answers

A: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY.

B: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY. Also it reserves space in the buffer cache, not the shared pool.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 275-277

Chapter 5: Tuning the Database Buffer Cache

Performance Tuning - Question 124

Which two statements are valid settings for the initialization parameter ‘USE_STORED_OUTLINES’? (Choose two)

A. SQL> alter session set USE_STORED_OUTLINES = TRUE;

B. SQL> alter session set USE_STORED_OUTLINES = PUBLIC;

C. SQL> alter session set USE_STORED_OUTLINES = PRIVATE;

D. SQL> alter session set USE_STORED_OUTLINES = DEFAULT;

E. SQL> alter session set USE_STORED_OUTLINES = ;

F. SQL> alter session set USE_STORED_OUTLINES = ;

Answer: A, F

Explanation:

There are two valid settings for the initialization parameter USE_STORED_OUTLINES: TRUE or category name.

Incorrect Answers

B: This statement is incorrect.

C: This statement is incorrect.

D: This statement is incorrect.

E: This initialization can use category name, not outline name.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 99-101

Chapter 3: SQL Application Tuning and Design

Performance Tuning - Question 123

There are two users, John and Susan, who are updating the EMPLOYEE table in the following order:

1. First, John issues this SQL update:

UPDATE EMPLOYEE SET LAST_NAME = ‘SMITH’ where ID=200;

2. Next, Susan issues this SQL update:

UPDATE EMPLOYEE SET SALARY=50000 WHERE ID=250;

3. Next, John issues this SQL update:

UPDATE EMPLOYEE SET LAST_NAME ‘BAKER’ WHERE ID=250;

4. FINALLY, Susan issues this SQL update:

UPDATE EMPLOYEE SET SALARY=60000 WHERE ID=200;

What will be the result?

A. Oracle kills Susan’s session to prevent a deadlock.

B. Oracle will detect a deadlock and roll back Susan’s session.

C. Oracle kills both John’s and Susan’s statements to prevent a deadlock.

D. Both John’s and Susan’s sessions would hang indefinitely because of a deadlock.

E. Oracle will detect a deadlock and roll back the statement causing the deadlock.

Answer: B

Explanation:

Oracle will detect a deadlock and roll back the Susan’s session: session detected a deadlock.

Incorrect Answers

A: Oracle will detect a deadlock FIRST and roll back the statement detected a deadlock. It will be Susan’s session.

C: Oracle will not kill both statements, only one, detecting a deadlock.

D: Both John’s and Susan’s sessions would not hang indefinitely: Oracle will handle a deadlock in most cases.

E: Oracle will detect a deadlock and roll back the statement DETECTED the deadlock, not causing it.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 468-470

Chapter 9: Tuning Contention

Performance Tuning - Question 122

Which three are used when administering systems with the database Resource Manager? (Choose three)

A. Histograms

B. Explain Plan

C. Resource Plan

D. Resource User Group

E. Resource Consumer Group

F. Resource Plan Directives

G. Resource Plan Assignments

Answer: C, E, F

Explanation:

Resource Plan, Resource Consumer Group and Resource Plan Directives are used when administering systems with the database Resource Manager.

Incorrect Answers

A: Histograms are not used with the database Resource Manager.

B: Explain Plan has no relation with the database Resource Manager.

D: There is Resource Consumer Group, not Resource User Group in Resource Manager.

G: There is no Resource Plan Assignments for Resource Manager.

OCP: Oracle 9i Performance Tuning Study Guide, Jaseph C. Johnson, p. 517-547

Chapter 10: Operating System Tuning

Performance Tuning - Question 121

The Accounting department has contacted you concerning their perceived poor performance of the new accounting package that was installed last week. Which three actions would help diagnose the problem? (Choose three)

A. Query the DBA_ROLLBACK_SEGs table to see if there is contention for rollback segment headers while the accounting application is being run.

B. Query DBA_DATA_FILES while the accounting application is being used to determine if any of the data files are being used excessively.

C. Query the V$SQL_PLAN table to determine which, if any, indexes are being used for the accounting tables.

D. Query the V$FILESTAT table while the accounting application is being used to determine if any of the data files are being used excessively.

E. Query the V$LOGFILE table to see if an excessive amount of redo is being generated while the accounting application is being run.

F. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK while the account application is being used and compare them to your previously generated base line reports from the same tool.

Answer: C, D, F

Explanation:

The V$SQL_PLAN view provides a way of examining the execution plan for cursors that were executed and are still cached. The V$FILESTAT view contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time. Generate performance monitoring reports using UTLBSTAT and UTLESTAT or the STATSPACK will also help to find the reason of poor performance.

Incorrect Answers

A: The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. But this information cannot be used to check contention for rollback segment headers.

B: The DBA_DATA_FILES data dictionary view provides descriptive information about each datafile, including the tablespace to which it belongs and the file id. The file id can be used to join with other views for detail information, but itself it will not give you information if any of the data files are being used excessively.

E: This view just identifies redo log groups and members and member status.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 40-41

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 120

Which four activities cause a sort? (Choose four)

A. Creation of an index.

B. Execution of the ANALYZE command.

C. Use of the ORDER BY and GROUP BY clauses.

D. Use of insert operations involving index maintenance.

E. Use of the UNION, INTERSECT and MINUS operators.

Answer: A, C, D, E

Explanation:

All these activities can cause a sort operation. Only if you are creating an index with NOSORT option sorting will not have place.

Incorrect Answers

B: Execution of the ANALYZE command does not require sorting.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 445-446

Chapter 8: Tuning Disk I/O

Performance Tuning - Question 119

Oracle Shared Server is best used when _____ and _____. (Choose two)

A. The database is experiencing database-intensive work.

B. The database is primary used for batch or DSS operations.

C. CPU usage on your machine is consistently at 90% or higher.

D. An OLTP application is running on a machine approaching memory resource limits.

E. You do not need to scale up the number of concurrent connections to the database.

F. The database is primary used for an interactive application where dedicated servers are mainly idle.

Answer: D, F

Explanation:

Oracle Shared Server configuration will be very helpful when an OLTP application is running on a machine approaching memory resource limits or the database is primary used for an interactive application where dedicated servers are mainly idle. The idle server process is holding system resources. The shared server architecture eliminates the need for a dedicated server process for each connection

Incorrect Answers

A: If the database is experiencing database-intensive work all dedicated servers are busy. But Oracle Shared Server architecture is more useful when dedicated servers are mainly idle.

B: The Oracle Shared Server configuration is more useful for OLTP systems with many DML operations, not for DSS environment with long-running transactions.

C: Oracle Shared Server will be not the best in this situation.

E: It is required when you NEED to scale up the number of concurrent connections to the database.

OCP: Oracle 9i Performance Tuning Study Guide, Jaseph C. Johnson, p. 517-547

Chapter 10: Operating System Tuning

Performance Tuning - Question 118

What may happen if you size your rollback segments too small?

A. The long transaction switches to SYSROL rollback.

B. The long transaction fails for lack of rollback space.

C. The long transaction hangs until the DBA alters MINEXTENTS.

D. The long transaction is forces to switch to another rollback segment.

Answer: B

Explanation:

The long transaction can fail for lack of rollback space if you size your rollback segments too small.

Incorrect Answers

A: Transaction cannot be switched to another rollback segment.

C: MINEXTENTS parameter has nothing to do with rollback segments. The long transaction will just fail.

D: Transaction cannot be switched to another rollback segment.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 338-342

Chapter 7: Tuning Redo Mechanisms

Performance Tuning - Question 117

You want to find the high water mark and the number of blocks above the high water mark without analyzing the table. Which method will provide this information?

A. Use DBMS_OUTPUT package.

B. Use the ANALYZE command.

C. Use DBMS_SQL.PARSE procedure.

D. Use DBMS_SPACE.UNUSED_SPACE procedure.

Answer: D

Explanation:

The DBMS_SPACE.UNUSED_SPACE procedure will help you to find the high water mark and the number of blocks above the high water mark without analyzing the table. This procedure returns information about unused space in an object (table, index, or cluster).

Incorrect Answers

A: Package DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them.

B: The task is not to use the ANALYZE command.

C: The PARSE procedure from the DBMS_SQL package can be used to parse DML or DDL statement.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 40-41

Chapter 2: Sources of Tuning Information

Performance Tuning - Question 116

Which two methods can you use to calculate the size of the recycle pool? (Choose two)

A. Total the buffer cache blocks used by the object.

B. Use the direct reads statistic from a tracing tool.

C. Use the physical reads statistic from a tracing tool.

D. Use the indirect reads statistic from a tracing tool.

Answer: B, C

Explanation:

To calculate the size of the recycle pool you can use the direct reads and the physical reads statistic from the tracing tool.

Incorrect Answers

A: Total the buffer cache blocks used by the object cannot be used for this purpose.

D: The indirect reads statistic from a tracing tool will not help you to calculate the size of the recycle pool.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 205-208

Chapter 4: Tuning the Shared Pool

Performance Tuning - Question 115

Which three can you use to monitor locks? (Choose three)

A. V$LOCK

B. DBA_WAITERS

C. V$LOCKED_OBJECT

D. DBA_PENDING_TRANSACTIONS

Answer: A, B, C

Explanation:

All these views can be used to monitor locks. DBA_WAITERS shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting. V$LOCKED_OBJECT view lists all locks acquired by every transaction on the system.

Incorrect Answers

D: DBA_PENDING_TRANSACTIONS describes unresolved transactions (either due to failure or if the coordinator has not sent a commit/rollback).

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 468-470

Chapter 9: Tuning Contention