New Features for Administrators - Question 026

Examine this fragment from a SQL*Plus session:

SQL> SELECT name, value FROM v$sysstat
2> WHERE name LIKE 'work area executions%';

NAME VALUE

-------------------------------- -------

work area executions - optimal 1544
work area executions - onepass 11
work area executions - multipass 1038

What can you conclude about the setting of the PGA_AGGREGATE_TARGET initialization parameter?

A. You cannot conclude anything because these statistics are not related to the PGA_AGGREGATE_TARGET parameter.

B. It may be set too low because there are many more multipass work area executions than one pass executions.

C. It may be set too high because there are many more multipass work area executions than one pass executions.

D. It is probably at its optimal setting because the ratio of one pass to multipass work area executions is below 0.10 (ten percent).

Answer: B

Explanation:

If the PGA_AGGREGATE_TARGET initialization parameter is set too low there will be more multipass work area executions than one pass executions.

Incorrect Answers

A: These statistics are related to the PGA_AGGREGATE_TARGET parameter.

C: If you set the PGA_AGGREGATE_TARGET initialization parameter is set too high there will be more one pass work area executions than multipass executions.

D: It’s not optimal setting because there are many more multipass work area executions than one pass executions.

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

Chapter 3: Manageability Enhancements

Oracle 9i New Features, Robert Freeman, p. 19-25

Chapter 1: Oracle9i Database Administration and Management Features