Practice Test Performance Tuning of Oracle 9i

What should one be your tuning goals?
Use as much memory as possible.
Use multiple copies of the code in memory.
Access the most possible number of blocks from disk.
Access the least possible number of blocks from disk.
Which statement about improving the performance of the database buffer cache by creating multiple buffer pools is true?
The KEEP buffer pool must also be deferred if the RECYCLE pool is defined.
The buffer pool for an object can be set explicitly only at object creation time.
The blocks from an object without an explicitly set buffer pool go into the RECYCLE pool.
Buffer pools are assigned to a segment, so option with multiple segments can have blocks in multiple Buffer pools.
What should be two goals in tuning rollback segments?
Transactions should never wait for access to rollback segment &. No transaction, however large or exceptional, should ever run out of rollback space.
No transaction, however large or exceptional, should ever run out of rollback space & Rollback segments should be configured to extend continually during normal processing.
Rollback segments should be configured to extend continually during normal processing& The ratio of waits to the rollback segment header blocks should be less than 5% of the sum of access.
Transactions should never wait for access to rollback segment &. Rollback segments should be configured to extend continually during normal processing.
Where can you find the non default parameters when the instance is started?
Alert log
Online redo log
Archive redo log
SYSTEM user's trace file
Which statements is not true while rebuilding indexes?
The ALTER INDEX REBUILD command is used to change the storage characteristics of an index
Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses the fast full scan feature.
Oracle 9i allows for the creation of an index or re-creation of an existing index while allowing concurrent operations on the base table.
When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to reduce the time it takes to rebuild
What is the least number of buffers an LRU latch must cover in the database buffer cache?
5
10
30
50
Which type of transaction should you assign to a specific large rollback segment?
Batch jobs that modify many rows.
Long running serialize able transactions.
Long running reports, to avoid 'snapshot too old' errors.
Discrete transactions that modify many rows in the same block.
What will this statement do? CREATE TABLESPACE temp DATAFILE 'C:\database\temp.dbf' SIZE 10m Temporary;
Create a tablespace that will be dropped on instance shutdown.
Create a tablespace in which the user can create segments for usage during sorts.
Create a tablespace in which Oracle can create segments for usage during sorts.
Create a tablespace in which a user can create tables that will be automatically dropped after a week.
Which V$ view which is used to monitor granule allocation and de-allocation in the buffer cache?
V$CACHE
V$ SESSTAT
V$BUFFER_POOL
V$LIBRARYCACHE
Which one of the statement is wrong while improving the performance of the database buffer cache by creating multiple buffer pools?
One, two, or three pools may be defined.
There are at least 50 blocks per LRU latch for each pool.
Each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES.
The size if the DEFAULT pool is obtained by adding all the pools to the value of the DB_BLOCK_BUFFERS parameter.
Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?
V$CACHE
V$SESS_IO
V$SYSSTAT
V$BUFFER_POOL
Which tablespace is used as the temporary tablespace if 'TEMPORARY TABLESPACE' is not specified for a user?
TEMP
DATA
SYSTEM
ROLLBACK
What are two main OLTP requirements?
Use bind variables rather than literals in your SQL code & Analyze your tables regularly to refresh optimizer statistics.
Analyze your tables regularly to refresh optimizer statistics.& Create multiple small rollback segments as opposed to a few big ones
Create multiple small rollback segments as opposed to a few big ones & Set up appropriate default storage parameter values for dynamic (implicit) space allocation.
Create indexes on all columns that are regularly used in query predicates& Set up appropriate default storage parameter values for dynamic (implicit) space allocation
What does this statement do? SQL> ANALYZE INDEX index_name VALIDITY STRUCTURE;
It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.
It provides information in the INDEX_HISTOGRAM view to indicate whether an index is invalid or valid.
It provides information in the DBA_INDEXES view for the COST BASED Optimizer when Choosing an execution plan.
It checks to see if each index entry points to a valid row in the underlying table and saves the results in the DBA_INDEXES view
Which two statements about plan stability and stored outlines are true?
You can group outlines in categories & Plan stability only wants when SQL statements match textually.
You can group outlines in categories & You can only have one stored outline per SQL statement
Plan stability only wants when SQL statements match textually & Stored outlines become invalid when you analyze the associated objects.
Stored outlines are saved in the data dictionary (SYS schema) & Stored outlines become invalid when you analyze the associated objects.
For which reason would you query V$SYSSTAT?
Name of the sort segment.
Free space available for a sort segment.
Number of disk sorts performed since startup.
Number of users active on individual sort segments.
Which two statements are true of the buffer cache?
The buffer pools in Oracle9i can be resized dynamically. & The blocks in the buffer cache are managed using two lists.
The buffer pools in Oracle9i can be resized dynamically & The blocks in the buffer cache are managed using one list.
The blocks in the buffer cache are managed using two lists. & In Oracle9i buffer caches can be dynamically added
The buffer pools in Oracle9i can be resized dynamically & In Oracle9i buffer caches can be dynamically added.
When a deadlock shutdown is detected by Oracle, where is the trace file generated?
SQL_TRACE
TRACE_DEST
USER_DUMP_DEST
CORE_DUMP_DEST
Which action could potentially cause checkpoints to take longer?
Increasing the number of redo log groups.
Increasing the size of rollback segments.
Decreasing the value of the REDO_LOG_BUFFERS parameter.
Increasing the value of the FAST_START_IO_TARGET parameter.
The database includes tables with static data, which are used for queries only. To which size should You set PCTFREE for this type of table?
0
50
20
10
Description:

Practice Test Performance Tuning of Oracle 9i
Disclaimer: Content, such as images used in the questions (if any), have been picked up from various places for the sole purpose of Instruction.

Discussion

Krunal

here two ans required but given a radio button.. so not possible attend two ans

3520 days 19 hours 55 minutes ago

Vaibhav

Good Questions

3617 days 11 hours 43 minutes ago

Your Facebook Friends on WizIQ

More Tests By Author

ASP Server Controls
10 Questions | 667 Attempts

ASP.NET, Namespaces ASP.NET
10 Questions | 2028 Attempts

An ASP.NET Application
10 Questions | 437 Attempts

Explore Similar Courses

Oracle ADF Online

Price:$100