Oracle 1Z0-054 問題集

質問 1：
A user session executes a query and the plan for the query is shown in the Exhibit as Plan-1. After verifying with the SQL Access advisor, an index is created on the table in the JOB_ID column and the query is executed again. A new plan is generated against the second query shown in the Exhibit as Plan-2. When explaining the plan for the second query, you observe that the optimizer uses the first plan instead of the second. The following parameters are set for the user session: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE SQLTUNE_CATEGORY=DEFAULT Why does the optimizer not use the second plan?

A. because the SQLTUNE_CATEGORY parameter is set to DEFAULT
B. because the explain plan option in the user session does not allow the new plan because SQLTUNE_CATEGORY parameter is set to DEFAULT
C. because the second plan is not verified
D. because the second plan is not part of the SQL plan baseline
正解：C

質問 2：
You are managing an online transaction processing (OLTP) application database. Some users on the system are reporting degradation in the performance of SQL statements. To diagnose the problem, you want to identify the SQL statements that are expensive on system resources.What would you recommend to accomplish the task? (Choose all that apply.)
A. creating an Automatic Workload Repository (AWR) report for the problematic period
B. running SQL Performance Analyzer
C. running SQL Access Advisor
D. querying the V$SQLSTATS and V$SQL_PLAN views to get the information
正解：A,D

質問 3：
You queried V$SYSTEM_EVENT in your online transaction processing (OLTP) environment to check the following wait events:
Average Event Total Seconds Total Wait Name Waits Waiting Timeouts (in secs)
direct path read temp 18,274 11,312 16,282 .619 direct path write temp 3,251 416 25 .127 Which step would be appropriate to improve the performance?
A. Increase the size of the Program Global Area (PGA).
B. Increase the number of I/O slaves.
C. Increase the number of database writer (DBWn) processes.
D. Increase the size of the Database Buffer Cache.
正解：A

質問 4：
View Exhibit1 and examine a portion of Active Session History(ASH) report.

View Exhibit2 to examine the tablespaces.

Which action would you suggest after analyzing the output?
A. Rebuild indexes on SPCT table.
B. Increase the log buffer size.
C. Decreasing the number of free lists in segment SPCT.
D. Move SPCT table to a different tablespace that is locally managed with automatic segment space management.
正解：D

質問 5：
You are working on an online transaction processing (OLTP) system. By day most of the application users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)
A. setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level
B. asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries
C. asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries
D. setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
正解：C,D

質問 6：
Which two statements are true about the database buffer cache? (Choose two.)
A. A buffer available to be overwritten with a different database block at any time is called a dirty buffer.
B. A buffer can contain a different version of a block that is available in a different buffer of the same cache.
C. A buffer can be written to by many processes at the same time.
D. A buffer containing a block that is identical to the block on disk is called free buffer.
E. A buffer containing a block that is being accessed by a process is called dirty buffer.
正解：B,D

質問 7：
Which statement is true regarding the setting of the PCTFREE value for an index?
A. It decides how much of a block to fill when the index is created.
B. It keeps space reserved in a block for possible updates to existing rows in that block.
C. It is the minimum percentage of a block that can be used for row data and overhead before new rows are added to the block.
D. It has no role to play when segment space management is automatic.
正解：A