1z0-054 | Refresh 1z0-054 Exam Study Guides With New Update Exam Questions
Q71. In your organization, there are two key databases. One of them is used primarily for an online transaction processing (OLTP) application and is directly accessed by the users in a client/server configuration. The application must process an average of 4,000 small transactions per day. The users enter frequent queries on whose results they depend for completing their transactions. One of the SLA requirements is that any query or transaction processing in the OLTP system should not exceed 15 seconds. The second database supports a decision support system (DSS) as well as batch report programs. It is accessed by client-side users, who submit ad hoc queries through SQL*Plus and by a browser-based,third party application that provides pooled connections for a large user base. The queries vary: some fetch only a few records, whereas others fetch and sort hundreds of records from several tables. As per the SLA specification the average turnaround time for a query on the DSS returning 10000 records should be 24 hours.
For both the systems, the number of users is not consistent and may vary from time to time.The management wants to ensure maximum resource usage is allocated for the OLTP processing.
Which two considerations should you give priority to when tuning these databases? (Choose two.)
A. Minimize parallelism for the OLTP systems.
B. Maximize throughput for batch processing.
C. Minimize the response time for OLTP users.
D. Maximize parallelism for the decision support systems.
E. Minimize response time for the decision support systems.
Answer: BC
Q72. You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database encountered performance-related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further. View the Exhibits and examine the AWR report.
What could be the problem in this database?
A. Java pool is not configured.
B. The CPU in the system is slow.
C. The shared pool size is inadequate.
D. The database buffer cache is inadequate.
E. The OPEN_CURSORS parameter is set to a small value.
Answer: C
Q73. 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
Q74. View the Exhibit and examine the partial output from the query on the v$file_histogram view:
Which statement correctly interprets the output displayed in the Exhibit?
Exhibit:
A. The database waited 58 times in a time interval of two milliseconds for FILE# 1.
B. The database experienced 58 waits of two milliseconds duration each for FILE# 1.
C. The database performed 58 synchronous single block reads in a time interval of two milliseconds for FILE# 1.
D. The database experienced 58 waits of duration less than two milliseconds each that are not included in any smaller bucket.
Answer: D
Q75. You work as a DBA and you have the responsibility of managing a large online transaction processing (OLTP) system. You used the following query during the performance tuning activity:
SQL> SELECT (1-((phy.value-phyd.value) / (cur.value + con.value))) * 100
2 "Cache Hit ratio"
3 FROM v$sysstat cur, v$sysstat con, v$sysstat phy, v$sysstat phyd
4 WHERE cur.name = 'db block gets'
5 AND con.name = 'consistent gets'
6 AND phy.name = 'physical reads'
7 AND phyd.name = 'physical reads direct'
Cache Hit Ratio
---------------
98.43
Which understanding of this ratio is correct?
A. 98.43% of times the requests have found free buffers.
B. 98.43% of the total number of requests used the buffer cache.
C. 98.43% of times the requests were satisfied by performing physical I/Os.
D. 98.43% of times the requests have found the required data blocks in the buffer cache.
Answer: D
Q76. View the Exhibit and analyze a portion of the Active Session History (ASH) report marked with a red rectangle.
Which two can be inferred from the output? (Choose two.)
Exhibit:
A. increase in the number of active sessions
B. unable to grow the database buffer cache as per demand
C. increase in number of COMMITS and ROLLBACKS per second
D. increase in the number of processes waiting for the buffer busy wait event
Answer: AD
Q77. On examining the Automatic Workload Repository (AWR) report, you find that log file sync appears among the top 5 wait events.On further investigation, you notice that the average time waited for this event is low, but the number of waits are high.
What does this imply?
A. DBWR is slow.
B. LGWR is slow due to slow I/O.
C. Redo log buffer size is insufficient.
D. The application might be committing frequently.
Answer: D
Q78. While diagnosing performance problems for a database, you find Buffer Busy wait is the reason from V$ views. But the Automatic Database Diagnostic Monitor (ADDM) report does not reflect the Buffer Busy findings.
Why does the Buffer Busy finding not appear in the ADDM report?
A. because an ADDM filter has been set
B. because the he USE_INDIRECT_DATA_BUFFERS parameter has been set to TRUE
C. because the DBIO_EXPECTED default task parameter for ADDM has been set to zero
D. because the CONTROL_MANAGEMENT_PACK_ACCESS parameter has been set to DIAGNOSTIC
Answer: A
Q79. You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.
On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated.
The SALES_RECORDS table has Automatic Segment Space Management (ASSM) and row movement enabled. The table is accessible in 24x7 mode.
What is the most efficient method to improve the performance?
A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table sequentially.
B. Shrink the SALES_RECORDS table by using the ALTER TABLE...SHRINK SPACE command.
C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE...MOVE command.
D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE...DEALLOCATE UNUSED command.
Answer: B
Q80. 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