1z0-054 | Leading 1z0-054 Exam Study Guides With New Update Exam Questions
Q81. 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.
Answer: AD
Q82. A user, SCOTT, complains that his database session, SID 249, is extremely slow in processing transactions. You check the ADDM report and do not find any overall database performance issues.
You decide to check the wait events, specifically in SCOTT's session and issue the following command:
SQL>SELECT wait_class_id, wait_class, total_waits, time_waited FROM v$session_wait_class
WHERE sid = 249;
View Exhibit1 and examine the output.
You note that the APPLICATION wait class is the second most expensive wait class after the IDLE wait class.
You issue the following query to identify individual waits in the APPLICATION wait class:
SQL>select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = 4217450380;
View Exhibit2 and examine the output.
Which view would you examine next to pinpoint the problem that is causing this performance issue?
A. DBA_HIST_SESSMETRIC_HISTORY - to find the highest value metrics in the session
B. V$SQLAREA statistics - to find the SQL statements with the highest number of executions
C. V$SESS_TIME_MODEL - to identify whether the SQL statements in the session were spending more of execution time in parsing or waiting for the CPU
D. V$EVENT_HISTOGRAM - to determine whether the TIME_WAITED is evenly distributed across the occurrences of the wait or whether some waits for the events were very long whilst others were short indicating more erratic response times.
Answer: D
Q83. You used SQL Tuning Advisor for a long-running SQL statement that suggested a SQL profile which can be used by the query subsequently for better execution plan. View the Exhibit. But you want certain user sessions not to use this SQL profile for their queries.
How would you implement this?
Exhibit:
A. Alter the SQL profile to change the category of the SQL profile.
B. Set the OPTIMIZER_USE_PENDING_STATISTICS to TRUE the desired sessions.
C. Use database resource manager to prevent the use of the SQL profile by these user sessions.
D. Use database resource manager to preUse the resource management feature in profiles of these users to prevent the use of the SQL profile.
Answer: A
Q84. Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database supports middle-tier applications using connection pooling for connecting to database.
Being a part of the performance management team, you want to test the performance of the SQL statements and the peak workload of the applications in the new environment before upgrading the production system.
Given below are some of the steps in random order to accomplish the task:
1) Capture the real workload on the existing system.
2) Create a SQL Tuning Set (STS) for the statements executed by the application.
3) Transport STS on the test environment and use SQL Tuning Advisor to compare the before and after performance.
4) Replay the workload on the test environment.
5) Analyze the divergence after replay.
6) Use Automatic Workload Repository (AWR) to capture the information for top SQL on the existing system.
7) Transport STS on the test environment and use SQL Performance Analyzer to compare the before and after performance.
Identify the correct steps required to perform the test.
A. 2 and 3 only
B. 6 and 7 only
C. 1, 4, and 5 only
D. 2, 3, and 5 only
E. 2, 3, 4, and 5 only
Answer: C
Q85. You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. Before the new applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option for the storage.
You have the following requirements for the database:
1) Less mean time to recover (MTTR)
2) Striped for performance
3) Mirrored for safety
Which RAID option would you recommend?
A. RAID level 0
B. RAID level 1
C. RAID level 0 over 1(1+0)
D. RAID level 1 over 0(0+1)
Answer: C
Q86. Your database has the ACCTG service configured for an accounting application running on a middle tier. These service is used by the middle-tier applications to connect to the database by using connection pools. The application has three modules LEDGER, ACCOUNTS, and BILLING. You asked the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for the application. You enabled tracing at the service level by executing the following command:
SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('ACCTG');
You want to consolidate the trace output from the trace files created based on module.
Which is the appropriate solution for this?
A. Using trace files as input for the tkprof utility to consolidate the tracing of sessions for a module
B. Using the trace file as input for the trcsess utility to consolidate and process the output for a module
C. Using the trcsess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility
D. Using the trace file as input for the trcsess utility and creating output files that can directly be used by the users for performance or debugging purposes
Answer: C
Q87. View the Exhibit. You decide to quickly configure the adaptive threshold.
What would happen to the existing metric thresholds shown in the Exhibit?
Exhibit:
A. It would be overwritten.
B. It would remain in effect.
C. Only the thresholds set with the SYSTEM_MOVING_WINDOW baseline will remain in effect.
D. Only the thresholds set with baselines other than the SYSTEM_MOVING_WINDOW baseline will remain in effect.
Answer: A
Q88. 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.
Answer: BC
Q89. You noticed a high number of SQL statements with the same PLAN_HASH_VALUES.
View Exhibit1 to see the details.
View Exhibit2 to see the details of the SQL text for the statements.
What do you infer from the results?
A. The statements are using the same cursor.
B. The statements are not using the same cursor.
C. Removing the bind variable would improve performance.
D. The cursor for the statement is aging out of the shared pool very quickly.
Answer: B
Q90. The OS administrator in your company informed you about the upgrade of storage to new disk controllers and hard disks. You want to test if the new I/O devices are capable of handling the I/O load. The database is using Automatic Storage Management(ASM) for storage. To test the throughput and other I/O metrics, you performed IO calibration from the Enterprise Manager but you notice that the IO calibration job failed. To see the details of the error, view the Exhibit.
How you can resolve the error?
Exhibit:
A. Set FILESYSTEMIO_OPTIONS to NONE and DISK_ASYNCH_IO to FALSE.
B. Set FILESYSTEMIO_OPTIONS to SYNCH and DISK_ASYNCH_IO to FALSE.
C. Set FILESYSTEMIO_OPTIONS to ASYNCH and DISK_ASYNCH_IO to TRUE.
D. Set FILESYSTEMIO_OPTIONS to DIRECTIO and DISK_ASYNCH_IO
Answer: C