Free Sharing Lead2pass Exam Dumps

Free Download All The Latest Cisco, Microsoft, CompTIA, Oracle, VCP, CISSP Questions And Answers

Free Download Pass4sure Oracle Oracle Database 11g 1Z0-054 Braindumps

Oracle Database 11g: Performance Tuning: 1Z0-054 Exam
1Z0-054 Questions & Answers
Exam Code: 1Z0-054
Exam Name: Oracle Database 11g: Performance Tuning
Q & A: 204 Q&As

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)
Correct Answers: B C

2: View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.
View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.
The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second
plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.Which SQL plan
baseline would be used if the SQL query in exhibit1 is executed again when the value of
OPTIMIZER_MODE is set to FIRST_ROWS?
A.the second plan, because it is a fixed plan
B.the first plan, because it is an accepted plan
C.the second plan, because it is the latest generated plan in FIRST_ROW mode
D.A new plan, because the second plan in FIRST_ROW mode is not an accepted plan
Correct Answers: B

3: You work as a DBA for a company and you have the responsibility of managing one of its
online transaction processing (OLTP) systems. The database encountered performance-related
problems and you generated an Automatic Workload Repository (AWR) report to investigate it
further. View the Exhibits and examine the AWR report.
What could be the problem in this database?
A.Java pool is not configured.
B.The CPU in the system is slow.
C.The shared pool size is inadequate.
D.The database buffer cache is inadequate.
E.The OPEN_CURSORS parameter is set to a small value.
Correct Answers: C

4: You are working on an online transaction processing (OLTP) system. You detected free buffer
waits events for your database instance running in a machine that has multiple CPUs. You
increased the database buffer cache size as the first step. After a few hours of work on the database,
further investigation shows that the same event is being recorded. What would be your next step to
avoid this event in future?
A.Decrease the value of the DBWR_IO_SLAVES parameter.
B.Set the USE_INDIRECT_DATA_BUFFERS parameter to TRUE.
C.Increase the value of the DB_WRITER_PROCESSES parameter.
D.Increase the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter.
Correct Answers: C

5: You are working on a development database that was upgraded to Oracle Database 11g from
Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately
sized, as shown in the Exhibit.
You diagnosed that this is due to different kinds of workloads and this occurs only during peak
hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate
buffer cache problems. The following are the related parameter settings:
SQL> show parameter sga
NAME TYPE VALUE
—————————— ————— —————–
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 0
SQL> show parameter target
NAME TYPE VALUE
—————————— ————— —————–
…..
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 100M
sga_target big integer 0
You want to balance the memory between the System Global Area (SGA) components within
SGA without affecting the size of the Program Global Area (PGA). Which action would solve this
problem?
A.Set the SGA_TARGET parameter to 300M.
B.Set the SGA_MAX_SIZE parameter to 400M.
C.Set the MEMORY_TARGET parameter to 100M.
D.Set the MEMORY_MAX_TARGET parameter to 300M.
Correct Answers: A

6: You observed that some of the queries are performing poorly on the SALES_RECORDS table
in your database.
On further investigation, you find that at the end of each day the contents of the
SALES_RECORDS table are transferred to the SALES table and deleted from the
SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You
decided to use the ALTER TABLE…SHRINK SPACE COMPACT command to shrink the table. Why would you choose this method? (Choose all that apply.)
A.because it can be used during peak hours
B.because it avoids unnecessary cursor invalidation
C.because it adjusts the high-water mark (HWM) immediately
D.because you have long-running queries that might span the shrink operation
E.because it does not allow any data manipulation language (DML) operations, thereby making
the shrink operation faster
Correct Answers: A B D

7: View the Exhibit and examine a portion of the output obtained from the following query:
SQL> SELECT * FROM v$sys_time_model;
Select three correct interpretations of the time model statistics. (Choose three.)
A.DB time includes the wait time of all the nonidle and idle user sessions.
B.SQL execute elapsed time includes the time spent in performing fetches of query results.
C.DB CPU includes the CPU time spent on database user-level calls and background CPU time.
D.SQL execute elapsed time includes components of the hard parse elapsed time like bind elapsed
time.
E.DB time includes the connection management call elapsed time excluding the background
process time.
Correct Answers: B D E

