Questions11-20

QUESTION 11:

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:
In public Toilet when some one is inside a sign on the door says "OCCUPIED".In short it indicates that Toilet is currently being used.Similarly When a Query is fired for searching some data(e.g select Query with where clause) during its Parsing phase Index is being used for searching.At this point of time Oracle9i  indicates 
that an index is being used.Oracle indicates it by showing a flag 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.

QUESTION 12:

 Using a bitmap structure to manage database object space has several benefits over
using freelist structures. Which three are benefits of managing space using automaticsegment-
space managed objects? (Choose three)
A. It readily responds to dynamic changes for concurrent access.
B. It improves performance and space utilization in a multiple-instance environment.
C. It requires fewer space related options then freelist managed structures.
D. It improves performance for SYSTEM tablespaces where a high degree of
concurrency is required.
Answer: A, B, D
Explanation:
Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused, freelists, and freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.
Automatic segment-space management was introduced in Oracle 9i for Locally Managed Tablespaces, and from Oracle 10g it is default option. Automatic segment-space management offers the following benefits:

• It provides administrative ease of use by avoiding the specification of storage parameters.
• It is a good method for handling objects with varying row sizes.
• It provides better run-time adjustment for variations in concurrent access and avoids tedious tuning methods.
• It provides better multi-instance behavior in terms of performance/space utilization

For some Extra Knowledge ASSM v/s Free List PPT.


QUESTION 13:

The EMPLOYEES table has six indexes and DML operations are slow. Which
command begins monitoring the EMPLOYEE_IDX_FK index to determine whether it
has been used by an execution plan?
A. ALTER TABLE employees monitor index employee_idx_fk;
B. ALTER INDEX employee_idx_fk monitoring on;
C. ALTER TABLE employees monitor all indexes;
D. ALTER INDEX employee_idx_fk monitoring usage;
Answer: D
Explanation:
This statement provides correct syntax to start monitoring the EMPLOYEE_IDX_FK index to
determine whether it has been used by an execution plan.

QUESTION 14:

You can control instance recover performance by setting parameters to define targets
for ______.
A. The size of the redo buffer in SGA.
B. The instance that will perform recovery in Real Application Cluster 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:
Instance recovery is completely server managed but then what can we do to make it fast?
Lesser is the loss,than more lesser will be the time required to recover that loss.In oracle terms more Frequently the CHKT takes place,then lesser will be the data loss.

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.


QUESTION 15:

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 remote password login file (orapw) is unchanged.
Answer: A, B, C
Explanation:
In 8i to log in as DBA in database,CONNECT INTERNAL was used.
but in 9i we get an error as

The CONNECT INTERNAL is disallowed in Oracle9i.
In 8i DBA needed to start Server Manager to perform startup and shutdown operation,but in 9i its not needed.The Server Manager tool is no longer supplied also. And the listener must be running to make a connection.
 
QUESTION 16:
 
What is the new Oracle Shared Server?
A. An improved version of multithreaded server configuration.
B. A connection pooling configuration where several clients are connected to the same
server process.
C. Two more database servers, which share data by means of database links; the client
software is unaware to which server it is connected.
D. A configuration of Real Application Cluster where the client connection is routed to
the least busy instance.
Answer: A
Explanation:
Oracle Shared Server is an improved version of multithreaded server configuration.imporved in the sense it supporsts more users.
 
QUESTION 17:
 
What type of protection is implemented when the DBA issues the ALTER DATABASE
SET STANDBY DATABASE PROTECTED; command?
A. The standby database is protected against write operations.
B. The primary database is protected against write operations.
C. The primary database is protected against data loss and data divergence.
D. The standby database is protected against data loss and data divergence.
Answer: C
Explanation:
Every database has an Standby database for emergency during crash.Primary database updates Standby database by passing its redo entries.What should happen on the primary database if all the
standby databases are unable to archive the redo logs from primary database.If primary database crashes at this time then there will be no standby database available for it.So to specify
guaranteed protection,we must establish the PROTECTED mode for the primary database.
The PROTECTED mode will establish a zero divergence and zero data loss configuration.

QUESTION 18:
 
You are attempting to create an Oracle-Managed Files (OMF) tablespace in a
production database with the following statement and receive the following error
message:
CREATE TABLESPACE tbsl;
ORA-02199: missing DATAFILE/TEMPFILE clause
oerr ora 2199
02199, 00000, "missing DATAFILE/TEMPFILE clause"
*Cause: A CREATE TABLESPACE statement has no DATAFILE/TEMPFTLE clause.
*Action: specify DATAFILE/TEMPFILE clause.
What is the corrective action to create the OMF based tablespace?
A. Issue the CREATE TABLESPACE tbsl DATAFILE SIZE 10M; command.
B. Issue the CREATE TABLESPACE tbsl EXTENT MANAGEMENT ORACLE;
command.
C. Set the db_create_file_dest parameter using the ALTER SESSION command and reissue
the statement.
D. An OMF tablespace is now allowed; only control files and/or redo log files can be
created by OMF.
Answer: C
Explanation:
OMF itself stands for Oracle Managed Files.Its like baby sitter,they will manage your baby but only thing you are required to do is take your baby to baby sitter's location.Similarly in OMF you are suppose to provide only location and managing part will be done by oracle.

You can set the DB_CREATE_FILE_DEST parameter using the ALTER SESSION
command and re-issue the statement. 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.
 
QUESTION 19:
 
Why can setting too low a value for the FAST_START_MTTR_TARGET parameter
reduce your overall database performance?
A. Data blocks have to be written more frequently by DBWR.
B. Redo blocks have to be written more frequently by LGWR.
C. You need to substantially increase the size of your database buffer cache to hold the
additional checkpoint records.
D. You need to substantially increase the size of your redo buffer cache to hold the
additional checkpoint records.
Answer: A
Explanation:
If you set a value for the FAST_START_MTTR_TARGET too low DBWR will write the
data blocks more frequently to reduce recovery time in accordance with the
FAST_START_MTTR_TARGET parameter. It can cause the overall database performance
degradation.
 
QUESTION 20:
 
What is true regarding a shared, server-side parameter file for a Real Application
Cluster database?
A. It can contain parameters with distinct values for each distance.
B. It can contain only parameters with identical values for each instance.
C. It must contain an IFILE parameter for each instance's individual parameter file.
D. It must be located in the default location for the primary instance's parameter file.
Answer: A
Explanation:
RAC environment supports multiple instance running servers but connected to same database.Then we shall define a single SPfile residing on disk(which is shared by all servers)which includes distinct values for each instance.or what we can do is  generate pfile on each server for their respective instance.