1z0-054 | The Leading Guide To 1z0-054 practice test
Q101. You work for a small manufacturing company as a DBA. The company has various applications to manage the details of its business and customer base. The company has only one sales outlet where the operator updates the SALES table in the sales application with the details when a product is sold. The company has many other applications for various other businesses that use the same database. The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted.
During peak hours, you noticed the following:
When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers:
SQL> SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name='SLNSEQ'
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
SLNSEQ 1056400
You observed this behavior many times and only during peak hours. What could be the problem and solution for this?
A. The reserved pool is not configured. Configure the reserved pool.
B. The keep buffer pool is not configured. Configure the keep buffer pool.
C. The sequence was created with a CACHE of 100 numbers. Re-create the sequence with a higher CACHE number.
D. The sequence is aging out of the shared pool. Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.
Answer: D
Q102. Which three statements are true about Statspack usage? (Choose three.)
A. If both Statspack and AWR are used, they cannot be scheduled to capture snapshots at the same time.
B. Time Model data cannot be captured by Statspack unless the STATISTICS_LEVEL parameter is set to ALL.
C. Both Statspack and AWR can be used on the same database only if the Database Diagnostics pack is present.
D. Statspack can be used to generate a report for a specific instance as well as a specific SQL statement in an instance.
E. For best performance when running the performance reports, optimizer statistics should be gathered on the Statspack schema.
Answer: CDE
Q103. You plan to use adaptive thresholds as part of the performance tuning activity. You decide to increase the window size of the default moving window baseline for all metric observations and comparisons in your database. The following error occurs when you try to increase the window size through Enterprise Manager:
Failed to commit: ORA-13541: system moving window baseline size (1296000) greater than retention (1036800) ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 601 ORA-06512: at line 2
Which action would allow you to perform the preceding task successfully?
A. increasing the flashback retention period
B. increasing the retention period for SQL Management Base
C. increasing the undo retention period for the database instance
D. increasing the Automatic Workload Repository (AWR) retention period
Answer: D
Q104. View the Exhibit. You decide to select the setting option in the Exhibit to choose a baseline to be displayed in the performance page.
What would you achieve if the baseline is selected in the performance page?
Exhibit:
A. A line appears in the chart that displays the 99th percentile level of the baseline.
B. The performance page displays session-wise statistics compared with the baseline.
C. The performance page displays the metric names and corresponding threshold values set to them.
D. An extra chart appears that shows the performance at the time when the base line was last computed.
Answer: A
Q105. You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the database performance.
What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?
A. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
B. Increase the value of the STALE_PERCENT preference for these tables.
C. Mark those associated indexes that are less frequently used as invisible.
D. Increase the value of the ESTIMATE_PERCENT preference for these tables
Answer: B
Q106. You work as a DBA for a company and as a performance improvement measure, you implemented the result cache in your database.
View the Exhibit named SETTING and note the result cache settings.
Many users in the company state that performance has improved on the queries they use but some users complain that they have not got any performance benefit on the queries they use.
You checked all the queries they use and the following is one of them:
SQL> SELECT slnoq.currval as "SLNO", prod_id, pdname,
2 cust_name FROM sales WHERE sl_date < sysdate;
View the Exhibit named TEST and examine the testing performed to check this.
Why is the result cache not used? (Choose all that apply.)
A. because the query uses SYSDATE
B. because the query uses an alias for a column
C. because the query uses the SLNOQ.CURRVAL sequence
D. because the CLIENT_RESULT_CACHE_SIZE parameter is set to 0
Answer: AC
Q107. You are working 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. However, 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.Performance is the primary goal of the database because applications are demanding in terms of I/O operations and it should have lower mean recovery time (MTTR) than the current MTTR.
Which RAID option would you recommend?
A. RAID level 0
B. RAID level 1
C. RAID level 5
D. RAID level 1 over 0(0+1)
Answer: B
Q108. 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
Answer: E
Q109. You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.
You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings:
SQL> show parameter sga
NAME TYPE VALUE
------------------------------ --------------- -----------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 0
SQL> show parameter target
NAME TYPE VALUE
------------------------------ --------------- ----------------- .....
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 100M
sga_target big integer 0
You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA).
Which action would solve this problem?
Exhibit:
A. Set the SGA_TARGET parameter to 300M.
B. Set the SGA_MAX_SIZE parameter to 400M.
C. Set the MEMORY_TARGET parameter to 100M.
D. Set the MEMORY_MAX_TARGET parameter to 300M.
Answer: A
Q110. View Exhibit1 to examine the description of the CUSTOMERS table.
You observed that optimizer selectivity is not accurate when the CUST_STATE_PROVINCE and COUNTRY_ID columns are used together in the WHERE clause of a query. View Exhibit2 to examine the query execution plan and the commands executed to gather the statistics.
The optimizer predicts that 20 rows will be processed rather than the 3,341 rows, which is the actual number of rows returned from the table.
What can you do to make the optimizer detect the actual number of rows?
A. Set the STATISTICS_LEVEL parameter to ALL.
B. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to FALSE.
C. Create extended statistics for the CUST_STATE_PROVINCE and COUNTRY_ID columns.
D. Increase the STALE_PERCENT value for the CUSTOMERS table by using the DBMS_STATS.SET_TABLE_PREFS procedure.
Answer: C