8: A batch workload that historically completed in the maintenance window between 10:00 PM
and midnight is currently showing poor performance and completing at 2 AM. To help in the
diagnosis of the performance degradation, the senior DBA in your organization asks you to
execute the awrddrpt.sql script to produce a Compare Periods report. Which two statements are
true about the report produced by this script? (Choose two.)
A.It is refreshed automatically based on the moving window baseline.
B.It compares details between any two selected time periods of the same duration.
C.It normalizes the statistics by the amount of time spent on the database for each time period.
D.It compares details between two consecutive time periods of the same or different durations and
is refreshed every 60 minutes.
Correct Answers: B C

9: You plan to use adaptive thresholds as part of the performance tuning activity. You decide to
increase the window size of the default moving window baseline for all metric observations and
comparisons in your database. The following error occurs when you try to increase the window
size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than
retention (1036800) ORA-06512: at “SYS.DBMS_WORKLOAD_REPOSITORY”, line 601
ORA-06512: at line 2 Which action would allow you to perform the preceding task successfully?
A.increasing the flashback retention period
B.increasing the retention period for SQL Management Base
C.increasing the undo retention period for the database instance
D.increasing the Automatic Workload Repository (AWR) retention period
Correct Answers: D

10: Identify two correct statements about the Active Session History (ASH) data. (Choose two.)
A.A part of SGA memory is used to store ASH data as rolling buffer.
B.The ASH data can be analyzed between any two small time intervals.
C.All ASH data in memory are flushed to disk by MMON in every 3 seconds.
D.All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.
Correct Answers: A B

11: A user in a session executed the following SQL statement to set the optimizer mode:
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS
What impact would it have on the goal of the optimizer for that session? (Choose all that apply.)
A.Statement level OPTIMIZER_MODE hints take precedence over the session-level setting.
B.The OPTIMIZER_MODE parameter set at instance level takes precedence over the
session-level value.
C.The optimizer uses a cost-based approach, regardless of the presence of statistics; it optimizes
with a goal of best response time.
D.The optimizer uses a cost-based approach for all SQL statements in the session, regardless of
the presence of statistics; it optimizes with a goal of best throughput.
Correct Answers: A D

12: 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.setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level
C.asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing
queries
D.asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing
queries Correct Answers: B C

13: 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
Correct Answers: C D E

14: Examine the initialization parameter values for the instance given below:
NAME TYPE VALUE
———————————— ———– ——————
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
db_file_multiblock_read_count integer 64
The index created on the column used in the WHERE clause of the query. You notice that the
query is not using the index. Instead of an index scan, a full table scan is used.
View the Exhibit and examine the autotrace output for a query.
What could be the reason for it? (Choose all that apply.)
A.The OPTIMIZER_INDEX_COST_ADJ initialization parameter has a low value.
B.The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter has a low value.
C.The statistics for the table and all the indexes associated with the table are not current.
D.The table has less than DB_FILE_MULTIBLOCK_READ_COUNT blocks under the
high-water mark.
Correct Answers: C D

15: Examine the initialization parameter values for the instance given below:
NAME TYPE VALUE
———————————— ———– ——————
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 11.1.0.6
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
db_file_multiblock_read_count integer 64
You notice that the one of the queries is using a full table scan (view Exhibit1) instead of index
unique scan (view Exhibit2). The index is present on the column that is accessed in the WHERE clause of the query. The cost for a full table scan is more than that for an index unique scan.
Why would the optimizer choose full table scan over index unique scan? (Choose all that apply.)
A.The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a low value.
B.The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to a high value.
C.The DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a low value.
D.The statistics for the table and all the indexes associated with the table are not current.
Correct Answers: B D

…go to http://www.lead2pass.com/1z0-054.html to download the full version, ensure a passing score in the test.

Lead2pass Testking Pass4sure Actualtests Others
$99.99 $124.99 $125.99 $189 $29.99-$49.99
Up-to-Dated
Real Questions
Error Correction
Printable PDF
Premium VCE
VCE Simulator
One Time Purchase
Instant Download
Unlimited Install
100% Pass Guarantee
100% Money Back