1Z0-033
Section A
QUESTION 1
Which two statements regarding OLTP systems are true? (Choose two)
A. Use literals for optimally shared SQL rather than bind variables to keep the overhead of parsing to a
minimum.
B. To avoid the performance load of dynamic space allocation, allocate space explicitly so tables,
clusters and indexes.
D. Use hash clusters especially on tables that are heavily inserted into, because of the use of space and
the number of blocks that need to be visited.
E. Use application code to enforce rules instead of constraints, because constraints are extremely
expensive to process.
Answer: B, C
Explanation:
Online Transaction Processing (OLTP) systems tend to be accessed by large numbers of users doing short
DML transactions. Users of OLTP systems are primarily concerned with throughput: the total time it takes
to place an order, remove an item from inventory, or schedule an appointment. To avoid the performance
load of dynamic space allocation, you need to allocate space explicitly so tables, clusters and indexes.
Bitmap indexes will not work good for the OLTP systems because of locking issues affecting DML
operations. B-tree indexes can handle this easier and effectively.
Incorrect Answers
A: You cannot use literals for optimally shared SQL rather than bind variables because of nature of OLTP
systems: they work effectively using bind variables because of performing DML operations.
D: Hash clusters work more effective for DSS (Decision Support Systems): high level inserts and updates
will eliminate the advantage of hash clusters which require lower level of DML activity as DSS systems
provide.
E: Constraints are not expensive to process: they need to be used to avoid additional application code
creation to enforce a business rules.
OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 187-188
Chapter 3: SQL Application Tuning and Design
QUESTION 2
When performing a sort operation, you notice that there are a large number of sorts requi