1z0-054 | Tested Oracle 1z0-054 pdf
Q1. Identify situations in which collecting statistics at the service level helps diagnosing performance problems. (Choose all that apply.)
A. in a database where you want to monitor and diagnose individual sessions
B. in a database where you want to statistically measure the workload of jobs
C. in a database that supports an application where the client directly creates a session
D. in multitier environments where a request from an end client is routed to different database sessions
Answer: BD
Q2. You are working for a company that uses huge applications to manage its customer details. During your regular performance checks, you executed the following query:
SQL> show parameter shared_pool_reserved_size
NAME TYPE VALUE
------------------------------ --------------------- -----------------
shared_pool_reserved_size big integer 6920601
SQL> SELECT free_space, used_space, request_misses, request_failures from V$SHARED_POOL_RESERVED;
FREE_SPACE USED_SPACE REQUEST_MISSES REQUEST_FAILURES
---------- ---------- -------------- ----------------
6469776 555528 0 113
You observed that REQUEST_FAILURES is 113 but not increasing, and also that REQUEST_MISSES is not increasing.
What would you interpret from this?
A. The reserved pool is very large. Consider reducing the size of the shared pool.
B. The reserved pool is large and has free space to satisfy requests. Consider reducing the size of the reserved pool.
C. The reserved pool is very small and is not finding enough memory to satisfy requests.
Increase the size of the shared pool.
D. The reserved pool is very small and is not finding enough memory to satisfy requests.
Increase the size of the reserved pool.
Answer: B
Q3. You work as a DBA and have the responsibility of managing a large online transaction processing (OLTP) system. You used three queries to check the database performance as shown in the Exhibit.View the Exhibit and analyze the output.
What conclusion can you draw from this?
Exhibit:
A. There are many physical I/Os happening.
B. There are many full table scans happening.
C. The data blocks are aging out of the buffer cache very fast.
D. There are many cursors trying to access the same data blocks.
E. The DBWn processes are not freeing sufficient buffers to meet the demand.
Answer: D
Q4. View the Exhibit and examine the output for the query on V$IOSTAT_FUNCTION.Which two statements are true about the interpretation of the output? (Choose two.)
Exhibit:
A. The I/O statistics are only for the single block read and write operations.
B. The I/O statistics are for single block or multi-block read and write operations.
C. If there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.
D. If there is a conflict of I/O functions, the I/O is placed in the bucket with the higher FUNCTION_ID.
Answer: BC
Q5. Which is the correct description of SQL profiling while using SQL Tuning Advisor?
A. It is a set of recommendations by the optimizer to create new indexes.
B. It is auxiliary information collected by the optimizer for a SQL statement to eliminate estimation error.
C. It is a set of recommendations by the optimizer to refresh stale statistics to avoid bad execution plan.
D. It is a set of recommendations by the optimizer to restructure a SQL statement to avoid bad execution plan.
Answer: B
Q6. 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. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performance analysis
B. capturing the workload on the production database, replaying it on the test machine, and comparing the performance
C. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis
D. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system, and using SQL Access Advisor for performance analysis
Answer: C
Q7. You are managing an online transaction processing (OLTP) database with multiple applications. The applications support a large number of users and most of the applications execute groups of SQL that run together.
Which option would you use to monitor the performance issues for users of these applications?
A. Monitor statistics for individual sessions in an application by querying V$SESSION.
B. Specify service-level thresholds and priorities for users of applications and monitor the alerts.
C. Monitor statistics and wait events collected for a MODULE and ACTION by performing service aggregation.
D. Configure application users as part of the resource consumer group and monitor the resource usage statistics for the resource consumer group.
Answer: C
Q8. Identify the situation in which you would use the Automatic Workload Repository (AWR) baseline template.
A. when creating AWR snapshots based on a repeating time schedule
B. when creating and dropping AWR baselines based on a repeating time schedule
C. when computing statistics for all moving window baselines based on a repeating time schedule in the future
D. when resetting the threshold values for the SYSTEM_MOVING_WINDOW baseline based on a repeating time schedule in the future
Answer: B
Q9. 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?
Exhibit:
A. Configure the large pool if it is not done already.
B. Change the temporary tablespace assigned to this user ID.
C. Create and assign temporary tablespace group to this user ID.
D. Increase the number of database writer (DBWn) processes ID.
E. Make the bigger temporary tablespace the default temporary tablespace ID.
Answer: C
Q10. Identify the correct statements about the SYSTEM_MOVING_WINDOW baseline. (Choose two.)
A. It is used for adaptive threshold setting.
B. The statistics computation cannot be scheduled explicitly for it.
C. Only significance-level thresholds can be set for metrics under it.
D. It is the only baseline that can be used on the performance page.
Answer: AB