1Z0-024 Oracle8i: Performance and Tuning Part 2

Oracle8i: Performance and Tuning Part 2


Get Full Version on:http://www.getfreedumps.com/

QUESTION 11

Which three types of statistics are reported in report.txt after running UTLESTAT SQL? (Choose three)
A. Locking statistics.
B. Memory usage statistics.
C. Explain plan statistics.
D. Library cache statistics.
E. Buffer busy wait statistics.
F. Rollback contention statistics.
Correct Answer: DEF
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Report.txt file contains library cache, buffer busy waits and rollback contention statistics.
Incorrect Answers:
A: This report does not shows locking statistics.
B: It will not provide memory usage statistics.
C: Report.txt file does not contain any information about explain plan statistics. Oracle 8, DBA Certification
Exam Guide, Jason S. Couchman, p. 769-776 Chapter 16: Introducing Database Tuning

QUESTION 12

What are two main OLTP requirements? (Choose two)
A. Use bind variables rather than literals in your SQL code.
B. Analyze your tables regularly to refresh optimizer statistics.
C. Create multiple small rollback segments as opposed to a few big ones.
D. Create indexes on all columns that are regularly used in query predicates.
E. Set up appropriate default storage parameter values for dynamic (implicit) space allocation.
Correct Answer: BC
Section: (none)
Explanation
Explanation/Reference:
Explanation:
There are several design paradoxes inherent in OLTP systems. First, OLTP systems need to be designed to
facilitate fast data entry without sacrificing accuracy. Any mechanism that checks the data being entered will
cause some performance degradation. Data change is the primary function of an OLTP system. The designers
and DBAs of such systems must work with users to create an effective trade-off between viewing data quickly
and making data changes quickly. Because of quick data changes you need to analyze your tables regularly to
keep optimizer statistics up to date. Because of high volume of user connections it's reasonable to create
multiple small rollback segments as opposed to a few big ones.
Incorrect Answers:
A: Using bind variables rather than literals in your SQL code will cause performance degradation because SQL
statements will be parsed again.
D: Iindexes on ALL columns will decrease performance also, because of high volume of insert, delete and
update requires indexes rebuilding constantly.
E: Setting up default storage parameter values for dynamic space allocation will cause performance
degradation, because these parameters need to be set based on database activity, insert and updates.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 793-795 Chapter 17: Tuning Database
Applications

QUESTION 13

Which tablespace is used as the temporary tablespace if 'TEMPORARY TABLESPACE' is not specified for a
user?
A. TEMP
B. DATA
C. SYSTEM
D. ROLL-BACK
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
Explanation:
SYSTEM tablespace will be used as the temporary tablespace if TEMPORARY TABLESPACE is not specified
for a user.
Incorrect Answers:
A: There is no TEMP tablespace by default in Oracle.
B: DATA tablespace is used for data storage, not for data sorts, so it must not to be used as temporary
tablespace. It will not be set as TEMPORARY TABLESPACE for user by default.
D: ROLLBACK tablespace is used for rollback segments information, not for data sorts, as temporary
tablespase is used. It will not be set as TEMPORARY TABLESPACE for user by default.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 953-955. Chapter 20: Tuning Other Areas of
the Oracle Database

QUESTION 14

Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?
A. V$CACHE
B. V$SESS_IO
C. V$SYSSTAT
D. V$BUFFER_POOL
Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:
Explanation:
The most useful dynamic view for determining the current number of blocks allocated to a buffer pool.
Incorrect Answers:
A: V$CACHE does not exist in Oracle8i. It's Oracle9i Real Application Clusters view. This view contains
information from the block header of each block in the SGA of the current instance as related to particular
database objects:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=180853.
B: V$SESS_IO view is used for I/O statistics for session, not for determining the current number of blocks
allocated to a buffer pool.
C: V$SYSSTAT view is used to determine the buffer-cache ratio, but it does not show the current number of
blocks allocated to a buffer pool. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 872-873.
Chapter 18: Tuning Memory and Operating System Use

QUESTION 15

Which three statements about improving the performance of the database buffer cache by creating multiple
buffer pools are true? (Choose three)
A. One, two, or three pools may be defined.
B. There are at least 50 blocks per LRU latch for
each pool.
C. Each buffer pool is assigned latches taken from DB_BLOCK_LRU_LATCHES.
D. The size of the DEFAULT pool is obtained by adding all the pools to the value of the
DB_BLOCK_BUFFERS parameter.
Correct Answer: ABC
Section: (none)
Explanation
Explanation/Reference:
Explanation:
There are three multiple buffer pools that can improve the performance of the database buffer cache: the KEEP
pool, the RECYCLE pool and the DEFAULT pool. Also there must be at least 50 block buffers for every LRU
latch you allocate to your buffer cache. Each buffer pool is assigned latches from DB_BLOCK_LRU_LATCHES.
Incorrect Answers:
D: The size of the DEFAULT pool is not obtained by adding all the pools. It's separate structure. Oracle 8, DBA
Certification Exam Guide, Jason S. Couchman, p. 867-872.
Chapter 18: Tuning Memory and Operating System Use

