Oracle Database 11g: Performance Tuning (1Z0-054) Free Practice Test

Question 1

You observed in your database that the queries formerly executed in 5 seconds now take longer
time.View the Exhibit and analyze the output of the query that you executed to find the problem.Which is the primary concern?

A. the number of full table scans

B. the number of rows fetched based on rowid

C. the number of disk sorts

D. the number of chained and migrated rows

Correct Answer: D

Question 2

You work as a DBA and you have the responsibility of managing a large online transaction
processing (OLTP) system. You used the following query during the performance tuning activity:
SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100
2 "Cache Hit ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads'
7 AND phyd.name = 'physical reads direct';
Cache Hit Ratio
Which understanding of this ratio is correct?

A. 98.43% of times the requests were satisfied by performing physicalI/Os.

B. 98.43% of the total number of requests used the buffer cache.

C. 98.43% of times the requests have found free buffers.

D. 98.43% of times the requests have found the required data blocks in the buffer cache.

Correct Answer: D

Question 3

Examine the query output shown below:
SQL> SELECT sum(pins), sum(reloads), sum(reloads)/sum(pins)
FROM v$librarycache;
SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
16479 16000 0.9709327 Which statement describes the correct interpretation of the reloads-to-pin ratio and the action to be taken?

A. This ratio is very high and the shared pool size should be increased immediately to reduce this ratio.

B. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio crosses 1.

C. This ratio is reasonable. However, this needs monitoring. Increase the shared pool size only if the ratio falls below 0.1.

D. This ratio is reasonable. However, decrease the shared pool size for better performance.

Correct Answer: A

Question 4

You work as a consultant DBA for various clients. A performance issue in one of the online transaction processing (OLTP) systems is reported to you and you received the Automatic Workload Repository (AWR) report generated in the database. The main sections of the AWR report is shown in the Exhibits. View the Exhibit named DBTIME and note % DB Time.

View the Exhibit named TIMEMODEL and note what has contributed to % DB Time.

View the Exhibit named EFFICIENCY and examine the various percentages shown. Which option describes the correct understanding of the main problem?

A. % Non-Parse CPU: andSoft Parse %: are low in the EFFICIENCY Exhibit, which shows that very few statements are found in the cache. This indicates that there were too many hard parses.

B. sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit. This indicates too many soft parses.

C. hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit. This indicates inadequate database buffer cache.

D. The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time, which indicates that the CPU is very slow.

Correct Answer: A

Question 5

You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions. Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details. After some time you observed that sort operations are running out of temporary space.
What would be a long-term solution?

A. Make the bigger temporary tablespace the default temporary tablespace I

B. Create and assign temporary tablespace group to this user ID.

C. Increase the size of the temporary tablespace assigned to this user ID.

D. Change the temporary tablespace assigned to this user ID.

Correct Answer: B

Question 6

View the Exhibit and examine the statistics of a SQL statement.Which two conclusions can you draw from the statistics? (Choose two.)

A. The SQL statement has high CPU usage.

B. The SQL statement has high physical I/O.

C. The SQL statement has high logical I/O.

D. The SQL statement has long sort operations.

Correct Answer: A,C

Question 7

View the Exhibit and examine the details of the Top 5 Timed Events from an Automatic Workload Repository (AWR) report.What can be the three possible causes for the latch-related wait events? (Choose three.)

A. A large number of COMMITs are being performed.

B. Shared pool size is too small.

C. There are frequent logons/logoffs.

D. A large number of data definition language (DDL) and query statements are being executed simultaneously by different sessions.

E. Cursors are not being shared.

Correct Answer: B,C,E

Question 8

You identified poorly performing SQL by analyzing the V$SQL and V$SQLSTATS views. You want to investigate the plans for these SQL statements.Which two methods can you use to pull out the execution plan from the library cache for the already executed SQL? (Choose two.)

A. Copy and paste the SQL text from the V$SQL view and use EXPLAIN PLAN to generate the execution plan.

B. Query DBA_HIST_SQL_PLAN to view the execution plan.

C. Query V$SQL_PLAN to view the execution plan.

