Proper study guides for Renovate Oracle Oracle Database 11g: Performance Tuning certified begins with Oracle 1z0-054 preparation products which designed to deliver the Highest Quality 1z0-054 questions by making you pass the 1z0-054 test at your first time. Try the free 1z0-054 demo right now.
2021 Sep 1z0-054 practice
Q81. You have a range-partitioned table in your database. Each partition in the table contains the sales data for a quarter. The partition related to the current quarter is modified frequently, whereas other partitions undergo fewer data manipulations. The preferences for the table are set to their default values. You collect statistics for the table using the following command at regular intervals:
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SH','SALES',GRANULARITY=>'AUTO');
You need statistics to be collected more quickly. How do you achieve this?
A. Set the PUBLISH preference to TRUE for the partition table.
B. Set the NO_VALIDATE preference to TRUE for the partition table.
C. Set the INCREMENTAL preference to TRUE for the partition table.
D. Increase the value of the STALE_PERCENT preference for the partition table.
Q82. You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.The applications running on the database use connection pooling to connect to the database to perform transactions. The company wants to upgrade the CPU and the memory for the machine on which the database is running. To test the realistic workload on the new machine with increased CPU and memory, you identified the peak workload time for the database and captured the workload on the production system.
You set up the test machine with the upgraded CPU and memory and installed the database with the same version as production.
Which three actions would you perform to accomplish the realistic replay of the peak workload on the test machine? (Choose three.)
A. Preprocess the captured workload for the specified target database version.
B. Set up as many replay clients as there are actual clients to the production system.
C. Set up all the replay clients on the same machine on which the test database is running.
D. Resolve all external references such as database links, external tables, and directory objects.
E. Restore the replay database to match the capture database at the start of the workload capture.
Q83. 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 sort operations are running out of temporary space.
What would be a long-term solution?
A. Change the temporary tablespace assigned to this user ID.
B. Create and assign temporary tablespace group to this user ID.
C. Increase the size of the temporary tablespace assigned to this user ID.
D. Make the bigger temporary tablespace the default temporary tablespace ID.
Q84. View the Exhibit and examine a portion of the output obtained from the following query:
SQL> SELECT * FROM v$sys_time_model;
Select three correct interpretations of the time model statistics. (Choose three.)
A. DB time includes the wait time of all the nonidle and idle user sessions.
B. SQL execute elapsed time includes the time spent in performing fetches of query results.
C. DB CPU includes the CPU time spent on database user-level calls and background CPU time.
D. SQL execute elapsed time includes components of the hard parse elapsed time like bind elapsed time.
E. DB time includes the connection management call elapsed time excluding the background process time.
Q85. Identify two correct statements about the Active Session History (ASH) data. (Choose two.)
A. A part of SGA memory is used to store ASH data as rolling buffer.
B. The ASH data can be analyzed between any two small time intervals.
C. All ASH data in memory are flushed to disk by MMON in every 3 seconds.
D. All ASH data in memory are flushed to disk by MMNL process whenever the buffer is full.
Improved 1z0-054 question:
Q86. As a DBA, you notice a change in the performance of your database.
Which two pieces of tuning-related information can you examine in the alert log to identify possible causes? (Choose two.)
A. the number of block corruption errors
B. SQL statements that are consuming maximum resources
C. the top five sessions that are consuming maximum resources
D. the instance parameter values that have been changed using ALTER SYSTEM since the last baseline
Q87. You are a DBA in ABC Corp. You are working on an online transaction processing (OLTP) system.The applications running on the database use connection pooling to connect to the database to perform transactions. The company wants to upgrade the CPU and the memory for the machine on which the production database is running. To test the realistic workload on the new machine with increased CPU and memory, you identified the peak workload time for the database and captured the workload on the production system. You set up the test machine with the upgraded CPU and memory, and performed the following operations for replay:
1) You preprocessed the captured workload.
2) You restored the replay database to match the capture database at the start of the workload capture.
3) You resolved all external references.
4) You set up replay clients.
You have the following replay considerations:
1) load testing to be performed
2) all the sessions to connect immediately
3) user waits between issuing calls to be kept to a minimum
What replay options would you choose to accomplish the replay?
A. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 0,
CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE
B. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,
CONNECT_TIME_SCALE = 0 and THINK_TIME_AUTO_CORRECT = FALSE
C. SYNCHRONIZATION = FALSE, THINK_TIME_SCALE = 100, CONNECT_TIME_SCALE = 100 and
THINK_TIME_AUTO_CORRECT = TRUE
D. SYNCHRONIZATION = TRUE, THINK_TIME_SCALE = 100,
CONNECT_TIME_SCALE = 100 and
THINK_TIME_AUTO_CORRECT = FALSE
Q88. You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below:
SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows
WHERE table_name = 'CUST'
TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS
---------- ---------- ---------- ---------- ----------
CUST 13768 80 111060
What would you recommend to reduce the I/O?
A. reorganizing the table to use fewer blocks
B. rebuilding the index on the CUST_FIRST_NAME column
C. increasing the value for the PCTFREE attribute for the CUST table
D. increasing the value for the PGA_AGGREGATE_TARGET initialization parameter
Q89. The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. You checked the Program Global Area (PGA)- related information in the AWR report to investigate it further.
View the Exhibit named PGAHIT and note the PGA cache-hit percentage.
View the Exhibit named PGAADV and analyze the content.
If you want to increase the size of PGA, you have sufficient memory available in the system. What would be the correct decision?
A. Double the size of PGA.
B. Increase the size of PGA to 120 MB.
C. Increase the size of PGA to 160 MB or more.
D. Leave the PGA at its current size and investigate other possible causes.
Q90. You look at the Top 5 Timed Events section of the Automatic Workload Repository (AWR) report and find that the wait event buffer busy waits is consuming a significant portion of DB time.
What does this wait event indicate?
A. buffer cache too small
B. insufficient DBWn processes
C. excessive block replacement
D. a large number of locally-managed tablespaces in the database
E. block contention on segment headers, data blocks, undo header, or undo blocks