1z0-054 | The Down to date Guide To 1z0-054 practice test
Q91. As a DBA configuring a new instance, you want to run a representative workload on the instance and examine the relevant statistics to see whether the cache is underconfigured or overconfigured.Given below is a list of the initialization parameters that would affect the statistics collection and advisories.
1. STATISTICS_LEVEL set to BASIC
2. STATISTICS_LEVEL set to TYPICAL
3. DB_CACHE_ADVICE set to ON
4.TIMED_STATISTICS set to TRUE
Which of the above initialization parameters' values must be set explicitly to get the relevant statistics with minimal impact on the overall database performance?
A. only 2
B. 2 and 3
C. 1, 3, and 4
D. 2, 3, and 4
Answer: A
Q92. View the Exhibit and examine the content of the V$PGA_TARGET_ADVICE_HISTOGRAM view.
What would you infer from this?
Exhibit:
A. If the PGA size is set to 4 MB, then all the work areas would execute in optimal mode.
B. If the PGA size is doubled, then all the work areas that are less than 2 MB would execute in optimal mode.
C. If the PGA size is set to 32 MB, then the number of work areas would reduce. So it is the appropriate size for PGA.
D. If the size of the Program Global Area (PGA) is set to more than 64 MB, then all the work areas would execute in optimal mode. So it is the appropriate size for PGA.
Answer: B
Q93. 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 are 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.
Identify the option that has the correct answers for the questions given below:
1) Which factor indicates the main problem?
2) What is the main problem?
3) What solution would you recommend?
A. 1) The Exhibit DBTIME shows that the DB CPU event consumed very high % DB Time.
2) The CPU is very slow.
3) Increase the number of processors.
B. 1) hard parse elapsed time has the majority time in parse time elapsed, which is shown in the TIMEMODEL Exhibit.
2) This is due to inadequate Database Buffer Cache.
3) Increase the size of database buffer cache.
C. 1) sql execute elapsed time and parse time elapsed are consuming very high % DB Time in the TIMEMODEL Exhibit.
2) There are too many soft parses.
3) Investigate the HOLD_CURSOR parameter setting in the application and set it appropriately.
D. 1) % Non-Parse CPU is low and Soft Parse % is also low in the EFFICIENCY Exhibit. This shows that very few statements are found in the cache.
2) There are too many hard parses.
3) Investigate the CURSOR_SHARING parameter setting and set it appropriately.
Answer: D
Q94. 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
Answer: BC
Q95. You are working on an online transaction processing (OLTP) system. You are investigating the reasons for performance degradation in the database. View the Exhibit named TOP5EVENTS and note the top five events.
View the Exhibit named TOPSQL and note the problems related to these statements.
View the Exhibit named INSTACT and note the table scans.
Which two are appropriate solutions to improve performance? (Choose two.)
A. Enable the large pool.
B. Keep the long tables in the keep buffer pool.
C. Keep the short tables in the keep buffer pool.
D. Set the CACHE property on the table for the long tables.
E. Set the CACHE property on the table for the short tables.
Answer: CE
Q96. You query the V$SYSSTAT view and notice a significantly high value for the redo log space requests statistic.
Which three components would you consider for further investigation based on this information? (Choose three.)
A. LGWR
B. checkpoints
C. archiver activity
D. size of the log buffer
E. size of the redo log files
Answer: BCE
Q97. Examine the values for the following initialization parameters:
STATISTICS_LEVEL = TYPICAL
TIMED_STATISTICS = true
You are managing an online transaction processing (OLTP) system. Application users notice that some queries have poor response time. You determine that queries from session ID 27, serial number 60, for user OE are heavy-resource consumers. To investigate further, you enabled tracing for the session by executing the following command:
SQL> EXECUTE dbms_system.set_sql_trace_in_session (27, 60, true);
Which statement is true?
A. The tkprof output file for the trace generated would display only the timed statistics for the SQL statements.
B. The tkprof output file for the trace generated would display statistics for all the sessions created by the user OE.
C. The tkprof output file for the trace generated would display bind variable values if bind variables are used in the queries executed in the session.
D. The tkprof output file for the trace generated would not display bind variable values if bind variables are used in the queries executed in the session.
Answer: D
Q98. 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. Service aggregation for action within application module
B. Monitoring individual sessions by querying V$SESSION_LONGOPS
C. Monitoring individual sessions in an application by querying V$SESSION
D. Mapping the sessions for an application to one resource consumer group
Answer: A
Q99. You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development databases. The company has recently upgraded this database to Oracle Database 11g from Oracle Database 10g.
You noticed that SGA is undersized as shown in Exhibit 1.
To investigate further, you checked the related parameters as shown in Exhibit 2.
You executed the following command to solve the problem:
ALTER SYSTEM SET MEMORY_TARGET=800M;
What would happen?
A. The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.
B. It implements AMM by setting MEMORY_TARGET and
MEMORY_MAX_TARGET parameters to 800M.
C. It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M and the MEMORY_MAX_TARGET parameter to 800M.
D. The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to a value less than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE values.
Answer: A
Q100. 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?
Exhibit:
A. There are excessive disk sorts.
B. There are excessive memory sorts.
C. There is a contention due to free list management.
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.
Answer: C