OCP Oracle 10g Admin II - Ch13: Monitoring and Managing Storage

Monitoring and Managing Storage

Review Questions

1. Which data dictionary view provides the recommended action, as a SQL statement, from the Segment Advisor?
A. DBA_ADVISOR_FINDINGS
B. DBA_ADVISOR_RECOMMENDATIONS
C. DBA_ADVISOR_ACTIONS
D. DBA_ADVISOR_RATIONALE
----
Ans: C.
The data dictionary view DBA_ADVISOR_ACTIONS contains the SQL statement(s) that the Segment Advisor supplies to implement its recommendation for segment maintenance. DBA_ADVISOR_FINDINGS contains the results of the analysis, but no SQL. DBA_ADVISOR_RECOMMENDATIONS presents one or more findings and the benefits for performing the recommendation. DBA_ADVISOR_RATIONALE provides a more detailed set of reasons why the recommendation should be implemented, along with the impact of not performing the recommendation.


2. Which of the following statements is not true about sorted hash clusters?
A. The new access path is used regardless of the type of predicate in the WHERE clause.
B. You are allowed to create indexes on sorted hash clusters.
C. The cost-based optimizer must be used to take advantage of the new access path.
D. Additional sorts are not necessary if you access the cluster by one of the lists of hash key columns.
E. More than one table can be stored in a sorted hash cluster.
---
Ans: A.
The new access path in a sorted hash cluster is used only if an equality predicate is used.


3. Consider the following scenario. The user SCOTT runs a query at 8:25 A.M.
that receives an ORA-01555: Snapshot too old error after running for 15
minutes. An alert is sent to the DBA that the undo tablespace is incorrectly
sized. At 10:15 A.M., the DBA checks the initialization parameter
UNDO_RETENTION, and its value is 3600; the parameter is sized correctly. The
DBA doubles the size of the undo tablespace by adding a second datafile. At
1:15 P.M., the user SCOTT runs the same query and once again receives an
ORA-01555: Snapshot too old error. What happens next? (Choose the best
answer.)

A. The DBA receives another alert indicating that the undo tablespace is
still undersized.
B. The user SCOTT calls the DBA to report that the query is still failing.
C. The second datafile autoextends so that future queries will have enough
undo space to complete when there is concurrent DML activity.
D. Resumable Space Allocation suspends the query until the DBA adds
another datafile to the undo tablespace, and then the query runs to completion.
---
Ans: B.
Even if the size of the undo tablespace is adjusted after an undo space problem,
only one alert is sent for each 24-hour period. Therefore, the only way that the
problem will be resolved promptly is for SCOTT to call the DBA, because the DBA
will not receive another alert until the next day when another query fails.

4. The background process __________ checks for tablespace threshold violation
or clearance every __________ minutes.
A. MMON, 10
B. SMON, 10
C. TMON, 30
D. PMON, 15
E. MMON, 30
---
Ans: A.
The new background process MMON checks for threshold violations every 10
minutes. An alert is triggered when the threshold is reached or is cleared.

5. Which of the following initialization parameters influences the recommended
redo log file size provided by the Redo Logfile Size Advisor?
A. LOG_CHECKPOINT_INTERVAL
B. OPTIMAL_LOGFILE_SIZE
C. FAST_START_IO_TARGET
D. FAST_START_MTTR_TARGET
E. None of the above
---
Ans: D.
FAST_START_MTTR_TARGET specifies the desired time, in seconds, for instance recovery after a crash or an instance failure. Therefore, the Redo Logfile Size
Advisor uses this value to determine the optimal log file size. OPTIMAL_LOGFILE_SIZE is not an initialization parameter but a column in the view V$INSTANCE_RECOVERY. The initialization parameter FAST_START_IO_TARGET specifies recovery at the I/O level, and LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of redo log file blocks used.

6. Which of the following is not a benefit of segment shrink?
A. Full table scans will take less time.
B. Better index access takes place because of a smaller b-tree.
C. Space is freed up for other database objects.
D. All chained rows are fixed.
E. Space below the HWM is released, and the HWM is moved down.
----
Ans: D.
While some chained rows may be fixed with segment shrink functionality, it is not guaranteed that all chained rows will be fixed, because not all blocks may be read in
a segment shrink operation.

