QUESTION NO: 1
After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in
the SPA output. Identify the two actions that you would suggest for these regressed SQL
statements. (Choose two.)
A. Running SQL Access Advisor
B. Adding them to SQL Plan Baseline
C. Submitting them to SQL Tuning Advisor
D. Running Automatic Database Diagnostic Monitor (ADDM)
Answer: B,C
QUESTION NO: 2
Which three factors influence the optimizer's behavior while choosing an optimization approach
and goal for a SQL statement? (Choose three.)
A. parsing of a SQL statement
B. operating system (OS) statistics
C. object statistics in the data dictionary
D. the OPTIMIZER_MODE initialization parameter
E. optimizer SQL hints for changing the query optimizer goal
Answer: C,D,E
QUESTION NO: 3
Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You
imported SQL Tuning Sets (STS) from the previous version that contains important SQL
statements. You are not sure about the elapsed time that the SQL statements will have in the new
system due to the version change of the optimizer. Which tool provides a comparative report with
respect to the elapsed time in both versions of the optimizer?
A. SQL Access Advisor
B. SQL Tuning Advisor (STA)
C. SQL Performance Analyzer (SPA)
D. Automatic Workload Repository (AWR) compare period report
Answer: C
QUESTION NO: 4
The database application developers are planning to make some major schema changes such as
creating new indexes and materialized views. They want to check the net impact of these changes
on the workload performance. This activity has to be performed in the production database, so
they want only the query part of the data manipulation language (DML) statements to be executed
so that the side effects to the database or user data can be prevented. What should they use to
achieve this?
A. Database Replay
B. SQL Tuning Advisor
C. SQL Access Advisor
D. SQL Performance Analyzer
Answer: D
QUESTION NO: 5
You upgraded database from the Oracle Database 10g to Oracle Database 11g. To test the
performance SQL on the upgraded database. You want to build the before-change performance
data by using SQL Performance Analyzer .Which method would allow the task to execute quickly?
A. the EXPLAIN PLAN method
B. the TEST EXECUTE method
C. the COMPARE PERFORMANCE method
D. the OPTIMIZER_USE_SQL_PLAN_BASELINE parameter set to TRUE
Answer: A
QUESTION NO: 6
Identify the type of recommendation that can automatically be implemented by the Automatic
Tuning Advisor as part of automatic SQL tuning task?
A. recommendation for SQL profiles
B. recommendation about effective indexing
C. recommendation about stale or no statistics
D. recommendation about the structure of SQL statements
Answer: A
QUESTION NO: 7
Identify the two direct sources from where SQL plans can be loaded into the SQL plan baselines.
(Choose two.)
A. Cursor cache
B. Stored outline
C. SQL Tuning Set
D. Automatic Workload Repository (AWR) snapshots
Answer: A,C
QUESTION NO: 8
In which case is the database buffer cache NOT used?
A. when physical I/Os are performed to read from data files
B. when the database writer process writes to the SYSTEM tablespace
C. when the server process writes sort data to the temporary tablespace
D. when the database writer process writes undo data to the undo tablespace
Answer: C
QUESTION NO: 9
Which two statements are true about the database buffer cache? (Choose two.)
A. A buffer can be written to by many processes at the same time.
B. A buffer containing a block that is identical to the block on disk is called free buffer.
C. A buffer containing a block that is being accessed by a process is called dirty buffer.
D. A buffer available to be overwritten with a different database block at any time is called a dirty
buffer.
E. A buffer can contain a different version of a block that is available in a different buffer of the
same cache.
Answer: B,E
QUESTION NO: 10
You are working in an online transaction processing (OLTP) environment. You received many
complaints from users about degraded performance. Your senior DBA asked you to execute the
following command to improve the performance:
SQL> ALTER TABLE subscribe_log STORAGE(BUFFER_POOL recycle);
You checked the data in the SUBSCRIBE_LOG table and found that it is a large table having one
million rows. Which factor could be a reason for this recommendation?
A. The keep pool is not configured.
B. The automatic Program Global Area (PGA) is not configured.
C. The CURSOR_SPACE_FOR_TIME initialization parameter is set to FALSE.
D. The most of the rows in SUBSCRIBE_LOG table are accessed once a week.
E. All the queries on the SUBSCRIBE_LOG table are rewritten using a materialized view.
Answer: D
I hope you must have find these questions very helpful. If you want to get complete set of real exam questions and their answer please visit our FAQ's section to know more.
