Oracle Database 11g: Performance Tuning - 1Z0-054 Exam Practice Test

Which two inferences are correct about the CPU time and the wait time that appear in the Automatic Workload Repository AWR and Statspack reports in the Top 5 Event section? (Choose two.)

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?

The columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID are frequently used together in the WHERE clause of the queries. The CUSTOMERS table is a big table with 20 GB of data. You notice that the selectivity for these three columns varies from the selectivity that the optimizer calculates. What would you recommend to influence the selectivity calculated by the optimizer?

View the Exhibit and note the workload type selected to quickly configure the metric threshold. Which statement describes the result after finishing this quick configuration process?

You want to review a summary of all the wait events in an instance since it started over a month
ago.
You issued the following query:
SQL>SELECT event, total_waits, time_waited, average_wait
FROM V$SYSTEM_EVENT
ORDER BY time_waited DESC;
View the Exhibit and examine a portion of the output from the above query.

Which events would you consider from the output for further investigation? (Choose all that apply.)

You are managing an online transaction processing (OLTP) system. The database is supporting a large number of applications using connection pools to connect to the database.Which method would you use for performance management?

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.)

You enabled tracing for a session by executing the following command:
SQL>SET AUTOTRACE TRACEONLY;
You issued a SQL query in the same session. Which two statements are true? (Choose two.)

You identified a SQL statement with SQL ID 9g485acn2n30m, which is expensive on the
resources. To view the execution plan for the statement, you executed the following command:
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('9g485acn2n30m',1));
What would this command accomplish?

Which three statements are true about performance analysis by SQL Performance Analyzer? (Choose three.)

Which three statements are true about Statspack usage? (Choose three.)

In a database, the values for transactions per second are higher during working hours and lower during nonworking hours. You want to receive alerts in future time periods if the daytime performance varies compared to the captured daytime performance or the nighttime performance varies compared to the captured nighttime performance, over a repeating time period. This would permit the DBA to perform additional performance tuning operations. Which feature would you use to record this variation in performance statistics?

As a DBA, you notice a change in the performance of your database. Which two pieces of tuning-related information can you examine in the alert log to identify possible causes? (Choose two.)

You are working on a 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. 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 the SGA depending on the workloads. Which two options would solve this problem? (Choose two.)