Verified of 1z0-054 test preparation materials and Q&A for Oracle certification for consumer, Real Success Guaranteed with Updated 1z0-054 pdf dumps vce Materials. 100% PASS Oracle Database 11g: Performance Tuning exam Today!

2021 Jun 1z0-054 Study Guide Questions:

Q1. You work as a database administrator to manage an online transaction processing (OLTP) system.The application code was recently tuned for optimum performance and users acknowledged the performance improvement. However, the users are now again complaining that the application response time has gone down considerably. You used a few queries to investigate the problem and a couple of important queries are shown in the Exhibit. View the Exhibit and analyze the output shown.

Which actions would improve the performance? (Choose all that apply.)

Exhibit:


A. increasing the number database writers

B. decreasing the number of database writers

C. increasing the size of the database buffer cache

D. decreasing the size of the database buffer cache

E. decreasing the value set to the FAST_START_MTTR_TARGET parameter

Answer: AC


Q2. You are working on a single node database. The storage for the database was recently upgraded to new, faster hard disks and new disk controllers. You ran the I/O calibration procedure to check the I/O metrics for the upgraded storage. The details for the execution of the I/O calibration procedure are given below:

SQL>DECLARE

2 l_latency INTEGER;

3 l_iops INTEGER;

4 l_mbps INTEGER;

5 BEGIN

6 dbms_resource_manager.calibrate_io (1,10,l_iops,l_mbps,l_latency);

7 END;/

You queried the DBA_RSRC_IO_CALIBRATE data dictionary view to check the results of the I/O calibration. The output for the query is given as follows:

SQL> SELECT max_iops, max_mbps, max_pmbps, latency FROM DBA_RSRC_IO_CALIBRATE;

MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY

----------      ----------       ---------- ----------

137                           12                    6             64

What do you infer from the output?

A. The actual latency is 10 milliseconds for one disk.

B. The maximum sustainable I/O for a single process is 12 MB per second.

C. The system can sustain a maximum of 137 I/O operations per second with a throughput of 6 MB per second.

D. The system can sustain a maximum of 137 I/O operations per second with a throughput of 12 MB per second.

Answer: D


Q3. The SALES table in your database undergoes frequent changes. While observing the statistics for the table in the middle of the day, you find a change since the last observation. But the information about the statistics collection is not recorded in the DBA_TAB_STATS_HISTORY.

Identify the reason for this.

A. The AWR retention time was set to zero.

B. The STATISTICS_LEVEL parameter was set to BASIC.

C. Some of the statistics were collected using the ANALYZE command.

D. There were user-defined statistics that were set by using the DBMS_STATS.SET_TABLE_STATS procedure.

Answer: C


2passeasy.com

Avant-garde 1z0-054 real exam:

Q4. View the Exhibit and examine the result of Automatic SQL Tuning for a period. None of the recommended SQL profiles are implemented.

What would you do to allow the Automatic SQL Tuning implement the SQL profiles automatically?

Exhibit:


A. Set the OPTIMIZER_MODE parameter to ALL_ROWS for the database instance.

B. Set the SQLTUNE_CATEGORY parameter to DEFAULT for the database instance.

C. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE for the database instance.

D. Use the DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETERS procedure to set ACCEPT_SQL_PROFILES to TRUE.

Answer: D


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


Q6. What indicates that the rate of writing data blocks to the disk is very slow?

A. very high cache-hit ratio

B. increasing number of free buffer waits

C. increasing number of buffer busy waits

D. waits on latch: cache buffers chains event

Answer: B


2passeasy.com

Validated 1z0-054 pdf:

Q7. You are working on a decision support system (DSS). The index is available on the COUNTRY_ID column of the CUSTOMERS table.View the Exhibit and examine the parameter settings and the query execution plan.

Why is the query using a full table scan instead of an index scan?

Exhibit:


A. because the histogram statistics for the COUNTRY_ID column are not updated

B. because the index statistics for the index on the COUNTRY_ID column are not current

C. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a higher value

D. because the optimizer predicts that most of the blocks in the table are accessed.

Therefore, it uses a full table scan, even though indexes are available.

Answer: D


Q8. View the Exhibit and examine the Load Profile from the AWR report of a test database.The application must process an average of 10,000 very small transactions per day. The bulk of the processing activity is for DML operations with minimal to nil query processing.

Which two statements are correct about the values of the Physical reads events? (Choose two.)

Exhibit:


A. The per second value indicates the total number of data blocks read from disk to only the buffer cache.

B. The per transaction value includes both the db block gets and the consistent gets that occurred during the last sample period.

C. The per transaction value indicates that write or update transactions have been committed or rolled back during the last sample period.

D. The per second value indicates the total number of data blocks read from disk to the buffer cache including reads bypassing the buffer cache, and reads into process private buffers.

Answer: CD


Q9. The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. On investigation, you find that the Program Global Area (PGA) is undersized and using temporary segments for sort operations. You plan to increase the size of the PGA and there is ample amount of memory available to you. You used the PGA advisor to check the correct size for PGA. View the Exhibit and examine the content of the PGA advisor.

What size of PGA would give the maximum benefit?

Exhibit:


A. 125 MB

B. 3009 MB

C. 8024 MB

D. 1003 MB

E. 1204 MB

F. 1404 MB

Answer: B


Q10. View the Exhibit and examine the content.

ALLOCATED_SPACE and FREE_SPACE indicate that a large amount of space is allocated to the temporary tablespace that is being used.

Which action would reclaim the space and refresh these values?

Exhibit:


A. shrinking the tablespace

B. closing all the current sessions

C. restarting the database instance

D. forcing a checkpoint or waiting for a checkpoint to occur

Answer: A