7. The EM Database Control Segment Resource Estimation feature uses all the following characteristics of the proposed table except for which one?
A. Column datatypes
B. PCTUSED
C. PCTFREE
D. Column sizes
E. Estimated number of rows
---
Ans: B.
Only PCTFREE is used in the calculation, because it is the amount of space to leave
free in the block for updates to existing rows. PCTUSED is not needed unless the
segment space management is not AUTO. In addition, extent sizes calculated by this
feature help assess the impact on the tablespace where this segment will be stored.

8. Which of the following is not a benefit of sorted hash clusters? (Choose the best answer.)
A. Rows within a given cluster key value are sorted by the sort key(s).
B. The ORDER BY clause is not required to retrieve rows in ascending or
descending order of the sort key(s).
C. Cluster key values are hashed.
D. Rows selected by a cluster key value using an equality operator are
returned in ascending or descending order.
---
Ans: C.
While cluster key values in a sorted hash cluster are hashed, this is also true of regular hash clusters, and therefore is not a benefit unique to sorted hash clusters.

9. In the following scenario, the DBA wants to reclaim a lot of wasted space in
the HR.EMPLOYEES table by using the segment shrink functionality. Which of
the following is the correct order of the steps?
1. ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
2. ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
3. ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
4. ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
5. ALTER TABLE HR.EMPLOYEES SHRINK SPACE CASCADE;

A. 3, 4, 1, 5, 2
B. 4, 1, 3, 2, 5
C. 5, 2, 1, 3, 4
D. 4, 1, 2, 3, 5
---
Ans: A.
While the segment shrink operation could combine steps 1 and 4, the impact to the users will most likely be lessened by performing two smaller operations instead of one by specifying SHRINK SPACE COMPACT before specifying SHRINK SPACE in a
subsequent operation.

10. Which of the following calls to DBMS_SERVER_ALERT.SET_THRESHOLD will set the thresholds for the UNDOTBS1 tablespace to 60 percent and 90 percent?
(Choose the best answer.)
A)

dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
null);

B)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_le, 60,
dbms_server_alert.operator_le, 90,
1, 1, null,
dbms_server_alert.object_type_datafile,
'UNDOTBS1');

C)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');

D)
dbms_server_alert.set_threshold(
dbms_server_alert.tablespace_pct_full,
dbms_server_alert.operator_ge, 60,
dbms_server_alert.operator_ge, 90,
1, 1, null,
dbms_server_alert.object_type_tablespace,
'UNDOTBS1');
---
Ans: D.
The call to DBMS_SERVER_ALERT.SET_THRESHOLD must specify the metric
TABLESPACE_PCT_FULL, the two thresholds, an object type of tablespace, and the
tablespace name itself. Specifying NULL for the tablespace name will set the threshold
for all tablespaces, not just the UNDOTBS1 tablespace.

11. Which of the following statements is not true about segment shrink operations?
(Choose the best answer.)
A. Tables with ROWID-based materialized views are maintained.
B. Segment shrink is allowed only on segments whose space is automatically managed.
C. Heap-organized and index-organized tables can be shrunk.
D. ROW MOVEMENT must be enabled for heap-organized segments.
E. Chained rows may be repaired during a segment shrink operation.
F. Triggers are not fired during a segment shrink operation.
----
Ans: A.
Because the ROWIDs are changed with a segment shrink operation, tables with
ROWID- based materialized views cannot be shrunk unless the materialized views
are dropped and re-created after the segment shrink operation.

12. Which of the following is not a feature of the Segment Advisor within EM
Database Control?
A. Growth trend analysis
B. Segment Resource Estimation
C. Finding candidates for segment shrink
D. Finding table segments with chained rows
----
Ans: D.
The Segment Advisor is not used to find tables with chained rows, but instead is
used for finding segments that are good candidates for segment shrink or may be growing too fast.