D. Use the dbms_xplan.display_cursor function with the SQL ID and child number to generate the execution plan.

Correct Answer: C,D

Question 9

You work in an online transaction processing (OLTP) environment and users are experiencing slow response time on their queries. These users connect as FINANCE to run their application and run hundreds of sessions. Your investigation shows that there is lot of I/O contention happening on only one temporary tablespace and there is no load on other temporary tablespaces. View the Exhibit and examine the details. After some time you observed that the sort operations are running out of temporary space. What would be a long-term solution?

A. Create and assign temporary tablespace group to this user ID.

B. Increase the number of database writer (DBWn) processes I

C. Configure the large pool if it is not done already.

D. Make the bigger temporary tablespace the default temporary tablespace ID.

E. Change the temporary tablespace assigned to this user ID.

Correct Answer: A

Question 10

In your organization the Oracle Database 11g installation includes the Oracle Diagnostic Pack.In your database, the STATISTICS_LEVEL and CONTROL_MANAGEMENT_PACK_ACCESS parameters, and the snapshot interval and retention period have been set to default values.Two users of your database have complained that a particular job or session is very slow for a transient interval of about 10 minutes during the day when the rest of the instance is performing as usual.Where would you begin the investigation of this performance issue?

A. ASH

B. ADDM

C. Alert log

D. Load Profile in an AWR Compare Period report

Correct Answer: A

Question 11

View the Exhibit.Which statement is true about applying the threshold setting in the Exhibit.

A. The threshold does not depend on any baseline.

B. The threshold is decided by comparing the warning and threshold values with the LAST_NIGHT baseline.

C. The threshold is decided by comparing the warning and critical values with the baseline set on the performance page.

D. The threshold is decided by comparing the warning and threshold values with the SYSTEM_MOVING_WINDOW baseline.

Correct Answer: A

Question 12

Examine the output of the following query:
SQL> SELECT c.name,a.addr,a.gets,a.misses,a.sleeps,
2 a.immediate_gets,a.immediate_misses,b.pid
3 FROM v$latch a, v$latchholder b, v$latchname c
4 WHERE a.addr = b.laddr(+) and a.latch# = c.latch#
5 AND c.name LIKE '&latch_name%' ORDER BY a.latch#;
LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
shared pool 20016544 8520540 14112 3137 0 0
Which two conclusions can you draw from this? (Choose two.)

A. The latch was requested in willing-to-wait mode.

B. The latch was requested in no wait mode.

C. Automatic Memory Management is not enabled because the IMMEDIATE_GETS and IMMEDIATE_MISSES columns have zero in them.

D. The shared pool operations to allocate and free memory in it caused the misses.

Correct Answer: A,D

Question 13

View the Exhibit that shows your investigation of performance degradation in your database.You also know that there are a few tablespaces using automatic segment-space management in the database.What could be the problem?

A. There is a contention due to free list management.

B. There are excessive memory sorts.

C. There are excessive disk sorts.

D. The tablespace that contains the segments on which waits are occurring is full.

E. The server process is processing buffers at a rate that is faster than the rate at which the I/O system can return the buffers.

Correct Answer: A

Question 14

You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to the database to perform transactions. The application users also perform direct loads. The company wants to upgrade from Oracle Database 10g version 10.2.0.1 to Oracle Database 11g. You want to test the performance of the SQL statements in the new environment before upgrading the production database. To accomplish this, you set up the test database with Oracle Database 11g, with the same data and schemas as in production. What would you recommend to accomplish the task?

A. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system, and using SQL Access Advisor for performance analysis

B. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis

C. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performance analysis

D. capturing the workload on the production database, replaying it on the test machine, and comparing the performance

Correct Answer: B

Question 15

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 most of the rows in SUBSCRIBE_LOG table are accessed once a week.

B. The keep pool is not configured.

C. The CURSOR_SPACE_FOR_TIME initialization parameter is set to FALSE.

D. All the queries on the SUBSCRIBE_LOG table are rewritten using a materialized view.

E. The automatic Program Global Area (PGA) is not configured.

Correct Answer: A

Welcome to TestSimulate

Oracle Database 11g: Performance Tuning (1Z0-054) Free Practice Test