Get Full Version on:http://www.getfreedumps.com/


QUESTION 16

In which two ways can you reduce the amount of sorting that is performed? (Choose two)
A. By using UNIONinstead of UNION ALL.
B. By using NOSORT when creating tables.
C. By using NOSORT when creating indexes.
D. By using COMPUTE instead of ESTIMATE when analysing objects.
E. By reducing the number of users that have the sort privilege.
F. By creating appropriate indexes on tables that are joined often.
Correct Answer: CF
Section: (none)
Explanation
Explanation/Reference:
Explanation:
To reduce the amount of sorting that is performed you can use NOSORT clause when creating indexes (data is
already sorted). Also you can create appropriate indexes on tables that are often joined.
Incorrect Answers:
A: It will be increased amount of sorting if you use UNION command.
B: There is no NOSORT clause for CREATE TABLE command.
D: Usage of COMPUTE instead of ESTIMATE will require more sorts because this clause will process all rows,
not only small representation amount of them.
E: There is no sort privileges in Oracle for user. All sorts are performed automatically by Oracle itself.

QUESTION 17

What will this statement do?
CREATE TABLESPACE temp
DATAFILE 'C:\database\temp.dbf' SIZE 10n
Temporary;
A. Create a tablespace that will be dropped on instance shutdown.
B. Create a tablespace in which the user can create segments for usage during sorts.
C. Create a tablespace in which oracle can create segments for usage during sorts.
D. Create a tablespace in which a user can create tables that will be automatically dropped after a week
Correct Answer: C
Section: (none)
Explanation
Explanation/Reference:
Explanation:
This statement can be used to create segments for sort usage by Oracle.
Incorrect Answers:
A: This tablespace will not be dropped on instance shutdown.
B: There is no way for user to create sort segments explicitly, only Oracle can do this itself.
D: Tables can be created by user in TEMP tablespace, but they will not be dropped automatically. Also
TEMPORARY clause will prevent users from creating permanent objects in tablespaces containing temporary
segments.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 893-895.
Chapter 19: Tuning Disk Utilization

QUESTION 18

Which type of transaction should you assign to a specific large rollback segment?
A. Batch jobs that modify many rows.
B. Long running serializable transactions.
C. Long running reports, to avoid 'snapshot too old' errors.
D. Discrete transactions that modify many rows in the same block.
Correct Answer: A
Section: (none)
Explanation
Explanation/Reference:
Explanation:
Using SET TRANSACTION USE ROLLBACK SEGMENT command you can assign batch job transaction that
modify many rows to a specific large rollback segment.
Incorrect Answers:
B: You don't need to assign long running serializable transactions to a specific large rollback segment.
C: Reports are not changing data usually, so they will not cause 'Snapshot too old' error.
D: Discrete transactions do not need a specific large rollback segment to be assigned. Oracle 8, DBA
Certification Exam Guide, Jason S. Couchman, p. 911.
Chapter 19: Tuning Disk Utilization

QUESTION 19

What is the least number of buffers an LRU latch must cover in the database buffer cache?
A. 5
B. 10
C. 30
D. 50
E. 100
Correct Answer: D
Section: (none)
Explanation
Explanation/Reference:
Explanation:
There must be at least 50 block buffers for every LRU latch you allocate to database buffer cache.
Incorrect Answers:
A: 5 buffers are not enough for an LRU latch, there must be at least 50 blocks.
B: 10 buffers are not enough for an LRU latch, there must be at least 50 blocks.
C: 30 buffers are not enough for an LRU latch, there must be at least 50 blocks.
D: 100 buffers will be enough for an LRU latch, but the minimum number is 50 blocks, not 100. Oracle 8, DBA
Certification Exam Guide, Jason S. Couchman, p. 867-872.
Chapter 18: Tuning Memory and Operating System Use

QUESTION 20

Which three statements about rebuilding indexes are true? (Choose three)
A. The ALTER INDEX REBUILD command is used to change the storage characteristics of an index.
B. Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses
the fast full scan feature.
C. Oracle8i allows for the creation of an index or re-creation of an existing index while allowing concurrent
operations on the base table.
D. When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to
reduce the time it takes to rebuild.
Correct Answer: ABC
Section: (none)
Explanation
Explanation/Reference:
Explanation:
It's possible to change the storage parameters of an index with ALTER INDEX REBUILD command. This
command usually faster than dropping and recreating an index because of fast full scan feature. Users can
perform concurrent operations on the base table during the creation or re-creation of an index in Oracle8i.
Incorrect Answers:
D: Usage of NOLOGGING keyword can reduce the time to rebuild index, but not an UNRECOVERABLE
keyword.
Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 917-918.
Chapter 19: Tuning Disk utilization

Get Full Version on:http://www.getfreedumps.com/

Comments

Popular posts from this blog

1z0-020 Oracle8i: New Features for Administrators Part 2

1Z0-023 Oracle8i: Architecture and Administration Part 2

1Z0-024 Oracle8i: Performance and Tuning Part 3