13. Which of the following conditions will trigger an additional sort on a sorted
hash cluster? (Choose two.)
A. The ORDER BY clause specifies non-sort columns that are not indexed.
B. An ORDER BY clause is used in the query, although the sort may still fit in memory.
C. The cost-based optimizer is in effect.
D. The ORDER BY clause is omitted, and the WHERE clause does not reference the cluster key.
E. The ORDER BY clause specifies trailing sort columns.
---
Ans: A, E.
If a query on a sorted hash cluster retrieves rows and an ORDER BY clause
specifies either non-sort columns or a suffix of the sort columns, additional sorting is
required, assuming that indexes are not defined on the columns in the ORDER BY
clause.

14. Which of the following statements is not true about segment shrink operations
in tablespaces with automatic segment space management?
A. Clustered tables cannot be shrunk.
B. LOB segments can be shrunk.
C. IOT mapping tables and overflow segments cannot be shrunk.
D. Tables with function-based indexes cannot be shrunk.
E. ROW MOVEMENT must be enabled for heap-based segments.
---
Ans: B.
For segments in tablespaces with automatic segment space management, LOB
segments cannot be shrunk. In addition, tables with LONG columns, on-commit
materialized views, and ROWID-based materialized view cannot be shrunk. In all
cases, shrink operations cannot be performed on segments managed by freelists.


15. Which of the following is a disadvantage of rebuilding an index instead of
coalescing an index?
A. You temporarily need twice as much disk space to rebuild the index.
B. You cannot move the index to another tablespace.
C. The storage characteristics of the index cannot be changed.
D. The rebuild operation only coalesces index leaf blocks within each
branch instead of re-creating the entire tree.
----
Ans: A.
Whether you rebuild the index offline or online, you temporarily need twice as
much disk space. If you rebuild the index online, you also need disk space to support
a journal table to hold the intermediate changes to the index while the index is being
rebuilt.

16. Which of the following commands adds a member /logs/redo22.log to redo
log file group 2?
A. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;
B. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;
C. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;
D. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;
---
Ans: B.
When adding log file members, specify the group number or specify all the
existing group members.

17. Which of the following is not a benefit of index clusters?
A. The tables in the cluster are always queried together.
B. Queries with an equivalence operator will perform better.
C. The tables in the cluster have little or no DML activity.
D. The child tables have roughly the same number of rows for each parent key in the parent table.
----
Ans: B.
Hash clusters, not index clusters, use a hashing function to find a row in a cluster
and perform best for equivalence queries.

18. Which of the following scenarios will never trigger Resumable Space
Allocation? (Choose all that apply.)
A. SELECT * FROM HR.EMPLOYEES;
B. DELETE FROM HR.EMPLOYEES;
C. DROP TABLE HR.EMPLOYEES;
D. SELECT * FROM HR.DEPARTMENTS ORDER BY DEPARTMENT_NUMBER;
E. ALTER INDEX HR.EMP_NAME_IX REBUILD ONLINE;
F. ALTER INDEX HR.EMP_NAME_IX REBUILD;
----
Ans: A, C.
Unless an ORDER BY clause forces a sort operation and uses up temporary
space, a SELECT command will not otherwise trigger Resumable Space Allocation.
Dropping a table does not use any additional disk space in a tablespace; it frees up
disk space. Therefore, it will not trigger Resumable Space Allocation.

19. An AFTER SUSPEND trigger can be defined at what level? (Choose all that apply.)
A. Instance
B. Schema
C. Table
D. Session
E. Database
---
Ans: B, E.
The AFTER SUSPEND system-level trigger can be defined only at the database
level or at the schema level.

20. Which of the following statements is not true about index-organized tables?
A. An index-organized table can have additional indexes defined on other columns in the table.
B. An index-organized table has both physical and logical ROWIDs.
C. An index-organized table without secondary indexes is stored in a single segment.
D. The space requirements for an index-organized table are reduced in part
because the data is stored in the same segment as the index, and therefore no physical ROWID is required.
---
Ans: B.
An index-organized table (IOT) does not have a physical ROWID; instead, a
logical ROWID is constructed based on the value of the primary key.